TP7數(shù)據(jù)庫(kù)設(shè)計(jì)

1、數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)設(shè)計(jì)Contents熟悉數(shù)據(jù)庫(kù)設(shè)計(jì)流程1理解數(shù)據(jù)庫(kù)設(shè)計(jì)三大范式2三大范式的應(yīng)用34為什么需要設(shè)計(jì)數(shù)據(jù)庫(kù) 2-1 設(shè)計(jì)上有差異嗎？結(jié)論：當(dāng)數(shù)據(jù)庫(kù)比較復(fù)雜時(shí)我們需要設(shè)計(jì)數(shù)據(jù)庫(kù)4為什么需要設(shè)計(jì)數(shù)據(jù)庫(kù) 2-2良好的數(shù)據(jù)庫(kù)設(shè)計(jì)：q 節(jié)省數(shù)據(jù)的存儲(chǔ)空間q 能夠保證數(shù)據(jù)的完整性q 方便進(jìn)行數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)糟糕的數(shù)據(jù)庫(kù)設(shè)計(jì)：q 數(shù)據(jù)冗余、存儲(chǔ)空間浪費(fèi)q 內(nèi)存空間浪費(fèi)q 數(shù)據(jù)更新和插入的異常5軟件項(xiàng)目開(kāi)發(fā)周期 需求分析階段需求分析階段：分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求：分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求; 概要設(shè)計(jì)階段概要設(shè)計(jì)階段：設(shè)計(jì)數(shù)據(jù)庫(kù)的：設(shè)計(jì)數(shù)據(jù)庫(kù)的E-R模型圖，確認(rèn)需求模型圖，確認(rèn)需求信信息

2、的息的正確和完整正確和完整; 詳細(xì)設(shè)計(jì)階段詳細(xì)設(shè)計(jì)階段：將：將E-R圖轉(zhuǎn)換為多張表，進(jìn)行圖轉(zhuǎn)換為多張表，進(jìn)行邏輯設(shè)計(jì)邏輯設(shè)計(jì)并并應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式進(jìn)行審核應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式進(jìn)行審核; 代碼編寫(xiě)階段：選擇具體數(shù)據(jù)庫(kù)進(jìn)行物理實(shí)現(xiàn)，并編寫(xiě)代碼編寫(xiě)階段：選擇具體數(shù)據(jù)庫(kù)進(jìn)行物理實(shí)現(xiàn)，并編寫(xiě) 代碼代碼實(shí)現(xiàn)前端應(yīng)用實(shí)現(xiàn)前端應(yīng)用; 軟件測(cè)試階段：軟件測(cè)試階段： 安裝部署：安裝部署：現(xiàn)實(shí)世界建模信息世界數(shù)據(jù)庫(kù)世界模型轉(zhuǎn)換 規(guī)范化6設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟4-1 收集信息收集信息： 與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理解數(shù)據(jù)庫(kù)需要與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理解數(shù)據(jù)庫(kù)需要完成的任務(wù)完成的任務(wù)BBS論

3、壇的基本功能：l用戶注冊(cè)和登錄，后臺(tái)數(shù)據(jù)庫(kù)需要存放用戶的注冊(cè)信息和在線狀態(tài)信息；l用戶發(fā)貼，后臺(tái)數(shù)庫(kù)需要存放貼子相關(guān)信息，如貼子內(nèi)容、標(biāo)題等；l論壇版塊管理：后臺(tái)數(shù)據(jù)庫(kù)需要存放各個(gè)版塊信息，如版主、版塊名稱、貼子數(shù)等；7設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟4-2 標(biāo)識(shí)對(duì)象標(biāo)識(shí)對(duì)象（實(shí)體（實(shí)體Entity） 標(biāo)識(shí)數(shù)據(jù)庫(kù)要管理的關(guān)鍵對(duì)象或?qū)嶓w標(biāo)識(shí)數(shù)據(jù)庫(kù)要管理的關(guān)鍵對(duì)象或?qū)嶓w 實(shí)體一般是名詞：l用戶：論壇普通用戶、各版塊的版主。l用戶發(fā)的主貼l用戶發(fā)的跟貼（回貼）l版塊：論壇的各個(gè)版塊信息8設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟4-3論壇用戶：l呢稱l密碼l電子郵件l生日l(shuí)性別l用戶的等級(jí)l備注信息l注冊(cè)日期l狀態(tài)l積分主貼l發(fā)貼人l發(fā)貼表

4、情l回復(fù)數(shù)量l標(biāo)題l正文l發(fā)貼時(shí)間l點(diǎn)擊數(shù)l狀態(tài)：l最后回復(fù)時(shí)間回貼l貼子編號(hào)l回貼人,l回貼表情l標(biāo)題l正文l回貼時(shí)間l點(diǎn)擊數(shù)版塊l版塊名稱l版主l本版格言l點(diǎn)擊率l發(fā)貼數(shù)q標(biāo)識(shí)每個(gè)實(shí)體的屬性（Attribute）9設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟4-4 標(biāo)識(shí)對(duì)象之間的關(guān)系標(biāo)識(shí)對(duì)象之間的關(guān)系（Relationship）l跟貼和主貼有主從關(guān)系：我們需要在跟貼對(duì)象中表明它是誰(shuí)的跟貼；l版塊和用戶有關(guān)系：從用戶對(duì)象中可以根據(jù)版塊對(duì)象查出對(duì)應(yīng)的版主用戶的情況；l主貼和版塊有主從關(guān)系：需要表明發(fā)貼是屬于哪個(gè)版塊的；l跟貼和版塊有主從關(guān)系：需要表明跟貼是屬于哪個(gè)版塊的；10繪制E-R圖 4-1 E-R（EntityRe

5、lationship）實(shí)體關(guān)系圖實(shí)體關(guān)系圖 符合符合含義含義實(shí)體，一般是名詞屬性，一般是名詞關(guān)系，一般是動(dòng)詞11繪制E-R圖 4-2管理bbsUser（用戶，版主）出生日期昵稱版塊名稱版主bbsSection（版塊）12繪制E-R圖 4-3 映射基數(shù)映射基數(shù)一對(duì)一X X X X Y Y Y Y X X X X Y Y Y Y 一對(duì)多X X X X Y Y Y 多對(duì)一X X X X Y Y Y Y 多對(duì)多 1 N M N13繪制E-R圖111M1MMM M用戶積分性別用戶等級(jí)備注信息注冊(cè)日期版塊名稱本版留言發(fā)貼數(shù)狀態(tài)密碼昵稱電子郵件生日論壇用戶（BBSUser）管理發(fā)表發(fā)表跟隨屬于屬于點(diǎn)擊率版主

6、標(biāo)題發(fā)貼人貼子編號(hào)正文點(diǎn)擊率版塊（BBSSection）發(fā)貼（BBSTopic）發(fā)貼人正文狀態(tài)貼子編號(hào)所在版塊最后回復(fù)時(shí)間發(fā)貼表情回復(fù)數(shù)量點(diǎn)擊率發(fā)貼時(shí)間標(biāo)題M跟貼（BBSReply）所在版塊發(fā)貼時(shí)間最后回復(fù)時(shí)間發(fā)貼表情1論壇E-R圖 M14如何將E-R圖轉(zhuǎn)換為表 3-1q 將各實(shí)體轉(zhuǎn)換為對(duì)應(yīng)的表，將各屬性轉(zhuǎn)換為各表對(duì)應(yīng)的列q 標(biāo)識(shí)每個(gè)表的主鍵列，需要注意的是：沒(méi)有主鍵的表添加ID編號(hào)列，它沒(méi)有實(shí)際含義，用于做主鍵或外鍵，例如用戶表中的“UID”列，版塊表中添加“SID”列，發(fā)貼表和跟貼表中的“TID”列 q 在表之間建立主外鍵，體現(xiàn)實(shí)體之間的映射關(guān)系 15 如何將E-R圖轉(zhuǎn)換為表 3-2UID

7、主鍵TID主鍵RID主鍵SID主鍵16如何將E-R圖轉(zhuǎn)換為表添加各表之間的關(guān)系17數(shù)據(jù)規(guī)范化 僅有好的僅有好的RDBMS并不足以避免數(shù)據(jù)冗余，必須在數(shù)據(jù)庫(kù)的并不足以避免數(shù)據(jù)冗余，必須在數(shù)據(jù)庫(kù)的設(shè)計(jì)中創(chuàng)建好的表結(jié)構(gòu)設(shè)計(jì)中創(chuàng)建好的表結(jié)構(gòu)Dr E.F.codd 最初定義了規(guī)范化的三個(gè)級(jí)別，范式是具有最初定義了規(guī)范化的三個(gè)級(jí)別，范式是具有最小冗余的表結(jié)構(gòu)。這些范式是：最小冗余的表結(jié)構(gòu)。這些范式是： 第一范式(1st NF First Normal Fromate) 第二范式(2nd NFSecond Normal Fromate) 第三范式(3rd NF Third Normal Fromate)18

8、第一范式 (1st NF)BuyerIDCountryCity1342中國(guó)英國(guó)日本美國(guó)北京倫敦東京紐約BuyerIDAddress1234中國(guó)北京市 美國(guó)紐約市英國(guó)倫敦日本東京市 第一范式的目標(biāo)是確保每列的原子性第一范式的目標(biāo)是確保每列的原子性 如果每列都是不可再分的最小數(shù)據(jù)單元（也稱為如果每列都是不可再分的最小數(shù)據(jù)單元（也稱為最小的原子單元），則滿足第一范式（最小的原子單元），則滿足第一范式（1NF）19第二范式 (2nd NF) 如果一個(gè)關(guān)系滿足如果一個(gè)關(guān)系滿足1NF，并且除了主鍵以外的其他列，都，并且除了主鍵以外的其他列，都依賴與該主鍵，則滿足第二范式（依賴與該主鍵，則滿足第二范式（2N

9、F） 要求每列都和主鍵列相關(guān)要求每列都和主鍵列相關(guān) 第二范式要求每個(gè)表只描述一件事情第二范式要求每個(gè)表只描述一件事情Orders字段例子訂單編號(hào)產(chǎn)品編號(hào)訂購(gòu)日期價(jià) 格001A0012000-2-3$29.00 Orders字段例子訂單編號(hào)訂購(gòu)日期0012000-2-3Products字段例子產(chǎn)品編號(hào)價(jià) 格A001$29.0020第三范式 (3rd NF) 如果一個(gè)關(guān)系滿足如果一個(gè)關(guān)系滿足2NF，并且除了主鍵以外的其他列，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式（都不傳遞依賴于主鍵列，則滿足第三范式（3NF） 第三范式的目標(biāo)確保每個(gè)列和主鍵列直接相關(guān)，而不第三范式的目標(biāo)確保每

10、個(gè)列和主鍵列直接相關(guān)，而不是間接相關(guān)。是間接相關(guān)。 Orders字段例子訂單編號(hào)訂購(gòu)日期顧客編號(hào)0012000-2-3AB001顧客姓名 Tony Orders字段例子訂單編號(hào)訂購(gòu)日期顧客編號(hào)0012000-2-3AB001 21規(guī)范化實(shí)例 5-1假設(shè)某建筑公司要設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)。公司的業(yè)務(wù)規(guī)假設(shè)某建筑公司要設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)。公司的業(yè)務(wù)規(guī)則概括說(shuō)明如下：則概括說(shuō)明如下： 公司承擔(dān)多個(gè)工程項(xiàng)目，每一項(xiàng)工程有：工程號(hào)、工程名公司承擔(dān)多個(gè)工程項(xiàng)目，每一項(xiàng)工程有：工程號(hào)、工程名稱、施工人員等稱、施工人員等 公司有多名職工，每一名職工有：職工號(hào)、姓名、性別、公司有多名職工，每一名職工有：職工號(hào)、姓名、性別、

11、職務(wù)（工程師、技術(shù)員）等職務(wù)（工程師、技術(shù)員）等 公司按照工時(shí)和小時(shí)工資率支付工資，小時(shí)工資率由職工公司按照工時(shí)和小時(shí)工資率支付工資，小時(shí)工資率由職工的職務(wù)決定（例如，技術(shù)員的小時(shí)工資率與工程師不同）的職務(wù)決定（例如，技術(shù)員的小時(shí)工資率與工程師不同） 公司定期制定一個(gè)工資報(bào)表，如圖公司定期制定一個(gè)工資報(bào)表，如圖- -1 1所示所示22規(guī)范化實(shí)例 5-2工程號(hào)工程號(hào)工程名稱工程名稱職工號(hào)職工號(hào)姓名姓名職務(wù)職務(wù)小時(shí)工資率小時(shí)工資率工時(shí)工時(shí)實(shí)發(fā)工資實(shí)發(fā)工資A1花園大廈1001齊光明工程師6513845.001002李思岐技術(shù)員6016960.001004葛宇宏律師60191140.00小計(jì)2945.

12、00A2立交橋1001齊光明工程師6515975.001003鞠明亮工人5517935.00小計(jì)1910.00A3臨江飯店1002李思岐技術(shù)員60181080.001004葛宇洪技術(shù)員6014840.00小計(jì)1920.00圖-1 某公司的工資表23規(guī)范化實(shí)例 5-3工程號(hào)工程號(hào)工程名稱工程名稱職工號(hào)職工號(hào)姓名姓名職務(wù)職務(wù)小時(shí)工資率小時(shí)工資率工時(shí)工時(shí)A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術(shù)員6016A1花園大廈1001齊光明工程師6513A1花園大廈1003鞠明亮工人5517A3臨江飯店1002李思岐技術(shù)員6018A3臨江飯店1004葛宇洪技術(shù)員6014圖-2 某公

13、司的項(xiàng)目工時(shí)表24規(guī)范化實(shí)例 5-41.表中包含大量的冗余，可能會(huì)導(dǎo)致數(shù)據(jù)異常：表中包含大量的冗余，可能會(huì)導(dǎo)致數(shù)據(jù)異常： 更新異常更新異常 例如，修改職工號(hào)例如，修改職工號(hào)=1001的職務(wù)，則必須修改所有職的職務(wù)，則必須修改所有職工號(hào)工號(hào)=1001的行的行 添加異常添加異常 若要增加一個(gè)新的職工時(shí)，首先必須給這名職工分配一若要增加一個(gè)新的職工時(shí)，首先必須給這名職工分配一個(gè)工程。或者為了添加一名新職工的數(shù)據(jù)，先給這名職個(gè)工程?；蛘邽榱颂砑右幻侣毠さ臄?shù)據(jù)，先給這名職工分配一個(gè)虛擬的工程。（因?yàn)橹麝P(guān)鍵字不能為空）工分配一個(gè)虛擬的工程。（因?yàn)橹麝P(guān)鍵字不能為空） 刪除異常刪除異常 例如，例如，1001

14、號(hào)職工要辭職，則必須刪除所有職工號(hào)號(hào)職工要辭職，則必須刪除所有職工號(hào)1001的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有用的數(shù)據(jù)用的數(shù)據(jù)25規(guī)范化實(shí)例 5-52采用這種方法設(shè)計(jì)表的結(jié)構(gòu)，雖然很容采用這種方法設(shè)計(jì)表的結(jié)構(gòu)，雖然很容易產(chǎn)生工資報(bào)表，但是每當(dāng)一名職工分配易產(chǎn)生工資報(bào)表，但是每當(dāng)一名職工分配一個(gè)工程時(shí)，都要重復(fù)輸入大量的數(shù)據(jù)。一個(gè)工程時(shí)，都要重復(fù)輸入大量的數(shù)據(jù)。這種重復(fù)的輸入操作，很可能導(dǎo)致數(shù)據(jù)的這種重復(fù)的輸入操作，很可能導(dǎo)致數(shù)據(jù)的不一致性。不一致性。26一張表描述了多件事情，如圖-3所示。應(yīng)用范式規(guī)范化設(shè)計(jì)工程號(hào)工程名稱職工號(hào)姓名職務(wù)小時(shí)工

15、資率工時(shí)圖-3 函數(shù)依賴圖工程信息員工信息項(xiàng)目工時(shí)信息27應(yīng)用第二范式規(guī)范化工程號(hào)工程名稱職工號(hào)姓名職務(wù)小時(shí)工資率工程號(hào)職工號(hào)工時(shí)圖-4 應(yīng)用第二范式工程表員工表項(xiàng)目工時(shí)表滿足第三范式嗎？ 28應(yīng)用第三范式規(guī)范化工程號(hào)工程名稱職工號(hào)姓名職務(wù)職務(wù)小時(shí)工資率工程號(hào)職工號(hào)工時(shí)工程表員工表職務(wù)表工程表規(guī)范化與性能 規(guī)范不是必須的。規(guī)范不是必須的。 進(jìn)行規(guī)范化的同時(shí)，還需要綜合考慮數(shù)據(jù)庫(kù)的性進(jìn)行規(guī)范化的同時(shí)，還需要綜合考慮數(shù)據(jù)庫(kù)的性能能。 通過(guò)在給定的表中添加冗余的字段，以避免查詢過(guò)程中，多表連接造成的時(shí)間損耗。30總結(jié) 2-1 在需求分析階段，設(shè)計(jì)數(shù)據(jù)庫(kù)的一般步驟為：在需求分析階段，設(shè)計(jì)數(shù)據(jù)庫(kù)的一般步驟為：收集信息標(biāo)識(shí)對(duì)象標(biāo)識(shí)每個(gè)對(duì)象的屬性標(biāo)識(shí)對(duì)象之間的關(guān)系 在概要設(shè)