關係資料庫設計/規範化

資料庫從業人員經常談論規範化，但它通常被人們誤解。有些人認為它是一個學術細節，在現實世界中不切實際。許多從業人員相信規範化在效能方面總是過於昂貴。事實上，規範化具有重要的實際益處。儘管在某些情況下可能存在合理理由避免規範化，但最好從瞭解成本和效益的角度做出決定，而不是出於無知而將其摒棄。

規範化提供了一套規則和模式，可以應用於任何資料庫以避免常見的邏輯不一致。規範化資料庫設計通常會提高

• 一致性，因為資料庫中可能發生的錯誤在結構上是不可能的
• 可擴充套件性，因為對資料庫結構的更改只會影響它們邏輯上依賴的資料庫部分
• 效率，因為不需要儲存冗餘資訊

資料庫界已經確定了幾個不同的規範化級別，每個級別都比上一個更嚴格。這些被稱為正規化，從一（最低的規範化形式，稱為第一正規化或1NF）到五（第五正規化或5NF）。在實踐中，通常會說一種資料庫設計比另一種更規範或更不規範，如這些級別所定義的那樣。

在實際應用中，您通常會看到1NF、2NF和3NF，偶爾也會看到4NF。第五正規化很少見，但本文會簡要介紹。第五正規化意味著SQL選擇新手不會透過與由於多對多依賴而產生的人為偽行進行聯接來產生誤導性的結果，或者等於或大於三方關係，而三方關係與大多數一對多相比很少見，而一對多關係將由早期形式檢測到。

為什麼要進行規範化 ?

資料庫是對世界的一組事實。資料庫管理員希望他們的資料庫只包含關於世界的真實事實，而不包含虛假的事實。規範化允許RDBMS防止某些類別的虛假事實出現在資料庫中，從而有助於一致性。顯然，資料庫中的大部分資訊無法由計算機驗證；唯一可以防止的錯誤型別是與某些邏輯矛盾不一致的錯誤。一般來說，隨著規範化級別的增加，由於表的結構，一類或多類邏輯錯誤將變得不可能。

如果資料庫從一致狀態開始，並且一個明顯有效的操作導致資料庫變得不一致，那麼就會在資料庫中發生一個異常。這可以進一步指定為更新、插入或刪除異常，具體取決於導致錯誤的操作是分別進行行更新、插入或刪除。

上面的示例顯示了更新異常。資料庫使用者嘗試更新員工的地址。但是，由於地址在表中出現多次，並且只更新了一個副本，因此表中現在存在不一致的資料：員工不能同時擁有兩個地址。

此示例顯示了插入異常。新聘請了一位教職工，但必須等到他被分配至少教授一門課程後，才能將此資訊插入資料庫。

什麼是FD ?

函式依賴，或FD，看起來像

$a\rightarrow b$

這意味著b函式依賴於a。另一種理解方式是說，“a決定b”。

這裡的意思是，如果你知道一個欄位的值（a），那麼你也知道另一個欄位的值（b）。例如，如果你知道某人的社會安全號碼（在美國），那麼你可以找到該人的姓、名等。知道一個數據就足以讓你確定其他資料。

另一個例子可能是，如果你知道汽車的序列號，你也可以找到（確定）汽車的製造商和型號。

${\text{serial number}}\rightarrow ({\text{manufacturer}},{\text{model}})$

因此，製造商和型號依賴於序列號。這就是函式依賴。

請注意，這種關係通常不會反過來。如果你知道一輛汽車有特定的製造商，這並不足以找到它的序列號（因為有很多不同的汽車，序列號不同，但都是由同一個製造商製造的）。

瞭解函式依賴的意義何在 ?

如果你不瞭解你的函式依賴，那麼你將永遠被譴責輪迴你的更新、插入和刪除異常。思考你的FD，你也許能夠在整個資料庫中實現BCNF，如果沒有，至少規範化涅槃中還有兩個級別，你可以做3NF，打破低效資料庫設計的束縛，這是你微不足道和虛榮的ER建模沒有考慮到的。儘管這位DB Kung Fu大師盡了最大努力向你展示ER圖示的方式，但它仍然無法與笨拙的掌法風格或堅韌的盲人計算機之拳相提並論。你以為你選擇了偉大的關係，並將所有必要的屬性掛在你的實體矩形上，併為所有主鍵屬性加了下劃線，但最初的影片租賃店特許經營管理系統雲狀想法的圖示分解並沒有防止有損聯接和丟棄一些FD。你可能會懷疑地說，如果你一開始就不知道FD，那麼丟棄FD並不是什麼大不了的事，但是FD的意義在於它們就像二手車保修一樣，保留FD可以避免更新異常的昂貴維修。

Boyce-Codd正規化所做的是將FD的概念與關係表主鍵屬性的概念聯絡起來，記住表的任何候選鍵都可以是主鍵。在BCNF中，所有FD左側的屬性，即FD關係的支配者，都是候選鍵。

考慮負面情況，即當一個或多個 FD 不是候選鍵時，則模式不在 BCNF 中，並且模式容易出現無損連線或功能依賴完整性約束無法保持。在此插入一個有說服力的例子。

正規化

在我們開始討論正規化之前，重要的是要指出它們只是指南，僅僅是指南。有時，為了滿足實際的業務需求，有必要偏離它們。但是，當發生變化時，評估它們對系統可能產生的任何影響並考慮可能出現的不一致性非常重要。話雖如此，讓我們來探索正規化。

正規化就像觀看法律劇一樣有趣：“我發誓要講真話，全部真話，絕不隱瞞真相，願上帝保佑我”。“所有非鍵屬性都將講述關於鍵的事實，整個鍵，以及僅關於鍵的事實，願 Codd 保佑我。”（關於 2NF、3NF 和 BCNF）。

一些用於理解每種正規化的術語

1NF - 原子值

2NF、3NF、BCNF - 一個鍵是關係中的一組屬性，可用於標識關係中的一行。一個超鍵是候選鍵的超集，一個可用於標識行的最小屬性集。因此，關係的所有屬性都可以是超鍵。

2NF - 非鍵屬性依賴於其他非鍵屬性，整個候選鍵，但不依賴於複合鍵的一部分。
3NF - 非鍵屬性直接依賴於整個候選鍵，或為候選鍵的一部分。
BCNF - 非鍵屬性直接依賴於整個候選鍵，並且不是鍵的一部分。

以上總結似乎足夠好，但事實並非如此，因為超鍵的定義是關係的所有屬性都可以是鍵，因此，不存在“非鍵”屬性。所以最好用函式依賴來表達正規化，X -> A “X 決定 A”，或“A 依賴於 X”。然後 2NF 變為“X 不能是候選鍵的一部分，但可以是候選鍵，或者不是超鍵最小鍵集中的超鍵屬性”。3NF 變為“X 是一個完整的超鍵，或者 A 是候選鍵的一部分”。BCNF 變為“X 是一個完整的超鍵”。

為什麼要有 NF ?

1NF - 使編寫查詢更容易。示例是 Clients 表，其中 Client 有三個單獨的電話號碼（可能允許輸入辦公室、家庭和手機號碼）。如果你的資料庫有一個客戶表，其中包含多個電話號碼欄位，那麼它甚至不是 1NF。

如果要從給定電話號碼查詢客戶，則不處於第一正規化會使查詢變得複雜且效率低下。由於你不知道電話號碼將出現在哪個欄位中，因此你必須執行類似以下操作

SELECT Id
  FROM Clients
 WHERE FirstPhoneNumber = '555-123-4567'
       OR SecondPhoneNumber = '555-123-4567'
       OR ThirdPhoneNumber = '555-123-4567';

2NF - 一本教科書說，鑑於 3NF 和 BCNF，它並不重要，並且可能在炫耀為什麼人們會將一個表拆分為由外部索引鍵連結的父關係和子關係。例如，有一個客戶表，還有一個承包商表，其中承包商具有不止一項技能，而承包商-技能表擁有承包商的地址欄位，意味著擁有一個部分依賴於整個鍵承包商-技能的非主屬性，即地址由承包商決定。如果承包商具有不止一項技能，那麼將存在包含相同承包商名稱和地址的冗餘行。2NF 將要求根據 FD 對錶進行拆分，N -> A，以及多值依賴，N ->-> S，或 (Name , Address)，和 (Name, Skill ); 除了是 2NF 之外，這也意味著是 3NF、BCNF 以及 4NF。為什麼呢？

3NF - 就像 BCNF 的一種限制形式，因此，也許可以先看看 BCNF，看看它是否可行，然後再進行 3NF。

BCNF - 對映更容易，因為對於每個 FD X->A，X 必須是超鍵。這使得很容易看出，當將未規範化的 1NF 或 2NF 關係分解為 BC 正規化時，如果沒有丟失衝突的 FD，就無法完成分解，因此分解不是依賴保持的，因此規範化應還原為 3NF，它始終可以適應試圖進行 BCNF 時的衝突 FD。

3NF - 唯一額外的放寬是，如果 FD X-> A 適用，則 A 可以是候選鍵的一部分。為了看到一個不是 BCNF 的分解（因為它沒有保留 FD，但卻是 3NF），一個例子會有所幫助，而這個作者沒有足夠的認知能力來想出一個，因此將給出一些來自網際網路和書籍的例子，前提是它們可能會因無關的社會落後原因（通常是法律）而被撤回。例子是：enroll (student, class, assistant)，具有 FD student class -> assistant , assistant -> class (只允許協助一個班級)，以及 part_supply，(contract, supplier, department, project, part, quantity, value)，具有以下 FD

1) project part -> contract

2) supplier department -> part

3) project -> supplier , 以及

4) contract -> 原始關係的其餘部分。

對於原始的 part_supply 關係，第二和第三 FD 的決定因素 X 不符合“X 是超鍵”的宣告。

Armstrong 公理的傳遞公理表明，第一個 FD，1) Project part -> contract，不會使原始關係違反 BCNF，因為 Project,Part 是超鍵或備用鍵，因為它傳遞對映到原始的單屬性鍵，contract。

嘗試 BCNF 分解的步驟

推薦的分解步驟是使用一個違反 FD，它在 X -> A 中有一個單一的依賴 A，這可能是由於另一個 Armstrong 公理指出 X-> A B C，始終可以分解為 X -> A , X->B, X -> C，單屬性依賴 FD。

（分解公理是另一種表述反射公理和傳遞公理相結合的方式，即，如果 X 是 Y 的超集，則 X -> Y，並且，在這種情況下，A B C 是 A 的超集，並且 X -> A B C，因此 X 傳遞 -> A，因為 A B C -> A。）

回到上面兩個段落中的陳述，要使用一個 FD X -> A，其中

a) A 是 R 的單個屬性，並且

b) X 必須是 R 的子集。

c) FD X -> A 是最小覆蓋 FD 集的成員，這意味著原始的 FD 集已被全部轉換為具有單個依賴的 FD，並且使用 Armstrong 公理無法建立更小的 FD 集，可以根據 Armstrong 公理重新生成原始的 FD 集。

如果滿足 a) 和 b)，則將關係 R 分解為 R - A 和 X A，例如

R = ( contract, supllier, department, project, part, quantity, value) , X = supplier department, A = part

所以 R -A = ( contract, supplier, department, project, quantity, value) , 以及 XA = ( supplier, department, part )。

因為 X -> A，X 可以是 XA 的鍵，並且 X 可以自然連線到 R - A，因為 R 的屬性是 X 的超集。

然後第 2 步是檢查生成的分解關係是否為 BCNF，例如，在這個例子中，R-A 不是 BCNF，因為存在 project -> supplier FD，並且 project 不是 (contract supplier dept project, quantity, value) 的超鍵。

如果違反了 BCNF，則遞迴，因此現在 R = contract, supplier, dept, project, quantity, value，下一個單屬性依賴 FD 是 project -> supplier，因此刪除 A (supplier) 以像以前一樣形成 X-A，得到 ( contract , department, project, quantity , value )。

分解現在是，

(supplier, department, part) (project, supplier) (contract, department, project, quantity, value)

但是，唯一剩餘的違反 FD 是 project, part -> contract，但這裡 X (project, part) 不是 R (contract, department, project, quantity, value) 的子集。

因此 BCNF 分解無法繼續。此時，放棄嘗試實現 BCNF，下一個目標是實現 3NF。

3NF 分解繼續進行失敗的 BCNF 分解的產物，並在所有情況下實現無損連線、FD 保持分解

分解為 3NF 的技術是，從原始關係的無損分解和違反 FD 集開始。前者是透過嘗試分解為 BCNF 建立的，如上所述，後者是 BCNF 分解無法再繼續時剩餘的 FD。

這是以 BCNF 為目標的理由，因為放棄實現 BCNF 的結果可用於實現 3NF。

為了實現 3NF，對於每個違反 FD X -> A，只需新增一個關係 XA。在這個例子中，最後一個 FD，project, part -> contract，可以形成一個關係，因此成功的 3NF 分解現在是

(supplier, department, part) (project, supplier) (contract, department, project, quantity, value) (project, part, contract).

為了總結，不帶例子,

步驟 1：嘗試分解為 BCNF。

步驟 2：嘗試分解為 3NF。

要執行步驟 1，

a. 記住Armstrong 公理 

反射，如果 X 是 Y 的子集，則 Y -> X，

傳遞，如果 X -> Y 且 Y -> Z，則 X -> Z，

增強，如果 XB -> YB，則 X-> Y

反射和傳遞意味著分解：ABC -> A , X -> ABC，則 X -> A，（以及 X->B，以及 X-> C）

聯合也被暗示，X -> Y，X -> Z，則 X -> YZ

b) 使用 Armstrong 公理將原始的 FD 集轉換為最小覆蓋 FD 集。

b1. 使用分解使所有 FD 在右手邊都具有單屬性（單屬性依賴）。

b2. 使用 Armstrong 公理刪除冗餘的決定因素屬性，然後

b3. 刪除等效的轉換 FD。

c) 使用剩餘最小覆蓋 FD 集中的一個 FD。

d) 對於這個 FD，X -> A (single)，X 和 A 必須分別為 R 屬性的子集和成員。即 R 是 X 聯合 A 的超集。

e) 從 R 中移除 A，得到 R - A 作為分解結果之一，以及另一個關係 X A 作為另一個分解結果（即構成 X 和 A 的屬性）。

f) 從 FD 的覆蓋集中移除已使用的 FD，並返回到 c)。 使用關係集 - R，與每個分解結果 R - A 和 X A 的並集作為待分解關係的源集，並繼續直到所有最小覆蓋集的 FD 都已耗盡（清空），或者剩餘的單一屬性 FD 無法用於分解任何正在增長的分解關係集。

如果存在剩餘的 FD，則在保留所有 FD 的情況下無法實現 BCNF。

透過構建，切換到 3NF 覆蓋關係生成

獲取每個屬於剩餘最小 FD 集的 FD，即每個 FD 的形式為 X->A，其中 A 是一個單一屬性的依賴項，使用阿姆斯特朗公理來移除決定因素側 (X) 的任何冗餘，然後移除任何剩餘的本質上等效的 FD，直到沒有更多 FD 可以移除，而不會丟失重建原始 FD 集的能力。

使用此 FD，構建關係 XA 並從剩餘的 FD 集中移除 FD；繼續直到所有 FD 都被處理。

歸一化的銷售宣傳

無論是成功 BCNF 轉換還是 BCNF/3NF 轉換，生成的關係列都可以透過連線重新組合成原始關係，不會丟失或生成虛假行，並保留所有原始的函式依賴。 使用這些歸一化的關係時，更新、插入和刪除異常不應該發生。

4NF 和 5NF 處理一對多和多對多關係，或多值依賴

如果生成的分解關係具有單一屬性的鍵，則實現 BCNF 可能實現 4NF。 多值依賴表示為 X ->-> A，這意味著對於 X 的每個例項，存在一個固定的 A 值集，這些值集與除 X 之外的任何其他屬性都*獨立*。 事實上，FD X -> A（其中每個 X 值都隱含一個單一的 A 值）是 MVD 的一個特例。

例如，stereotype_expertise是一個多值關係，具有原型值/多值依賴的專業知識例項

medical_graduate ->-> physiology, anatomy , pharmacology ; 

computer_science_graduate ->-> concrete mathematics, database design, programming ; 

idiot ->->  pharmacology, database design.

在 4NF 中，如果該單一 MVD 的決定因素和多值依賴項是關係中唯一的屬性，或者如果決定因素是超鍵，並且除 X、Y 之外還存在其他屬性，則多值依賴將始終成立。 最後一個標準沒有意義，因為如果 X 是超鍵，那麼對於每個 X 只有一個行。

4NF 的基本原理透過一個關係的例子來說明，該關係具有 2 個*獨立*的多值依賴，例如 Person Skill Language。 一個人擁有一組技能，還有一組語言。 設計問題的一個例子是當一個人新增一門語言時，存在一個設計選擇：在具有新的個人語言詳細資訊的行中對技能使用空值，或者始終為每個人生成技能和語言的叉積，因此對於技能和語言的叉積的每一行，都有個人，所以這方便了查詢具有特定技能和語言的人。 在這種型別的表中，由於多重技能 + 語言對映，個人不能作為候選鍵，實際上所有 3 個屬性構成了鍵。 因此，根據 4NF，需要分解此關係，直到所有 MVD X->->Y 只有 X Y 作為屬性，或者 X 是超鍵（這實際上沒有意義，因為對於一個 X 存在多個 Y）。 Person Skill Language 的 4NF 分解是什麼？

[http://www.bkent.net/Doc/simple5.htm#label4 William Kent，“關係資料庫理論中的五種正規化的簡單指南”，ACM 通訊 26(2)，1983 年 2 月，120-125]

5NF

這位作者的好，但稍微不一致的教科書指出

如果關係中沒有複合候選鍵，則 3NF 分解關係在 5NF 中。

可以推測，一個複合鍵可以只用其兩個或多個屬性中的一個投影到更小的分解，當與關係的其餘部分重新連線時，將導致虛假行。 5NF 類似於 BCNF 透過其函式依賴的程式分解的宣告式版本，以及 3NF 的額外單一 FD 關係的修補，只是它也應用於 MVD，因為無損連線條件被合併到連線依賴的定義中，即 5NF 關係可以分解成任何一組較小的關係，並且所有這些都可以重新連線而不會產生虛假行。

正規化，沒有邏輯迷戀

1NF

第一正規化 (1NF) 為組織良好的資料庫設定了最基本的規則：從同一個表中消除重複的列。 為每個相關資料組建立單獨的表，並使用唯一的列或列集（主鍵）標識每一行。

2NF

第二正規化 (2NF) 進一步討論了消除重複資料的概念：滿足第一正規化的所有要求。 刪除應用於表中多行的部分資料，並將它們放在單獨的表中。 透過使用外部索引鍵建立這些新表與其前身之間的關係。

3NF

第三正規化 (3NF) 邁出了一大步：滿足第二正規化的所有要求。 刪除不依賴於主鍵的列。 如果剩餘的列（屬性）在函式依賴中起作用，則函式依賴的所有屬性都在表中，並且屬性的決定因素形成一個鍵，或者所有依賴屬性都是鍵屬性。 後者意味著另一個表，它具有決定因素屬性作為鍵，將這些決定因素屬性對映到依賴屬性，這些屬性構成到此表的外部鍵。

Boyce-Codd 正規化

如果表中的屬性在函式依賴中充當決定因素屬性，那麼它和函式依賴的所有其他決定因素屬性都在表中，並且它們一起形成表鍵的超集或等於表鍵。 函式依賴的所有依賴屬性也在表中。

4NF

第四正規化 (4NF) 有一個額外的要求：滿足第三正規化的所有要求。 如果一個關係在 BCNF 中並且它具有單列鍵，或者表僅具有一個多值依賴的屬性，或者多值依賴的決定因素是鍵的超集或等於鍵，則該關係在 4NF 中。

第五正規化 (5NF)

5NF 的目標是針對關係的所有候選鍵實現無損連線分解。5NF 是透過不破壞關係中存在的連線依賴性來定義的。連線依賴性是指特定分解將導致無損重構的宣告。多值依賴性是連線依賴性的一種特殊情況，表示為由 MVD 的決定因素和依賴屬性組成的子關係，以及由原始關係中的所有其他屬性和 MVD 的決定因素屬性組成的子關係，因此連線發生在決定因素屬性上。一篇論文表明，如果一個 3NF 關係的所有候選鍵都是單個屬性，那麼所有連線依賴性都將成立（不會有涉及候選鍵的分解導致無損重構）。

域鍵正規化 (DKNF)

域鍵正規化 消除了插入和刪除異常，同時僅依賴於域約束和鍵約束（不需要任意 CHECK 約束）。不幸的是，沒有通用的方法將資料庫轉換為 DKNF，因此它通常是最難實現的正規化。

反規範化

閱讀完以上關於規範化的內容以及如何進行資料拆分後，你可能在想

現在我的資料庫中到處都是表格，我必須連線一堆表格才能從系統中獲取簡單的資料集！這會如何影響效能呢？

你可能是對的。在某些效能至關重要的場景中，可能需要對資料庫/模式的某些部分進行反規範化，並使用更寬的表格，這些表格可能包含重複/冗餘資料。

但是，此類決策最好留給經驗豐富的 DBA，因為可能還有其他方法可以提高效能，例如物理設計、索引、查詢最佳化等，這些都是 DBA 的專業領域。資料庫反規範化，就像規範化一樣，最好留給經驗豐富的專業人士處理。

但是，如果你足夠自信，想要精通資料庫反規範化，那麼閱讀一本好的資料庫教科書中關於資料庫調優的章節將會有所幫助；我閱讀的那本教科書只是重申了關於 Boyce-Codd 正規化和 3NF 正規化的先前課程，以及兩者之間的選擇，因此在試圖實現無損連線和函式依賴性保留的規範化目標時，從 BCNF 到 3NF 已經存在一個反規範化步驟。這本書還簡要介紹了使用更新、插入和刪除 SQL 觸發器來強制執行函式依賴性（由於反規範化而沒有被強制執行），然後比較這些觸發器的查詢計劃和執行成本，以及保留規範化的查詢計劃和執行成本，以及由此產生的對於某些查詢必須進行連線的成本，後者通常需要建立索引來降低僅需要一小部分行資料的查詢的連線成本。