第6章關(guān)系數(shù)據(jù)理論習(xí)題課

1、1/16數(shù)據(jù)庫原理及應(yīng)用數(shù)據(jù)庫原理及應(yīng)用Principle and Application of Database第六章關(guān)系數(shù)據(jù)理論第六章關(guān)系數(shù)據(jù)理論( (習(xí)題課習(xí)題課) )2/16需要了解的：需要了解的：什么是一個什么是一個“不好不好”的數(shù)據(jù)庫模式；什么是模式的數(shù)據(jù)庫模式；什么是模式的插入異常、刪除異常；規(guī)范化理論的重要意義。的插入異常、刪除異常；規(guī)范化理論的重要意義。需要牢固掌握的：需要牢固掌握的：關(guān)系的形式化定義；數(shù)據(jù)依賴的基本概念關(guān)系的形式化定義；數(shù)據(jù)依賴的基本概念(函函數(shù)依賴、平凡函數(shù)依賴、非平凡函數(shù)依賴、部分函數(shù)依賴、完數(shù)依賴、平凡函數(shù)依賴、非平凡函數(shù)依賴、部分函數(shù)依賴、完全函數(shù)依

2、賴、傳遞函數(shù)依賴、碼、候選碼、外碼、多值依賴全函數(shù)依賴、傳遞函數(shù)依賴、碼、候選碼、外碼、多值依賴)；范式的概念；從范式的概念；從1NF到到4NF的定義；規(guī)范化的含義和作用。的定義；規(guī)范化的含義和作用。需要舉一反三的：需要舉一反三的：四個范式的理解與應(yīng)用，各個級別范式中存四個范式的理解與應(yīng)用，各個級別范式中存在的問題在的問題(插入異常、刪除異常、數(shù)據(jù)冗余插入異常、刪除異常、數(shù)據(jù)冗余)和解決方法；能夠和解決方法；能夠根據(jù)應(yīng)用語義，完整地寫出關(guān)系模式的數(shù)據(jù)依賴集合，并能根根據(jù)應(yīng)用語義，完整地寫出關(guān)系模式的數(shù)據(jù)依賴集合，并能根據(jù)數(shù)據(jù)依賴分析某一個關(guān)系模式屬于第幾范式。據(jù)數(shù)據(jù)依賴分析某一個關(guān)系模式屬于第

3、幾范式。難點：難點：各個級別范式的關(guān)系及其證明。各個級別范式的關(guān)系及其證明。3/161.理解并給出下列術(shù)語的定義：函數(shù)依賴、部分函數(shù)依賴、完全理解并給出下列術(shù)語的定義：函數(shù)依賴、部分函數(shù)依賴、完全函數(shù)依賴、傳遞依賴、候選碼、主碼、外碼、全碼、函數(shù)依賴、傳遞依賴、候選碼、主碼、外碼、全碼、1NF、2NF、3NF、BCNF、多值依賴、多值依賴、4NF。答：函數(shù)依賴答：函數(shù)依賴 ：設(shè)：設(shè)R(U)是一個屬性集是一個屬性集U上的關(guān)系模式，上的關(guān)系模式，X和和Y是是U的子集。若對于的子集。若對于R(U)的任意一個可能的關(guān)系的任意一個可能的關(guān)系r，r中不可能存在中不可能存在兩個元組在兩個元組在X上的屬性值相

4、等，上的屬性值相等， 而在而在Y上的屬性值不等，上的屬性值不等， 則稱則稱 “X函數(shù)確定函數(shù)確定Y” 或或 “Y函數(shù)依賴于函數(shù)依賴于X”，記作，記作XY。 解析：解析：函數(shù)依賴是最基本的、也是最重要的一種數(shù)據(jù)依賴。函數(shù)依賴是最基本的、也是最重要的一種數(shù)據(jù)依賴。 函數(shù)依賴是屬性之間的一種聯(lián)系，體現(xiàn)在屬性值是否相等。函數(shù)依賴是屬性之間的一種聯(lián)系，體現(xiàn)在屬性值是否相等。由定義可知，若由定義可知，若XY，則，則 r中任意兩個元組，如果它們在中任意兩個元組，如果它們在X上上的屬性值相等，的屬性值相等， 那么在那么在Y上的屬性值也一定相等。上的屬性值也一定相等。 要從屬性間實際存在的語義來確定它們之間的函

5、數(shù)依賴。要從屬性間實際存在的語義來確定它們之間的函數(shù)依賴。 函數(shù)依賴不是指關(guān)系模式函數(shù)依賴不是指關(guān)系模式R在某個時刻的關(guān)系在某個時刻的關(guān)系(值值)滿足的約束滿足的約束條件，而是指條件，而是指R在任何時刻的一切關(guān)系均要滿足的約束條件。在任何時刻的一切關(guān)系均要滿足的約束條件。 4/16完全與部分函數(shù)依賴：若完全與部分函數(shù)依賴：若XY，對于，對于X的任何真子集的任何真子集X，都有，都有X Y，則稱，則稱Y完全函數(shù)依賴于完全函數(shù)依賴于X，記作，記作X Y。若。若XY，但，但Y不完全函數(shù)依賴于不完全函數(shù)依賴于X，則稱，則稱Y部分函數(shù)依賴于部分函數(shù)依賴于X，記作，記作X p Y。傳遞函數(shù)依賴：若傳遞函數(shù)依

6、賴：若XY，YZ，且，且Y X，YX，則稱，則稱Z傳遞傳遞函數(shù)依賴于函數(shù)依賴于X，記作，記作X t Z 。若。若YX，即，即XY，則，則Z直接依直接依賴于賴于X。侯選碼：設(shè)侯選碼：設(shè)K為關(guān)系模式為關(guān)系模式R中的屬性中的屬性(組組)，若，若K U，則，則K稱為稱為R的一個侯選碼。若的一個侯選碼。若K為為R的整個屬性組，則的整個屬性組，則K稱為全碼稱為全碼若關(guān)系模式若關(guān)系模式R有多個候選碼，則選定其中的一個就稱為主碼。有多個候選碼，則選定其中的一個就稱為主碼。若關(guān)系模式若關(guān)系模式R中的屬性中的屬性(組組)X并非并非R的碼，但的碼，但X是另一關(guān)系模式是另一關(guān)系模式S的碼，則稱的碼，則稱X是是R的外碼

7、。的外碼。5/16若關(guān)系模式若關(guān)系模式R的所有屬性都是不可分的數(shù)據(jù)項，則的所有屬性都是不可分的數(shù)據(jù)項，則R 1NF。若關(guān)系模式若關(guān)系模式R 1NF，并且每一個非主屬性都完全函數(shù)依賴于，并且每一個非主屬性都完全函數(shù)依賴于R的碼，則的碼，則R 2NF。若關(guān)系模式若關(guān)系模式R 中若不存在這樣的碼中若不存在這樣的碼X、屬性組、屬性組Y及非主屬及非主屬性性Z(Z Y), 使得使得XY，YX，YZ成立，則成立，則R 3NF。若關(guān)系模式若關(guān)系模式R 1NF，若，若XY且且Y X時時X必含有候選碼，必含有候選碼，則則R BCNF。設(shè)關(guān)系模式設(shè)關(guān)系模式R(U)中，中，X、Y和和Z是是U的子集，并且的子集，并且Z

8、=U-X-Y，多，多值依賴值依賴 XY成立當(dāng)且僅當(dāng)對成立當(dāng)且僅當(dāng)對R的任一關(guān)系的任一關(guān)系r，r在在(X,Z)上的每上的每個值對應(yīng)一組個值對應(yīng)一組Y的值，這組值僅僅決定于的值，這組值僅僅決定于X值而與值而與Z值無關(guān)。值無關(guān)。關(guān)系模式關(guān)系模式R 1NF，如果對于，如果對于R的每個非平凡多值依賴的每個非平凡多值依賴XY(Y X)，X都含有候選碼，則都含有候選碼，則R 4NF。6/162.建立一個關(guān)于系、學(xué)生、班級、學(xué)會等諸信息的關(guān)系數(shù)據(jù)庫。建立一個關(guān)于系、學(xué)生、班級、學(xué)會等諸信息的關(guān)系數(shù)據(jù)庫。 學(xué)生的屬性有：學(xué)號、姓名、生日、系名、班號、宿舍區(qū)。學(xué)生的屬性有：學(xué)號、姓名、生日、系名、班號、宿舍區(qū)。

9、班級的屬性有：班號、專業(yè)名、系名、人數(shù)、入校年份。班級的屬性有：班號、專業(yè)名、系名、人數(shù)、入校年份。 系的屬性有：系號、系名、系辦公室地點、人數(shù)。系的屬性有：系號、系名、系辦公室地點、人數(shù)。 學(xué)會的屬性有：學(xué)會名、成立年份、地點、人數(shù)。學(xué)會的屬性有：學(xué)會名、成立年份、地點、人數(shù)。 有關(guān)語義為：一個系有若干專業(yè)，每個專業(yè)每年只招一個班，有關(guān)語義為：一個系有若干專業(yè)，每個專業(yè)每年只招一個班，每個班有若干學(xué)生。一個系的學(xué)生住在同一宿舍區(qū)。每個學(xué)生每個班有若干學(xué)生。一個系的學(xué)生住在同一宿舍區(qū)。每個學(xué)生可參加若干學(xué)會，每學(xué)會有若干學(xué)生。學(xué)生參加某學(xué)會有一個可參加若干學(xué)會，每學(xué)會有若干學(xué)生。學(xué)生參加某學(xué)會

10、有一個入會年份。入會年份。 請給出關(guān)系模式，寫出每個關(guān)系模式的極小函數(shù)依賴集，指出請給出關(guān)系模式，寫出每個關(guān)系模式的極小函數(shù)依賴集，指出是否存在傳遞函數(shù)依賴，對于函數(shù)依賴左部是多屬性的情況討是否存在傳遞函數(shù)依賴，對于函數(shù)依賴左部是多屬性的情況討論函數(shù)依賴是完全函數(shù)依賴，還是部分函數(shù)依賴。指出各關(guān)系論函數(shù)依賴是完全函數(shù)依賴，還是部分函數(shù)依賴。指出各關(guān)系的候選碼、外部碼，有沒有全碼存在？的候選碼、外部碼，有沒有全碼存在？7/16答：關(guān)系模式有：學(xué)生答：關(guān)系模式有：學(xué)生S(S#,SN,SB,DN,C#,SA) 班級班級C(C#,CS,DN,CNUM,CDATE) 系系D(D#,DN,DA,DNUM)

11、 學(xué)會學(xué)會P(PN,DATE1,PA,PNUM) 學(xué)生學(xué)生學(xué)會學(xué)會SP(S#,PN,DATE2) 其中：其中：S#為學(xué)號，為學(xué)號，SN為姓名，為姓名，SB為生日，為生日，DN為系名，為系名，C#為班號，為班號，SA為為宿舍區(qū)，宿舍區(qū)，CS為專業(yè)名，為專業(yè)名，CNUM為班級為數(shù)，為班級為數(shù)，CDATE為入校年份，為入校年份，D#為系為系號，號，DA為系辦公室地點，為系辦公室地點，DNUM為系人數(shù)，為系人數(shù)，PN為學(xué)會名，為學(xué)會名，DATE1為學(xué)會為學(xué)會成立年月，成立年月，PA為地點，為地點，PNUM為人數(shù)，為人數(shù)，DATE2為入會年份。為入會年份。 各關(guān)系模式的極小函數(shù)依賴集為：各關(guān)系模式的極小

12、函數(shù)依賴集為： S：S#SN，S#SB，S#C#，C#DN，DNSA C：C#CS，C#CNUM，C#CDATE，CSDN，(CS,CDATE)C# D：D#DN，DND#，D#DA，D#DNUM P：PNDATE1，PNPA，PNPNUM SP：(S#,PN)DATE2 S中存在傳遞函數(shù)依賴：中存在傳遞函數(shù)依賴：S#DN，S#SA，C#SA C中存在傳遞函數(shù)依賴：中存在傳遞函數(shù)依賴：C#DN (CS,CDATE)C# 和和(S#,PN)DATE2都是完全函數(shù)依賴。都是完全函數(shù)依賴。8/16 學(xué)生學(xué)生S(S#,SN,SB,DN,C#,SA) 班級班級C(C#,CS,DN,CNUM,CDATE)

13、 系系D(D#,DN,DA,DNUM) 學(xué)會學(xué)會P(PN,DATE1,PA,PNUM) 學(xué)生學(xué)生學(xué)會學(xué)會SP(S#,PN,DATE2) S：S#SN，S#SB，S#C#，C#DN，DNSA C：C#CS，C#CNUM，C#CDATE，CSDN，(CS,CDATE)C# D：D#DN，DND#，D#DA，D#DNUM P：PNDATE1，PNPA，PNPNUM SP：(S#,PN)DATE2 關(guān)系關(guān)系 候選碼候選碼 外部碼外部碼 全碼全碼 S S# C#,DN 無無 C C#和和(CS,CDATE) DN 無無 D D#和和DN 無無 無無 P PN 無無 無無 SP (S#,PN) S#,DN

14、 無無 9/163.試由試由Armostrong公理系統(tǒng)推導(dǎo)出下面三條推理規(guī)則：公理系統(tǒng)推導(dǎo)出下面三條推理規(guī)則：合并規(guī)則：若合并規(guī)則：若XZ，XY，則有，則有XYZ偽傳遞規(guī)則：由偽傳遞規(guī)則：由XY，WYZ，則有，則有XWZ分解規(guī)則：若分解規(guī)則：若XY，Z Y，則有，則有XZ證明：證明：已知已知XZ，由增廣律知，由增廣律知XYYZ，又因，又因XY，可得，可得 XXXYYZ，根據(jù)傳遞律有，根據(jù)傳遞律有XYZ已知已知XY，由增廣律知，由增廣律知XWWY，又因，又因WYZ，可得，可得 XWWYZ，根據(jù)傳遞律有，根據(jù)傳遞律有XWZ已知已知Z Y ，由自反律知，由自反律知YZ，又因，又因XY，所以由傳遞律

15、可得，所以由傳遞律可得 XZ10/165.試舉出試舉出3個多值依賴的實例。個多值依賴的實例。答：答：關(guān)系模式關(guān)系模式MSC(M,S,C)中，中，M為專業(yè)，為專業(yè)，S為學(xué)生，為學(xué)生，C為該專為該專業(yè)的必修課。假設(shè)每個專業(yè)有多個學(xué)生，有一組必修課。設(shè)同業(yè)的必修課。假設(shè)每個專業(yè)有多個學(xué)生，有一組必修課。設(shè)同專業(yè)內(nèi)所有學(xué)生選修的必修課相同。按照語義，對于專業(yè)內(nèi)所有學(xué)生選修的必修課相同。按照語義，對于M的每一的每一個值個值Mi，S有一個完整的集合與之對應(yīng)而不管有一個完整的集合與之對應(yīng)而不管C取何值，所以取何值，所以MS。由于。由于C與與S的完全對稱性，必然有的完全對稱性，必然有MC。關(guān)系模式關(guān)系模式IS

16、A(I,S,A)中，中，I為學(xué)生興趣小組，為學(xué)生興趣小組，S為學(xué)生，為學(xué)生，A為某興為某興趣小組的活動項目。假設(shè)每個興趣小組有多個學(xué)生，有若干活趣小組的活動項目。假設(shè)每個興趣小組有多個學(xué)生，有若干活動項目。每個學(xué)生參加所在興趣小組的所有興趣活動項目，每動項目。每個學(xué)生參加所在興趣小組的所有興趣活動項目，每個活動項目要求該興趣小組的所有學(xué)生參加。按照語義有個活動項目要求該興趣小組的所有學(xué)生參加。按照語義有IS，IA。關(guān)系模式關(guān)系模式RDP(R,D,P)中，中，R為醫(yī)院病房，為醫(yī)院病房，D為醫(yī)務(wù)人員，為醫(yī)務(wù)人員，P為為病人。假設(shè)每個病房住有多個病人，有多個醫(yī)務(wù)人員負(fù)責(zé)醫(yī)治病人。假設(shè)每個病房住有多個

17、病人，有多個醫(yī)務(wù)人員負(fù)責(zé)醫(yī)治和護(hù)理該病房的所有病人。按照語義有和護(hù)理該病房的所有病人。按照語義有RD，RP。11/169. 下圖表示一個公司各部門的層次結(jié)構(gòu)。下圖表示一個公司各部門的層次結(jié)構(gòu)。 對每個部門，數(shù)據(jù)庫中包含部門號對每個部門，數(shù)據(jù)庫中包含部門號(唯一的唯一的)D#、預(yù)算費、預(yù)算費(BUDGET)以及此部門領(lǐng)導(dǎo)人員的職工號以及此部門領(lǐng)導(dǎo)人員的職工號E#(唯一的唯一的)信息。信息。 職工信息包括：職工號、他所參加的生產(chǎn)與科研項目號職工信息包括：職工號、他所參加的生產(chǎn)與科研項目號(J#)、他所在辦公室的電話號碼他所在辦公室的電話號碼(PHONE#)。 生產(chǎn)科研項目包含：項目號生產(chǎn)科研項目包

18、含：項目號(唯一的唯一的)、預(yù)算費。、預(yù)算費。 辦公室信息包含辦公室房間號辦公室信息包含辦公室房間號(唯一的唯一的)、面積。、面積。 對每個職工，數(shù)據(jù)庫中有他曾擔(dān)任過的職務(wù)以及擔(dān)任某一職務(wù)對每個職工，數(shù)據(jù)庫中有他曾擔(dān)任過的職務(wù)以及擔(dān)任某一職務(wù)時的工資歷史。時的工資歷史。 對每個辦公室包含此辦公室中全部電話號碼的信息。對每個辦公室包含此辦公室中全部電話號碼的信息。 請給出你認(rèn)為合理的數(shù)據(jù)依賴，把這個層次結(jié)構(gòu)轉(zhuǎn)換成一組規(guī)請給出你認(rèn)為合理的數(shù)據(jù)依賴，把這個層次結(jié)構(gòu)轉(zhuǎn)換成一組規(guī)范化的關(guān)系。提示：此題可分步完成，第一步先轉(zhuǎn)換成一組范化的關(guān)系。提示：此題可分步完成，第一步先轉(zhuǎn)換成一組1NF的關(guān)系，然后逐步

19、轉(zhuǎn)換為的關(guān)系，然后逐步轉(zhuǎn)換為2NF，3NF，BCNF。12/16答：答：根據(jù)題中的語義假設(shè)給出一組函數(shù)依賴。語義假設(shè)為：一個職工不能根據(jù)題中的語義假設(shè)給出一組函數(shù)依賴。語義假設(shè)為：一個職工不能同時成為多個部門的領(lǐng)導(dǎo)人；一個職工不能同時在多個部門就職；一個職同時成為多個部門的領(lǐng)導(dǎo)人；一個職工不能同時在多個部門就職；一個職工不能同時參加多個生產(chǎn)與科研項目；一個職工不能同時在兩個不同的辦工不能同時參加多個生產(chǎn)與科研項目；一個職工不能同時在兩個不同的辦公室辦公；一個職工不能同時擁有兩部或兩部以上的電話；一個生產(chǎn)與科公室辦公；一個職工不能同時擁有兩部或兩部以上的電話；一個生產(chǎn)與科研項目不能同時分配給多個

20、部門；一個辦公室不能同時分配給多個部門；研項目不能同時分配給多個部門；一個辦公室不能同時分配給多個部門；部門號、職工號、項目號、辦公室號碼及電話號碼是全局唯一的。部門號、職工號、項目號、辦公室號碼及電話號碼是全局唯一的。 設(shè)計一組關(guān)系模式，它們都是屬于設(shè)計一組關(guān)系模式，它們都是屬于1NF的。的。 部門部門DEPT(DEPT#,DBUDGET,MGR_EMP#)，其中，其中DEPT#和和MGR_EMP#都是候選碼都是候選碼 職工職工EMP1(EMP#,DEPT#,PROJ#,OFF#,PHONE#)，候選碼為，候選碼為EMP# ，但，但有有PHONE#OFF#, OFF#DEPT#,PROJ#D

21、EPT# 職務(wù)職務(wù)JOB(EMP#,JOBN)，工資史，工資史SALHIST(EMP#,DATE,JOBN,SALARY) 生產(chǎn)與科研項目生產(chǎn)與科研項目PROJ(PROJ#,DEPT#,PBUDGET) 辦公室辦公室OFFICE(OFF#,DEPT#,AREA)， 電話電話PHONE(PHONE#,OFF#) 分析可知，分析可知，JOB的屬性全包含在的屬性全包含在SALHIST中，所以中，所以JOB可消去?？上ァ?EMP1中中OFF#,DEPT#都傳遞函數(shù)依賴于主碼都傳遞函數(shù)依賴于主碼EMP#，故將，故將EMP#分解。分解。13/16 分解為分解為4個個3NF的關(guān)系模式：的關(guān)系模式： EMP(EMP#,PROJ#,PHONE#)、X(PHONE#,OFF#)、 Y (PROJ#,DEPT#) 和和Z(OFF#,DEPT#) 然而然而X就是就是PHONE，Y是是PROJ的投影，的投影，Z是是OFFICE的投影，的投影，所以所以X、Y和和Z都可以消去。最后得到如下都可以消去。最后得到如下6個關(guān)系模式，它們都個關(guān)系模式，它們都是是3NF，也是，也是BCNF。 DEPT(DEPT#,DBUDGET,MGR_EMP#) EMP(EMP#,