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

1、第第5講講 關系完整性規(guī)則和規(guī)范化理論關系完整性規(guī)則和規(guī)范化理論 2 關系模式的規(guī)范化理論與方法（重點與難點） 1 關系規(guī)范化的作用（了解） 1 關系完整性規(guī)則（掌握） 要求學生掌握關系模式的3個完整性規(guī)則 要求學生了解關系模式的設計問題，掌握函數(shù)依賴 和多值依賴的定義和含義，能夠應用關系模式的范 式及關系模式的分解特性解決實際的關系數(shù)據(jù)庫設 計問題 本講的理論性和應用性都比較強，學生應 從概念著手，弄清概念之間的聯(lián)系和作用。 了解關系數(shù)據(jù)庫規(guī)范化理論及其在數(shù)據(jù)庫 設計中的作用。 本講的重點是函數(shù)依賴和范式，學生必須 掌握這些概念并能在實際問題中加以運用， 為以后設計關系數(shù)據(jù)庫奠定一個良好基礎

2、。 關系數(shù)據(jù)庫設計主要是關系模式設計。關系模式設 計的好壞將直接影響數(shù)據(jù)庫的質量。 要求設計一個教學管理數(shù)據(jù)庫，希望從該數(shù)據(jù)庫中經常得 到下面的有關信息：學生學號，學生姓名，年齡，性別， 系別，系主任姓名，學生學習的課程號，該課程的成績等。 該怎樣設計該數(shù)據(jù)庫的關系模式？ S S( (Sno,Sname,Sage,Sex,Dept,DN,Cno,Cname,ScoreSno,Sname,Sage,Sex,Dept,DN,Cno,Cname,Score) ) 5 引例：下表關系模型存在如下4方面的問題。 插入異常 刪除異常 數(shù)據(jù)冗余 更新異常 訂貨單 號 供應商代 碼 供應商名稱聯(lián)系人商品名稱訂

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

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

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

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

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

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

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

10、論中具有核心作用。 是衡量關系模式優(yōu)劣的標準， 范式有許多種，與數(shù)據(jù)依賴有著直接的聯(lián)系。 設計規(guī)范的數(shù)據(jù)庫模式的方 法。 實體內部和實體之間各屬性間的聯(lián)系： 一對一關系（1:1） 一對多關系（1:n） 多對多關系（m:n） 屬性間的數(shù)據(jù)依賴 函數(shù)依賴FD 多值依賴MD 連接依賴JD 解決單表異常 的理論基礎 解決多表異常 的理論基礎 Y=f(X) 函數(shù) Y=sin(X) Y=X+1 Y=X2+2X+1 省=f(城市) 姓名=f(學號) n定義1：設R(U)是屬性集U上的關系模式X，Y 是U的子集若對于R(U)的任意個可能的關系r ，r中不可能存在兩個元組在X上的屬性值相等， 而在Y上的屬性值不

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

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

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

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

15、、如果在關系模式R(A,B,C)中存在 (A,B)C但不存在BC和AC，則 (A,B)C為 函數(shù)依賴。 部分部分 完全完全 如果如果XY（非平凡函數(shù)依賴，并（非平凡函數(shù)依賴，并 且且Y X）、）、YZ，則稱，則稱Z傳遞函數(shù)傳遞函數(shù) 依賴依賴于于X。 如學號專業(yè)，專業(yè)院系， 則院系傳遞函數(shù)依賴于學號。 如果在關系模式R(A,B,C)中存在 AC 且存在AB，BC，則 A C 為 函數(shù)依賴 傳遞傳遞 設有關系模式設有關系模式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。 在關系模式中，函數(shù)依賴不能表示屬性值之間 的一對多聯(lián)系，這些屬性之間有些雖然沒有直 接關系，但存在間接的關系。把沒有直接聯(lián)系、 但有間接的聯(lián)系稱為多值依賴的數(shù)據(jù)依賴。 設R(U)是屬性集U上的一個關系模式。X，Y，Z 是的U的子集,并且Z=U-X-Y。關系模式

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

18、庫號 倉管員庫存產品倉庫號 張三X11 李四X22 李四X11 多值依賴的性質： 對稱性：若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ù)庫模式。 關系模式的范式分為： 第一范式INF 第二范式2NF 第三范式3NF BCNF范式 第四范式4NF 第五范式5NF 低低 高高 規(guī)范化程度規(guī)范化程度 每個

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