---
# System prepended metadata

title: Database Final Project Report Source code

---

---
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); -->