DBMS 中的范式：數據庫中的 1NF，2NF，3NF 和 BCNF · BeginnersBook 中文系列教程

# DBMS 中的范式：數據庫中的 1NF，2NF，3NF 和 BCNF > 原文： [https://beginnersbook.com/2015/05/normalization-in-dbms/](https://beginnersbook.com/2015/05/normalization-in-dbms/) **范式**是在數據庫中組織數據以避免數據冗余，插入異常，更新異常和數據的過程。刪除異常。讓我們首先討論異常，然后我們將用例子討論正常形式。 ## DBMS 中的異常數據庫未范式時會發生三種類型的異常。這些是 - 插入，更新和刪除異常。我們舉一個例子來理解這一點。 **示例**：假設制造公司將員工詳細信息存儲在名為`employee`的表中，該表具有四個屬性：`emp_id`用于存儲員工的 id，`emp_name`用于存儲員工的姓名，`emp_address`用于存儲員工的地址，`emp_dept`用于存儲部門詳細信息員工的工作方式。在某些時候，表看起來像這樣： | `emp_id` | `emp_name` | `emp_address` | `emp_dept` | | --- | --- | --- | --- | | 101 | Rick | Delhi | D001 | | 101 | Rick | Delhi | D002 | | 123 | Maggie | Agra | D890 | | 166 | Glenn | Chennai | D900 | | 166 | Glenn | Chennai | D004 | 上表未范式。我們將看到當表未范式時我們面臨的問題。 **更新異常**：在上表中，員工 Rick 有兩行，因為他屬于公司的兩個部門。如果我們想要更新 Rick 的地址，那么我們必須在兩行中更新相同的內容，否則數據將變得不一致。如果不知何故，正確的地址在一個部門更新，但在其他部門沒有更新，那么根據數據庫，Rick 將有兩個不同的地址，這是不正確的，會導致數據不一致。 **插入異常**：假設一名新員工加入正在接受培訓并且當前未分配到任何部門的公司，那么如果`emp_dept`字段不允許空值，我們將無法將數據插入表中。 **刪除異常**：假設，如果公司關閉部門 D890 的某個時間點，那么刪除具有`emp_dept`為 D890 的行也會刪除員工 Maggie 的信息，因為她只被分配給該部門。為了克服這些異常，我們需要范式數據。在下一節中，我們將討論范式。 ## 范式以下是最常用的常規形式： * 第一范式（1NF） * 第二范式（2NF） * 第三范式（3NF） * Boyce Codd 范式（BCNF） ## 第一范式（1NF）根據第一范式的規則，表的屬性（列）不能包含多個值。它應該只包含原子值。 **示例**：假設公司想要存儲其員工的姓名和聯系方式。它創建一個如下所示的表： | `emp_id` | `emp_name` | `emp_address` | `emp_mobile` | | --- | --- | --- | --- | | 101 | Herschel | New Delhi | 8912312390 | | 102 | Jon | Kanpur | 88121212129900012222 | | 103 | Ron | Chennai | 7778881212 | | 104 | Lester | Bangalore | 99900001238123450987 | 兩名員工（Jon& Lester）有兩個手機號碼，因此公司將它們存儲在您在上表中看到的相同字段中。該表是**不在 1NF** 中，因為規則說“表的每個屬性必須具有原子（單個）值”，員工的`emp_mobile`值為`Jon & Lester`違反了這條規則。為了使表符合 1NF，我們應該有這樣的數據： | `emp_id` | `emp_name` | `emp_address` | `emp_mobile` | | --- | --- | --- | --- | | 101 | Herschel | New Delhi | 8912312390 | | 102 | Jon | Kanpur | 8812121212 | | 102 | Jon | Kanpur | 9900012222 | | 103 | Ron | Chennai | 7778881212 | | 104 | Lester | Bangalore | 9990000123 | | 104 | Lester | Bangalore | 8123450987 | ## 第二范式（2NF）如果以下兩個條件成立，則表示在 2NF 中： * 表為 1NF（第一范式） * 沒有非主屬性取決于表的任何候選鍵的適當子集。不屬于任何候選鍵的屬性稱為非主屬性。 **示例**：假設學校想要存儲教師的數據和他們教授的科目。他們創建了一個如下所示的表：由于教師可以??教授多個科目，因此該表可以為同一位教師設置多行。 | `teacher_id` | `subject` | `teacher_age` | | --- | --- | --- | | 111 | Maths | 38 | | 111 | Physics | 38 | | 222 | Biology | 38 | | 333 | Physics | 40 | | 333 | Chemistry | 40 | **候選鍵**：`{teacher_id, subject}` **非主屬性**：`teacher_age` 該表在 1 NF 中，因為每個屬性都有原子值。但是，它不在 2NF 中，因為非主屬性`teacher_age`僅依賴于`teacher_id`，它是候選鍵的適當子集。這違反了 2NF 的規則，因為規則說“**沒有**非主屬性依賴于表的任何候選鍵的正確子集”。為了使表符合 2NF，我們可以在兩個表中打破它： **`teacher_details`表：** | `teacher_id` | `teacher_age` | | --- | --- | | 111 | 38 | | 222 | 38 | | 333 | 40 | **`teacher_subject `表：** | `teacher_id` | `subject` | | --- | --- | | 111 | Maths | | 111 | Physics | | 222 | Biology | | 333 | Physics | | 333 | Chemistry | 現在表符合第二范式（2NF）。 ## 第三范式（3NF）如果滿足以下條件，則表設計為 3NF： * 表必須是 2NF * [應刪除任何超鍵上非主要屬性的傳遞函數依賴](https://beginnersbook.com/2015/04/transitive-dependency-in-dbms/)。不屬于任何[候選鍵](https://beginnersbook.com/2015/04/candidate-key-in-dbms/)的屬性稱為非主屬性。換句話說，3NF 可以這樣解釋：如果表在 2NF 中，則表在 3NF 中，并且對于每個函數依賴`X -> Y`至少滿足下列條件之一： * `X`是表的[超鍵](https://beginnersbook.com/2015/04/super-key-in-dbms/) * `Y`是表的主要屬性作為候選鍵之一的一部分的屬性稱為主要屬性。 **示例**：假設公司想要存儲每個員工的完整地址，他們會創建一個名為`employee_details`的表，如下所示： | `emp_id` | `emp_name` | `emp_zip` | `emp_state` | `emp_city` | `emp_district` | | --- | --- | --- | --- | --- | | 1001 | John | 282005 | UP | Agra | Dayal Bagh | | 1002 | Ajeet | 222008 | TN | Chennai | M-City | | 1006 | Lora | 282007 | TN | Chennai | Urrapakkam | | 1101 | Lilly | 292008 | UK | Pauri | Bhagwan | | 1201 | Steve | 222999 | MP | Gwalior | Ratan | **超鍵**：`{emp_id}`，`{emp_id, emp_name}`，`{emp_id, emp_name, emp_zip}`...等 **候選鍵**：`{emp_id}` **非主屬性**：除`emp_id`之外的所有屬性都是非主，因為它們不是任何候選鍵的一部分。在這里，`emp_state`，`emp_city`和`emp_district`依賴于`emp_zip`。并且，`emp_zip`依賴于`emp_id`，這使得非主屬性（`emp_state`，`emp_city`和`emp_district`）可傳遞地依賴于超鍵（`emp_id`）。這違反了 3NF 的規則。要使此表符合 3NF，我們必須將表拆分為兩個表以刪除傳遞依賴項： **員工表：** | `emp_id` | `emp_name` | `emp_zip` | | --- | --- | --- | | 1001 | John | 282005 | | 1002 | Ajeet | 222008 | | 1006 | Lora | 282007 | | 1101 | Lilly | 292008 | | 1201 | Steve | 222999 | **`employee_zip`表：** | `emp_zip` | `emp_state` | `emp_city` | `emp_district` | | --- | --- | --- | --- | | 282005 | UP | Agra | Dayal Bagh | | 222008 | TN | Chennai | M-City | | 282007 | TN | Chennai | Urrapakkam | | 292008 | UK | Pauri | Bhagwan | | 222999 | MP | Gwalior | Ratan | ## Boyce Codd 范式（BCNF）它是 3NF 的高級版本，這也是它被稱為 3.5NF 的原因。 BCNF 比 3NF 更嚴格。如果表是 3NF，則表符合 BCNF，對于每個[函數依賴](https://beginnersbook.com/2015/04/functional-dependency-in-dbms/)`X -> Y`，`X`應該是表的超鍵。 **示例**：假設有一家公司，員工在**多個部門工作**。他們存儲這樣的數據： | `emp_id` | `emp_nationality` | `emp_dept` | `dept_type` | `dept_no_of_emp` | | --- | --- | --- | --- | --- | | 1001 | Austrian | Production and planning | D001 | 200 | | 1001 | Austrian | stores | D001 | 250 | | 1002 | American | design and technical support | D134 | 100 | | 1002 | American | Purchasing department | D134 | 600 | **上表**中的函數依賴： + `emp_id -> emp_nationality` + `emp_dept -> {dept_type，dept_no_of_emp}` **候選鍵**：`{emp_id, emp_dept}` 該表不在 BCNF 中，因為`emp_id`和`emp_dept`都不是鍵。為了使表符合 BCNF，我們可以在三個表中打破這樣的表： **`emp_nationality`表：** | `emp_id` | `emp_nationality` | | --- | --- | | 1001 | Austrian | | 1002 | American | **`emp_dept`表：** | `emp_dept` | `dept_type` | `dept_no_of_emp` | | --- | --- | --- | | Production and planning | D001 | 200 | | stores | D001 | 250 | | design and technical support | D134 | 100 | | Purchasing department | D134 | 600 | **`emp_dept_mapping`表：** | `emp_id` | `emp_dept` | | --- | --- | | 1001 | Production and planning | | 1001 | stores | | 1002 | design and technical support | | 1002 | Purchasing department | **函數依賴**： + `emp_id -> emp_nationality` + `emp_dept -> {dept_type, dept_no_of_emp}` **候選鍵**： + 第一個表：`emp_id` + 第二個表：`emp_dept` + 第三個表：`{emp_id, emp_dept}` 現在這是在 BCNF 中，因為在函數依賴中左側部分是關鍵。