細說資料庫正規化

理論性的東西，往往容易把人人都看得懂的東西寫成連鬼都看不懂，近似於主任醫生開的藥方。從前學正規化的時候，把書中得概念翻來覆去看，看得痛心疾首深惡痛絕，再加上老師深切誤導，最後一塌糊塗。藉助網路資源，自己寫了一篇，自己是看懂了，希望對大家也有所幫助，有錯誤幫忙指正。

 

資料庫正規化（Normal forms）：是用於規範關係型資料庫設計，以減少謬誤發生的一種準則。

 

1NF(first normal form)：

Table faithfully represents a relation and has no repeating groups.

資料庫表必須如實地展現“關係”，並且不允許有“重複組”出現。

 

這樣的概念真是令人痛心疾首，我們只好再搬出1NF的的作者之一Chris Date的解釋：

1. There's no top-to-bottom ordering to the rows.

（任意兩行沒有特定的順序關係。不存在一個特定的理由要某一行必須在另一行之前。）

2. There's no left-to-right ordering to the columns.

（任意兩列沒有特定的順序關係。）

3. There are no duplicate rows.

（不允許存在重複的行。如果一張表沒有Unique Key，事實上它是違反1NF的。）

4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

（不允許出現空值Null，這一點不同作者是有爭議的。事實上我們常常違背這點。）

5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

（不允許存在隱藏欄位。不知道Oracle的Rowid屬不屬於這個？）

 

有人從第四點的“one value”大肆挖掘，於是我們就見到了書上這樣的定義：“如果一個關係模式R的所有屬性都是原子的，即不可再分的基本資料項，則RÎ1NF”。

 

這一點被認為是1NF的核心，“關係模式R”↔“表”，“屬性” ↔ “列”，下面是一種與1NF不一致的情況，通常這是一類很明顯的設計缺陷：

ID	Artist	FavoriteColor	……
1	Babyface	Blue,Yellow	……
2	Sting	Green	……

 

對上例我們不能把它拆分成FavoriteColor1、FavoriteColor2……因為首先我們不能確定該拆分成幾列；其次FavoriteColor1與FavoriteColor2在結構、含意方面都是相同的，這實際上也是一類“repeating group”；同時這種設計會導致某些查詢困難，比如“有哪些藝人喜歡黃色？”

 

解決方案是將表拆分成兩個：

ID	Artist	……
1	Babyface	……
2	Sting	……

 

ID	FavoriteColor
1	Blue
1	Yellow
2	Green

 

總結：

對1NF最核心的 “原子性”，違反此規範的可能性：接近於0%。不過，網上很多帖子說在關係型資料庫中根本不可能違背1NF，我認為這是不對的。

 

 

2NF(second normal form):

No non-prime attribute in the table is functionally dependent on a part (proper subset) of a candidate key.

不存在非主屬性對任一候選鍵的部分函式依賴。

 

如果解釋完下面幾個概念，這個定義就可以讀懂了：

Superkey：超級鍵（L）,如果屬性或屬性組合能唯一標識一條記錄，則它是一個Superkey。

Candidate key：候選鍵，當Superkey只包含一個屬性時，則它是一個候選鍵；當Superkey包含一組屬性時，僅當這一組屬性不包含另一Superkey時，它是一個候選鍵。換句話說，候選鍵是“純淨的”、最小化的Superkey。

Non-prime attribute：非主屬性，未在任何候選鍵中出現的屬性，即為非主屬性。

 

舉例來說，對錶{First_name,Last_name,Address}，假定全名不重複，則：

Superkey：

{First_name,Last_name}

{First_name,Last_name,Address}

Candidate key:

{First_name,Last_name}

Non-prime attribute：

Address

 

淺白版：“2NF針對的是複合候選鍵（即鍵包含的欄位個數>1）的情況，非主屬性不能只依賴於複合候選鍵中的一部分欄位。”顯然，如果是非複合候選鍵，如果它符合1NF，那麼它一定符合2NF。

 

假設有這樣一張涉及藝人與唱片公司的關係表：

Artist 藝人	Company 唱片公司	DurationYears 簽約總年數	CompAddr 公司住址
Babyface	Solar	4	Indiana
Babyface	Laface	2	Indiana

顯然，{Artist,Company}為可以作為一個候選鍵，DurationYears在這沒有問題，但CompAddr是違反2NF的，它只依賴於候選鍵的一部分（依賴於Company），這是違反2NF的，為了消除這種情況，我們可以：

Artist 藝人	CompID 唱片公司	DurationYears 簽約總年數
Babyface	1	4
Babyface	2	2

 

ID	Company 唱片公司	CompAddr 公司住址
1	Solar	Indiana
2	Laface	Indiana

 

總結：

對於2NF，如果關係中的候選鍵只包含一個屬性，可以直接略過。

 

在考慮2NF的過程中，不要把幾個無關的實體的屬性雜揉放在一個關係中，比如Artist是一個實體、Company是一個實體，它們可以有一系列的關聯表（也是實體），但在關聯表中儘量不要引入前兩個實體的無關屬性。

 

 

3NF(Third normal form)

Every non-prime attribute is non-transitively dependent on every key of the table.

不存在非主屬性對任一鍵（候選鍵）的傳遞依賴。

 

傳遞依賴，你可以顧名思義，這裡就不再引入定義了，舉個例子，有下面一張表：

Tournament 賽事	Year 年份	Winner 冠軍	Winner Date of Birth 冠軍生日
Indiana Invitational	1998	Al Fredrickson	21 July 1975
Cleveland Open	1999	Bob Albertson	28 September 1968
Des Moines Masters	1999	Al Fredrickson	21 July 1975
Indiana Invitational	1999	Chip Masterson	14 March 1977

 

這裡的候選鍵為{Tournament,Year}，顯然有這樣的決定關係：

{Tournament,Year}→Winner

{Tournament,Year}→Winner→Winner Date of Birth

其中第二條就屬於違反3NF的情況，因為Winner Date of Birth依賴於Winner而不是直接依賴於候選鍵。這種情況下，可以將Winner,Winner Date of Birth單獨作為一張表，這裡不贅述。

 

總結：

我覺得大多數人憑藉直觀感覺，就可使設計的關係符合3NF，所以這些理論，你只需要姑且讀之。

 

 

BCNF(Boyce-Codd normal form)（Boyce與Codd是該正規化的兩名作者。）

Every non-trivial functional dependency in the table is a dependency on a superkey.

表中的任何非平凡函式依賴，都必須是對superkey的依賴。

 

non-trivial functional dependency：非平凡函式依賴，如果存在一個決定關係x→y，且y並非x的子集，則叫著y非平凡函式依賴於x。

 

BCNF與3NF的最大區別是它並不僅針對非主屬性（non-prime attribute）來說，它發生的時候常常是表中根本不存在非主屬性，以至於它不可能違反2NF或3NF。而BCNF的出現就是為了擴大“打擊面”。

 

於是BCNF的主旨是：補充對發生在主屬性（prime attribute）身上的函式依賴的約束，因為對於非主屬性的約束已經在3NF中完成了。

 

例子，使用關係表描述學生、課程、教師的關係（假定一名教師只負責一門課程，一門課程則可以由多位教師負責）：

Student 學生	Course 課程	Teacher 教師
S1	C1	T1
S1	C2	T2
S2	C1	T1
S2	C2	T3
S2	C3	T2

候選鍵：

{Student,Course}

{Student,Teacher}

因此這裡不存在非主屬性，而在主屬性的函式依賴中，存在Teacher→Course，這屬於違反BCNF的情況。

 

可是，問題是這個表看起來還挺正常的啊？！它的毛病在於，我們無法阻止類似最後一行這樣的資料插入，而這會導致與前提“一名教師只負責一門課程”違背。所以我們還是需要將它拆分：

Student 學生	Teacher 教師
S1	T1
S1	T2
S2	T1
S2	T3

 

Teacher 教師	Course 課程
T1	C1
T2	C2
T3	C2

這樣，在“Teacher-Course”表中，藉助主鍵的幫助，最後可以避免違背“一名教師只負責一門課程”這個前提。

 

那麼，如果沒有這樣一個前提，是初的設計是否符合BCNF？目前看來是的。

 

真實的情況可能更為複雜，下面這個更接近於我的一些經歷：

1）學生需要學習多門課程

2）一門課程可能有多位教師負責

3）一位教師可能負責多門課程

4）某一班級的某一課程對應的教師是固定的（一位）

據此，為了描述學生、課程、教師三者的關係，從這一團亂麻中最早跳出來的大概是這樣的表：

Student 學生	Class 班級	Course 課程	Teacher 教師

 

候選鍵：

{Student,Course}

我們可以明顯地看到Student→Class違反了2NF，於是：

Student 學生	Class 班級

 

Class 班級	Course 課程	Teacher 教師

 

從這兩張表，仔細考慮，即便我們通過Class關聯兩張表，還是無法得出學生與課程的關係（只能得出可供該學生選擇的課程），所以我們需要再新增一張表：

Student 學生	Course 課程

 

最後大概是這麼三張表，可能還有其它的方案，這裡只是舉例說明，就不糾纏了。

 

在BCNF之後，還有4NF，5NF，DKNF，6NF，等什麼時候有空了再看看是什麼東東。

原文地址：http://www.cnblogs.com/KissKnife/archive/2009/10/26/1590029.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617508/，如需轉載，請註明出處，否則將追究法律責任。