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

第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埃德加·弗蘭克·科德（EdgarFrankCodd，1923－2003）是密執(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)系規(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ī)范化過(guò)程用形式更為簡(jiǎn)潔、結(jié)構(gòu)更加規(guī)范的關(guān)系模式取代原有關(guān)系模式的過(guò)程關(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é)斌工商系趙德勝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í)例分析插入異常：在不規(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í)例分析刪除異常在不規(guī)范的數(shù)據(jù)表中，刪除某條記錄會(huì)將其中一部分有用數(shù)據(jù)一起刪除有用數(shù)據(jù)被刪除majorNomajorNamemajorLeaderdepNamedepDeanNameM009計(jì)算機(jī)網(wǎng)絡(luò)劉少云計(jì)算機(jī)系王海洋M010建筑學(xué)白意愿建筑系劉玉明關(guān)系規(guī)范化的目的和意義實(shí)例分析更新異常在不規(guī)范的數(shù)據(jù)表中修改某條記錄的特定分量，而其它記錄中的等值分量不能同步更新，則會(huì)導(dǎo)致數(shù)據(jù)不一致majorNomajorNamemajorLeaderdepNamedepDeanNameM001日語(yǔ)王金寶外語(yǔ)系李強(qiáng)生M002英語(yǔ)陳龍外語(yǔ)系李強(qiáng)生M003阿拉伯語(yǔ)何炅新外語(yǔ)系劉強(qiáng)生數(shù)據(jù)不一致關(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)函數(shù)依賴(FunctionalDependencies)的內(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，記為：X→Y，X被稱為決定因素任何屬性都函數(shù)依賴于候選鍵，所有候選鍵都是決定因子例子：函數(shù)依賴思考下面給出的關(guān)系表R(A,B,C,D)滿足下述哪個(gè)函數(shù)依賴？ABCD1431271130221233A.A→BB.B→DC.D→CD.A→D

平凡與非平凡函數(shù)依賴的定義：設(shè)一個(gè)關(guān)系為R（U），X和Y為屬性集U上的子集，若X→Y，且X不包含Y，則稱X→Y為非平凡函數(shù)依賴，否則，若X包含Y，則稱X→Y為平凡函數(shù)依賴。 如（學(xué)號(hào)，姓名）→性別非平凡函數(shù)依賴， 如（學(xué)號(hào)，姓名）→姓名平凡函數(shù)依賴完全與部分函數(shù)依賴的定義：設(shè)一個(gè)關(guān)系為R（U），X和Y為屬性集U上的子集，若X→Y，同時(shí)X的一個(gè)真子集X’也能夠函數(shù)決定Y，即X’→Y，則稱X部分函數(shù)決定Y，或Y部分函數(shù)依賴于X，記作X-P→Y。否則若不存在X的一個(gè)真子集X’

，使得X’也能夠函數(shù)決定Y，則稱X完全函數(shù)決定Y，或Y完全函數(shù)依賴于X，記為X-f→Y。 如（學(xué)號(hào)，課程號(hào)）-f→成績(jī) （學(xué)號(hào)，性別）-p→年齡例:關(guān)系模式S（學(xué)號(hào)，姓名，系名，系主任，課程號(hào)，成績(jī)）主碼（學(xué)號(hào)，課程號(hào)）函數(shù)依賴：（學(xué)號(hào)，課程號(hào)）-f→成績(jī)學(xué)號(hào)→姓名（學(xué)號(hào)，課程號(hào)）-p→姓名學(xué)號(hào)→系名（學(xué)號(hào)，課程號(hào)）-p→系名系名→系主任傳遞與直接函數(shù)依賴的定義：一個(gè)關(guān)系為R（U），X，Y和Z為屬性集U上的子集，其中X→Y，Y→Z，但Y\→X且Y不包含Z，則存在X→Z，稱此為傳遞函數(shù)依賴。否則稱X→Z為直接函數(shù)依賴。如：學(xué)號(hào)→系名，系名→系主任名所以學(xué)號(hào)→系主任設(shè)一個(gè)關(guān)系為R（U），X，Y和Z為屬性集U上的子集，若X→Y，則存在XZ→YZ和XZ→Y。最小函數(shù)依賴集的定義：設(shè)一個(gè)關(guān)系為R（U），X和Y為U的子集，若X→Y，并且為完全非平凡函數(shù)依賴，同時(shí)Y是單屬性，則稱X→Y為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è)屬性，則稱X為關(guān)系R的一個(gè)候選碼。根據(jù)一個(gè)關(guān)系模式的語(yǔ)義，能夠求出它的最小函數(shù)依賴集，從而能夠得到各屬性間的函數(shù)依賴關(guān)系，以及得到所有的候選碼。自反性：若X包含Y，則存在X→Y。增廣性：若X→Y，則存在XZ→YZ。傳遞性：若X→Y和Y→Z則存在X→Z。合并性：若X→Y和X→Z則存在X→YZ。分解性：若X→Y且Y包含Z，則存在X→Z。偽傳遞性：若X→Y和WY→Z則存在WX→Z。復(fù)合性：若X→Y和Z→W則存在XZ→YW。自增性：若X→Y，則存在WX→Y。范式范式的定義范式（NormalFormula：NF）是表結(jié)構(gòu)在優(yōu)化時(shí)必須滿足的一些規(guī)則最重要的范式包括：第一范式（1NF）是一個(gè)關(guān)系的最低規(guī)范化級(jí)別，它確保關(guān)系中的每個(gè)屬性都是單值屬性，即不是復(fù)合屬性。第二范式（2NF）消除了關(guān)系中所有非主屬性對(duì)候選碼的部分依賴。若關(guān)系中的每個(gè)候選碼都是單屬性，則符合第一范式的關(guān)系自然也達(dá)到第二范式。第三范式（3NF）消除了關(guān)系中所有非主屬性對(duì)候選碼的部分和傳遞依賴。Boyce-Code范式（BCNF）范式范式的特征E.F.Codd把關(guān)系模式應(yīng)滿足的規(guī)則劃分成幾級(jí)，每一級(jí)稱為一個(gè)范式范式的等級(jí)越高，應(yīng)滿足的條件也越嚴(yán)規(guī)范化一個(gè)低級(jí)范式的關(guān)系模式，通過(guò)分解（投影）方法可以轉(zhuǎn)換成若干高一級(jí)范式的關(guān)系模式，這個(gè)過(guò)程稱為規(guī)范化1NF

2NF

3NF

BCNF

BC范式消除了關(guān)系中所有屬性對(duì)候選碼的部分和傳遞依賴。若一個(gè)關(guān)系達(dá)到了第三范式，并且它只有單個(gè)候選碼，或者它的每個(gè)候選碼都是單屬性，則該關(guān)系自然也達(dá)到BC范式。關(guān)系規(guī)范化的過(guò)程就是概念單一化和逐步分解關(guān)系的過(guò)程，就是把屬性間存在的部分和傳遞依賴逐步轉(zhuǎn)化為關(guān)系之間1對(duì)1或1對(duì)多聯(lián)系的過(guò)程。通過(guò)關(guān)系的規(guī)范化能夠逐步消除數(shù)據(jù)冗余和操作異常，從而提高數(shù)據(jù)的共享度，提高插入、刪除修改數(shù)據(jù)安全性、一致性、單一性和靈活性。但規(guī)范化程度越高，查詢時(shí)愈需要進(jìn)行多個(gè)關(guān)系之間的連接操作，從而增加了一些查詢的復(fù)雜性。對(duì)于一般應(yīng)用來(lái)說(shuō)，通常是規(guī)范化到第三范式就可以了。實(shí)例分析：

假設(shè)一個(gè)關(guān)系為R(A,B,C,D,E)，它的最小函數(shù)依賴集為FD={A→B,C→D,C→E}，則該關(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ù)依賴，即A→B,C→D，C→E，其中B，C和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á)到了BC范式的要求。第一范式第一范式定義若關(guān)系R中的每個(gè)屬性（列）都是不可再分的，并且每個(gè)屬性都是單值的，則稱關(guān)系R滿足第一范式，記為：R∈1NF屬性不可再分：1NF中不能出現(xiàn)子屬性屬性值為單值：每個(gè)單元格中不允許出現(xiàn)一個(gè)以上的數(shù)據(jù)1NF是對(duì)關(guān)系的最低要求，不滿足1NF的關(guān)系是非規(guī)范化的關(guān)系第一范式第二范式第三范式BC范式第一范式實(shí)例分析提出/分析問題

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

teacherNoteacherNameteacherTitleofficeTelhomeTelT001夏玉明講師8123498181231231T002李娟教授8123498688712136T003王筱琳講師8123498188245178T004劉明輝副教授8123498186570081T005唐勇副教授8123498283226315T006黃潔講師8123498282225743T007曾惠副教授8123498288710038T008趙文波教授8123498688461136T009劉明浩副教授8123498186570081第二范式第二范式定義若關(guān)系R∈1NF，并且不存在非主屬性對(duì)候選碼的部分函數(shù)依賴，則稱關(guān)系R滿足第二范式，記為：R∈2NF1NF轉(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劉健鵬男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第二范式實(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蒲惠妃女19classNoclassCourseNameclassStartTimeC001微觀經(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第二范式思考在按照2NF要求分解之后的關(guān)系模式中，確定學(xué)生表、班級(jí)表和選課表的主鍵和外鍵。第三范式第三范式定義若關(guān)系R∈2NF，并且不存在非主屬性對(duì)候選碼的傳遞函數(shù)依賴，則稱R滿足第三范式，記為：R∈3NF2NF轉(zhuǎn)化為3NF：將不只依賴于鍵的非主屬性和所依賴的非主屬性組成一個(gè)關(guān)系原有關(guān)系中去掉那些不只依賴于鍵的非主屬性，剩余部分組成一個(gè)關(guān)系第一范式第二范式第三范式BC范式第三范式實(shí)例分析提出問題：滿足2NF，不滿足3NFstuNostuNamestuSexstuAgemajorNomajorNamemajorLeaderS001白玉芬女19M001日語(yǔ)王金寶S002唐嘉芝女18M001日語(yǔ)王金寶S003劉健鵬男21M001日語(yǔ)王金寶S004田洋男19M001日語(yǔ)王金寶S005申一雅男18M001日語(yǔ)王金寶S006范力男19M002英語(yǔ)陳龍S007高紅女20M002英語(yǔ)陳龍S008張埔濤男18M002英語(yǔ)陳龍S009蒲惠妃女19M003阿拉伯語(yǔ)何炅新第三范式實(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白玉芬女19M001S002唐嘉芝女18M001S003劉健鵬男21M001S004田洋男