數據庫設計范式深入淺出
?
?
??? 關系數據庫設計之時是要遵守一定的規則的，尤其是數據庫設計范式。現簡單介紹1NF(第一范式)，2NF(第二范式)，3NF(第三范式)和BCNF，另有第四范式和第五范式留到以后再介紹。在你設計數據庫之時，若能符合這幾個范式，你就是數據庫設計的高手。
?
第一范式(1NF)：在關系模式R中的每一個具體關系r中，如果每個屬性值都是不可再分的最小數據單位，則稱R是第一范式的關系。
?
??? 例：如職工號，姓名，電話號碼組成一個表(一個人可能有一個辦公室電話和一個家里電話號碼)，規范成為1NF有三種方法：
?
????
1、重復存儲職工號和姓名。這樣關鍵字只能是電話號碼。
??? 2、職工號為關鍵字，電話號碼分為單位電話和住宅電話兩個屬性
??? 3、職工號為關鍵字，但強制每條記錄只能有一個電話號碼。
?
??? 以上三個方法，第一種方法最不可取，按實際情況選取后兩種情況。
?
?
第二范式(2NF)：如果關系模式R(U,F)中的所有非主屬性都完全依賴于任意一個候選關鍵字，則稱關系R是屬于第二范式的。
?
??? 例：選課關系SCI(SNO,CNO,GRADE,CREDIT)其中SNO為學號，CNO為課程號，GRADEGE為成績，CREDIT為學分。由以上條件，關鍵字為組合關鍵字(SNO,CNO)
?
??? 在應用中使用以上關系模式有以下問題：
?
??? a.數據冗余，假設同一門課由40個學生選修，學分就重復40次。
??? b.更新異常，若調整了某課程的學分，相應的元組CREDIT值都要更新，有可能會出現同一門課學分不同。
??? c.插入異常，如計劃開新課，由于沒人選修，沒有學號關鍵字，只能等有人選修才能把課程和學分存入。
??? d.刪除異常，若學生已經結業，從當前數據庫刪除選修記錄。某些門課程新生尚未選修，則此門課程及學分記錄無法保存。
?
??? 原因：非關鍵字屬性CREDIT僅函數依賴于CNO，也就是CREDIT部分依賴組合關鍵字(SNO,CNO)而不是完全依賴。
??? 解決方法：分成兩個關系模式 SC1(SNO,CNO,GRADE)，C2(CNO,CREDIT)。新關系包括兩個關系模式，它們之間通過SC1中的外關鍵字CNO相聯系，需要時再進行自然聯接，恢復了原來的關系
?
?
第三范式(3NF)：如果關系模式R(U,F)中的所有非主屬性對任何候選關鍵字都不存在傳遞信賴，則稱關系R是屬于第三范式的。
?
??? 例：如S1(SNO,SNAME,DNO,DNAME,LOCATION)各屬性分別代表學號、姓名、所在系、系名稱、系地址。
?
??? 關鍵字SNO決定各個屬性。由于是單個關鍵字，沒有部分依賴的問題，肯定是2NF。但這關系肯定有大量的冗余，有關學生所在的幾個屬性DNO,DNAME,LOCATION將重復存儲，插入，刪除和修改時也將產生類似以上例的情況。
?
??? 原因：關系中存在傳遞依賴造成的。即SNO -> DNO。 而DNO -> SNO卻不存在，DNO -> LOCATION, 因此關鍵字 SNO 對 LOCATION 函數決定是通過傳遞依賴 SNO -> DNO -> LOCATION 實現的。也就是說，SNO不直接決定非主屬性LOCATION。
??? 解決目地：每個關系模式中不能留有傳遞依賴。
??? 解決方法：分為兩個關系 S(SNO,SNAME,DNO)，D(DNO,DNAME,LOCATION)
?
??? 注意：關系S中不能沒有外關鍵字DNO。否則兩個關系之間失去聯系。
?
?
BCNF：如果關系模式R(U,F)的所有屬性(包括主屬性和非主屬性)都不傳遞依賴于R的任何候選關鍵字，那么稱關系R是屬于BCNF的。或是關系模式R，如果每個決定因素都包含關鍵字(而不是被關鍵字所包含)，則RCNF的關系模式。
?
??? 例：配件管理關系模式 WPE(WNO,PNO,ENO,QNT)分別表倉庫號、配件號、職工號、數量。有以下條件：
?
??? a.一個倉庫有多個職工。
??? b.一個職工僅在一個倉庫工作。
??? c.每個倉庫里一種型號的配件由專人負責，但一個人可以管理幾種配件。
??? d.同一種型號的配件可以分放在幾個倉庫中。
?
??? 分析：由以上得 PNO 不能確定QNT，由組合屬性(WNO,PNO)來決定，存在函數依賴(WNO,PNO) -> ENO。由于每個倉庫里的一種配件由專人負責，而一個人可以管理幾種配件，所以有組合屬性(WNO,PNO)才能確定負責人，有(WNO,PNO)-> ENO。因為 一個職工僅在一個倉庫工作，有ENO -> WNO。由于每個倉庫里的一種配件由專人負責，而一個職工僅在一個倉庫工作，有?(ENO,PNO)-> QNT。
?
??? 找一下候選關鍵字，因為(WNO,PNO) -> QNT，(WNO,PNO)-> ENO ，因此?(WNO,PNO)可以決定整個元組，是一個候選關鍵字。根據ENO->WNO，(ENO,PNO)->QNT，故(ENO,PNO)也能決定整個元組，為另一個候選關鍵字。屬性ENO,WNO,PNO 均為主屬性，只有一個非主屬性QNT。它對任何一個候選關鍵字都是完全函數依賴的，并且是直接依賴，所以該關系模式是3NF。
?
??? 分析一下主屬性。因為ENO->WNO，主屬性ENO是WNO的決定因素，但是它本身不是關鍵字，只是組合關鍵字的一部分。這就造成主屬性WNO對另外一個候選關鍵字(ENO,PNO)的部分依賴，因為(ENO,PNO)-> ENO但反過來不成立，而PNO->WNO，故(ENO,PNO)-> WNO 也是傳遞依賴。
?
??? 雖然沒有非主屬性對候選關鍵遼的傳遞依賴，但存在主屬性對候選關鍵字的傳遞依賴，同樣也會帶來麻煩。如一個新職工分配到倉庫工作，但暫時處于實習階段，沒有獨立負責對某些配件的管理任務。由于缺少關鍵字的一部分PNO而無法插入到該關系中去。又如某個人改成不管配件了去負責安全，則在刪除配件的同時該職工也會被刪除。
?
??? 解決辦法：分成管理EP(ENO,PNO,QNT)，關鍵字是(ENO,PNO)工作EW(ENO,WNO)其關鍵字是ENO
?
??? 缺點：分解后函數依賴的保持性較差。如此例中，由于分解,函數依賴（WNO，PNO）-> ENO 丟失了, 因而對原來的語義有所破壞。沒有體現出每個倉庫里一種部件由專人負責。有可能出現一部件由兩個人或兩個以上的人來同時管理。因此，分解之后的關系模式降低了部分完整性約束。
?
??? 一個關系分解成多個關系，要使得分解有意義，起碼的要求是分解后不丟失原來的信息。這些信息不僅包括數據本身，而且包括由函數依賴所表示的數據之間的相互制約。進行分解的目標是達到更高一級的規范化程度，但是分解的同時必須考慮兩個問題：無損聯接性和保持函數依賴。有時往往不可能做到既有無損聯接性，又完全保持函數依賴。需要根據需要進行權衡。
?
?
1NF直到BCNF的四種范式之間有如下關系：
?
??? BCNF > 3NF > 2NF > 1NF
?
?
小結：
?
??? 目地：規范化目的是使結構更合理，消除存儲異常，使數據冗余盡量小，便于插入、刪除和更新
??? 原則：遵從概念單一化“一事一地”原則，即一個關系模式描述一個實體或實體間的一種聯系。規范的實質就是概念的單一化。
??? 方法：將關系模式投影分解成兩個或兩個以上的關系模式。
??? 要求：分解后的關系模式集合應當與原關系模式“等價”，即經過自然聯接可以恢復原關系而不丟失信息，并保持屬性間合理的聯系。
??? 注意：一個關系模式結這分解可以得到不同關系模式集合，也就是說分解方法不是唯一的。最小冗余的要求必須以分解后的數據庫能夠表達原來數據庫所有信息為前提來實現。其根本目標是節省存儲空間，避免數據不一致性，提高對關系的操作效率，同時滿足應用需求。實際上，并不一定要求全部模式都達到BCNF不可。有時故意保留部分冗余可能更方便數據查詢。尤其對于那些更新頻度不高，查詢頻度極高的數據庫系統更是如此。
?
??? 在關系數據庫中，除了函數依賴之外還有多值依賴，聯接依賴的問題，從而提出了第四范式，第五范式等更高一級的規范化要求。在此，以后再談。
?
??? 各位朋友，你看過后有何感想，其實，任何一本數據庫基礎理論的書都會講這些東西，考慮到很多網友是半途出家，來做數據庫。特找一本書大抄特抄一把，各位有什么問題，也別問我了，自已去找一本關系數據庫理論的書去看吧，說不定，對各位大有幫助。說是說以上是基礎理論的東西，請大家想想，你在做數據庫設計的時候有沒有考慮過遵過以上幾個范式呢，有沒有在數據庫設計做得不好之時，想一想，對比以上所講，到底是違反了第幾個范式呢？
?
??? 我見過的數據庫設計，很少有人做到很符合以上幾個范式的，一般說來，第一范式大家都可以遵守，完全遵守第二第三范式的人很少了，遵守的人一定就是設計數據庫的高手了，BCNF的范式出現機會較少，而且會破壞完整性，你可以在做設計之時不考慮它，當然在ORACLE中可通過觸發器解決其缺點。以后我們共同做設計之時，也希望大家遵守以上幾個范式。
?
?
?
?
附：對6個范式的通俗歸納
------------------------------------------------------------------
?
1NF：一個table中的列是不可再分的(即列的原子性)
例：(A,B,C-D-E) 分解為 (A,B,C,D,E)
?
2NF：非主鍵列必須完全依賴整個主鍵
例：(A,B,C,D)主鍵(A,B)，且(A,B)->C,A->D，則分解為(A,B,C)主鍵(A,B)外鍵A,(A,D)主鍵A
?
3NF：一個table中的非主鍵列C不存在 A->B->C 情況
例：(A,B,C,D)主鍵A，且A->B,A->C,B->D，則分解為(A,B,C)主鍵A外鍵B,(B,D)主鍵B
?
BCNF：3NF去掉非主鍵限制
例：(A,B,C,D)主鍵(A,B,C)，且(A,B)->C,C->A，則分解為(A,B,D),(C,A)，但是會失去(A,B)->C，破壞數據一致性
注：如果滿足3NF，且主鍵只有一個屬性或主鍵屬性間無依賴，則自動滿足BCNF
?
4NF：禁止主鍵列和非主鍵列一對多關系(消除多值依賴)
例：(A,B,C)主鍵(A,B)，且(A,B)->C，則分解為(A,C),(B,C)
?
5NF：將一個table盡可能的分割成小的塊，以排除在table中所有連接依賴
例：(A,B,C)主鍵(A,B,C)，則分解為(A,B),(B,C),(A,C)
?
?
再稍微詳細一點的舉例說明：
-----------------------------------------------------------------
?
第一范式:
定義：如果關系R 中所有屬性的值域都是單純域，那么關系模式R是第一范式的
那么符合第一模式的特點就有
1)有主關鍵字
2)主鍵不能為空，
3)主鍵不能重復,
4)字段不可以再分
例如：
StudyNo??|?Name?|?Sex?? |??Contact
20040901???john???Male?????Email:kkkk@ee.net,phone:222456
20040901???mary???famale?? email:kkk@fff.net,phone:123455
以上的表就不符合，第一范式：主鍵重復(實際中數據庫不允許重復的)，而且Contact字段可以再分
所以變更為正確的是
StudyNo??|?Name |?Sex?? |??Email????????|?Phone
20040901???john???Male?????
kkkk@ee.net
??? 222456
20040902???mary???famale???
kkk@fff.net
??? 123455
?
第二范式：
定義：如果關系模式R是第一范式的，而且關系中每一個非主屬性不部分依賴于主鍵，稱R是第二范式的。
所以第二范式的主要任務就是滿足第一范式的前提下，消除部分函數依賴。
StudyNo??|?Name?|?Sex?? |??Email????????|?Phone??? |??ClassNo? | ClassAddress
01?????????john???Male?????
kkkk@ee.net
????222456????? 200401?????A樓2
02?????????mary???famale???
kkk@fff.net
????123455????? 200402?????A樓3
這個表完全滿足于第一范式,主鍵由StudyNo和ClassNo組成，這樣才能定位到指定行
但是,ClassAddress部分依賴于關鍵字(ClassNo-〉ClassAddress),所以要變為兩個表
表一
StudyNo??|?Name?|?Sex?? |??Email?????? ?|?Phone??? |??ClassNo
01???????? john???Male?????
kkkk@ee.net
????222456????? 200401?????
02???????? mary???famale???
kkk@fff.net
????123455????? 200402????
表二
ClassNo? | ClassAddress
200401?????A樓2
200402?????A樓3
第三范式：
滿足第二范式的前提下，消除傳遞依賴。
例:
StudyNo?? |?Name?|?Sex?? |??Email???????|?bounsLevel??|?bouns
20040901????john???Male?????
kkkk@ee.net
?? 優秀???????????$1000
20040902???mary???famale???
kkk@fff.net
???良?????????????$600
這個完全滿足了第二范式,但是bounsLevel和bouns存在傳遞依賴
更改為：
StudyNo?? |?Name |?Sex?? |??Email???????|?bouunsNo
20040901????john???Male?????
kkkk@ee.net
?? 1
20040902????mary???famale???
kkk@fff.net
???2
bounsNo?? |?bounsLevel?? |?? bouns
1???????????優秀?????????????$1000
2???????????良???????????????$600
這里我比較喜歡用bounsNo作為主鍵，基于兩個原因
1) 不要用字符作為主鍵。可能有人說：如果我的等級一開始就用數值就代替呢？
2) 但是如果等級名稱更改了，不叫 1,2,3或優,良，這樣就可以方便更改，所以我一般優先使用與業務無關的字段作為關鍵字。
?
一般滿足前三個范式就可以避免數據冗余。
?
第四范式：
主要任務：滿足第三范式的前提下，消除多值依賴
product??| agent |? factory
Car????????A1???????F1
Bus????????A1???????F2
Car????????A2???????F2
在這里，Car的定位，必須由 agent 和 Factory才能得到（所以主鍵由agent和factory組成）,可以通過 product依賴了agent和factory兩個屬性
所以正確的是
表1????????????????????????????? 表2：
product?? |?? agent??????????? factory? |?? product
Car??????????? A1????????????????? F1???????Car
Bus??????????? A1????????????????? F2???????Car
Car??????????? A2????????????????? F2???????Bus
?
第五范式：
定義: 如果關系模式R中的每一個連接依賴, 都是由R的候選鍵所蘊含, 稱R是第五范式的
看到定義，就知道是要消除連接依賴,并且必須保證數據完整
例子
A?? |?? B? |?? C
a1????? b1???? c1
a2????? b1???? c2
a1????? b2???? c1
a2????? b2???? c2
如果要定位到特定行，必須三個屬性都為關鍵字。
所以關系要變為 三個關系，分別是A 和B,B和C,C和A
如下：
表1????????????????表2??????????????表3
A?? |?? B?????????B?? |?? C????????C??? |???A
a1????? b1????????b1????? c1???????c1???????a1???????????
a1????? b2????????b1????? c2???????c1??????a2
?
??? 范式可以避免數據冗余，減少數據庫的空間，減輕維護數據完整性的麻煩，但是操作難，因為需要聯系多個表才能得到所需要數據，而且越高范式性能就會越差。要權衡是否使用更高范式是比較麻煩。
??? 一般我在做項目中都，用得最多的也就是第三范式，我認為使用到第三范式也就足夠了，性能好而且方便管理數據。
?
?
?
?
-The End-