數(shù)據(jù)庫設計與實現(xiàn)1本章目標了解設計數(shù)據(jù)庫的步驟掌握如何繪制數(shù)據(jù)庫的E-R圖理解數(shù)據(jù)庫的規(guī)范化－三大范式2為什么需要設計數(shù)據(jù)庫2-1修建茅屋需要設計嗎？修建大廈需要設計嗎？當數(shù)據(jù)庫比較復雜時我們需要設計數(shù)據(jù)庫3為什么需要設計數(shù)據(jù)庫2-2良好的數(shù)據(jù)庫設計：節(jié)省數(shù)據(jù)的存儲空間能夠保證數(shù)據(jù)的完整性方便進行數(shù)據(jù)庫應用系統(tǒng)的開發(fā)糟糕的數(shù)據(jù)庫設計：數(shù)據(jù)冗余、存儲空間浪費內(nèi)存空間浪費數(shù)據(jù)更新和插入的異常4軟件項目開發(fā)周期現(xiàn)實世界建模信息世界數(shù)據(jù)庫世界模型轉(zhuǎn)換規(guī)范化需求分析階段：分析客戶的業(yè)務和數(shù)據(jù)處理需求;概要設計階段：設計數(shù)據(jù)庫的E-R模型圖，確認需求信息的正確和完整;詳細設計階段：將E-R圖轉(zhuǎn)換為多張表，進行邏輯設計，并應用數(shù)據(jù)庫設計的三大范式進行審核;代碼編寫階段：選擇具體數(shù)據(jù)庫進行物理實現(xiàn)，

并編寫代碼實現(xiàn)前端應用;軟件測試階段：……安裝部署階段：……5設計數(shù)據(jù)庫的步驟4-1收集信息

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

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

實體一般是名詞：用戶：論壇普通用戶、各版塊的版主。用戶發(fā)的主貼用戶發(fā)的跟貼（回貼）版塊：論壇的各個版塊信息7設計數(shù)據(jù)庫的步驟4-3論壇用戶：呢稱密碼電子郵件生日性別用戶的等級備注信息注冊日期狀態(tài)積分主貼發(fā)貼人發(fā)貼表情回復數(shù)量標題正文發(fā)貼時間點擊數(shù)狀態(tài)最后回復時間回貼貼子編號回貼人回貼表情標題正文回貼時間點擊數(shù)版塊版塊名稱版主本版格言點擊率發(fā)貼數(shù)標識每個實體的屬性（Attribute）8設計數(shù)據(jù)庫的步驟4-4標識對象之間的關系（Relationship）跟貼和主貼有主從關系：我們需要在跟貼對象中表明它是誰的跟貼版塊和用戶有關系：可以根據(jù)版塊對象查出對應的版主用戶的情況主貼和版塊有主從關系：需要表明發(fā)貼是屬于哪個版塊的跟貼和版塊有主從關系：需要表明跟貼是屬于哪個版塊的9繪制E-R圖4-1E-R（Entity－Relationship）實體關系圖符號含義實體，一般是名詞屬性，一般是名詞關系，一般是動詞10繪制E-R圖4-2管理bbsUser（用戶，版主）……出生日期昵稱版塊名稱版主……bbsSection（版塊）11繪制E-R圖4-3映射基數(shù)一對一XXXXYYYYXXXXYYYY一對多XXXXY

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

13如何將E-R圖轉(zhuǎn)換為表3-1將各實體轉(zhuǎn)換為對應的表，將各屬性轉(zhuǎn)換為各表對應的列標識每個表的主鍵列，需要注意的是：沒有主鍵的表添加ID編號列，它沒有實際含義，用于做主鍵或外鍵，例如用戶表中的“UID”列，版塊表中添加“SID”列，發(fā)貼表和跟貼表中的“TID”列在表之間建立主外鍵，體現(xiàn)實體之間的映射關系14

如何將E-R圖轉(zhuǎn)換為表3-2UID主鍵TID主鍵RID主鍵SID主鍵15如何將E-R圖轉(zhuǎn)換為表添加各表之間的關系16數(shù)據(jù)規(guī)范化(Normalize)

僅有好的RDBMS并不足以避免數(shù)據(jù)冗余，必須在數(shù)據(jù)庫的設計中創(chuàng)建好的表結(jié)構(gòu)Dr.E.F.Codd最初定義了規(guī)范化的三個級別，范式用于降低表的冗余數(shù)據(jù)。這些范式是：第一范式(1stNF－FirstNormalForm)第二范式(2ndNF－SecondNormalForm)第三范式(3rdNF－ThirdNormalForm)17第一范式(1stNF)BuyerIDCountryCity1142中國中國日本美國北京北京東京紐約………BuyerIDAddress1234中國北京市

美國紐約市英國利物浦日本東京市……第一范式的目標是確保每列的原子性如果每列都是不可再分的最小數(shù)據(jù)單元（也稱為最小的原子單元），則滿足第一范式（1NF）18第二范式(2ndNF)如果一個關系滿足1NF，并且除了主鍵以外的其他列，都依賴與該主鍵，則滿足第二范式（2NF）第二范式要求每個表只描述一件事情Orders字段例子訂單編號產(chǎn)品編號訂購日期價格001A0012000-2-3$29.00……Orders字段例子訂單編號訂購日期0012000-2-3Products字段例子產(chǎn)品編號價格A001$29.00產(chǎn)品編號A00119第三范式(3rdNF)如果一個關系滿足2NF，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式（3NF）Orders字段例子訂單編號訂購日期顧客編號0012000-2-3AB001顧客姓名Tony……Orders字段例子訂單編號訂購日期顧客編號0012000-2-3AB001……20規(guī)范化實例5-1假設某建筑公司要設計一個數(shù)據(jù)庫。公司的業(yè)務規(guī)則概括說明如下：公司承擔多個工程項目，每一項工程有：工程號、工程名稱、施工人員等公司有多名職工，每一名職工有：職工號、姓名、性別、職務（工程師、技術(shù)員）等公司按照工時和小時工資率支付工資，小時工資率由職工的職務決定（例如，技術(shù)員的小時工資率與工程師不同）公司定期制定一個工資報表如圖所示21規(guī)范化實例5-2工程號工程名稱職工號姓名職務小時工資率工時實發(fā)工資A1花園大廈1001齊光明工程師6513845.001002李思岐技術(shù)員6016960.001004葛宇宏技術(shù)員60191140.00小計2945.00A2立交橋1001齊光明工程師6515975.001003鞠明亮工人5517935.00小計1910.00A3臨江飯店1002李思岐技術(shù)員60181080.001004葛宇宏技術(shù)員6014840.00小計1920.00某公司的工資表22規(guī)范化實例5-3工程號工程名稱職工號姓名職務小時工資率工時A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術(shù)員6016A1立交橋1001齊光明工程師6513A1立交橋1003鞠明亮工人5517A3臨江飯店1002李思岐技術(shù)員6018A3臨江飯店1004葛宇洪技術(shù)員6014某公司的項目工時表23規(guī)范化實例5-41.表中包含大量的冗余，可能會導致數(shù)據(jù)異常：更新異常例如，修改職工號=1001的職務，則必須修改所有職工號=1001的行添加異常若要增加一個新的職工時，首先必須給這名職工分配一個工程?；蛘邽榱颂砑右幻侣毠さ臄?shù)據(jù)，先給這名職工分配一個虛擬的工程。（因為主關鍵字不能為空）刪除異常例如，1001號職工要辭職，則必須刪除所有職工號＝1001的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有用的數(shù)據(jù)24規(guī)范化實例5-52．采用這種方法設計表的結(jié)構(gòu)，雖然很容易產(chǎn)生工資報表，但是每當一名職工分配一個工程時，都要重復輸入大量的數(shù)據(jù)。這種重復的輸入操作，很可能導致數(shù)據(jù)的不一致性。25一張表描述了多個實體的信息應用范式規(guī)范化設計工程號工程名稱職工號姓名職務小時工資率工時函數(shù)依賴圖工程信息員工信息項目工時信息26應用第二范式規(guī)范化工程號工程名稱職工號姓名職務小時工資率工程號職工號工時工程表員工表項目工時表滿足第三范式嗎？

27應用第三范式規(guī)范化工程號工程名稱職工號姓名職務職務小時工資率工程號職工號工時工程表員工表職務表工程表28規(guī)范化和性能的關系為滿足某種商業(yè)目標，數(shù)據(jù)庫性能比規(guī)范化數(shù)據(jù)庫更重要通過在給定的表中添加額外的字段，以大量減少需要從中搜索信息所需的時間通過在給定的表中插入計算列（如成績總分），以方便查詢進行規(guī)范化的同時，還需要綜合考慮數(shù)據(jù)庫的性能，必要的時候需要較低規(guī)范化的程度，即非規(guī)范化(Denormalize)29總結(jié)2-1在需求分析階段，設計數(shù)據(jù)庫的一般步驟為：收集信息標識對象標識每個對象的屬性標識對象之間的關系在詳細設計階段，設計數(shù)據(jù)庫的步驟為：繪制E-R圖將E-R圖轉(zhuǎn)換為表結(jié)構(gòu)應用三大范式規(guī)范化表30總結(jié)2-2為了設計結(jié)構(gòu)良好的數(shù)據(jù)庫，需要遵守一些專門的規(guī)則，稱為數(shù)據(jù)庫的設計范式第一范式（1NF）的目標：確保每列的原子性第二范式（2NF）的目標：確保表中的每列，都和主鍵相關第三范式（3NF）的目標：確保每列都和主鍵列直接相關，而不是間接相關31數(shù)據(jù)庫實現(xiàn)32目標掌握建表的SQL語句掌握加約束的SQL語句掌握授權(quán)的SQL語句33回顧表的基礎知識建表的基本步驟：確定表中有哪些列確定每列的數(shù)據(jù)類型給表添加各種約束創(chuàng)建各表之間的關系34創(chuàng)建表建表的語法CREATETABLE

表名(

字段1數(shù)據(jù)類型列的特征，字段2數(shù)據(jù)類型列的特征，

...);列的特征：包括該列是是否為空（NULL）、是否有默認值、是否為主鍵等。35建表示例CREATETABLETEST_DETAIL(IDNUMBER(10)NOTNULL,MASTER_IDNUMBER(10),SCORENUMBER(3)NULL,REG_DATEDATEDEFAULTSYSDATENOTNULL);36刪除表刪除表的語法：DROPTABLE表名

[CASCADECONSTRAINTS];DROPTABLETEST_MASTERCASCADECONSTRAINTS;37回顧約束的類型約束的目的：確保表中數(shù)據(jù)的完整型常用的約束類型：主鍵約束（PrimaryKeyConstraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空唯一約束（UniqueConstraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個空值。檢查約束（CheckConstraint）：某列取值范圍限制、格式限制等，如有關年齡的約束默認約束（DefaultConstraint）：某列的默認值，如默認處理日期為系統(tǒng)日期外鍵約束（ForeignKeyConstraint）：用于兩表間建立關系，需要指定引用主表的列38添加約束添加約束的語法：ALTERTABLE表名

ADDCONSTRAINT約束名約束類型具體的約束說明;約束名的取名規(guī)則推薦采用：約束類型_約束字段主鍵（PrimaryKey）約束：如PK_stuNo唯一（UniqueKey）約束：如UK_stuID檢查（CheckKey）約束：如CK_stuAge外鍵（ForeignKey）約束：如FK_stuNo

39添加約束示例--CreateprimarykeyALTERTABLETEST_MASTERADDCONSTRAINTPK_TEST_MASTERPRIMARYKEY(ID);--CreateuniquekeyALTERTABLETEST_MASTERADDCONSTRAINTUK_TEST_MASTERUNIQUE(NAME);