第3講關(guān)系數(shù)據(jù)庫原理(關(guān)系的完整性和規(guī)范化)

SQL

Server2005第1章 關(guān)系數(shù)據(jù)庫原理——關(guān)系的完整性和關(guān)系的規(guī)范化主講人：段利文第3講第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化2復(fù)習什么是關(guān)鍵字/碼/鍵？什么是主關(guān)鍵字/主碼/主鍵？ER圖轉(zhuǎn)化為關(guān)系模式的原則是什么？答：是能用來惟一標識元組的屬性或?qū)傩越M合。答：被指定為關(guān)鍵字的候選關(guān)鍵字，稱為~。答：（1）每個實體轉(zhuǎn)換為一個關(guān)系。每個聯(lián)系也轉(zhuǎn)換成一個關(guān)系。有相同碼的關(guān)系可以合并。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化3復(fù)習關(guān)系應(yīng)具備什么性質(zhì)？答：（1）關(guān)系中每一個屬性值都是不可分解的。關(guān)系中不允許出現(xiàn)相同的行。關(guān)系中不允許出現(xiàn)相同的列。關(guān)系中沒有行序、沒有列序。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化4第1章 關(guān)系數(shù)據(jù)庫原理——關(guān)系的完整性規(guī)則、規(guī)范化教學內(nèi)容關(guān)系的完整性規(guī)則實體完整性、參照完整性、域完整性函數(shù)依賴完全函數(shù)依賴、部分函數(shù)依賴、傳遞函數(shù)依賴關(guān)系的規(guī)范化第一范式、第二范式、第三范式第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化5第1章 關(guān)系數(shù)據(jù)庫原理——關(guān)系的完整性規(guī)則、規(guī)范化學習目標認知目標：理解關(guān)系的完整性規(guī)則了解函數(shù)依賴、關(guān)系規(guī)范化的含義理解三種范式的定義能力目標:能判別關(guān)系的范式類別 (重點、難點)能進行范式的分解（重點、難點）第3講一、關(guān)系的完整性規(guī)則定義是對關(guān)系的某種約束條件。目的用于保證關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的正確性和可靠性。類型實體完整性規(guī)則參照完整性規(guī)則（引用完整性規(guī)則

）域完整性規(guī)則（用戶自定義完整性規(guī)則）是關(guān)系模型必須滿足的完整性約束條件。第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化6第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化7一、關(guān)系的完整性規(guī)則1.實體完整性規(guī)則規(guī)則要求：在任何關(guān)系的任何一個元組中，主鍵的值不能為空

值、也不能取重復(fù)的值。目的：用于保證數(shù)據(jù)庫表中的每一個元組都是惟

一的。請思考：下面的關(guān)系是否違反實體完整性規(guī)則？學號姓名性別年齡系編號03001馬力剛男210103102王萍華女200203223王平男2103張華男220403001李萍女1905第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化8一、關(guān)系的完整性規(guī)則2.域完整性規(guī)則(用戶定義完整性規(guī)則)規(guī)則要求由用戶根據(jù)實際情況，定義表中屬性的取值范圍例如：性別只能是男和女、年齡不能為負值、成績在0—100之間等。目的用于保證給定字段中數(shù)據(jù)的有效性,即保證數(shù)據(jù)的取值在有效的范圍內(nèi)。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化9一、關(guān)系的完整性規(guī)則3.參照完整性規(guī)則（引用完整性規(guī)則）規(guī)則要求：“不引用不存在的實體”。即：不允許在一個關(guān)系中引用另一個關(guān)系中不存在的元組。目的用于確保相關(guān)聯(lián)的表間的數(shù)據(jù)保持一致。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化10一、關(guān)系的完整性規(guī)則3.參照完整性規(guī)則（引用完整性規(guī)則）請思考：下面兩個關(guān)系是否違反參照完整性規(guī)則？

系表（主表） 學生表（從表）說明從表的“系編號（外鍵）”的取值只能為兩種情況：若取非空值，則它必須是主表中存在的值。取空值(null)。表明尚未給學生分配專業(yè)。Null不等于0或空字符串。學號姓名性別年齡系編號03001馬力剛男210103102王萍華女2003223王平男2105系編號系名系主任辦公室電話01計算機龔小勇205600302通信譚中華207602503電子袁

勇2106018第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化11二、函數(shù)依賴1.概念、類型函數(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ù)依賴部分函數(shù)依賴傳遞函數(shù)依賴第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化12二、函數(shù)依賴2.完全函數(shù)依賴定義如果屬性Y函數(shù)依賴于復(fù)合屬性X，且不與X的任何子集函數(shù)相依賴，則稱“Y完全函數(shù)依賴于

X”。示例有如下的關(guān)系模式:生產(chǎn)(工廠編號,產(chǎn)品編號,計劃數(shù)量)“工廠編號+產(chǎn)品編號”

→“計劃數(shù)量”，是完全函數(shù)依賴。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化13二、函數(shù)依賴3.部分函數(shù)依賴（局部依賴）定義對于一個函數(shù)依賴：X→Y，如果存在Z∈X（Z

屬于X），且有Z→Y成立，則稱“Y部分函數(shù)依賴于X”。示例有如下的關(guān)系模式SCD(學號，姓名，課程號，成績，系名，系主任)主碼為復(fù)合碼：學號+課程號“姓名、系名、系主任”與主碼是部分函數(shù)依賴第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化14二、函數(shù)依賴4.傳遞函數(shù)依賴定義在關(guān)系模式中，如果X→Y(不能Y→X)，Y→Z，

若存在X→Z ，則稱“Z傳遞依賴于X”。示例有如下的關(guān)系模式SD（學號，姓名，系名，系主任）“學號→系名，系名→系主任”，則系主任傳遞依賴

于學號。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化15三、關(guān)系的規(guī)范化引在關(guān)系數(shù)據(jù)庫的設(shè)計過程中，對于同一個問題，選用不同的關(guān)系模式，其性能的優(yōu)劣是大不相

同的，為了區(qū)分關(guān)系模式的優(yōu)劣，人們常常把

關(guān)系模式分為各種不同等級的范式。第3講三、關(guān)系的規(guī)范化1.

概念和范式的類型類型第一范式(1NF)第二范式(2NF)第三范式(3NF)……關(guān)系規(guī)范化的概念將低級范式轉(zhuǎn)換為若干個高級范式過程。低第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化16高第3講三、關(guān)系的規(guī)范化2.第一范式(1NF)定義如果關(guān)系模式R的所有屬性的值域中第一個值都是不可

再分解值，則稱R是屬于1NF模式。第一范式是對關(guān)系的最低要求。示例:判斷哪一張表是1NF？第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化17第3講三、關(guān)系的規(guī)范化2.第一范式(1NF)第一范式可能存在的問題：數(shù)據(jù)冗余：如各位老師的信息重復(fù)。插入異常：如果要插入劉老師的個人信息，但劉老師未開

課，會造成缺關(guān)鍵字“學號”、“課程號”。刪除異常：當要刪除 課程號=“C3”

的元組，會丟失李老師的信息。修改量大。第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化18學號課程號成績教師教師年齡辦公室01C190王342-1401C278周451-0102C185王342-1403C357李273-1503C277周451-0104C183王342-14第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化19三、關(guān)系的規(guī)范化3.第二范式(2NF)定義如果關(guān)系模式R為第一范式，且R中每個非主屬性完全

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

性都不傳遞函數(shù)依賴于R的某個候選碼，則稱R為3NF模式。示例下列關(guān)系模式是2NF，是否為3NF？為什么？

SD（學號，姓名，系名，系主任）解決方法：消除傳遞函數(shù)依賴。不是。因為：學號→系名，系名→系主任，存在傳遞依賴關(guān)系。第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化22三、關(guān)系的規(guī)范化4.第三范式(3NF)分解為3NF的方法：把直接對主碼函數(shù)依賴的非主屬性與決定它們的主碼放在一個關(guān)系模式中。把造成傳遞函數(shù)依賴的決定因素連同被它們決定的屬性放在一個關(guān)系模式中。檢查分解后的新模式，如果不是3NF，則繼續(xù)按照前面

的方法進行分解，直到達到要求。關(guān)系模式SD的分解結(jié)果如下：SD（學號，姓名，系名）SD（系名，系主任）第3講三、關(guān)系的規(guī)范化5.各范式間的關(guān)系各范式之間的關(guān)系第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化23第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化24四、綜合應(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。第3講四、綜合應(yīng)用分析與解答：1.

基本的函數(shù)依賴有：(商店編號+商品編號)→部門編號(商店編號+部門編號)→負責人(商店編號+商品編號)→數(shù)量2.

由題意,上面的函數(shù)依賴可知“商店編號

”是主屬性，商品編號也是主屬性，由這兩個屬性，可以確定關(guān)系元組的所有屬性，所以R的候選碼是：商店編號+商品編號。第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化25第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化26四、綜合應(yīng)用分析與解答：R(商店編號,商品編號,數(shù)量,部門編號,負

責人) 最高已達到2NF。因為負責人是碼的傳遞依賴。要達到3NF，必須消除非主屬性對碼的傳

遞依賴。對R分解后的兩個模式如下：R1(商店編號，商品編號，數(shù)量，部門編號)R2(部門編號，負責人)第3講第1章關(guān)系數(shù)據(jù)庫原理—關(guān)系的完整性規(guī)則、關(guān)系的規(guī)范化27本課小結(jié)關(guān)系的完整性規(guī)則實體完整性/參照完整性/域完整性范式的類型1NF、2NF、3NF各范式間的關(guān)系:1NF中的每個屬性值是不可再分的，它是關(guān)系模式的最低要求。消除1NF中的部分函數(shù)依賴