第三章關系數據庫的規范化設計4.1關系模式的設計問題4.2函數依賴4.3范式4.4關系模式的規范化4.1關系模式的設計問題4.1.1關系模式的冗余和異常問題4.1.2關系模式的非形式化設計準則4.1.1關系模式的冗余和異常問題例4.1設有一個關系模式R(st_no,st_name,st_sex,st_age,st_depid,st_depname,sub_no,sub_name,st_score,st_credit)，其屬性分別表示學號、姓名、性別、年齡、系編號、系名、課程編號、課程名、成績、學分。如圖4-1所示。4.1.1關系模式的冗余和異常問題st_nost_namest_sexst_agest_depidst_depnamesub_nosub_namest_scorest_credit07301康洪男1801經濟管理011數據庫87407301康洪男1801經濟管理012網絡技術75307302張力男2003自動化011數據庫77407303李小言女1902信息技術012網絡技術91307303李小言女1902信息技術013軟件工程69307304龐倩女2003自動化014單片機894圖4-1關系模式R的實例4.1.1關系模式的冗余和異常問題該關系R的主關鍵字為st_no和sub_no。在使用該關系模式過程中會出現以下幾個問題：(1)數據冗余。學生康洪選修了兩門課，他的個人信息被重復存儲了2次。他每多選修一門課程，他的個人信息就要重復一次。(2)更新異常（不一致性的危險）。由于數據冗余，有可能在一個元組中更改了某學生的個人信息，而沒有更改另一個元組中同一學生的個人信息，于是出現同一學生有兩個不同的個人信息，與實際情況不符，但DBMS無法獲知這種不一致。(3)插入異常。如果要增加一名新學生，但他還未選修課程，則st_no和sub_no為空，但由于st_no和sub_no是主關鍵字，如果為空則違反了實體完整性，所以這名學生將無法插入到數據庫中。(4)刪除異常。如果課程014不再開了，則需將07304的元組刪除，但同時也把學生的個人信息刪掉了，這也是一種不合理的現象。因此，關系模式R的設計不是一個合理的設計。4.1.2關系模式的非形式化設計準則關系模式的非形式化設計準則主要有：（1）關系模式的設計應盡可能只包含有直接聯系的屬性，不要包含有間接聯系的屬性。也就是，每個關系模式應只對應于一個實體類型或一個聯系類型。（2）關系模式的設計應盡可能使得相應關系中不出現插入異常、刪除和修改等操作異常現象。（3）關系模式的設計應盡可能使得相應關系中避免放置經常為空值的屬性。（4）關系模式的設計應盡可能使得關系的等值連接在主鍵和外鍵的屬性上進行，并且保證以后不會生成額外的元組。4.2函數依賴4.2.1基本數據類型4.2.2基本表的創建、刪除與修改

4.2.3索引的創建與刪除4.2.1函數依賴的定義定義4-1設有關系模式R(A1,A2,…,An)，X和Y是屬性集{A1,A2,…,An}的子集，若對于R的任意一個可能的關系r，r中不可能有兩個元組在X中的屬性值相等，而在Y中的屬性值不等，則稱X函數決定Y，或Y函數依賴于X，記作X→Y。X稱為這個函數依賴的決定屬性集(Determinant)，可表示為Y＝(X)。若X→Y，并且Y→X,則記為X←→Y。若Y不函數依賴于X,則記為X→Y。4.2.1函數依賴的定義(1)如果X→Y，但Y不為X的子集，則稱X→Y是非平凡的函數依賴。例4.2在關系score(st_no,sub_no,st_score)中，非平凡函數依賴：(st_no,sub_no)→st_score(2)若X→Y，但Y為X的子集,則稱X→Y是平凡的函數依賴。例4.3平凡函數依賴：(st_no,sub_no)→st_no，(st_no,sub_no)→sub_no4.2.1函數依賴的定義(3)若X→Y并且存在X的真子集X1，使得X1→Y，則稱Y部分依賴于X。例4.4student(st_no,st_name,st_sex,st_age,st_depid)關系中，因為(st_no,st_name)→st_sex，st_no→st_sex，所以(st_no,st_name)→st_sex是部分函數依賴。4.2.1函數依賴的定義(4)若X→Y并且對于X的任何一個真子集X1，都不存在X1→Y，則稱Y完全依賴于X。例4.5在關系score(st_no,sub_no,st_score)中，因為(st_no,sub_no,st_score)→st_score，st_no→st_score，sub_no→st_score，所以(st_no,sub_no,st_score)→st_score是完全函數依賴。4.2.1函數依賴的定義(5)若X→Y并且Y→Z，而Y→X，則有X→Z，則稱Z對X傳遞函數依賴。例4.6關系S1（st_no，st_depname，st_depid）因為st_no→st_depname，st_depname→st_depid，并且st_depname→st_no，所以st_no→st_depid為傳遞函數依賴。4.2.2函數依賴和碼的聯系定義4-2設關系模式R<U，F>，X，Y是U中的屬性組，若在R<U，F>的任何一個滿足F中函數依賴的關系r上，都有函數依賴X→Y成立，則稱F邏輯蘊含X→Y，記為F|=X→Y。定義4-3在關系模式R<U，F>中為F所邏輯蘊含的函數依賴的全體稱作F閉包（Closure），記作F+。即F+={X→Y|F|=X→Y}。例4.7關系S（st_no,st_name,st_age,st_depid,st_depname），其屬性組上的函數依賴集為：F＝{st_no→st_name，st_no→st_age，st_no→st_depid，st_depid→st_depname}，則st_no→st_depname就是F所邏輯蘊含的一個函數依賴。4.2.2函數依賴和碼的聯系定義4-4設K為關系模式R<U，F>中的屬性或屬性組，若K→U在F+中，而找不到K的任何一個真子集K′，能使K′→U在F+中，則稱K為關系模式R的候選碼（CandidateKey）。若候選碼多于一個，則選定其中一個作主碼（PrimaryKey）。包含在任何一個候選碼中的屬性叫做主屬性（PrimeAttibute）。不包含在任何碼中的屬性稱為非主屬性（NonprimeAttibute）。最簡單的情況，單個屬性是碼。最極端的情況，整個屬性組是碼，稱為全碼（All-Key）。4.2.2函數依賴和碼的聯系例4.8關系（sub_name，st_depname，sub_no），其屬性組上的函數依賴集為：F={（sub_name，st_depname）→sub_no，sub_no→sub_name}，則（sub_name，st_depname）和（st_depname，sub_no）是兩個候選碼，sub_name，st_depname，sub_no都是主屬性。4.2.2函數依賴和碼的聯系定義4-5設屬性或屬性組X是關系模式R的屬性子集，如果X是另一個關系模式的候選碼，則稱X是R的外部碼（ForeignKey），也稱外碼。例4.9關系score(st_no,sub_no,st_score)中st_no不是碼，但st_no是關系student(st_no,st_name,st_sex,st_age,st_depid)的候選碼，則st_no是關系score的外部碼。4.3函數依賴的公理系統設R是一個具有屬性集合U的關系模式，F是R的一個函數依賴集合。Armstrong公理系統包含如下三條推理規則：(1)自反律：若Y

X

U，則F邏輯蘊含X→Y。(2)增廣律：若F邏輯蘊含X→Y，且Z

U，則F邏輯蘊含XZ→YZ。(3)傳遞律：若F邏輯蘊含X→Y和Y→Z，則F邏輯蘊含X→Z。由自反律所得到的函數依賴均為平凡的函數依賴，事實上自反律的應用只依賴于U，不依賴于F。4.3函數依賴的公理系統例4.10關系（sub_name，st_depname，sub_no），其屬性組上的函數依賴集為：F={（sub_name，st_depname）→sub_no，sub_no→sub_name}。求證：（st_depname，sub_no）→（sub_name，st_depname，sub_no）證明：（1）由F得sub_no→sub_name

（2）根據增廣律對（1）加st_depname得（st_depname，Zp_code）→（sub_name，st_depname）（3）根據增廣律對（2）加sub_no得（st_depname，Zp_code）→（sub_name，st_depname，sub_no）根據Armstrong公理系統的三條推理規則可以推導出下面三條實用的推理規則：（1）合并規則：如果X→Y，X→Z，則X→YZ。（2）偽傳遞規則：如果X→Y，YW→Z，則XW→Z。（3）分解規則：如果X→Y，Z

Y，則X→Z。4.4范式4.4.11NF4.4.22NF4.4.33NF4.4.4BCNF4.4.54NF4.4.11NF定義4-6設R是一個關系模式。如果R的每個屬性的值域都是不可分的簡單數據項的集合，則稱這個關系模式為第一范式關系模式，記作1NF。在任何一個關系數據庫系統中，1NF都是一個最起碼的要求。4.4.22NF定義4-7若關系模式R是1NF，而且每一個非主屬性都完全函數依賴于R的碼，則R稱為第二范式關系模式，記作2NF。2NF不允許關系模式的屬性之間有這樣的函數依賴：X→Y，其中X是碼的真子集，Y是非主屬性，即不允許有非主屬性對碼的部分函數依賴。4.4.22NF例4.1中的關系模式R(st_no,st_name,st_sex,st_age,st_depid,st_depname,sub_no,sub_name,st_score,st_credit)，其屬性組上的函數依賴集是：F＝{st_no→st_name,st_no→st_sex,st_no→st_age，st_no→st_depid

，st_depid→st_depname,sub_no→sub_name，sub_no→st_credit

，(st_no，sub_no)→st_score}，顯然(st_no，sub_no)是碼，st_name,st_sex,st_age,st_depid,st_depname,sub_name,st_score,st_credit是非主屬性，st_no→st_name是非主屬性對碼的部分函數依賴，所以關系模式R不是2NF。可以用分解的方法將一個非2NF的關系模式分解為多個2NF的關系模式。例如，將R(st_no,st_name,st_sex,st_age,st_depid,st_depname,sub_no,sub_name,st_score,st_credit)分解為三個關系模式：R1(st_no,st_name,st_sex,st_age,st_depid,st_depname)R2(st_no,sub_no,st_score)R3(sub_no,sub_name,st_credit)就不再有非主屬性對碼的部分依賴，R1、R2和R3都是2NF的關系模式了。4.4.33NF定義4-8如果關系模式R是2NF,而且它的任何一個非主屬性都不傳遞地依賴于任何候選鍵，則R稱為第三范式關系模式，記作3NF。3NF不允許關系模式的屬性之間有這樣的非平凡函數依賴：X→Y，其中X不包含碼，Y是非主屬性。X不包含碼有兩種情況，一種情況X是碼的真子集，這是2NF不允許的，另一種情況X不是碼的真子集，這是3NF不允許的。4.4.33NF前面討論的關系S（st_no,st_name,st_sex,st_age,st_depid,st_depname），其屬性組上的函數依賴集為：F＝{st_no→st_name,st_no→st_sex,st_no→st_age，st_no→st_depid

，st_depid→st_depname}，顯然st_no是碼，其余的屬性都是非主屬性。非主屬性st_depname傳遞依賴于碼st_no，所以該關系S不是3NF。但該關系沒有非主屬性對碼的部分依賴，所以該關系是2NF。4.4.33NF可以用分解的方法將一個非3NF的關系模式分解為多個3NF的關系模式。例如，將S分解為兩個關系模式：S1(st_no,st_name,st_sex,st_age,st_depid)S2(st_depid,st_depname)就不再有非主屬性對碼的傳遞依賴，S1和S2都是3NF的關系模式了。4.4.4BCNF定義4-9設關系模式R是1NF。如果對于R的每個函數依賴X→Y，則X必為候選鍵，則R是BCNF范式（BoyceCoddNormalForm，由Boyce和Codd提出的）。BCNF是3NF的進一步規范化，即限制條件更嚴格，通常認為BCNF是修正的3NF。3NF不允許有X不包含碼，Y是非主屬性的非平凡函數依賴X→Y。而BCNF則不管Y是主屬性還是非主屬性，只要X不包含碼，就不允許有X→Y這樣的非平凡函數依賴。判斷一個關系模式是否屬于BCNF，只需考察每個非平凡函數依賴X→Y的決定因素X是否包含碼即可。4.4.4BCNF每個BCNF關系模式都具有如下三個性質：（1）所有非主屬性都完全函數依賴于每個候選鍵。（2）所有主屬性都完全函數依賴于每個不包含它的候選鍵。（3）沒有任何屬性完全函數依賴于非碼的任何一組屬性。4.4.4BCNF例4.11關系（sub_name，st_depname，sub_no），其屬性組上的函數依賴集為：F={（sub_name，st_depname）→sub_no，sub_no→sub_name}（sub_name，st_depname）和（st_depname，sub_no）是兩個候選碼，沒有非主屬性，自然該關系是3NF。但函數依賴sub_no→sub_name的決定因素不包含碼，所以該關系不是BCNF。可將該關系分解為兩個關系模式：C1(sub_no，sub_name)C2(st_depname，sub_no)則不再有非平凡的函數依賴的決定因素中不包含碼的情況，C1和C2都是BCNF關系模式了。4.4.54NF定義4-10設R是屬性集U上的一個關系模式，X，Y是U的子集，Z=U-X-Y。關系模式R中多值依賴（MultivaluedDependency，簡記為MVD）X→→Y成立，當且僅當對R的任一關系r，給定的一對(x，z)值有一組Y的值，這組值僅僅決定于x值而與z值無關。如果X→→Y，但Z＝U－X－Y≠Ф，則稱為非平凡多值依賴，否則稱為平凡多值依賴。4.4.54NF定義4-11關系模式R是1NF，對于R中的任意兩個屬性子集X和Y，如果對于每一個非平凡的多值依賴X→→Y（Y＼

X），都有X含有碼，則R稱為第三范式關系模式，記作4NF。4.4.54NF例4.12關系模式D(st_depname，t_name，st_name)中，其中t_name為教師姓名，由于一個系有很多教師，一個系有很多學生，教師與學生間沒有直接聯系，則存在多值依賴st_depname→→t_name和st_depname→→st_name。由于該關系模式的碼是（t_name，st_name），所以該關系不是4NF。可把關系模式D分解成兩個關系模式：D1(st_depname，t_name)D2(st_depname，st_name)D1和D2都是4NF。4.4.54NF圖4-2各范式之間的關系

4.5關系模式的規范化4.5.1規范化步驟4.5.2關系模式的分解及其指標4.5.1規范化步驟消除非主屬性對碼的傳遞函數依賴消除非主屬性對碼的部分和傳遞函數依賴消除非平凡且非函數依賴的多值依賴消除非主屬性對碼的部分函數依賴1NF2NF3NFBCNF4NF圖4-3規范化步驟4.5.2關系模式的分解及其指標定義4-12關系模式R<U,F>的一個分解是指

={R1<U1,F1>,R2<U2,F2>,…,Rn<Un,Fn>}，其中U=U1∪U2∪…∪Un，并且沒有Ui

Uj

，1≤i，j≤n，Fi是F在Ui上的投影。4.5.2關系模式的分解及其指標模式分解就是將一個泛關系模式R分解成數據庫模式ρ，以ρ代替R的過程。它不僅僅是屬性集合的分解，它也是對關系模式上的函數依賴集、以及關系模式的當前值分解的具體表現。關系模式的規范化過程是通過對關系模式的分解來實現的，但是把低一級的關系分解為若干高一級的關系模式的方法并不是唯一的。分解一個模式有很多方法，但是有的分解會出現失去函數依賴、或出現插入、刪除異常等情況，而有的分解則不出現相關問題。在這些分解方法中，只有能夠保證分解后關系模式與原模式等價的方法才有意義，即分解后沒有信息的丟失。4.5.2關系模式的分解及其指標(1)無損分解無損連接性是指當關系模式分解時，原關系模式下的任一合法的關系實例在分解之后能通過自然連接恢復起來。定義4-13設R是一關系模式，分解成ρ={R1，R2，……，Rk}，F是R上的一個函數依賴集。無損連接就是指R中每一個滿足F的關系r（一個關系實例）都有：r=πR1(r)πR2(r)…

πRk(r)，即r為它在Ri上的投影的自然連接。4.5.2關系模式的分解及其指標定理4-1設R的分解為ρ={R1，R2}，F為R所滿足的函數依賴集，則分解ρ具有無損連接性的充分必要條件是（只要滿足一個即可）：R1∩R2→（R1-R2）F+R1∩R2→（R2-R1）F+也就是說，分解后的兩個模式的交能確定這兩個模式的差集，即R1、R2的公共屬性能夠函數確定R1或R2中的其他屬性，這樣的分解就必定是無損連接分解。4.5.2關系模式的分解及其指標例4.13對給定的關系模式R(U，F)，U={A，B，C}，F={A→B}，如下的兩個分解：（1）ρ1={AB，BC}（2）ρ2={AB，AC}判這兩個分解是否無損。4.5.2關系模式的分解及其指標解：①可根據無損連接定理4-1判斷本題∵AB∩BC=BAB-BC=ABC-AB=C∴B→AF+B→CF+故ρ1為有損連接。②根據無損連接定理4-1判斷本題∵AB∩AC=AAB-AC=BAC-AB=C∴A→BF+故ρ2為無損連接。4.5.2關系模式的分解及其指標

(2)保持函數依賴的分解定義4-14設關系模式R的一個分解r={R1，R2，……，Rk}，F是R的依賴集，如果F等價于πR1(F)∪πR2(F)∪…∪πRk(F)，則稱分解r具有依賴保持性。4.5.2關系模式的分解及其指標①若要求分解具有無損連接性，那么分解后的模式一定能達到4NF；②若要求分解保持函數依賴，那么分解后的模式總可以達到3NF，但不一定能達到BCNF；③若要求分解既具有無損連接性，又保持函數依賴，則分解后的模式可以達到3NF，但不一定能達到BCNF。4.5.2關系模式的分解及其指標定義4-15如果函數依賴集F滿足下列條件，則稱F為一個極小函數依賴集。亦稱為最小依賴集或最小覆蓋。

①F中任一函數依賴的右部僅含有一個屬性。

②F中不存在這樣的函數依賴X→A，使得F與F-{X→A}等價。

③F中不存在這樣的函數依賴X→A，X有真子集Z使得F-{X→A}∪{Z-A}與F等價。4.5.2關系模