數(shù)據(jù)庫ER圖設(shè)計的課件

1、第6章 數(shù)據(jù)庫設(shè)計 數(shù)據(jù)庫設(shè)計是開發(fā)數(shù)據(jù)庫應(yīng)用系統(tǒng)的關(guān)鍵。簡單地講數(shù)據(jù)庫設(shè)計就是創(chuàng)建數(shù)據(jù)庫系統(tǒng)的管理對象。 本章以學(xué)生信息管理系統(tǒng)為例,講解數(shù)據(jù)庫設(shè)計的有關(guān)內(nèi)容。5.1數(shù)據(jù)庫設(shè)計概述數(shù)據(jù)庫設(shè)計方法主要是設(shè)計數(shù)據(jù)庫系統(tǒng)結(jié)構(gòu)中的三級模式結(jié)構(gòu)。物理模式邏輯模式外模式1外模式3外模式2應(yīng)用E應(yīng)用D應(yīng)用C應(yīng)用B應(yīng)用A 磁盤說明：邏輯模式與外模式是一對多的關(guān)系；邏輯模式與物理模式是一對一的關(guān)系。5.1數(shù)據(jù)庫設(shè)計概述數(shù)據(jù)庫設(shè)計步驟數(shù)據(jù)庫的設(shè)計過程可分為六個階段： 1. 需求分析2. 概念結(jié)構(gòu)設(shè)計階段3. 邏輯結(jié)構(gòu)設(shè)計階段4. 物理結(jié)構(gòu)設(shè)計階段5. 數(shù)據(jù)庫實施6. 數(shù)據(jù)庫運行與維護概念結(jié)構(gòu)設(shè)計階段需求分析物理

2、結(jié)構(gòu)設(shè)計階段邏輯結(jié)構(gòu)設(shè)計階段數(shù)據(jù)庫運行維護數(shù)據(jù)庫實施5.2需求分析 需求分析是數(shù)據(jù)庫設(shè)計的第一步。這一階段主要是對數(shù)據(jù)庫應(yīng)用系統(tǒng)所要處理的對象進行全面了解，了解用戶需求，收集信息，提出問題及解決問題的思路。 例如：建立“學(xué)生信息管理系統(tǒng)”，目的是對學(xué)院、系、班級、教研室等部門的信息進行管理，還要管理教師和學(xué)生的基本信息，以及學(xué)生選課的情況。 信息要求：系統(tǒng)涉及到學(xué)院、系、教研室、教師、學(xué)生、班級、課程、選課等各方面的信息。 處理要求：增加、刪除、修改各數(shù)據(jù)對象中的數(shù)據(jù)，各類查詢、統(tǒng)計及報表打印等等。 安全性要求：只允許學(xué)院內(nèi)部人員使用該系統(tǒng)，允許學(xué)生查詢成績但不能修改，允許教師錄入和修改學(xué)生成

3、績等。5.3概念結(jié)構(gòu)設(shè)計 概念設(shè)計的目標是產(chǎn)生反映需求信息的數(shù)據(jù)庫概念結(jié)構(gòu)，它與具體的DBMS無關(guān)。從現(xiàn)實世界到概念模型的轉(zhuǎn)換是由數(shù)據(jù)庫設(shè)計人員完成的，概念設(shè)計用實體-聯(lián)系模型(簡稱E-R圖) 表示。在E-R模型中，用于描述數(shù)據(jù)的概念主要有實體、屬性、實體型、實體集、關(guān)鍵字及實體之間的聯(lián)系等。 現(xiàn)實世界概念模型將現(xiàn)實世界客觀存在的事物及其相互間聯(lián)系通過符號記錄下來抽象為信息世界。 1. 實體及相關(guān)概念 (1) 實體：客觀存在并相互區(qū)別的事物。 實體可以是可觸及的對象，如一個學(xué)生，一本書，一輛汽車；也可以是抽象的事件，如一堂課，一次比賽等。 (2) 屬性：實體所具有的特征和性質(zhì)。 如：學(xué)生實體有

4、學(xué)號、姓名、年齡、性別等。 (3) 碼：在實體的屬性當中能夠唯一的標識一個實體的屬性稱為這個實體的關(guān)鍵字，也稱碼或鍵。 如：學(xué)生的學(xué)號。而學(xué)生的姓名可能有重名，不能作為學(xué)生實體的關(guān)鍵字(碼)。 (4) 實體集：具有相同屬性的同一類事物，是一個實體的集合。 如：所有的學(xué)生信息記錄、所有的課程信息記錄等。 (5) 實體型：用實體名和屬性名稱集來描述同類實體。 如：學(xué)生(學(xué)號，姓名，年齡，性別)就是一個實體型。所有學(xué)生都可用這一實體型進行描述。 兩個或兩個以上的實體集間的關(guān)聯(lián)關(guān)系的描述。 如：系與系主任、班級與班長(1:1關(guān)系) ；系與教研室、班級與學(xué)生(1:n關(guān)系) ；學(xué)生與課程、教師與課程(m:

5、n關(guān)系) 。 實體集之間的聯(lián)系分一對一(1:1)、一對多(1:n)、多對多(m:n)3種。 2. 聯(lián)系與聯(lián)系集 E-R圖 E-R模型(概念模型的描述方法) ，是用實體-聯(lián)系圖(簡稱E-R圖）來描述的，是通過圖形描述實體集、實體屬性和實體集之間聯(lián)系的圖形。 實體集矩形框 屬性橢圓框 聯(lián)系菱形框例如：學(xué)院實體集的E-R圖學(xué)院學(xué)院編號學(xué)院名稱院長姓名學(xué)院電話學(xué)院地址 例如：教師與班級、學(xué)院與系和班級、學(xué)生和課程實體集之間聯(lián)系的E-R圖。教師班級班主任11學(xué)生課程選課mn學(xué)院系設(shè)置n1班級設(shè)置1n 例如：學(xué)生、教師和課程三個實體共同參與了一個選課聯(lián)系，一般稱這種由三類實體參與的聯(lián)系為三元聯(lián)系。因為某教

6、師選定一門課講授，學(xué)生選中同一門課要學(xué)習(xí)時，三者才會聯(lián)系起來。 教師、學(xué)生與課程的E-R圖mmnn 多對多關(guān)系實際上是某兩個表與第三個表的兩個一對多關(guān)系，第三個表的主鍵包含兩個字段，分別是前兩個表的主鍵。字段名字段類型字段長度小數(shù)點索引類型學(xué)號字符型6主索引姓名字符型6性別字符型2出生年月日期/時間型短日期籍貫字符型50班級編號字符型8外鍵選課 學(xué)生-選課(1:n) 課程-選課(1:n) 教師-選課(1:n) nMMn此圖為3個表與第4個表的聯(lián)系。學(xué)生信息管理系統(tǒng)(E-R概念模型)學(xué)院學(xué)院編號學(xué)院名稱院長姓名學(xué)院電話學(xué)院地址設(shè)置系系名稱系主任系編號學(xué)院編號1n學(xué)生屬于姓名性別學(xué)生編號出生年月籍

7、貫班級編號1n課程學(xué)時學(xué)分課程名課程編號mnnm選課成績1nn11n1教研室設(shè)置教研室編號教研室名稱系編號n教師屬于教師編號姓名性別教研室編號n1設(shè)置1n班級班級名班長姓名專業(yè)系編號班級編號5.4邏輯結(jié)構(gòu)設(shè)計 邏輯結(jié)構(gòu)設(shè)計：將已設(shè)計好的概念模型(E-R模型)轉(zhuǎn)換為與DBMS支持的數(shù)據(jù)模型相符的邏輯結(jié)構(gòu)。 從概念模型到邏輯模型的轉(zhuǎn)換可以由數(shù)據(jù)庫設(shè)計人員完成，也可以用數(shù)據(jù)庫設(shè)計工具協(xié)助設(shè)計人員完成。概念模型邏輯模型E-R模型與關(guān)系模型的轉(zhuǎn)換規(guī)則： 一個實體型轉(zhuǎn)換為一個關(guān)系模式。實體的屬性就是關(guān)系的屬性，實體的鍵就是關(guān)系的鍵。學(xué)院學(xué)院編號學(xué)院名稱院長姓名學(xué)院電話學(xué)院地址 例如：將學(xué)院實體轉(zhuǎn)換為一個關(guān)

8、系，其屬性取原來實體的屬性，利用規(guī)則1，得到關(guān)系模式： 學(xué)院(學(xué)院編號,學(xué)院名稱,院長姓名,學(xué)院電話,學(xué)院地址) 一個1:1聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以與任意一端對應(yīng)的關(guān)系模式合并。舉例：兩個實體:一對一聯(lián)系系(系編號,系名字,系性質(zhì))系主任 (工號,姓名,年齡,系編號)系(系編號,系名字,系性質(zhì),工號)系主任 (工號,姓名,年齡) 聯(lián)系系系編號系名字系性質(zhì)系主任年齡姓名擁有11工號系(系編號,系名字,系性質(zhì))系主任 (工號,姓名,年齡)系與系主任(工號,系編號)系(系編號,系名字,系性質(zhì), 系主任姓名,年齡)方式1方式2方式3方式4 一個1:n聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也

9、可以與n端對應(yīng)的關(guān)系模式合并。 例如：兩個實體1:n關(guān)系屬于n1學(xué)生姓名性別學(xué)號出生年月籍貫班級編號班級班級名班長姓名專業(yè)系編號班級編號聯(lián)系 利用規(guī)則1將學(xué)生和班級實體各轉(zhuǎn)換為一個關(guān)系，其屬性取原來實體的屬性，得到關(guān)系模式： 學(xué)生(學(xué)號,姓名,性別,出生年月,籍貫) 班級(班級編號，班級名稱，班長姓名，專業(yè)，系編號) 由于班級與學(xué)生之間存在一對多的聯(lián)系，利用規(guī)則3可將這種聯(lián)系合并到n端，即將班級關(guān)系的碼加入到學(xué)生關(guān)系中，得到最終結(jié)果：學(xué)生(學(xué)號,姓名,性別,出生年月,籍貫,班級編號) 實體之間多對多的聯(lián)系轉(zhuǎn)換為一個獨立的關(guān)系模式。與該聯(lián)系相關(guān)系的各實體的碼及聯(lián)系本身的屬性均轉(zhuǎn)換為此關(guān)系模式的屬

10、性，而關(guān)系的碼為各實體的碼的組合。 三個或以上的實體間的一個多元聯(lián)系可以轉(zhuǎn)換為一個關(guān)系模式。 具有相同鍵的關(guān)系模式可以合并。 例如：三個實體m:n關(guān)系選課mm學(xué)生姓名性別學(xué)號出生年月籍貫班級編號教師姓名性別教研室編號教師編號聯(lián)系班級課程名學(xué)時學(xué)分課程編號nn成績 使用規(guī)則1：三個實體集轉(zhuǎn)換為關(guān)系； 使用規(guī)則4和5：為聯(lián)系單獨建立一個關(guān)系，即將學(xué)生課程、教師課程實體之間多對多的多元“選課”聯(lián)系轉(zhuǎn)換為一個獨立的關(guān)系模式。 實體之間多對多的多元“選課”聯(lián)系轉(zhuǎn)換為一個獨立的關(guān)系模式如下： 學(xué)生(學(xué)號,姓名,性別,出生年月,籍貫,班級編號) 教師(教師編號,姓名,性別,職務(wù),教研室編號) 課程(課程編號

11、,課程名,學(xué)時,學(xué)分) 選課(學(xué)號,課程編號,教師編號,成績) (新關(guān)系包含兩組多對多實體的主鍵及其聯(lián)系自身的屬性) E-R圖-關(guān)系模型-關(guān)系數(shù)據(jù)庫的對應(yīng)關(guān)系：概念模型理論關(guān)系模型理論關(guān)系數(shù)據(jù)庫實體集關(guān)系二維表記錄集實體元組記錄屬性屬性字段屬性值分量數(shù)據(jù)項碼主鍵主鍵學(xué)院(學(xué)院編號,學(xué)院名稱,院長姓名,電話,地址)系(系編號,系名稱,系主任,學(xué)院編號)教研室(教研室編號,教研室名稱,系編號)教師(教師編號,姓名,性別,職務(wù),教研室編號)班級(班級編號,班級名稱,班長姓名,專業(yè),系編號)學(xué)生(學(xué)號,姓名,性別,出生年月,籍貫,班級編號)課程(課程編號,課程名,學(xué)時,學(xué)分)選課(學(xué)號,課程編號,教師

12、編號,成績)學(xué)生信息管理系統(tǒng)(邏輯模型)關(guān)系的規(guī)范化： 2. 函數(shù)依賴和鍵的基本概念(略) 函數(shù)依賴它反映屬性或?qū)傩越M之間相依存，互相制約的關(guān)系。 簡單地說，若想設(shè)計一個性能良好的數(shù)據(jù)庫，就要盡量滿足關(guān)系規(guī)范化原則。 1. 數(shù)據(jù)庫設(shè)計中的問題 如果一個關(guān)系沒有經(jīng)過規(guī)范化，可能會出現(xiàn)數(shù)據(jù)冗余、更新異常、插入異常、刪除異常。 3. 范式及其關(guān)系(簡介) 關(guān)系規(guī)范化理論簡稱范式，它提供了判別關(guān)系模式設(shè)計的優(yōu)劣標準，為數(shù)據(jù)庫設(shè)計提供了嚴格的理論基礎(chǔ)。 使用范式表示關(guān)系模式滿足規(guī)范化的等級，滿足最低要求的為第一范式，在第一范式的基礎(chǔ)上滿足進一步要求的可升級為第二范式，其余以此類推。 范式(1NF5NF)

13、，主要為1NF-3NF。也就是說能滿足前3個范式的關(guān)系基本上是合理的。 (1) 1NF：若一個關(guān)系模式R的所有屬性都是不可再分的基本數(shù)據(jù)項，則該關(guān)系模式屬于1NF。 例如：教師關(guān)系表如下，判斷是否為第一范式，并規(guī)范教師關(guān)系。教師編號姓名系別聯(lián)系電話聯(lián)系電話1聯(lián)系電話295010張樂經(jīng)濟12341234567895011趙希明經(jīng)濟23452345678995012李小平經(jīng)濟345634567890教師關(guān)系教師編號姓名系別聯(lián)系電話1聯(lián)系電話295010張樂經(jīng)濟12341234567895011趙希明經(jīng)濟23452345678995012李小平經(jīng)濟345634567890修改后的教師關(guān)系 不符合第一

14、范式，因為“聯(lián)系電話”不是基本數(shù)據(jù)項。 屬于第一范式的關(guān)系應(yīng)該滿足的基本條件是每個元組的每個屬性中只能包含一個數(shù)據(jù)項，不能將兩個以上數(shù)據(jù)項“擠入”到一個屬性中。 (2) 2NF：若關(guān)系模式R屬于1NF，且每個非主屬性都完全依賴于主關(guān)鍵字，則該關(guān)系模式屬于2NF。 例如：學(xué)生選課成績關(guān)系表如下，判斷是否為第二范式，并規(guī)范學(xué)生選課關(guān)系。學(xué)生編號姓名課程編號課程名稱周學(xué)時學(xué)分成績20020102劉力101數(shù)據(jù)庫337720020104劉紅102英語6667學(xué)生選課成績關(guān)系 在這個關(guān)系中學(xué)生編號和課程編號共同組成主關(guān)鍵字，其中成績完全依賴于主關(guān)鍵字，而姓名卻完全依賴于學(xué)號，課程名稱、周學(xué)時、學(xué)分又完全

15、依賴于課程編號，因此，此關(guān)系不符合第二范式。 上述關(guān)系可能帶來以下幾個問題： 數(shù)據(jù)冗余：假設(shè)100個學(xué)生選同一門課，重復(fù)100次相同的學(xué)分。 更新復(fù)雜：若調(diào)整某門課的學(xué)分，與其相關(guān)的記錄都要修改。 刪除異常：若學(xué)生已畢業(yè)，由于學(xué)生編號不存在，選課記錄也要刪除。 插入異常：或開一門新課，還沒有學(xué)生選，由于沒有學(xué)生編號，課程編號、學(xué)分均無法輸入。 可以將上述關(guān)系分解為三個關(guān)系： 學(xué)生(學(xué)生編號，姓名，年齡，入校日期) 選課 (學(xué)生編號，課程編號，成績) 課程(課程編號，課程名稱，周學(xué)時，學(xué)分) (3) 3NF：若關(guān)系模式R屬于1NF，且每個非主屬性都不傳遞依賴于主關(guān)鍵字，則關(guān)系模式屬于3NF。(略

16、) 學(xué)生選課成績的三個關(guān)系： 學(xué)生(學(xué)生編號，姓名，年齡，入校日期) 選課成績(學(xué)生編號，課程編號，成績) 課程(課程編號，課程名稱，周學(xué)時，學(xué)分) 其中 和屬于第三范式， 如果學(xué)分是依據(jù)周學(xué)時多少來決定，那么學(xué)分就是通過周學(xué)時傳遞依賴于課程號。 解決方法是，將課程關(guān)系進一步分解為兩個關(guān)系。 課程(課程編號，課程名稱，周學(xué)時) 學(xué)分(周學(xué)時，學(xué)分)字段名數(shù)據(jù)類型長度默認值規(guī)則索引類型學(xué)號文本12無無 主索引姓名文本8無無一般索引性別文本2男取值:男 女出生年月日期短日期籍貫文本100無無班級編號文本6無無一般索引，外鍵“學(xué)生”關(guān)系 邏輯結(jié)構(gòu)詳細設(shè)計： 若選用了Access數(shù)據(jù)庫管理系統(tǒng)，可按其要規(guī)則設(shè)計類似下面的邏