第4章-關(guān)系規(guī)范化

第4章關(guān)系規(guī)范化劉根萍情境：要求設(shè)計(jì)教學(xué)管理數(shù)據(jù)庫(kù)，其關(guān)系模式如下：

SCD(SNO,SN,AGE,DEPT,MN,CNO,SCORE)其中，SNO表示學(xué)生學(xué)號(hào)，SN表示學(xué)生姓名，AGE表示學(xué)生年齡，DEPT表示學(xué)生所在的系別，MN表示系主任姓名，CNO表示課程號(hào)，SCORE表示成績(jī)。

根據(jù)實(shí)際情況，這些數(shù)據(jù)有如下語(yǔ)義規(guī)定：一個(gè)系有若干個(gè)學(xué)生，但一個(gè)學(xué)生只屬于一個(gè)系；一個(gè)系只有一名系主任，但一個(gè)系主任可以同時(shí)兼幾個(gè)系的系主任；一個(gè)學(xué)生可以選修多門功課，每門課程可有若干學(xué)生選修；每個(gè)學(xué)生學(xué)習(xí)課程有一個(gè)成績(jī)。問(wèn)題的提出劉根萍關(guān)系SCDSNOSNAGEDEPTMNCNOSCORES1趙亦17計(jì)算機(jī)劉偉C190S1趙亦17計(jì)算機(jī)劉偉C285S2錢爾18信息王平C557S2錢爾18信息王平C680S2錢爾18信息王平C7

70S2錢爾18信息王平C570S3孫珊20信息王平C10S3孫珊20信息王平C270S3孫珊20信息王平C485S4李思男自動(dòng)化劉偉C193填入一部分具體的數(shù)據(jù)，則可得到SCD關(guān)系模式的實(shí)例，即一個(gè)教學(xué)管理數(shù)據(jù)庫(kù)，如下圖所示：(SNO,CNO)屬性的組合能唯一標(biāo)識(shí)一個(gè)元組，所以(SNO,CNO)是該關(guān)系模式的主關(guān)系鍵。劉根萍每個(gè)系名和系主任的名字存儲(chǔ)的次數(shù)等于該系的學(xué)生人數(shù)乘以每個(gè)學(xué)生選修的課程門數(shù)，同時(shí)學(xué)生的姓名、年齡也都要重復(fù)存儲(chǔ)多次，數(shù)據(jù)的冗余度很大，浪費(fèi)了存儲(chǔ)空間。1.數(shù)據(jù)冗余劉根萍如果某個(gè)新系沒有招生，尚無(wú)學(xué)生時(shí)，則系名和系主任的信息無(wú)法插入到數(shù)據(jù)庫(kù)中。因?yàn)?SNO,CNO)是主關(guān)系鍵，不能為空，而這時(shí)沒有學(xué)生，SNO和CNO均無(wú)值，因此不能進(jìn)行插入操作。當(dāng)某個(gè)學(xué)生尚未選課，即CNO未知，實(shí)體完整性約束還規(guī)定，主關(guān)系鍵的值不能部分為空，同樣不能進(jìn)行插入操作。2.插入異常劉根萍某系學(xué)生全部畢業(yè)而沒有招生時(shí)，刪除全部學(xué)生的記錄則系名、系主任也隨之刪除，而這個(gè)系依然存在，在數(shù)據(jù)庫(kù)中卻無(wú)法找到該系的信息。另外，如果某個(gè)學(xué)生不再選修C1課程，本應(yīng)該只刪去C1，但C1是主關(guān)系鍵的一部分，為保證實(shí)體完整性，必須將整個(gè)元組一起刪掉，這樣，有關(guān)該學(xué)生的其它信息也隨之丟失。存在的問(wèn)題3.刪除異常劉根萍如果學(xué)生改名，則該學(xué)生的所有記錄都要逐一修改SN；又如某系更換系主任，則屬于該系的學(xué)生記錄都要修改MN的內(nèi)容，稍有不慎，就有可能漏改某些記錄，這就會(huì)造成數(shù)據(jù)的不一致性，破壞了數(shù)據(jù)的完整性。4.更新異常劉根萍由于存在以上問(wèn)題，我們說(shuō)，SCD是一個(gè)不好的關(guān)系模式。產(chǎn)生上述問(wèn)題的原因，直觀地說(shuō)，是因?yàn)殛P(guān)系中“包羅萬(wàn)象”，內(nèi)容太雜了。那么，怎樣才能得到一個(gè)好的關(guān)系模式呢？我們把關(guān)系模式SCD分解為下面三個(gè)結(jié)構(gòu)簡(jiǎn)單的關(guān)系模式：學(xué)生關(guān)系S(SNO,SN,AGE,DEPT)選課關(guān)系SC(SNO,CNO,SCORE)系關(guān)系D(DEPT,MN)劉根萍SSCSNOSNAGEDEPT

SNOCNOSCORES1趙亦17計(jì)算機(jī)

S1C190S2錢爾18信息

S1C285S3孫珊20信息

S2C557S4李思21自動(dòng)化

S2C680

S2C7

D

S2C570DEPTMN

S3C10計(jì)算機(jī)劉偉

S3C270信息王平

S3C485自動(dòng)化劉偉

S4C193分解后的關(guān)系模式

劉根萍在以上三個(gè)關(guān)系模式中，實(shí)現(xiàn)了信息的某種程度的分離，S中存儲(chǔ)學(xué)生基本信息，與所選課程及系主任無(wú)關(guān)；D中存儲(chǔ)系的有關(guān)信息，與學(xué)生無(wú)關(guān)；SC中存儲(chǔ)學(xué)生選課的信息，而與所學(xué)生及系的有關(guān)信息無(wú)關(guān)。與SCD相比，分解后，數(shù)據(jù)冗余度明顯降低。當(dāng)新插入一個(gè)系時(shí)，只要在關(guān)系D中添加一條記錄。當(dāng)某個(gè)學(xué)生尚未選課，只要在關(guān)系S中添加一條學(xué)生記錄，而與選課關(guān)系無(wú)關(guān)，這就避免了插入異常。當(dāng)一個(gè)系的學(xué)生全部畢業(yè)時(shí)，只需在S中刪除該系的全部學(xué)生記錄，而關(guān)系D中有關(guān)該系的信息仍然保留，從而不會(huì)引起刪除異常。同時(shí)，由于數(shù)據(jù)冗余度的降低，數(shù)據(jù)沒有重復(fù)存儲(chǔ)，也不會(huì)引起更新異常。劉根萍經(jīng)過(guò)上述分析，我們說(shuō)分解后的關(guān)系模式是一個(gè)好的關(guān)系數(shù)據(jù)庫(kù)模式。從而得出結(jié)論，一個(gè)好的關(guān)系模式應(yīng)該具備以下四個(gè)條件：1.盡可能少的數(shù)據(jù)冗余。2.沒有插入異常。3.沒有刪除異常。4.沒有更新異常。

劉根萍但要注意，一個(gè)好的關(guān)系模式并不是在任何情況下都是最優(yōu)的，比如查詢某個(gè)學(xué)生選修課程名及所在系的系主任時(shí)，要通過(guò)連接，而連接所需要的系統(tǒng)開銷非常大，因此要以實(shí)際設(shè)計(jì)的目標(biāo)出發(fā)進(jìn)行設(shè)計(jì)如何按照一定的規(guī)范設(shè)計(jì)關(guān)系模式，將結(jié)構(gòu)復(fù)雜的關(guān)系分解成結(jié)構(gòu)簡(jiǎn)單的關(guān)系，從而把不好的關(guān)系數(shù)據(jù)庫(kù)模式轉(zhuǎn)變?yōu)楹玫年P(guān)系數(shù)據(jù)庫(kù)模式，這就是關(guān)系的規(guī)范化。我們要設(shè)計(jì)的關(guān)系模式中的各屬性是相互依賴、相互制約的，這樣才構(gòu)成了一個(gè)結(jié)構(gòu)嚴(yán)謹(jǐn)?shù)恼w。因此在設(shè)計(jì)關(guān)模式時(shí)，必須從語(yǔ)義上分析這些依賴關(guān)系。數(shù)據(jù)庫(kù)模式的好壞和關(guān)系中各屬性間的依賴關(guān)系有關(guān)。函數(shù)依賴數(shù)據(jù)依賴屬性之間相互依賴、相互制約的關(guān)系被稱為數(shù)據(jù)依賴

關(guān)系模式中的最常見的數(shù)據(jù)依賴為：函數(shù)依賴函數(shù)依賴(FunctionalDependencies)學(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é)分函數(shù)依賴函數(shù)依賴的定義已知：X和Y是關(guān)系R中兩個(gè)屬性（或?qū)傩越M）若對(duì)于X的任何一個(gè)值，只有一個(gè)Y的值與之對(duì)應(yīng)，則稱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ù)依賴思考：一個(gè)關(guān)系模式有n個(gè)屬性，那么在它上面成立的所有可能的函數(shù)依賴有多少個(gè)？非平凡的函數(shù)依賴又有多少個(gè)？如果XY，但YX，則稱其為非平凡的函數(shù)依賴，否則稱為平凡的函數(shù)依賴如（S#，SN）SN是平凡的函數(shù)依賴部分函數(shù)依賴

（S#，C#）G（S#，C#）SNXYX'Y在R(U)中，如果XY，且對(duì)于任意X的真子集X'，都有，則稱Y對(duì)X完全函數(shù)依賴，記作XY否則稱為Y對(duì)X部分函數(shù)依賴，記作傳遞函數(shù)依賴S#SD，SDDEAN在R(U)中，如果：則稱Z對(duì)X傳遞函數(shù)依賴XY，YZ，YX，且ZY范式范式的定義范式（NormalFormula：NF）是表結(jié)構(gòu)在優(yōu)化時(shí)必須滿足的一些規(guī)則最重要的范式包括：第一范式（1NF）第二范式（2NF）第三范式（3NF）Boyce-Code范式（BCNF）范式范式的特征把關(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

第一范式第一范式定義若關(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)系1NF 關(guān)系中每一分量不可再分。即不能以集合、序列等作為屬性值S#C#S1{C1，C2，C3}S#C#S1C1S1C2S1C31NF對(duì)象－關(guān)系數(shù)據(jù)庫(kù)嵌套關(guān)系傳統(tǒng)數(shù)據(jù)庫(kù)平面關(guān)系1NFS#C#S1{C1，C2，C3}適合于查詢每個(gè)學(xué)生的選修課程S#C#{S1,S2,S3}C1適合于查詢每門課程的選修學(xué)生兩個(gè)都保存，數(shù)據(jù)冗余，更新困難只保存一個(gè)，某些查詢困難1NF原子粒度的選擇分量是否需要再分，與具體應(yīng)用有關(guān)。如果用到值的一部分，則需要進(jìn)一步分割否則需要應(yīng)用編碼解析姓名生日王軍78.7.10張立79.7.10李明80.3.28姓名年月日王軍787.10張立797.10李明803.28只是查詢出生日期比較兩人生肖是否相同姓名年月日王軍78710張立79710李明80328比較兩人生日是否相同1NF較細(xì)的原子粒度有助于標(biāo)準(zhǔn)化，施加約束，避免輸入錯(cuò)誤，從而提高數(shù)據(jù)質(zhì)量北京大學(xué)，北京，中國(guó)，100871，06/25/2009中國(guó)，北京，北京大學(xué)，100871，25/06/2009國(guó)家城市單位郵編年月日中國(guó)北京北京大學(xué)10087120090625第一范式實(shí)例分析提出/分析問(wèn)題

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

teacherNoteacherNameteacherTitleofficeTelhomeTelT001夏玉明講師8123498181231231T002李娟教授8123498688712136T003王筱琳講師8123498188245178T004劉明輝副教授8123498186570081T005唐勇副教授8123498283226315T006黃潔講師8123498282225743T007曾惠副教授8123498288710038T008趙文波教授8123498688461136T009劉明浩副教授8123498186570081劉根萍第一范式可能存在的問(wèn)題：數(shù)據(jù)冗余：如各位老師的信息重復(fù)。插入異常：如果要插入劉老師的個(gè)人信息，但劉老師未開課，會(huì)造成缺關(guān)鍵字“學(xué)號(hào)”、“課程號(hào)”。刪除異常：當(dāng)要?jiǎng)h除課程號(hào)=“C3”

的元組，會(huì)丟失李老師的信息。修改量大。837757857890成績(jī)2-1434王C1041-0145周C2011-0145周C2033-1527李C3032-1434王C1022-1434王C101辦公室教師年齡教師課程號(hào)學(xué)號(hào)第二范式第二范式定義若關(guān)系R∈1NF，并且每個(gè)非主屬性都函數(shù)依賴于整個(gè)候選鍵，而不是依賴于部分候選鍵，則稱關(guān)系R滿足第二范式，記為：R∈2NF示例：下列關(guān)系模式是1NF，是否是2NF？為什么？

SCD（學(xué)號(hào)，姓名，課程號(hào)，成績(jī)，系名，系主任）主碼為復(fù)合碼：學(xué)號(hào)+課程號(hào)不是。因?yàn)椋骸靶彰?、系名、系主任”與主碼是部分函數(shù)依賴（即：只依賴于學(xué)號(hào)）解決方法：消除部分函數(shù)依賴。劉根萍分解為2NF的方法：把關(guān)系模式中對(duì)主碼完全函數(shù)依賴的非主屬性與決定它們的主碼放在一個(gè)關(guān)系模式中。把對(duì)主碼部分函數(shù)依賴的非主屬性和決定它們的主屬性放在一個(gè)關(guān)系模式中。檢查分解后的新模式，如果仍不是2NF，則繼續(xù)按照前面的方法進(jìn)行分解，直到達(dá)到要求。關(guān)系模式SCD的分解結(jié)果如下：SC（學(xué)號(hào)，課程號(hào)，成績(jī)）SD（學(xué)號(hào)，姓名，系名，系主任）第二范式劉根萍定義如果關(guān)系模式R為第二范式，且R中每個(gè)非主屬性都不傳遞函數(shù)依賴于R的某個(gè)候選碼，則稱R為3NF模式。示例下列關(guān)系模式是2NF，是否為3NF？為什么？

SD（學(xué)號(hào)，姓名，系名，系主任）解決方法：消除傳遞函數(shù)依賴。不是。因?yàn)椋簩W(xué)號(hào)→系名，系名→系主任，存在傳遞依賴關(guān)系。第三范式劉根萍分解為3NF的方法：把直接對(duì)主碼函數(shù)依賴的非主屬性與決定它們的主碼放在一個(gè)關(guān)系模式中。把造成傳遞函數(shù)依賴的決定因素連同被它們決定的屬性放在一個(gè)關(guān)系模式中。檢查分解后的新模式，如果不是3NF，則繼續(xù)按照前面的方法進(jìn)行分解，直到達(dá)到要求。關(guān)系模式SD的分解結(jié)果如下：SD（學(xué)號(hào)，姓名，系名）SD（系名，系主任）劉根萍各范式間的關(guān)系關(guān)系分解原則1.無(wú)損連接2.保持依賴

對(duì)關(guān)系模式進(jìn)行分解的目的：使關(guān)系模式更加規(guī)范化，從而減少以至消除數(shù)據(jù)冗余和更新異常。要使關(guān)系模式的分解有意義，模式分解需要滿足一些約束條件是分解不能破壞原來(lái)的語(yǔ)義，即模式分解要符合無(wú)損連接和保持函數(shù)依賴的原則。1.無(wú)損連接

如果對(duì)新的關(guān)系進(jìn)行自然連接得到的元組的集合與原關(guān)系完全一致，則稱為無(wú)損連接。無(wú)損連接保證分解前后關(guān)系模式的信息不能丟失和增加，保持原有的信息不變。反映了模式分解的數(shù)據(jù)等價(jià)原則。2.保持依賴分解前所有的函數(shù)依賴要由分解后的所有關(guān)系模式中的函數(shù)依賴反映。分解后的關(guān)系不能破壞原來(lái)的函數(shù)依賴(不能破壞原來(lái)的語(yǔ)義)，即保持分解前后原有的函數(shù)依賴依然成立。