第7章關(guān)系數(shù)據(jù)庫規(guī)范化理論

數(shù)據(jù)庫原理與應(yīng)用（第2版）高等院校計算機教材系列第7章關(guān)系數(shù)據(jù)庫規(guī)范化理論7.1函數(shù)依賴7.2關(guān)系規(guī)范化7.3關(guān)系模式分解準(zhǔn)則概述數(shù)據(jù)庫設(shè)計是數(shù)據(jù)庫應(yīng)用領(lǐng)域中的主要研究課題，其主要任務(wù)是創(chuàng)建滿足用戶需求且性能良好的數(shù)據(jù)庫模式。關(guān)系數(shù)據(jù)庫規(guī)范化理論是指導(dǎo)數(shù)據(jù)庫設(shè)計的一個理論指南。概述數(shù)據(jù)的語義不僅表現(xiàn)為完整性約束，對關(guān)系模式的設(shè)計也提出了一定的要求。如何構(gòu)造一個合適的關(guān)系模式，應(yīng)構(gòu)造幾個關(guān)系模式，每個關(guān)系模式由哪些屬性組成等，都是數(shù)據(jù)庫設(shè)計問題，確切地講是關(guān)系數(shù)據(jù)庫的邏輯設(shè)計問題。7.1函數(shù)依賴7.1.1函數(shù)依賴基本概念7.1.2一些術(shù)語和符號7.1.3為什么要討論函數(shù)依賴7.1.1基本概念省=f(城市)：只要給出一個具體的城市值，就會有唯一一個省值和它對應(yīng)，如“武漢市”在“湖北省”，這里“城市”是自變量X，“省”是因變量或函數(shù)值Y。把X函數(shù)決定Y，或Y函數(shù)依賴于X表示為：

X→Y如果有關(guān)系模式R(A1,A2,…,An)，X和Y為{A1,A2,…,An}的子集，則對于關(guān)系R中的任意一個X值，都只有一個Y值與之對應(yīng)，則稱X函數(shù)決定Y，或Y函數(shù)依賴于X。示例例1：對學(xué)生關(guān)系模式Student（Sno,Sname,Sdept,Sage）有以下依賴關(guān)系：

Sno→Sname,Sno→Sdept,Sno→Sage

例2：SC（Sno,Cno,Grade）（Sno,Cno）→Grade

函數(shù)依賴定義設(shè)有關(guān)系模式R(A1,A2,…,An)，X和Y均為{A1,A2,…,An}的子集，r是R的任一具體關(guān)系，t1、t2是r中的任意兩個元組；如果由t1[X]=t2[X]可以推導(dǎo)出t1[Y]=t2[Y]，則稱X函數(shù)決定Y，或Y函數(shù)依賴于X，記為X→Y。7.1.2一些術(shù)語和符號（1）如果X→Y，但Y不包含于X，則稱

X→Y是非平凡的函數(shù)依賴。（2）如果X→Y，但Y包含于X，則稱

X→Y是平凡的函數(shù)依賴。

若無特別聲明，我們討論的都是非平凡的函數(shù)依賴。（3）如果X→Y，則X稱為決定因子。術(shù)語和符號（續(xù)）（4）如果X→Y，并且Y→X，則記作

X←→Y。（5）如果X→Y，并且對于X的一個任意真子集X‘都有X‘—/→Y，則稱Y完全函數(shù)依賴于X，記作：

如果X‘→Y成立，則稱Y部分函數(shù)依賴于X，記作：

（6）如果X→Y（非平凡函數(shù)依賴，并且Y—/→X）、Y→Z，則稱Z傳遞函數(shù)依賴于X。示例例1：有關(guān)系模式

SC（Sno,Sname,Cno,Credit,Grade）主鍵為（Sno,Cno），則函數(shù)依賴關(guān)系有：

示例例2：假設(shè)有關(guān)系模式S（Sno,Sname,Dept,Dept_master）假設(shè)一個系只有一個主任，主鍵為Sno，則函數(shù)依賴關(guān)系有：7.1.3為什么要討論函數(shù)依賴？有關(guān)系模式：S-L-C(Sno,Sname,Ssex,Sdept,SLOC,Cno,Grade)

其中各屬性分別為：學(xué)號、姓名、性別、學(xué)生所在系、學(xué)生所住宿舍樓、課程號和考試成績。

假設(shè)每個系的學(xué)生都住在一棟樓里，（Sno,Cno）為主鍵*13數(shù)據(jù)示例SnoSnameSsexSdeptSlocCnoGrade9512101李勇男計算機系2公寓C01909512101李勇男計算機系2公寓C02869512101李勇男計算機系2公寓C06NULL9512102劉晨男計算機系2公寓C02789512102劉晨男計算機系2公寓C04669521102吳賓女信息系1公寓C01829521102吳賓女信息系1公寓C02759521102吳賓女信息系1公寓C04929521102吳賓女信息系1公寓C05509521103張海男信息系1公寓C02689521103張海男信息系1公寓C06NULL9531101錢小平女?dāng)?shù)學(xué)系1公寓C01809531101錢小平女?dāng)?shù)學(xué)系1公寓C05959531102王大力男數(shù)學(xué)系1公寓C0585存在問題數(shù)據(jù)冗余問題數(shù)據(jù)更新問題數(shù)據(jù)插入問題數(shù)據(jù)刪除問題結(jié)論S-L-C關(guān)系模式不是一個好的模式。如何改造這個關(guān)系模式并克服以上種種問題是關(guān)系規(guī)范化理論要解決的問題，也是討論函數(shù)依賴的原因。解決方法:模式分解，即把一個關(guān)系模式分解成兩個或多個關(guān)系模式，在分解的過程中消除那些“不良”的函數(shù)依賴，從而獲得良好的關(guān)系模式7.2關(guān)系規(guī)范化7.2.1關(guān)系模式中的碼7.2.2范式7.2.1關(guān)系模式中的碼1．候選碼：設(shè)K為R(U,F)中的屬性或?qū)傩越M，若Kf→U，則K為R候選碼。（K為決定R全部屬性值的最小屬性組）。2.主碼：關(guān)系R(U,F)中可能有多個候選碼，則選其中一個作為主碼。3.全碼：候選碼為整個屬性組。 主屬性與非主屬性：4.在R(U,F)中，包含在任一候選碼中的屬性稱為主屬性，不包含在任一候選碼中的屬性稱為非主屬性。示例1有關(guān)系模式：學(xué)生（學(xué)號，姓名，性別，身份證號，年齡，所在系）候選碼：學(xué)號，身份證號。主碼：“學(xué)號”或“身份證號”。主屬性：學(xué)號，身份證號。非主屬性：姓名，性別，年齡，所在系。示例2有關(guān)系模式：選課（學(xué)號，課程號，考試次數(shù)，成績）設(shè)一個學(xué)生對一門課程可以有多次考試，每一次考試有一個考試成績。候選碼：（學(xué)號，課程號，考試次數(shù)），也為主碼。主屬性：學(xué)號，課程號，考試次數(shù)非主屬性：成績。示例3有關(guān)系模式：授課（教師號，課程號，學(xué)年）語義：一個教師在一個學(xué)年可以講授多門不同的課程，可以在不同學(xué)年對同一門課程講授多次，但不能在同一個學(xué)年對同一門課程講授多次。一門課程在一個學(xué)年可以由多個不同的教師講授，同一個學(xué)年可以開設(shè)多門課程，同一門課程可以在不同學(xué)年開設(shè)多次。候選碼：（教師號，課程號，學(xué)年）主碼：同候選碼。主屬性：教師號，課程號，學(xué)年非主屬性：無稱這種候選碼為全部屬性的表為全碼表7.2.1關(guān)系模式中的碼（續(xù)）外碼：用于關(guān)系表之間建立關(guān)聯(lián)的屬性（組）。定義：若R（U，F(xiàn)）的屬性（組）X（X屬于U）是另一個關(guān)系S的主碼，則稱X為R的外碼。（X必須先被定義為S的主碼）。7.2.2范式關(guān)系數(shù)據(jù)庫中的關(guān)系要滿足一定的要求，滿足不同程度要求的為不不同的范式。第一范式第一范式：不包含重復(fù)組的關(guān)系。第二范式2.第二范式:如果R(U,F)∈1NF，并且R中的每個非主屬性都完全函數(shù)依賴于主碼，則R(U,F)∈2NF例:S-L-C（Sno,Sname,Ssex,Sdept,Sloc,Cno,Grade）∵有：(Sno,Cno)→Sname∴S-L-C不是2NF。P分解辦法用組成主碼的屬性集合的每一個子集作為主碼構(gòu)成一個關(guān)系模式。將依賴于這些主碼的屬性放置到相應(yīng)的關(guān)系模式中。最后去掉只由主碼的子集構(gòu)成的關(guān)系模式。分解示例對于S-L-C表，首先分解為如下形式的三張表：

S-L（Sno，…）

C（Cno，…）

S-C（Sno,Cno,…）將依賴于這些主碼的屬性放置到相應(yīng)的表中:

S-L（Sno，Sname,Ssex,Sdept,Sloc）

C（Cno）

S-C（Sno,Cno,Grade）去掉只由主碼的子集構(gòu)成的表，最終分解為：S-L（Sno,Sname,Ssex,Sdept,Sloc）S-C（Sno,Cno,Grade）

S-L(Sno,Sname,Ssex,Sdept,Sloc)存在問題數(shù)據(jù)冗余：有多少個學(xué)生就有多少個重復(fù)的Sdept和SLOC；插入異常：當(dāng)新建一個系時，若還沒有招收學(xué)生，則無法插入；SnoSnameSsexSdeptSloc9512101李勇男計算機系2公寓9512102劉晨男計算機系2公寓9521102吳賓女信息系1公寓9521103張海男信息系1公寓9531101錢小平女?dāng)?shù)學(xué)系1公寓9531103王大力女?dāng)?shù)學(xué)系1公寓第三范式

定義：如果R(U,F)∈2NF，并且所有非主屬性都不傳遞依賴于主碼，則R(U,F)∈3NF。對S-L(Sno,Sname,Ssex,Sdept,SLOC)∵Sno傳遞→SLOC,∴不是3NF分解過程對于不是候選碼的每個決定因子，從表中刪去依賴于它的所有屬性；新建一個表，新表中包含在原表中所有依賴于該決定因子的屬性；將決定因子作為新表的主碼。S-L分解后的關(guān)系模式為：S-D（Sno,Sname,Ssex,Sdept），主碼為SnoS-L（Sdept,Sloc），主碼為Sdept分析S-D和S-L關(guān)系模式S-D（Sno,Sname,Ssex,Sdept）S-L（Sdept,Sloc）對S-D，有：SnoSname，SnoSsex，SnoSdept，因此S-D是3NF的。對S-L，有：SdeptSloc，因此S-L也是3NF的。S-L-C最終分解結(jié)果S-D（Sno,Sname,Ssex,Sdept）Sno為主碼Sdept為引用S-L關(guān)系模式的外碼S-L（Sdept,Sloc）Sdept為主碼沒有外碼S-C（Sno,Cno,Grade）（Sno，Cno）為主碼Sno為引用S-D關(guān)系模式的外碼BCNF例：關(guān)系模式：CSZ（City，Street，Zip）語義：城市和街道可以決定郵政編碼，郵政編碼可以決定城市。候選碼：(City，Street），(Street，Zip)F:{(City，Street）→Zip，Zip→City}是3NF但存在的問題:插入異常,刪除異常BCNF定義若R∈1NF，且能決定其它屬性取值的屬性（組）必定包含候選碼，則R∈BCNF。

如果一個關(guān)系的每個決定因素都是候選碼，則其是BCNF。

如果一個關(guān)系的每個函數(shù)依賴的左部都是候選碼，則其是BCNF。

如果R∈3NF，并且不存在主屬性對非主屬性的函數(shù)依賴，則其是BCNF。BCNF和3NF的關(guān)系若R∈BCNF，則R∈3NF若R∈3NF，則R不一定屬于BCNF可能違反BCNF的情形關(guān)系中包含兩個（或更多）復(fù)合候選碼。候選碼有重疊，通常至少有一個重疊的屬性。規(guī)范化舉例設(shè)有關(guān)系模式：Student（學(xué)號，姓名，導(dǎo)師號，導(dǎo)師名，課程號，課程說明，成績）語義：一名學(xué)生只有一個導(dǎo)師，學(xué)生可選多門課。將其規(guī)范化成3NF的。1．此表是1NF，其函數(shù)依賴為： 學(xué)號p→姓名，學(xué)號p→導(dǎo)師號，學(xué)號p→導(dǎo)師名，課程號p→課程說明,(學(xué)號，課程號)→成績主碼為（學(xué)號，課程號）存在部分函數(shù)依賴關(guān)系，不是2NF，首先將其分解為2NF。學(xué)生（學(xué)號，姓名，導(dǎo)師號，導(dǎo)師名），課程（課程號，課程說明），成績（學(xué)號，課程號，成績）均為2NF2．判是否為3NF“學(xué)生”表不是3NF，其函數(shù)依賴為： 學(xué)號→姓名，學(xué)號→導(dǎo)師號，導(dǎo)師號p→導(dǎo)師名，∴學(xué)號傳遞→導(dǎo)師名消除依賴于決定者的屬性，把它們放在一個單獨的表中，得到：學(xué)生（學(xué)號，姓名，導(dǎo)師號），導(dǎo)師（導(dǎo)師號，導(dǎo)師名）7.3關(guān)系模式的分解準(zhǔn)則模式分解要滿足：模式分解具有無損連接性；模式分解能夠保持函數(shù)依賴。無損連接是指分解后的關(guān)系通過自然連接可以恢復(fù)成原來的關(guān)系，即通過自然連接得到的關(guān)系與原來的關(guān)系相比，既不多出信息、又不丟失信息。

保持函數(shù)依賴分解是指在模式的分解過程中，函數(shù)依賴不能丟失的特性，即模式分解不能破壞原來的語義。關(guān)系模式的分解準(zhǔn)則（續(xù)）例：S-D-L（Sno，Dept，Loc）有函數(shù)依賴：

Sno→Dept，Dept→Loc

不是第三范式的。至少可以有三種分解方案，分別為：方案1：S-L（Sno，Loc），D-L（Dept，Loc）方案2：S-D（Sno，Dept），S-L（Sno，Loc）方案3：S-D（Sno，Dept），D-L（Dept，Loc）這三種分解方案得到的關(guān)系模式都是第三范式的，那么如何比較這三種方案的好壞呢？由此在將一個關(guān)系模式分解為多個關(guān)系模式時除了提高規(guī)范化程度之外，還需要考慮其他的一些因素。關(guān)系模式的分解準(zhǔn)則（續(xù)）將一個關(guān)系模式R<U，F(xiàn)>分解為若干個關(guān)系模式R1<U1，F(xiàn)1>，R2<U2，F(xiàn)2>，…，Rn<Un，F(xiàn)n>，意味著將存儲在一張二維表r中的數(shù)據(jù)分散到了若干個二維表r1，r2，…，rn中。這樣的分解應(yīng)該不丟失信息，即能通過對關(guān)系r1，r2，…，rn的自然連接運算重新得到關(guān)系r中的所有信息。事實上，將關(guān)系r投影為r1，r2，…，rn時不會丟失信息，關(guān)鍵是對r1，r2，…，rn做自然連接時可能產(chǎn)生一些r中原來沒有的元組，從而無法區(qū)別哪些元組是r中原來有的，即數(shù)據(jù)庫中應(yīng)該存在的數(shù)據(jù)，哪些是不應(yīng)該有的。在這個意義上就丟失了信息。關(guān)系模式的分解準(zhǔn)則（續(xù)）這三種分解方案是否都滿足分解要求呢？假設(shè)此關(guān)系模式的數(shù)據(jù)如表所示，此關(guān)系用r表示。Sno

Dept

Loc

S01D1L1S02D2L2S03D3L3S04D4L4關(guān)系模式的分解準(zhǔn)則（續(xù)）若按方案1將S-D-L投影到S-L和D-L的屬性上，得到如左邊兩個表所示的關(guān)系。做自然連接得到結(jié)果如右表所示。Sno

Loc

S01L1S02L2S03L3S04L4Dept

Loc

D1L1D2L2D3L3D4L4Sno

Dept

Loc

S01D1L1S01D3L1S02D2L2S03D2L2S04D1L1S04D