關(guān)系數(shù)據(jù)庫規(guī)范化相關(guān)設(shè)計

關(guān)系數(shù)據(jù)庫規(guī)范化相關(guān)設(shè)計2023/3/202本章重要概念（1）關(guān)系模式的冗余和異常問題。（2）FD的定義、邏輯蘊涵、閉包、推理規(guī)則、與關(guān)鍵碼的聯(lián)系；平凡的FD；屬性集的閉包；推理規(guī)則的正確性和完備性；FD集的等價；最小依賴集。（3）無損分解的定義、性質(zhì)、測試；保持依賴集的分解。（4）關(guān)系模式的范式：1NF，2NF，3NF，BCNF。分解成2NF、3NF模式集的算法。2023/3/203前言關(guān)系數(shù)據(jù)庫的規(guī)范化設(shè)計是指面對一個現(xiàn)實問題,如何選擇一個比較好的關(guān)系模式集合。規(guī)范化設(shè)計理論主要包括三個方面的內(nèi)容：數(shù)據(jù)依賴、范式和模式設(shè)計方法。其中數(shù)據(jù)依賴起著核心的作用。數(shù)據(jù)依賴研究數(shù)據(jù)之間的聯(lián)系，范式是關(guān)系模式的標準，模式設(shè)計方法是自動化設(shè)計的基礎(chǔ)。規(guī)范化設(shè)計理論對關(guān)系數(shù)據(jù)庫結(jié)構(gòu)的設(shè)計起著重要的作用。2023/3/2044.1.1關(guān)系模型的外延和內(nèi)涵外延就是通常所說的關(guān)系、表或當前值，它的基本性質(zhì)已在前面介紹過。由于用戶經(jīng)常對關(guān)系進行插入、刪除和修改操作，因此外延是與時間有關(guān)的，隨著時間的推移在不斷變化。內(nèi)涵是與時間獨立的，是對數(shù)據(jù)的定義以及數(shù)據(jù)完整性約束的定義。對數(shù)據(jù)的定義包括對關(guān)系、屬性、域的定義和說明。對數(shù)據(jù)完整性約束的定義涉及面較廣，主要包括以下幾個方面：靜態(tài)約束，涉及到數(shù)據(jù)之間聯(lián)系（稱為“數(shù)據(jù)依賴，datadependences）、主鍵和值域的設(shè)計。動態(tài)約束，定義各種操作（插入、刪除、修改）對關(guān)系值的影響。2023/3/2054.1.2關(guān)系模式的冗余和異常問題（一）例4.1TNAMEADDRESSC#CNAMEt1a1c1n1t1a1c2n2t1a1c3n3t2a2c4n4t2a2c5n2t3a3c6n4圖4.1關(guān)系模式R的實例

2023/3/2064.1.2關(guān)系模式的冗余和異常問題（二）數(shù)據(jù)冗余如果一個教師教幾門課程，那么這個教師的地址就要重復幾次存儲。操作異常由于數(shù)據(jù)的冗余，在對數(shù)據(jù)操作時會引起各種異常：修改異常。例如教師t1教三門課程，在關(guān)系中就會有三個元組。如果他的地址變了，這三個元組中的地址都要改變。若有一個元組中的地址未更改，就會造成這個教師的地址不惟一，產(chǎn)生不一致現(xiàn)象。插入異常。如果一個教師剛調(diào)來，尚未分派教學任務(wù)，那么要將教師的姓名和地址存儲到關(guān)系中去時，在屬性C#和CNAME上就沒有值（空值）。在數(shù)據(jù)庫技術(shù)中空值的語義是非常復雜的，對帶空值元組的檢索和操作也十分麻煩。刪除異常。如果在圖4.1中要取消教師t3的教學任務(wù)，那么就要把這個教師的元組刪去，同時也把t3的地址信息從表中刪去了。這是一種不合適的現(xiàn)象。2023/3/2074.1.2關(guān)系模式的冗余和異常問題（三）TNAMEADDRESSTNAMEC#CNAMEt1a1t1c1n1t2a2t1c2n2t3a3t1c3n3

t2c4n4

t2c5n2

t3c6n4圖4.2關(guān)系模式分解的實例

（a）關(guān)系模式R1的實例

（b）關(guān)系模式R2的實例

2023/3/2084.2.1函數(shù)依賴的定義（一）定義4.1設(shè)有關(guān)系模式R(U)，X和Y是屬性集U的子集，函數(shù)依賴（FunctionalDependency，簡記為FD）是形為X→Y的一個命題，只要r是R的當前關(guān)系，對r中任意兩個元組t和s，都有t［X］=s［X］蘊涵t［Y］=s［Y］，那么稱FDX→Y在關(guān)系模式R(U)中成立。2023/3/2094.2.1函數(shù)依賴的定義（二）例4.2ABCDABCDa1b1c1d1a1b1c1d1a1b1c2d2a1b2c2d2a2b2c3d3a2b2c3d3a3b1c4d4a3b2c4d4圖4.3關(guān)系模式R的兩個關(guān)系

2023/3/20104.2.1函數(shù)依賴的定義（三）

例4.3

有一個關(guān)于學生選課、教師任課的關(guān)系模式：

R（S#，SNAME，C#，GRADE，CNAME，TNAME，TAGE） 屬性分別表示學生學號、姓名、選修課程的課程號、成績、課程名、任課教師姓名和年齡等意義。

如果規(guī)定，每個學號只能有一個學生姓名，每個課程號只能決定一門課程，那么可寫成下列FD形式：

S#→SNAME C#→CNAME 每個學生每學一門課程，有一個成績，那么可寫出下列FD：

（S#，C#）→GRADE 還可以寫出其他一些FD：

C#→（CNAME，TNAME，TAGE）TNAME→TAGE2023/3/20114.2.2FD的邏輯蘊涵定義4.2設(shè)F是在關(guān)系模式R上成立的函數(shù)依賴的集合，X→Y是一個函數(shù)依賴。如果對于R的每個滿足F的關(guān)系r也滿足X→Y，那么稱F邏輯蘊涵X→Y，記為F?X→Y。定義4.3設(shè)F是函數(shù)依賴集，被F邏輯蘊涵的函數(shù)依賴全體構(gòu)成的集合，稱為函數(shù)依賴集F的閉包（closure），記為F+。即F+=｛X→Y|記為F?X→Y。｝2023/3/20124.2.3FD的推理規(guī)則（一）設(shè)U是關(guān)系模式R的屬性集，F(xiàn)是R上成立的只涉及到U中屬性的函數(shù)依賴集。FD的推理規(guī)則有以下三條：A1（自反性，Reflexivity）：若YXU，則X→Y在R上成立。A2（增廣性，Augmentation）：若X→Y在R上成立，且ZU，則XZ→YZ在R上成立。A3（傳遞性，Transitivity）：若X→Y和Y→Z在R上成立，則X→Z在R上成立。2023/3/20134.2.3FD的推理規(guī)則（二）定理4.1FD推理規(guī)則A1、A2和A3是正確的。也就是，如果X→Y是從F用推理規(guī)則導出，那么X→Y在F+中。定理4.2FD的其他五條推理規(guī)則:(1)A4（合并性，Union）：｛X→Y，X→Z｝?X→YZ。(2)A5（分解性，Decomposition）：｛X→Y，ZY｝X→Z。(3)A6（偽傳遞性）：｛X→Y，WY→Z｝?WX→Z。(4)A7（復合性，Composition）：｛X→Y，W→Z｝XW→YZ。(5)A8｛X→Y，W→Z｝?X∪（W－Y）→YZ。2023/3/20144.2.3FD的推理規(guī)則（三）例4.5已知關(guān)系模式R（ABC），F(xiàn)=｛A→B，B→C｝，求F+。 根據(jù)FD的推理規(guī)則，可推出F的F+有43個FD。 例如，據(jù)規(guī)則A1可推出A→φ（φ表示空屬性集），A→A，…。據(jù)已知的A→B及規(guī)則A2可推出AC→BC，AB→B，A→AB，…。據(jù)已知條件及規(guī)則A3可推出A→C等。 有興趣的同學可推出這43個FD。2023/3/20154.2.3FD的推理規(guī)則（四）定義4.4對于FDX→Y，如果YX，那么稱X→Y是一個“平凡的FD”，否則稱為“非平凡的FD”。定理4.3如果A1…An是關(guān)系模式R的屬性集，那么X→A1…An成立的充分必要條件是X→Ai（i=1，…，n）成立。2023/3/20164.2.4FD和關(guān)鍵碼的聯(lián)系定義4.5設(shè)關(guān)系模式R的屬性集是U，X是U的一個子集。如果X→U在R上成立，那么稱X是R的一個超鍵。如果X→U在R上成立，但對于X的任一真子集X1都有X1→U不成立，那么稱X是R上的一個候選鍵。本章的鍵都是指候選鍵。 例4.6在學生選課、教師任課的關(guān)系模式中： R（S#，SNAME，C#，GRADE，CNAME，TNAME，TAGE） 如果規(guī)定：每個學生每學一門課只有一個成績；每個學生只有一個姓名；每個課程號只有一個課程名；每門課程只有一個任課教師。 根據(jù)這些規(guī)則，可以知道（S#，C#）能函數(shù)決定R的全部屬性，并且是一個候選鍵。雖然（S#，SNAME，C#，TNAME）也能函數(shù)決定R的全部屬性，但相比之下，只能說是一個超鍵，而不能說是候選鍵，因為其中含有多余屬性。

2023/3/20174.2.5屬性集的閉包定義4.6設(shè)F是屬性集U上的FD集，X是U的子集，那么（相對于F）屬性集X的閉包用X+表示，它是一個從F集使用FD推理規(guī)則推出的所有滿足X→A的屬性A的集合：X+=｛屬性A|X→A在F+中｝定理4.4X→Y能用FD推理規(guī)則推出的充分必要條件是YX+。例4.7屬性集U為ABCD，F(xiàn)D集為｛A→B，B→C，D→B｝。則用上述算法，可求出A+=ABC，（AD）+=ABCD，（BD）+=BCD，等等。2023/3/20184.2.5FD推理規(guī)則的完備性推理規(guī)則的正確性是指“從FD集F使用推理規(guī)則集推出的FD必定在F+中”，完備性是指“F+中的FD都能從F集使用推理規(guī)則集導出”。也就是正確性保證了推出的所有FD是正確的，完備性保證了可以推出所有被蘊涵的FD。這就保證了推導的有效性和可靠性。定理4.5FD推理規(guī)則{A1，A2，A3}是完備的。2023/3/20194.2.6FD集的最小依賴集（一）定義4.7如果關(guān)系模式R（U）上的兩個函數(shù)依賴集F和G，有F+=G+，則稱F和G是等價的函數(shù)依賴集。定義4.8設(shè)F是屬性集U上的FD集。如果Fmin是F的一個最小依賴集，那么Fmin應(yīng)滿足下列四個條件： ⑴F+min=F+； ⑵每個FD的右邊都是單屬性； ⑶Fmin中沒有冗余的FD（即F中不存在這樣的函數(shù)依賴X→Y，使得F與F－｛X→Y｝等價）； ⑷每個FD的左邊沒有冗余的屬性（即F中不存在這樣的函數(shù)依賴X→Y，X有真子集W使得F－｛X→Y｝∪｛W→Y｝與F等價）。2023/3/20204.2.6FD集的最小依賴集（二） 例4.8設(shè)F是關(guān)系模式R（ABC）的FD集，F(xiàn)=｛A→BC，B→C，A→B，AB→C｝，試求Fmin。

①先把F中的FD寫成右邊是單屬性形式： F=｛A→B，A→C，B→C，A→B，AB→C｝ 顯然多了一個A→B，可刪去。得F=｛A→B，A→C，B→C，AB→C｝ ②F中A→C可從A→B和B→C推出，因此A→C是冗余的，可刪去。得F=｛A→B，B→C，AB→C｝ ③F中AB→C可從A→B和B→C推出，因此AB→C也可刪去。最后得F=｛A→B，B→C｝，即所求的Fmin。2023/3/20214.3關(guān)系模式的分解

4.3.1模式分解問題（一）定義4.9設(shè)有關(guān)系模式R(U)，屬性集為U，R1、…、Rk都是U的子集，并且有R1∪R2∪…∪Rk＝U。關(guān)系模式R1、…、Rk的集合用ρ表示，ρ=｛R1，…，Rk｝。用ρ代替R的過程稱為關(guān)系模式的分解。這里ρ稱為R的一個分解，也稱為數(shù)據(jù)庫模式。2023/3/20224.3.1模式分解問題（二）圖4.5模式分解示意圖

2023/3/20234.3.2無損分解（一）例4.9rCC4343rABCr1AB2A111111112112圖4.6未丟失信息的分解

（b）（c）（a）

rABCr1ABr2AC

r1r2AB1141114111231213111212（a）（b）（c）（d）圖4.7丟失信息的分解

2023/3/20244.3.2無損分解（二）定義4.10設(shè)R是一個關(guān)系模式，F(xiàn)是R上的一個FD集。R分解成數(shù)據(jù)庫模式ρ=｛R1，…，Rk

｝。如果對R中滿足F的每一個關(guān)系r，都有r=πR1（r）?πR2（r）?…?πRk（r）那么稱分解ρ相對于F是“無損聯(lián)接分解”（losslessjoindecomposition），簡稱為“無損分解”，否則稱為“損失分解”（lossydecomposition）。2023/3/20254.3.2無損分解（三）定理4.6設(shè)ρ=｛R1，…，Rk

｝是關(guān)系模式R的一個分解，r是R的任一關(guān)系，ri=πRi（r）（1≤i≤k），那么有下列性質(zhì)：rmρ（r）；若s=mρ（r），則πRi（s）=ri；mρ（mρ（r））=mρ（r），這個性質(zhì)稱為冪等性（

Idempotent）。2023/3/20264.3.2無損分解（四）Rρ=｛R1，R2，…，Rk｝rr1，r2，…，rkss1，s2，…，skπππ圖中：ri=πRi（r）（1≤i≤k）si=πRi（r）（1≤i≤k）據(jù)性質(zhì)1.有rmρ（r）據(jù)性質(zhì)2.有si=ri（1≤i≤k）圖4.8r的投影連接變換示意圖

2023/3/20274.3.2無損分解（五）圖4.9泛關(guān)系假設(shè)下的示意圖

圖4.9泛關(guān)系假設(shè)時的示意圖

2023/3/20284.3.2無損分解（六）例4.10設(shè)關(guān)系模式R（ABC）分解成ρ=｛AB，BC｝。（a）和（b）分別是模式AB和BC上的值r1和r2，（c）是r1?r2的值。顯然πBC（r1?r2）≠r2。這里r2中元組（b2c2）就是一個懸掛元組，由于它的存在，使得r1和r2不存在泛關(guān)系r。r1ABr2BCABCa1b1a1b1c1bc1b12c2（a）關(guān)系r1（b）關(guān)系r2rr12（c）rr122023/3/20294.3.3無損分解的測試方法（一） 算法4.3無損分解的測試構(gòu)造一張k行n列的表格，每列對應(yīng)一個屬性Aj（1≤j≤n），每行對應(yīng)一個模式Ri（1≤i≤k）。如果Aj在Ri中，那么在表格的第i行第j列處填上符號aj，否則填上bij。把表格看成模式R的一個關(guān)系，反復檢查F中每個FD在表格中是否成立，若不成立，則修改表格中的值。修改方法如下：對于F中一個FDX→Y，如果表格中有兩行在X值上相等，在Y值上不相等，那么把這兩行在Y值上也改成相等的值。如果Y值中有一個是aj，那么另一個也改成aj；如果沒有aj，那么用其中一個bij替換另一個值（盡量把下標ij改成較小的數(shù)）。一直到表格不能修改為止。（這個過程稱為chase過程）若修改的最后一張表格中有一行是全a，即a1a2…an，那么稱ρ相對于F是無損分解，否則稱損失分解。2023/3/20304.3.3無損分解的測試方法（二）a4a3b32b31CDb24a3a2b21BCb14b13a2a1ABDCBAa4a3b32b31CDa4a3a2a1BCb14b13a2a1ABDCBA圖4.12算法4.3的運用示意圖（一）

（a）初始表格

（a）修改后的表格

a4a3b32b31CDb24a3a2b21BCb14b13a2a1ABDCBAa4a3b32b31CDa4a3a2b1BCb14b13a2b1ABDCBA（a）初始表格

（a）修改后的表格

圖4.13算法4.3的運用示意圖（二）

2023/3/20314.3.3無損分解的測試方法（三）定理4.7設(shè)ρ=｛R1，R2

｝是關(guān)系模式R的一個分解，F(xiàn)是R上成立的FD集，那么分解ρ相對于F是無損分解的充分必要條件是（R1∩R2）→（R1－R2）或（R1∩R2）→（R2－R1）。定理4.8如果FDX→Y在模式R上成立，且X∩Y=φ，那么R分解成ρ={R－Y，XY}是無損分解。2023/3/20324.3.4保持函數(shù)依賴的分解（一）定義4.11設(shè)F是屬性集U上的FD集，Z是U的子集，F(xiàn)在Z上的投影用πZ（F）表示，定義為πZ（F）=｛X→Y|X→Y∈F+，且XYZ｝定義4.12設(shè)ρ=｛R1，…，Rk｝是R的一個分解，F(xiàn)是R上的FD集，如果有∪πRi（F）?F，那么稱分解ρ保持函數(shù)依賴集F。

2023/3/20334.3.4保持函數(shù)依賴的分解（二）WNOWSWNOWGWNOWSWGW18級W12000W18級2000W26級W21600W26級1600W36級W31400W36級1400例4.12

（c）rr12（a）關(guān)系r1（b）關(guān)系r22023/3/20344.3.5模式分解與模式等價問題（一） 本節(jié)討論的關(guān)系模式分解的兩個特性實際上涉及到兩個數(shù)據(jù)庫模式的等價問題，這種等價包括數(shù)據(jù)等價和依賴等價兩個方面。數(shù)據(jù)等價是指兩個數(shù)據(jù)庫實例應(yīng)表示同樣的信息內(nèi)容，用“無損分解”衡量。如果是無損分解，那么對泛關(guān)系反復的投影和聯(lián)接都不會丟失信息。依賴等價是指兩個數(shù)據(jù)庫模式應(yīng)有相同的依賴集閉包。在依賴集閉包相等情況下，數(shù)據(jù)的語義是不會出差錯的。違反數(shù)據(jù)等價或依賴等價的分解很難說是一個好的模式設(shè)計。2023/3/20354.3.5模式分解與模式等價問題（二）

例4.13設(shè)關(guān)系模式R（ABC），ρ=｛AB，AC｝是R的一個分解。試分析分別在F1=｛A→B｝，F(xiàn)2=｛A→C，B→C｝，F(xiàn)3=｛B→A｝，F(xiàn)4=｛C→B，B→A｝情況下，ρ是否具有無損分解和保持FD的分解特性。相對于F1=｛A→B｝，分解ρ是無損分解且保持FD的分解。相對于F2

=｛A→C，B→C｝，分解ρ是無損分解，但不保持FD集。因為B→C丟失了。相對于F3

=｛B→A｝，分解ρ是損失分解但保持FD集的分解。相對于F4

=｛C→B，B→A｝，分解ρ是損失分解且不保持FD集的分解(丟失了C→B)

2023/3/20364.4關(guān)系模式的范式關(guān)系模式的好與壞，用什么標準衡量？這個標準就是模式的范式（NormalForms，簡記為NF）。范式的種類與數(shù)據(jù)依賴有著直接的聯(lián)系，基于FD的范式有1NF、2NF、3NF、BCNF等多種。在不提及FD時，關(guān)系中是不可能有冗余的問題，但是當存在FD時，關(guān)系中就有可能存在數(shù)據(jù)冗余問題。1NF是關(guān)系模式的基礎(chǔ)；2NF已成為歷史，一般不再提及；在數(shù)據(jù)庫設(shè)計中最常用的是3NF和BCNF。為了敘述的方便，我們還是從1NF、2NF、3NF、BCNF順序來介紹。2023/3/20374.4.1第一范式（1NF）定義4.13如果關(guān)系模式R的每個關(guān)系r的屬性值都是不可分的原子值，那么稱R是第一范式（firstnormalform，簡記為1NF）的模式。滿足1NF的關(guān)系稱為規(guī)范化的關(guān)系，否則稱為非規(guī)范化的關(guān)系。關(guān)系數(shù)據(jù)庫研究的關(guān)系都是規(guī)范化的關(guān)系。例如關(guān)系模式R（NAME，ADDRESS，PHONE），如果一個人有兩個號碼（PHONE），那么在關(guān)系中至少要出現(xiàn)兩個元組，以便存儲這兩個號碼。1NF是關(guān)系模式應(yīng)具備的最起碼的條件。2023/3/2038滿足第一范式的(不好的)關(guān)系模式的例子

例4：設(shè)有一關(guān)系R(S#，C#，G，TN，D)，其中(S#)為學號，C#為課程號，G為成績，TN為任課教師姓名，D為教師所在系名，這些數(shù)據(jù)具有下列語義：(1)學號是一個學生的標識，課程號是一門課程的標識。(2)一位學生所修的每門課程都有一個成績。(3)每門課程只有一位任課教師，但一位教師可以教多門課。(4)教師中沒有重名，每位教師只屬于一個系。2023/3/2039下面是一個具體實例：S#C#GTNDs1c1g1t1d1s1c2g2t2d2s2c1g3t1d1s2c2g4t2d2s3c2g5t2d2s3c3g6t2d2學號課程號成績教師系名2023/3/2040雖然上述的關(guān)系模式只有四個屬性，但在使用過程中有以下幾個問題：

(1)數(shù)據(jù)冗余。例如，教師所在系名對選該教師所開課的所有學生都重復一次。

(2)插入異常。由于關(guān)系的主鍵{S#,C#}不能為空值，如果一個教師不教課，則這位教師的姓名及所屬的系名就不能插入表中。2023/3/2041

(3)刪除異常。如果所有學生都退選某一門課，則有關(guān)該門課的其它數(shù)據(jù)(任課教師名及所在系名)也將被刪除。

(4)修改異常。例如，如果改變一門課的任課教師，則需要修改表中的多行記錄，如果部分修改，部分不修改，則會導致數(shù)據(jù)的不一致。上述關(guān)系模式只所以是一個不好的關(guān)系模式，是因為模式中存在部分函數(shù)依賴和傳遞函數(shù)依賴。消除這些部分函數(shù)依賴和傳遞函數(shù)依賴，就可以得到一個比較好的關(guān)系模式。2023/3/20424.4.2第二范式（2NF）（一）定義4.14對于FDW→A，如果存在X?W有X→A成立，那么稱W→A是局部依賴（A局部依賴于W）；否則稱W→A是完全依賴。完全依賴也稱為“左部不可約依賴”。定義4.15如果A是關(guān)系模式R的候選鍵中屬性，那么稱A是R的主屬性；否則稱A是R的非主屬性。定義4.16如果關(guān)系模式R是1NF，且每個非主屬性完全函數(shù)依賴于候選鍵，那么稱R是第二范式（2NF）的模式。如果數(shù)據(jù)庫模式中每個關(guān)系模式都是2NF，則稱數(shù)據(jù)庫模式為2NF的數(shù)據(jù)庫模式。2023/3/20434.4.2第二范式（2NF）（二）

例4.14

設(shè)關(guān)系模式R（S#，C#，GRADE，TNAME，TADDR）的屬性分別表示學生學號、選修課程的編號、成績、任課教師姓名和教師地址等意義。（S#，C#）是R的候選鍵。 R上有兩個FD：（S#，C#）→（TNAME，TADDR）和C#→（TNAME，TADDR），因此前一個FD是局部依賴，R不是2NF模式。此時R的關(guān)系就會出現(xiàn)冗余和異常現(xiàn)象。譬如某一門課程有100個學生選修，那么在關(guān)系中就會存在100個元組，因而教師的姓名和地址就會重復100次。 如果把R分解成R1（C#，TNAME，TADDR）和R2（S#，C#，GRADE）后，局部依賴（S#，C#）→（TNAME，TADDR）就消失了。R1和R2都是2NF模式。

2023/3/20444.4.2第二范式（2NF）（三）

算法4.4

分解成2NF模式集的算法 設(shè)關(guān)系模式R（U），主鍵是W，R上還存在FDX→Z，并且Z是非主屬性和XW，那么W→Z就是一個局部依賴。此時應(yīng)把R分解成兩個模式 R1（XZ），主鍵是X； R2（Y），其中Y=U-Z，主鍵仍是W，外鍵是X（REFERENCESR1）。 利用外鍵和主鍵的聯(lián)接可以從R1和R2重新得到R。 如果R1和R2還不是2NF，則重復上述過程，一直到數(shù)據(jù)庫模式中每一個關(guān)系模式都是2NF為止。2023/3/2045例：是1NF但不是2NF的關(guān)系模式設(shè)有關(guān)系模式如下：

REPROT(S#,C#,TITLE,LNAME,ROOM#,MARKS),其中S#是學號，C#是課程號，TITLE為課程名，LNAME是教師名，ROOM#是教室號，MARKS是分數(shù)。關(guān)系中的一個元組<s,c,t,l,r,m>表示學生s在課程c中的得分為m，課程名為t,由教師l講授，其教室編號為r。

若每門課只由一位教師講授，每位教師只有一個教室，即只在一個教室中講課，則REPORT的函數(shù)依賴如下：(S#,C#)MARKSC#TITLEC#LNAMELNAMEROOM#2023/3/2046關(guān)系模式REPROT(S#,C#,TITLE,LNAME,ROOM#,MARKS)的碼是(S#,C#)，非主屬性TITLE，LNAME和ROOM#對碼是部分函數(shù)依賴，并存在傳遞函數(shù)依賴C#LNAME，LNAMEROOM#。REPORT屬于1NF，不屬于2NF。S#C#MARKSTITLELANMEROOM#圖5-32023/3/2047消除部分函數(shù)依賴為消除部分函數(shù)依賴，將REPORT關(guān)系模式分解為REPORT1和COURSE這二個關(guān)系模式：

REPORT1(S#,C#,MARKS)

函數(shù)依賴是(S#,C#)MARKSCOURSE(C#,TITLE,LNAME,ROOM#)

函數(shù)依賴是C#TITLE,C#LNAME,LNAMEROOM#。

REPORT1和COURSE都消除了對碼的部分函數(shù)依賴，因此都屬于2NF。2023/3/2048一個關(guān)系模式僅僅滿足2NF仍是不夠的，如在關(guān)系模式COURSE(C#,TITLE,LNAME,ROOM#)中，仍存在著插入，刪除和修改異常問題：新來的教師，還沒有分配授課之前，教師的姓名及教室編號都不能加到關(guān)系模式中；如果要修改教室編號，必須修改與教師授課相對應(yīng)的各元組中的教室編號，因為一位教師可能會教多門課。

存在上述這些問題的原因是關(guān)系模式COURSE中存在傳遞函數(shù)依賴，所以要把關(guān)系模式COURSE向第三范式轉(zhuǎn)化，除去非主屬性對碼的傳遞函數(shù)依賴。2023/3/20494.4.3第三范式（3NF）（一）定義4.17如果X→Y，Y→A，且Y→X和A∈Y，那么稱X→A是傳遞依賴（A傳遞依賴于X）。定義4.18如果關(guān)系模式R是1NF，且每個非主屬性都不傳遞依賴于R的候選鍵，那么稱R是第三范式（3NF）的模式。如果數(shù)據(jù)庫模式中每個關(guān)系模式都是3NF，則稱其為3NF的數(shù)據(jù)庫模式。2023/3/20504.4.3第三范式（3NF）（二）例4.15

在例4.14中，R2是2NF模式，而且也已是3NF模式。但R1（C#，TNAME，TADDR）是2NF模式，卻不一定是3NF模式。如果R1中存在函數(shù)依賴C#→TNAME和TNAME→TADDR，那么C#→TADDR就是一個傳遞依賴，即R1不是3NF模式。此時R1的關(guān)系中也會出現(xiàn)冗余和異常操作。譬如一個教師開設(shè)五門課程，那么關(guān)系中就會出現(xiàn)五個元組，教師的地址就會重復五次。如果把R2分解成R21（TNAME，TADDR）和R22（C#，TNAME）后，C#→TADDR就不會出現(xiàn)在R21和R22中。這樣R21和R22都是3NF模式。

2023/3/20514.4.3第三范式（3NF）（三）

算法4.5

分解成3NF模式集的算法 設(shè)關(guān)系模式R（U），主鍵是W，R上還存在FDX→Z。并且Z是非主屬性，ZX，X不是候選鍵，這樣W→Z就是一個傳遞依賴。此時應(yīng)把R分解成兩個模式： R1（XZ），主鍵是X； R2（Y），其中Y=U-Z，主鍵仍是W，外鍵是X（REFERENCESR1）。 利用外鍵和主鍵相匹配機制，R1和R2通過聯(lián)接可以重新得到R。 如果R1和R2還不是3NF，則重復上述過程，一直到數(shù)據(jù)庫模式中每一個關(guān)系模式都是3NF為止。2023/3/20524.4.3第三范式（3NF）（四）定理4.9 如果R是3NF模式，那么R也是2NF模式。定理4.10設(shè)關(guān)系模式R，當R上每一個FDX→A滿足下列三個條件之一時：A∈X（即X→A是一個平凡的FD）；X是R的超鍵； A是主屬性。關(guān)系模式R就是3NF模式。2023/3/20534.4.3第三范式（3NF）（五）圖4.15違反3NF的傳遞依賴的三種情況

2023/3/2054

在前面的例子中，關(guān)系模式：

COURSE(C#,TITLE,LNAME,ROOM#)其中存在非主屬性ROOM#對碼的傳遞依賴，即：

C#LNAME,LNAMEROOM#，因此COURSE不屬于3NF。將COURSE分解為：

COURSE1(C#,TITLE,LNAME)

和

LECTURE(LNAME,ROOM#),

則關(guān)系模式COURSE1和LECTURE中都沒有傳遞函數(shù)依賴，因此COURSE1和LECTURE都屬于3NF。2023/3/2055至此，關(guān)系模式REPORT分解為下列3個屬于3NF的一組關(guān)系模式：REPORT1(S#,C#,MARKS)COURSE1(C#,TITLE,LNAME)

LECTURE(LNAME,ROOM#)和關(guān)系模式REPORT相比，這些關(guān)系模式是對現(xiàn)實世界更加精確的描述。把這3個關(guān)系進行連接，總能重構(gòu)初始的關(guān)系。2023/3/20564.4.4BCNF（Boyce–CoddNF）（一）圖4.16主屬性對候選鍵的傳遞依賴

2023/3/20574.4.4BCNF（Boyce–CoddNF）（二）定義4.19如果關(guān)系模式R是1NF，且每個屬性都不傳遞依賴于R的候選鍵，那么稱R是BCNF的模式。如果數(shù)據(jù)庫模式中每個關(guān)系模式都是BCNF，則稱為BCNF的數(shù)據(jù)庫模式。定理4.11如果R是BCNF模式，那么R也是3NF模式。定義4.19’設(shè)F是關(guān)系模式R的FD集，如果對F中每個非平凡的FDX→Y，都有X是R的超鍵，那么稱R是BCNF的模式。2023/3/20584.4.5分解成BCNF模式集的算法 算法4.6無損分解成BCNF模式集 對于關(guān)系模式R的分解ρ（初始時ρ={R}），如果ρ中有一個關(guān)系模式Ri相對于πRi（F）不是BCNF。據(jù)定義4-20可知，Ri中存在一個非平凡FDX→Y，有X不包含超鍵。此時把Ri分解成XY和Ri－Y兩個模式。重復上述過程，一直到ρ中每一個模式都是BCNF。2023/3/2059例：一個是3NF但不是BCNF的關(guān)系模式設(shè)有關(guān)系模式STJ(S,T,J)，S表示學生，T表示教師，J表示課程。每一教師只教一門課，每門課有若干教師，某一學生選定某門課，就對應(yīng)一個固定的教師，由語義可得到如下的函數(shù)依賴。(S,J)T;(S,T)J;TJ,即學生，所選課程決定授課教師；學生，授課教師決定所選課程；教師決定所授課程；如下圖所示：2023/3/2060則(S，J)，(S，T)都是候選碼；S，T，J都是主屬性。STJ3NF，因為沒有任何非主屬性對候選碼的傳遞依賴或部分依賴。STJBCNF，因為主屬性J傳遞函數(shù)依賴于候選碼S，J。SJTSTJ圖

STJ中的函數(shù)依賴2023/3/2061模式設(shè)計示例例:學生基本情況的關(guān)系模式：STUDENT（SNO，SNAME，AGE，SEX，CLASS，DEP，CNO，CNAME，GRADE，SCORE）該關(guān)系模式的函數(shù)依賴集F={SNOSNAME，SNOAGE，SNOSEX， SNOCLASS，SNODEP，CLASSDEP， CNOCNAME，CNOSCORE， SNO+CNOGRADE}

該模式的碼是(SNO,CNO)

該模式是屬于1NF：滿足的條件是元組的每個分量必須是不可分的數(shù)據(jù)項。不是一個好的關(guān)系模式。同學自己分析為什么是一個不好的關(guān)系模式？2023/3/2062修改設(shè)計使其滿足第二范式2NF關(guān)系模式STUDENT不符合2NF要求。因為其中存在部分函數(shù)依賴。關(guān)系模式STUDENT的主碼是(SNO，CNO)。非主屬性SNAME，AGE，SEX，CLASS，DEP， CNAME，GRADE，SCORE

非主屬性中存在對碼的部分函數(shù)依賴，如，SNOSNAME，CNOCNAME。2023/3/2063消除部分函數(shù)依賴將STUDENT關(guān)系模式分解，消除部分函數(shù)依賴，得到三個關(guān)系模式符合2NF要求：STUDENT2（SNO，SNAME，AGE，SEX，CLASS，DEP）COURSE（CNO，CNAME，SCORE）SC（SNO，CNO，GRADE） 在STUDENT2模式中，仍然存在數(shù)據(jù)冗余，以及插入和刪除異常。2023/3/2064修改設(shè)計使其滿足第三范式3NF關(guān)系模式STUDENT2不滿足第三范式要求，存在傳遞依賴。如SNOCLASSDEP，消除傳遞依賴，分解STUDENT2如下：STUDENT3（SNO，SNAME，AGE，SEX，CLASS）CLASS（CLASS，DEP）2023/3/2065滿足第三范式要求至此，關(guān)系模式STUDENT分解為4個3NF的關(guān)系模式：STUDENT3（SNO，SNAME，AGE，SEX，CLASS）CLASS（CLASS，DEP）COURSE（CNO，CNAME，SCORE）SC（SNO，CNO，GRADE）2023/3/2066修改設(shè)計使其滿足BCNF范式例如，關(guān)系模式課程COUSE(CNO，CNAME，SCORE)，只有一個碼CNO，沒有任何屬性對碼有部分和傳遞函數(shù)以來，所以COUSE3NF。同時，COUSE中CNO是唯一的決定因素，因此COUSEBCNF。2023/3/2067模式分解習題設(shè)有關(guān)系模式R(U,F),其中U={A，B，C，D，E}，F(xiàn)={ABC，BD，DE，CB}，試問R最高為第幾范式，并解釋原因？如果R不是3NF或BCNF，要求將其分解為3NF和BCNF。2023/3/2068關(guān)系R中的函數(shù)依賴如下圖表示R(A,B,C,D,E