/*
created		4/9/2013
modified		4/11/2013
project		
model		
company		
author		Dao
version		1.2
database		mysql 4.0 
*/


create table student (
	user_id char(15) not null,
	class_id char(10) not null,
	user_password char(35) not null,
	user_name char(100) not null,
	user_mobile char(15),
	user_email char(200),
 primary key (user_id));

create table department (
	dept_id char(20) not null,
	deft_name char(100) not null,
 primary key (dept_id));

create table room (
	room_id char(20) not null,
	room_desc text not null,
	room_seat int,
 primary key (room_id));

create table staff (
	staff_id char(10) not null,
	staff_password char(35) not null,
	staff_name char(100) not null,
	dept_id char(20) not null,
 primary key (staff_id));

create table teacher (
	teacher_id char(10) not null,
	teacher_pass char(35) not null,
	teacher_name char(100) not null,
	science_id char(10) not null,
 primary key (teacher_id));

create table course (
	corse_id char(10) not null,
	staff_id char(10) not null,
	course_name char(100) not null,
 primary key (corse_id));

create table class (
	class_id char(10) not null,
	science_id char(10) not null,
	level_id char(10) not null,
	class_desc char(100) not null,
	class_start char(4) not null,
 primary key (class_id));

create table subjects (
	sub_id char(20) not null,
	sub_id_prerequisite char(20),
	science_id char(10) not null,
	sub_name char(100) not null,
	subject_stc int not null,
	sub_enable bit default false,
 primary key (sub_id));

create table semester (
	sem_id char(5) not null,
	sem_desc char(100) not null,
 primary key (sem_id));

create table trainningprogram (
	sub_id char(20) not null,
	sem_id char(5) not null,
	level_id char(10) not null,
 primary key (sub_id,sem_id,level_id));

create table level (
	level_id char(10) not null,
	level_desc char(100) not null,
	year int not null,
 primary key (level_id));

create table classsubject (
	class_sub_id char(10) not null,
	room_id char(20) not null,
	teacher_id char(10),
	sub_id char(20) not null,
	amount int not null,
	date_start date not null,
	date_end date not null,
	th char(1) not null,
	unit char(2) not null,
	date_update date not null,
	corse_id char(10) not null,
	class_id char(10) not null,
 primary key (class_sub_id));

create table studentregistrationclass (
	class_sub_id char(10) not null,
	timetable_id char(10) not null,
 primary key (class_sub_id,timetable_id));

create table science (
	science_id char(10) not null,
	science_desc char(100) not null,
 primary key (science_id));

create table timeregistration (
	id char(10) not null,
	date_start date not null,
	date_end date not null,
	enable bit not null default false,
 primary key (id));

create table timetable (
	timetable_id char(10) not null,
	user_id char(15) not null,
	semester char(2) not null,
	yearstudy char(10) not null,
 primary key (timetable_id));
 
INSERT INTO `level` VALUES ('cd', 'Cao Đẳng', 3);
INSERT INTO `level` VALUES ('dh', 'Đại Học', 4);


INSERT INTO `science` VALUES ('00', 'Default');
INSERT INTO `science` VALUES ('cntt', 'Công Nghệ Thông Tin');
INSERT INTO `science` VALUES ('kt', 'Kinh Tế');


INSERT INTO `class` VALUES ('cd11kt', 'kt', 'cd', 'CD11TK', '2011');
INSERT INTO `class` VALUES ('dh08dt', 'cntt', 'dh', 'DH08DT', '2008');
INSERT INTO `class` VALUES ('dh09dt', 'cntt', 'dh', 'DH09DT', '2009');
INSERT INTO `class` VALUES ('dh09th', 'cntt', 'cd', 'CD09TH', '2009');
INSERT INTO `class` VALUES ('dh10kt', 'kt', 'dh', 'DH10TK', '2010');

INSERT INTO `student` VALUES ('09130027', 'dh09dt', '123', 'Nguyễn Xuân Hòa', '01626450100', 'xuanhoa@gmail.com');
INSERT INTO `student` VALUES ('09130046', 'dh09dt', '123', 'Nguyễn Chính Luân', '0956696568', 'luanxt@yahoo.com');
INSERT INTO `student` VALUES ('09130052', 'dh09dt', '123', 'Đào Lê Duy Nam', '01229404007', 'dlduynam@gmail.com');

INSERT INTO `department` VALUES ('dt', 'Đào Tạo');

INSERT INTO `staff` VALUES ('s12345', '123', 'Nguyễn Văn A', 'dt');

INSERT INTO `subjects` VALUES ('000000', NULL, '00', 'Default', 0, 1);
INSERT INTO `subjects` VALUES ('214231', '000000', 'cntt', 'Cấu trúc máy tính', 2, 0);
INSERT INTO `subjects` VALUES ('214241', '000000', 'cntt', 'Mạng máy tính cơ bản', 3, 0);
INSERT INTO `subjects` VALUES ('214242', '000000', 'cntt', 'Nhập môn hệ điều hành', 3, 0);
INSERT INTO `subjects` VALUES ('214321', '000000', 'cntt', 'Lập trình cơ bản', 4, 0);
INSERT INTO `subjects` VALUES ('214331', '214241', 'cntt', 'Lập trình nâng cao', 4, 0);

INSERT INTO `semester` VALUES ('i', 'HK I');
INSERT INTO `semester` VALUES ('ii', 'HK II');
INSERT INTO `semester` VALUES ('iii', 'HK III');
INSERT INTO `semester` VALUES ('iv', 'HK IV');
INSERT INTO `semester` VALUES ('v', 'HK V');
INSERT INTO `semester` VALUES ('vi', 'HK VI');
INSERT INTO `semester` VALUES ('vii', 'HK VII');
INSERT INTO `semester` VALUES ('viii', 'HK VIII');

INSERT INTO `trainningprogram` VALUES ('214231', 'i', 'dh');
INSERT INTO `trainningprogram` VALUES ('214241', 'ii', 'dh');
INSERT INTO `trainningprogram` VALUES ('214331', 'iii', 'dh');
INSERT INTO `trainningprogram` VALUES ('214242', 'iv', 'dh');

INSERT INTO `room` VALUES ('r100', 'RD100', 50);
INSERT INTO `room` VALUES ('r101', 'RD101', 50);
INSERT INTO `room` VALUES ('r200', 'RD200', 200);
INSERT INTO `room` VALUES ('t101', 'TV101', 150);
INSERT INTO `room` VALUES ('t102', 'TV102', 200);
INSERT INTO `room` VALUES ('t103', 'TV103', 300);

alter table timetable add foreign key (user_id) references student (user_id) on delete  restrict on update  restrict;

alter table staff add foreign key (dept_id) references department (dept_id) on delete  restrict on update  restrict;

alter table classsubject add foreign key (room_id) references room (room_id) on delete  restrict on update  restrict;

alter table course add foreign key (staff_id) references staff (staff_id) on delete  restrict on update  restrict;

alter table classsubject add foreign key (teacher_id) references teacher (teacher_id) on delete  restrict on update  restrict;

alter table classsubject add foreign key (corse_id) references course (corse_id) on delete  restrict on update  restrict;

alter table student add foreign key (class_id) references class (class_id) on delete  restrict on update  restrict;

alter table classsubject add foreign key (class_id) references class (class_id) on delete  restrict on update  restrict;

alter table trainningprogram add foreign key (sub_id) references subjects (sub_id) on delete  restrict on update  restrict;

alter table classsubject add foreign key (sub_id) references subjects (sub_id) on delete  restrict on update  restrict;

alter table subjects add foreign key (sub_id_prerequisite) references subjects (sub_id) on delete  restrict on update  restrict;

alter table trainningprogram add foreign key (sem_id) references semester (sem_id) on delete  restrict on update  restrict;

alter table trainningprogram add foreign key (level_id) references level (level_id) on delete  restrict on update  restrict;

alter table class add foreign key (level_id) references level (level_id) on delete  restrict on update  restrict;

alter table studentregistrationclass add foreign key (class_sub_id) references classsubject (class_sub_id) on delete  restrict on update  restrict;

alter table subjects add foreign key (science_id) references science (science_id) on delete  restrict on update  restrict;

alter table class add foreign key (science_id) references science (science_id) on delete  restrict on update  restrict;

alter table teacher add foreign key (science_id) references science (science_id) on delete  restrict on update  restrict;

alter table studentregistrationclass add foreign key (timetable_id) references timetable (timetable_id) on delete  restrict on update  restrict;