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

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

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

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

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

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

SNOCNOSCORES1趙亦17計算機

S1C190S2錢爾18信息

S1C285S3孫珊20信息

S2C557S4李思21自動化

S2C680

S2C7

D

S2C570DEPTMN

S3C10計算機劉偉

S3C270信息王平

S3C485自動化劉偉

S4C193分解后的關系模式

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

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

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

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

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

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

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

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

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

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