access數(shù)據(jù)庫(kù)設(shè)計(jì)案例

數(shù)據(jù)庫(kù)設(shè)計(jì)第七章數(shù)據(jù)庫(kù)設(shè)計(jì)7.1數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程與設(shè)計(jì)實(shí)例7.2需求分析7.3概念結(jié)構(gòu)設(shè)計(jì)7.4邏輯結(jié)構(gòu)設(shè)計(jì)7.5關(guān)系模式的規(guī)范化7.6設(shè)計(jì)實(shí)例——期刊采編系統(tǒng)7.1數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程

與設(shè)計(jì)實(shí)例前面章節(jié)介紹的內(nèi)容只解決了系統(tǒng)的實(shí)現(xiàn)過(guò)程，即如何建立各種對(duì)象，但并沒(méi)有解釋為什么創(chuàng)建這些對(duì)象。數(shù)據(jù)庫(kù)設(shè)計(jì)是根據(jù)一個(gè)給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)模型，利用DBMS，建立數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)。如何建立一個(gè)高效適用的數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)，是數(shù)據(jù)庫(kù)應(yīng)用領(lǐng)域中的一個(gè)重要課題。數(shù)據(jù)庫(kù)設(shè)計(jì)是一項(xiàng)軟件工程，具有自身的特點(diǎn)，已逐步形成了數(shù)據(jù)庫(kù)設(shè)計(jì)方法學(xué)。簡(jiǎn)單地講，數(shù)據(jù)庫(kù)設(shè)計(jì)包括結(jié)構(gòu)設(shè)計(jì)和行為設(shè)計(jì)。結(jié)構(gòu)設(shè)計(jì)是指按照應(yīng)用要求，確定一個(gè)合理的數(shù)據(jù)模型。數(shù)據(jù)模型是用來(lái)反映和顯示事物及其關(guān)系的。結(jié)構(gòu)設(shè)計(jì)的結(jié)果就是得到數(shù)據(jù)庫(kù)中表的結(jié)構(gòu)。行為設(shè)計(jì)是指應(yīng)用程序的設(shè)計(jì)，將設(shè)計(jì)的結(jié)果物理化，實(shí)施數(shù)據(jù)庫(kù)，如完成查詢、修改、添加、刪除、統(tǒng)計(jì)數(shù)據(jù)，制作報(bào)表等。行為設(shè)計(jì)要求滿足數(shù)據(jù)的完整性、安全性、并發(fā)控制和數(shù)據(jù)庫(kù)的恢復(fù)。數(shù)據(jù)庫(kù)設(shè)計(jì)要經(jīng)歷6個(gè)階段：需求分析概念結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)物理結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)庫(kù)實(shí)施數(shù)據(jù)庫(kù)運(yùn)行與維護(hù)需求分析——數(shù)據(jù)流程圖需求分析——數(shù)據(jù)流數(shù)據(jù)流名稱：教師信息來(lái)源：教師去向：處理——授課包含的數(shù)據(jù)項(xiàng)：教師號(hào)姓名性別年齡職稱專業(yè)需求分析——數(shù)據(jù)字典數(shù)據(jù)項(xiàng)名稱數(shù)據(jù)類型及長(zhǎng)度說(shuō)明學(xué)號(hào)字符，固定長(zhǎng)度6前4位為班號(hào)，后2位為在班內(nèi)序號(hào)學(xué)生姓名字符，可變長(zhǎng)度8

學(xué)生性別字符，固定長(zhǎng)度2取值范圍男或女……

概念結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)“教學(xué)管理系統(tǒng)”的數(shù)據(jù)模型表示為：系（系號(hào)，系名，系主任，電話）教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡，系號(hào)）學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，專業(yè)，照片，系號(hào)）課程（課程號(hào)，課程名，學(xué)時(shí)，類別）課程評(píng)價(jià)（教師號(hào)，課程號(hào)，評(píng)價(jià)）選修（學(xué)號(hào)，課程號(hào)，分?jǐn)?shù)）

物理設(shè)計(jì)進(jìn)行物理結(jié)構(gòu)設(shè)計(jì)是根據(jù)使用的計(jì)算機(jī)軟硬件環(huán)境和數(shù)據(jù)庫(kù)管理系統(tǒng)，確定數(shù)據(jù)庫(kù)表的結(jié)構(gòu)，并進(jìn)行優(yōu)化，為數(shù)據(jù)模型選擇合理的存儲(chǔ)結(jié)構(gòu)和存取方法，決定存取路徑和分配存取空間等。

建立索引的基本原則1）如果一個(gè)屬性（或一組屬性）經(jīng)常在查詢條件或在連接操作的聯(lián)接條件中出現(xiàn)，則考慮在這個(gè)屬性（或這組屬性）上建立索引（或組合索引）2）如果一個(gè)屬性經(jīng)常作為最大值或最小值等聚合函數(shù)的參數(shù)，則考慮在這個(gè)屬性上建立索引數(shù)據(jù)庫(kù)實(shí)施數(shù)據(jù)庫(kù)實(shí)施是運(yùn)用DBMS建立數(shù)據(jù)庫(kù)，創(chuàng)建各種對(duì)象（表、窗體、查詢……），編制與調(diào)試應(yīng)用程序，錄入數(shù)據(jù)，進(jìn)行試運(yùn)行數(shù)據(jù)庫(kù)運(yùn)行與維護(hù)數(shù)據(jù)庫(kù)系統(tǒng)正式投入使用后，還應(yīng)不斷進(jìn)行評(píng)價(jià)、修改與調(diào)整。這一時(shí)期的工作就是數(shù)據(jù)庫(kù)的運(yùn)行和維護(hù)。數(shù)據(jù)庫(kù)設(shè)計(jì)流程7.2需求分析需求分析是數(shù)據(jù)庫(kù)設(shè)計(jì)的起點(diǎn)和基礎(chǔ)需求分析階段的工作是詳細(xì)準(zhǔn)確地了解數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的運(yùn)行環(huán)境和用戶要求開(kāi)發(fā)的目的是什么用戶需要從數(shù)據(jù)庫(kù)中得到的數(shù)據(jù)信息是什么輸出這些信息采用何種方式或格式需求分析的方法------調(diào)查步驟調(diào)查各部門的組成和業(yè)務(wù)活動(dòng)幫助用戶進(jìn)一步明確系統(tǒng)最終要求把結(jié)果以標(biāo)準(zhǔn)化的文檔表示出來(lái)，如使用數(shù)據(jù)流程圖、數(shù)據(jù)字典和需求說(shuō)明等方式發(fā)調(diào)查表請(qǐng)專業(yè)人員介紹詢問(wèn)跟班作業(yè)查閱資料數(shù)據(jù)流程圖的基本畫法數(shù)據(jù)流程圖要表述出數(shù)據(jù)來(lái)源、數(shù)據(jù)處理、數(shù)據(jù)輸出以及數(shù)據(jù)存儲(chǔ)，它主要反映了數(shù)據(jù)和處理的關(guān)系。

7.3概念結(jié)構(gòu)設(shè)計(jì)任務(wù)將需求分析結(jié)果抽象為概念模型方法建立E-R模型(Entity-Relationshipmodel)E-R模型的表示方法為E-R圖E-R圖實(shí)質(zhì)表示了實(shí)體、屬性和實(shí)體間的聯(lián)系7.3.1E-R圖的表示方法實(shí)體——用矩形表示，矩形框內(nèi)寫明實(shí)體名屬性——用橢圓表示，橢圓內(nèi)寫明屬性名，并且將橢圓用線與相應(yīng)的實(shí)體連接。聯(lián)系——用菱形表示，菱形框內(nèi)寫明聯(lián)系名，并用線分別與有關(guān)的實(shí)體連接起來(lái)，同時(shí)在線上注明聯(lián)系類型。（1：1，1：n或m：n）學(xué)生實(shí)體、課程實(shí)體的屬性及其聯(lián)系的E-R圖表示

系、教師、學(xué)生和課程間的聯(lián)系確定出實(shí)體與實(shí)體間聯(lián)系教學(xué)管理的E-R模型

填全實(shí)體屬性7.3.2建立E-R模型中的幾個(gè)問(wèn)題相對(duì)原則對(duì)于同一對(duì)象抽象的結(jié)果可能不同簡(jiǎn)單原則現(xiàn)實(shí)世界的事物能作為屬性的盡量歸為屬性設(shè)計(jì)過(guò)程設(shè)計(jì)局部E-R模型集成全局E-R模型解決三種沖突命名沖突屬性沖突結(jié)構(gòu)沖突簡(jiǎn)單原則舉例不考慮宿舍的進(jìn)一步信息時(shí)，宿舍應(yīng)作為屬性建立E-R模型舉例例7-1

一個(gè)企業(yè)應(yīng)用包括人員管理、生產(chǎn)管理、設(shè)備管理等功能模塊。其中：人員管理需要記錄職工編號(hào)、職工姓名、職務(wù)（干部/工人）、年齡、性別等。一個(gè)職工工作于一個(gè)部門，一個(gè)部門有若干職工。對(duì)于部門應(yīng)記錄各部門的編號(hào)、部門名稱、負(fù)責(zé)人、電話等信息。設(shè)計(jì)局部E-R模型

——人員管理的E-R圖

設(shè)計(jì)局部E-R模型

——設(shè)備管理的E-R圖

設(shè)計(jì)局部E-R模型

——生產(chǎn)管理的E-R圖

將局部E-R模型集成全局E-R模型7.4邏輯結(jié)構(gòu)設(shè)計(jì)任務(wù)將概念設(shè)計(jì)結(jié)果轉(zhuǎn)換為DBMS支持的數(shù)據(jù)模型步驟將概念結(jié)構(gòu)轉(zhuǎn)換為數(shù)據(jù)模型將轉(zhuǎn)換來(lái)的模型向特定DBMS支持的數(shù)據(jù)模型轉(zhuǎn)換（這里使用關(guān)系數(shù)據(jù)庫(kù)模型）對(duì)數(shù)據(jù)模型進(jìn)行優(yōu)化7.4.1E-R模型轉(zhuǎn)換為

關(guān)系模型的方法實(shí)體的轉(zhuǎn)換一個(gè)實(shí)體轉(zhuǎn)換為一個(gè)關(guān)系模式實(shí)體間聯(lián)系的轉(zhuǎn)換一對(duì)一聯(lián)系一對(duì)多聯(lián)系多對(duì)多聯(lián)系主鍵的定義實(shí)體的轉(zhuǎn)換一個(gè)實(shí)體轉(zhuǎn)換為一個(gè)關(guān)系模式實(shí)體的屬性就是關(guān)系模式的屬性實(shí)體的鍵就是關(guān)系的主鍵例如教學(xué)管理系統(tǒng)中共有教師、系、學(xué)生和課程四個(gè)實(shí)體，它們轉(zhuǎn)換為關(guān)系模式后分別為教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡）系（系號(hào)，系名，系主任，電話）學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，專業(yè)，照片）課程（課程號(hào)，課程名，學(xué)時(shí)，類別）

一對(duì)一聯(lián)系的轉(zhuǎn)換方法一聯(lián)系轉(zhuǎn)換為獨(dú)立的關(guān)系模式模式的屬性由聯(lián)系本身的屬性及兩個(gè)實(shí)體的鍵構(gòu)成主鍵由兩個(gè)實(shí)體中的任意一個(gè)鍵構(gòu)成方法二將聯(lián)系與一端的實(shí)體的關(guān)系模式合并，即將聯(lián)系的屬性加入到實(shí)體的關(guān)系模式內(nèi)主鍵不變一對(duì)一聯(lián)系的轉(zhuǎn)換

——建立獨(dú)立的關(guān)系模式實(shí)體對(duì)應(yīng)關(guān)系模式分別為：班級(jí)（班號(hào)，專業(yè)，人數(shù)）班長(zhǎng)（學(xué)號(hào)，姓名，專長(zhǎng)）聯(lián)系管理（班號(hào)，學(xué)號(hào)）關(guān)系模式“管理”的主鍵也可以選擇學(xué)號(hào)一對(duì)一聯(lián)系的轉(zhuǎn)換

——合并到實(shí)體關(guān)系模式原實(shí)體對(duì)應(yīng)關(guān)系模式分別為：班級(jí)（班號(hào)，專業(yè)，人數(shù)）班長(zhǎng)（學(xué)號(hào)，姓名，專長(zhǎng)）將聯(lián)系“管理”合并到實(shí)體“班級(jí)”對(duì)應(yīng)的模式后為：班級(jí)（班號(hào)，專業(yè)，人數(shù)，學(xué)號(hào)）班長(zhǎng)（學(xué)號(hào)，姓名，專長(zhǎng)）聯(lián)系“管理”也可以合并到實(shí)體“班長(zhǎng)”對(duì)應(yīng)的模式一對(duì)多聯(lián)系的轉(zhuǎn)換方法一聯(lián)系轉(zhuǎn)換為獨(dú)立的關(guān)系模式模式的屬性由聯(lián)系本身的屬性及兩個(gè)實(shí)體的鍵構(gòu)成主鍵由n端實(shí)體的鍵組成方法二將聯(lián)系與n端的實(shí)體的關(guān)系模式合并，即將聯(lián)系的屬性加入到實(shí)體的關(guān)系模式內(nèi)主鍵不變一對(duì)多聯(lián)系的轉(zhuǎn)換

——建立獨(dú)立的關(guān)系模式實(shí)體對(duì)應(yīng)的關(guān)系模式系（系號(hào)，系名，系主任，電話）教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡）聯(lián)系對(duì)應(yīng)的關(guān)系模式管理（教師號(hào)，系號(hào)）一對(duì)多聯(lián)系的轉(zhuǎn)換

——合并到實(shí)體關(guān)系模式實(shí)體對(duì)應(yīng)的關(guān)系模式

系（系號(hào)，系名，系主任，電話）教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡）合并到實(shí)體“教師”后教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡，系號(hào)）只能合并到“多”的一端多對(duì)多聯(lián)系的轉(zhuǎn)換聯(lián)系只能轉(zhuǎn)換為獨(dú)立模式模式的屬性由聯(lián)系本身的屬性及兩個(gè)實(shí)體的鍵構(gòu)成主鍵由兩端實(shí)體的鍵組合而成多對(duì)多聯(lián)系的轉(zhuǎn)換

——建立獨(dú)立的關(guān)系模式課程（課程號(hào)，課程名，學(xué)時(shí)，類別）學(xué)生（學(xué)號(hào)，姓名，性別，專業(yè)，出生日期，照片）選修（學(xué)號(hào)，課程號(hào)，分?jǐn)?shù)）7.4.2E-R模型轉(zhuǎn)換為關(guān)系模型舉例根據(jù)教學(xué)管理E-R模型建立教學(xué)管理系統(tǒng)的關(guān)系模型根據(jù)例7-1的E-R模型建立企業(yè)應(yīng)用系統(tǒng)的關(guān)系模型系（系號(hào)，系名，系主任，電話）學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，專業(yè)，照片）課程（課程號(hào)，課程名，學(xué)時(shí)，類別）

教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡）選修（學(xué)號(hào)，課程號(hào)，分?jǐn)?shù)）課程評(píng)價(jià)（教師號(hào)，課程號(hào)，評(píng)價(jià)）管理（教師號(hào)，系號(hào)）注冊(cè)（學(xué)號(hào)，系號(hào)）系（系號(hào)，系名，系主任，電話）課程（課程號(hào)，課程名，學(xué)時(shí)，類別）教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡，系號(hào)）學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，專業(yè)，照片，系號(hào)）選修（學(xué)號(hào)，課程號(hào)，分?jǐn)?shù)）課程評(píng)價(jià)（教師號(hào)，課程號(hào)，評(píng)價(jià)）教學(xué)管理的關(guān)系模型設(shè)備（設(shè)備編號(hào)，設(shè)備名稱，價(jià)格）零件（零件編號(hào)，零件名稱，規(guī)格）人員（職工編號(hào)，姓名，性別，年齡，職務(wù)）部門（部門編號(hào)，部門名稱，負(fù)責(zé)人，電話）生產(chǎn)（零件編號(hào)，部門編號(hào)，生產(chǎn)日期，生產(chǎn)數(shù)量）裝配（設(shè)備編號(hào)，零件編號(hào)，裝配日期，裝配數(shù)量）包含（職工編號(hào)，部門編號(hào)）人員（職工編號(hào)，姓名，性別，年齡，職務(wù)，部門編號(hào)）

管理（部門編號(hào)，設(shè)備編號(hào)）合并合并設(shè)備（設(shè)備編號(hào)，設(shè)備名稱，價(jià)格，部門編號(hào)）企業(yè)應(yīng)用的關(guān)系模型人員（職工編號(hào)，姓名，性別，年齡，職務(wù)，部門編號(hào)）部門（部門編號(hào)，部門名稱，負(fù)責(zé)人，電話）設(shè)備（設(shè)備編號(hào)，設(shè)備名稱，價(jià)格，部門編號(hào)）零件（零件編號(hào)，零件名稱，規(guī)格）生產(chǎn)（零件編號(hào)，部門編號(hào)，生產(chǎn)日期，生產(chǎn)數(shù)量）裝配（設(shè)備編號(hào)，零件編號(hào)，裝配日期，裝配數(shù)量）

7.4.3數(shù)據(jù)完整性設(shè)計(jì)實(shí)體完整性參照完整性用戶自定義完整性7.5關(guān)系模式的規(guī)范化7.5.1問(wèn)題的提出7.5.2函數(shù)依賴與鍵7.5.3關(guān)系模式的范式與規(guī)范化供應(yīng)商編號(hào)供應(yīng)商名稱聯(lián)系方式商品名稱商品價(jià)格101華訊12345678光驅(qū)180101華訊12345678光盤150101華訊12345678打印紙20102欣欣87654321光盤160102欣欣87654321鼠標(biāo)56…

例如針對(duì)供應(yīng)商建立了如下關(guān)系模式：

供貨（供應(yīng)商編號(hào)，供應(yīng)商名稱，聯(lián)系方式，商品名稱，商品價(jià)格）數(shù)據(jù)冗余大數(shù)據(jù)不一致性操作異常這個(gè)模式存在如下問(wèn)題函數(shù)依賴函數(shù)依賴的概念函數(shù)依賴的表示方法函數(shù)依賴的類型完全依賴部分依賴傳遞依賴

學(xué)生關(guān)系的函數(shù)依賴關(guān)系學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，專業(yè)）該關(guān)系的函數(shù)依賴集表示為：學(xué)號(hào)→姓名學(xué)號(hào)→性別學(xué)號(hào)→出生日期學(xué)號(hào)→專業(yè)函數(shù)依賴的定義給定一關(guān)系R，若當(dāng)且僅當(dāng)對(duì)應(yīng)于R中屬性X的每一個(gè)值，在任一時(shí)刻必有一個(gè)確定的屬性Y值，則稱Y是函數(shù)依賴于X，也可稱為X函數(shù)決定Y，記為X->Y。成績(jī)（學(xué)號(hào)，姓名，課程號(hào)，課程名，分?jǐn)?shù)）

成績(jī)關(guān)系的函數(shù)依賴集:學(xué)號(hào)→姓名課程號(hào)→課程名(學(xué)號(hào),課程號(hào))→分?jǐn)?shù)完全函數(shù)依賴和部分函數(shù)依賴完全依賴若稱關(guān)系R中的屬性Y是完全依賴于屬性X，則應(yīng)滿足以下兩個(gè)條件：屬性Y函數(shù)依賴于屬性X屬性Y不函數(shù)依賴于屬性X的任一真子集X’部分依賴如果屬性Y只函數(shù)依賴于屬性X的某一真子集X’，則稱屬性Y部分函數(shù)依賴于屬性X。傳遞依賴定義：在關(guān)系R中，如果屬性X、Y、Z之間滿足：XY,YZ，則稱Z對(duì)X傳遞依賴。例如關(guān)系模式:輔導(dǎo)（學(xué)號(hào)，班級(jí)，輔導(dǎo)員）函數(shù)依賴集:學(xué)號(hào)→班級(jí)班級(jí)→輔導(dǎo)員鍵定義：設(shè)K是關(guān)系R模式中的屬性，當(dāng)K的值確定后，關(guān)系中其它屬性值也就唯一確定，且K的任何一個(gè)真子集不再具有這樣的性質(zhì)，則稱K為R的鍵或候選鍵。注意：通常選擇其中一個(gè)作主鍵K可以是單個(gè)屬性或?qū)傩越M合主屬性與非主屬性主屬性包含在任何一個(gè)候選鍵中的屬性稱為主屬性非主屬性不包含在候選鍵中的屬性稱為非主屬性或非鍵屬性關(guān)系中主鍵是單屬性的，則非鍵屬性對(duì)主鍵肯定是完全函數(shù)依賴的。而當(dāng)主鍵是復(fù)合屬性時(shí)，則非鍵屬性對(duì)主鍵的函數(shù)依賴就有完全依賴和部分依賴兩種可能。外部鍵設(shè)X是關(guān)系模式R中的屬性或?qū)傩越M，X并非R的鍵，而是另一關(guān)系模式的鍵，則稱X是R的外鍵。如：系（部門編號(hào)，系名，系主任，電話）教師（教師號(hào)，姓名，專業(yè)，職稱，性別，年齡，部門編號(hào)）范式與規(guī)范化范式關(guān)系滿足不同層次的要求就稱為不同的范式范式由低到高依次為1NF,2NF,3NF,4NF,5NF規(guī)范化將一個(gè)低一級(jí)范式的關(guān)系模式分解為若干個(gè)滿足高一級(jí)范式關(guān)系模式的集合的過(guò)程1NF、2NF、3NF1NF：如果關(guān)系模式R的每一個(gè)屬性只包含單一的值，則關(guān)系模式R滿足1NF2NF：如果關(guān)系R滿足第一范式，而且它的所有非主關(guān)鍵字屬性完全依賴于整個(gè)主屬性（也就是說(shuō)，不存在部分依賴），則R滿足第二范式3NF：如果某關(guān)系滿足第二范式，而且他的任何一個(gè)非主屬性都不傳遞函數(shù)依賴于任何非主屬性，則R滿足第三范式