ER設(shè)計(jì)基礎(chǔ)教程-數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)PPT課件

1 數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn) 2 本章目標(biāo) 了解設(shè)計(jì)數(shù)據(jù)庫的步驟掌握如何繪制數(shù)據(jù)庫的E R圖理解數(shù)據(jù)庫的規(guī)范化 三大范式 3 為什么需要設(shè)計(jì)數(shù)據(jù)庫2 1 修建茅屋需要設(shè)計(jì)嗎 修建大廈需要設(shè)計(jì)嗎 當(dāng)數(shù)據(jù)庫比較復(fù)雜時(shí)我們需要設(shè)計(jì)數(shù)據(jù)庫 4 為什么需要設(shè)計(jì)數(shù)據(jù)庫2 2 良好的數(shù)據(jù)庫設(shè)計(jì) 節(jié)省數(shù)據(jù)的存儲(chǔ)空間能夠保證數(shù)據(jù)的完整性方便進(jìn)行數(shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā) 糟糕的數(shù)據(jù)庫設(shè)計(jì) 數(shù)據(jù)冗余 存儲(chǔ)空間浪費(fèi)內(nèi)存空間浪費(fèi)數(shù)據(jù)更新和插入的異常 5 軟件項(xiàng)目開發(fā)周期 現(xiàn)實(shí)世界 需求分析階段 分析客戶的業(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)用 軟件測(cè)試階段 安裝部署階段 6 設(shè)計(jì)數(shù)據(jù)庫的步驟4 1 收集信息與該系統(tǒng)有關(guān)人員進(jìn)行交流 坐談 充分理解數(shù)據(jù)庫需要完成的任務(wù) BBS論壇的基本功能 用戶注冊(cè)和登錄 后臺(tái)數(shù)據(jù)庫需要存放用戶的注冊(cè)信息和在線狀態(tài)信息用戶發(fā)貼 后臺(tái)數(shù)據(jù)庫需要存放貼子相關(guān)信息 如貼子內(nèi)容 標(biāo)題等論壇版塊管理 后臺(tái)數(shù)據(jù)庫需要存放各個(gè)版塊信息 如版主 版塊名稱 貼子數(shù)等 7 設(shè)計(jì)數(shù)據(jù)庫的步驟4 2 標(biāo)識(shí)對(duì)象 實(shí)體 Entity 標(biāo)識(shí)數(shù)據(jù)庫要管理的關(guān)鍵對(duì)象或?qū)嶓w 實(shí)體一般是名詞 用戶 論壇普通用戶 各版塊的版主 用戶發(fā)的主貼用戶發(fā)的跟貼 回貼 版塊 論壇的各個(gè)版塊信息 8 設(shè)計(jì)數(shù)據(jù)庫的步驟4 3 論壇用戶 呢稱密碼電子郵件生日性別用戶的等級(jí)備注信息注冊(cè)日期狀態(tài)積分 主貼發(fā)貼人發(fā)貼表情回復(fù)數(shù)量標(biāo)題正文發(fā)貼時(shí)間點(diǎn)擊數(shù)狀態(tài)最后回復(fù)時(shí)間 回貼貼子編號(hào)回貼人回貼表情標(biāo)題正文回貼時(shí)間點(diǎn)擊數(shù) 版塊版塊名稱版主本版格言點(diǎn)擊率發(fā)貼數(shù) 標(biāo)識(shí)每個(gè)實(shí)體的屬性 Attribute 9 設(shè)計(jì)數(shù)據(jù)庫的步驟4 4 標(biāo)識(shí)對(duì)象之間的關(guān)系 Relationship 跟貼和主貼有主從關(guān)系 我們需要在跟貼對(duì)象中表明它是誰的跟貼版塊和用戶有關(guān)系 可以根據(jù)版塊對(duì)象查出對(duì)應(yīng)的版主用戶的情況主貼和版塊有主從關(guān)系 需要表明發(fā)貼是屬于哪個(gè)版塊的跟貼和版塊有主從關(guān)系 需要表明跟貼是屬于哪個(gè)版塊的 10 繪制E R圖4 1 E R Entity Relationship 實(shí)體關(guān)系圖 11 繪制E R圖4 2 管理 bbsUser 用戶 版主 出生日期 昵稱 版塊名稱 版主 bbsSection 版塊 12 繪制E R圖4 3 映射基數(shù) 一對(duì)一 XXXX YYYY XXXX YYYY 一對(duì)多 XXXX YYY 多對(duì)一 XXXX YYYY 多對(duì)多 13 繪制E R圖 論壇E R圖 14 如何將E R圖轉(zhuǎn)換為表3 1 將各實(shí)體轉(zhuǎn)換為對(duì)應(yīng)的表 將各屬性轉(zhuǎn)換為各表對(duì)應(yīng)的列標(biāo)識(shí)每個(gè)表的主鍵列 需要注意的是 沒有主鍵的表添加ID編號(hào)列 它沒有實(shí)際含義 用于做主鍵或外鍵 例如用戶表中的 UID 列 版塊表中添加 SID 列 發(fā)貼表和跟貼表中的 TID 列在表之間建立主外鍵 體現(xiàn)實(shí)體之間的映射關(guān)系 15 如何將E R圖轉(zhuǎn)換為表3 2 UID主鍵 TID主鍵 RID主鍵 SID主鍵 16 如何將E R圖轉(zhuǎn)換為表 添加各表之間的關(guān)系 17 數(shù)據(jù)規(guī)范化 Normalize 僅有好的RDBMS并不足以避免數(shù)據(jù)冗余 必須在數(shù)據(jù)庫的設(shè)計(jì)中創(chuàng)建好的表結(jié)構(gòu)Dr E F Codd最初定義了規(guī)范化的三個(gè)級(jí)別 范式用于降低表的冗余數(shù)據(jù) 這些范式是 第一范式 1stNF FirstNormalForm 第二范式 2ndNF SecondNormalForm 第三范式 3rdNF ThirdNormalForm 18 第一范式 1stNF 第一范式的目標(biāo)是確保每列的原子性如果每列都是不可再分的最小數(shù)據(jù)單元 也稱為最小的原子單元 則滿足第一范式 1NF 19 第二范式 2ndNF 如果一個(gè)關(guān)系滿足1NF 并且除了主鍵以外的其他列 都依賴與該主鍵 則滿足第二范式 2NF 第二范式要求每個(gè)表只描述一件事情 產(chǎn)品編號(hào) A001 20 第三范式 3rdNF 如果一個(gè)關(guān)系滿足2NF 并且除了主鍵以外的其他列都不傳遞依賴于主鍵列 則滿足第三范式 3NF Orders 字段 例子 訂單編號(hào) 訂購日期 顧客編號(hào) 001 2000 2 3 AB001 21 規(guī)范化實(shí)例5 1 假設(shè)某建筑公司要設(shè)計(jì)一個(gè)數(shù)據(jù)庫 公司的業(yè)務(wù)規(guī)則概括說明如下 公司承擔(dān)多個(gè)工程項(xiàng)目 每一項(xiàng)工程有 工程號(hào) 工程名稱 施工人員等公司有多名職工 每一名職工有 職工號(hào) 姓名 性別 職務(wù) 工程師 技術(shù)員 等公司按照工時(shí)和小時(shí)工資率支付工資 小時(shí)工資率由職工的職務(wù)決定 例如 技術(shù)員的小時(shí)工資率與工程師不同 公司定期制定一個(gè)工資報(bào)表如圖所示 22 規(guī)范化實(shí)例5 2 某公司的工資表 23 規(guī)范化實(shí)例5 3 某公司的項(xiàng)目工時(shí)表 24 規(guī)范化實(shí)例5 4 1 表中包含大量的冗余 可能會(huì)導(dǎo)致數(shù)據(jù)異常 更新異常例如 修改職工號(hào) 1001的職務(wù) 則必須修改所有職工號(hào) 1001的行添加異常若要增加一個(gè)新的職工時(shí) 首先必須給這名職工分配一個(gè)工程 或者為了添加一名新職工的數(shù)據(jù) 先給這名職工分配一個(gè)虛擬的工程 因?yàn)橹麝P(guān)鍵字不能為空 刪除異常例如 1001號(hào)職工要辭職 則必須刪除所有職工號(hào) 1001的數(shù)據(jù)行 這樣的刪除操作 很可能丟失了其它有用的數(shù)據(jù) 25 規(guī)范化實(shí)例5 5 2 采用這種方法設(shè)計(jì)表的結(jié)構(gòu) 雖然很容易產(chǎn)生工資報(bào)表 但是每當(dāng)一名職工分配一個(gè)工程時(shí) 都要重復(fù)輸入大量的數(shù)據(jù) 這種重復(fù)的輸入操作 很可能導(dǎo)致數(shù)據(jù)的不一致性 26 一張表描述了多個(gè)實(shí)體的信息 應(yīng)用范式規(guī)范化設(shè)計(jì) 函數(shù)依賴圖 工程信息 員工信息 項(xiàng)目工時(shí)信息 27 應(yīng)用第二范式規(guī)范化 工程表 員工表 項(xiàng)目工時(shí)表 滿足第三范式嗎 28 應(yīng)用第三范式規(guī)范化 工程表 員工表 職務(wù)表 工程表 29 規(guī)范化和性能的關(guān)系 為滿足某種商業(yè)目標(biāo) 數(shù)據(jù)庫性能比規(guī)范化數(shù)據(jù)庫更重要通過在給定的表中添加額外的字段 以大量減少需要從中搜索信息所需的時(shí)間通過在給定的表中插入計(jì)算列 如成績總分 以方便查詢進(jìn)行規(guī)范化的同時(shí) 還需要綜合考慮數(shù)據(jù)庫的性能 必要的時(shí)候需要較低規(guī)范化的程度 即非規(guī)范化 Denormalize 30 總結(jié)2 1 在需求分析階段 設(shè)計(jì)數(shù)據(jù)庫的一般步驟為 收集信息標(biāo)識(shí)對(duì)象標(biāo)識(shí)每個(gè)對(duì)象的屬性標(biāo)識(shí)對(duì)象之間的關(guān)系在詳細(xì)設(shè)計(jì)階段 設(shè)計(jì)數(shù)據(jù)庫的步驟為 繪制E R圖將E R圖轉(zhuǎn)換為表結(jié)構(gòu)應(yīng)用三大范式規(guī)范化表 31 總結(jié)2 2 為了設(shè)計(jì)結(jié)構(gòu)良好的數(shù)據(jù)庫 需要遵守一些專門的規(guī)則 稱為數(shù)據(jù)庫的設(shè)計(jì)范式第一范式 1NF 的目標(biāo) 確保每列的原子性第二范式 2NF 的目標(biāo) 確保表中的每列 都和主鍵相關(guān)第三范式 3NF 的目標(biāo) 確保每列都和主鍵列直接相關(guān) 而不是間接相關(guān) 32 數(shù)據(jù)庫實(shí)現(xiàn) 33 目標(biāo) 掌握建表的SQL語句掌握加約束的SQL語句掌握授權(quán)的SQL語句 34 回顧表的基礎(chǔ)知識(shí) 建表的基本步驟 確定表中有哪些列確定每列的數(shù)據(jù)類型給表添加各種約束創(chuàng)建各表之間的關(guān)系 35 創(chuàng)建表 建表的語法 CREATETABLE表名 字段1數(shù)據(jù)類型列的特征 字段2數(shù)據(jù)類型列的特征 列的特征 包括該列是是否為空 NULL 是否有默認(rèn)值 是否為主鍵等 36 建表示例 CREATETABLETEST DETAIL IDNUMBER 10 NOTNULL MASTER IDNUMBER 10 SCORENUMBER 3 NULL REG DATEDATEDEFAULTSYSDATENOTNULL 37 刪除表 刪除表的語法 DROPTABLE表名 CASCADECONSTRAINTS DROPTABLETEST MASTERCASCADECONSTRAINTS 38 回顧約束的類型 約束的目的 確保表中數(shù)據(jù)的完整型常用的約束類型 主鍵約束 PrimaryKeyConstraint 要求主鍵列數(shù)據(jù)唯一 并且不允許為空唯一約束 UniqueConstraint 要求該列唯一 允許為空 但只能出現(xiàn)一個(gè)空值 檢查約束 CheckConstraint 某列取值范圍限制 格式限制等 如有關(guān)年齡的約束默認(rèn)約束 DefaultConstraint 某列的默認(rèn)值 如默認(rèn)處理日期為系統(tǒng)日期外鍵約束 ForeignKeyConstraint 用于兩表間建立關(guān)系 需要指定引用主表的列 39 添加約束 添加約束的語法 ALTERTABLE表名ADDCONSTRAINT約束名約束類型具體的約束說明 約束名的取名規(guī)則推薦采用 約束類型 約束字段主鍵 PrimaryKey 約束 如PK stuNo唯一 UniqueKey 約束 如UK stuID檢查 CheckKey 約束 如CK stuAge外鍵 ForeignKey 約束 如FK stuNo 40 添加約束示例 CreateprimarykeyALTERTABLETEST MASTERADDCONSTRAINTPK TEST MASTERPRIMARYKEY ID CreateuniquekeyALTERTABLETEST MASTERADDCONSTRAINTUK TEST MASTERUNIQUE NAME CreatecheckconstraintsALTERTABLETEST MASTERADDCONSTRAINTCK TEST MASTER NAMECHECK LENGTH NAME 2 CreateforeignkeyALTERTABLETEST DETAILADDCONSTRAINTFK TEST DETAIL IDFOREIGNKEY MASTER ID REFERENCESTEST MASTER ID 41 刪除約束 刪除約束的語法 ALTERTABLE表名DROPCONSTRAINT約束名 ALTERTABLEstuInfoDROPCON