第七章數(shù)據(jù)庫設(shè)計(jì)

第七章數(shù)據(jù)庫設(shè)計(jì)2課程內(nèi)容回顧數(shù)據(jù)庫有哪些基本操作？建庫建表基本的數(shù)據(jù)操縱語句有哪些？語法是？增（INSERT）刪（DELETE）改（UPDATE）查（SELECT）常用的聚合函數(shù)有哪些？表連接分為哪幾種類型？目標(biāo)回顧關(guān)系型數(shù)據(jù)庫和SQLServer的基本原理理解與數(shù)據(jù)庫設(shè)計(jì)有關(guān)的概念，如數(shù)據(jù)庫建模實(shí)體關(guān)系模型理解用于設(shè)計(jì)數(shù)據(jù)庫的E-R圖及其實(shí)現(xiàn)理解數(shù)據(jù)規(guī)范化數(shù)據(jù)完整性了解數(shù)據(jù)字典、數(shù)據(jù)完整性和數(shù)據(jù)庫服務(wù)器設(shè)計(jì)4為什么需要設(shè)計(jì)數(shù)據(jù)庫2-1修建茅屋需要設(shè)計(jì)嗎？修建大廈需要設(shè)計(jì)嗎？結(jié)論：當(dāng)數(shù)據(jù)庫比較復(fù)雜時(shí)我們需要設(shè)計(jì)數(shù)據(jù)庫5為什么需要設(shè)計(jì)數(shù)據(jù)庫2-2良好的數(shù)據(jù)庫設(shè)計(jì)：節(jié)省數(shù)據(jù)的存儲空間能夠保證數(shù)據(jù)的完整性方便進(jìn)行數(shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā)糟糕的數(shù)據(jù)庫設(shè)計(jì)：數(shù)據(jù)冗余、存儲空間浪費(fèi)內(nèi)存空間浪費(fèi)數(shù)據(jù)更新和插入的異常6軟件項(xiàng)目開發(fā)周期需求分析階段：分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求;概要設(shè)計(jì)階段：設(shè)計(jì)數(shù)據(jù)庫的E-R模型圖，確認(rèn)需求信息的正確和完整;詳細(xì)設(shè)計(jì)階段：將E-R圖轉(zhuǎn)換為多張表，進(jìn)行邏輯設(shè)計(jì)，并應(yīng)用數(shù)據(jù)庫設(shè)計(jì)的三大范式進(jìn)行審核;代碼編寫階段：選擇具體數(shù)據(jù)庫進(jìn)行物理實(shí)現(xiàn)，并編寫代碼實(shí)現(xiàn)前端應(yīng)用;軟件測試階段：……安裝部署：……現(xiàn)實(shí)世界建模信息世界數(shù)據(jù)庫世界模型轉(zhuǎn)換規(guī)范化7設(shè)計(jì)數(shù)據(jù)庫的步驟4-1收集信息：

與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理解數(shù)據(jù)庫需要完成的任務(wù)BBS論壇的基本功能：用戶注冊和登錄，后臺數(shù)據(jù)庫需要存放用戶的注冊信息和在線狀態(tài)信息；用戶發(fā)貼，后臺數(shù)據(jù)庫需要存放貼子相關(guān)信息，如貼子內(nèi)容、標(biāo)題等；論壇版塊管理：后臺數(shù)據(jù)庫需要存放各個(gè)版塊信息，如版主、版塊名稱、貼子數(shù)等；8設(shè)計(jì)數(shù)據(jù)庫的步驟4-2標(biāo)識對象（實(shí)體－Entity）

標(biāo)識數(shù)據(jù)庫要管理的關(guān)鍵對象或?qū)嶓w

實(shí)體一般是名詞：用戶：論壇普通用戶、各版塊的版主。用戶發(fā)的主貼用戶發(fā)的跟貼（回貼）版塊：論壇的各個(gè)版塊信息9設(shè)計(jì)數(shù)據(jù)庫的步驟4-3論壇用戶：呢稱密碼電子郵件生日性別用戶的等級備注信息注冊日期狀態(tài)積分主貼發(fā)貼人發(fā)貼表情回復(fù)數(shù)量標(biāo)題正文發(fā)貼時(shí)間點(diǎn)擊數(shù)狀態(tài)：最后回復(fù)時(shí)間回貼貼子編號回貼人,回貼表情標(biāo)題正文回貼時(shí)間點(diǎn)擊數(shù)版塊版塊名稱版主本版格言點(diǎn)擊率發(fā)貼數(shù)標(biāo)識每個(gè)實(shí)體的屬性（Attribute）10設(shè)計(jì)數(shù)據(jù)庫的步驟4-4標(biāo)識對象之間的關(guān)系（Relationship）跟貼和主貼有主從關(guān)系：我們需要在跟貼對象中表明它是誰的跟貼；版塊和用戶有關(guān)系：從用戶對象中可以根據(jù)版塊對象查出對應(yīng)的版主用戶的情況；主貼和版塊有主從關(guān)系：需要表明發(fā)貼是屬于哪個(gè)版塊的；跟貼和版塊有主從關(guān)系：需要表明跟貼是屬于哪個(gè)版塊的；數(shù)據(jù)建模的概念將現(xiàn)實(shí)世界的數(shù)據(jù)轉(zhuǎn)換成信息世界的數(shù)據(jù)的過程稱為建模數(shù)據(jù)建模步驟商業(yè)信息需求可操作的數(shù)據(jù)庫外模式概念模式內(nèi)模式商業(yè)視圖系統(tǒng)視圖建立外模式外模式是數(shù)據(jù)庫用戶能夠看見和使用的局部數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述是數(shù)據(jù)庫用戶的數(shù)據(jù)視圖是與某一應(yīng)用有關(guān)的數(shù)據(jù)的邏輯表現(xiàn)不依賴于數(shù)據(jù)庫的邏輯結(jié)構(gòu)，外模式是與用戶有關(guān)的數(shù)據(jù)模型建立概念模型1-2概念模式是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述，是所有用戶的公共數(shù)據(jù)視圖。概念模式是整個(gè)組織的數(shù)據(jù)視圖，表示數(shù)據(jù)庫中的全部數(shù)據(jù)內(nèi)容，因此一個(gè)數(shù)據(jù)庫只有一個(gè)概念模式。建立概念模型2-2概念模式由若干個(gè)概念記錄類型組成，它不僅要描述概念記錄類型，還要描述記錄之間的聯(lián)系、所允許的操作、數(shù)據(jù)的完整性、安全性和其它數(shù)據(jù)控制方面的要求。概念模型不涉及到存儲結(jié)構(gòu)、訪問技術(shù)等細(xì)節(jié)。設(shè)計(jì)概念模型的方法有多種，例如實(shí)體關(guān)系模型、對象模型等。建立內(nèi)模型內(nèi)模式是數(shù)據(jù)物理結(jié)構(gòu)和存儲方式的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表示方法。一個(gè)數(shù)據(jù)庫只有一個(gè)內(nèi)模式。內(nèi)模式定義了所有內(nèi)部記錄類型、索引和文件的組織形式，以及數(shù)據(jù)控制方面的細(xì)節(jié)。使用DBMS設(shè)計(jì)數(shù)據(jù)庫體系結(jié)構(gòu)概念模型設(shè)計(jì)－實(shí)體關(guān)系模型實(shí)體關(guān)系模型：DB設(shè)計(jì)過程，并且表示DB的整個(gè)邏輯結(jié)構(gòu)實(shí)體：實(shí)體可以是具體的（例如一個(gè)人或一本書），也可以是抽象的（如一個(gè)節(jié)日或一個(gè)概念）屬性：實(shí)體是由一組屬性來表示的。例如：Person（個(gè)人）實(shí)體的屬性有Name（名稱）、SSN、Age（年齡）、Street（街道）、City（城市）關(guān)系：關(guān)系是兩個(gè)或多個(gè)實(shí)體之間的聯(lián)系19繪制E-R圖4-1E-R（Entity－Relationship）實(shí)體關(guān)系圖符合含義實(shí)體，一般是名詞屬性，一般是名詞關(guān)系，一般是動詞20繪制E-R圖4-2管理bbsUser（用戶，版主）……出生日期昵稱版塊名稱版主……bbsSection（版塊）21繪制E-R圖4-3映射基數(shù)一對一XXXXYYYYXXXXYYYY一對多XXXXY

YY多對一XXXXYYYY多對多客戶訂單產(chǎn)品

1

N

M

N22繪制E-R圖1111M1MMMM用戶積分性別用戶等級備注信息注冊日期版塊名稱本版留言發(fā)貼數(shù)狀態(tài)密碼昵稱電子郵件生日論壇用戶（BBSUser）管理發(fā)表發(fā)表跟隨屬于屬于點(diǎn)擊率版主標(biāo)題發(fā)貼人貼子編號正文點(diǎn)擊率版塊（BBSSection）發(fā)貼（BBSTopic）發(fā)貼人正文狀態(tài)貼子編號所在版塊最后回復(fù)時(shí)間發(fā)貼表情回復(fù)數(shù)量點(diǎn)擊率發(fā)貼時(shí)間標(biāo)題M跟貼（BBSReply）所在版塊發(fā)貼時(shí)間最后回復(fù)時(shí)間發(fā)貼表情1論壇E-R圖

23如何將E-R圖轉(zhuǎn)換為表3-1將各實(shí)體轉(zhuǎn)換為對應(yīng)的表，將各屬性轉(zhuǎn)換為各表對應(yīng)的列標(biāo)識每個(gè)表的主鍵列，需要注意的是：沒有主鍵的表添加ID編號列，它沒有實(shí)際含義，用于做主鍵或外鍵，例如用戶表中的“UID”列，版塊表中添加“SID”列，發(fā)貼表和跟貼表中的“TID”列在表之間建立主外鍵，體現(xiàn)實(shí)體之間的映射關(guān)系24如何將E-R圖轉(zhuǎn)換為表3-2UID主鍵TID主鍵RID主鍵SID主鍵25如何將E-R圖轉(zhuǎn)換為表添加各表之間的關(guān)系26數(shù)據(jù)規(guī)范化僅有好的RDBMS并不足以避免數(shù)據(jù)冗余，必須在數(shù)據(jù)庫的設(shè)計(jì)中創(chuàng)建好的表結(jié)構(gòu)DrE.F.codd最初定義了規(guī)范化的三個(gè)級別，范式是具有最小冗余的表結(jié)構(gòu)。這些范式是：第一范式(1stNF－FirstNormalFromate)第二范式(2ndNF－SecondNormalFromate)第三范式(3rdNF－ThirdNormalFromate)27第一范式(1stNF)BuyerIDCountryCity1142中國中國日本美國北京北京東京紐約………BuyerIDAddress1234中國北京市

美國紐約市英國利物浦日本東京市…

…第一范式的目標(biāo)是確保每列的原子性如果每列都是不可再分的最小數(shù)據(jù)單元（也稱為最小的原子單元），則滿足第一范式（1NF）28第二范式(2ndNF)如果一個(gè)關(guān)系滿足1NF，并且除了主鍵以外的其他列，都依賴與該主鍵，則滿足第二范式（2NF）第二范式要求每個(gè)表只描述一件事情Orders字段例子訂單編號產(chǎn)品編號訂購日期價(jià)格001A0012000-2-3$29.00…

…Orders字段例子訂單編號訂購日期0012000-2-3Products字段例子產(chǎn)品編號價(jià)格A001$29.0029第三范式(3rdNF)如果一個(gè)關(guān)系滿足2NF，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式（3NF）Orders字段例子訂單編號訂購日期顧客編號0012000-2-3AB001顧客姓名Tony…

…Orders字段例子訂單編號訂購日期顧客編號0012000-2-3AB001…

…30規(guī)范化實(shí)例5-1假設(shè)某建筑公司要設(shè)計(jì)一個(gè)數(shù)據(jù)庫。公司的業(yè)務(wù)規(guī)則概括說明如下：公司承擔(dān)多個(gè)工程項(xiàng)目，每一項(xiàng)工程有：工程號、工程名稱、施工人員等公司有多名職工，每一名職工有：職工號、姓名、性別、職務(wù)（工程師、技術(shù)員）等公司按照工時(shí)和小時(shí)工資率支付工資，小時(shí)工資率由職工的職務(wù)決定（例如，技術(shù)員的小時(shí)工資率與工程師不同）公司定期制定一個(gè)工資報(bào)表，如圖-1所示31規(guī)范化實(shí)例5-2工程號工程名稱職工號姓名職務(wù)小時(shí)工資率工時(shí)實(shí)發(fā)工資A1花園大廈1001齊光明工程師6513845.001002李思岐技術(shù)員6016960.001004葛宇宏律師60191140.00小計(jì)2945.00A2立交橋1001齊光明工程師6515975.001003鞠明亮工人5517935.00小計(jì)1910.00A3臨江飯店1002李思岐技術(shù)員60181080.001004葛宇洪技術(shù)員6014840.00小計(jì)1920.00圖-1某公司的工資表32規(guī)范化實(shí)例5-3工程號工程名稱職工號姓名職務(wù)小時(shí)工資率工時(shí)A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術(shù)員6016A1花園大廈1001齊光明工程師6513A1花園大廈1003鞠明亮工人5517A3臨江飯店1002李思岐技術(shù)員6018A3臨江飯店1004葛宇洪技術(shù)員6014圖-2某公司的項(xiàng)目工時(shí)表33規(guī)范化實(shí)例5-41.表中包含大量的冗余，可能會導(dǎo)致數(shù)據(jù)異常：更新異常例如，修改職工號=1001的職務(wù)，則必須修改所有職工號=1001的行添加異常若要增加一個(gè)新的職工時(shí)，首先必須給這名職工分配一個(gè)工程?；蛘邽榱颂砑右幻侣毠さ臄?shù)據(jù)，先給這名職工分配一個(gè)虛擬的工程。（因?yàn)橹麝P(guān)鍵字不能為空）刪除異常例如，1001號職工要辭職，則必須刪除所有職工號＝1001的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有用的數(shù)據(jù)34規(guī)范化實(shí)例5-52．采用這種方法設(shè)計(jì)表的結(jié)構(gòu)，雖然很容易產(chǎn)生工資報(bào)表，但是每當(dāng)一名職工分配一個(gè)工程時(shí)，都要重復(fù)輸入大量的數(shù)據(jù)。這種重復(fù)的輸入操作，很可能導(dǎo)致數(shù)據(jù)的不一致性。35一張表描述了多件事情，如圖-3所示。應(yīng)用范式規(guī)范化設(shè)計(jì)工程號工程名稱職工號姓名職務(wù)小時(shí)工資率工時(shí)圖-3函數(shù)依賴圖工程信息員工信息項(xiàng)目工時(shí)信息36應(yīng)用第二范式規(guī)范化工程號工程名稱職工號姓名職務(wù)小時(shí)工資率工程號職工號工時(shí)圖-4

應(yīng)用第二范式工程表員工表項(xiàng)目工時(shí)表滿足第三范式嗎？

37應(yīng)用第三范式規(guī)范化工程號工程名稱職工號姓名職務(wù)職務(wù)小時(shí)工資率工程號職工號工時(shí)工程表員工表職務(wù)表工程表38規(guī)范化和性能的關(guān)系為滿足某種商業(yè)目標(biāo)，數(shù)據(jù)庫性能比規(guī)范化數(shù)據(jù)庫更重要通過在給定的表中添加額外的字段，以大量減少需要從中搜索信息所需