數(shù)據(jù)庫(kù)基礎(chǔ)與應(yīng)用 access2010 第三章 關(guān)系規(guī)范化課件

1、第3章 關(guān)系規(guī)范化本章知識(shí)點(diǎn)關(guān)系規(guī)范化目的和意義 函數(shù)依賴 第一范式（1NF）第二范式 （2NF）第三范式（3NF）BC范式（BCNF）逆規(guī)范化 關(guān)系規(guī)范化的目的和意義E.F.Codd指出：一個(gè)關(guān)系數(shù)據(jù)庫(kù)中的關(guān)系都應(yīng)滿足一定的規(guī)范，才能構(gòu)造出好的關(guān)系模式E. F. Codd埃德加弗蘭克科德（Edgar Frank Codd，19232003）是密執(zhí)安大學(xué)哲學(xué)博士，IBM公司研究員，被譽(yù)為“關(guān)系數(shù)據(jù)庫(kù)之父”，并因?yàn)樵跀?shù)據(jù)庫(kù)管理系統(tǒng)的理論和實(shí)踐方面的杰出貢獻(xiàn)于1981年獲圖靈獎(jiǎng)。20世紀(jì)80年代以來(lái)，計(jì)算機(jī)廠商推出的數(shù)據(jù)庫(kù)管理系統(tǒng)幾乎都支持關(guān)系模型，數(shù)據(jù)庫(kù)領(lǐng)域當(dāng)前的研究工作大都以關(guān)系模型為基礎(chǔ)。關(guān)

2、系規(guī)范化的目的和意義一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)所遵循的標(biāo)準(zhǔn)： 每個(gè)表應(yīng)提供一個(gè)惟一的標(biāo)識(shí)符，用來(lái)區(qū)分不同的行 每個(gè)表應(yīng)當(dāng)存儲(chǔ)單個(gè)實(shí)體類型的數(shù)據(jù) 每個(gè)表不應(yīng)該出現(xiàn)值全部相同的行（或列） 盡量避免接受NULL值（空值）的列 盡量避免值重復(fù)關(guān)系規(guī)范化過程用形式更為簡(jiǎn)潔、結(jié)構(gòu)更加規(guī)范的關(guān)系模式取代原有關(guān)系模式的過程關(guān)系規(guī)范化的目的和意義實(shí)例分析 將專業(yè)和系兩個(gè)實(shí)體存放在一個(gè)表內(nèi)majorNomajorNamemajorLeaderdepNamedepDeanNameM001日語(yǔ)王金寶外語(yǔ)系李強(qiáng)生M002英語(yǔ)陳龍外語(yǔ)系李強(qiáng)生M003阿拉伯語(yǔ)何炅新外語(yǔ)系李強(qiáng)生M004工商管理任德華工商系趙德勝M(fèi)005金融張學(xué)斌工

3、商系趙德勝M(fèi)006國(guó)際貿(mào)易李東生工商系趙德勝M(fèi)007經(jīng)濟(jì)學(xué)錢海波工商系趙德勝M(fèi)008軟件工程朱曉明計(jì)算機(jī)系王海洋M009計(jì)算機(jī)網(wǎng)絡(luò)劉少云計(jì)算機(jī)系王海洋M010建筑學(xué)白意愿建筑系劉玉明關(guān)系規(guī)范化的目的和意義實(shí)例分析數(shù)據(jù)冗余 ：數(shù)據(jù)在數(shù)據(jù)庫(kù)中的重復(fù)存放稱為數(shù)據(jù)冗余數(shù)據(jù)冗余導(dǎo)致的問題：浪費(fèi)存儲(chǔ)空間可能造成數(shù)據(jù)的不一致性，產(chǎn)生插入、刪除和更新異常增加了插入、刪除和更新數(shù)據(jù)的時(shí)間majorNomajorNamemajorLeaderdepNamedepDeanNameM001日語(yǔ)王金寶外語(yǔ)系李強(qiáng)生M002英語(yǔ)陳龍外語(yǔ)系李強(qiáng)生M003阿拉伯語(yǔ)何炅新外語(yǔ)系李強(qiáng)生數(shù)據(jù)重復(fù)關(guān)系規(guī)范化的目的和意義實(shí)例分析插入異常

4、 ：在不規(guī)范的數(shù)據(jù)表中插入數(shù)據(jù)時(shí)，由于實(shí)體完整性約束要求主鍵非空的限制，將會(huì)出現(xiàn)一些有用數(shù)據(jù)無(wú)法插入的情況majorNomajorNamemajorLeaderdepNamedepDeanNameM001日語(yǔ)王金寶外語(yǔ)系李強(qiáng)生M002英語(yǔ)陳龍外語(yǔ)系李強(qiáng)生歷史系張大同主鍵不能為空，插入失敗關(guān)系規(guī)范化的目的和意義設(shè)計(jì)數(shù)據(jù)庫(kù)的方法：憑借經(jīng)驗(yàn)和常識(shí)可以進(jìn)行數(shù)據(jù)庫(kù)初步設(shè)計(jì)使用一些系統(tǒng)方法（如：規(guī)范化理論）來(lái)降低冗余，從而使數(shù)據(jù)庫(kù)設(shè)計(jì)更為合理關(guān)系規(guī)范化是數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)的指南，它是基于函數(shù)依賴和范式的3.1函數(shù)依賴 數(shù)據(jù)依賴是一個(gè)關(guān)系中各屬性之間的相互依賴數(shù)據(jù)依賴包括函數(shù)依賴和多值依賴，我們這本書只討論有關(guān)

5、函數(shù)依賴(Functional Dependencies)的內(nèi)容。函數(shù)依賴涉及到平凡函數(shù)依賴、非平凡函數(shù)依賴、完全函數(shù)依賴、局部（部分）函數(shù)依賴、直接函數(shù)依賴和傳遞函數(shù)依賴等概念。學(xué)生關(guān)系（學(xué)號(hào)，姓名，性別，年齡，專業(yè)編號(hào)，已取得總學(xué)分） 學(xué)號(hào)姓名學(xué)號(hào)性別 學(xué)號(hào)年齡 學(xué)號(hào)專業(yè)編號(hào) 學(xué)號(hào)已取得總學(xué)分3.1函數(shù)依賴 函數(shù)依賴的定義已知：X和Y是關(guān)系R中兩個(gè)屬性（或?qū)傩越M）若對(duì)于X的任何一個(gè)值，只有一個(gè)Y的值與之對(duì)應(yīng)，則稱 X函數(shù)決定Y ，或Y函數(shù)依賴于X，記為：XY，X被稱為決定因素 任何屬性都函數(shù)依賴于候選鍵，所有候選鍵都是決定因子例 子：傳遞與直接函數(shù)依賴的定義：一個(gè)關(guān)系為R（U），X，Y和Z

6、為屬性集U上的子集，其中XY， YZ，但YX且Y不包含Z，則存在XZ，稱此為傳遞函數(shù)依賴。 否則稱XZ為直接函數(shù)依賴。 如：學(xué)號(hào)系名，系名系主任名 所以 學(xué)號(hào)系主任設(shè)一個(gè)關(guān)系為R（U），X，Y和Z為屬性集U上的子集，若XY，則存在XZYZ和XZY。最小函數(shù)依賴集的定義：設(shè)一個(gè)關(guān)系為R（U），X和Y為U的子集，若XY，并且為完全非平凡函數(shù)依賴，同時(shí)Y是單屬性，則稱XY為R的最小函數(shù)依賴。由R中所有最小函數(shù)依賴構(gòu)成R的最小函數(shù)依賴集，其中不含有冗余的傳遞函數(shù)依賴。候選碼的定義：設(shè)一個(gè)關(guān)系為R（U），X為U的一個(gè)子集，若X能夠函數(shù)決定U中每個(gè)屬性，并且X的任何真子集都不能函數(shù)決定U中的每個(gè)屬性，則稱

7、X為關(guān)系R的一個(gè)候選碼。根據(jù)一個(gè)關(guān)系模式的語(yǔ)義，能夠求出它的最小函數(shù)依賴集，從而能夠得到各屬性間的函數(shù)依賴關(guān)系，以及得到所有的候選碼。自反性：若X包含Y，則存在XY。增廣性：若XY，則存在XZYZ。傳遞性：若XY和YZ則存在XZ。合并性：若XY和XZ則存在XYZ。分解性：若XY且Y包含Z，則存在XZ。偽傳遞性：若XY和WYZ則存在WXZ。復(fù)合性：若XY和ZW則存在XZYW。自增性：若XY，則存在WXY。范式 范式的定義范式（Normal Formula：NF）是表結(jié)構(gòu)在優(yōu)化時(shí)必須滿足的一些規(guī)則最重要的范式包括：第一范式（1NF）是一個(gè)關(guān)系的最低規(guī)范化級(jí)別，它確保關(guān)系中的每個(gè)屬性都是單值屬性，即

8、不是復(fù)合屬性。第二范式（2NF）消除了關(guān)系中所有非主屬性對(duì)候選碼的部分依賴。若關(guān)系中的每個(gè)候選碼都是單屬性，則符合第一范式的關(guān)系自然也達(dá)到第二范式。第三范式（3NF）消除了關(guān)系中所有非主屬性對(duì)候選碼的部分和傳遞依賴。 Boyce-Code范式（BCNF）實(shí)例分析： 假設(shè)一個(gè)關(guān)系為R(A,B,C,D,E)，它的最小函數(shù)依賴集為FD= AB,CD, CE ，則該關(guān)系的候選碼為什么？該關(guān)系屬于第幾范式，請(qǐng)簡(jiǎn)要地說(shuō)明理由。若要規(guī)范化到高一級(jí)的范式，則將得到什么樣的關(guān)系。解答：該關(guān)系的候選碼為（A，C）。因?yàn)樵撽P(guān)系中存在有非主屬性對(duì)候選碼的部分函數(shù)數(shù)依賴，即AB,CD， CE ，其中B，C和E只依賴于候

9、選碼的部分A或C，所以該關(guān)系只屬于第一范式。 若要規(guī)范化到高一級(jí)的范式，則需要將關(guān)系R根據(jù)屬性對(duì)候選碼的部分依賴拆分成兩個(gè)關(guān)系，它們分別為： R1（A，B）和R2（C，D，E），這兩個(gè)關(guān)系達(dá)到了BC范式的要求。第一范式 第一范式定義 若關(guān)系R中的每個(gè)屬性（列）都是不可再分的，并且每個(gè)屬性都是單值的，則稱關(guān)系R滿足第一范式，記為：R1NF屬性不可再分：1NF中不能出現(xiàn)子屬性屬性值為單值：每個(gè)單元格中不允許出現(xiàn)一個(gè)以上的數(shù)據(jù)1NF是對(duì)關(guān)系的最低要求，不滿足1NF的關(guān)系是非規(guī)范化的關(guān)系第一范式第二范式第三范式BC范式第一范式 實(shí)例分析 提出 / 分析問題 teacherNoteacherNamete

10、acherTitleteacherTelofficeTelhomeTelT001夏玉明講 師8123498181231231T002李 娟教 授8123498688712136T003王筱琳講 師8123498188245178T004劉明輝劉明浩副教授8123498186570081T005唐 勇副教授8123498283226315T006黃 潔講 師8123498282225743T007曾 惠副教授8123498288710038T008趙文波教 授8123498688461136非規(guī)范化第一范式 實(shí)例分析 問題求解 teacherNoteacherNameteacherTitleof

11、ficeTelhomeTelT001夏玉明講 師8123498181231231T002李 娟教 授8123498688712136T003王筱琳講 師8123498188245178T004劉明輝副教授8123498186570081T005唐 勇副教授8123498283226315T006黃 潔講 師8123498282225743T007曾 惠副教授8123498288710038T008趙文波教 授8123498688461136T009劉明浩副教授8123498186570081第二范式 第二范式定義 若關(guān)系R1NF，并且不存在非主屬性對(duì)候選碼的部分函數(shù)依賴，則稱關(guān)系R滿足第二范式

12、，記為：R2NF1NF轉(zhuǎn)化為2NF：優(yōu)先判斷主鍵的規(guī)范性將依賴部分鍵的非主屬性和所依賴部分鍵組成一個(gè)關(guān)系將依賴整個(gè)鍵的非主屬性和整個(gè)鍵組成另一個(gè)關(guān)系第一范式第二范式第三范式BC范式第二范式 實(shí)例分析 提出問題：滿足1NF，不滿足2NF stuNostuNamestuSexstuAgeclassNoclassCourseNameclassStartTimescoreS001白玉芬女19C001微觀經(jīng)濟(jì)學(xué)2008-3-178S001白玉芬女19C002大學(xué)英語(yǔ)2008-3-167S001白玉芬女19C005操作系統(tǒng)2009-4-6S002唐嘉芝女18C001微觀經(jīng)濟(jì)學(xué)2008-3-192S003劉

13、健鵬男21C006數(shù)據(jù)庫(kù)原理2009-4-897S004田 洋男19C001微觀經(jīng)濟(jì)學(xué)2008-3-166S004田 洋男19C002大學(xué)英語(yǔ)2008-3-165S004田 洋男19C003建筑藝術(shù)2008-3-1663S004田 洋男19C004計(jì)算機(jī)網(wǎng)絡(luò)2008-3-2571S005申一雅男18C004計(jì)算機(jī)網(wǎng)絡(luò)2008-3-2559S005申一雅男18C005操作系統(tǒng)2009-4-6S006范 力男19C005操作系統(tǒng)2009-4-6S007高 紅女20C005操作系統(tǒng)2009-4-6S008張埔濤男18C005操作系統(tǒng)2009-4-6S009蒲惠妃女19C005操作系統(tǒng)2009-4-6

14、第二范式 實(shí)例分析 分析問題：插入異常：在一個(gè)學(xué)生未選擇任何課程時(shí)，該學(xué)生信息不能被插入 更新異常：對(duì)于給定的學(xué)生，學(xué)號(hào)、性別、姓名和年齡重復(fù)多次；對(duì)于給定的班級(jí)，課程名稱、開課時(shí)間重復(fù)多次 刪除異常：當(dāng)需要?jiǎng)h除某個(gè)學(xué)生的信息時(shí)，與該學(xué)生相關(guān)的班級(jí)信息將被刪除；刪除班級(jí)信息時(shí)，選該班級(jí)課程的學(xué)生信息也將被刪除，這種刪除將造成一些信息的丟失第二范式 實(shí)例分析 問題求解stuNostuNamestuSexstuAgeS001白玉芬女19S002唐嘉芝女18S003劉健鵬男21S004田 洋男19S005申一雅男18S006范 力男19S007高 紅女20S008張埔濤男18S009蒲惠妃女19cl

15、assNoclassCourseNameclassStartTimeC001微觀經(jīng)濟(jì)學(xué)2008-3-1C002大學(xué)英語(yǔ)2008-3-1C003建筑藝術(shù)2008-3-16C004計(jì)算機(jī)網(wǎng)絡(luò)2008-3-25C005操作系統(tǒng)2009-4-6C006數(shù)據(jù)庫(kù)原理2009-4-8stuNoclassNoscoreS001C00178S001C00267S001C005S002C00192S003C00697S004C00166S004C00265S004C00363S004C00471S005C00459S005C005S006C005S007C005S008C005S009C005滿足2NF第二范式

16、思 考在按照2NF要求分解之后的關(guān)系模式中，確定學(xué)生表、班級(jí)表和選課表的主鍵和外鍵。 第三范式實(shí)例分析 分析問題 插入異常：如果一個(gè)專業(yè)沒有學(xué)生時(shí)不能插入該專業(yè)的信息更新異常：對(duì)于給定的專業(yè)，專業(yè)名稱和專業(yè)帶頭人姓名重復(fù)多次出現(xiàn)，產(chǎn)生了數(shù)據(jù)冗余，如果一個(gè)專業(yè)的專業(yè)帶頭人姓名發(fā)生變更，則與該專業(yè)相關(guān)的所有記錄都要更新，否則將造成同一專業(yè)其帶頭人不一致，引發(fā)更新異常。刪除異常：當(dāng)一個(gè)專業(yè)只有一個(gè)學(xué)生時(shí)，在刪除該學(xué)生信息的時(shí)候會(huì)將該專業(yè)的信息全部刪除，造成有效數(shù)據(jù)的丟失，引發(fā)刪除異常。第三范式 實(shí)例分析 問題求解stuNostuNamestuSexstuAgemajorNoS001白玉芬女19M00

17、1S002唐嘉芝女18M001S003劉健鵬男21M001S004田 洋男19M001S005申一雅男18M001S006范 力男19M002S007高 紅女20M002S008張埔濤男18M002S009蒲惠妃女19M003滿足3NFmajorNomajorNamemajorLeaderM001日語(yǔ)王金寶M002英語(yǔ)陳龍M003阿拉伯語(yǔ)何炅新第三范式思 考在按照3NF要求分解之后的關(guān)系模式中，確定學(xué)生表和專業(yè)表的主鍵和外鍵。 實(shí)例分析： 假設(shè)一個(gè)關(guān)系為R(A,B,C,D,E)，它的最小函數(shù)依賴集為FD= AB,CD, CE ，則該關(guān)系的候選碼為什么？該關(guān)系屬于第幾范式，請(qǐng)簡(jiǎn)要地說(shuō)明理由。若要

18、規(guī)范化到高一級(jí)的范式，則將得到什么樣的關(guān)系。解答：該關(guān)系的候選碼為（A，C）。因?yàn)樵撽P(guān)系中存在有非主屬性對(duì)候選碼的部分函數(shù)數(shù)依賴，即AB,CD，CE ，其中B，D和E只依賴于候選碼的部分A或C，所以該關(guān)系只屬于第一范式。 若要規(guī)范化到高一級(jí)的范式，則需要將關(guān)系R根據(jù)屬性對(duì)候選碼的部分依賴拆分成兩個(gè)關(guān)系，它們分別為： R1（A，B）和R2（C，D，E），這兩個(gè)關(guān)系達(dá)到了3NF的要求。BC范式 BC范式定義若關(guān)系R1NF，并且它的決定因子都是候選鍵，則稱R滿足BC范式，記為：RBCNF第一范式第二范式第三范式BC范式BC范式 實(shí)例分析 提出問題：滿足3NF，不滿足BCNFdepNodepNamep

19、roductNoaccountD001外語(yǔ)系P001100D001外語(yǔ)系P002120D001外語(yǔ)系P0032D001外語(yǔ)系P0046D002工商系P00180D002工商系P002100D003計(jì)算機(jī)系P00178D003計(jì)算機(jī)系P002160D004建筑系P00162BC范式實(shí)例分析 分析問題 該關(guān)系中存在如下函數(shù)依賴：(depNo, productNo) account(depName, productNo ) accountdepNodepNamedepNamedepNo候選鍵包括：（depNo, productNo）和（depName，productNo）主屬性包括：depNo、de

20、pName和productNo非主屬性包括：accountBC范式 實(shí)例分析 分析問題 BCNF轉(zhuǎn)換方法將產(chǎn)生函數(shù)依賴關(guān)系的主屬性組成一個(gè)新關(guān)系組合剩余的屬性，合并相同的元組BC范式 實(shí)例分析 問題求解depNoproductNoaccountD001P001100D001P002120D001P0032D001P0046D002P00180D002P002100D003P00178D003P002160D004P00162滿足BCNFdepNodepNameD001外語(yǔ)系D002工商系D003計(jì)算機(jī)系D004建筑系逆規(guī)范化 通過規(guī)范化可以分解得到一些結(jié)構(gòu)簡(jiǎn)單的表，但是在查詢時(shí)可能需要連接多個(gè)

21、表，這造成了大量的數(shù)據(jù)庫(kù)邏輯運(yùn)算，從而降低了查詢效率為了提高查詢效率，可以在特定的表中引入一些額外的屬性數(shù)據(jù)庫(kù)設(shè)計(jì)允許存在合理的冗余逆規(guī)范化逆規(guī)范化的定義 將兩個(gè)或多個(gè)滿足高范式的關(guān)系模式通過自然連接，重新合并成一個(gè)滿足較低范式的關(guān)系模式的過程稱為逆規(guī)范化規(guī)范化和逆規(guī)范化是相互矛盾的，何時(shí)進(jìn)行規(guī)范化、何時(shí)進(jìn)行逆規(guī)范化、進(jìn)行到什么程度，在具體的應(yīng)用環(huán)境中，需要數(shù)據(jù)庫(kù)設(shè)計(jì)者仔細(xì)分析和平衡逆規(guī)范化需要設(shè)計(jì)者在關(guān)系中有意地引入一些冗余以改進(jìn)系統(tǒng)的查詢性能逆規(guī)范化實(shí)例分析 提出問題 stuNostuNamestuSexstuAgeS001白玉芬女19S002唐嘉芝女18stuNoclassNoscore

22、S001C00178S001C00267S001C00356S002C00192classNoclassCourseNameclassStartTimeclassCreditC001微觀經(jīng)濟(jì)學(xué)2008-3-12C002大學(xué)英語(yǔ)2008-3-14C003建筑藝術(shù)2008-3-162 計(jì)算stuNo= S001學(xué)生的已獲學(xué)分，需要對(duì)三個(gè)表進(jìn)行連接逆規(guī)范化實(shí)例分析 分析問題 查詢和計(jì)算過程在經(jīng)過數(shù)據(jù)庫(kù)編譯后，將會(huì)變成一個(gè)比較繁瑣的程序在后臺(tái)運(yùn)行，尤其在數(shù)據(jù)庫(kù)記錄非常多、用戶訪問量很大的情況下將會(huì)造成用戶難以忍受的延遲。在表中適當(dāng)增加一定冗余，可以減少數(shù)據(jù)庫(kù)服務(wù)器的負(fù)荷，提高查詢速度，如果需要經(jīng)常查詢和統(tǒng)計(jì)學(xué)生已獲得學(xué)分，可以在原來(lái)的學(xué)生表中增加一個(gè)新的字段stu