學校選課系統的關聯式資料庫設計
本文以學校選課系統為例，從一個初始需求的資料表出發，一步步完成資料庫正規化的過程。同時，也會解析如何處理一對一、一對多、多對多等關係，會更明白關聯式資料庫設計的核心概念與應用技巧。
正規化類型
資料庫正規化
- 第一正規化(1NF): 每個欄位必須具有原子性，即單一值，不能存放多值或重複資料。
- 第二正規化(2NF): 滿足 1NF 下，確保每個欄位都只和主鍵完全相關。
- 第三正規化(3NF): 滿足 2NF 下，確保非主鍵欄位不依賴於其他非主鍵欄位。
關係類型
- 一對一(1:1): 通常可以合併在一個表中。若資訊過多，也可以依主鍵拆分不同表格。
- 一對多(1:N): 在「多」的一方加入外鍵，用「一」的一方為主鍵。
- 多對多(M:N): 使用中間表(關聯表)拆分成兩個一對多的關係，中間表通常包含兩邊的主鍵作為外鍵。
案例介紹
以下我將以一個學校選課系統為例，說明資料表從初始需求的資料表到正規化的過程。
↓初始需求的資料表↓
| 學生ID | 學生姓名 | 聯繫方式 | 課程ID | 課程名稱 | 學分 | 教師姓名 | 辦公室 |
|---|---|---|---|---|---|---|---|
| S001 | Stella | 0981123456 | C101, C102 | 數學, 機器學習 | 3 | 李老師 | 教學樓101 |
| S004 | Joe | 0983123456 | C103 | 英語 | 2 | 王老師 | 教學樓102 |
| S002 | Tom | 0986123456 | C101 | 數學 | 3 | 李老師 | 教學樓101 |
| S003 | Lily | 0989123456 | C104 | 物理 | 4 | 張老師 | 教學樓103 |
正規化過程
第一正規化(1NF)
第一正規化(1NF): 每個欄位必須具有原子性，即單一值，不能存放多值或重複資料。
對於原始資料表中的「課程ID」和「課程名稱」多值情況，我們需要拆分為多行以滿足 1NF。
↓更新的資料表↓
| 學生ID | 學生姓名 | 聯繫方式 | 課程ID | 課程名稱 | 學分 | 教師姓名 | 辦公室 |
|---|---|---|---|---|---|---|---|
| S001 | Stella | 0981123456 | C101 | 數學 | 3 | 李老師 | 教學樓101 |
| S001 | Stella | 0981123456 | C102 | 機器學習 | 3 | 李老師 | 教學樓101 |
| S004 | Joe | 0983123456 | C103 | 英語 | 2 | 王老師 | 教學樓102 |
| S002 | Tom | 0986123456 | C101 | 數學 | 3 | 李老師 | 教學樓101 |
| S003 | Lily | 0989123456 | C104 | 物理 | 4 | 張老師 | 教學樓103 |
第二正規化(2NF)
第二正規化(2NF): 滿足 1NF 下，確保每個欄位都只和主鍵完全相關。
在原始資料表中:
- 「學生姓名」和「聯繫方式」欄位，僅與「學生ID」相關。
- 「課程名稱」和「學分」欄位，則與「課程ID」相關。
- 我們通過拆分表格，使每個非主鍵欄位只與主鍵完全相關。
↓學生表 (Students)↓
| 學生ID | 學生姓名 | 聯繫方式 |
|---|---|---|
| S001 | Stella | 0981123456 |
| S004 | Joe | 0983123456 |
| S002 | Tom | 0986123456 |
| S003 | Lily | 0989123456 |
↓課程表 (Courses)↓
| 課程ID | 課程名稱 | 學分 |
|---|---|---|
| C101 | 數學 | 3 |
| C102 | 機器學習 | 3 |
| C103 | 英語 | 2 |
| C104 | 物理 | 4 |
↓教師表 (Teachers)↓
| 教師姓名 | 辦公室 |
|---|---|
| 李老師 | 教學樓101 |
| 王老師 | 教學樓102 |
| 張老師 | 教學樓103 |
第三正規化(3NF)
第三正規化(3NF): 滿足 2NF 下，確保非主鍵欄位不依賴於其他非主鍵欄位。
若「辦公室」直接依賴於「教師姓名」…
↓教師表 (Teachers)↓
| 教師姓名 | 辦公室 |
|---|---|
| 李老師 | 教學樓101 |
| 王老師 | 教學樓102 |
| 張老師 | 教學樓103 |
當多個課程由同一教師授課時，對應的「辦公室」資訊會在每條記錄中重複儲存。
| 課程ID | 課程名稱 | 學分 | 教師姓名 | 辦公室 |
|---|---|---|---|---|
| C101 | 數學 | 3 | 李老師 | 教學樓101 |
| C102 | 機器學習 | 3 | 李老師 | 教學樓101 |
那我們要怎麼簡化資料儲存呢? 我們需要分離非主鍵欄位「辦公室」與「教師姓名」之間的依賴關係，是透過加入唯一識別碼(教師ID)。
- 以
教師ID作為主鍵，提供每位教師的唯一識別碼。 - 將教師的描述性資訊（如教師姓名和辦公室）儲存在與
教師ID相關的記錄中。
↓更新的教師表 (Teachers)↓
| 教師ID | 教師姓名 | 辦公室 |
|---|---|---|
| T001 | 李老師 | 教學樓101 |
| T002 | 王老師 | 教學樓102 |
| T003 | 張老師 | 教學樓103 |
這樣當多個課程由同一教師授課時，對應的「辦公室」資訊就不需在每條記錄中重複儲存了。
| 課程ID | 課程名稱 | 學分 | 教師ID |
|---|---|---|---|
| C101 | 數學 | 3 | T001 |
| C102 | 機器學習 | 3 | T001 |
關係處理過程
一對一(1:1)
- 通常可以合併在一個表中。
- 若資訊過多，也可以依主鍵拆分不同表格。
↓學生表 (Students)↓
| 學生ID | 學生姓名 | 聯繫方式 |
|---|---|---|
| S001 | Stella | 0981123456 |
| S004 | Joe | 0983123456 |
| S002 | Tom | 0986123456 |
| S003 | Lily | 0989123456 |
↓學生詳細資訊表 (Student_Details)↓
| 學生ID | 出生日期 | 居住地 | 入學日期 |
|---|---|---|---|
| S001 | 2000-01-01 | 海奧華 | 2020-09-01 |
| S004 | 2000-02-01 | 天狼星 | 2020-09-02 |
| S002 | 2000-03-01 | 尼比魯 | 2020-09-04 |
| S003 | 2000-04-01 | 阿爾法 | 2020-09-05 |
一對多(1:N)
在「多」的一方加入外鍵，用「一」的一方為主鍵。
思考方向:
- 以「課程」角度:
1個課程會有1個教師。 - 以「教師」角度:
1個教師會有多個課程。 - 所以以「教師ID」為外鍵(FK)，以「課程ID」為主鍵(PK)。
可以這樣記: 能者多勞
教師會面對多個課程，所以教師是對外的業務窗口。
所以讓 教師ID 進入到課程表中成為外鍵(FK)。
↓教師表 (Teachers)↓
| 教師ID | 教師姓名 | 辦公室 |
|---|---|---|
| T001 | 李老師 | 教學樓101 |
| T002 | 王老師 | 教學樓102 |
| T003 | 張老師 | 教學樓103 |
↓課程表 (Courses)↓
| 課程ID | 課程名稱 | 學分 |
|---|---|---|
| C101 | 數學 | 3 |
| C102 | 機器學習 | 3 |
| C103 | 英語 | 2 |
| C104 | 物理 | 4 |
↓更新的課程表 (Courses)↓
| 課程ID | 課程名稱 | 學分 | 教師ID |
|---|---|---|---|
| C101 | 數學 | 3 | T001 |
| C102 | 機器學習 | 3 | T001 |
| C103 | 英語 | 2 | T002 |
| C104 | 物理 | 4 | T003 |
多對多(M:N)
使用中間表(關聯表)拆分成兩個一對多的關係，中間表通常包含兩邊的主鍵作為外鍵。
↓學生選課表 (Student_Courses)↓
- 學生與課程之間是多對多關係。
- 通過學生選課表 (Student_Courses) 作為中間表，將其轉化為兩個一對多關係。
學生ID和課程ID組合可以作為主鍵，用於唯一標識每條記錄。
| 學生ID | 課程ID |
|---|---|
| S001 | C101 |
| S001 | C102 |
| S004 | C103 |
| S002 | C101 |
| S003 | C104 |
總結
我將學校選課系統繪製成視覺化的資料庫設計圖，呈現實體(如學生、教師、課程)及其彼此之間的關係。
- 一對一:
Students與Student_Details的連結，將學生的基本資訊與詳細資料分開管理。 - 一對多:
Teachers與Courses的連結，表示每位教師可授課多門課程的關係。 - 多對多:
Students與Courses透過中間表Student_Courses連結，表示每位學生可選多門課程，且每個課程也可有多位學生的關係。
