university-db.sql

--
-- Create schema university
--

CREATE DATABASE IF NOT EXISTS university;
USE university;
CREATE TABLE  `university`.`course` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(10) NOT NULL,
  `name` varchar(30) NOT NULL,
  `length` int(11) default NULL,
  `teacher_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_teacher` (`teacher_id`),
  CONSTRAINT `fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COMMENT='Cours enseignés à l''université';

INSERT INTO `university`.`course` VALUES  (1,'DJ-INI','Initiation à java',4,1),
 (2,'DJ-ADV','Java, compléments',5,1),
 (3,'DJ-WEB','Java pour le Web',3,1),
 (4,'MM-UML','Analyse et conception avec UML',4,NULL),
 (5,'DJ-JSF','Framework JSF',4,NULL),
 (6,'DJ-STRUTS','Framework Struts',3,NULL);

CREATE TABLE  `university`.`course_student` (
  `course_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  KEY `fk_course` (`course_id`),
  KEY `fk_student` (`student_id`),
  CONSTRAINT `fk_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
  CONSTRAINT `fk_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Association entre des cours et des étudiants';

CREATE TABLE  `university`.`student` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `forname` varchar(30) default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='Etudiants inscrits à l''université';

CREATE TABLE  `university`.`teacher` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `forname` varchar(30) default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='Professeurs enseignants à l''université';

INSERT INTO `university`.`teacher` VALUES  (1,'Hassler','Alexis',28);