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

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

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

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

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

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

SNOCNOSCORES1趙亦17計算機

S1C190S2錢爾18信息

S1C285S3孫珊20信息

S2C557S4李思21自動化

S2C680

S2C7

D

S2C570DEPTMN

S3C10計算機劉偉

S3C270信息王平

S3C485自動化劉偉

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

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

一、問題的提出劉根萍但要注意，一個好的關(guān)系模式并不是在任何情況下都是最優(yōu)的，比如查詢某個學生選修課程名及所在系的系主任時，要通過連接，而連接所需要的系統(tǒng)開銷非常大，因此要以實際設(shè)計的目標出發(fā)進行設(shè)計如何按照一定的規(guī)范設(shè)計關(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è)計的關(guān)系模式中的各屬性是相互依賴、相互制約的，這樣才構(gòu)成了一個結(jié)構(gòu)嚴謹?shù)恼w。因此在設(shè)計關(guān)模式時，必須從語義上分析這些依賴關(guān)系。數(shù)據(jù)庫模式的好壞和關(guān)系中各屬性間的依賴關(guān)系有關(guān)。一、問題的提出劉根萍二、函數(shù)依賴函數(shù)依賴是指關(guān)系中各屬性或?qū)傩越M之間的相互依賴關(guān)系。它是關(guān)系規(guī)范化的理論基礎(chǔ)。函數(shù)依賴的定義給定一個關(guān)系模式R，X和Y都是R的屬性或?qū)傩越M，對于X的每個確定值在任何時刻Y只有一個確定的值與之對應(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)品編號,計劃數(shù)量)“工廠編號+產(chǎn)品編號”→“計劃數(shù)量”，是完全函數(shù)依賴。二、函數(shù)依賴2.完全函數(shù)依賴定義對于一個函數(shù)依賴：X→Y，如果存在Z∈X（Z屬于X），且有Z→Y成立，則稱“Y部分函數(shù)依賴于X”。示例有如下的關(guān)系模式

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

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

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

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

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

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

(2)每個商店的每個部門只有一個負責人。

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