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

1、第第5講講 關(guān)系完整性規(guī)則和規(guī)范化理論關(guān)系完整性規(guī)則和規(guī)范化理論 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ì)問題，掌握函數(shù)依賴 和多值依賴的定義和含義，能夠應(yīng)用關(guān)系模式的范 式及關(guān)系模式的分解特性解決實(shí)際的關(guān)系數(shù)據(jù)庫設(shè) 計(jì)問題 本講的理論性和應(yīng)用性都比較強(qiáng)，學(xué)生應(yīng) 從概念著手，弄清概念之間的聯(lián)系和作用。 了解關(guān)系數(shù)據(jù)庫規(guī)范化理論及其在數(shù)據(jù)庫 設(shè)計(jì)中的作用。 本講的重點(diǎn)是函數(shù)依賴和范式，學(xué)生必須 掌握這些概念并能在實(shí)際問題中加以運(yùn)用， 為以后設(shè)計(jì)關(guān)系數(shù)據(jù)庫奠定一個(gè)良好基礎(chǔ)

2、。 關(guān)系數(shù)據(jù)庫設(shè)計(jì)主要是關(guān)系模式設(shè)計(jì)。關(guān)系模式設(shè) 計(jì)的好壞將直接影響數(shù)據(jù)庫的質(zhì)量。 要求設(shè)計(jì)一個(gè)教學(xué)管理數(shù)據(jù)庫，希望從該數(shù)據(jù)庫中經(jīng)常得 到下面的有關(guān)信息：學(xué)生學(xué)號(hào)，學(xué)生姓名，年齡，性別， 系別，系主任姓名，學(xué)生學(xué)習(xí)的課程號(hào)，該課程的成績等。 該怎樣設(shè)計(jì)該數(shù)據(jù)庫的關(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方面的問題。 插入異常 刪除異常 數(shù)據(jù)冗余 更新異常 訂貨單 號(hào) 供應(yīng)商代 碼 供應(yīng)商名稱聯(lián)系人商品名稱訂

3、貨數(shù) 量 單價(jià) 日期 A20120 1 S001 華科電子有限公 司 施賓彬筆記本計(jì)算 機(jī) 109800 2011.1.2 A20120 2 S001 華科電子有限公 司 施賓彬激光打印機(jī)52800 2011.1.2 A20120 3 S002 湘江計(jì)算機(jī)公司方勝力筆記本計(jì)算 機(jī) 51020 0 2011.8.1 2 A20120 4 S003 韋力電子實(shí)業(yè)公 司 周昌 噴墨打印機(jī)5480 2011.8.1 2 一個(gè)不好的關(guān)系模式一個(gè)不好的關(guān)系模式訂貨關(guān)系訂貨關(guān)系 1.實(shí)體完整性規(guī)則實(shí)體完整性規(guī)則（） 規(guī)則要求： 在任何關(guān)系的任何一個(gè)元組中，主鍵的值不能為空值、 也不能取重復(fù)的值。 目的：用于

4、保證數(shù)據(jù)庫表中的每一個(gè)元組都是惟一的。 請(qǐng)思考：下面的關(guān)系是否違反實(shí)體完整性規(guī)則？ 學(xué)號(hào)姓名性別年齡系編號(hào) 03001馬力剛男2101 03102王萍華女2002 03223王平男2103 張華男22 04 03001李萍女1905 2.域完整性規(guī)則域完整性規(guī)則(用戶定義完整性規(guī)則用戶定義完整性規(guī)則) 規(guī)則要求 由用戶根據(jù)實(shí)際情況，定義表中屬性的取值范圍 例如：性別只能是男和女、年齡不能為負(fù)值、成 績在0100之間等。 目的 用于保證給定字段中數(shù)據(jù)的有效性,即保證數(shù)據(jù) 的取值在有效的范圍內(nèi)。 3.參照完整性規(guī)則（引用完整性規(guī)則）參照完整性規(guī)則（引用完整性規(guī)則） 規(guī)則要求： “不引用不存在的實(shí)體

5、”。即：不允許在一個(gè)不允許在一個(gè) 關(guān)系中引用另一個(gè)關(guān)系中不存在的元組關(guān)系中引用另一個(gè)關(guān)系中不存在的元組。 目的 在關(guān)系數(shù)據(jù)庫中通常用外碼來實(shí)現(xiàn)參照完整性 的，用于確保相關(guān)聯(lián)的表間的數(shù)據(jù)保持一致。 請(qǐng)思考：下面兩個(gè)關(guān)系是否違反參照完整性規(guī)則？ 系表（主表） 學(xué)生表（從表） 說明 從學(xué)生表的“系編號(hào)（外鍵）”的取值只能為兩種情況： 若取非空值，則它必須是主表中若取非空值，則它必須是主表中存在的值存在的值。 取空值取空值(null)。表明尚未給學(xué)生分配專業(yè)。表明尚未給學(xué)生分配專業(yè)。Null不等于不等于0或空字符串?；蚩兆址?。 學(xué)號(hào)學(xué)號(hào)姓名姓名性別性別年齡年齡系編號(hào)系編號(hào) 03001馬力剛馬力剛男男

6、2101 03102王萍華王萍華女女20 03223王平王平男男2105 系編號(hào)系編號(hào)系名系名系主任系主任辦公室辦公室電話電話 01計(jì)算機(jī)計(jì)算機(jī)龔小勇龔小勇2056003 02通信通信譚中華譚中華2076025 03電子電子袁勇袁勇2106018 上面兩個(gè)關(guān)系存在著屬性的引用，即學(xué)生關(guān)系引用了系關(guān)系的主碼上面兩個(gè)關(guān)系存在著屬性的引用，即學(xué)生關(guān)系引用了系關(guān)系的主碼“系編號(hào)系編號(hào)” 以主表的主鍵（以主表的主鍵（ primary key）值）值 為外鍵為外鍵 (Foreign Key)的表，可以通的表，可以通 過外鍵與主表進(jìn)行過外鍵與主表進(jìn)行 關(guān)聯(lián)查詢關(guān)聯(lián)查詢 一個(gè)好的關(guān)系數(shù)據(jù)庫模式，到底包括多少

7、個(gè)關(guān)系模式，而每個(gè)關(guān)系模式又應(yīng)該包括 哪些屬性（字段），這就是關(guān)系數(shù)據(jù)庫設(shè) 計(jì)要解決的主要問題，或者說，這就是數(shù) 據(jù)庫邏輯設(shè)計(jì)要解決的規(guī)范化問題 11 引例：下表關(guān)系模型存在如下4方面的問題。 插入異常 刪除異常 數(shù)據(jù)冗余 更新異常 訂貨單號(hào)供應(yīng)商代 碼 供應(yīng)商名稱聯(lián)系人商品名稱訂貨數(shù)量單價(jià)日期 A201201S001 華科電子有限公司施賓彬筆記本計(jì)算機(jī)1098002011.1. 2 A201202S001 華科電子有限公司施賓彬激光打印機(jī)528002011.1. 2 A201203S002 湘江計(jì)算機(jī)公司方勝力筆記本計(jì)算機(jī)5102002011.8. 12 A201204S003 韋力電子實(shí)業(yè)

8、公司周昌 噴墨打印機(jī)54802011.8. 12 A201205S003 韋力電子實(shí)業(yè)公司周昌 交換機(jī)285002011- 8.12 一個(gè)不好的關(guān)系模式一個(gè)不好的關(guān)系模式訂貨關(guān)系訂貨關(guān)系 12 上述訂貨關(guān)系模式的問題分析 插入異常 新發(fā)展了一個(gè)供應(yīng)商，但沒有訂貨，無法插 入該供應(yīng)商信息。 刪除異常 如果刪除某些訂貨信息，則連供應(yīng)商的信息 也會(huì)被刪除。 數(shù)據(jù)冗余和更新異常 供應(yīng)商的名稱出現(xiàn)了重復(fù)。 如果某供應(yīng)商更換了聯(lián)系人，則所有的訂貨 信息都要更新，漏掉一處就會(huì)造成數(shù)據(jù)不一 致。 13 上述訂貨關(guān)系模式的問題的解決方法 對(duì)關(guān)系模式進(jìn)行分解，分成兩個(gè)表，上述異常 問題就完全解決了。 供應(yīng)商代 碼

9、 供應(yīng)商名 稱 聯(lián)系人 S001 華科電子 有限公司 施賓彬 S002 湘江計(jì)算 機(jī)公司 方勝力 S003 韋力電子 實(shí)業(yè)公司 周昌 訂貨單 號(hào) 供應(yīng)商 代碼 商品名 稱 訂 貨 數(shù) 量 日期單價(jià) A20120 1 S001 筆記本 計(jì)算機(jī) 102011.1.29800 A20120 2 S001 激光打 印機(jī) 52011.1.22800 A20120 3 S002 筆記本 計(jì)算機(jī) 52011.8.1210200 A20120 4 S003 噴墨打 印機(jī) 52011.8.12480 供應(yīng)商關(guān)系供應(yīng)商關(guān)系訂貨關(guān)系訂貨關(guān)系 指數(shù)據(jù)之間存在的各種聯(lián)系和約 束，函數(shù)依賴是最基本的一種依賴，在規(guī)范 化理

10、論中具有核心作用。 是衡量關(guān)系模式優(yōu)劣的標(biāo)準(zhǔn)， 范式有許多種，與數(shù)據(jù)依賴有著直接的聯(lián)系。 設(shè)計(jì)規(guī)范的數(shù)據(jù)庫模式的方 法。 實(shí)體內(nèi)部和實(shí)體之間各屬性間的聯(lián)系： 一對(duì)一關(guān)系（1:1） 一對(duì)多關(guān)系（1:n） 多對(duì)多關(guān)系（m:n） 屬性間的數(shù)據(jù)依賴 函數(shù)依賴FD 多值依賴MD 連接依賴JD 解決單表異常 的理論基礎(chǔ) 解決多表異常 的理論基礎(chǔ) Y=f(X) 函數(shù) Y=sin(X) Y=X+1 Y=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上的屬性值不

11、等，則稱 X函數(shù)確定函數(shù)確定Y或或Y函函 數(shù)依賴于數(shù)依賴于X，記作，記作XY。 n定義定義2：在關(guān)系R中，X、Y為R的兩個(gè)屬性或?qū)傩?組，如果對(duì)于R的所有關(guān)系r 都存在：對(duì)于X的每 一個(gè)具體值，Y都只有一個(gè)具體值與之對(duì)應(yīng)，則 稱屬性Y函數(shù)依賴于屬性X?；蛘哒f，屬性X函數(shù) 決定屬性Y，記作XY。其中X稱為決定因素，Y 稱為被決定因素。 有函數(shù)依賴： 編號(hào)名稱（名稱函數(shù)依賴于編號(hào)） 編號(hào)負(fù)責(zé)人（負(fù)責(zé)人函數(shù)依賴于編號(hào)） 名稱地點(diǎn)（地點(diǎn)函數(shù)依賴于名稱） 名稱編號(hào)（編號(hào)函數(shù)依賴于名稱） 若Y函數(shù)不依賴不依賴于X，記作：XY。 如學(xué)號(hào)不函數(shù)依賴于性別 若X,Y間是1:1關(guān)系，則存在函數(shù)依賴 XY 若X,Y

12、間是1:n關(guān)系，則存在函數(shù)依賴 XY或 YX（多方為決定因素） 若X,Y間是m:n關(guān)系，則不存在函數(shù)依賴 11 經(jīng)經(jīng) 理理公公 司司 領(lǐng)領(lǐng) 導(dǎo)導(dǎo) 姓姓 名名 住住 址址 出生年出生年 月月 電電 話話 民民 族族 名名 稱稱 電電 話話 類類 型型 注冊地注冊地 若若X,Y間是間是1:1關(guān)系關(guān)系，則存在函數(shù)依賴，則存在函數(shù)依賴 XY 因此經(jīng)理因此經(jīng)理-公司，或者公司公司，或者公司-經(jīng)理經(jīng)理 1 n 學(xué)學(xué) 生生 班班 級(jí)級(jí) 屬屬 于于 姓姓 名名學(xué)學(xué) 號(hào)號(hào) 民民 族族 出生年出生年 月月 名名 稱稱 班班 號(hào)號(hào) 系系 年年 級(jí)級(jí) 專專 業(yè)業(yè) 若若X,Y間是間是1:n關(guān)系關(guān)系，則存在函數(shù)依賴，則存

13、在函數(shù)依賴 XY(多方為決定因素多方為決定因素) 因此學(xué)生因此學(xué)生-班級(jí)（一個(gè)學(xué)生的取值，班級(jí)僅有一個(gè)值與之對(duì)應(yīng)）班級(jí)（一個(gè)學(xué)生的取值，班級(jí)僅有一個(gè)值與之對(duì)應(yīng)） 如果如果XY，并且，并且YX，則可記作，則可記作XY， 這時(shí)這時(shí)X和和Y可以稱做可以稱做函數(shù)等價(jià)函數(shù)等價(jià)。 如在院系關(guān)系上：如在院系關(guān)系上： 編號(hào)編號(hào)名稱名稱,名稱名稱編號(hào)編號(hào) 所以在院系關(guān)系上編號(hào)和名稱可以稱作函所以在院系關(guān)系上編號(hào)和名稱可以稱作函 數(shù)等價(jià)。數(shù)等價(jià)。 如果XY，但Y不包含于X，則稱XY是非平凡非平凡 的函數(shù)依賴的函數(shù)依賴。若不特別聲明，我們總是討論非平若不特別聲明，我們總是討論非平 凡的函數(shù)依賴凡的函數(shù)依賴 如：(學(xué)

14、號(hào),課程號(hào))成績 如：(學(xué)號(hào),學(xué)院)學(xué)院 非平凡依賴 平凡依賴 關(guān)系模式：學(xué)生（學(xué)號(hào)，姓名，年齡） (學(xué)號(hào)，姓名)姓名 函數(shù)依賴 學(xué)號(hào)姓名 函數(shù)依賴 平凡平凡 非平凡非平凡 如果XY，并且對(duì)于X的一個(gè)任意真子集X/ 都有X/ Y，則稱Y完全函數(shù)依賴完全函數(shù)依賴于X，并記 作 ；如果X/ Y成立，則稱Y部分函數(shù)部分函數(shù) 依賴依賴于X，并記作 YX f YX p 如：如：(學(xué)號(hào)學(xué)號(hào), ,課程號(hào)課程號(hào))成績成績 完全完全函數(shù)依賴函數(shù)依賴 而：而：(學(xué)號(hào)學(xué)號(hào),院系院系)負(fù)責(zé)人負(fù)責(zé)人 部分部分函數(shù)依賴函數(shù)依賴 1、如果在關(guān)系模式R(A,B,C)中存在 (A,B)C和BC，則(A,B)C為 函數(shù)依賴。 2

15、、如果在關(guān)系模式R(A,B,C)中存在 (A,B)C但不存在BC和AC，則 (A,B)C為 函數(shù)依賴。 部分部分 完全完全 如果如果XY（非平凡函數(shù)依賴，并（非平凡函數(shù)依賴，并 且且Y X）、）、YZ，則稱，則稱Z傳遞函數(shù)傳遞函數(shù) 依賴依賴于于X。 如學(xué)號(hào)專業(yè)，專業(yè)院系， 則院系傳遞函數(shù)依賴于學(xué)號(hào)。 如果在關(guān)系模式R(A,B,C)中存在 AC 且存在AB，BC，則 A C 為 函數(shù)依賴 傳遞傳遞 設(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，則，則X

16、ZXZYZYZ； 傳遞律：如果傳遞律：如果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ī)則：如果X XY Y、YWYWZ Z，則，則XWXWZ Z。 在關(guān)系模式中，函數(shù)依賴不能表示屬性值之間 的一對(duì)多聯(lián)系，這些屬性之間有些雖然沒有直 接關(guān)系，但存在間接的關(guān)系。把沒有直接聯(lián)系、 但有間接的聯(lián)系稱為多值依賴的數(shù)據(jù)依賴。 設(shè)R(U)是屬性集U上的一個(gè)關(guān)系模式。X，Y，Z 是的U的子集,并且Z=U-X-Y。關(guān)系模式

17、R(U)中 多值依賴多值依賴XYXY成立，當(dāng)且僅當(dāng)對(duì)R(U)的任一 關(guān)系r,給定的一對(duì)(x，z)值有一組Y的值，這 組值僅僅決定于x值而與z值無關(guān)。 定義中若Z=0(空值)，則稱 XYXY為平凡的多為平凡的多 值依賴，否則為非平凡的多值依賴值依賴，否則為非平凡的多值依賴 函數(shù)就是唯一確定的關(guān)系；多值依賴卻不能唯一確定函數(shù)就是唯一確定的關(guān)系；多值依賴卻不能唯一確定 舉例如下，有這樣一個(gè)關(guān)系 ，假設(shè)一個(gè)產(chǎn)品只 能放到一個(gè)倉庫中，但是一個(gè)倉庫可以有 若干管理員，那么對(duì)應(yīng)于一個(gè) 倉庫管理 員，庫存產(chǎn)品有一組倉庫號(hào)，而實(shí)際上， 這個(gè)倉庫號(hào)只與庫存產(chǎn)品號(hào)有關(guān)，與管理 員無關(guān)，就說這是多值依賴。 庫存產(chǎn)品倉

18、庫號(hào) 倉管員庫存產(chǎn)品倉庫號(hào) 張三X11 李四X22 李四X11 多值依賴的性質(zhì)： 對(duì)稱性：若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,則則XYXYZ Z 若若XYXY， XZ,XZ,則則XY-ZXY-Z， XZ-YXZ-Y 目的是要設(shè)計(jì)“好的”關(guān)系數(shù)據(jù)庫模式。 關(guān)系模式的范式分為： 第一范式INF 第二范式2NF 第三范式3NF BCNF范式 第四范式4NF 第五范式5NF 低低 高高 規(guī)范化程度規(guī)范化程度 每個(gè)

19、關(guān)系模式都應(yīng)滿足最低要求：所有分量都必須是不 可分的最小數(shù)據(jù)項(xiàng)，并把其稱為第一范式（1NF）關(guān)系。 如果R(U,F) 1NF，并且R中的每個(gè)非主屬性 都完全函數(shù)依賴于關(guān)鍵字，則R(U,F) 2NF。 判斷關(guān)系模式是否滿足2NF的方法？ 主關(guān)鍵字為單個(gè)屬性時(shí)：一定為2NF 主關(guān)鍵字為多個(gè)屬性時(shí)：如果每個(gè)非主屬性組 都完全依賴于主關(guān)鍵字，則為2NF；否則不 是2NF。 不滿足第二范式不滿足第二范式 因?yàn)榇嬖诓糠趾瘮?shù)依賴因?yàn)榇嬖诓糠趾瘮?shù)依賴 把“選課”關(guān)系分解為如下“選課”和“課程”兩個(gè) 關(guān)系： 選課(學(xué)號(hào), 課程號(hào), 考試成績) 課程(課程號(hào), 課程名, 責(zé)任教師, 職稱) 存儲(chǔ)冗余、插入異常、更新異常、存儲(chǔ)冗余、插入異常、更新異常、 刪除異常等現(xiàn)象是否還存在呢？刪除異常等現(xiàn)象是否還存在呢？ 如果R(U,F) 2NF，并且所有非主屬性都不傳 遞依賴于關(guān)鍵字，則R(U,F) 3NF。 例如