數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（SQL Server ）2.2

數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（SQLServer）教學(xué)單元2.2第3章關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)(關(guān)系規(guī)范化)案例2-3圖書(shū)管理數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)學(xué)習(xí)導(dǎo)航2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)知識(shí)框架3單元2.2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)能力目標(biāo)能夠?qū)?shù)據(jù)庫(kù)概念設(shè)計(jì)得到的概念模型轉(zhuǎn)換為關(guān)系模型能夠?qū)﹃P(guān)系模型進(jìn)行實(shí)體完整性、域完整性、參照完整性和用戶定義完整性的約束設(shè)計(jì)能夠?qū)﹃P(guān)系模型進(jìn)行規(guī)范化和優(yōu)化培養(yǎng)用英文單詞或英文縮寫(xiě)描述和識(shí)別屬性的習(xí)慣4單元2.2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)知識(shí)目標(biāo)關(guān)系規(guī)范化理論基礎(chǔ)關(guān)系模型規(guī)范化方法數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)有關(guān)英文術(shù)語(yǔ)素質(zhì)目標(biāo)培養(yǎng)團(tuán)隊(duì)精神和自主學(xué)習(xí)的能力培養(yǎng)深入探究的學(xué)習(xí)態(tài)度5工作任務(wù)單元2.2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)6案例2-3圖書(shū)管理數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)概念模型關(guān)系模型將圖書(shū)管理數(shù)據(jù)庫(kù)概念設(shè)計(jì)得到的IDEF1X概念模型（案例2-2-2）轉(zhuǎn)換為關(guān)系模型根據(jù)需求分析的要求進(jìn)行數(shù)據(jù)庫(kù)的完整性約束設(shè)計(jì)和規(guī)范化處理IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換一關(guān)系規(guī)范化二單元2.2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)7一、IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換8信息世界機(jī)器世界（概念模型：IDEF1X）（邏輯模型：關(guān)系模型）

實(shí)體（E）轉(zhuǎn)換為關(guān)系獨(dú)立實(shí)體：讀者類型、讀者、出版社、圖書(shū)從屬實(shí)體：罰款、圖書(shū)修復(fù)直接轉(zhuǎn)換為關(guān)系，實(shí)體的屬性就是關(guān)系的屬性，實(shí)體的主鍵就是關(guān)系的主鍵一、IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換9聯(lián)系（R）轉(zhuǎn)換為關(guān)系（1：n）確定聯(lián)系—標(biāo)識(shí)聯(lián)系：讀者與罰款、圖書(shū)與圖書(shū)修復(fù)（遷移為主屬性）確定聯(lián)系—非標(biāo)識(shí)聯(lián)系（強(qiáng)制）：讀者類型與讀者（遷移為非主屬性）確定聯(lián)系—非標(biāo)識(shí)聯(lián)系（非強(qiáng)制）：出版社與圖書(shū)（設(shè)置為允許空）Visio建立IDEF1X概念模型已經(jīng)自動(dòng)將父實(shí)體的主鍵遷移到子實(shí)體中作為主屬性外鍵（FK）或者非主屬性外鍵（FK）或者設(shè)置為允許空。一、IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換10聯(lián)系（R）轉(zhuǎn)換為關(guān)系（m:n）不確定聯(lián)系：讀者與圖書(shū)Visio建立IDEF1X概念模型時(shí)建立了一個(gè)關(guān)聯(lián)實(shí)體“借閱”，并在建立父實(shí)體“讀者”和關(guān)聯(lián)實(shí)體“借閱”，父實(shí)體“圖書(shū)”和關(guān)聯(lián)實(shí)體“借閱”之間的標(biāo)識(shí)聯(lián)系時(shí)，分別將父實(shí)體的主鍵遷移到關(guān)聯(lián)實(shí)體中作為組合主鍵（PK），本身成為其外鍵（FK）。一、IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換11綜合以上，根據(jù)標(biāo)識(shí)要求，將中文實(shí)體和屬性名稱轉(zhuǎn)換為英文標(biāo)識(shí)的標(biāo)準(zhǔn)命名標(biāo)識(shí)符。圖書(shū)管理數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)得到的關(guān)系模型的7個(gè)關(guān)系模式如下。（1）讀者類型：ReaderType（TypeID,Typename,LimitNum,LimitDays,DelayFine,LostFine）PK：TypeID（2）讀

者：Reader（RID,Rname,TypeID,Lendnum,Address,TEL,EMAIL）PK：RIDFK：TypeID（3）罰款：Fine（RID,FineID,FineReason,Fines,FineDate）

PK：RID+FineIDFK：RID（4）出版社：PublishingHouse（PHID,Publisher,Address,TEL,EMAIL,Contacts）

PK：PHID（5）圖

書(shū)：Book（BID,Bname,PHID,Author,PubDate,Price,LentOut）PK：BIDFK：PHID（6）圖書(shū)修復(fù)：BookRepaire（BID,RepaireID,DamagedCondition,DamageCauses,RepairContent,DateOfRepairing,CostOfRepairing）PK：RID+RepaireIDFK：BID（7）借閱：Borrow（RID,BID,LendDate,ReturnDate）PK：RID+BID+LendDateFK：RID,BID一、IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換12IDEF1X概念模型到關(guān)系模型的轉(zhuǎn)換一關(guān)系規(guī)范化二單元2.2關(guān)系模型與數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)13二、關(guān)系規(guī)范化不規(guī)范：產(chǎn)生數(shù)據(jù)冗余，帶來(lái)很多問(wèn)題。規(guī)范：提高數(shù)據(jù)的結(jié)構(gòu)化、共享性、一致性和可操作性。范式：規(guī)范化的程度，級(jí)別。規(guī)范化：在關(guān)系數(shù)據(jù)庫(kù)中的每個(gè)關(guān)系都需要進(jìn)行規(guī)范化，使之達(dá)到一定的規(guī)范化程度。14二、關(guān)系規(guī)范化15

第一范式（1NF）1第二范式（2NF）2

第三范式（3NF）3

BC范式4（一）第一范式1NF（FirstNormalForm）定義：設(shè)R是一個(gè)關(guān)系，R的所有屬性不可再分，即原子屬性。記作：R∈1NF【例3-23】設(shè)一個(gè)通信錄，電話屬性可以再分，達(dá)不到1NF。問(wèn)題：電話屬性可以再分，不是二維表，不夠1NF學(xué)號(hào)姓名性別電話號(hào)碼手機(jī)號(hào)碼家庭號(hào)碼宿舍號(hào)碼2022216001趙成剛男13105242***612796361254632022216002李敬女13105543***623115962351592022216003郭洪亮男13105326***389035657903562022216004呂珊珊女13105242***7843567790045316（一）第一范式1NF（FirstNormalForm）解決方法1：在屬性上展開(kāi)學(xué)號(hào)姓名性別手機(jī)號(hào)碼家庭電話宿舍電話2022216001趙成剛男13105242***6127963612546320202216002李敬女13105543***623115962351592022216003郭洪亮男13105326***389035657903562022216004呂珊珊女13105242***78435677900453（一）第一范式1NF（FirstNormalForm）解決方法2：分解為二個(gè)關(guān)系學(xué)號(hào)手機(jī)家庭電話宿舍電話202221600113105242***61279636125463202221600213105543***62311596235159202221600313105326***38903565790356202221600113105242***78435677900453學(xué)號(hào)姓名性別2022216001趙成剛男2022216002李敬女2022216003郭洪亮男2022216001呂珊珊女（二）第二范式2NF（SecondNormalForm）定義設(shè)R是一個(gè)關(guān)系，其所有非主屬性完全函數(shù)依賴每個(gè)候選關(guān)鍵字記作R∈2NF或：取消部分函數(shù)依賴。【例3-24】有一個(gè)教師授課的關(guān)系模式如下，試對(duì)其進(jìn)行規(guī)范化。教師授課（職工號(hào),姓名,性別,職稱,住址,課程號(hào),課程名,學(xué)分,評(píng)價(jià)）主鍵（候選鍵）：職工號(hào)+課程號(hào)。19職工號(hào)姓名性別職稱住

址課程號(hào)

課

程

名學(xué)分評(píng)價(jià)1011張文娟女教授靜海花園5-220010微機(jī)組裝與維護(hù)2.0優(yōu)1011張文娟女教授靜?；▓@5-220013面向過(guò)程程序設(shè)計(jì)10.0良1011張文娟女教授靜海花園5-220014數(shù)據(jù)庫(kù)開(kāi)發(fā)與維護(hù)6.5優(yōu)1012劉紅霞女講師福萊花苑3-120014數(shù)據(jù)庫(kù)開(kāi)發(fā)與維護(hù)6.5良1013李曉峰男講師先鋒小區(qū)5-220014數(shù)據(jù)庫(kù)開(kāi)發(fā)與維護(hù)6.5優(yōu)

[H1]此列原數(shù)據(jù)改為現(xiàn)在的數(shù)據(jù)（二）第二范式2NF（SecondNormalForm）存在問(wèn)題數(shù)據(jù)冗余：不同課程同一任教的教師名、職稱、住址等存在大量重復(fù)（表左邊的灰色區(qū)域），不同教師同一課程的課程名與學(xué)分等大量重復(fù)（表右邊的灰色區(qū)域）。更新異常：冗余帶來(lái)更新的不一致。如教師張文娟更新職稱或地址，課程數(shù)據(jù)庫(kù)開(kāi)發(fā)與維護(hù)更新名稱或?qū)W分，多次輸入可能因表達(dá)方式不同、遺漏或者失誤帶來(lái)不一致。插入異常：沒(méi)有上課的教師的主屬性課程號(hào)無(wú)值將不允許插入。刪除異常：刪除某一課程，致使刪除有關(guān)教師的信息。20（二）第二范式2NF（SecondNormalForm）問(wèn)題原因：關(guān)系屬性之間存在部分函數(shù)依賴，達(dá)不到2NF。所有非主屬性姓名、性別、職稱、課程名、學(xué)分和教學(xué)評(píng)價(jià)函數(shù)依賴主鍵（職工號(hào)+課程號(hào)），但是存在主鍵的一部分“課程號(hào)”就可以決定課程名和學(xué)分的情況，即非主屬性課程名和學(xué)分部分函數(shù)依賴主鍵（候選鍵），依賴關(guān)系表現(xiàn)如下。

（職工號(hào)+課程號(hào)）→課程名，學(xué)分

（課程號(hào)）→課程名，學(xué)分21（二）第二范式2NF（SecondNormalForm）解決辦法：對(duì)關(guān)系進(jìn)行拆分，原則是概念單一，數(shù)據(jù)完整（無(wú)損）

教師授課(職工號(hào),姓名,性別,職稱,住址,課程號(hào),課程名,學(xué)分,評(píng)價(jià))

上述達(dá)不到2NF的關(guān)系分解如下：聯(lián)系類型

關(guān)系分解多

教師(職工號(hào),姓名,性別,職稱,住址)對(duì)

授課(職工號(hào),課程號(hào),評(píng)價(jià))多

課程(課程號(hào),課程名,學(xué)分)22（二）第二范式2NF（SecondNormalForm）教師(職工號(hào),姓名,性別,職稱,住址)授課(職工號(hào),課程號(hào),評(píng)價(jià))課程(課程號(hào),課程名,學(xué)分)23教師情況關(guān)系教師授課關(guān)系課程情況關(guān)系職工號(hào)姓名性別職稱住址職工號(hào)課程號(hào)評(píng)價(jià)課程號(hào)課程名學(xué)分1011張文娟女教授靜?；▓@5-2101120010優(yōu)20010微機(jī)組裝與維護(hù)2.01012劉紅霞女講師福萊花苑3-1101120013良20013面向過(guò)程程序設(shè)計(jì)10.01013李曉峰男講師先鋒小區(qū)5-2101120014優(yōu)20014數(shù)據(jù)庫(kù)開(kāi)發(fā)與維護(hù)6.5101220014良101320014優(yōu)（三）第三范式3NF（ThirdNormalForm）定義設(shè)R是一個(gè)關(guān)系，其所有非主屬性都不傳遞函數(shù)依賴每個(gè)候選鍵?；颍喝∠麄鬟f函數(shù)依賴記作R∈3NF假設(shè)：圖書(shū)管理系統(tǒng)中讀者的關(guān)系模式。讀者(讀者號(hào),姓名,類型編號(hào),類型名稱,限借數(shù)量,限借天數(shù),借閱數(shù)量)

主鍵（候選鍵）：讀者號(hào)24讀

者編號(hào)姓名類型編號(hào)類型名稱限借數(shù)量限借天數(shù)借閱數(shù)量2000186010張子健1教師69002022216117孟霞3學(xué)生33002023216008楊淑華3學(xué)生33002023216009程鵬3學(xué)生3302（三）第三范式3NF（ThirdNormalForm）存在問(wèn)題數(shù)據(jù)冗余：同一讀者類型的多位（試想有上萬(wàn)名學(xué)生）讀者對(duì)應(yīng)的類型名稱、限借數(shù)量和限借天數(shù)等數(shù)據(jù)存在大量重復(fù)（表灰色區(qū)域）。更新異常：冗余帶來(lái)更新的不一致。如果要修改限借數(shù)量、限借天數(shù)可能要改上萬(wàn)處，很可能造成失誤。插入異常：在某種讀者類型沒(méi)有對(duì)應(yīng)讀者的情況下，不允許插入。刪除異常：如果某類型的讀者只有一位，則該讀者被刪除將致使刪除對(duì)應(yīng)的讀者類型。25（三）第三范式3NF（ThirdNormalForm）問(wèn)題原因：關(guān)系屬性之間存在傳遞函數(shù)依賴，達(dá)不到3NF。主鍵“讀者號(hào)”決定屬性“類型編號(hào)”，而“類型編號(hào)”決定非主屬性“類型名稱”、“限借數(shù)量”和“限借天數(shù)”，即這些非主屬性通過(guò)“類型編號(hào)”傳遞函數(shù)依賴主鍵（候選鍵）“讀者編號(hào)”，依賴關(guān)系表現(xiàn)如下。

讀者號(hào)→類型編號(hào)

類型編號(hào)→(類型名稱、限借數(shù)量、限借天數(shù))

類型編號(hào)

讀者號(hào)26（三）第三范式3NF（ThirdNormalForm）解決辦法：對(duì)關(guān)系進(jìn)行拆分，原則是概念單一，數(shù)據(jù)完整（無(wú)損）

讀者（讀者號(hào),姓名,類型編號(hào),類型名稱,限借數(shù)量,限借天數(shù),借閱數(shù)量）上述達(dá)不到2NF的關(guān)系分解如下：聯(lián)系類型

關(guān)系分解多

讀者(讀者號(hào),姓名,類型編號(hào),借閱數(shù)量)PK：讀者號(hào)FK：類型編號(hào)對(duì)

所屬類型(讀者號(hào),類型編號(hào))

此聯(lián)系可以通過(guò)在多端加外鍵省略一

讀者類型(類型編號(hào),類型名稱,限借數(shù)量,限借天數(shù))PK：類型編號(hào)27（三）第三范式3NF（ThirdNormalForm）讀者(讀者號(hào),姓名,類型編號(hào),借閱數(shù)量)PK：讀者號(hào)FK：類型編號(hào)

讀者類型(類型編號(hào),類型名稱,限借數(shù)量,限借天數(shù))PK：類型編號(hào)28

讀者關(guān)系讀者類型關(guān)系讀者編號(hào)姓名類型編號(hào)借閱數(shù)量類型編號(hào)類型名稱限借數(shù)量限借天數(shù)2000186010張子健101教師6902022216117孟霞302職員4602023216008楊淑華303學(xué)生3302023216009程鵬32（四）BC范式BCNF（Boyce－CoddNormalForm）29定義設(shè)R是一個(gè)關(guān)系，其所有所有屬性都不傳遞依賴每個(gè)候選關(guān)鍵字，記作：R∈BCNF。說(shuō)明由于關(guān)系規(guī)范化理論較深，此處不再贅述，讀者可以根據(jù)實(shí)際設(shè)計(jì)加以體會(huì)。二、關(guān)系規(guī)范化30圖書(shū)管理數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)——關(guān)系規(guī)范化分析圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)每個(gè)關(guān)系的屬性之間的依賴關(guān)系，均達(dá)到了3NF。其中，分解的實(shí)體“讀者”和“讀者類型”、“圖書(shū)”和“出版社”消除了傳遞函數(shù)依賴，使得關(guān)系達(dá)到了3NF。（1）讀者類型：ReaderType（TypeID,Typename,LimitNum,LimitDays,DelayFine,LostFine）PK：TypeID（2）讀

者：Reader（RID,Rname,TypeID,Lendnum,Address,TEL,EMAIL）PK：RIDFK：TypeID（3）罰