1.SQL Server數(shù)據(jù)庫設(shè)計與實現(xiàn)TP1 ppt課件

1、ACCP V4.0第一章第一章數(shù)據(jù)庫的設(shè)計ACCP V4.0課程地位C#SQL ServerXMLPSPMASP.NETOracleOOADComputer BaseHTMLJavaScriptSQL BaseOOPJavaCSTBJSP/ServletEJB/WebServiceWinFormsStruts/JSFTesting/SQALinuxACCP V4.0課程內(nèi)容回顧q 數(shù)據(jù)庫有哪些基本操作？q 建庫q 建表q 加約束q 創(chuàng)建登錄帳戶 q 基本的數(shù)據(jù)操縱語句有哪些？語法是？q 增INSERT）q 刪DELETE）q 改UPDATE）q 查SELECT）q 常用的聚合函數(shù)有哪些？q 表

2、連接分為哪幾種類型？ACCP V4.0課程目標(biāo)q了解設(shè)計數(shù)據(jù)庫的基本步驟q熟練使用T-SQL實現(xiàn)建庫、建表、加約束q掌握T-SQL編程，實現(xiàn)功能強(qiáng)大的查詢q掌握創(chuàng)建索引、視圖，快速訪問數(shù)據(jù)庫 q掌握創(chuàng)建存儲過程，實現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則q理解觸發(fā)器的原理，實現(xiàn)高級的約束ACCP V4.0本章目標(biāo)q了解設(shè)計數(shù)據(jù)庫的步驟q掌握如何繪制數(shù)據(jù)庫的E-R圖q理解數(shù)據(jù)庫的規(guī)范化三大范式ACCP V4.0為什么需要設(shè)計數(shù)據(jù)庫 2-1修建茅屋需要設(shè)計嗎？修建大廈需要設(shè)計嗎？結(jié)論：當(dāng)數(shù)據(jù)庫比較復(fù)雜時我們需要設(shè)計數(shù)據(jù)庫ACCP V4.0為什么需要設(shè)計數(shù)據(jù)庫 2-2良好的數(shù)據(jù)庫設(shè)計：節(jié)省數(shù)據(jù)的存儲空間能夠保證數(shù)據(jù)的完整性

3、方便進(jìn)行數(shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā)糟糕的數(shù)據(jù)庫設(shè)計：數(shù)據(jù)冗余、存儲空間浪費內(nèi)存空間浪費數(shù)據(jù)更新和插入的異常ACCP V4.0軟件項目開發(fā)周期q 需求分析階段：分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求;q 概要設(shè)計階段：設(shè)計數(shù)據(jù)庫的E-R模型圖，確認(rèn)需求信息q 的正確和完整;q 詳細(xì)設(shè)計階段：將E-R圖轉(zhuǎn)換為多張表，進(jìn)行邏輯設(shè)計，q 并應(yīng)用數(shù)據(jù)庫設(shè)計的三大范式進(jìn)行審核;q 代碼編寫階段：選擇具體數(shù)據(jù)庫進(jìn)行物理實現(xiàn)，并編寫 q 代碼實現(xiàn)前端應(yīng)用;q 軟件測試階段：q 安裝部署：現(xiàn)實世界建模信息世界數(shù)據(jù)庫世界模型轉(zhuǎn)換 規(guī)范化ACCP V4.0設(shè)計數(shù)據(jù)庫的步驟4-1q收集信息：q 與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理

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

5、時間點擊數(shù)形狀：最后回復(fù)時間回貼貼子編號回貼人,回貼表情標(biāo)題正文回貼時間點擊數(shù)版塊版塊名稱版主本版格言點擊率發(fā)貼數(shù)q標(biāo)識每個實體的屬性Attribute）ACCP V4.0設(shè)計數(shù)據(jù)庫的步驟4-4q標(biāo)識對象之間的關(guān)系Relationship）l跟貼和主貼有主從關(guān)系：我們需要在跟貼對象中表明它是誰的跟貼；l版塊和用戶有關(guān)系：從用戶對象中可以根據(jù)版塊對象查出對應(yīng)的版主用戶的情況；l主貼和版塊有主從關(guān)系：需要表明發(fā)貼是屬于哪個版塊的；l跟貼和版塊有主從關(guān)系：需要表明跟貼是屬于哪個版塊的；ACCP V4.0繪制E-R圖 4-1q E-REntityRelationship實體關(guān)系圖實體關(guān)系圖 符合符合含

6、義含義實體，一般是名詞屬性，一般是名詞關(guān)系，一般是動詞ACCP V4.0繪制E-R圖 4-2管理管理bbsUser（用戶，版主）出生日期昵稱版塊名稱版主bbsSection（版塊）ACCP V4.0繪制E-R圖 4-3q 映射基數(shù)一對一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 NACCP V4.0繪制E-R圖1111M1MMM M用戶積分性別用戶等級備注信息注冊日期版塊名稱本版留言發(fā)貼數(shù)形狀密碼昵稱電子郵件生日論壇用戶BBSUser）管理發(fā)表發(fā)表跟隨屬于屬于點擊率版主標(biāo)題發(fā)貼人貼子

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

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

9、國日本美國北京北京東京紐約BuyerIDAddress1234中國北京市 美國紐約市美國紐約市英國利物浦日本東京市 q第一范式的目標(biāo)是確保每列的原子性q如果每列都是不可再分的最小數(shù)據(jù)單元也稱為最小的原子單元），則滿足第一范式1NF）ACCP V4.0第二范式 (2nd NF)q 如果一個關(guān)系滿足1NF，并且除了主鍵以外的其他列，都依賴與該主鍵，則滿足第二范式2NF） q 第二范式要求每個表只描述一件事情Orders字段字段例子例子訂單編號產(chǎn)品編號訂購日期價 格001A0012000-2-3$29.00 Orders字段字段例子例子訂單編號訂購日期0012000-2-3Products字段字段例

10、子例子產(chǎn)品編號價 格A001$29.00ACCP V4.0第三范式 (3rd NF)q 如果一個關(guān)系滿足2NF，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式3NF） Orders字段字段例子例子訂單編號訂購日期顧客編號0012000-2-3AB001顧客姓名TonyTony Orders字段字段例子例子訂單編號訂購日期顧客編號0012000-2-3AB001 ACCP V4.0規(guī)范化實例 5-1假設(shè)某建筑公司要設(shè)計一個數(shù)據(jù)庫。公司的業(yè)務(wù)規(guī)則概括說明如下：公司承擔(dān)多個工程項目，每一項工程有：工程號、工程名稱、施工人員等公司有多名職工，每一名職工有：職工號、姓名、性別、職務(wù)工程師、

11、技術(shù)員等公司按照工時和小時工資率支付工資，小時工資率由職工的職務(wù)決定例如，技術(shù)員的小時工資率與工程師不同）公司定期制定一個工資報表，如圖-1所示ACCP V4.0規(guī)范化實例 5-2工程號工程號工程名稱工程名稱職工號職工號姓名姓名職務(wù)職務(wù)小時工資率小時工資率工時工時實發(fā)工資實發(fā)工資A1花園大廈1001齊光明工程師6513845.001002李思岐技術(shù)員6016960.001004葛宇宏律師60191140.00小計2945.00A2立交橋1001齊光明工程師6515975.001003鞠明亮工人5517935.00小計1910.00A3臨江飯店1002李思岐技術(shù)員60181080.001004葛

12、宇洪技術(shù)員6014840.00小計1920.00圖-1 某公司的工資表ACCP V4.0規(guī)范化實例 5-3工程號工程號工程名稱工程名稱職工號職工號姓名姓名職務(wù)職務(wù)小時工資率小時工資率工時工時A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術(shù)員6016A1花園大廈1001齊光明工程師6513A1花園大廈1003鞠明亮工人5517A3臨江飯店1002李思岐技術(shù)員6018A3臨江飯店1004葛宇洪技術(shù)員6014圖-2 某公司的項目工時表ACCP V4.0規(guī)范化實例 5-41.表中包含大量的冗余，可能會導(dǎo)致數(shù)據(jù)異常：更新異常 例如，修改職工號=1001的職務(wù)，則必須修改所有職工號=

13、1001的行添加異常 若要增加一個新的職工時，首先必須給這名職工分配一個工程?；蛘邽榱颂砑右幻侣毠さ臄?shù)據(jù)，先給這名職工分配一個虛擬的工程。（因為主關(guān)鍵字不能為空）刪除異常 例如，1001號職工要辭職，則必須刪除所有職工號1001的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有用的數(shù)據(jù)ACCP V4.0規(guī)范化實例 5-52采用這種方法設(shè)計表的結(jié)構(gòu)，雖然很容易產(chǎn)生工資報表，但是每當(dāng)一名職工分配一個工程時，都要重復(fù)輸入大量的數(shù)據(jù)。這種重復(fù)的輸入操作，很可能導(dǎo)致數(shù)據(jù)的不一致性。ACCP V4.0一張表描述了多件事情，如圖-3所示。應(yīng)用范式規(guī)范化設(shè)計工程號工程名稱職工號姓名職務(wù)小時工資率工時圖圖-3 -3

14、 函數(shù)依賴圖函數(shù)依賴圖工程信息員工信息項目工時信息ACCP V4.0應(yīng)用第二范式規(guī)范化工程號工程名稱職工號姓名職務(wù)小時工資率工程號職工號工時圖圖-4 -4 應(yīng)用第二范式應(yīng)用第二范式工程表員工表項目工時表滿足第三范式嗎？滿足第三范式嗎？ ACCP V4.0應(yīng)用第三范式規(guī)范化工程號工程名稱職工號姓名職務(wù)職務(wù)小時工資率工程號職工號工時工程表員工表職務(wù)表工程表ACCP V4.0規(guī)范化和性能的關(guān)系 q 為滿足某種商業(yè)目標(biāo)，數(shù)據(jù)庫性能比規(guī)范化數(shù)據(jù)庫更重要q 通過在給定的表中添加額外的字段，以大量減少需要從中搜索信息所需的時間q 通過在給定的表中插入計算列如成績總分），以方便查詢q 進(jìn)行規(guī)范化的同時，還需要綜合考慮數(shù)據(jù)庫的性能。ACCP V4.0總結(jié) 2-1q在需求分析階段，設(shè)計數(shù)據(jù)庫的一般步驟