CREATE TABLE STUDENT ( ID int(11) NOT NULL auto_increment, NUMBER char(15) NOT NULL UNIQUE, NAME varchar(4) NOT NULL , SEX varchar(1) default NULL, NATION varchar(10) default NULL, ENROLL_YEAR YEAR(4) NOT NULL , BIRTHDAY date default NULL, BIRTH_PLACE varchar(50) default NULL, PASSWORD char(15) NOT NULL , CLASS_ID int(11) NOT NULL , PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE TEACHER( ID int(11) NOT NULL auto_increment, NAME varchar(4) default NULL , SEX varchar(1) default NULL, USERNAME char(15) NOT NULL UNIQUE , PASSWORD char(15) NOT NULL , PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE DEPARTMENT( ID int(11) NOT NULL auto_increment, NAME varchar(20) NOT NULL, PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE SPECIAL( ID int(11) NOT NULL auto_increment, NAME varchar(20) NOT NULL, DEPARTMENT_ID int(11) NOT NULL , PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE CLASS( ID int(11) NOT NULL auto_increment, NAME varchar(20) NOT NULL, TEACHER_ID int(11) NOT NULL , SPECIAL_ID int(11) NOT NULL , YEAR year(4) NOT NULL , PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE COURSE( ID int(11) NOT NULL auto_increment, NAME varchar(20) NOT NULL, TEACHER_ID int(11) NOT NULL , HOUR int(4) default NULL, CREDIT float(2,1) NOT NULL , YEAR year(4) default NULL, TERM varchar(1) default NULL, EXAM_MODE varchar(2) default NULL, EXAM_TYPE varchar(2) default NULL, TYPE varchar(2) default NULL, PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE SCORE( COURSE_ID int(11) NOT NULL , STUDENT_ID int(11) NOT NULL , VALUE int(4) default NULL , DATE date NOT NULL , PRIMARY KEY (STUDENT_ID,COURSE_ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE ROOT( ID int(11) NOT NULL auto_increment, USERNAME char(15) NOT NULL UNIQUE , PASSWORD char(15) NOT NULL , PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE CLASS_COURSE( CLASS_ID int(11) NOT NULL , COURSE_ID int(11) NOT NULL , PRIMARY KEY (CLASS_ID,COURSE_ID))ENGINE=InnoDB DEFAULT CHARSET=utf8;alter table STUDENT add constraint FK_STUDENT_CLASS foreign key(CLASS_ID) references CLASS(ID) on delete cascade;alter table SPECIAL add constraint FK_SPECIAL_DEPARTMENT foreign key(DEPARTMENT_ID) references DEPARTMENT(ID) on delete cascade;alter table CLASS add constraint FK_CLASS_TEACHER foreign key(TEACHER_ID) references TEACHER(ID) on delete cascade;alter table CLASS add constraint FK_CLASS_SPECIAL foreign key(SPECIAL_ID) references SPECIAL(ID) on delete cascade;alter table SCORE add constraint FK_SCORE_COURSE foreign key(COURSE_ID) references COURSE(ID) on delete cascade;alter table SCORE add constraint FK_SCORE_SUTDENT foreign key(STUDENT_ID) references STUDENT(ID) on delete cascade;alter table CLASS_COURSE add constraint FK_CC_CLASS foreign key(CLASS_ID) references CLASS(ID) on delete cascade;alter table CLASS_COURSE add constraint FK_CC_COURSE foreign key(COURSE_ID) references COURSE(ID) on delete cascade;alter table COURSE add constraint FK_COURSE_TEACHER foreign key(TEACHER_ID) references TEACHER(ID) on delete cascade;insert into ROOT(USERNAME,PASSWORD) values('vlinux','lovefs');create trigger CLASS_COURSE_DELETE BEFORE DELETE ON CLASS_COURSE FOR EACH ROW DELETE FROM SCORE WHERE COURSE_ID=old.COURSE_ID and STUDENT_ID in (SELECT ID FROM STUDENT WHERE CLASS_ID=old.CLASS_ID);grant all on ENOVA.* to enova@localhost identified by "enova" with grant option; |
