1、數據庫的設計數據庫的設計SQLSQL數據庫設計與實現數據庫設計與實現第第一一章章2課程內容回顧課程內容回顧數據庫有哪些基本操作？建庫建表加約束創建登錄帳戶 基本的數據操縱語句有哪些？語法是？增（INSERT）刪（DELETE）改（UPDATE）查（SELECT）常用的聚合函數有哪些？表連接分為哪幾種類型？3課程目標課程目標了解設計數據庫的基本步驟熟練使用T-SQL實現建庫、建表、加約束掌握T-SQL編程，實現功能強大的查詢掌握創建索引、視圖，快速訪問數據庫 掌握創建存儲過程，實現復雜的業務規則4本章目標本章目標了解設計數據庫的步驟掌握如何繪制數據庫的E-R圖理解數據庫的規范化三大范式5為什么需

2、要設計為什么需要設計數據庫數據庫 2-1修建茅屋需要設計嗎？修建大廈需要設計嗎？結論：當數據庫比較復雜時我們需要設計數據庫6大家看看這張表有什么問題?學號姓名地址年齡課程號課程名學分1001阿信長沙28001C51002張韶涵邵陽25002C#51003成龍南門口22001C41004孫燕姿桐梓坡24003SQL51005陳佩斯馬王堆26002C#51006馮鞏新開鋪28003SQL51007蕭峰湘潭21006Java51008鐘躍民北京25002C#51 存在數據冗余2添加異常3更新異常4刪除異常為什么需要設計為什么需要設計數據庫數據庫 2-27為什么需要設計為什么需要設計數據庫數據庫 2-

3、3良好的數據庫設計：q 節省數據的存儲空間q 能夠保證數據的完整性q 方便進行數據庫應用系統的開發糟糕的數據庫設計：q 數據冗余、存儲空間浪費q 內存空間浪費q 數據更新和插入的異常8軟件項目開發周期軟件項目開發周期需求分析階段：分析客戶的業務和數據處理需求;概要設計階段：設計數據庫的E-R模型圖，確認需求信息 的正確和完整;詳細設計階段：將E-R圖轉換為多張表，進行邏輯設計， 并應用數據庫設計的三大范式進行審核;代碼編寫階段：選擇具體數據庫進行物理實現，并編寫 代碼實現前端應用;軟件測試階段：安裝部署：現實世界建模信息世界數據庫世界模型轉換 規范化9設計數據庫的步驟設計數據庫的步驟4-1收集

4、信息： 與該系統有關人員進行交流、坐談，充分理解數據庫需要完成的任務BBS論壇的基本功能：l用戶注冊和登錄，后臺數據庫需要存放用戶的注冊信息和在線狀態信息；l用戶發貼，后臺數據庫需要存放貼子相關信息，如貼子內容、標題等；l論壇版塊管理：后臺數據庫需要存放各個版塊信息，如版主、版塊名稱、貼子數等；10設計數據庫的步驟設計數據庫的步驟4-2標識對象（實體Entity） 標識數據庫要管理的關鍵對象或實體 實體一般是名詞：l用戶：論壇普通用戶、各版塊的版主。l用戶發的主貼l用戶發的跟貼（回貼）l版塊：論壇的各個版塊信息11設計數據庫的步驟設計數據庫的步驟4-3論壇用戶：l呢稱l密碼l電子郵件l生日l性

5、別l用戶的等級l備注信息l注冊日期l狀態l積分主貼l發貼人l發貼表情l回復數量l標題l正文l發貼時間l點擊數l狀態：l最后回復時間回貼l貼子編號l回貼人,l回貼表情l標題l正文l回貼時間l點擊數版塊l版塊名稱l版主l本版格言l點擊率l發貼數標識每個實體的屬性（Attribute）12設計數據庫的步驟設計數據庫的步驟4-4標識對象之間的關系（Relationship）l跟貼和主貼有主從關系：我們需要在跟貼對象中表明它是誰的跟貼；l版塊和用戶有關系：從用戶對象中可以根據版塊對象查出對應的版主用戶的情況；l主貼和版塊有主從關系：需要表明發貼是屬于哪個版塊的；l跟貼和版塊有主從關系：需要表明跟貼是屬于

6、哪個版塊的；13繪制繪制E-R圖圖 4-1E-R（EntityRelationship）實體關系圖 符號符號含義含義實體，一般是名詞屬性，一般是名詞關系，一般是動詞14繪制繪制E-R圖圖 4-2管理管理bbsUser（用戶，版主）出生日期昵稱版塊名稱版主bbsSection（版塊）15繪制繪制E-R圖圖 4-3映射基數一對一X X X X Y Y Y Y X X X X Y Y Y Y 一對多X X X X Y Y Y 多對一X X X X Y Y Y Y 多對多 1 N M N16繪制繪制E-R圖圖1111M1MMM M用戶積分性別用戶等級備注信息注冊日期版塊名稱本版留言發貼數狀態密碼昵稱電

7、子郵件生日論壇用戶（BBSUser）管理發表發表跟隨屬于屬于點擊率版主標題發貼人貼子編號正文點擊率版塊（BBSSection）發貼（BBSTopic）發貼人正文狀態貼子編號所在版塊最后回復時間發貼表情回復數量點擊率發貼時間標題M跟貼（BBSReply）所在版塊發貼時間最后回復時間發貼表情1論壇E-R圖 17如何將如何將E-R圖轉換為表圖轉換為表 3-1將各實體轉換為對應的表，將各屬性轉換為各表對應的列標識每個表的主鍵列，需要注意的是：沒有主鍵的表添加ID編號列，它沒有實際含義，用于做主鍵或外鍵，例如用戶表中的“UID”列，版塊表中添加“SID”列，發貼表和跟貼表中的“TID”列 在表之間建立主

8、外鍵，體現實體之間的映射關系 18 如何將如何將E-R圖轉換為表圖轉換為表 3-2UID主鍵TID主鍵RID主鍵SID主鍵19數據規范化數據規范化 僅有好的RDBMS并不足以避免數據冗余，必須在數據庫的設計中創建好的表結構Dr E.F.codd 最初定義了規范化的三個級別，范式是具有最小冗余的表結構。這些范式是：第一范式(1st NF First Normal Fromate)第二范式(2nd NFSecond Normal Fromate)第三范式(3rd NF Third Normal Fromate)20先理解幾個概念先理解幾個概念部分函數依賴傳遞函數依賴21部分函數依賴部分函數依賴學號

9、姓名課程號年級學分復合主鍵復合主鍵學分僅依賴課程號學分僅依賴課程號22傳遞函數依賴傳遞函數依賴學號姓名系號系地址系主任主鍵主鍵系地址和系主任僅依賴系號系地址和系主任僅依賴系號XYZ系號依賴學號系號依賴學號23第一范式第一范式 (1st NF)BuyerIDCountryCity1142中國中國日本美國北京北京東京紐約BuyerIDAddress1234中國北京市 美國紐約市英國利物浦日本東京市 第一范式的目標是確保每列的原子性如果每列都是不可再分的最小數據單元（也稱為最小的原子單元），則滿足第一范式（1NF）24第二范式第二范式 (2nd NF)如果一個關系滿足1NF，并且除了主鍵以外的其他列

10、，都依賴與該主鍵，則滿足第二范式（2NF） 第二范式要求每個表只描述一件事情Orders字段字段例子例子訂單編號產品編號訂購日期價 格001A0012000-2-3$29.00 Orders字段字段例子例子訂單編號訂購日期0012000-2-3Products字段字段例子例子產品編號價 格A001$29.0025第三范式第三范式 (3rd NF)如果一個關系滿足2NF，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式（3NF） Orders字段字段例子例子訂單編號訂購日期顧客編號0012000-2-3AB001顧客姓名TonyTony Orders字段字段例子例子訂單編號訂購日期顧

11、客編號0012000-2-3AB001 26規范化實例規范化實例 5-1假設某建筑公司要設計一個數據庫。公司的業務規則概括說明如下：公司承擔多個工程項目，每一項工程有：工程號、工程名稱、施工人員等公司有多名職工，每一名職工有：職工號、姓名、性別、職務（工程師、技術員）等公司按照工時和小時工資率支付工資，小時工資率由職工的職務決定（例如，技術員的小時工資率與工程師不同）公司定期制定一個工資報表，如圖-1所示27規范化實例規范化實例 5-2工程號工程號工程名稱工程名稱職工號職工號姓名姓名職務職務小時工資率小時工資率工時工時實發工資實發工資A1花園大廈1001齊光明工程師6513845.001002

12、李思岐技術員6016960.001004葛宇宏律師60191140.00小計2945.00A2立交橋1001齊光明工程師6515975.001003鞠明亮工人5517935.00小計1910.00A3臨江飯店1002李思岐技術員60181080.001004葛宇洪技術員6014840.00小計1920.00圖-1 某公司的工資表28規范化實例規范化實例 5-3工程號工程號工程名稱工程名稱職工號職工號姓名姓名職務職務小時工資率小時工資率工時工時A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術員6016A1花園大廈1004葛宇洪技術員6013A1花園大廈1003鞠明亮工人55

13、17A3臨江飯店1002李思岐技術員6018A3臨江飯店1004葛宇洪技術員6014圖-2 某公司的項目工時表29規范化實例規范化實例 5-41.表中包含大量的冗余，可能會導致數據異常：更新異常 例如，修改職工號=1001的職務，則必須修改所有職工號=1001的行添加異常 若要增加一個新的職工時，首先必須給這名職工分配一個工程。或者為了添加一名新職工的數據，先給這名職工分配一個虛擬的工程。（因為主關鍵字不能為空）刪除異常 例如，1001號職工要辭職，則必須刪除所有職工號1001的數據行。這樣的刪除操作，很可能丟失了其它有用的數據30規范化實例規范化實例 5-52采用這種方法設計表的結構，雖然很

14、容易產生工資報表，但是每當一名職工分配一個工程時，都要重復輸入大量的數據。這種重復的輸入操作，很可能導致數據的不一致性。31一張表描述了多件事情，如圖-3所示。應用范式規范化設計應用范式規范化設計工程號工程名稱職工號姓名職務小時工資率工時圖圖-3 函數依賴圖函數依賴圖工程信息員工信息項目工時信息32應用第二范式規范化應用第二范式規范化工程號工程名稱職工號姓名職務小時工資率工程號職工號工時圖圖-4 應用第二范式應用第二范式工程表員工表項目工時表滿足第三范式嗎？滿足第三范式嗎？ 33應用第三范式規范化應用第三范式規范化工程號工程名稱職工號姓名職務職務小時工資率工程號職工號工時工程表員工表職務表工程表34規范化和性能的關系規范化和性能的關系 為滿足某種商業目標，數據庫性能比規范化數據庫更重要通過在給定的表中添加額外的字段，以大量減少需要從中搜索信息所需的時間通過在給定的表中插入計算列（如成績總分），以方便查詢進行規范化的同時，還需要綜合考慮數據庫的性能。35總結總結 2-1在