---
title: Database Final Project Report Source code
---
<!-- ## create_tables.sql -->
:::info
<!-- :::spoiler create_tables.sql -->
```sql
-- create_tables.sql
CREATE TABLE department
(
id INTEGER NOT NULL, -- Department's ID
name TEXT NOT NULL, -- Department's name
degree_level INTEGER NOT NULL, -- Department's level of academic degree
CONSTRAINT pk__department
PRIMARY KEY (id),
CONSTRAINT ck__department__degree_level
CHECK (degree_level IN
(0 /* Bachelor */, 1 /* Master */, 2 /* Doctorate */))
);
CREATE TABLE class
(
dept_id INTEGER NOT NULL, -- Class's superior department's ID
grade INTEGER NOT NULL, -- Class's grade
id TEXT NOT NULL, -- Class's ID
CONSTRAINT pk__class
PRIMARY KEY (dept_id, grade, id),
CONSTRAINT fk__class__department
FOREIGN KEY (dept_id)
REFERENCES department (id)
);
CREATE TABLE student
(
id TEXT NOT NULL, -- Student's ID
name TEXT NOT NULL, -- Student's name
status INTEGER NOT NULL, -- Student's status of study
dept_id INTEGER, -- Student's department's ID
grade INTEGER, -- Student's grade
class_id TEXT, -- Student's class's ID
CONSTRAINT pk__student
PRIMARY KEY (id),
CONSTRAINT fk__student__class
FOREIGN KEY (dept_id, grade, class_id)
REFERENCES class (dept_id, grade, id),
CONSTRAINT ck__student__status
CHECK (status IN
(0 /* Studying */, 1 /* Suspension */, 2 /* Withdrawal */))
);
CREATE TABLE room
(
id INTEGER, -- Room's ID
building_name TEXT NOT NULL, -- Room's building's name
room_name TEXT NOT NULL, -- Room's name
CONSTRAINT pk__room
PRIMARY KEY (id),
);
CREATE TABLE selection
(
student_id TEXT NOT NULL, -- Student's ID
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
id INTEGER NOT NULL, -- Selection's ID
result INTEGER NOT NULL, -- Selection's result
CONSTRAINT pk__selection
PRIMARY KEY (student_id, semester, course_no, id),
CONSTRAINT fk__selection__student
FOREIGN KEY (student_id)
REFERENCES student (id),
CONSTRAINT fk__selection__course
FOREIGN KEY (semester, course_no)
REFERENCES session (semester, no),
CONSTRAINT ck__selection__result
CHECK (result IN
(0 /* Addition failed */,
1 /* Addition succeeded */,
2 /* Manually addition succeeded */))
);
CREATE TABLE taking
(
student_id TEXT NOT NULL, -- Student's ID
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
selection_id INTEGER NOT NULL, -- Selection's ID
score REAL, -- Taking's score
feedback_rank INTEGER, -- Taking's feedback rank
CONSTRAINT pk__taking
PRIMARY KEY (student_id, semester, course_no, selection_id),
CONSTRAINT fk__taking__selection
FOREIGN KEY (student_id, semester, course_no, selection_id)
REFERENCES selection (student_id, semester, course_no, id),
CONSTRAINT ck__taking__feedback_rank
CHECK (feedback_rank IS NULL OR feedback_rank BETWEEN 1 AND 5)
);
CREATE UNIQUE INDEX uq__taking__student_id_semester_course_no
ON taking (student_id, semester, course_no);
CREATE TABLE course
(
no TEXT NOT NULL, -- Course's number
name TEXT NOT NULL, -- Course's name
type INTEGER NOT NULL, -- Course's type of required or elective
credit INTEGER NOT NULL, -- Course's credit
limitation INTEGER NOT NULL, -- Course's maximum number of students
CONSTRAINT pk__course
PRIMARY KEY (no),
CONSTRAINT ck__course__type
CHECK (type IN (0 /* Required */, 1 /* Elective */)),
);
CREATE TABLE session
(
semester TEXT NOT NULL,
course_no TEXT NOT NULL,
room_id TEXT NOT NULL,
status INTEGER NOT NULL,
CONSTRAINT pk__session
PRIMARY KEY (semester, course_no),
CONSTRAINT fk__session__course
FOREIGN KEY (course_no)
REFERENCES course (no),
CONSTRAINT fk__session__room
FOREIGN KEY (room_id)
REFERENCES room (id),
CONSTRAINT ck__course__status
CHECK (status IN (0 /* Proceeded */, 1 /* Cancelled */))
);
CREATE TABLE teacher
(
id INTEGER NOT NULL, -- Teacher's ID
name TEXT NOT NULL, -- Teacher's name
CONSTRAINT pk__teacher
PRIMARY KEY (id)
);
CREATE TABLE curriculum_field
(
id INTEGER NOT NULL, -- Field's ID
name TEXT NOT NULL, -- Field's name
CONSTRAINT pk__curriculum_field
PRIMARY KEY (id)
);
CREATE TABLE field_of_course
(
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
field_id INTEGER NOT NULL, -- Field's ID
CONSTRAINT pk__field_of_course
PRIMARY KEY (semester, course_no, field_id),
CONSTRAINT fk__field_of_course__course
FOREIGN KEY (semester, course_no)
REFERENCES session (semester, course_no),
CONSTRAINT fk__field_of_course__curriculum_field
FOREIGN KEY (field_id)
REFERENCES curriculum_field (id)
);
CREATE TABLE teaching
(
teacher_id INTEGER NOT NULL, -- Teacher's ID
course_no TEXT NOT NULL, -- Course's number
CONSTRAINT pk__teaching
PRIMARY KEY (teacher_id, course_no),
CONSTRAINT fk__teaching__teacher
FOREIGN KEY (teacher_id)
REFERENCES teacher (id),
CONSTRAINT fk__teaching__course
FOREIGN KEY (course_no)
REFERENCES course (no)
);
CREATE TABLE session_timetable
(
semester TEXT NOT NULL, -- Session's semester
course_no TEXT NOT NULL, -- Session's number
weekday_code INTEGER NOT NULL, -- Session's time slot's code of weekdays
time_code INTEGER NOT NULL, -- Session's time slot's code of sessions
CONSTRAINT pk__course_timetable
PRIMARY KEY (semester, course_no, weekday_code, time_code),
CONSTRAINT fk__course_timetable__session
FOREIGN KEY (semester, course_no)
REFERENCES session (semester, course_no),
CONSTRAINT ck__course_timetable__timeslot
CHECK ((weekday_code BETWEEN 1 AND 7) AND
(session_code BETWEEN 1 AND 14 /* 1234Z56789ABCD */))
);
```
:::
<!-- ## insert_sample_data.sql -->
:::warning
<!-- :::spoiler insert_sample_data.sql -->
```sql
-- insert_sample_data.sql
INSERT INTO department VALUES(0,'數學系',0);
INSERT INTO department VALUES(1,'資訊工程系',0);
INSERT INTO department VALUES(2,'資訊工程研究所',1);
INSERT INTO department VALUES(3,'資訊管理系',0);
INSERT INTO department VALUES(4,'數學系碩士班',1);
INSERT INTO class VALUES(0,1,'A');
INSERT INTO class VALUES(1,1,'A');
INSERT INTO class VALUES(2,1,'A');
INSERT INTO class VALUES(3,1,'A');
INSERT INTO class VALUES(4,1,'A');
INSERT INTO student VALUES('0','張飛',0,0,1,'A');
INSERT INTO student VALUES('1','孫尚香',1,0,1,'A');
INSERT INTO student VALUES('2','周瑜',0,0,1,'A');
INSERT INTO student VALUES('3','黃蓋',0,0,1,'A');
INSERT INTO student VALUES('4','趙雲',0,0,1,'A');
INSERT INTO student VALUES('5','關興',0,0,1,'A');
INSERT INTO student VALUES('6','夏侯惇',0,0,1,'A');
INSERT INTO student VALUES('7','龐統',1,1,1,'A');
INSERT INTO student VALUES('8','關羽',0,1,1,'A');
INSERT INTO student VALUES('9','華雄',2,2,1,'A');
INSERT INTO student VALUES('10','華陀',0,2,1,'A');
INSERT INTO student VALUES('11','劉備',0,3,1,'A');
INSERT INTO student VALUES('12','呂布',0,2,1,'A');
INSERT INTO student VALUES('13','諸葛亮',0,2,1,'A');
INSERT INTO student VALUES('14','呂蒙',0,2,1,'A');
INSERT INTO student VALUES('15','圖靈',0,4,1,'A');
INSERT INTO student VALUES('16','巴斯卡',0,4,1,'A');
INSERT INTO student VALUES('17','大喬',0,1,1,'A');
INSERT INTO student VALUES('18','甘寧',0,1,1,'A');
INSERT INTO student VALUES('19','司馬昭',0,1,1,'A');
INSERT INTO student VALUES('20','馬超',0,1,1,'A');
INSERT INTO student VALUES('21','郭嘉',0,2,1,'A');
INSERT INTO room VALUES(0,'工程一館','K205');
INSERT INTO room VALUES(1,'工程五館','L102');
INSERT INTO room VALUES(2,'鴻經館','M-605');
INSERT INTO room VALUES(3,'管理二館','I1-018');
INSERT INTO room VALUES(4,'管理二館','I1-304');
INSERT INTO room VALUES(5,'綜教館','O-214');
INSERT INTO selection VALUES('0','1112','A0001',0,1);
INSERT INTO selection VALUES('1','1112','A0001',0,1);
INSERT INTO selection VALUES('2','1112','A0001',0,1);
INSERT INTO selection VALUES('3','1112','A0001',0,1);
INSERT INTO selection VALUES('4','1112','A0001',0,1);
INSERT INTO selection VALUES('5','1112','A0001',0,1);
INSERT INTO selection VALUES('6','1112','A0001',0,1);
INSERT INTO selection VALUES('7','1112','A0001',0,1);
INSERT INTO selection VALUES('8','1112','A0002',0,1);
INSERT INTO selection VALUES('7','1112','A0002',0,1);
INSERT INTO selection VALUES('2','1112','A0002',0,1);
INSERT INTO selection VALUES('3','1112','A0002',0,1);
INSERT INTO selection VALUES('4','1112','A0002',0,1);
INSERT INTO selection VALUES('6','1112','A0002',0,1);
INSERT INTO selection VALUES('9','1112','A0002',0,1);
INSERT INTO selection VALUES('10','1112','A0002',0,1);
INSERT INTO selection VALUES('7','1112','A0003',0,1);
INSERT INTO selection VALUES('0','1112','A0003',0,1);
INSERT INTO selection VALUES('3','1112','A0003',0,1);
INSERT INTO selection VALUES('5','1112','A0003',0,1);
INSERT INTO selection VALUES('11','1112','A0003',0,0);
INSERT INTO selection VALUES('9','1112','A0003',0,1);
INSERT INTO selection VALUES('12','1112','A0003',0,1);
INSERT INTO selection VALUES('10','1112','A0003',0,1);
INSERT INTO selection VALUES('13','1112','A0003',0,1);
INSERT INTO selection VALUES('14','1112','A0003',0,1);
INSERT INTO selection VALUES('15','1112','A0003',0,1);
INSERT INTO selection VALUES('16','1112','A0003',0,2);
INSERT INTO selection VALUES('7','1112','A0004',0,0);
INSERT INTO selection VALUES('0','1112','A0004',0,1);
INSERT INTO selection VALUES('1','1112','A0004',0,1);
INSERT INTO selection VALUES('3','1112','A0004',0,1);
INSERT INTO selection VALUES('4','1112','A0004',0,1);
INSERT INTO selection VALUES('6','1112','A0004',0,1);
INSERT INTO selection VALUES('12','1112','A0004',0,1);
INSERT INTO selection VALUES('8','1112','A0005',0,1);
INSERT INTO selection VALUES('17','1112','A0005',0,1);
INSERT INTO selection VALUES('18','1112','A0005',0,1);
INSERT INTO selection VALUES('1','1112','A0005',0,1);
INSERT INTO selection VALUES('2','1112','A0005',0,1);
INSERT INTO selection VALUES('3','1112','A0005',0,1);
INSERT INTO selection VALUES('11','1112','A0005',0,1);
INSERT INTO selection VALUES('8','1112','A0006',0,1);
INSERT INTO selection VALUES('7','1112','A0006',0,1);
INSERT INTO selection VALUES('19','1112','A0006',0,0);
INSERT INTO selection VALUES('20','1112','A0006',0,0);
INSERT INTO selection VALUES('0','1112','A0006',0,1);
INSERT INTO selection VALUES('1','1112','A0006',0,0);
INSERT INTO selection VALUES('2','1112','A0006',0,0);
INSERT INTO selection VALUES('3','1112','A0006',0,1);
INSERT INTO selection VALUES('5','1112','A0006',0,1);
INSERT INTO selection VALUES('6','1112','A0006',0,1);
INSERT INTO selection VALUES('11','1112','A0006',0,1);
INSERT INTO selection VALUES('9','1112','A0006',0,1);
INSERT INTO selection VALUES('10','1112','A0006',0,1);
INSERT INTO selection VALUES('13','1112','A0006',0,1);
INSERT INTO selection VALUES('14','1112','A0006',0,1);
INSERT INTO selection VALUES('0','1112','A0007',0,0);
INSERT INTO selection VALUES('9','1112','A0007',0,1);
INSERT INTO selection VALUES('12','1112','A0007',0,1);
INSERT INTO selection VALUES('21','1112','A0007',0,1);
INSERT INTO selection VALUES('10','1112','A0007',0,1);
INSERT INTO selection VALUES('7','1112','A0007',0,1);
INSERT INTO selection VALUES('15','1112','A0007',0,1);
INSERT INTO selection VALUES('16','1112','A0007',0,1);
INSERT INTO taking VALUES('0','1112','A0001',0,77.70000000000000284,1);
INSERT INTO taking VALUES('1','1112','A0001',0,NULL,NULL);
INSERT INTO taking VALUES('2','1112','A0001',0,55.999999999999999999,2);
INSERT INTO taking VALUES('3','1112','A0001',0,34.0,3);
INSERT INTO taking VALUES('4','1112','A0001',0,98.000000000000000001,4);
INSERT INTO taking VALUES('5','1112','A0001',0,55.0,5);
INSERT INTO taking VALUES('6','1112','A0001',0,66.999999999999999998,2);
INSERT INTO taking VALUES('7','1112','A0001',0,NULL,NULL);
INSERT INTO taking VALUES('8','1112','A0002',0,65.999999999999999999,3);
INSERT INTO taking VALUES('7','1112','A0002',0,NULL,NULL);
INSERT INTO taking VALUES('2','1112','A0002',0,93.000000000000000001,4);
INSERT INTO taking VALUES('3','1112','A0002',0,44.0,4);
INSERT INTO taking VALUES('4','1112','A0002',0,49.0,5);
INSERT INTO taking VALUES('6','1112','A0002',0,78.000000000000000001,3);
INSERT INTO taking VALUES('9','1112','A0002',0,NULL,NULL);
INSERT INTO taking VALUES('10','1112','A0002',0,74.0,5);
INSERT INTO taking VALUES('7','1112','A0003',0,NULL,NULL);
INSERT INTO taking VALUES('0','1112','A0003',0,45.999999999999999999,5);
INSERT INTO taking VALUES('3','1112','A0003',0,75.999999999999999999,4);
INSERT INTO taking VALUES('5','1112','A0003',0,86.999999999999999998,5);
INSERT INTO taking VALUES('9','1112','A0003',0,NULL,NULL);
INSERT INTO taking VALUES('12','1112','A0003',0,75.999999999999999999,4);
INSERT INTO taking VALUES('10','1112','A0003',0,80.0,5);
INSERT INTO taking VALUES('13','1112','A0003',0,78.000000000000000001,3);
INSERT INTO taking VALUES('14','1112','A0003',0,65.0,4);
INSERT INTO taking VALUES('15','1112','A0003',0,98.999999999999999996,3);
INSERT INTO taking VALUES('16','1112','A0003',0,69.0,1);
INSERT INTO taking VALUES('0','1112','A0004',0,55.999999999999999999,3);
INSERT INTO taking VALUES('1','1112','A0004',0,NULL,NULL);
INSERT INTO taking VALUES('3','1112','A0004',0,67.5,5);
INSERT INTO taking VALUES('4','1112','A0004',0,78.000000000000000001,4);
INSERT INTO taking VALUES('6','1112','A0004',0,88.999999999999999996,2);
INSERT INTO taking VALUES('12','1112','A0004',0,45.0,5);
INSERT INTO taking VALUES('8','1112','A0005',0,68.700000000000002843,1);
INSERT INTO taking VALUES('17','1112','A0005',0,63.000000000000000001,4);
INSERT INTO taking VALUES('18','1112','A0005',0,45.999999999999999999,5);
INSERT INTO taking VALUES('1','1112','A0005',0,NULL,NULL);
INSERT INTO taking VALUES('2','1112','A0005',0,78.000000000000000001,2);
INSERT INTO taking VALUES('3','1112','A0005',0,86.999999999999999998,3);
INSERT INTO taking VALUES('11','1112','A0005',0,96.000000000000000003,2);
INSERT INTO taking VALUES('8','1112','A0006',0,75.999999999999999999,4);
INSERT INTO taking VALUES('7','1112','A0006',0,NULL,NULL);
INSERT INTO taking VALUES('0','1112','A0006',0,34.0,3);
INSERT INTO taking VALUES('3','1112','A0006',0,80.0,4);
INSERT INTO taking VALUES('5','1112','A0006',0,61.999999999999999998,5);
INSERT INTO taking VALUES('6','1112','A0006',0,60.0,3);
INSERT INTO taking VALUES('11','1112','A0006',0,55.999999999999999999,5);
INSERT INTO taking VALUES('9','1112','A0006',0,NULL,NULL);
INSERT INTO taking VALUES('10','1112','A0006',0,98.000000000000000001,4);
INSERT INTO taking VALUES('13','1112','A0006',0,55.0,5);
INSERT INTO taking VALUES('14','1112','A0006',0,78.000000000000000001,5);
INSERT INTO taking VALUES('9','1112','A0007',0,NULL,NULL);
INSERT INTO taking VALUES('12','1112','A0007',0,79.0,5);
INSERT INTO taking VALUES('21','1112','A0007',0,86.999999999999999998,4);
INSERT INTO taking VALUES('10','1112','A0007',0,68.000000000000000001,3);
INSERT INTO taking VALUES('7','1112','A0007',0,NULL,NULL);
INSERT INTO taking VALUES('15','1112','A0007',0,98.999999999999999996,3);
INSERT INTO taking VALUES('16','1112','A0007',0,69.0,1);
INSERT INTO course VALUES('A0001','微積分',0,2,50);
INSERT INTO course VALUES('A0002','計算機概論',0,3,50);
INSERT INTO course VALUES('A0003','統計學習',1,3,50);
INSERT INTO course VALUES('A0004','經濟學',0,3,50);
INSERT INTO course VALUES('A0005','統計學',1,3,50);
INSERT INTO course VALUES('A0006','音樂欣賞',1,2,100);
INSERT INTO course VALUES('A0007','演算法',1,3,50);
INSERT INTO session VALUES('1112','A0001','0',0);
INSERT INTO session VALUES('1112','A0002','1',0);
INSERT INTO session VALUES('1112','A0003','2',0);
INSERT INTO session VALUES('1112','A0004','3',0);
INSERT INTO session VALUES('1112','A0005','4',0);
INSERT INTO session VALUES('1112','A0006','5',0);
INSERT INTO session VALUES('1112','A0007','1',0);
INSERT INTO teacher VALUES(0,'岳飛');
INSERT INTO teacher VALUES(1,'陸羽');
INSERT INTO teacher VALUES(2,'劉邦');
INSERT INTO teacher VALUES(3,'項羽');
INSERT INTO teacher VALUES(4,'孔丘');
INSERT INTO teacher VALUES(5,'莊周');
INSERT INTO teacher VALUES(6,'巴哈');
INSERT INTO teacher VALUES(7,'達文西');
INSERT INTO curriculum_field VALUES(0,'理論數學');
INSERT INTO curriculum_field VALUES(1,'基礎知識');
INSERT INTO curriculum_field VALUES(2,'人工智慧');
INSERT INTO curriculum_field VALUES(3,'財務工程');
INSERT INTO curriculum_field VALUES(4,'統計推論');
INSERT INTO curriculum_field VALUES(5,'統計推論');
INSERT INTO curriculum_field VALUES(6,'人文思想');
INSERT INTO curriculum_field VALUES(7,'資料科學');
INSERT INTO field_of_course VALUES('1112','A0001',0);
INSERT INTO field_of_course VALUES('1112','A0002',1);
INSERT INTO field_of_course VALUES('1112','A0002',2);
INSERT INTO field_of_course VALUES('1112','A0003',3);
INSERT INTO field_of_course VALUES('1112','A0003',4);
INSERT INTO field_of_course VALUES('1112','A0004',1);
INSERT INTO field_of_course VALUES('1112','A0005',1);
INSERT INTO field_of_course VALUES('1112','A0006',6);
INSERT INTO field_of_course VALUES('1112','A0007',2);
INSERT INTO field_of_course VALUES('1112','A0007',7);
INSERT INTO teaching VALUES(0,'A0001');
INSERT INTO teaching VALUES(1,'A0002');
INSERT INTO teaching VALUES(2,'A0003');
INSERT INTO teaching VALUES(3,'A0003');
INSERT INTO teaching VALUES(4,'A0004');
INSERT INTO teaching VALUES(5,'A0005');
INSERT INTO teaching VALUES(6,'A0006');
INSERT INTO teaching VALUES(7,'A0007');
INSERT INTO session_timetable VALUES('1112','A0001',1,6);
INSERT INTO session_timetable VALUES('1112','A0001',1,7);
INSERT INTO session_timetable VALUES('1112','A0001',1,8);
INSERT INTO session_timetable VALUES('1112','A0002',2,3);
INSERT INTO session_timetable VALUES('1112','A0002',2,4);
INSERT INTO session_timetable VALUES('1112','A0002',5,4);
INSERT INTO session_timetable VALUES('1112','A0003',4,6);
INSERT INTO session_timetable VALUES('1112','A0003',4,7);
INSERT INTO session_timetable VALUES('1112','A0003',4,8);
INSERT INTO session_timetable VALUES('1112','A0004',4,6);
INSERT INTO session_timetable VALUES('1112','A0004',4,7);
INSERT INTO session_timetable VALUES('1112','A0004',4,8);
INSERT INTO session_timetable VALUES('1112','A0005',5,2);
INSERT INTO session_timetable VALUES('1112','A0005',5,3);
INSERT INTO session_timetable VALUES('1112','A0005',5,4);
INSERT INTO session_timetable VALUES('1112','A0006',3,6);
INSERT INTO session_timetable VALUES('1112','A0006',3,7);
INSERT INTO session_timetable VALUES('1112','A0007',3,2);
INSERT INTO session_timetable VALUES('1112','A0007',3,3);
INSERT INTO session_timetable VALUES('1112','A0007',3,4);
```
:::
<!-- CREATE TABLE building
(
id TEXT NOT NULL, -- Building's ID
name TEXT NOT NULL, -- Building's name
CONSTRAINT pk__building
PRIMARY KEY (id)
); -->
<!-- CREATE TABLE room
(
building_id TEXT NOT NULL, -- Room's building's ID
id TEXT NOT NULL, -- Room's ID
CONSTRAINT pk__room
PRIMARY KEY (building_id, id),
CONSTRAINT fk__room__building
FOREIGN KEY (building_id)
REFERENCES building (id)
); -->
<!-- CREATE TABLE selection
(
student_id TEXT NOT NULL, -- Student's ID
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
id INTEGER NOT NULL, -- Selection's ID
result INTEGER NOT NULL, -- Selection's result
CONSTRAINT pk__selection
PRIMARY KEY (student_id, semester, course_no, id),
CONSTRAINT fk__selection__student
FOREIGN KEY (student_id)
REFERENCES student (id),
CONSTRAINT fk__selection__course
FOREIGN KEY (semester, course_no)
REFERENCES course (semester, no),
CONSTRAINT ck__selection__result
CHECK (result IN
(0 /* Addition failed */,
1 /* Addition succeeded */,
2 /* Manually addition succeeded */))
); -->
<!-- CREATE TABLE course
(
semester TEXT NOT NULL, -- Course's semester
no TEXT NOT NULL, -- Course's number
name TEXT NOT NULL, -- Course's name
type INTEGER NOT NULL, -- Course's type of required or elective
building_id TEXT, -- Course's location's building's ID
room_id TEXT, -- Course's location's room's ID
credit INTEGER NOT NULL, -- Course's credit
limitation INTEGER NOT NULL, -- Course's maximum number of students
status INTEGER NOT NULL, -- Course's status of proceeded or cancelled
CONSTRAINT pk__course
PRIMARY KEY (semester, no),
CONSTRAINT fk__course__room
FOREIGN KEY (building_id, room_id)
REFERENCES room (building_id, id),
CONSTRAINT ck__course__type
CHECK (type IN (0 /* Required */, 1 /* Elective */)),
CONSTRAINT ck__course__status
CHECK (status IN (0 /* Proceeded */, 1 /* Cancelled */))
); -->
<!-- CREATE TABLE teaching
(
teacher_id INTEGER NOT NULL, -- Teacher's ID
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
CONSTRAINT pk__teaching
PRIMARY KEY (teacher_id, semester, course_no),
CONSTRAINT fk__teaching__teacher
FOREIGN KEY (teacher_id)
REFERENCES teacher (id),
CONSTRAINT fk__teaching__course
FOREIGN KEY (semester, course_no)
REFERENCES course (semester, no)
); -->
<!-- CREATE TABLE course_timetable
(
semester TEXT NOT NULL, -- Course's semester
course_no TEXT NOT NULL, -- Course's number
weekday_code INTEGER NOT NULL, -- Course's time slot's code of weekdays
session_code INTEGER NOT NULL, -- Course's time slot's code of sessions
CONSTRAINT pk__course_timetable
PRIMARY KEY (semester, course_no, weekday_code, session_code),
CONSTRAINT fk__course_timetable__course
FOREIGN KEY (semester, course_no)
REFERENCES course (semester, no),
CONSTRAINT ck__course_timetable__timeslot
CHECK ((weekday_code BETWEEN 1 AND 7) AND
(session_code BETWEEN 1 AND 14 /* 1234Z56789ABCD */))
); -->
<!-- INSERT INTO building VALUES('0','工程一館');
INSERT INTO building VALUES('1','工程五館');
INSERT INTO building VALUES('2','鴻經館');
INSERT INTO building VALUES('3','管理二館');
INSERT INTO building VALUES('4','綜教館');
INSERT INTO room VALUES('0','K205');
INSERT INTO room VALUES('1','L102');
INSERT INTO room VALUES('2','M-605');
INSERT INTO room VALUES('3','I1-018');
INSERT INTO room VALUES('3','I1-304');
INSERT INTO room VALUES('4','O-214'); -->
<!-- INSERT INTO course VALUES('1112','A0001','微積分',0,'0','K205',2,50,0);
INSERT INTO course VALUES('1112','A0002','計算機概論',0,'1','L102',3,50,0);
INSERT INTO course VALUES('1112','A0003','統計學習',1,'2','M-605',3,50,0);
INSERT INTO course VALUES('1112','A0004','經濟學',0,'3','I1-018',3,50,0);
INSERT INTO course VALUES('1112','A0005','統計學',1,'3','I1-304',3,50,0);
INSERT INTO course VALUES('1112','A0006','音樂欣賞',1,'4','O-214',2,100,0);
INSERT INTO course VALUES('1112','A0007','演算法',1,'1','L102',3,50,0); -->
<!-- INSERT INTO teaching VALUES(0,'1112','A0001');
INSERT INTO teaching VALUES(1,'1112','A0002');
INSERT INTO teaching VALUES(2,'1112','A0003');
INSERT INTO teaching VALUES(3,'1112','A0003');
INSERT INTO teaching VALUES(4,'1112','A0004');
INSERT INTO teaching VALUES(5,'1112','A0005');
INSERT INTO teaching VALUES(6,'1112','A0006');
INSERT INTO teaching VALUES(7,'1112','A0007'); -->
<!-- INSERT INTO course_timetable VALUES('1112','A0001',1,6);
INSERT INTO course_timetable VALUES('1112','A0001',1,7);
INSERT INTO course_timetable VALUES('1112','A0001',1,8);
INSERT INTO course_timetable VALUES('1112','A0002',2,3);
INSERT INTO course_timetable VALUES('1112','A0002',2,4);
INSERT INTO course_timetable VALUES('1112','A0002',5,4);
INSERT INTO course_timetable VALUES('1112','A0003',4,6);
INSERT INTO course_timetable VALUES('1112','A0003',4,7);
INSERT INTO course_timetable VALUES('1112','A0003',4,8);
INSERT INTO course_timetable VALUES('1112','A0004',4,6);
INSERT INTO course_timetable VALUES('1112','A0004',4,7);
INSERT INTO course_timetable VALUES('1112','A0004',4,8);
INSERT INTO course_timetable VALUES('1112','A0005',5,2);
INSERT INTO course_timetable VALUES('1112','A0005',5,3);
INSERT INTO course_timetable VALUES('1112','A0005',5,4);
INSERT INTO course_timetable VALUES('1112','A0006',3,6);
INSERT INTO course_timetable VALUES('1112','A0006',3,7);
INSERT INTO course_timetable VALUES('1112','A0007',3,2);
INSERT INTO course_timetable VALUES('1112','A0007',3,3);
INSERT INTO course_timetable VALUES('1112','A0007',3,4); -->
Sign in with Wallet
Connect another wallet