資料庫設計/規範化

規範化應該是資料庫設計過程的一部分。然而，很難將規範化過程與 ER 建模過程分離，因此這兩種技術應該同時使用。

使用實體關係圖 (ERD) 來提供組織資料需求和操作的大圖或宏觀檢視。這可以透過一個迭代過程建立，該過程涉及識別相關實體、它們的屬性及其關係。

規範化過程側重於特定實體的特徵，並代表 ERD 中實體的微觀檢視。

什麼是規範化？

規範化是關係理論的一個分支，它提供了設計見解。它是確定表中存在多少冗餘的過程。規範化的目標是

• 能夠描述關係模式的冗餘程度
• 提供用於轉換模式以消除冗餘的機制

規範化理論很大程度上借鑑了函式依賴理論。規範化理論定義了六種正規化 (NF)。每種正規化都涉及模式必須滿足的一組依賴屬性，並且每種正規化都對更新異常的存在或不存在提供保證。這意味著更高的正規化具有更少的冗餘，因此更新問題更少。

正規化

資料庫中的所有表都可以處於我們將要討論的正規化之一。理想情況下，我們只希望 PK 到 FK 的冗餘最小。所有其他內容都應該從其他表中推匯出來。有六種正規化，但我們只關注前四種，它們是

• 第一正規化 (1NF)
• 第二正規化 (2NF)
• 第三正規化 (3NF)
• Boyce-Codd 正規化 (BCNF)

BCNF 很少使用。

第一正規化 (1NF)

在第一正規化中，在每行和每列的交點處只允許單個值；因此，沒有重複組。

要規範化包含重複組的關係，請刪除重複組並形成兩個新的關係。

新關係的 PK 是原始關係的 PK 與新建立關係的屬性的組合，用於唯一標識。

1NF 的過程

我們將使用來自學校資料庫的下面的 Student_Grade_Report 表作為我們的示例來解釋 1NF 的過程。

Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

• 在 Student Grade Report 表中，重複組是課程資訊。一個學生可以修很多門課。
• 刪除重複組。在本例中，它是每個學生的課程資訊。
• 為新表識別 PK。
• PK 必須唯一地識別屬性值 (StudentNo 和 CourseNo)。
• 刪除與課程和學生相關的所有屬性後，您將剩下學生課程表 (StudentCourse)。
• 學生表 (Student) 現在處於第一正規化，重複組已刪除。
• 下面顯示了兩個新表。

Student (StudentNo, StudentName, Major)

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

如何更新 1NF 異常

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

• 要新增新課程，我們需要一個學生。
• 當需要更新課程資訊時，我們可能會出現不一致。
• 要刪除一個學生，我們也可能會刪除有關課程的關鍵資訊。

第二正規化 (2NF)

對於第二正規化，關係必須首先處於 1NF。當且僅當 PK 包含單個屬性時，關係才會自動處於 2NF。

如果關係具有複合 PK，則每個非鍵屬性必須完全依賴於整個 PK 而不是 PK 的子集（即，必須不存在部分依賴或增補）。

2NF 的過程

要轉到 2NF，表必須首先處於 1NF。

• Student 表已經處於 2NF，因為它具有單列 PK。
• 檢查 Student Course 表時，我們看到並非所有屬性都完全依賴於 PK；具體來說，所有課程資訊。唯一完全依賴的屬性是成績。
• 識別包含課程資訊的新表。
• 為新表識別 PK。
• 下面顯示了三個新表。

Student (StudentNo, StudentName, Major)

CourseGrade (StudentNo, CourseNo, Grade)

CourseInstructor (CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation)

如何更新 2NF 異常

• 新增新講師時，我們需要一門課程。
• 更新課程資訊可能會導致講師資訊的不一致。
• 刪除課程也可能會刪除講師資訊。

第三正規化 (3NF)

要處於第三正規化，關係必須處於第二正規化。此外，所有傳遞依賴必須被刪除；非鍵屬性不得函式依賴於另一個非鍵屬性。

3NF 的過程

• 從每個具有傳遞關係的表中消除所有依賴於傳遞關係的屬性。
• 使用已刪除的依賴項建立新的表。
• 檢查新表以及已修改的表以確保每個表都有一個決定因素，並且沒有表包含不合適的依賴項。
• 請參見下面的四個新表。

Student (StudentNo, StudentName, Major)

CourseGrade (StudentNo, CourseNo, Grade)

Course (CourseNo, CourseName, InstructorNo)

Instructor (InstructorNo, InstructorName, InstructorLocation)

在此階段，第三正規化中不應該有任何異常。讓我們看一下此示例的依賴關係圖（圖 12.1）。第一步是刪除重複組，如上所述。

Student (StudentNo, StudentName, Major)

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

為了回顧學校資料庫的規範化過程，請檢視圖 12.1 中顯示的依賴關係。

圖 12.1 依賴關係圖，由 A. Watt 提供。

圖 12.1 中使用的縮略語如下

• PD：部分依賴項
• TD：傳遞依賴項
• FD：完全依賴項（注意：FD 通常代表函式依賴項。在圖 12.1 中使用 FD 作為完全依賴項的縮寫只在圖 12.1 中使用。）
  

Boyce-Codd 正規化 (BCNF)

當一個表有多個候選鍵時，即使關係處於 3NF，也可能導致異常。*Boyce-Codd 正規化*是 3NF 的一種特殊情況。當且僅當每個決定因素都是候選鍵時，關係才處於 BCNF。

BCNF 示例 1

考慮以下表 (St_Maj_Adv)。

學生 ID	專業	導師
111	物理	史密斯
111	音樂	陳
320	數學	多布斯
671	物理	懷特
803	物理	史密斯

此表的*語義規則*（應用於資料庫的業務規則）為

1. 每個學生可以主修多個科目。
2. 對於每個專業，給定學生只有一個導師。
3. 每個專業有多個導師。
4. 每個導師只指導一個專業。
5. 每個導師指導一個專業中的多個學生。

此表的函式依賴關係列在下面。第一個是候選鍵；第二個不是。

1. 學生 ID, 專業 ——>  導師
2. 導師  ——>  專業

此表的異常包括

1. 刪除 - 學生刪除導師資訊
2. 插入 - 新導師需要學生
3. 更新 - 不一致

注意：沒有單個屬性是候選鍵。

PK 可以是學生 ID, 專業 或 學生 ID, 導師。

要將 St_Maj_Adv 關係簡化為 BCNF，您需要建立兩個新表

1. St_Adv (學生 ID, 導師)
2. Adv_Maj (導師, 專業)

St_Adv 表                                                                    

學生 ID	導師
111	史密斯
111	陳
320	多布斯
671	懷特
803	史密斯

Adv_Maj 表

導師	專業
史密斯	物理
陳	音樂
多布斯	數學
懷特	物理

BCNF 示例 2

考慮以下表 (Client_Interview)。

客戶編號	面試日期	面試時間	員工編號	房間編號
CR76	02 年 5 月 13 日	10.30	SG5	G101
CR56	02 年 5 月 13 日	12.00	SG5	G101
CR74	02 年 5 月 13 日	12.00	SG37	G102
CR56	02 年 7 月 1 日	10.30	SG5	G102

FD1 – 客戶編號, 面試日期 –> 面試時間, 員工編號, 房間編號  (PK)

FD2 – 員工編號, 面試日期, 面試時間 –> 客戶編號      (候選鍵：CK)

FD3 – 房間編號, 面試日期, 面試時間 –> 員工編號, 客戶編號    (CK)

FD4 – 員工編號, 面試日期 –> 房間編號

當且僅當每個決定因素都是候選鍵時，關係才處於 BCNF。我們需要建立一個包含前三個 FD 的表 (Client_Interview2 表) 和另一個用於第四個 FD 的表 (StaffRoom 表)。

Client_Interview2 表

客戶編號	面試日期	面試時間	員工編號
CR76	02 年 5 月 13 日	10.30	SG5
CR56	02 年 5 月 13 日	12.00	SG5
CR74	02 年 5 月 13 日	12.00	SG37
CR56	02 年 7 月 1 日	10.30	SG5

StaffRoom 表

員工編號	面試日期	房間編號
SG5	02 年 5 月 13 日	G101
SG37	02 年 5 月 13 日	G102
SG5	02 年 7 月 1 日	G102

規範化和資料庫設計

在資料庫設計的規範化過程中，請確保在建立表結構之前，建議的實體滿足所需的正規化。如果許多現實世界的資料庫在設計時不當，或者在一段時間內被錯誤地修改，就會出現異常。您可能會被要求重新設計和修改現有的資料庫。如果表沒有正確規範化，這可能是一項巨大的任務。

關鍵術語和縮略語

Boyce-Codd 正規化 (BCNF)

  第 3 NF 的一種特殊情況

第一正規化 (1NF)

在每一行和每一列的交點處只允許單個值，因此沒有重複組

規範化

確定表中存在多少冗餘的過程

第二正規化 (2NF)

關係必須處於 1NF，並且 PK 包含單個屬性

語義規則

應用於資料庫的業務規則

第三正規化 (3NF)

關係必須處於 2NF，並且所有傳遞依賴關係必須被刪除；非鍵屬性不能函式依賴於另一個非鍵屬性

練習

在完成第 11 章和第 12 章後，再進行這些練習。

1. 什麼是規範化？
2. 何時表處於 1NF？
3. 何時表處於 2NF？
4. 何時表處於 3NF？
5. 識別和討論圖 12.2 中顯示的依賴關係圖中所示的每個依賴關係。圖 12.2 用於問題 5，由 A. Watt 提供。
6. 為了跟蹤學生和課程，一所新學院使用圖 12.3 中的表結構。繪製此表的依賴關係圖。圖 12.3 用於問題 6，由 A. Watt 提供。
7. 使用剛剛繪製的依賴關係圖，顯示您將建立的表（以其第三正規化形式）來解決遇到的問題。繪製修復後的表的依賴關係圖。
8. 一家名為 Instant Cover 的機構為蘇格蘭的酒店提供兼職/臨時員工。圖 12.4 列出了機構員工在各個酒店工作的時間。國民保險號碼 (NIN) 對於每個員工都是唯一的。使用圖 12.4 回答問題 (a) 和 (b)。圖 12.4 用於問題 8，由 A. Watt 提供。
   1. 此表容易受到更新異常的影響。提供插入、刪除和更新異常的示例。
   2. 將此表規範化為第三正規化。說明所有假設。
9. 填寫空白
   1. ____________________ 產生較低的正規化。
   2. 任何其值決定行內其他值的屬性稱為____________________。
   3. 不能進一步劃分的屬性被稱為顯示____________________。
   4. ____________________ 指的是儲存在錶行中的值所表示的詳細程度。
   5. 關係表不能包含____________________ 組。

另見*附錄 - 示例 ERD 練習*

參考書目

• Nguyen Kim Anh，*關係設計理論*。OpenStax CNX。2009 年 7 月 8 日。2014 年 7 月從 http://cnx.org/contents/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1 檢索

• Russell，Gordon。第 4 章 - 規範化。*資料庫電子學習*。未註明日期。2014 年 7 月從 db.grussell.org/ch4.html 檢索

參考資料

• BCOpenCampus：資料庫設計

← 函式依賴關係

資料庫設計

資料庫開發流程 →