1、1第六章 教學要求n掌握函數依賴、碼、范式、2NF、3NF、BCNF概念與判別。n了解保持函數依賴并具有無損連接性的3NF模式分解算法。2第六章 教學重點n函數依賴、碼、范式、2NF、3NF、BCNF概念與判別。n保持函數依賴并具有無損連接性的3NF模式分解算法。3第六章 教學難點n函數依賴、碼、范式、2NF、3NF、BCNF概念與判別。n保持函數依賴并具有無損連接性的3NF模式分解算法。4第六章 教學內容n設計一個好的關系數據庫系統，關鍵是要設計一個好的數據庫模式數據庫邏輯設計問題）n數據庫邏輯設計主要解決的問題：n關系數據庫應該組織成幾個關系模式n關系模式中包括哪些屬性n在關系數據庫設計理

2、論的指導下,選擇較好的關系模式集合，模式的規范56.1 “不好的數據庫設計n舉例：為學校設計一個關系數據庫n關系模式: UN(Sno,Cno,G,Sdept,MN)n其中:Sno:描述學生n Sdept:描述系名n MN:描述系主任n Cno:描述課程n G:描述學習成績n根據對現實世界的分析,可得出:Sno,Cno是碼n按照關系模式UN裝入部分數據:66.1 “不好的數據庫設計S#CNGSDNMNS1C1A計算機系趙S1C2A計算機系趙S1C4A計算機系趙S2C2A計算機系趙S2C3B計算機系趙S3C1B數學系王S3C2A數學系王S4C1A物理系王S4C2B物理系王S4C3C物理系王S5C4

3、A信息系李76.1 “不好的數據庫設計n對數據庫操作時,會出現以下問題n1. 數據冗余(系主任名的存儲次數)n 數據重復存儲:浪費存儲空間,數據庫維護困難(更新異常)n2. 插入異常(一個系剛成立)n 主碼為空的記錄不能存在與數據庫,導致不能進行插入操作n3. 刪除異常(一個系的學生全部畢業)n 刪除操作后,一些相關信息無法保存在數據庫中86.1 “不好的數據庫設計n要消除以上的“弊病”,把上面的關系數據庫模式分解為三個關系模式:n S(Sno,Sdept)n SG(Sno,Cno,G)n Dept(Sdept,MN)n用以上三個結構簡單的關系代替了原來的結構復雜的關系,從而得到的了“好的關系

4、數據庫模式n“好的關系數據庫模式并不是在任何情況下都是最優的,要從實際的設計目標出發96.2 函數依賴n類似于變量之間的單值函數關系n Y=F(X),其中自變量X的值,決定一個唯一的函數值Yn在一個關系模式里的屬性,由于它在不同元組里屬性值可能不同,由此可以把關系中的屬性看作變量n一個屬性與另一個屬性在取值上可能存在制約關系n函數依賴就是屬性間的邏輯依賴關系106.2.1 函數依賴n定義1 設R(U)是一個關系模式,U是R的屬性集合,X和Y是U的子集.對于R(U)的任何一個可能的關系r,如果r中不存在兩個元組,它們在X上的屬性值相同,而在Y上的屬性值不同,則稱X函數決定Y,或Y函數依賴于X,記

5、作:X Y.nX通常稱為“決定因素”n當Y不函數依賴與X,則記作:X Yn當X Y,且Y X,則記作:X Y116.2.1 函數依賴n舉例:n職工號(A)基本工資(B)獎金(C)n 051 390 50n 052 420 50n 053 390 80nA B A C B A C A126.2.1 函數依賴n幾點說明:n1. 函數依賴是語義范疇的概念.它反映了一種語義完整性約束,只能根據語義來確定一個函數依賴.n2. 函數依賴是指關系R模式的所有關系元組均應滿足的約束條件,而不是關系模式中的某個或某些元組滿足的約束條件136.2.1 函數依賴n幾點說明:n3. 函數依賴與屬性間的聯系類型有關n(

6、1)若屬性X和Y之間有“一對一的聯系,n 則X Y,Y X,X Y.n(2)若屬性X和Y之間有“多對一的聯系,n 則X Y,但Y X.n(3)若屬性X和Y之間有“多對多的聯系,n 則X與Y之間不存在任何函數依賴.n當確定函數依賴關系時,可從屬性間的聯系入手146.2.1 函數依賴n幾點說明n4. 如果X Y,并且Y不是X的子集,則稱X Y是非平凡的函數依賴;如果Y是X的子集,則稱X Y是平凡的函數依賴;n 我們討論的是非平凡的函數依賴.156.2.2 完全函數依賴與部分函數依賴 n定義2:設X Y是關系模式R(U)的一個函數依賴,當對X的每一個真子集X,都有X Y,則Y對X的函數依賴是完全的,

7、記作X f Y;如果對X某個真子集X有X Y,則稱Y對X的函數依賴是部分的,記作X p Y.n例:UN(Sno,Cno,G,Sdept,MN)n (Sno,Cno) f Gn (Sno,Cno) p Sdept 166.2.3 傳遞函數依賴n定義3:設X,Y,Z為關系模式R的互不相同的屬性集合,如果X Y,而Y X,但Y Z,則稱Z傳遞函數依賴與X,記作X t Z.n例:UN(Sno,Cno,G,Sdept,MN)n Sno Sdept, Sdept Sno, Sdept MNn 則有Sno t MN176.3 碼的形式定義n定義4: 在關系模式R(U)中,K是U中的屬性或屬性組,如果K f

8、U,則稱K為關系R(U)的一個候選碼;若關系候選碼多于一個,則選定其中一個作為主碼.其中包含在任意一個候選碼中的屬性稱為主屬性;不包含在任意一個候選碼中的屬性稱為非主屬性n 186.3 碼的形式定義n候選碼的兩個性質:n1. 標識的唯一性: 對于R(U)中的每一元組,K的值確定后,該元組就相應確定了.n2. 無冗余性: K是屬性組的情況下,K的任何一部分都不能唯一標識該元組(定義中的完全函數依賴的意義) 196.3 碼的形式定義n舉例:n1. S(Sno,Sname,AGE,SEX,DEPT)n 其中: Sno和Sname是主屬性,其它屬性是非主屬性n2. SC(Sno,Cno,GRADE)n

9、 其中:Sno,Cno是主屬性,GRADE屬性是非主屬性n3. 設演奏者、作品和聽眾分別用P、W，A表示，構成一個關系模式PWA(P,W,A)n 其中:全屬性集(P,W,A)是碼(全碼),P,W,A都是主屬性206.4 規范化n用幾個簡單的關系去取代原來結構復雜的關系的過程叫做關系規范化.n規范化理論是研究如何把一個不好的關系模式轉化為好的關系模式的理論n規范化理論是E.E.Codd在1971年首先提出的n規范化理論是數據庫設計過程中的一個非常有用的輔助工具216.4.1 范式n規范化理論是圍繞著范式建立的.n滿足不同程度要求的約束集則稱為不同的范式.n如果一個關系滿足某個指定的約束集,則稱它

10、屬于某個特定的范式.n較高層次的范式比較低層次的范式具有“更合乎要求的性質”n一個低一級范式的關系模式,通過投影運算可以轉化為若干個高一級范式的關系模式的集合,這個過程叫做規范化.n如果一個關系滿足某個范式要求,則它也會滿足較其級別低的所有范式的要求226.4.1 范式n范式層次規范與非規范化關系1NF2NF3NFBCNF4NF5NF236.4.2 第一范式(1NF)n定義5: 在關系模式R中的每一個具體關系r中,如果每個屬性值都是不可再分的最小數據單位,則稱R是第一范式的關系,記作R1NF.n數據庫理論研究的是規范化關系.n1NF規范化: 把非規范化關系規范提高到1NF關系模式的集合.246

11、.4.3 第二范式(2NF)n定義6: 若關系模式R1NF,且每個非主屬性都完全依賴于R的任意候選碼,則關系模式R屬于第二范式,記作R 2NF.n2NF規范化是把1NF關系模式規范提高到變成2NF關系模式的集合.n從1NF中消除非主屬性對候選碼的部分函數依賴,則獲得2NF關系.n舉例:UN(Sno,Cno,G,SDN,MN)256.4.3 第二范式(2NF)非主屬性對碼的函數依賴關系:SnoCnoGSDNMNfpp266.4.3 第二范式(2NF)n為了消除非主屬性對碼的部分函數依賴,采用投影分解運算來提高關系模式UN的范式等級nSG=UNSno,Cno,GnSDM=UNSno,SDN,MNn

12、在上述兩個關系中,非主屬性對其主碼都是完全的函數依賴,即SG2NF, SDM2NFn如果關系R不屬于2NF,那么可把R分解成它的投影的集合,使這些投影都屬于2NF,并且對R的各個投影進行自然連接操作,可恢復原來的關系R(具有無損連接性)276.4.3 第三范式(3NF)n定義7: 若關系模式R2NF,且每個非主屬性都不傳遞依賴于R的任意候選碼,則R3NF.n從2NF關系中,消除非主屬性對碼的傳遞依賴函數而獲得3NF關系nR3NF,則每個非主屬性既不部分依賴,也不傳遞依賴于R的任何候選碼.n3NF的規范化: R(X,Y,Z,W),若X Y,Y X,且n Y Z適合于R,那么R首先分解為投影RX,

13、Y,W和RY,Z,若RX,Y,W還不屬于3NF,則繼續上述過程286.4.4 第三范式(3NF)n舉例:nSG3NFnSD不屬于3NF,將其分解為兩個投影nSD=SDMSno,SDNnDM=SDMSDN,MNn可用SG,SD,SM來代替原來的關系模式UNn它們均是單個關系表示單個實體,一切“異常都消失,(規范到3NF就可以了)n要達到3NF的規范化,必須要求“既具有無損連接性,又保持函數依賴特性”296.4.5 BCNF范式1. 3NF的不完善性3NF沒有限制有些主屬性對碼的部分依賴和傳遞依賴關系.舉例: SCG(Sno,Sname,Cno,G)(Sno,Cno) G(Sname,Cno) G

14、 SCG3NF (Sno,Cno) p Sname Sno Sname關系SCG存在著較大的數據冗余當關系具有幾個候選碼,特別具有幾個重疊候選碼時,僅滿足3NF仍可能發生異常306.4.5 BCNF范式n2. BCNF范式n定義8: 若R1NF,且R中每個決定因素都包含候選碼,則R BCNF.n滿足BCNF的關系將消除任何屬性對候選碼的部分依賴與傳遞依賴n應用BCNF定義時,可直接判斷1NF是否屬于BCNFnBCNF規范化: 設R不屬于BCNF,R可表示成R(X,Y,Z),X Y,且X Z,則可用RX,Y和RX,Z代替R(X,Y,Z),如果需要,可繼續此過程,它是有限的316.4.5 BCNF

15、范式n舉例: SCG(Sno,Sname,Cno,G)屬于3NF,繼續把它分解為:n SS=SCGSno,Snamen SG=SCGSno,Cno,Gn可用SS,SG代替原模式SCGn規范化后,SS,SG都屬于BCNF,并且大大減少了冗余 32小結n目的目的: 規范化的目的是使結構合理規范化的目的是使結構合理,使數據冗余盡量小使數據冗余盡量小,清清除插入除插入,刪除和更新異常刪除和更新異常.n準繩準繩: 遵從概念單一化遵從概念單一化“一事一地準繩一事一地準繩,即一個關系模即一個關系模式描述一個實體或實體間的一種聯系式描述一個實體或實體間的一種聯系.規范化的實質就規范化的實質就是概念單一化是概念

16、單一化.(一個關系表示一個實體一個關系表示一個實體)n方法方法: 將關系模式投影分解成兩個或兩個以上的關系模將關系模式投影分解成兩個或兩個以上的關系模式式.n要求要求: 分解后的關系模式集合應當與原關系模式分解后的關系模式集合應當與原關系模式“等等價價”,既具有無損連接性既具有無損連接性,又保持函數依賴特性又保持函數依賴特性”.n一個關系模式達到一個關系模式達到BCNF,說明在函數依賴的范疇內說明在函數依賴的范疇內,已已實現了徹底分離實現了徹底分離,可消除可消除“異常異?！?但在實際應用中但在實際應用中,并不并不一定要求全部模式都達到一定要求全部模式都達到BCNF.33思考題1. 設學校環境如

17、下設學校環境如下: 一個系有若干個專業，每一個專業只一個系有若干個專業，每一個專業只屬于一個系。每個專業一年只招一個班屬于一個系。每個專業一年只招一個班,每個班有若干每個班有若干名學生名學生.一個系的學生住在同一個宿舍區一個系的學生住在同一個宿舍區,每個學生可以每個學生可以參加幾個學會參加幾個學會,一個學會有若干學生一個學會有若干學生.現在要建立關于系、現在要建立關于系、學生、班級、學會的數據庫，關系模式為：學生、班級、學會的數據庫，關系模式為：班班號，專業名，系名，班人數，入學年份）班班號，專業名，系名，班人數，入學年份）學生學號，姓名，出生日期，系名，班號，宿舍區）學生學號，姓名，出生日期

18、，系名，班號，宿舍區）系系號，系名，辦公室，系人數）系系號，系名，辦公室，系人數）學會學會名，成立時間，地點，會員數）學會學會名，成立時間，地點，會員數）學生參加各學會要注明入會年份學生參加各學會要注明入會年份34思考題(1)請寫出每個關系模式的函數依賴,分析是否存在部分依賴,是否存在傳遞依賴(2) 找出各個關系的候選碼 (3)對該關系模式進行規范化，并使規范化后的數據模型屬于3NF關系模式35分析n班班號，專業名，系名，班人數，入學年班班號，專業名，系名，班人數，入學年份候選碼：班號、（專業名，入學年份）份候選碼：班號、（專業名，入學年份）n 班號班號 系名系名n 班號班號 班人數班人數 n

19、 （專業名，入學年份）（專業名，入學年份） 班號班號 n （專業名，入學年份）（專業名，入學年份） 班人數班人數n 專業名專業名 系名系名 n （專業名，入學年份）（專業名，入學年份） 系名系名fffffp36分析n學生學號，姓名，出生日期，系名，班號，學生學號，姓名，出生日期，系名，班號，宿舍區候選碼：學號宿舍區候選碼：學號n 學號學號 姓名姓名 學號學號 出生日出生日期期n 學號學號 系名系名 學號學號 班號班號n 學號學號 宿舍區宿舍區 班號班號 系名系名n 系名系名 宿舍區宿舍區 班號班號 宿舍區宿舍區n 學號學號 系名系名 學號學號 宿舍宿舍區區fffffffttt37分析n系系號，

20、系名，辦公室，系人數）系系號，系名，辦公室，系人數）n 系號系號 系名系名 系號系號 辦公辦公室室n 系號系號 系人數系人數 系名系名 辦公室辦公室n 系名系名 系人數系人數n學會學會名，成立時間，地點，會員數）學會學會名，成立時間，地點，會員數）n 學會名學會名 成立時間成立時間 學會名學會名 地點地點n 學會名學會名 會員數會員數n參會學會名，學號，參會時間）參會學會名，學號，參會時間）n （學會名，學號）（學會名，學號） 學會名學會名n（學會名，學號）（學會名，學號） 學號學號n（學會名，學號）（學會名，學號） 參會時間參會時間fffffffffff383NF規范要點n首先R屬于2NF，

21、再判斷是否存在傳遞函數依賴。nR(X,Y,Z,W),若X Y,Y X,且n Y Z適合于R,那么R首先分解為投影RX,Y,W和RY,Z,若RX,Y,W還不屬于3NF,則繼續上述過程。n2NF是在1NF的基礎上，消除非主屬性對候選碼的部分函數依賴39需要規范的關系模式n班班號，專業名，系名，班人數，入學年份）班班號，專業名，系名，班人數，入學年份）n（專業名，入學年份）（專業名，入學年份） 系名系名n專業專業名，系名）專業專業名，系名） n班班號，班人數，專業名，入學年份）班班號，班人數，專業名，入學年份） n學生學號，姓名，出生日期，系名，班號，宿舍區）學生學號，姓名，出生日期，系名，班號，宿

22、舍區）n學號學號 系名班號）系名班號） 學號學號 宿舍區班號）宿舍區班號）n班號班號 宿舍區系名）宿舍區系名）n班班號，系名）班班號，系名）n班班號，宿舍區）班班號，宿舍區） 系系名，宿舍區）系系名，宿舍區）n學生學號，姓名，出生日期，班號學生學號，姓名，出生日期，班號 ） pttt40分析n班班號，專業名，入學年份，班人數）班班號，專業名，入學年份，班人數）n專業專業名，系名）專業專業名，系名）n學生學號，姓名，出生日期，班號）學生學號，姓名，出生日期，班號）n系系名，宿舍區）系系名，宿舍區）n系系號，系名，辦公室，系人數）系系號，系名，辦公室，系人數）n學會學會名，成立時間，地點，會員數）

23、學會學會名，成立時間，地點，會員數）n參會學會名，學號，參會時間）參會學會名，學號，參會時間）系系號，系名，辦公室，系人系系號，系名，辦公室，系人數，宿舍區）數，宿舍區）41思考題2. 有教師任課關系模式有教師任課關系模式TDCTDC(T#,TNAME,TITLE,ADDR,D#,DNAME,LOC,C#,CNAME,LEVEL,CREDIT);其中屬性分別表示教師編其中屬性分別表示教師編號、教師姓名、職稱、教師地址、系號、系名稱、系號、教師姓名、職稱、教師地址、系號、系名稱、系地址、課程號碼、課程名、教學水平、學分，并且現地址、課程號碼、課程名、教學水平、學分，并且現實世界的事實告訴我們，一

24、個系有若干名教師，但一實世界的事實告訴我們，一個系有若干名教師，但一個教師只能屬于一個系，一個教師可以擔任多門課程個教師只能屬于一個系，一個教師可以擔任多門課程的教學，同時任意一門課程可以由多名教師承擔。的教學，同時任意一門課程可以由多名教師承擔。試分析該關系模式有何弊?。空垖υ撽P系模式進行規試分析該關系模式有何弊??？請對該關系模式進行規范化，并使規范化后的數據模型屬于范化，并使規范化后的數據模型屬于BCNF關系模式。關系模式。42分析n該關系模式存在：n數據冗余(同系的教師系號、系名稱、系地址重復存儲)n更新異常(某位教師轉系，可能造成系號、系名稱、系地址修改不一致)n插入異常(一個系剛成立

25、，沒有教師，則不能體現系的情況) n刪除異常(一個系的任課教師轉系，同時刪除系信息)43分析R(X,Y,Z),X Y,且且X Z,可分解成可分解成RX,Y和和RX,ZTDC(T#,TNAME,TITLE,ADDR,D#,DNAME,LOC,C#,CNAME,LEVEL,CREDIT)T1(T#,TNAME,TITLE,ADDR,D#,DNAME,LOC)T2(T#, C#,CNAME,LEVEL,CREDIT)T11(D#,DNAME,LOC) T12(D#,T#,TNAME,TITLE,ADDR) T21(C#,CNAME,CREDIT) T22(C#,T#,LEVEL) 44思考題屬于屬于BCNF的關系模式的關系模式_。 A 已消除了插入、刪除異常已消除了插入、刪除異常B 已消除了插入、刪除異常、數據已消除了插入、刪除異常、數據冗余冗余C 仍然存在插入、刪除異常仍然存在插入、刪除異常D 在函數依賴范疇內在函數依賴范疇內,已消除了插入已消除了插入和刪除的異常和刪除的異常45思考題設設R(U)是屬性集是屬性集U上的關系模式。上的關系模式。X,Y是是U的子集。若對于的子集。若對于R(U)的任意一個可能的任意一個可能的關系的關系r，r中不可能存在兩個元組在中不可能存在兩個元組在X上上的屬性值相等的屬