第5講 關(guān)系完整性規(guī)則與規(guī)范化要求

1、2 關(guān)系模式的規(guī)范化理論與方法（重點(diǎn)與難點(diǎn)）1 關(guān)系規(guī)范化的作用（了解） 1 關(guān)系完整性規(guī)則（掌握） 要求學(xué)生掌握關(guān)系模式的3個(gè)完整性規(guī)則 要求學(xué)生了解關(guān)系模式的設(shè)計(jì)問(wèn)題，掌握函數(shù)依賴(lài)和多值依賴(lài)的定義和含義，能夠應(yīng)用關(guān)系模式的范式及關(guān)系模式的分解特性解決實(shí)際的關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)問(wèn)題 本講的理論性和應(yīng)用性都比較強(qiáng)，學(xué)生應(yīng)從概念著手，弄清概念之間的聯(lián)系和作用。了解關(guān)系數(shù)據(jù)庫(kù)規(guī)范化理論及其在數(shù)據(jù)庫(kù)設(shè)計(jì)中的作用。 本講的重點(diǎn)是函數(shù)依賴(lài)和范式，學(xué)生必須掌握這些概念并能在實(shí)際問(wèn)題中加以運(yùn)用，為以后設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)奠定一個(gè)良好基礎(chǔ)。關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)主要是關(guān)系模式設(shè)計(jì)。關(guān)系模式設(shè)計(jì)的好壞將直接影響數(shù)據(jù)庫(kù)的質(zhì)量。要求設(shè)

2、計(jì)一個(gè)教學(xué)管理數(shù)據(jù)庫(kù)，希望從該數(shù)據(jù)庫(kù)中經(jīng)常得到下面的有關(guān)信息：學(xué)生學(xué)號(hào)，學(xué)生姓名，年齡，性別，系別，系主任姓名，學(xué)生學(xué)習(xí)的課程號(hào)，該課程的成績(jī)等。該怎樣設(shè)計(jì)該數(shù)據(jù)庫(kù)的關(guān)系模式？S S( (Sno,Sname,Sage,Sex,Dept,DN,Cno,Cname,ScoreSno,Sname,Sage,Sex,Dept,DN,Cno,Cname,Score) )5引例：下表關(guān)系模型存在如下4方面的問(wèn)題。插入異常 刪除異常數(shù)據(jù)冗余更新異常訂貨單號(hào)供應(yīng)商代碼供應(yīng)商名稱(chēng)聯(lián)系人商品名稱(chēng)訂貨數(shù)量單價(jià)日期A201201S001 華科電子有限公司施賓彬筆記本計(jì)算機(jī)1098002011.1.2A201202S0

3、01 華科電子有限公司施賓彬激光打印機(jī)528002011.1.2A201203S002 湘江計(jì)算機(jī)公司方勝力筆記本計(jì)算機(jī)5102002011.8.12A201204S003 韋力電子實(shí)業(yè)公司周昌 噴墨打印機(jī)54802011.8.12一個(gè)不好的關(guān)系模式一個(gè)不好的關(guān)系模式訂貨關(guān)系訂貨關(guān)系規(guī)則要求：在任何關(guān)系的任何一個(gè)元組中，主鍵的值不能為空值、也不能取重復(fù)的值。目的：用于保證數(shù)據(jù)庫(kù)表中的每一個(gè)元組都是惟一的。請(qǐng)思考：下面的關(guān)系是否違反實(shí)體完整性規(guī)則？學(xué)號(hào)姓名性別年齡系編號(hào)03001馬力剛男210103102王萍華女200203223王平男2103張華男220403001李萍女1905規(guī)則要求由用戶

4、根據(jù)實(shí)際情況，定義表中屬性的取值范圍例如：性別只能是男和女、年齡不能為負(fù)值、成績(jī)?cè)?100之間等。目的用于保證給定字段中數(shù)據(jù)的有效性,即保證數(shù)據(jù)的取值在有效的范圍內(nèi)。規(guī)則要求：“不引用不存在的實(shí)體”。即：不允許在一個(gè)不允許在一個(gè)關(guān)系中引用另一個(gè)關(guān)系中不存在的元組關(guān)系中引用另一個(gè)關(guān)系中不存在的元組。目的在關(guān)系數(shù)據(jù)庫(kù)中通常用外碼來(lái)實(shí)現(xiàn)參照完整性的，用于確保相關(guān)聯(lián)的表間的數(shù)據(jù)保持一致。請(qǐng)思考：下面兩個(gè)關(guān)系是否違反參照完整性規(guī)則？ 系表（主表） 學(xué)生表（從表）說(shuō)明從學(xué)生表的“系編號(hào)（外鍵）”的取值只能為兩種情況：若取非空值，則它必須是主表中若取非空值，則它必須是主表中存在的值存在的值。取空值取空值(n

5、ull)。表明尚未給學(xué)生分配專(zhuān)業(yè)。表明尚未給學(xué)生分配專(zhuān)業(yè)。Null不等于不等于0或空字符串?；蚩兆址?。學(xué)號(hào)學(xué)號(hào)姓名姓名性別性別年齡年齡系編號(hào)系編號(hào)03001馬力剛馬力剛男男210103102王萍華王萍華女女20 03223王平王平男男2105系編號(hào)系編號(hào)系名系名系主任系主任辦公室辦公室電話電話01計(jì)算機(jī)計(jì)算機(jī)龔小勇龔小勇205600302通信通信譚中華譚中華207602503電子電子袁勇袁勇2106018上面兩個(gè)關(guān)系存在著屬性的引用，即學(xué)生關(guān)系引用了系關(guān)系的主碼上面兩個(gè)關(guān)系存在著屬性的引用，即學(xué)生關(guān)系引用了系關(guān)系的主碼“系編號(hào)系編號(hào)”以主表的主鍵（以主表的主鍵（primary key）值）值

6、為外鍵為外鍵 (Foreign Key)的表，可以通的表，可以通過(guò)外鍵與主表進(jìn)行過(guò)外鍵與主表進(jìn)行關(guān)聯(lián)查詢(xún)關(guān)聯(lián)查詢(xún) 一個(gè)好的關(guān)系數(shù)據(jù)庫(kù)模式，到底包括多少個(gè)關(guān)系模式，而每個(gè)關(guān)系模式又應(yīng)該包括哪些屬性（字段），這就是關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)要解決的主要問(wèn)題，或者說(shuō)，這就是數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)要解決的規(guī)范化問(wèn)題11引例：下表關(guān)系模型存在如下4方面的問(wèn)題。插入異常 刪除異常數(shù)據(jù)冗余更新異常訂貨單號(hào)供應(yīng)商代碼供應(yīng)商名稱(chēng)聯(lián)系人商品名稱(chēng)訂貨數(shù)量單價(jià)日期A201201S001 華科電子有限公司施賓彬筆記本計(jì)算機(jī)1098002011.1.2A201202S001 華科電子有限公司施賓彬激光打印機(jī)528002011.1.2A201

7、203S002 湘江計(jì)算機(jī)公司方勝力筆記本計(jì)算機(jī)5102002011.8.12A201204S003 韋力電子實(shí)業(yè)公司周昌 噴墨打印機(jī)54802011.8.12A201205S003 韋力電子實(shí)業(yè)公司周昌 交換機(jī)285002011-8.12一個(gè)不好的關(guān)系模式一個(gè)不好的關(guān)系模式訂貨關(guān)系訂貨關(guān)系12上述訂貨關(guān)系模式的問(wèn)題分析插入異常 新發(fā)展了一個(gè)供應(yīng)商，但沒(méi)有訂貨，無(wú)法插入該供應(yīng)商信息。刪除異常 如果刪除某些訂貨信息，則連供應(yīng)商的信息也會(huì)被刪除。數(shù)據(jù)冗余和更新異常 供應(yīng)商的名稱(chēng)出現(xiàn)了重復(fù)。 如果某供應(yīng)商更換了聯(lián)系人，則所有的訂貨信息都要更新，漏掉一處就會(huì)造成數(shù)據(jù)不一致。13上述訂貨關(guān)系模式的問(wèn)題的

8、解決方法對(duì)關(guān)系模式進(jìn)行分解，分成兩個(gè)表，上述異常問(wèn)題就完全解決了。供應(yīng)商代碼供應(yīng)商名稱(chēng)聯(lián)系人S001 華科電子有限公司施賓彬S002 湘江計(jì)算機(jī)公司方勝力S003 韋力電子實(shí)業(yè)公司周昌 訂貨單號(hào)供應(yīng)商代碼商品名稱(chēng)訂貨數(shù)量日期單價(jià)A201201S001 筆記本計(jì)算機(jī)102011.1.29800A201202S001 激光打印機(jī)52011.1.22800A201203S002 筆記本計(jì)算機(jī)52011.8.1210200供應(yīng)商關(guān)系供應(yīng)商關(guān)系訂貨關(guān)系訂貨關(guān)系指數(shù)據(jù)之間存在的各種聯(lián)系和約束，函數(shù)依賴(lài)是最基本的一種依賴(lài)，在規(guī)范化理論中具有核心作用。是衡量關(guān)系模式優(yōu)劣的標(biāo)準(zhǔn)，范式有許多種，與數(shù)據(jù)依賴(lài)有著直接

9、的聯(lián)系。設(shè)計(jì)規(guī)范的數(shù)據(jù)庫(kù)模式的方法。實(shí)體內(nèi)部和實(shí)體之間各屬性間的聯(lián)系： 一對(duì)一關(guān)系（1:1） 一對(duì)多關(guān)系（1:n） 多對(duì)多關(guān)系（m:n）屬性間的數(shù)據(jù)依賴(lài)函數(shù)依賴(lài)FD多值依賴(lài)MD連接依賴(lài)JD解決單表異常的理論基礎(chǔ)解決多表異常的理論基礎(chǔ)Y=f(X)函數(shù)Y=sin(X)Y=X+1Y=X2+2X+1省=f(城市)姓名=f(學(xué)號(hào))n定義1：設(shè)R(U)是屬性集U上的關(guān)系模式X，Y是U的子集若對(duì)于R(U)的任意個(gè)可能的關(guān)系r，r中不可能存在兩個(gè)元組在X上的屬性值相等，而在Y上的屬性值不等，則稱(chēng) X函數(shù)確定函數(shù)確定Y或或Y函函數(shù)依賴(lài)于數(shù)依賴(lài)于X，記作，記作XY。 n定義定義2：在關(guān)系R中，X、Y為R的兩個(gè)屬性

10、或?qū)傩越M，如果對(duì)于R的所有關(guān)系r 都存在：對(duì)于X的每一個(gè)具體值，Y都只有一個(gè)具體值與之對(duì)應(yīng)，則稱(chēng)屬性Y函數(shù)依賴(lài)于屬性X?；蛘哒f(shuō)，屬性X函數(shù)決定屬性Y，記作XY。其中X稱(chēng)為決定因素，Y稱(chēng)為被決定因素。 有函數(shù)依賴(lài)：編號(hào)名稱(chēng)（名稱(chēng)函數(shù)依賴(lài)于編號(hào)）編號(hào)負(fù)責(zé)人（負(fù)責(zé)人函數(shù)依賴(lài)于編號(hào)）名稱(chēng)地點(diǎn)（地點(diǎn)函數(shù)依賴(lài)于名稱(chēng)）名稱(chēng)編號(hào)（編號(hào)函數(shù)依賴(lài)于名稱(chēng)） 若Y函數(shù)不依賴(lài)不依賴(lài)于X，記作：XY。 如學(xué)號(hào)不函數(shù)依賴(lài)于性別 若X,Y間是1:1關(guān)系，則存在函數(shù)依賴(lài) XY 若X,Y間是1:n關(guān)系，則存在函數(shù)依賴(lài) XY或YX（多方為決定因素） 若X,Y間是m:n關(guān)系，則不存在函數(shù)依賴(lài)11經(jīng)經(jīng) 理理公公 司司領(lǐng)領(lǐng) 導(dǎo)導(dǎo)姓姓 名

11、名住住 址址出生年出生年月月電電 話話民民 族族名名 稱(chēng)稱(chēng)電電 話話類(lèi)類(lèi) 型型注冊(cè)地注冊(cè)地若若X,Y間是間是1:1關(guān)系關(guān)系，則存在函數(shù)依賴(lài)，則存在函數(shù)依賴(lài) XY因此經(jīng)理因此經(jīng)理-公司，或者公司公司，或者公司-經(jīng)理經(jīng)理1n學(xué)學(xué) 生生班班 級(jí)級(jí)屬屬 于于姓姓 名名學(xué)學(xué) 號(hào)號(hào)民民 族族出生年出生年月月名名 稱(chēng)稱(chēng)班班 號(hào)號(hào)系系年年 級(jí)級(jí)專(zhuān)專(zhuān) 業(yè)業(yè)若若X,Y間是間是1:n關(guān)系關(guān)系，則存在函數(shù)依賴(lài)，則存在函數(shù)依賴(lài) XY(多方為決定因素多方為決定因素)因此學(xué)生因此學(xué)生-班級(jí)（一個(gè)學(xué)生的取值，班級(jí)僅有一個(gè)值與之對(duì)應(yīng)）班級(jí)（一個(gè)學(xué)生的取值，班級(jí)僅有一個(gè)值與之對(duì)應(yīng)） 如果如果XY，并且，并且YX，則可記作，則可記

12、作XY，這時(shí)這時(shí)X和和Y可以稱(chēng)做可以稱(chēng)做函數(shù)等價(jià)函數(shù)等價(jià)。如在院系關(guān)系上：如在院系關(guān)系上： 編號(hào)編號(hào)名稱(chēng)名稱(chēng),名稱(chēng)名稱(chēng)編號(hào)編號(hào)所以在院系關(guān)系上編號(hào)和名稱(chēng)可以稱(chēng)作函所以在院系關(guān)系上編號(hào)和名稱(chēng)可以稱(chēng)作函數(shù)等價(jià)。數(shù)等價(jià)。 如果XY，但Y不包含于X，則稱(chēng)XY是非平凡非平凡的函數(shù)依賴(lài)的函數(shù)依賴(lài)。若不特別聲明，我們總是討論非平若不特別聲明，我們總是討論非平凡的函數(shù)依賴(lài)凡的函數(shù)依賴(lài)如：(學(xué)號(hào),課程號(hào))成績(jī)?nèi)纾?學(xué)號(hào),學(xué)院)學(xué)院非平凡依賴(lài)平凡依賴(lài)關(guān)系模式：學(xué)生（學(xué)號(hào)，姓名，年齡） (學(xué)號(hào)，姓名)姓名 函數(shù)依賴(lài)學(xué)號(hào)姓名 函數(shù)依賴(lài)平凡平凡非平凡非平凡 如果XY，并且對(duì)于X的一個(gè)任意真子集X/ 都有X/ Y，則稱(chēng)Y

13、完全函數(shù)依賴(lài)完全函數(shù)依賴(lài)于X，并記作 ；如果X/ Y成立，則稱(chēng)Y部分函數(shù)部分函數(shù)依賴(lài)依賴(lài)于X，并記作YXf YXp 如：如：(學(xué)號(hào)學(xué)號(hào), ,課程號(hào)課程號(hào))成績(jī)成績(jī) 完全完全函數(shù)依賴(lài)函數(shù)依賴(lài)而：而：(學(xué)號(hào)學(xué)號(hào),院系院系)負(fù)責(zé)人負(fù)責(zé)人 部分部分函數(shù)依賴(lài)函數(shù)依賴(lài) 1、如果在關(guān)系模式R(A,B,C)中存在(A,B)C和BC，則(A,B)C為 函數(shù)依賴(lài)。 2、如果在關(guān)系模式R(A,B,C)中存在(A,B)C但不存在BC和AC，則(A,B)C為 函數(shù)依賴(lài)。部分部分完全完全 如果如果XY（非平凡函數(shù)依賴(lài)，并（非平凡函數(shù)依賴(lài)，并且且Y X）、）、YZ，則稱(chēng)，則稱(chēng)Z傳遞函數(shù)傳遞函數(shù)依賴(lài)依賴(lài)于于X。 如學(xué)號(hào)專(zhuān)業(yè)，

14、專(zhuān)業(yè)院系，則院系傳遞函數(shù)依賴(lài)于學(xué)號(hào)。如果在關(guān)系模式R(A,B,C)中存在 AC且存在AB，BC，則 A C 為 函數(shù)依賴(lài)傳遞傳遞設(shè)有關(guān)系模式設(shè)有關(guān)系模式R R( (U U, ,F F) )，X X、Y Y、Z Z 均為均為U U 的子集，的子集，有如下推理規(guī)則：有如下推理規(guī)則：增廣律：如果增廣律：如果X XY Y，則，則XZXZYZYZ；傳遞律：如果傳遞律：如果X XY Y、Y YZ Z，則，則X XZ Z 。合并規(guī)則：如果合并規(guī)則：如果X XY Y、X XZ Z，則，則X XYZYZ。分解規(guī)則：如果分解規(guī)則：如果X XYZYZ，則，則X XY Y、X XZ Z。偽傳遞規(guī)則：如果偽傳遞規(guī)則：如

15、果X XY Y、YWYWZ Z，則，則XWXWZ Z。 在關(guān)系模式中，函數(shù)依賴(lài)不能表示屬性值之間的一對(duì)多聯(lián)系，這些屬性之間有些雖然沒(méi)有直接關(guān)系，但存在間接的關(guān)系。把沒(méi)有直接聯(lián)系、但有間接的聯(lián)系稱(chēng)為多值依賴(lài)的數(shù)據(jù)依賴(lài)。設(shè)R(U)是屬性集U上的一個(gè)關(guān)系模式。X，Y，Z是的U的子集,并且Z=U-X-Y。關(guān)系模式R(U)中多值依賴(lài)多值依賴(lài)XYXY成立，當(dāng)且僅當(dāng)對(duì)R(U)的任一關(guān)系r,給定的一對(duì)(x，z)值有一組Y的值，這組值僅僅決定于x值而與z值無(wú)關(guān)。 定義中若Z=0(空值)，則稱(chēng) XYXY為平凡的多為平凡的多值依賴(lài)，否則為非平凡的多值依賴(lài)值依賴(lài)，否則為非平凡的多值依賴(lài)函數(shù)就是唯一確定的關(guān)系；多值依賴(lài)

16、卻不能唯一確定函數(shù)就是唯一確定的關(guān)系；多值依賴(lài)卻不能唯一確定 舉例如下，有這樣一個(gè)關(guān)系 ，假設(shè)一個(gè)產(chǎn)品只能放到一個(gè)倉(cāng)庫(kù)中，但是一個(gè)倉(cāng)庫(kù)可以有若干管理員，那么對(duì)應(yīng)于一個(gè) 倉(cāng)庫(kù)管理員，庫(kù)存產(chǎn)品有一組倉(cāng)庫(kù)號(hào)，而實(shí)際上，這個(gè)倉(cāng)庫(kù)號(hào)只與庫(kù)存產(chǎn)品號(hào)有關(guān)，與管理員無(wú)關(guān)，就說(shuō)這是多值依賴(lài)。 庫(kù)存產(chǎn)品倉(cāng)庫(kù)號(hào) 倉(cāng)管員庫(kù)存產(chǎn)品倉(cāng)庫(kù)號(hào)張三X11李四X22李四X11多值依賴(lài)的性質(zhì)： 對(duì)稱(chēng)性：若XYXY，則，則XZ,XZ,其中其中Z=U-X-YZ=U-X-Y 傳遞性：若傳遞性：若XYXY， YZ,YZ,則則XZ-YXZ-Y 合并規(guī)則：合并規(guī)則：若若XYXY， XZ,XZ,則則XYZXYZ 若若XYXY， XZ,XZ,則則

17、XYXYZ Z 若若XYXY， XZ,XZ,則則XY-ZXY-Z， XZ-YXZ-Y目的是要設(shè)計(jì)“好的”關(guān)系數(shù)據(jù)庫(kù)模式。關(guān)系模式的范式分為：第一范式INF第二范式2NF第三范式3NFBCNF范式第四范式4NF第五范式5NF低低高高規(guī)范化程度規(guī)范化程度 每個(gè)關(guān)系模式都應(yīng)滿足最低要求：所有分量都必須是不可分的最小數(shù)據(jù)項(xiàng)，并把其稱(chēng)為第一范式（1NF）關(guān)系。 如果R(U,F) 1NF，并且R中的每個(gè)非主屬性都完全函數(shù)依賴(lài)于關(guān)鍵字，則R(U,F) 2NF。 判斷關(guān)系模式是否滿足2NF的方法？主關(guān)鍵字為單個(gè)屬性時(shí)：一定為2NF主關(guān)鍵字為多個(gè)屬性時(shí)：如果每個(gè)非主屬性組都完全依賴(lài)于主關(guān)鍵字，則為2NF；否則不

18、是2NF。不滿足第二范式不滿足第二范式因?yàn)榇嬖诓糠趾瘮?shù)依賴(lài)因?yàn)榇嬖诓糠趾瘮?shù)依賴(lài)把“選課”關(guān)系分解為如下“選課”和“課程”兩個(gè)關(guān)系： 選課(學(xué)號(hào), 課程號(hào), 考試成績(jī)) 課程(課程號(hào), 課程名, 責(zé)任教師, 職稱(chēng))存儲(chǔ)冗余、插入異常、更新異常、存儲(chǔ)冗余、插入異常、更新異常、刪除異常等現(xiàn)象是否還存在呢？刪除異常等現(xiàn)象是否還存在呢？ 如果R(U,F) 2NF，并且所有非主屬性都不傳遞依賴(lài)于關(guān)鍵字，則R(U,F) 3NF。 例如： 已知關(guān)系模式 R（A，B，C）中存在B C 則此關(guān)系模式一定滿足？NF2例如： 已知關(guān)系模式 R（A，B，C）中 不存在B C 且C B 則此關(guān)系模式一定滿足？NF3例如： 已知關(guān)系模式 R（A，B，C） 則此關(guān)系模式一定滿足？NF3特殊情況特殊情況 如果一個(gè)關(guān)系模式滿足2NF，判斷關(guān)系模式是否滿足3NF的根本是判斷非主屬性之間是否有傳遞函數(shù)依賴(lài)。若有，則不滿足3NF；若無(wú)，則滿足3NF。 如果一個(gè)關(guān)系模式滿足2NF，并且它最多只有一個(gè)非主屬性，則一定滿足3 3 NF。 如果一個(gè)關(guān)系模式滿足1NF，并且沒(méi)有非主屬性，則一定滿足 3 3 NF滿足滿足3NF么？么？ 存在操作異常嗎？存在操作異常嗎？ 課程號(hào)課程號(hào) 責(zé)任教師