細說數據庫范式

2019-11-03 08:36:26

字體：大中小

來源：轉載

供稿：網友

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

數據庫范式（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，等什么時候有空了再看看是什么東東。

上一篇：SQL Server系統視圖

下一篇：Microsoft SQL Server 2008 Express版本