---
# System prepended metadata

title: Database Final Project Report

---

# Database Final Project Report

## 大綱
:::info

[TOC]

:::

---

<style>
#doc.markdown-body {max-width: 960px !important;}
</style>

## 團隊
<table class="u17b4-table" style="font-family: monospace; text-align: center">
    <tr><td colspan="3" style="font-weight:948.763">Group 1</td></tr>
    <tr><th style="width: 10%;text-align: center">成員姓名</th>
        <th style="width: 15%;text-align: center">系級</th>
        <th style="width: 15%;text-align: center">學號</th></tr>
    <tr><td>謝程偉</td><td>資訊工程學系四年級 A 班</td><td>108502516</td></tr>
    <tr><td>洪裕翔</td><td>資訊工程學系四年級 A 班</td><td>108502520</td></tr>
    <tr><td>陳彥全</td><td>資訊工程學系四年級 A 班</td><td>108502521</td></tr>
    <tr><td>劉子雍</td><td>資訊工程學系四年級 A 班</td><td>108502523</td></tr>
</table>


## 1. 資料表正規化
1. department
    ![department](https://drive.google.com/uc?export=view&id=11qe9DPSUJ8FUcsl_HzlZn_dK3WnRCOlD)

---

2. class
    ![class](https://drive.google.com/uc?export=view&id=11r7Au6fK8SjsLJvbwkXnIAJ1UpA_tD0G)

---

3. student
    ![student](https://drive.google.com/uc?export=view&id=11rInL-MoDwgSrTNMDpXhjrgt9hUnXbLY)

---

<!-- 4. building
    ![building](https://drive.google.com/uc?export=view&id=11zTSh5dl0D5EOKVtbtlsZluvEz0RoHVm)

--- -->

4. room
    ![room](https://drive.google.com/uc?export=view&id=13yc5riC8xT7um_GZr1sprQUEjC2a4z8w)
<!--     ![room](https://drive.google.com/uc?export=view&id=127gMYXFQM7ARFWp3d_S6vrWe5iFM-QkS) -->

---

5. selection
    ![selection](https://drive.google.com/uc?export=view&id=13yeN4ab8o4Vo2ONzOyq42xCro7ljO0_U)
<!--     ![selection](https://drive.google.com/uc?export=view&id=12COV7bF3VGGhmeiQ0MQDSH4H1uIF-JvF) -->

---

6. taking
    ![taking](https://drive.google.com/uc?export=view&id=14-xDyydtlhrQZCrAtWxkhv9gPdei_pHm)
<!--     ![taking](https://drive.google.com/uc?export=view&id=12651RENx9jtVNbsCn3c-_QJax7Bo0qL_) -->

---

7. course
    ![course](https://drive.google.com/uc?export=view&id=1459LSoW637-wwh1oldwcHo5zuYFW9CTZ)
<!--     ![course](https://drive.google.com/uc?export=view&id=129SFXxh1cBIVKN0BD7ifvp_EKuQVnIKu) -->

---

8. session
    ![session](https://drive.google.com/uc?export=view&id=1498kxuxutvzeomeovhxensRyIGfoCF3i)

---

9. teacher
    ![teacher](https://drive.google.com/uc?export=view&id=128k3zozq5mPcV0yb2KgobUONmnMTdFg4)

---

<!-- 11. course_timeable
    ![course_timeable](https://drive.google.com/uc?export=view&id=11vL3uZSksDRbv_kaPWsBoS6S9wf16aW0)

--- -->

10. curriculum_field
    ![curriculum_field](https://drive.google.com/uc?export=view&id=11wRWhjF0e6Y53E1VvbSQ6ke4TRMc_KLE)

---

11. field_of_course
    ![field_of_course](https://drive.google.com/uc?export=view&id=14AJFLhn6U_wKBwvLJ7CKIN4pTMhQUFy8)
<!--     ![field_of_course](https://drive.google.com/uc?export=view&id=11tyagD027l_EyDx0vFBR1i-xUvBVmxQa) -->

---

12. teaching
    ![teaching](https://drive.google.com/uc?export=view&id=14DTCZGrxMOpeWNWqTxw9fXZHBmTjCISI)
<!--     ![teaching](https://drive.google.com/uc?export=view&id=122o1uImiI2vAi8KmSPV1gzI_dQL5Luag) -->

---

13. session_timetable
    ![session_timetable](https://drive.google.com/uc?export=view&id=14Ed90-USR7TFIRunESfUHgqUBJcJk5FG)

---

- We edit above table images in this [Google Sheet](https://docs.google.com/spreadsheets/d/16TBPoA2hyfdQeI3JLlJZ47ktiM7nFLDGi9Piyb5rJZs/edit?usp=sharing).
- Contents in **RED** font color in the tables mean newly added or updated contents.

## 2. <!--ER-Diagram 與 -->ER-Model
<!-- ![ER-Model](https://drive.google.com/uc?export=view&id=12MZL2g_keiQ-e-iUrgNjJAkTROv8yqCW) -->
<!-- ![ER-Model](https://hackmd.io/_uploads/rkxOvAO8h.png) -->
![ER-Model](https://hackmd.io/_uploads/Bk3dEiGDh.png)
> 圖1：我們設計之資料表的 **ER-Model**。
> > 註：Weak Entities 的 Partial Keys 暫且用實心底線表示，而非虛線底線。

<!-- --- -->

<!-- ![ER-Diagram](https://drive.google.com/uc?export=view&id=12HojLYhPNG5851DaD565byjbtkEslvBo) -->
<!-- ![ER-Diagram](https://hackmd.io/_uploads/By4_PRO8n.png)
> 圖2：我們設計之資料表的 **ER-Diagram**。 -->


## 3. 使用 SQL 解決事件
1. 「A0001微積分」上課地點要由K205修改到K210 大教室，該怎麼做？
    ```sql
    INSERT OR IGNORE INTO room (building_name, room_name)
    VALUES ('工程一館', 'K210');

    UPDATE session AS s, room AS r
       SET s.room_id = r.id
     WHERE s.semester = '1112'
       AND s.course_no = 'A0001'
       AND r.building_name = '工程一館'
       AND r.room_name = 'K210';
    ```
<!--    ```sql=
    INSERT OR IGNORE INTO room
    VALUES (0 /* 工程一館 */, 'K210');

    UPDATE course
       SET room_id = 'K210'
     WHERE semester = '1112'
       AND no = 'A0001';
    ``` -->


2. 請列出「A0002 計算機概」的修課名單（點名表）。
    ```sql
    SELECT d.name  AS '系所',
           c.grade AS '年級',
           c.id    AS '班級',
           s.id    AS '學號',
           s.name  AS '姓名'
      FROM taking AS t
           JOIN student AS s
           ON s.id = t.student_id
           LEFT JOIN class AS c
           ON c.dept_id = s.dept_id AND c.grade = s.grade AND c.id = s.class_id
           LEFT JOIN department AS d
           ON d.id = c.dept_id
     WHERE t.semester = '1112'
       AND t.course_no = 'A0002';
    ```

    | 系所           | 年級 | 班級 | 學號 |  姓名  |
    | -------------- | ---- | ---- | ---- |:------:|
    | 資訊工程研究所 | 1    | A    | 10   |  華陀  |
    | 數學系         | 1    | A    | 2    |  周瑜  |
    | 數學系         | 1    | A    | 3    |  黃蓋  |
    | 數學系         | 1    | A    | 4    |  趙雲  |
    | 數學系         | 1    | A    | 6    | 夏侯惇 |
    | 資訊工程系     | 1    | A    | 7    |  龐統  |
    | 資訊工程系     | 1    | A    | 8    |  關羽  |
    | 資訊工程研究所 | 1    | A    | 9    |  華雄  |

3. 請列出課程成績不及格的學生比例資料（大學部：低於60分、碩博：70分），產出欄位範例如下：
    | 課名 | 授課教師 | 不及格人次 | 修課人次 | 不及格比例 |
    | ---- | ------ | -------- | ------- | -------- |
    ```sql
    SELECT c.name                                          AS "課名",
           GROUP_CONCAT(DISTINCT t2.name)                  AS "授課教師",
           /* degree_level = 0 means "Bachelor". */
           COUNT(iif(t3.score < iif(d.degree_level = 0, 60, 70),
                     1, NULL))                             AS "不及格人次",
           COUNT(s.id)                                     AS "修課人次",
           /* degree_level = 0 means "Bachelor". */
           CAST(COUNT(iif(t3.score < iif(d.degree_level = 0, 60, 70),
                          1, NULL)) AS REAL) / COUNT(s.id) AS "不及格比例"
      FROM course AS c
           LEFT JOIN teaching AS t
           ON c.no = t.course_no
           LEFT JOIN teacher AS t2
           ON t.teacher_id = t2.id
           LEFT JOIN taking AS t3
           ON c.no = t3.course_no
           LEFT JOIN student AS s
           ON t3.student_id = s.id
           LEFT JOIN department AS d
           ON s.dept_id = d.id
     GROUP BY c.no;
    ```
    <!--```sql=
    SELECT c.name                                          AS "課名",
           GROUP_CONCAT(DISTINCT t2.name)                  AS "授課教師",
           COUNT(iif(t3.score < iif(d.degree_level = 0 /* Bachelor */, 60, 70),
                     1, NULL))                             AS "不及格人次",
           COUNT(s.id)                                     AS "修課人次",
           CAST(COUNT(iif(t3.score < iif(d.degree_level = 0 /* Bachelor */, 60, 70),
                          1, NULL)) AS REAL) / COUNT(s.id) AS "不及格比例"
      FROM course AS c
           LEFT JOIN teaching AS t
           ON c.semester = t.semester AND c.no = t.course_no
           LEFT JOIN teacher AS t2
           ON t.teacher_id = t2.id
           LEFT JOIN taking AS t3
           ON c.semester = t3.semester AND c.no = t3.course_no
           LEFT JOIN student AS s
           ON t3.student_id = s.id
           LEFT JOIN department AS d
           ON s.dept_id = d.id
     GROUP BY c.semester, c.no;
    ``` -->

    | 課名       | 授課教師  | 不及格人次 | 修課人次 | 不及格比例          |
    | ---------- |:---------:| ---------- | -------- | ------------------- |
    | 微積分     |   岳飛    | 3          | 8        | 0.375               |
    | 計算機概論 |   陸羽    | 2          | 8        | 0.25                |
    | 統計學習   | 劉邦,項羽 | 6          | 22       | 0.2727272727272727  |
    | 經濟學     |   孔丘    | 2          | 6        | 0.3333333333333333  |
    | 統計學     |   莊周    | 1          | 7        | 0.14285714285714285 |
    | 音樂欣賞   |   巴哈    | 3          | 11       | 0.2727272727272727  |
    | 演算法     |  達文西   | 2          | 7        | 0.2857142857142857  |

4. 請列出各系學生修課領域分佈情況，產出欄位範例如下：
    | 學生系所 | 課程領域 | 人次 | 佔比 |
    | ------- | ------- | --- | --- |
    ```sql
      WITH
          dept_field_num(did, fid, fn) AS
              (SELECT d.id, cf.id, COUNT(s.id)
                 FROM department AS d
                      JOIN student AS s
                      ON d.id = s.dept_id
                      JOIN taking AS t
                      ON s.id = t.student_id
                      JOIN course AS c
                      ON t.course_no = c.no
                      JOIN field_of_course AS foc
                      ON c.no = foc.course_no
                      JOIN curriculum_field AS cf
                      ON foc.field_id = cf.id
                GROUP BY d.id, cf.id),
          dept_field_sum(did, fs) AS
              (SELECT did, SUM(fn)
                 FROM dept_field_num
                GROUP BY did)
    SELECT d.name                                   AS "學生系所",
           cf.name                                  AS "課程領域",
           IFNULL(dfn.fn, 0)                        AS "人次",
           IFNULL(CAST(dfn.fn AS REAL) / dfs.fs, 0) AS "占比"
      FROM department AS d
           CROSS JOIN curriculum_field AS cf
           LEFT JOIN dept_field_num AS dfn
           ON dfn.did = d.id AND dfn.fid = cf.id
           LEFT JOIN dept_field_sum AS dfs
           ON dfs.did = d.id;
    ```
    <!-- ```sql=
      WITH
          dept_field_num(did, fid, fn) AS
              (SELECT d.id, cf.id, COUNT(s.id)
                 FROM department AS d
                      JOIN student AS s
                      ON d.id = s.dept_id
                      JOIN taking AS t
                      ON s.id = t.student_id
                      JOIN course AS c
                      ON t.semester = c.semester AND t.course_no = c.no
                      JOIN field_of_course AS foc
                      ON c.semester = foc.semester AND c.no = foc.course_no
                      JOIN curriculum_field AS cf
                      ON foc.field_id = cf.id
                GROUP BY d.id, cf.id),
          dept_field_sum(did, fs) AS
              (SELECT did, SUM(fn)
                 FROM dept_field_num
                GROUP BY did)
    SELECT d.name                                   AS "學生系所",
           cf.name                                  AS "課程領域",
           IFNULL(dfn.fn, 0)                        AS "人次",
           IFNULL(CAST(dfn.fn AS REAL) / dfs.fs, 0) AS "占比"
      FROM department AS d
           CROSS JOIN curriculum_field AS cf
           LEFT JOIN dept_field_num AS dfn
           ON dfn.did = d.id AND dfn.fid = cf.id
           LEFT JOIN dept_field_sum AS dfs
           ON dfs.did = d.id;
    ``` -->

    | 學生系所       | 課程領域 | 人次 | 占比                |
    | -------------- |:--------:| ---- | ------------------- |
    | 數學系         | 理論數學 | 7    | 0.21212121212121213 |
    | 數學系         | 基礎知識 | 12   | 0.36363636363636365 |
    | 數學系         | 人工智慧 | 4    | 0.12121212121212122 |
    | 數學系         | 財務工程 | 3    | 0.09090909090909091 |
    | 數學系         | 統計推論 | 3    | 0.09090909090909091 |
    | 數學系         | 統計推論 | 0    | 0                   |
    | 數學系         | 人文思想 | 4    | 0.12121212121212122 |
    | 數學系         | 資料科學 | 0    | 0                   |
    | 資訊工程系     | 理論數學 | 1    | 0.07142857142857142 |
    | 資訊工程系     | 基礎知識 | 5    | 0.35714285714285715 |
    | 資訊工程系     | 人工智慧 | 3    | 0.21428571428571427 |
    | 資訊工程系     | 財務工程 | 1    | 0.07142857142857142 |
    | 資訊工程系     | 統計推論 | 1    | 0.07142857142857142 |
    | 資訊工程系     | 統計推論 | 0    | 0                   |
    | 資訊工程系     | 人文思想 | 2    | 0.14285714285714285 |
    | 資訊工程系     | 資料科學 | 1    | 0.07142857142857142 |
    | 資訊工程研究所 | 理論數學 | 0    | 0                   |
    | 資訊工程研究所 | 基礎知識 | 3    | 0.1111111111111111  |
    | 資訊工程研究所 | 人工智慧 | 6    | 0.2222222222222222  |
    | 資訊工程研究所 | 財務工程 | 5    | 0.18518518518518517 |
    | 資訊工程研究所 | 統計推論 | 5    | 0.18518518518518517 |
    | 資訊工程研究所 | 統計推論 | 0    | 0                   |
    | 資訊工程研究所 | 人文思想 | 4    | 0.14814814814814814 |
    | 資訊工程研究所 | 資料科學 | 4    | 0.14814814814814814 |
    | 資訊管理系     | 理論數學 | 0    | 0                   |
    | 資訊管理系     | 基礎知識 | 1    | 0.5                 |
    | 資訊管理系     | 人工智慧 | 0    | 0                   |
    | 資訊管理系     | 財務工程 | 0    | 0                   |
    | 資訊管理系     | 統計推論 | 0    | 0                   |
    | 資訊管理系     | 統計推論 | 0    | 0                   |
    | 資訊管理系     | 人文思想 | 1    | 0.5                 |
    | 資訊管理系     | 資料科學 | 0    | 0                   |
    | 數學系碩士班   | 理論數學 | 0    | 0                   |
    | 數學系碩士班   | 基礎知識 | 0    | 0                   |
    | 數學系碩士班   | 人工智慧 | 2    | 0.25                |
    | 數學系碩士班   | 財務工程 | 2    | 0.25                |
    | 數學系碩士班   | 統計推論 | 2    | 0.25                |
    | 數學系碩士班   | 統計推論 | 0    | 0                   |
    | 數學系碩士班   | 人文思想 | 0    | 0                   |
    | 數學系碩士班   | 資料科學 | 2    | 0.25                |

5. 請列出教學評量平均分數及總分，產出欄位範例如下：
    | 課名 | 授課教師 | 教學評量總分 | 教學評量平均分數 |
    | ---- | ------ | ---------- | ------------- |
    ```sql
    SELECT c.name                                         AS "課名",
           GROUP_CONCAT(DISTINCT t.name)                  AS "授課教師",
           SUM(tk.feedback_rank) / COUNT(DISTINCT t.name) AS "教學評量總分",
           AVG(tk.feedback_rank)                          AS "教學評量平均分數"
      FROM course AS c
           LEFT JOIN teaching AS tchg
           ON c.no = tchg.course_no
           LEFT JOIN teacher AS t
           ON t.id = tchg.teacher_id
           LEFT JOIN taking AS tk
           ON c.no = tk.course_no
     GROUP BY c.no, c.name;
    ```
    <!-- ```sql=
    SELECT c.name                                         AS "課名",
           GROUP_CONCAT(DISTINCT t.name)                  AS "授課教師",
           SUM(tk.feedback_rank) / COUNT(DISTINCT t.name) AS "教學評量總分",
           AVG(tk.feedback_rank)                          AS "教學評量平均分數"
      FROM course AS c
           LEFT JOIN teaching AS tchg
           ON c.semester = tchg.semester AND c.no = tchg.course_no
           LEFT JOIN teacher AS t
           ON t.id = tchg.teacher_id
           LEFT JOIN taking AS tk
           ON c.semester = tk.semester AND c.no = tk.course_no
     GROUP BY c.semester, c.no, c.name;
    ``` -->

    ---

    | 課名       | 授課教師  | 教學評量總分 | 教學評量平均分數   |
    | ---------- |:---------:| ------------ | ------------------ |
    | 微積分     |   岳飛    | 17           | 2.8333333333333335 |
    | 計算機概論 |   陸羽    | 24           | 4                  |
    | 統計學習   | 劉邦,項羽 | 34           | 3.7777777777777777 |
    | 經濟學     |   孔丘    | 19           | 3.8                |
    | 統計學     |   莊周    | 17           | 2.8333333333333335 |
    | 音樂欣賞   |   巴哈    | 38           | 4.222222222222222  |
    | 演算法     |  達文西   | 16           | 3.2                |

<!--
## 4. 實作資料庫正規化
1. department

    | id  | name    | degree_level |
    | --- | ------- | ------------ |
    | 0   | 數學系     | 0            |
    | 1   | 資訊工程系   | 0            |
    | 2   | 資訊工程研究所 | 1            |
    | 3   | 資訊管理系   | 0            |
    | 4   | 數學系碩士班  | 1            |

---

2. class

    | dept_id | grade | id  |
    | ------- | ----- | --- |
    | 0       | 1     | A   |
    | 1       | 1     | A   |
    | 2       | 1     | A   |
    | 3       | 1     | A   |
    | 4       | 1     | A   |

---

3. student

    | id  | name | status | dept_id | grade | class_id |
    | --- | ---- | ------ | ------- | ----- | -------- |
    | 0   | 張飛   | 0      | 0       | 1     | A        |
    | 1   | 孫尚香  | 1      | 0       | 1     | A        |
    | 2   | 周瑜   | 0      | 0       | 1     | A        |
    | 3   | 黃蓋   | 0      | 0       | 1     | A        |
    | 4   | 趙雲   | 0      | 0       | 1     | A        |
    | 5   | 關興   | 0      | 0       | 1     | A        |
    | 6   | 夏侯惇  | 0      | 0       | 1     | A        |
    | 7   | 龐統   | 1      | 1       | 1     | A        |
    | 8   | 關羽   | 0      | 1       | 1     | A        |
    | 9   | 華雄   | 2      | 2       | 1     | A        |
    | 10  | 華陀   | 0      | 2       | 1     | A        |
    | 11  | 劉備   | 0      | 3       | 1     | A        |
    | 12  | 呂布   | 0      | 2       | 1     | A        |
    | 13  | 諸葛亮  | 0      | 2       | 1     | A        |
    | 14  | 呂蒙   | 0      | 2       | 1     | A        |
    | 15  | 圖靈   | 0      | 4       | 1     | A        |
    | 16  | 巴斯卡  | 0      | 4       | 1     | A        |
    | 17  | 大喬   | 0      | 1       | 1     | A        |
    | 18  | 甘寧   | 0      | 1       | 1     | A        |
    | 19  | 司馬昭  | 0      | 1       | 1     | A        |
    | 20  | 馬超   | 0      | 1       | 1     | A        |
    | 21  | 郭嘉   | 0      | 2       | 1     | A        |

---

4. building

    | id  | name |
    | --- | ---- |
    | 0   | 工程一館 |
    | 1   | 工程五館 |
    | 2   | 鴻經館  |
    | 3   | 管理二館 |
    | 4   | 綜教館  |

---

5. room

    | building_id | id     |
    | ----------- | ------ |
    | 0           | K205   |
    | 1           | L102   |
    | 2           | M-605  |
    | 3           | I1-018 |
    | 3           | I1-304 |
    | 4           | O-214  |
    | 0           | K210   |

---

6. course

    | semester | no    | name  | type | building_id | room_id | credit | limitation | status |
    | -------- | ----- | ----- | ---- | ----------- | ------- | ------ | ---------- | ------ |
    | 1112     | A0001 | 微積分   | 0    | 0           | K210    | 2      | 50         | 0      |
    | 1112     | A0002 | 計算機概論 | 0    | 1           | L102    | 3      | 50         | 0      |
    | 1112     | A0003 | 統計學習  | 1    | 2           | M-605   | 3      | 50         | 0      |
    | 1112     | A0004 | 經濟學   | 0    | 3           | I1-018  | 3      | 50         | 0      |
    | 1112     | A0005 | 統計學   | 1    | 3           | I1-304  | 3      | 50         | 0      |
    | 1112     | A0006 | 音樂欣賞  | 1    | 4           | O-214   | 2      | 100        | 0      |
    | 1112     | A0007 | 演算法   | 1    | 1           | L102    | 3      | 50         | 0      |

---

7. selection

    | student_id | semester | course_no | id  | result |
    | ---------- | -------- | --------- | --- | ------ |
    | 0          | 1112     | A0001     | 0   | 1      |
    | 1          | 1112     | A0001     | 0   | 1      |
    | 2          | 1112     | A0001     | 0   | 1      |
    | 3          | 1112     | A0001     | 0   | 1      |
    | 4          | 1112     | A0001     | 0   | 1      |
    | 5          | 1112     | A0001     | 0   | 1      |
    | 6          | 1112     | A0001     | 0   | 1      |
    | 7          | 1112     | A0001     | 0   | 1      |
    | 8          | 1112     | A0002     | 0   | 1      |
    | 7          | 1112     | A0002     | 0   | 1      |
    | 2          | 1112     | A0002     | 0   | 1      |
    | 3          | 1112     | A0002     | 0   | 1      |
    | 4          | 1112     | A0002     | 0   | 1      |
    | 6          | 1112     | A0002     | 0   | 1      |
    | 9          | 1112     | A0002     | 0   | 1      |
    | 10         | 1112     | A0002     | 0   | 1      |
    | 7          | 1112     | A0003     | 0   | 1      |
    | 0          | 1112     | A0003     | 0   | 1      |
    | 3          | 1112     | A0003     | 0   | 1      |
    | 5          | 1112     | A0003     | 0   | 1      |
    | 11         | 1112     | A0003     | 0   | 0      |
    | 9          | 1112     | A0003     | 0   | 1      |
    | 12         | 1112     | A0003     | 0   | 1      |
    | 10         | 1112     | A0003     | 0   | 1      |
    | 13         | 1112     | A0003     | 0   | 1      |
    | 14         | 1112     | A0003     | 0   | 1      |
    | 15         | 1112     | A0003     | 0   | 1      |
    | 16         | 1112     | A0003     | 0   | 2      |
    | 7          | 1112     | A0004     | 0   | 0      |
    | 0          | 1112     | A0004     | 0   | 1      |
    | 1          | 1112     | A0004     | 0   | 1      |
    | 3          | 1112     | A0004     | 0   | 1      |
    | 4          | 1112     | A0004     | 0   | 1      |
    | 6          | 1112     | A0004     | 0   | 1      |
    | 12         | 1112     | A0004     | 0   | 1      |
    | 8          | 1112     | A0005     | 0   | 1      |
    | 17         | 1112     | A0005     | 0   | 1      |
    | 18         | 1112     | A0005     | 0   | 1      |
    | 1          | 1112     | A0005     | 0   | 1      |
    | 2          | 1112     | A0005     | 0   | 1      |
    | 3          | 1112     | A0005     | 0   | 1      |
    | 11         | 1112     | A0005     | 0   | 1      |
    | 8          | 1112     | A0006     | 0   | 1      |
    | 7          | 1112     | A0006     | 0   | 1      |
    | 19         | 1112     | A0006     | 0   | 0      |
    | 20         | 1112     | A0006     | 0   | 0      |
    | 0          | 1112     | A0006     | 0   | 1      |
    | 1          | 1112     | A0006     | 0   | 0      |
    | 2          | 1112     | A0006     | 0   | 0      |
    | 3          | 1112     | A0006     | 0   | 1      |
    | 5          | 1112     | A0006     | 0   | 1      |
    | 6          | 1112     | A0006     | 0   | 1      |
    | 11         | 1112     | A0006     | 0   | 1      |
    | 9          | 1112     | A0006     | 0   | 1      |
    | 10         | 1112     | A0006     | 0   | 1      |
    | 13         | 1112     | A0006     | 0   | 1      |
    | 14         | 1112     | A0006     | 0   | 1      |
    | 0          | 1112     | A0007     | 0   | 0      |
    | 9          | 1112     | A0007     | 0   | 1      |
    | 12         | 1112     | A0007     | 0   | 1      |
    | 21         | 1112     | A0007     | 0   | 1      |
    | 10         | 1112     | A0007     | 0   | 1      |
    | 7          | 1112     | A0007     | 0   | 1      |
    | 15         | 1112     | A0007     | 0   | 1      |
    | 16         | 1112     | A0007     | 0   | 1      |

---

8. teacher

    | id  | name |
    | --- | ---- |
    | 0   | 岳飛   |
    | 1   | 陸羽   |
    | 2   | 劉邦   |
    | 3   | 項羽   |
    | 4   | 孔丘   |
    | 5   | 莊周   |
    | 6   | 巴哈   |
    | 7   | 達文西  |

---

9. taking

    | student_id | semester | course_no | selection_id | score | feedback_rank |
    | ---------- | -------- | --------- | ------------ | ----- | ------------- |
    | 0          | 1112     | A0001     | 0            | 77.7  | 1             |
    | 1          | 1112     | A0001     | 0            |       |               |
    | 2          | 1112     | A0001     | 0            | 56    | 2             |
    | 3          | 1112     | A0001     | 0            | 34    | 3             |
    | 4          | 1112     | A0001     | 0            | 98    | 4             |
    | 5          | 1112     | A0001     | 0            | 55    | 5             |
    | 6          | 1112     | A0001     | 0            | 67    | 2             |
    | 7          | 1112     | A0001     | 0            |       |               |
    | 8          | 1112     | A0002     | 0            | 66    | 3             |
    | 7          | 1112     | A0002     | 0            |       |               |
    | 2          | 1112     | A0002     | 0            | 93    | 4             |
    | 3          | 1112     | A0002     | 0            | 44    | 4             |
    | 4          | 1112     | A0002     | 0            | 49    | 5             |
    | 6          | 1112     | A0002     | 0            | 78    | 3             |
    | 9          | 1112     | A0002     | 0            |       |               |
    | 10         | 1112     | A0002     | 0            | 74    | 5             |
    | 7          | 1112     | A0003     | 0            |       |               |
    | 0          | 1112     | A0003     | 0            | 46    | 5             |
    | 3          | 1112     | A0003     | 0            | 76    | 4             |
    | 5          | 1112     | A0003     | 0            | 87    | 5             |
    | 9          | 1112     | A0003     | 0            |       |               |
    | 12         | 1112     | A0003     | 0            | 76    | 4             |
    | 10         | 1112     | A0003     | 0            | 80    | 5             |
    | 13         | 1112     | A0003     | 0            | 78    | 3             |
    | 14         | 1112     | A0003     | 0            | 65    | 4             |
    | 15         | 1112     | A0003     | 0            | 99    | 3             |
    | 16         | 1112     | A0003     | 0            | 69    | 1             |
    | 0          | 1112     | A0004     | 0            | 56    | 3             |
    | 1          | 1112     | A0004     | 0            |       |               |
    | 3          | 1112     | A0004     | 0            | 67.5  | 5             |
    | 4          | 1112     | A0004     | 0            | 78    | 4             |
    | 6          | 1112     | A0004     | 0            | 89    | 2             |
    | 12         | 1112     | A0004     | 0            | 45    | 5             |
    | 8          | 1112     | A0005     | 0            | 68.7  | 1             |
    | 17         | 1112     | A0005     | 0            | 63    | 4             |
    | 18         | 1112     | A0005     | 0            | 46    | 5             |
    | 1          | 1112     | A0005     | 0            |       |               |
    | 2          | 1112     | A0005     | 0            | 78    | 2             |
    | 3          | 1112     | A0005     | 0            | 87    | 3             |
    | 11         | 1112     | A0005     | 0            | 96    | 2             |
    | 8          | 1112     | A0006     | 0            | 76    | 4             |
    | 7          | 1112     | A0006     | 0            |       |               |
    | 0          | 1112     | A0006     | 0            | 34    | 3             |
    | 3          | 1112     | A0006     | 0            | 80    | 4             |
    | 5          | 1112     | A0006     | 0            | 62    | 5             |
    | 6          | 1112     | A0006     | 0            | 60    | 3             |
    | 11         | 1112     | A0006     | 0            | 56    | 5             |
    | 9          | 1112     | A0006     | 0            |       |               |
    | 10         | 1112     | A0006     | 0            | 98    | 4             |
    | 13         | 1112     | A0006     | 0            | 55    | 5             |
    | 14         | 1112     | A0006     | 0            | 78    | 5             |
    | 9          | 1112     | A0007     | 0            |       |               |
    | 12         | 1112     | A0007     | 0            | 79    | 5             |
    | 21         | 1112     | A0007     | 0            | 87    | 4             |
    | 10         | 1112     | A0007     | 0            | 68    | 3             |
    | 7          | 1112     | A0007     | 0            |       |               |
    | 15         | 1112     | A0007     | 0            | 99    | 3             |
    | 16         | 1112     | A0007     | 0            | 69    | 1             |

---

10. teaching

    | teacher_id | semester | course_no |
    | ---------- | -------- | --------- |
    | 0          | 1112     | A0001     |
    | 1          | 1112     | A0002     |
    | 2          | 1112     | A0003     |
    | 3          | 1112     | A0003     |
    | 4          | 1112     | A0004     |
    | 5          | 1112     | A0005     |
    | 6          | 1112     | A0006     |
    | 7          | 1112     | A0007     |

---

11. course_timetable

    | semester | course_no | weekday_code | session_code |
    | -------- | --------- | ------------ | ------------ |
    | 1112     | A0001     | 1            | 6            |
    | 1112     | A0001     | 1            | 7            |
    | 1112     | A0001     | 1            | 8            |
    | 1112     | A0002     | 2            | 3            |
    | 1112     | A0002     | 2            | 4            |
    | 1112     | A0002     | 5            | 4            |
    | 1112     | A0003     | 4            | 6            |
    | 1112     | A0003     | 4            | 7            |
    | 1112     | A0003     | 4            | 8            |
    | 1112     | A0004     | 4            | 6            |
    | 1112     | A0004     | 4            | 7            |
    | 1112     | A0004     | 4            | 8            |
    | 1112     | A0005     | 5            | 2            |
    | 1112     | A0005     | 5            | 3            |
    | 1112     | A0005     | 5            | 4            |
    | 1112     | A0006     | 3            | 6            |
    | 1112     | A0006     | 3            | 7            |
    | 1112     | A0007     | 3            | 2            |
    | 1112     | A0007     | 3            | 3            |
    | 1112     | A0007     | 3            | 4            |

---

12. curriculum_field

    | id  | name |
    | --- | ---- |
    | 0   | 理論數學 |
    | 1   | 基礎知識 |
    | 2   | 人工智慧 |
    | 3   | 財務工程 |
    | 4   | 統計推論 |
    | 5   | 統計推論 |
    | 6   | 人文思想 |
    | 7   | 資料科學 |

---

13. field_of_course

    | semester | course_no | field_id |
    | -------- | --------- | -------- |
    | 1112     | A0001     | 0        |
    | 1112     | A0002     | 1        |
    | 1112     | A0002     | 2        |
    | 1112     | A0003     | 3        |
    | 1112     | A0003     | 4        |
    | 1112     | A0004     | 1        |
    | 1112     | A0005     | 1        |
    | 1112     | A0006     | 6        |
    | 1112     | A0007     | 2        |
    | 1112     | A0007     | 7        |
-->

<!-- ## 附錄：原始碼 -->
## 4. 實作資料庫正規化（原始碼）
<!-- View and run on <a href="https://www.db-fiddle.com/f/sH5GYyzPSxkxuzhEpPwAA8/9">DB Fiddle</a>, or only view in the two fields below. -->

<!-- {%hackmd c_cleg88R66erkaw3x35vg %} -->

### create_tables.sql
:::info
```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
```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);
```
:::