數(shù)據(jù)庫技術(shù)基礎(chǔ)：第三章 數(shù)據(jù)庫設(shè)計(jì)

1、數(shù)據(jù)庫技術(shù)基礎(chǔ)SQL Server & VB.NET 數(shù)據(jù)庫技術(shù)與應(yīng)用本書目錄1第三章 數(shù)據(jù)庫設(shè)計(jì)E/R圖到關(guān)系模式的轉(zhuǎn)換關(guān)系模式的規(guī)范化需求分析概念模式設(shè)計(jì)邏輯模式設(shè)計(jì)數(shù)據(jù)庫實(shí)施E/R圖RDBMS模式存儲(chǔ)模式數(shù)據(jù)庫運(yùn)行和維護(hù)概念模式設(shè)計(jì)轉(zhuǎn)換分析抽象信息世界概念模式機(jī)器世界 RDBMS所支持的關(guān)系模式型3.2.1 數(shù)據(jù)建模的基本過程現(xiàn)實(shí)世界需求分析報(bào)告E-R圖 E-R圖是由P.P.Chen于1976年提出的，其論文發(fā)表于ACM Transaction on Database System上。當(dāng)時(shí)關(guān)系模型剛提出不久（1970年，E.F.Codd），并且關(guān)系數(shù)據(jù)庫系統(tǒng)剛剛被工業(yè)界認(rèn)可。由于時(shí)代的原

2、因，當(dāng)時(shí)的應(yīng)用軟件應(yīng)當(dāng)是相對(duì)簡(jiǎn)單和以數(shù)據(jù)庫查詢?yōu)橹鞯?。這些應(yīng)用可以對(duì)應(yīng)于今天的管理信息系統(tǒng)（MIS）。 當(dāng)時(shí)P.P.Chen認(rèn)識(shí)到MIS的質(zhì)量，取決于數(shù)據(jù)庫的組織，而良好的數(shù)據(jù)庫組織取決于數(shù)據(jù)之間的依賴關(guān)系。E-R圖可以清楚地說明這種依賴關(guān)系。 最大的貢獻(xiàn)是使得數(shù)據(jù)庫的設(shè)計(jì)“可視化”，可視化帶來的效果是不言而喻的，因此至今E-R圖仍被廣泛地使用。 實(shí)體聯(lián)系模型 實(shí)體聯(lián)系模型（Entity-Relationship Model，簡(jiǎn)稱E-R模型）是P.P.Chen于1976年在“實(shí)體聯(lián)系模型：將來的數(shù)據(jù)視圖”的論文中提出的。這個(gè)模型直接從現(xiàn)實(shí)世界中抽象出實(shí)體類型及實(shí)體集之間的聯(lián)系，然后用實(shí)體聯(lián)系圖

3、即E-R表示數(shù)據(jù)模型。其中的核心就是設(shè)計(jì)E-R圖（Entity-Relationship Diagram）。實(shí)體聯(lián)系模型的基本概念基本概念：實(shí)體(Entity)：客觀存在，可以相互區(qū)別的事物稱為實(shí)體。 實(shí)體集(Entity Set)：性質(zhì)相同的同類實(shí)體的集合，稱為實(shí)體集。屬性(Attribute)：實(shí)體有很多特性，每一個(gè)特性稱為屬性。每一個(gè)屬性有一個(gè)值域，其類型可以是整數(shù)型、實(shí)數(shù)型、字符串型等。實(shí)體標(biāo)識(shí)符(Identifier)：能惟一標(biāo)識(shí)實(shí)體的屬性或?qū)傩约?，稱為實(shí)體標(biāo)識(shí)符。有時(shí)也稱為關(guān)鍵碼(Key)，或簡(jiǎn)稱為鍵。 聯(lián)系(Relationship)是實(shí)體之間的相互關(guān)系。與一個(gè)聯(lián)系有關(guān)的實(shí)體集個(gè)

4、數(shù)，稱為聯(lián)系的元數(shù)。 數(shù)據(jù)模型: 能表示實(shí)體類型及實(shí)體間聯(lián)系的模型稱為“數(shù)據(jù)模型”。 E-R圖的基本組成部分 E/R圖有四個(gè)主要部分：（1）實(shí)體集，用矩形表示。（2）屬性，用橢圓形表示。（3）聯(lián)系，用菱形或三角形表示。（4）直線，表示聯(lián)系的類型及實(shí)體與實(shí)體之間的聯(lián)系；多端用無箭頭的直線，一端用有箭頭的直線。數(shù)據(jù)聯(lián)系聯(lián)系(Relationship)是實(shí)體之間的相互關(guān)系。二元聯(lián)系有以下三種類型：一對(duì)一聯(lián)系：如果實(shí)體集E1中每個(gè)實(shí)體至多和實(shí)體集E2中的一個(gè)實(shí)體有聯(lián)系，反之亦然，那么實(shí)體集E1和E2的聯(lián)系稱為“一對(duì)一聯(lián)系”，記為“1:1”。一對(duì)多聯(lián)系：如果實(shí)體集E1中每個(gè)實(shí)體可以與實(shí)體集E2中任意個(gè)（

5、零個(gè)或多個(gè)）實(shí)體間有聯(lián)系，而E2中每個(gè)實(shí)體至多和E1中一個(gè)實(shí)體有聯(lián)系，那么稱E1對(duì)E2的聯(lián)系是“一對(duì)多聯(lián)系”，記為“1:N”。 多對(duì)多聯(lián)系：如果實(shí)體集E1中每個(gè)實(shí)體可以與實(shí)體集E2中任意個(gè)（零個(gè)或多個(gè)）實(shí)體有聯(lián)系，反之亦然，那么稱E1和E2的聯(lián)系是“多對(duì)多聯(lián)系”，記為“M:N”。 數(shù)據(jù)聯(lián)系的描述實(shí)體集E1 實(shí)體集E2座位E1乘客E2實(shí)體集E1 實(shí)體集E2E1E2車間工人實(shí)體集E1 實(shí)體集E2E1E2學(xué)生課程三種聯(lián)系的E-R圖實(shí)體1實(shí)體2聯(lián)系實(shí)體1實(shí)體2聯(lián)系實(shí)體1實(shí)體2聯(lián)系 11聯(lián)系 1n聯(lián)系 mn聯(lián)系E-R圖實(shí)例 例：請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)庫的概念模型。其中包括實(shí)體學(xué)生(student)、課程(cou

6、rse)和教師(teacher)的信息及實(shí)體聯(lián)系的描述。 實(shí)體： 學(xué)生：學(xué)號(hào)(snum)、姓名(sname)、年齡(age)、地址(saddress)等； 課程：課程號(hào)(cnum)、課程名(cname)、課程學(xué)分(credit)等； 教師：工號(hào)(tnum)、姓名(tname)、地址(taddress)等。 聯(lián)系： 一個(gè)學(xué)生可以選修多門課，而每門課也能由多個(gè)學(xué)生選修；一位教師可以教多門課，每門課也可以由多個(gè)教師講授；用E-R圖來描述此數(shù)據(jù)庫。 學(xué)生-課程-教師E-R圖studentcourseteachertaketeachcreditscnamecnumsaddressagesnamesnum

7、taddresstnametnumscore思考題一個(gè)教師任課管理數(shù)據(jù)庫中提供以下信息： 教師：教師號(hào)、姓名、性別、職稱；課程：課程號(hào)，課程名；工作單位：?jiǎn)挝幻?，電話；上述?shí)體中存在如下聯(lián)系：（1）一個(gè)教師可講授多門課程，一門課程可為多個(gè)教師講授，教師講授的班級(jí)信息用班號(hào)表示；（2）一個(gè)單位可有多個(gè)教師，一個(gè)教師只能屬于一個(gè)單位。試畫出上述實(shí)體集及實(shí)體聯(lián)系的E-R圖。課程教師工作單位講授隸屬性別姓名工號(hào)課程名課程號(hào)電話單位名班號(hào)職稱邏輯模式設(shè)計(jì)需求分析概念模式設(shè)計(jì)邏輯模式設(shè)計(jì)數(shù)據(jù)庫實(shí)施E/R圖RDBMS模式存儲(chǔ)模式數(shù)據(jù)庫運(yùn)行和維護(hù)E/R圖到關(guān)系模式的轉(zhuǎn)換關(guān)系模式的規(guī)范化1實(shí)體到關(guān)系模式的轉(zhuǎn)換

8、規(guī)則：將實(shí)體的名稱作為關(guān)系的名稱，將實(shí)體的屬性作為關(guān)系的屬性，并用下劃線標(biāo)識(shí)出主屬性。學(xué)生 課程 選課 snum sname sex agecnum cnamecreditsgrade 學(xué)生(snum,sname,sex,age)，表示學(xué)生關(guān)系，其中snum為主鍵。課程(cnum,cname,credits)，表示課程關(guān)系，其中cnum為主鍵。2聯(lián)系到關(guān)系模式的轉(zhuǎn)換若實(shí)體間聯(lián)系是1:1，可以在兩個(gè)實(shí)體轉(zhuǎn)換成的兩個(gè)關(guān)系模式后，在其中任意一個(gè)關(guān)系模式的屬性集中加入另一個(gè)關(guān)系模式的主鍵和聯(lián)系自身的屬性。也就是說1:1聯(lián)系不單獨(dú)轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系。若實(shí)體間聯(lián)系是1:N，則在N端實(shí)體轉(zhuǎn)換成的關(guān)系模式中

9、加入1端實(shí)體的主鍵和聯(lián)系的屬性。同樣1:N聯(lián)系也不獨(dú)立構(gòu)成一個(gè)關(guān)系。若實(shí)體間聯(lián)系是M:N，則將聯(lián)系轉(zhuǎn)換成一個(gè)關(guān)系模式，其屬性為兩端實(shí)體的主鍵加上聯(lián)系自身的屬性，而聯(lián)系關(guān)系的主鍵為一復(fù)合鍵，通常包含兩端實(shí)體主鍵。 對(duì)于學(xué)生選課E/R圖的課程和學(xué)生之間的多對(duì)多的聯(lián)系，可轉(zhuǎn)換成關(guān)系模式： 選課(snum,cnum,grade) 主鍵為復(fù)合鍵 snum,cnum。學(xué)生 課程 選課 snum sname sex agecnum cnamecreditsgrade 3三個(gè)或三個(gè)以上實(shí)體間的一個(gè)聯(lián)系 規(guī)則：三個(gè)或三個(gè)以上實(shí)體間個(gè)聯(lián)系轉(zhuǎn)換成一個(gè)關(guān)系模式，其屬性為三端實(shí)體或多端實(shí)體的鍵加上聯(lián)系自身的屬性，而鍵為

10、三端實(shí)體或多端實(shí)體鍵的組合。 4E/R圖中“屬于”聯(lián)系到關(guān)系的轉(zhuǎn)換假若A類實(shí)體和B類實(shí)體之間是“屬于”關(guān)系，這里假定為B屬于A，為了表示出A和B之間的關(guān)系，用一種稱為“屬于”（“isa”）的特殊聯(lián)系將實(shí)體集A和B相連。 “isa“聯(lián)系用三角形表示，尖端指向A類實(shí)體集，底邊指向B類實(shí)體集，三角形中還要寫上”isa”的字樣。A類實(shí)體和B類實(shí)體也稱為超類和子類。子類與超類有兩個(gè)性質(zhì): 子類與超類之間具有繼承性特點(diǎn)，即子類實(shí)體繼承超類實(shí)體的所有屬性。但子類實(shí)體本身還可以包含比超類實(shí)體更多的屬性。 這種繼承性是通過子類實(shí)體和超類實(shí)體有相同的實(shí)體標(biāo)識(shí)符實(shí)現(xiàn)的。 E-R圖轉(zhuǎn)換實(shí)例學(xué)生研究生學(xué)號(hào)導(dǎo)師性別年齡系

11、名姓名isa轉(zhuǎn)換為關(guān)系模式：學(xué)生(學(xué)號(hào)，姓名，性別，年齡，系名)研究生（學(xué)號(hào)，導(dǎo)師）E-R圖設(shè)計(jì)實(shí)例 例3.6 根據(jù)需求分析報(bào)告描述，在某影展信息管理系統(tǒng)中存在下列實(shí)體和聯(lián)系，設(shè)計(jì)滿足需求的E-R圖。實(shí)體集： 電影：片名，出品年份，影片長度，影片類型；鍵碼為片名，出品年份。 明星：姓名，聯(lián)系地址；鍵碼為姓名。 影片公司：公司名稱，地址；鍵碼為公司名稱。 卡通片：設(shè)計(jì)平臺(tái)；是電影實(shí)體的子類，鍵碼為其超類的鍵碼片名，出品年份。E-R圖設(shè)計(jì)實(shí)例聯(lián)系描述： 一個(gè)影片公司有一個(gè)名稱和一個(gè)地址，由名稱唯一標(biāo)識(shí)；一位明星最多只能簽約一個(gè)影片公司，而一個(gè)影片公司可以擁有多位明星；一部影片由多位明星參演，一位明

12、星可以出演多部影片的多個(gè)角色；一部影片最多歸屬于一家影片公司，而一家公司可以出品多部影片?？ㄍㄆ请娪暗囊粋€(gè)子類。設(shè)計(jì)的E-R圖的步驟1.設(shè)計(jì)實(shí)體及其屬性和鍵碼。2.設(shè)計(jì)兩兩實(shí)體之間的聯(lián)系，比如電影和明星之間的扮演（Act）聯(lián)系，明星和影片公司之間的聘用聯(lián)系等。3.在所有實(shí)體之間的聯(lián)系設(shè)計(jì)完后，在將這些E-R圖合并為一個(gè)總的E-R圖。在這個(gè)過程中要去掉多個(gè)實(shí)體和屬性間的命名沖突和聯(lián)系沖突，生成一個(gè)完整的、滿足應(yīng)用需求的全局E-R圖。卡通片影片公司地址公司名稱圖3.2.16 影展信息數(shù)據(jù)庫的E/R圖電影明星扮演出品年份影片長度影片類型片名聯(lián)系地址姓名角色聘用出品isa設(shè)計(jì)平臺(tái)思考題MovieAc

13、torMurderCartoonIDNameWeaponTypeLengthYearTitleActVoiceisaisa1、Movie的主鍵是什么？2、各個(gè)實(shí)體與聯(lián)系如何轉(zhuǎn)換為關(guān)系模式？邏輯模式設(shè)計(jì)需求分析概念模式設(shè)計(jì)邏輯模式設(shè)計(jì)數(shù)據(jù)庫實(shí)施E/R圖RDBMS模式存儲(chǔ)模式數(shù)據(jù)庫運(yùn)行和維護(hù)E/R圖到關(guān)系模式的轉(zhuǎn)換關(guān)系模式的規(guī)范化關(guān)系模型設(shè)計(jì)理論關(guān)系模式規(guī)范化數(shù)據(jù)冗余。Sdept和Mname兩個(gè)屬性列的同一個(gè)值的多次存儲(chǔ)。由于數(shù)據(jù)的冗余，在對(duì)數(shù)據(jù)操作時(shí)會(huì)引起各種異常。修改異常。由于同一個(gè)信息存放多次，我們可能修改了一個(gè)元組中的信息，但另一個(gè)元組中的相同的信息卻沒有修改。比如：計(jì)算機(jī)系的系主任換了，

14、也許只在上述關(guān)系中的兩個(gè)元組中修改了，而沒在第三個(gè)元組中修改。 插入異常。如果一個(gè)新生注冊(cè)，尚未選修課程，那么要將學(xué)生的學(xué)號(hào)、姓名存儲(chǔ)到關(guān)系中去時(shí)，在屬性Cname、Grade等屬性上就沒有值(空值)。在數(shù)據(jù)庫技術(shù)中空值的語義是非常復(fù)雜的，對(duì)帶空值元組的檢索和操作也十分麻煩。刪除異常。如果一組屬性的值變?yōu)榭?，帶來的副作用可能就是丟失一些其他信息。主鍵Key=snum,cname，如果要?jiǎng)h除某學(xué)生選修的課程“建筑原理”，那么連同該生的整條記錄都要被刪掉，這樣就刪掉了不該丟的信息。 表3.2學(xué)生選課表SnumSnameSdeptMnameCnameGrade991230賀小華計(jì)算機(jī)周至光數(shù)據(jù)庫96

15、991239金謙計(jì)算機(jī)周至光操作系統(tǒng)90991239金謙計(jì)算機(jī)周至光編譯原理92993851陳剛建筑王勇建筑原理89992076呂宋自動(dòng)化李霞自動(dòng)化設(shè)計(jì)85992076呂宋自動(dòng)化李霞電路原理82函數(shù)依賴 設(shè)有關(guān)系模式R(U)，X和Y是屬性集U的子集，函數(shù)依賴(Functional Dependency，簡(jiǎn)記為FD)是形為XY的一個(gè)命題，只要r是R的當(dāng)前關(guān)系，對(duì)r中任意兩個(gè)元組t和s，都有tX=sX蘊(yùn)涵tY=sY，那么稱FD XY在關(guān)系模式R(U)中成立。函數(shù)依賴實(shí)例例：關(guān)于學(xué)生選課的關(guān)系模式：student(snum,sname,sdept,mname,cname,grade) 屬性分別表示學(xué)

16、生學(xué)號(hào)、姓名、開課的系別、系主任、課程名、成績。 如果規(guī)定，每個(gè)學(xué)號(hào)只能有一個(gè)學(xué)生姓名，每個(gè)課程號(hào)只能決定一門課程，那么可寫成下列FD形式：snumsname sdeptmname每個(gè)學(xué)生每學(xué)一門課程，有一個(gè)成績，那么可寫出下列FD：(snum,cname)grade還可以寫出其他一些FD：snum(sname,sdept,mname)問題的根源(1)完全函數(shù)依賴與部分函數(shù)依賴 對(duì)于函數(shù)依賴WA，如果存在VW（V是W的真子集）而函數(shù)依賴VA成立，則稱A部分依賴(Partial Dependency)于W；否則，若不存在這種V，則稱A完全函數(shù)依賴(Full Dependency)于W。說明：完全

17、函數(shù)依賴和部分函數(shù)依賴主要強(qiáng)調(diào)的是主鍵與非主屬性之間的關(guān)系。函數(shù)依賴實(shí)例例如，在關(guān)系模式Student(snum,sname,sdept,mname,cname,grade)中，snum,cname為主鍵，并存在下列函數(shù)依賴： snumsname snumsdept snummnamesnum,cname sname,sdept,mnamepsnum,cname gradeF 結(jié)論：在一個(gè)關(guān)系模式中，當(dāng)存在非主屬性對(duì)主鍵的部分函數(shù)依賴時(shí)，就會(huì)產(chǎn)生數(shù)據(jù)冗余和更新異常。若是完全函數(shù)依賴，則不會(huì)出現(xiàn)類似的問題。（2）傳遞函數(shù)依賴 對(duì)于函數(shù)依賴X Y，如果Y X（X不函數(shù)依賴于Y），而函數(shù)依賴Y Z成

18、立，則稱Z傳遞函數(shù)依賴于X（Transitive Dependency）結(jié)論：傳遞依賴也是產(chǎn)生數(shù)據(jù)冗余和更新異常的主要根源。 snum sdept, sdept mname, 又 sdept snum snum mnamet規(guī)范化所謂范式就是符合某一規(guī)范級(jí)別的關(guān)系模式的集合。共有六種范式(Normal Form，NF)： 1NF2NF3NFBCNF4NF5NF規(guī)范化：通過分解把屬于低級(jí)范式的關(guān)系模式轉(zhuǎn)換為幾個(gè)屬于高級(jí)范式的關(guān)系模式的集合，這一過程稱為規(guī)范化(Normalization)。范式（一）（1）第一范式（First Normal Form，1NF） 如果一個(gè)關(guān)系模式R的所有屬性都是不可

19、再分的基本數(shù)據(jù)項(xiàng)，則這個(gè)關(guān)系屬于第一范（2）第二范式（Second Normal Form，2NF） 若關(guān)系模式R屬于第一范式，且每個(gè)非主屬性都完全函數(shù)依賴于主鍵，則R屬于第二范式。 對(duì)于上述的Student關(guān)系模式，由于存在部分函數(shù)依賴：因此就有：student2NFSnum,cname sname,sdept,mnamep范式（二）（3）第三范式（Third Normal Form，3NF） 若關(guān)系模式R屬于第一范式，且每個(gè)非主屬性都不傳遞依賴于主鍵，則R屬于第三范式。（4）BC范式（Boyce-Codd，BCNF） 若關(guān)系模式R屬于第一范式，且每個(gè)屬性都不傳遞依賴于主鍵，則R屬于BC范式

20、。 也就是說：決定因素必須包含鍵碼。模式的分解模式分解的基本原則：1、無損連接性（Lossless Join） 設(shè)關(guān)系模式R（U，F(xiàn)）被分解為若干個(gè)關(guān)系模式R1，R2，Rn，如果R與R1，R2，Rn自然連接的結(jié)果相等，則稱關(guān)系模式R的分解具有無損連接性。無損連接性關(guān)系RABC123678978R1AB126797R2BC2378R1 R2ABC123678978有損連接性的示例 ABC173678978關(guān)系RAB176797R1 R2BC7378R2ABC173178673678973978 R1 R2模式的分解模式的分解原則：2、函數(shù)依賴保持性（Preserve Dependency） 設(shè)關(guān)

21、系模式R（U，F(xiàn)）被分解為若干個(gè)關(guān)系模式R1，R2，Rn，如果R中的函數(shù)依賴集F與關(guān)系分解模式R1，R2，Rn中所有的函數(shù)依賴是一致的，則稱關(guān)系模式R的分解具有函數(shù)依賴保持性。 規(guī)范化實(shí)例分析 例3.11 關(guān)系模式student的規(guī)范化處理過程。 student(snum,sname,sdept,mname,cname,grade) （1）確定是否是1NF 因?yàn)樵陉P(guān)系模式student中，不存在表中有表的情況，即每個(gè)屬性都是不可再分的，所以，關(guān)系模式student1NF.student(snum,sname,sdept,mname,cname,grade)（2）確定是否是2NF在關(guān)系student中，主鍵為：snum,cname因?yàn)榇嬖趯傩约痵name,sdept,mname對(duì)主鍵snum,cname的部分函數(shù)依賴：Snum,cname sname,sdept,mnamep所以： student2NF分解為關(guān)系： R1(snum,sname,sdept,mname)其中存在函數(shù)依賴： snumsname,sdept,mname sdeptmname 及關(guān)系： R2 (snum,cname,grade)其中存在函數(shù)依賴： snum,cnamegrade很顯然，R1和R2都不存在部分函數(shù)依賴，所以: R1、R22NF。 s