關(guān)系規(guī)范化樣例

1、第三章 關(guān)系規(guī)范化理論關(guān)系的規(guī)范是關(guān)系數(shù)據(jù)模型設(shè)計(jì)中的一個(gè)非常重要的問(wèn)題，它可以指導(dǎo)我們?cè)O(shè)計(jì)出好的關(guān)系。設(shè)計(jì)和構(gòu)造合理的關(guān)系，使之能準(zhǔn)確地反映現(xiàn)實(shí)世界并有利于應(yīng)用和具體操作，是關(guān)系的規(guī)范和探討的問(wèn)題。所以有人把關(guān)系的規(guī)范化理論稱(chēng)為設(shè)計(jì)數(shù)據(jù)庫(kù)的理論。第一節(jié) 關(guān)系中的鍵一、候選鍵（candidate key）凡在一個(gè)關(guān)系中具有主鍵特性的屬性或?qū)傩越M，均稱(chēng)為候選鍵。因?yàn)樗鼈兌季哂斜贿x為主鍵的條件，所以一個(gè)關(guān)系可能有多個(gè)候選鍵，但只能選其中的一個(gè)為主鍵。候選鍵中包含的屬性，期于的屬性稱(chēng)為非主屬性。例：在職工關(guān)系ZG（姓名，性別，年齡）中，增加一個(gè)屬性：職工號(hào)，即得到一個(gè)新關(guān)系：ZG（職工號(hào)，姓名，性別

2、，年齡）又假定職工號(hào)與職工姓名是一一對(duì)應(yīng)的，即沒(méi)有兩個(gè)職工的姓名相同，則“職工號(hào)”和“姓名”兩個(gè)都是候選鍵。 二、替代鍵(alternate key)對(duì)于某一指定的關(guān)系可能存在多個(gè)候選鍵，但只能選其中的一個(gè)為主鍵。在確定主鍵后，其余的候選鍵都是替代鍵，替代鍵在需要時(shí)可代替主鍵。二、 外來(lái)鍵(foreign key)但關(guān)系中的某些屬性系由另一個(gè)關(guān)系的主鍵構(gòu)成時(shí)，則該屬性（或?qū)傩越M）稱(chēng)為外來(lái)鍵。第二節(jié) 函數(shù)依賴(lài)一、函數(shù)依賴(lài)定義1：設(shè)R是一個(gè)關(guān)系，X和Y是R中的兩個(gè)屬性。若R中X的任何一個(gè)值，僅有一個(gè)Y的值與之對(duì)應(yīng)，則稱(chēng)為R的屬性Y函數(shù)依賴(lài)（FD）于屬性X，記作XàY。例如：在描述船員的關(guān)

3、系CREW（NO，NAME，AGE，JOB，PAY）它表示由任一船員號(hào)NO，僅能找到一個(gè)姓名、一個(gè)年齡、一個(gè)。定義中的屬性X可以是復(fù)合屬性，例如SP（S#，P#，QTY-USED）中的（S#，P#）二、完全函數(shù)依賴(lài)和部分函數(shù)依賴(lài)定義2：如果屬性Y函數(shù)依賴(lài)于復(fù)合屬性X，而且不與X的任一子集X函數(shù)依賴(lài)（XàY ），則稱(chēng)屬性Y完全函數(shù)依賴(lài)（FFD）于復(fù)合函數(shù)X，記作XàY。若XàY但不是完全函數(shù)依賴(lài)，則稱(chēng)Y部分函數(shù)依賴(lài)于X。例：在關(guān)系SP（S#，P#，QTY-USED）中QTY-USED表示部件P#在S#船上使用的數(shù)量，只有同時(shí)指定S#和P#，才能說(shuō)明某部件在某船上的用

4、量，缺一不可，因此QTY-USED完全函數(shù)依賴(lài)于（S#，P#）。三、傳遞函數(shù)依賴(lài)定義3：如果X，Y，Z是R中的三個(gè)屬性（或?qū)傩詮?fù)合）若XàY， YàX， YàZ，則稱(chēng)Z對(duì)X傳遞函數(shù)依賴(lài)。例如：S（S#，SNAME，CITY，POSTCODE）中S#àCITY，CITYà S#，若CITYà POSTCODE，則稱(chēng)POSTCODE傳遞依賴(lài)于S#。例：設(shè)有下列關(guān)系GPD（零件號(hào)，零件名，設(shè)計(jì)人，設(shè)計(jì)人等級(jí)）因?yàn)榱慵?hào)零件名 零件號(hào)設(shè)計(jì)人 設(shè)計(jì)人設(shè)計(jì)人等級(jí) 故 零件號(hào)設(shè)計(jì)人等級(jí)第三節(jié) 規(guī)范化和范式 一、規(guī)范化問(wèn)題的提出關(guān)系模型的特點(diǎn)是使用二

5、維表來(lái)表示現(xiàn)實(shí)世界的實(shí)體集合和屬性關(guān)系，這樣容易歷屆和被用戶所接受，然而并不是所有二維表都能構(gòu)成關(guān)系模型，見(jiàn)表農(nóng)產(chǎn)品統(tǒng)計(jì)表省市總產(chǎn)值產(chǎn)量畝產(chǎn)小麥稻谷小麥稻谷北京河北職工情況登記表職工號(hào)姓名級(jí)別工資學(xué)歷畢業(yè)時(shí)間001張技1083中專(zhuān)大學(xué)研究生195719681981002李技1262以上兩張二維表就不能構(gòu)成關(guān)系，因?yàn)槌霈F(xiàn)了子項(xiàng)，那么具備那些條件的二維表才能稱(chēng)為關(guān)系呢？在關(guān)系模式中，要求二維表具有以下性質(zhì)：（1） 二維表中的每一列都是不能分割的基本數(shù)據(jù)項(xiàng)，且無(wú)重復(fù)組。（2） 同一關(guān)系中，沒(méi)有相同的列出現(xiàn)。 描述一個(gè)實(shí)體，不需要重復(fù)出現(xiàn)相同的屬性名（3） 同一關(guān)系中，各行的內(nèi)容不能完全相同完全相同的

6、行，實(shí)無(wú)意義滿足上述條件的關(guān)系，稱(chēng)為規(guī)范化的關(guān)系，否則叫非規(guī)范化形式，這種“形式”即不能被定義成關(guān)系模型，又不能被關(guān)系型的DBMS所接受，因此要對(duì)非規(guī)范化的表格（關(guān)系）進(jìn)行規(guī)范化處理。所謂規(guī)范化處理，就是逐步用更單純、更規(guī)則的關(guān)系來(lái)取代原有關(guān)系的過(guò)程。 二、規(guī)范化的意義規(guī)范化處理的目的不僅將關(guān)系的“概念”單一化，使每一個(gè)數(shù)據(jù)項(xiàng)使一個(gè)簡(jiǎn)單的基本項(xiàng)，又無(wú)重復(fù)組。還有以下意義：（1）解決冗余度問(wèn)題所謂“冗余”問(wèn)題是指表格中的數(shù)據(jù)重復(fù)。例：船與船員之間的1：N聯(lián)系見(jiàn)表船與船員SHIPS#SNAMENSPS#SNAMENONAMEAGES1DQS1DQ1001S2XHS1DQ1004S1DQ1020SC

7、S#NONAMES2XH1025S11001S11004S11020這樣重復(fù)太多，一條船有多個(gè)船員，船號(hào)與船名就要重復(fù)存儲(chǔ)多次，如果將船的有關(guān)數(shù)據(jù)分開(kāi)存儲(chǔ)，分為SHIP和SC兩個(gè)表，在SC中存放船號(hào)與船名數(shù)據(jù)，則重復(fù)的僅僅是船號(hào)，其余的重復(fù)都消除了。為了減少甚至消除重復(fù)，將關(guān)系進(jìn)行分離，正是逐步規(guī)范化的重要一步。（2）消除多義性問(wèn)題多義性是指關(guān)系中某些屬性含義不清或有多種可能的含義。例：船部件這個(gè)關(guān)系 SP（S#，P#，QTY） 其中數(shù)量QTY到底是說(shuō)明S與P之間的聯(lián)系，即某船需要某個(gè)部件多少個(gè)，還是僅僅說(shuō)明P為倉(cāng)庫(kù)中現(xiàn)存某個(gè)部件多少個(gè)呢？在這個(gè)關(guān)系中是確定不了的，如果QTY僅僅表示部件的庫(kù)存

8、量，則將關(guān)系SP分離，使用關(guān)系PQ（P#，QTY）來(lái)描述就不再含糊不清了。（3）解決操作可行性及提高操作方便性指對(duì)數(shù)據(jù)的插入、刪除與修改是否可行，是否方便例：職工編號(hào)，姓名，工資等級(jí)，工資假如要插入新的工資等級(jí)和工資額,例如9-110元,由于沒(méi)有對(duì)應(yīng)的職工編號(hào)、姓名，無(wú)法插入。假如要?jiǎng)h除E1這一行，則10級(jí)100元也丟失。修改也隨之都修改。NONAMESTATUSSALE1陳10100E2李8120E3嚴(yán)1280E9王8120 引起上述問(wèn)題的原因，是非主屬性之間的依賴(lài)關(guān)系所致。 這個(gè)關(guān)系中各個(gè)屬性之間的對(duì)應(yīng)關(guān)系可用下圖表示，NO為關(guān)鍵字，即主屬性，其余屬性為非主屬性。 EMP（NO，NAME，

9、STATUS，SAL）箭頭表示屬性間的對(duì)應(yīng)關(guān)系，即任意一個(gè)職工號(hào)，僅能在表中找到一個(gè)姓名NAME與之對(duì)應(yīng)，任何一個(gè)非主屬性SAL函數(shù)依賴(lài)于非主屬性STATUS。如果要從這個(gè)關(guān)系中消除非主屬性之間的依賴(lài)關(guān)系，可將表改為兩個(gè)關(guān)系 EMP（NO，NAME，STATUS） SS（STATUS，SAL）這樣插入數(shù)據(jù)9級(jí)、110元就可在SS中進(jìn)行。由上例可見(jiàn)，通過(guò)適當(dāng)“分離”可以消除非主屬性之間的依賴(lài)性，如何進(jìn)行分離或合并，使得新的一組關(guān)系模式既能反映現(xiàn)實(shí)世界，又能排除多義性，控制冗余度，并方便實(shí)現(xiàn)數(shù)據(jù)操作，正是我們研究規(guī)范化問(wèn)題的目的所在。三、 系規(guī)范化的表述所謂關(guān)系的規(guī)范化，是指滿足某些條件后的關(guān)系，

10、通常按屬性間依賴(lài)情況來(lái)區(qū)分關(guān)系規(guī)范化的程度，并義范式來(lái)表述（NORMAL FORMS）范式又分為n級(jí)，有1NF，2NF，3NF等等，為了判斷一個(gè)關(guān)系屬于哪一級(jí)范式，引入函數(shù)依賴(lài)這一概念。所有規(guī)范化的關(guān)系起碼是第一范式，在第一范式中進(jìn)一步滿足一些要求的關(guān)系為第二范式，依次類(lèi)推。各種形式的范式在關(guān)系數(shù)據(jù)哭系統(tǒng)中都允許存在，但為了更方便于數(shù)據(jù)處理，通常要把低級(jí)范式分解為若干個(gè)3NF或BCNF，下面給出各范式的概念。1） 第一范式（1NF） 關(guān)系R中，每個(gè)分量都是不可分割的。2） 第二范式（2NF） 若關(guān)系R滿足1NF，且每個(gè)非主屬性完全函數(shù)依賴(lài)于關(guān)鍵字。3） 第三范式（3NF） 若關(guān)系R滿足2NF，

11、且每個(gè)非主屬性非傳遞依賴(lài)于關(guān)鍵字。4） 加強(qiáng)第三范式（BCNF） 若關(guān)系R滿足3NF，且所有主屬性和非主屬性既非部分依賴(lài)關(guān)鍵字，也非傳遞依賴(lài)于關(guān)鍵字。下面舉例說(shuō)明逐步規(guī)范化的方法與過(guò)程。例：已知一張購(gòu)物登記表，要求規(guī)范到BCNF范式的程度，以便被關(guān)系型DBMS所接受。購(gòu)物登記顧客姓名商品名稱(chēng)數(shù)量單價(jià)付款工作單位顧客地址電話A彩電11400元1400元××廠L市 38540B手電筒鋼筆12035元2.2元47.50元××學(xué)校P市 28266C電冰箱手電筒鋼筆1121530元3.5元2.2元1537.9元××機(jī)關(guān)L市53834（1） 分析

12、已知表，來(lái)決定是否需要進(jìn)行規(guī)范化處理通過(guò)觀察可知，表不能直接被關(guān)系型DBMS所接受，因?yàn)樗嬖谠S多問(wèn)題。1） 在關(guān)系模型中對(duì)關(guān)系的最起碼要求，應(yīng)該滿足第一范式，表顯然不滿足這個(gè)條件。2） 在數(shù)據(jù)操作上將會(huì)出現(xiàn)下列問(wèn)題： 刪除異常假如顧客A不購(gòu)買(mǎi)彩電，那么表中的記錄A刪除時(shí)就會(huì)將商品名稱(chēng)、單價(jià)等同時(shí)刪除，此時(shí)彩電價(jià)格也無(wú)從查找。 插入異常如顧客A想要購(gòu)買(mǎi)洗衣機(jī)，不但要填上洗衣機(jī)的名稱(chēng)與價(jià)格，還要填上有關(guān)顧客A的信息（工作單位、地址、電話）顯然是重復(fù)的。 數(shù)據(jù)的冗余量大在有多個(gè)顧客購(gòu)買(mǎi)同一商品的情況下，就使這一商品的名稱(chēng)和單價(jià)多次重復(fù)出現(xiàn)在數(shù)據(jù)庫(kù)中，造成數(shù)據(jù)的大量冗余。3） 非獨(dú)立數(shù)據(jù)存在付款項(xiàng)目

13、是由數(shù)量*單價(jià)得來(lái)的，在此可以去掉，基于上述情況，必須對(duì)表進(jìn)行規(guī)范化處理。 （2）利用規(guī)范化工具逐步解決表中的數(shù)據(jù)結(jié)構(gòu)所存在的問(wèn)題首先，去掉表中的非獨(dú)立項(xiàng)，變成滿足第一范式的要求的關(guān)系命名為RR 購(gòu)物登記表顧客姓名商品名稱(chēng)數(shù)量單價(jià)工作單位顧客地址電話A彩電11400元××廠L市38540B手電筒135元××學(xué)校P市28266B鋼筆202.2元××學(xué)校P市28266C電冰箱11530元××機(jī)關(guān)L市53834C手電筒13.5元××機(jī)關(guān)L市53834C鋼筆22.2元××機(jī)關(guān)L市53

14、834顯然R是1NF，從而解決了第1）個(gè)問(wèn)題進(jìn)一步規(guī)范化過(guò)程如下步驟進(jìn)行：第一步 語(yǔ)義分析 每一名顧客有一個(gè)工作單位、住址、電話； 每種商品有一種價(jià)格 每個(gè)顧客所購(gòu)物品有一定數(shù)量； 付款=單價(jià)*數(shù)量（非獨(dú)立項(xiàng)可去掉）為了討論方便，將表種屬性用字母簡(jiǎn)記顧客姓名 A 單價(jià) D商品名稱(chēng) B 顧客工作單位 E數(shù)量 C 顧客地址 F 顧客電話 G第二步 找出函數(shù)依賴(lài)集 FD有語(yǔ)義分析，根據(jù)函數(shù)依賴(lài)的含義，可以的出下面一組函數(shù)依賴(lài)關(guān)系。 AàE ,AàF,AàG BàD ABàC故FD=AàE，AàF，AàG，BàD

15、，ABàC 第三步 畫(huà)出函數(shù)依賴(lài)圖 首先從函數(shù)依賴(lài)集中，選出一組屬性作為關(guān)鍵字，這里選關(guān)鍵字AB，分析非主屬性對(duì)關(guān)鍵字依賴(lài)的情況。 （A，B）-f->C 表明屬性C對(duì)關(guān)鍵字（A，B）為函數(shù)依賴(lài) (A,B)-P->E 表明屬性E對(duì)關(guān)鍵字（A，B）為部分函數(shù)依賴(lài) (A,B)-P->F (A,B)-P->G(A,B)-P->D （理由同上）畫(huà)出關(guān)系R的函數(shù)依賴(lài)圖ECAFGDB從圖中看出，非主屬性與關(guān)鍵字的關(guān)系，哪些是完全函數(shù)依賴(lài)，哪些是部分函數(shù)依賴(lài)，根據(jù)范式的定義，下面可以逐步再進(jìn)行規(guī)范化。E第四步 去掉非主屬性對(duì)關(guān)鍵字的部分依賴(lài)關(guān)系，得到一組新的關(guān)系函數(shù)依賴(lài)

16、圖。DBACAFGB 于是R得到一組新的關(guān)系R1R2R3的集合三個(gè)關(guān)系表R1顧客關(guān)系顧客姓名工作單位顧客地址電話A××廠L市38540B××學(xué)校P市28266C××機(jī)關(guān)L市53834R2商品關(guān)系商品名稱(chēng)單價(jià)彩電1400元手電筒35元鋼筆2.2元電冰箱1530元R3購(gòu)物關(guān)系顧客姓名商品名稱(chēng)數(shù)量A彩電1B手電筒1B鋼筆20C電冰箱1C手電筒1C鋼筆2R1的關(guān)鍵字為顧客姓名R2的關(guān)鍵字為商品姓名R3的關(guān)鍵字為顧客姓名 商品姓名的組合 關(guān)系R1 R2和R3均屬于BCNF，這是因?yàn)樗兄鲗傩院头侵鲗傩约确遣糠忠蕾?lài)關(guān)鍵字，也非傳遞依賴(lài)關(guān)鍵字。至此

17、，規(guī)范化處理完畢上述的規(guī)范化處理結(jié)果，事實(shí)上已消除了前面提到的刪除異常、插入異常和數(shù)據(jù)冗余的問(wèn)題。.假如顧客不購(gòu)買(mǎi)彩電，只是合理地刪除R3中一個(gè)記錄，不會(huì)影響R1中的顧客信息，也不會(huì)影響R2中的商品信息。.假如顧客A購(gòu)買(mǎi)洗衣機(jī)時(shí)，在R3中填寫(xiě)數(shù)量，而價(jià)格在R2中填寫(xiě)，與顧客信息無(wú)關(guān)。.在多個(gè)顧客購(gòu)買(mǎi)同一種商品的情況下，因商品名稱(chēng)與價(jià)格R2在中，不會(huì)重復(fù)出現(xiàn)多次，消除了冗余第四節(jié) 關(guān)系模式的分解 分解是提高關(guān)系范式等級(jí)的重要方法，以下通過(guò)一個(gè)事例說(shuō)明模式分解的一般方法和對(duì)分解的要求。例：已知關(guān)系S（學(xué)號(hào)，班級(jí)，班主任）2NF下圖顯示了它包含的內(nèi)容和屬性間的依賴(lài)關(guān)系，試將S分解為3NF的新關(guān)系學(xué)號(hào)

18、班級(jí)班級(jí)班主任5101520253035財(cái)9241財(cái)9241管9235管9235電9241電9241電9241李力李力學(xué)號(hào)李力李力班主任丁一丁一丁一這里有三種不同的分解法，即 S S-C（學(xué)號(hào)，班級(jí)）C-M（班級(jí)，班主任） S S-C（學(xué)號(hào)，班級(jí)）S-M（學(xué)號(hào)，班主任） S S-M（學(xué)號(hào)，班主任）C-M（班級(jí)，班主任） 三種方案得出的新關(guān)系全是3NF。但分解的質(zhì)量卻大有差異。以下結(jié)合對(duì)分解質(zhì)量的要求，對(duì)這三種方案作一比較。1.分解必須是無(wú)損的，即不應(yīng)在分解丟失信息在上例中，第種方案就不能保證無(wú)損分解，下圖顯示了這一方案得出的兩個(gè)關(guān)系。由于財(cái)9241班和管9335班的班主任是同一個(gè)人，分解后將無(wú)法分辨5，10，15，20號(hào)各屬于哪一個(gè)班。學(xué)號(hào)班主任班級(jí)班主任5101520253035李力李力李力李力丁一丁一丁一財(cái)9241管9235電9241李力李力丁一2. 分解后的新關(guān)系應(yīng)相互獨(dú)立，對(duì)一個(gè)關(guān)系的更改，不會(huì)影響另一關(guān)系試比較一上的、兩種方案，設(shè)15好同學(xué)從管92