@知行思新

引言：前文（數(shù)據(jù)庫設(shè)計(jì)Step by Step (9)——ER-to-SQL轉(zhuǎn)化）討論了如何把ER圖轉(zhuǎn)化為關(guān)系表結(jié)構(gòu)。本文將介紹數(shù)據(jù)庫范式并討論如何范式化候選表。我們先來看一下此刻處在數(shù)據(jù)庫生命周期中的位置（如下圖所示）。

前幾篇博文中我們?cè)敿?xì)的討論了ER建模的方法。精心設(shè)計(jì)的ER模型將幫助我們直接得到范式化的表或只需稍許修改即為范式化的表，設(shè)計(jì)、繪制ER圖的重要性也體現(xiàn)在這里。概念數(shù)據(jù)建模（ER建模）從一開始就潛移默化的引導(dǎo)著我們走向范式化的數(shù)據(jù)庫表結(jié)構(gòu)。

本文的討論將始于第一范式，止于BCNF范式。在現(xiàn)實(shí)數(shù)據(jù)庫設(shè)計(jì)中，一般需達(dá)到的范式化目標(biāo)是第三或BCNF范式，更高級(jí)別的范式更多的是理論價(jià)值，本文也不將涉及。

 

范式基礎(chǔ)

關(guān)系數(shù)據(jù)庫中的表有時(shí)會(huì)面對(duì)性能、一致性和可維護(hù)性方面的問題。舉例來說，把整個(gè)數(shù)據(jù)庫中的數(shù)據(jù)都定義在一張表中將導(dǎo)致大量冗余的數(shù)據(jù)，低效的查詢和更新性能，對(duì)某些數(shù)據(jù)的刪除將造成有用數(shù)據(jù)的丟失等。如圖1所示，products, salespersons, customers, orders都存儲(chǔ)在一張名為Sales的表中。

product_name	order_no	cust_name	cust_addr	credit	date	sales_name
vacuum cleaner	1435	Dave	Austin	6	2010-03-01	Carl
computer	2730	Qiang	Plymouth	10	2011-04-15	Ted
refrigerator	2460	Mike	Ann Arbor	8	2010-09-12	Dick
DVD player	519	Peter	Detroit	3	2010-12-05	Fred
radio	1986	Charles	Chicago	7	2011-05-10	Richard
CD player	1817	Eric	Mumbai	8	2010-08-03	Paul
vacuum cleaner	1865	Charles	Chicago	7	2010-10-01	Carl
vacuum cleaner	1885	Betsy	Detroit	8	2009-04-19	Carl
refrigerator	1943	Dave	Austin	6	2011-01-04	Dick
television	2315	Sakti	East Lansing	6	2011-03-15	Fred

（圖1 Sales表）

在這張表中，某些產(chǎn)品和客戶信息是冗余的，浪費(fèi)了存儲(chǔ)空間。某些查詢?nèi)纭吧蟼€(gè)月哪些客戶訂購了吸塵器”需要搜索整張表。當(dāng)要修改客戶Dave的地址需要更新該表的多條記錄。最后刪除客戶Qiang的訂單（2730）將造成該客戶姓名、地址、信用級(jí)別信息的丟失，因?yàn)樵摽蛻糁挥形ㄒ贿@個(gè)訂單。

如果我們通過一些方法把該大表拆分成多個(gè)小表，從而消除上述這些問題使數(shù)據(jù)庫更為高效和可靠，范式化就是為了達(dá)到這一目標(biāo)。范式化是指通過分析表中各屬性之間的互相依賴，并把大表映射為多個(gè)小表的過程。

 

第一范式（1NF）

定義：當(dāng)且僅當(dāng)一張表的所有列只包含原子值時(shí)，即表中每行中的每一個(gè)列只有一個(gè)值，則該表符合第一范式。

圖1中的Sales表的每一行、每一列中只有原子值，故Sales表滿足第一范式。

為了更好的理解第一范式，我們討論一下域、屬性、列之間的差異。

域是某屬性所有可能值的集合，但同一個(gè)域可能被用于多個(gè)屬性上。舉例來說，人名的域包含所有可能的姓名集合，在圖1的表中可用于cust_name或sales_name屬性。每一列代表一個(gè)屬性，有些情況下代表不同屬性的多個(gè)列具有相同的域，這并不會(huì)違反第一范式，因?yàn)楸碇械闹等允窃拥摹?/p>

僅符合第一范式的表常會(huì)遇到數(shù)據(jù)重復(fù)、更新性能以及更新一致性等問題。為了更好的理解這些問題，我們必須定義鍵的概念。

超鍵是一個(gè)或多個(gè)屬性的集合，其能幫助我們唯一確定一條記錄。若組成超鍵屬性列的子集仍為一個(gè)超鍵，但該子集少了任何一個(gè)屬性都將使其不再是一個(gè)超鍵，則該屬性列子集稱為候選鍵。主鍵是從一張表的候選鍵集合中任意挑選出的，作為該表的一個(gè)索引。

作為一個(gè)例子，圖2中表的所有屬性組成一個(gè)超鍵。

report_no	editor	dept_no	dept_name	dept_addr	author_id	author_name	author_addr
4216	woolf	15	design	argus1	53	mantei	cs-tor
4216	woolf	15	design	argus1	44	bolton	mathrev
4216	woolf	15	design	argus1	71	koenig	mathrev
5789	koenig	27	analysis	argus2	26	fry	folkstone
5789	koenig	27	analysis	argus2	38	umar	prise
5789	koenig	27	analysis	argus2	71	koenig	mathrev

（圖2 Report表）

在關(guān)系模型中不允許有重復(fù)的行，因此一個(gè)明顯的超鍵是表的所有列（屬性）的組合。假設(shè)表中每一個(gè)部門的地址（dept_addr）都相同，則除dept_addr之外的屬性仍然是一個(gè)超鍵。對(duì)其他屬性作類似的假設(shè)，逐步縮小屬性的組合。我們發(fā)現(xiàn)屬性組合report_no, author_id能唯一確定表中的其他屬性，即是一個(gè)超鍵。同時(shí)report_no或author_id中的任意一個(gè)都無法唯一確定表中的一行，故屬性組合report_no, author_id是一個(gè)候選鍵。由于它們是該表的唯一候選鍵，它們也是該表的主鍵。

一張表能有多個(gè)候選鍵。舉例來說，在圖2中，若有附加列author_ssn（SSN：社會(huì)保險(xiǎn)號(hào)），屬性組合report_no, author_ssn也能唯一確定表中的其他屬性。因此屬性組合（report_no, author_id）和（report_no, author_ssn）都是候選鍵，可以任選其一作為主鍵。

 

第二范式（2NF）

為了解釋第二以及更高級(jí)別范式。我們需引入函數(shù)依賴的概念。一個(gè)或多個(gè)屬性值能唯一確定一個(gè)或多個(gè)其他屬性值稱為函數(shù)依賴。給定某表（R），一組屬性（B）函數(shù)依賴于另一組屬性（A），即在任意時(shí)刻每個(gè)A值只與唯一的B值相關(guān)聯(lián)。這一函數(shù)依賴用A –> B表示。以圖2中的表為例，表report的函數(shù)依賴如下：

report:  report_no –> editor, dept_no

                dept_no –> dept_name, dept_addr

                author_id –> author_name, author_addr

定義：一張表滿足第二范式（2NF）的條件是當(dāng)且僅當(dāng)該表滿足第一范式且每個(gè)非鍵屬性完全依賴于主鍵。當(dāng)一個(gè)屬性出現(xiàn)在函數(shù)依賴式的右端，且函數(shù)依賴式的左端為表的主鍵或可由主鍵傳遞派生出的屬性組，則該屬性完全依賴于主鍵。

report表中一個(gè)傳遞函數(shù)依賴的例子：

report_no –> dept_no

dept_no –> dept_name

因?yàn)槲覀兡芘缮龊瘮?shù)依賴（report_no –> dept_name），即dept_name傳遞依賴于report_no。

繼續(xù)我們的例子，圖2中表的復(fù)合鍵（report_no, author_id）是唯一的候選鍵，即為表的主鍵。該表存在一個(gè)FD（dept_no –> dept_name, dept_addr），其左端沒有主鍵的任何組成部分。該表的另兩個(gè)FD（report_no –> editor, dept_no和author_id –> author_name, author_addr）的左端包含主鍵的一部分但不是全部。故report表的任何一條FD都不滿足第二范式的條件。

思考一下僅滿足第一范式的report表的缺陷。report_no, editor和dept_no對(duì)該Report的每一位author都需要重復(fù)，故當(dāng)Report的editor需要變更時(shí)，多條記錄必須同步修改。這就是所謂的更新異常（update anomaly），冗余的更新會(huì)降低性能。當(dāng)沒有把所有符合條件的記錄同步更新時(shí)，還會(huì)造成數(shù)據(jù)的不一致。若要在表中加入一位新的author，只有在該author參與了某Report的撰寫才能插入該author的記錄，這就是所謂的插入異常（insert anomaly）。最后，若某一張Report無效了，所有與該Report相關(guān)聯(lián)的記錄必須一起刪除。這可能造成author信息的丟失（與該Report相關(guān)聯(lián)的author_id, author_name, author_addr也被刪除了）。這一副作用被稱為刪除異常（delete anomaly），使數(shù)據(jù)喪失了完整性。

上述這些缺陷可通過把僅滿足第一范式的表轉(zhuǎn)化為多張滿足第二范式的表來克服。在保留原先函數(shù)依賴和語義關(guān)系的前提下，把Report表映射為三張小表（report1, report2, report3），其中包含的數(shù)據(jù)如圖3所示。

Report 1

report_no	editor	dept_no	dept_name	dept_addr
4216	woolf	15	design	argus 1
5789	koenig	27	analysis	argus 2

Report 2

author_id	author_name	author_addr
53	mantei	cs-tor
44	bolton	mathrev
71	koenig	mathrev
26	fry	folkstone
38	umar	prise
71	koenig	mathrev

Report 3

report_no	author_id
4216	53
4216	44
4216	71
5789	26
5789	38
5789	71

（圖3  2NF表）

這些滿足第二范式表的函數(shù)依賴為：

report1: report_no –> editor, dept_no

                 dept_no –> dept_name, dept_addr

report2: author_id –> author_name, author_addr

report3: report_no, author_id為候選鍵，無函數(shù)依賴

現(xiàn)在我們已得到了三張滿足第二范式的表，消除了第一范式表存在的最糟糕的問題。第一、editor, dept_no, dept_name, dept_addr不再需要為每一位author重復(fù)。第二、更改一位editor只需要更新report1的一條記錄。第三、刪除report不再會(huì)造成author信息丟失的副作用。

我們可以注意到這三張滿足第二范式的表可以直接從ER圖轉(zhuǎn)化得到。ER圖中的Author、Report實(shí)體以及之間的“多對(duì)多”關(guān)系可根據(jù)上一篇博文（數(shù)據(jù)庫設(shè)計(jì)Step by Step (9)——ER-to-SQL轉(zhuǎn)化）的規(guī)則很自然的轉(zhuǎn)化為三張表。

 

第三范式（3NF）

第二范式相對(duì)于第一范式已經(jīng)有了巨大的進(jìn)步，但由于存在傳遞依賴（transitive dependency），滿足第二范式的表仍會(huì)存在數(shù)據(jù)操作異常（anomaly）。當(dāng)一張表中存在傳遞依賴，其意味著該表中描述了兩個(gè)單獨(dú)的事實(shí)。每個(gè)事實(shí)對(duì)應(yīng)于一條函數(shù)依賴，函數(shù)依賴的左側(cè)各不相同。舉例來說，刪除一個(gè)report，其包含刪除report1和report3表中的相應(yīng)記錄（如圖3所示），該刪除動(dòng)作的副作用是dept_no, dept_name, dept_addr信息也被刪除了。如果把表report1映射為包含列report_no, editor, dept_no的表report11和包含列dept_no, dept_name, dept_addr的表report12（如圖4所示），我們就能消除上述問題。

Report11

report_no	editor	dept_no
4216	woolf	15
5789	koenig	27

Report12

dept_no	dept_name	dept_addr
15	design	argus 1
27	analysis	argus 2

Report 2

author_id	author_name	author_addr
53	mantei	cs-tor
44	bolton	mathrev
71	koenig	mathrev
26	fry	folkstone
38	umar	prise
71	koenig	mathrev

Report 3

report_no	author_id
4216	53
4216	44
4216	71
5789	26
5789	38
5789	71

（圖4  3NF表）

定義：一張表滿足第三范式（3NF）當(dāng)且僅當(dāng)其每個(gè)非平凡函數(shù)依賴X –> A，其中X和A可為簡(jiǎn)單或復(fù)合屬性，必須滿足以下兩個(gè)條件之一。1. X為超鍵 或 2. A為某候選鍵的成員。若A為某候選鍵的成員，則A被稱為主屬性。注：平凡函數(shù)依賴的形式為YZ –> Z。

在上述例子中通過把report1映射為report11和report12，消除了傳遞依賴report_no –> dept_no –> dept_name, dept_addr，我們得到了如圖4所示的第三范式表及函數(shù)依賴：

report11: repot_no –> editor, dept_no

report12: dept_no –> dept_name, dept_addr

report2:   author_id –> author_name, author_addr

report3:   report_no, author_id為候選鍵（無函數(shù)依賴）

 

Boyce-Codd范式（BCNF）

第三范式消除了大部分的異常，也是商業(yè)數(shù)據(jù)庫設(shè)計(jì)中達(dá)到的最普遍的標(biāo)準(zhǔn)。剩下的異常情況可通過Boyce-Codd范式（BCNF）或更高級(jí)別范式來消除。BCNF范式可看作加強(qiáng)的第三范式。

定義：一張表R滿足Boyce-Codd范式（BCNF），若其每一條非平凡函數(shù)依賴X –> A中X為超鍵。

BCNF范式是比第三范式更高級(jí)別的范式因?yàn)槠淙コ说谌妒街械牡诙N條件（允許函數(shù)依賴右側(cè)為主屬性），即表的每一條函數(shù)依賴的左側(cè)必須為超鍵。每一張滿足BCNF范式的表同時(shí)滿足第三范式、第二范式和第一范式。

以下的例子展示了一張滿足第三范式但不滿足BCNF范式的表。這樣的表和那些僅滿足較低范式的表一樣存在刪除異常。

斷言1：一個(gè)小組里的每一名員工只由一位領(lǐng)導(dǎo)來管理。一個(gè)小組可能有多位領(lǐng)導(dǎo)。

emp_name, team_name –> leader_name

斷言2：每一位領(lǐng)導(dǎo)只會(huì)參與一個(gè)組的管理。

leader_name –> team_name

emp_name	team_name	leader_name
Sutton	Hawks	Wei
Sutton	Condors	Bachmann
Niven	Hawks	Wei
Niven	Eagles	Makowski
Wilson	Eagles	DeSmith

（圖5  team表）

team表滿足第三范式，具有復(fù)合候選鍵emp_name, team_name

team表有如下刪除異常：若Sutton離開了Condors組，Bachmann為Condors組的領(lǐng)導(dǎo)這一信息將丟失。

消除這一刪除異常最簡(jiǎn)單的方法是根據(jù)兩條斷言創(chuàng)建兩張表，通過兩張表中冗余的信息來消除刪除異常。這一分解是無損的并保持了所有原先的函數(shù)依賴，但這降低了更新性能，并需要更多存儲(chǔ)空間。為了避免刪除異常，這樣做是值得的。

注：無損分解是指把一張表分解為兩張小表后，通過對(duì)兩張小表進(jìn)行natural join得到的表與原始表相同，不會(huì)產(chǎn)生任何多余行。

 

數(shù)據(jù)庫范式化示例

（圖6  employee數(shù)據(jù)庫ER圖）

該案例基于圖6中的ER模型和以下相關(guān)函數(shù)依賴。一般而言，函數(shù)依賴可通過分析ER圖及業(yè)務(wù)經(jīng)驗(yàn)推得。

1. emp_id, start_date –> job_title, end_date
2. emp_id –> emp_name, phone_no, office_no, proj_no, proj_name, dept_no
3. phone_no –> office_no
4. proj_no –> proj_name, proj_start_date, proj_end_date
5. dept_no –> dept_name, mgr_id
6. mgr_id –> dept_no

我們的目標(biāo)是設(shè)計(jì)至少能達(dá)到第三范式（3NF）的關(guān)系數(shù)據(jù)庫表結(jié)構(gòu)，并盡可能減少表的數(shù)量。

如果將函數(shù)依賴1至6放入一張表，并設(shè)置復(fù)合主鍵：emp_id, start_date，那么我們違反了第三范式，因?yàn)楹瘮?shù)依賴2至6的等式左側(cè)不是超鍵。因此，我們需要把函數(shù)依賴1從其余的函數(shù)依賴中分離出來。如果將函數(shù)依賴2至6進(jìn)行合并，我們將得到很多傳遞依賴。故函數(shù)依賴2、3、4、5必須分到不同的表中。我們?cè)賮砜紤]函數(shù)依賴5和6是否能在不違反第三范式的前提下進(jìn)行合并。因?yàn)閙gr_id和dept_no是相互依賴的，這兩個(gè)屬性在表中都是超鍵，所以可以合并。

通過合理的映射函數(shù)依賴1至6，我們能得到如下表：

emp_hist:      emp_id, start_date –> job_title, end_date

employee:    emp_id –> emp_name, phone_no, proj_no, dept_no

phone:            phone_no –> office_no

project:           proj_no –> proj_name, proj_start_date, proj_end_date

department: dept_no –> dept_name, mgr_id

                           mgr_id –> dept_no

這一解決方案涵蓋了所有函數(shù)依賴。滿足第三范式和BCNF范式，同時(shí)該方案創(chuàng)建了最少數(shù)量的表。

 

范式化從ER圖得到的候選表

在數(shù)據(jù)庫生命周期中，對(duì)表的范式化是通過分析表的函數(shù)依賴完成的。這些函數(shù)依賴包括：從需求分析中得到的函數(shù)依賴；從ER圖中得到的函數(shù)依賴；從直覺中得到的函數(shù)依賴。

主函數(shù)依賴代表了實(shí)體鍵之間的依賴。次函數(shù)依賴代表實(shí)體內(nèi)數(shù)據(jù)元素間的依賴。一般來說，主函數(shù)依賴可從ER圖中得到，次函數(shù)依賴可從需求分析中得到。表1展示了每種基本ER構(gòu)件所能得到的主函數(shù)依賴。

關(guān)系的度（Degree）	關(guān)系的連通數(shù)（Connectivity）	主函數(shù)依賴
二元或二元回歸	“一對(duì)一” “一對(duì)多” “多對(duì)多”	2個(gè)：鍵（“一”側(cè)） –> 鍵（“一”側(cè)） 1個(gè)：鍵（“多”側(cè)） –> 鍵（“一”側(cè)） 無（由兩側(cè)鍵組成的組合鍵）
三元	“一對(duì)一對(duì)一” “一對(duì)一對(duì)多” “一對(duì)多對(duì)多” “多對(duì)多對(duì)多”	3個(gè)：鍵（“一”），鍵（“一”） –> 鍵（“一”） 2個(gè)：鍵（“一”），鍵（“多”） –> 鍵（“一”） 1個(gè)：鍵（“多”），鍵（“多”） –> 鍵（“一”） 無（有三側(cè)鍵組成的組合鍵）
泛化	無	無

每個(gè)候選表一般會(huì)有多個(gè)主函數(shù)依賴和次函數(shù)依賴，這決定了當(dāng)前表的范式化程度。對(duì)每個(gè)表采用各種技術(shù)使其達(dá)到需求規(guī)格中要求的范式化程度，在范式化過程中要保證數(shù)據(jù)完整性，即范式化后得到的表應(yīng)包含原先候選表的所有函數(shù)依賴。精心設(shè)計(jì)的概念數(shù)據(jù)模型通常能得到基本已范式化的表，后期的范式化處理不會(huì)很困難，所以概念數(shù)據(jù)建模非常重要。

 

主要內(nèi)容回顧

1. 不良的表結(jié)構(gòu)設(shè)計(jì)將導(dǎo)致表數(shù)據(jù)的更新異常（update anomaly）、插入異常（insert anomaly）、刪除異常（delete anomaly）

2. 范式化通過消除冗余數(shù)據(jù)，來解決數(shù)據(jù)庫存在的一致性、完整性和可維護(hù)性等方面的問題。

3. 在實(shí)際數(shù)據(jù)庫設(shè)計(jì)中，范式化的目標(biāo)一般是達(dá)到第三范式或BCNF范式。

4. 精心設(shè)計(jì)的概念數(shù)據(jù)模型（ER模型）能幫助我們得到范式化的表。

數(shù)據(jù)庫范式化參考資料

1. Database Normalization（http://en.wikipedia.org/wiki/Database_normalization）

2. 3 Normal Forms Database Tutorial（http://www.phlonx.com/resources/nf3/）