關係型資料庫設計：DBMS-mysql教程-PHP中文網

Relational Database Design: DBMS

關係資料庫設計：綜合指南

關聯式資料庫設計是有效資料庫系統的基石，專注於有效組織數據，同時減少冗餘並保持資料完整性。本文對分解、規範化、函數依賴和鍵進行了徹底的探索，確保您完全理解關聯式資料庫設計原則。

關係資料庫設計中的分解

分解是將一個大的關係（表）分解成更小的、有意義的關係，以消除冗餘、提高一致性並優化效能的過程。這是正常化的關鍵面向。

分解類型

有損分解:
- 如果原始表無法透過連接分解的關係來完美重建，則分解是有損。
- 當分解過程中某些資料或關係遺失時，就會發生這種情況。
- 範例：考慮下表：
EmployeeID | ProjectID | ProjectManager --------------------------------------- E1 | P1 | M1 E2 | P1 | M1
登入後複製
登入後複製
登入後複製
登入後複製
登入後複製
登入後複製
登入後複製
如果將其分解為：
- 表 1：員工 ID |專案ID
- 表 2：專案 ID |專案經理重新連接這些表格可能會導致數據重複或不一致，從而導致有損分解。
無損分解:
- 如果可以透過連接分解的關係來完美地重建原始表，而不會丟失任何數據或引入不一致，那麼分解就是無損。
- 當分解保留所有函數依賴關係或每個分解關係中包含關鍵屬性時，即可實現這一點。

功能依賴

函數依賴 (FD) 描述關係中兩個屬性之間的關係，其中一個屬性（或屬性集）的值決定另一個屬性（或屬性集）的值。它是關係資料庫設計和規範化中的基本概念。

定義：

設 X 和 Y 為關係 R 中的屬性集。函數依賴

X → Y 表示對於 R 中的任兩個元組（行），如果元組在 X 的值上一致，他們也必須就 Y 的值達成一致。

X：行列式（左側的屬性）。
Y：依賴（右邊的屬性）。

例子：

考慮一個儲存學生資料的表格：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

這裡，StudentID → 姓名、專業，因為 StudentID 唯一決定姓名和專業。

函數依賴的屬性：

自反性：若 Y 是 X 的子集，則 X → Y。
增強：如果 X → Y，則 XZ → YZ（向兩側添加屬性保留依賴關係）。
傳遞性：若 X → Y 且 Y → Z，則 X → Z。

關係資料庫中的鍵

鍵對於唯一識別表中的記錄和強制資料完整性至關重要。

鑰匙類型：

超級鍵：
- 一組一個或多個屬性，可以唯一標識關係中的元組。
- 範例：在具有 EmployeeID 和 Name 屬性的表中，{EmployeeID}、{EmployeeID, Name} 是超級鍵。
候選金鑰:
- 最小的超級鍵，表示它的沒有適當的子集也是超級鍵。
- 範例：如果 {EmployeeID} 可以唯一識別一個元組，則它是候選鍵。
主鍵:
- 資料庫設計者選擇的候選鍵，用於唯一標識元組。
- 範例：Employee 表中的 EmployeeID。
外鍵:
- 一個表中的屬性（或一組屬性）引用另一個表中的主鍵，從而在表之間建立關係。
- 範例：員工表中的 DepartmentID 引用部門表中的 DepartmentID。
複合鍵:
- 由兩個或多個屬性組成的主鍵。
- 範例：學生註冊表中的（StudentID、CourseID）。
唯一密鑰：
- 一個鍵約束，確保列（或列組合）中的所有值都是唯一的。

規範化和範式

標準化是組織屬性和關係以減少冗餘和依賴性、確保資料完整性的過程。這是透過逐步滿足連續範式的標準來實現的。

範式（綜合概述）

第一範式 (1NF)

定義:

若關係符合以下條件，則稱為 第一範式 (1NF)：

原子性：所有屬性（列）必須包含原子值。這意味著每列中的值是不可分割的並且不能進一步細分。
單值條目：表中的每一列都應包含單一資料類型的值，且任何列都不應包含集合、列表或陣列。
行的唯一性：每一行必須是唯一的，這表示表格應該有一個主鍵來區分行。
無重複群組：表格不應具有相同屬性的多個欄位（如 Item1、Item2 等），也不應在單一儲存格中儲存多個值。

說明：

原子值：每個單元格中的資料必須採用最簡單的形式。例如，每個項目應該佔據自己的行，而不是在一個單元格中儲存多個項目。
重複組：這是多列或多行代表相同類型資料的地方，使得表不符合 1NF。
主鍵：主鍵確保每一行都是唯一可辨識的，這是關聯式資料庫的基本要求。

範例：

不合規表（不在 1NF 中）：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

Items 列違反了原子性，因為它包含多個值（例如，「筆，筆記本」）。
存在重複的群組，因為項目儲存在單一儲存格中而不是單獨的行中。

合規表（在 1NF 中）：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

這裡，項目列被分解為原子值，每個項目位於單獨的行中。
沒有一個單元格包含多個值，確保原子性。
該表沒有重複的組或數組，使其符合 1NF。

第二範式 (2NF)

定義:

若滿足下列條件，則關係為 第二範式 (2NF)：

它已經處於第一範式（1NF）（即沒有多值或重複組）。
每個非主屬性在功能上完全依賴整個主鍵。

非素數屬性：不屬於任何候選鍵的屬性。
完全功能依賴：非素數屬性必須依賴整個複合主鍵，而不僅僅是其中的一部分。

說明：

當非主屬性只依賴複合主鍵的一部分而不是整個鍵時，就會發生部分依賴。
2NF 透過將關係分解為更小的關係來消除部分依賴，確保非素數屬性僅依賴整個主鍵或另一個候選鍵。

這一步減少了部分依賴造成的冗餘，更好地組織資料。

範例：

不合規表（不在 2NF 中）：

考慮一個儲存學生課程資訊的表格：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

複合主鍵：（StudentID，CourseID）。
部分依賴：
- 教師和部門僅依賴 CourseID，而不依賴整個主鍵（StudentID、CourseID）。

這違反了 2NF，因為非主屬性（講師和部門）部分依賴組合鍵。

相容表（在 2NF 中）：

要刪除部分依賴關係，請將表分解為兩個關係：

學生課表：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

課程詳情表：

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

登入後複製

第三範式 (3NF)

定義:

若滿足下列條件，則關係為 第三範式 (3NF)：

它採用第二範式（2NF）（即沒有部分依賴）。
不存在傳遞依賴，這表示：
- 沒有一個非主屬性依賴另一個非主屬性。
- 非主屬性應該只依賴候選鍵，而不是透過另一個非主屬性。

非素數屬性：不屬於任何候選鍵的屬性。
傳遞依賴：非主屬性透過另一個非主屬性間接依賴候選鍵的依賴。

說明:

在 3NF 中，我們消除傳遞依賴以減少冗餘並提高資料一致性。

傳遞依賴範例：如果 A → B 且 B → C，則 A → C 是傳遞依賴。這意味著 C 透過 B 間接依賴 A。
此類依賴關係會引入冗餘，因為對 B 的變更可能會導致更新 C 時出現異常。

範例：

不合規表（不在 3NF 中）：

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

登入後複製

候選鍵：StudentID 唯一標識每一行。

問題：HOD屬性取決於Department，而不是直接取決於StudentID。
- 學生 ID → 部門（直接依賴）。
- 部門 → HOD（傳遞依賴）。
- 因此，StudentID → HOD 是傳遞依賴。

這種結構會導致冗餘：如果 CS 部門的 HOD 發生變化，則需要更新多行。

相容表（在 3NF 中）：

要解決傳遞依賴關係，請將表分解為兩個關係：

學生系表：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

部門-HOD表：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

Boyce-Codd 範式 (BCNF)

定義:

若符合下列條件，則關係為 Boyce-Codd 範式 (BCNF)：

它採用第三範式（3NF）（即不存在部分或傳遞依賴）。
每個行列式都是候選鍵。

行列式：另一個屬性在功能上所依賴的屬性（或一組屬性）。
候選鍵：可以唯一標識關係中每個元組的最小屬性集。

3NF 和 BCNF 之間的主要差異：

雖然 3NF 允許某些依賴關係，其中非素數屬性在功能上依賴候選鍵，但 BCNF 透過確保每個行列式都是候選鍵來消除任何此類異常情況。

說明:

BCNF 比 3NF 更嚴格，解決了關係可能滿足 3NF 但仍然具有由於違反 BCNF 的依賴關係而導致的冗餘的情況。

何時需要 BCNF：

當非候選鍵屬性決定了候選鍵的一部分，導致冗餘和異常時，BCNF 是必要的。

範例：

不合規表（不在 BCNF 中）：

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

登入後複製

函數依賴：

課程ID → 講師
教練 → 房間

候選鍵：課程ID

問題：

行列式 Instructor 不是候選鍵，而是決定 Room。
這違反了 BCNF，因為並非所有決定因素都是候選鍵。

相容表（BCNF 中）：

要實現 BCNF，請將表分解為兩個關係式：

課程-講師表：

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

登入後複製

教練室桌子：

StudentID | CourseID | Instructor | Department
----------------------------------------------
S1        | C1       | Dr. Smith  | CS
S2        | C2       | Dr. Jones  | EE
S1        | C2       | Dr. Jones  | EE

登入後複製

第四範式 (4NF)

定義:

若符合下列條件，則關係屬於 第四範式 (4NF)：

它採用 Boyce-Codd 範式 (BCNF)（即沒有部分、傳遞或其他異常）。
它沒有任何多值依賴項。

多值依賴（MVD）：當表中的一個屬性決定多個獨立的屬性集時，就存在多值依賴。換句話說，如果一個關係包含兩個或多個彼此不相關的獨立多值屬性，則違反了 4NF。

說明:

在4NF 中，主要目標是消除多值依賴，當記錄包含兩個或多個不直接相關但由於依賴於同一鍵而出現在一起的獨立屬性時，就會發生這種情況。

這些類型的依賴關係會導致冗餘，因為相同資訊的多個副本在行中重複。
透過分解關係以刪除 MVD，我們消除了冗餘並提高了資料庫的一致性。

關鍵概念：

在 4NF 中，關係不應具有兩個或多個依賴候選鍵的多值屬性。必須透過適當分解表來消除每個多值依賴關係。

範例：

不合規表（不在 4NF 中）：

考慮一個儲存有關學生、他們參加的課程以及他們參與的俱樂部的資訊的表：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

候選鍵：StudentID

多值依賴:

StudentID 可以確定一組課程和一組俱樂部，但這些組是相互獨立的。
- StudentID → {Courses}（StudentID 與 Courses 之間的多值相依性）
- StudentID → {Clubs}（StudentID 和 Clubs 之間的多值依賴）

表格違反了 4NF，因為 StudentID 獨立確定課程和俱樂部。這會導致冗餘，因為同一學生 ID 在不同的課程和俱樂部組合中重複多次。

相容表（在 4NF 中）：

為了使表符合4NF，我們必須透過將其分解為兩個表來消除多值依賴：

學生課表：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

學生俱樂部桌：

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

登入後複製

現在，兩個多值依賴項是分開處理的：

學生-課程表儲存學生和他們所修課程之間的關係。
學生俱樂部表格儲存學生和他們所參與的俱樂部之間的關係。

第五範式 (5NF)

定義:

關係採用 第五範式 (5NF)，也稱為 投影連結範式 (PJNF)，如果：

它採用第四範式（4NF）（即不存在多值依賴關係）。
它不能在不遺失資訊的情況下進一步分解，這意味著該關係不包含任何連接依賴或無損連接分解。

連接依賴（JD）：當一個關係可以分解為兩個或多個關係，但當它們重新連接在一起時，不會丟失任何信息，就會發生連接依賴。換句話說，當一個關係可以分為子關係，但原始關係可以在不遺失任何資料的情況下重建時，就存在連結依賴。

說明:

5NF 處理連接依賴，它確保資料以這樣的方式分解，即所有資訊都可以從分解的部分重建，而不會丟失任何資料。 5NF 中的關係的設計方式是，其所有重要的連接依賴關係都由其候選鍵隱含。

無損連接分解：當一個關係被分解為更小的關係然後重新連接時，原始關係可以完全重建，而不會丟失任何數據。如果一個關係不能在不導致資訊遺失的情況下進一步分解，則該關係處於 5NF 狀態。
非平凡連接依賴性：如果連接依賴性未被簡單滿足（即，並非關係中的所有屬性都存在於連接依賴性中），則連接依賴性是非平凡的。

簡單來說，5NF 關注的是確保不存在因分解不當而導致的冗餘。它保證當關係被分解並隨後連接回來時，所有原始數據仍然可用，沒有任何丟失或歧義。

範例：

不合規表（不在 5NF 中）：

考慮一個表，儲存哪些供應商為不同項目提供哪些零件的資訊：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

候選鍵：（供應商、零件、項目）

加入依賴:

上面的關係具有連接依賴性，因為它可以分解為更小的關係而不丟失資訊。例如，表格可以分解為三個子關係：

供應商零件表：

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

登入後複製

供應商-專案表：

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

登入後複製

部分項目表：

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

登入後複製

透過將表分解為這些較小的關係，我們仍然可以透過對這三個較小的關係執行自然連接來重新建立原始表。

但是，由於這種分解是可能的，所以它違反了 5NF。它違反 5NF 的原因是，關於哪個供應商為給定項目提供哪個部件的資訊被冗餘地儲存在多行中。我們多次儲存相同的事實，這是不必要的，並且可能會導致不一致。

合規表（5NF 中）：

為了實現 5NF，我們分解表，以便在不丟失資訊的情況下無法進一步分解關係：

供應商-部分-專案表：

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

登入後複製

在這種形式中，關係現在處於 5NF 狀態，因為它無法在不丟失資料的情況下進一步分解。該表表示與原始表相同的信息，但以更規範的形式表示，其中每個屬性完全依賴候選鍵，並且不存在由於分解不當而導致的冗餘。

關係設計中的關鍵概念

多值依賴：當一個屬性決定多個獨立值時。
連線依賴：確保在連線期間不會建立虛假元組。
依賴關係保留：確保分解後保留所有函數依賴關係。

這份綜合指南使您能夠掌握關聯式資料庫設計，確保資料庫系統有效率、一致且無異常。

以上是關係型資料庫設計：DBMS的詳細內容。更多資訊請關注PHP中文網其他相關文章！