第八章關(guān)系模式規(guī)范化

第八章關(guān)系模式規(guī)范化劉根萍本章內(nèi)容問題的提出函數(shù)依賴關(guān)系的規(guī)范化劉根萍數(shù)據(jù)庫的基本概念學(xué)習(xí)目標(biāo)認(rèn)知目標(biāo)：了解函數(shù)依賴、關(guān)系規(guī)范化的含義理解三種范式的定義能力目標(biāo):能判別關(guān)系的范式類別(重點(diǎn)、難點(diǎn))能進(jìn)行范式的分解（重點(diǎn)、難點(diǎn)）劉根萍在關(guān)系數(shù)據(jù)庫系統(tǒng)中，關(guān)系模型包括一組關(guān)系模式，各個(gè)關(guān)系不是完全孤立的，數(shù)據(jù)庫的設(shè)計(jì)較層次和網(wǎng)狀模型更為重要。如何設(shè)計(jì)一個(gè)適合的關(guān)系數(shù)據(jù)庫系統(tǒng)，關(guān)鍵是關(guān)系數(shù)據(jù)庫模式的設(shè)計(jì)一個(gè)好的關(guān)系數(shù)據(jù)庫模式應(yīng)該包括多少關(guān)系模式每一個(gè)關(guān)系模式又應(yīng)該包括哪些屬性如何將這些相互關(guān)聯(lián)的關(guān)系模式組建一個(gè)適合的關(guān)系模型這些工作決定了到整個(gè)系統(tǒng)運(yùn)行的效率，也是系統(tǒng)成敗的關(guān)鍵所在，所以必須在關(guān)系數(shù)據(jù)庫的規(guī)范化理論的指導(dǎo)下逐步完成。

一、問題的提出劉根萍情境：要求設(shè)計(jì)教學(xué)管理數(shù)據(jù)庫，其關(guān)系模式如下：

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

根據(jù)實(shí)際情況，這些數(shù)據(jù)有如下語義規(guī)定：一個(gè)系有若干個(gè)學(xué)生，但一個(gè)學(xué)生只屬于一個(gè)系；一個(gè)系只有一名系主任，但一個(gè)系主任可以同時(shí)兼幾個(gè)系的系主任；一個(gè)學(xué)生可以選修多門功課，每門課程可有若干學(xué)生選修；每個(gè)學(xué)生學(xué)習(xí)課程有一個(gè)成績。一、問題的提出劉根萍關(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ù)庫，如下圖所示：(SNO,CNO)屬性的組合能唯一標(biāo)識一個(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è)新系沒有招生，尚無學(xué)生時(shí)，則系名和系主任的信息無法插入到數(shù)據(jù)庫中。因?yàn)?SNO,CNO)是主關(guān)系鍵，不能為空，而這時(shí)沒有學(xué)生，SNO和CNO均無值，因此不能進(jìn)行插入操作。當(dāng)某個(gè)學(xué)生尚未選課，即CNO未知，實(shí)體完整性約束還規(guī)定，主關(guān)系鍵的值不能部分為空，同樣不能進(jìn)行插入操作。存在的問題2.插入異常劉根萍某系學(xué)生全部畢業(yè)而沒有招生時(shí)，刪除全部學(xué)生的記錄則系名、系主任也隨之刪除，而這個(gè)系依然存在，在數(shù)據(jù)庫中卻無法找到該系的信息。另外，如果某個(gè)學(xué)生不再選修C1課程，本應(yīng)該只刪去C1，但C1是主關(guān)系鍵的一部分，為保證實(shí)體完整性，必須將整個(gè)元組一起刪掉，這樣，有關(guān)該學(xué)生的其它信息也隨之丟失。存在的問題3.刪除異常劉根萍如果學(xué)生改名，則該學(xué)生的所有記錄都要逐一修改SN；又如某系更換系主任，則屬于該系的學(xué)生記錄都要修改MN的內(nèi)容，稍有不慎，就有可能漏改某些記錄，這就會(huì)造成數(shù)據(jù)的不一致性，破壞了數(shù)據(jù)的完整性。存在的問題4.更新異常劉根萍由于存在以上問題，我們說，SCD是一個(gè)不好的關(guān)系模式。產(chǎn)生上述問題的原因，直觀地說，是因?yàn)殛P(guān)系中“包羅萬象”，內(nèi)容太雜了。那么，怎樣才能得到一個(gè)好的關(guān)系模式呢？我們把關(guān)系模式SCD分解為下面三個(gè)結(jié)構(gòu)簡單的關(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é)生基本信息，與所選課程及系主任無關(guān)；D中存儲(chǔ)系的有關(guān)信息，與學(xué)生無關(guān)；SC中存儲(chǔ)學(xué)生選課的信息，而與所學(xué)生及系的有關(guān)信息無關(guān)。與SCD相比，分解后，數(shù)據(jù)冗余度明顯降低。當(dāng)新插入一個(gè)系時(shí)，只要在關(guān)系D中添加一條記錄。當(dāng)某個(gè)學(xué)生尚未選課，只要在關(guān)系S中添加一條學(xué)生記錄，而與選課關(guān)系無關(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ān)系模式是一個(gè)好的關(guān)系數(shù)據(jù)庫模式。從而得出結(jié)論，一個(gè)好的關(guān)系模式應(yīng)該具備以下四個(gè)條件：1.盡可能少的數(shù)據(jù)冗余。2.沒有插入異常。3.沒有刪除異常。4.沒有更新異常。

一、問題的提出劉根萍但要注意，一個(gè)好的關(guān)系模式并不是在任何情況下都是最優(yōu)的，比如查詢某個(gè)學(xué)生選修課程名及所在系的系主任時(shí)，要通過連接，而連接所需要的系統(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)簡單的關(guān)系，從而把不好的關(guān)系數(shù)據(jù)庫模式轉(zhuǎn)變?yōu)楹玫年P(guān)系數(shù)據(jù)庫模式，這就是關(guān)系的規(guī)范化。我們要設(shè)計(jì)的關(guān)系模式中的各屬性是相互依賴、相互制約的，這樣才構(gòu)成了一個(gè)結(jié)構(gòu)嚴(yán)謹(jǐn)?shù)恼w。因此在設(shè)計(jì)關(guān)模式時(shí)，必須從語義上分析這些依賴關(guān)系。數(shù)據(jù)庫模式的好壞和關(guān)系中各屬性間的依賴關(guān)系有關(guān)。一、問題的提出劉根萍二、函數(shù)依賴函數(shù)依賴是指關(guān)系中各屬性或?qū)傩越M之間的相互依賴關(guān)系。它是關(guān)系規(guī)范化的理論基礎(chǔ)。函數(shù)依賴的定義給定一個(gè)關(guān)系模式R，X和Y都是R的屬性或?qū)傩越M，對于X的每個(gè)確定值在任何時(shí)刻Y只有一個(gè)確定的值與之對應(yīng)，則稱“X函數(shù)決定Y”，或稱為“Y函數(shù)依賴于X”，X稱為決定因素，記為：X→Y。函數(shù)依賴的類型完全函數(shù)依賴1.概念、類型部分函數(shù)依賴傳遞函數(shù)依賴定義如果屬性Y函數(shù)依賴于復(fù)合屬性X，且不與X的任何子集函數(shù)相依賴，則稱“Y完全函數(shù)依賴于X”。示例有如下的關(guān)系模式:生產(chǎn)(工廠編號,產(chǎn)品編號,計(jì)劃數(shù)量)“工廠編號+產(chǎn)品編號”→“計(jì)劃數(shù)量”，是完全函數(shù)依賴。二、函數(shù)依賴2.完全函數(shù)依賴定義對于一個(gè)函數(shù)依賴：X→Y，如果存在Z∈X（Z屬于X），且有Z→Y成立，則稱“Y部分函數(shù)依賴于X”。示例有如下的關(guān)系模式

SCD(學(xué)號，姓名，課程號，成績，系名，系主任)主碼為復(fù)合碼：學(xué)號+課程號“姓名、系名、系主任”與主碼是部分函數(shù)依賴二、函數(shù)依賴3.部分函數(shù)依賴（局部依賴）定義在關(guān)系模式中，如果X→Y(不能Y→X)，Y→Z，若存在X→Z，則稱“Z傳遞依賴于X”。示例有如下的關(guān)系模式

SD（學(xué)號，姓名，系名，系主任）“學(xué)號→系名，系名→系主任”，則系主任傳遞依賴于學(xué)號。二、函數(shù)依賴4.傳遞函數(shù)依賴三、關(guān)系的規(guī)范化在關(guān)系數(shù)據(jù)庫的設(shè)計(jì)過程中，對于同一個(gè)問題，選用不同的關(guān)系模式，其性能的優(yōu)劣是大不相同的，為了區(qū)分關(guān)系模式的優(yōu)劣，人們常常把關(guān)系模式分為各種不同等級的范式。類型第一范式(1NF)第二范式(2NF)第三范式(3NF)……關(guān)系規(guī)范化的概念將低級范式轉(zhuǎn)換為若干個(gè)高級范式過程。低高1.概念和范式的類型三、關(guān)系的規(guī)范化定義如果關(guān)系模式R的所有屬性的值域中第一個(gè)值都是不可再分解值，則稱R是屬于1NF模式。第一范式是對關(guān)系的最低要求。示例:判斷哪一張表是1NF？2.第一范式（1NF）三、關(guān)系的規(guī)范化第一范式可能存在的問題：數(shù)據(jù)冗余：如各位老師的信息重復(fù)。插入異常：如果要插入劉老師的個(gè)人信息，但劉老師未開課，會(huì)造成缺關(guān)鍵字“學(xué)號”、“課程號”。刪除異常：當(dāng)要?jiǎng)h除課程號=“C3”

的元組，會(huì)丟失李老師的信息。修改量大。837757857890成績2-1434王C1041-0145周C2011-0145周C2033-1527李C3032-1434王C1022-1434王C101辦公室教師年齡教師課程號學(xué)號2.第一范式（1NF）三、關(guān)系的規(guī)范化定義如果關(guān)系模式R為第一范式，且R中每個(gè)非主屬性完全函數(shù)依賴于R的主碼(復(fù)合碼)，則稱R為2NF模式。示例下列關(guān)系模式是1NF，是否是2NF？為什么？

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

SD（學(xué)號，姓名，系名，系主任）解決方法：消除傳遞函數(shù)依賴。不是。因?yàn)椋簩W(xué)號→系名，系名→系主任，存在傳遞依賴關(guān)系。4.第三范式（3NF）三、關(guān)系的規(guī)范化分解為3NF的方法：把直接對主碼函數(shù)依賴的非主屬性與決定它們的主碼放在一個(gè)關(guān)系模式中。把造成傳遞函數(shù)依賴的決定因素連同被它們決定的屬性放在一個(gè)關(guān)系模式中。檢查分解后的新模式，如果不是3NF，則繼續(xù)按照前面的方法進(jìn)行分解，直到達(dá)到要求。關(guān)系模式SD的分解結(jié)果如下：SD（學(xué)號，姓名，系名）SD（系名，系主任）4.第三范式（3NF）三、關(guān)系的規(guī)范化5.各范式間的關(guān)系三、關(guān)系的規(guī)范化四、綜合應(yīng)用實(shí)例：假設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫中有1個(gè)關(guān)系模式R(商店編號,商品編號,數(shù)量,部門編號,負(fù)責(zé)人)。如果規(guī)定：

(1)每個(gè)商店的每種商品只在一個(gè)部門銷售。

(2)每個(gè)商店的每個(gè)部門只有一個(gè)負(fù)責(zé)人。

(3)每個(gè)商店的每種商品只有一個(gè)庫存數(shù)量。寫出關(guān)系模式R的基本函數(shù)依賴集找出關(guān)系模式R的候選碼。關(guān)系模式R最高已經(jīng)達(dá)到第幾范式？為什么？如果R不屬于3NF，請將R分解成3NF。四、綜合應(yīng)用分析與解答：基本的函數(shù)依賴有：(商店編號+商品編號)→部門編號(商店編號+部門編號)→負(fù)責(zé)人(商店編號+商品編號)→數(shù)量由題意,上面的函數(shù)依賴可知“商店編號”是主屬性，商品編號也是主屬性，由這兩個(gè)屬性，可以確定關(guān)系元組的所有屬性，所以R的候選碼是：商店編號+商品編號。四、綜合應(yīng)用分析與解答：R(商店編號,商品編號,數(shù)量,部門編號,負(fù)責(zé)人)最高已達(dá)到2NF。因?yàn)樨?fù)責(zé)人是碼的傳遞依賴。要達(dá)到3NF，必須消除非主屬性對碼的傳遞依賴。對R分解后的兩個(gè)模式如下：R1(商店編號，商品編號，數(shù)量，部門編號)R2(部門編號，負(fù)責(zé)人)本課小結(jié)關(guān)系