02_關(guān)系型數(shù)據(jù)庫設(shè)計(jì)ppt課件.ppt

1 第2章關(guān)系數(shù)據(jù)庫設(shè)計(jì) 2 要點(diǎn) 本章將從學(xué)生成績管理數(shù)據(jù)庫實(shí)例入手介紹關(guān)系數(shù)據(jù)庫的設(shè)計(jì)方法和相關(guān)理論 1 數(shù)據(jù)庫系統(tǒng)需求分析 2 數(shù)據(jù)庫設(shè)計(jì) 3 數(shù)據(jù)庫的設(shè)計(jì)規(guī)范 4 關(guān)系數(shù)據(jù)庫設(shè)計(jì)示例 5 數(shù)據(jù)庫設(shè)計(jì)小結(jié) 2 1數(shù)據(jù)庫系統(tǒng)需求分析2 1 1系統(tǒng)功能分析2 1 2數(shù)據(jù)庫需求分析 2 2數(shù)據(jù)庫的設(shè)計(jì)2 2 1用戶需求分析2 2 2概念結(jié)構(gòu)設(shè)計(jì)2 2 3邏輯結(jié)構(gòu)設(shè)計(jì)2 2 4物理結(jié)構(gòu)設(shè)計(jì) 2 3數(shù)據(jù)庫的設(shè)計(jì)規(guī)范2 3 1第一范式2 3 2第二范式2 3 3第三范式2 3 4第四范式 2 4關(guān)系數(shù)據(jù)庫設(shè)計(jì)示例2 4 1系統(tǒng)功能分析2 4 2系統(tǒng)需求分析2 4 3數(shù)據(jù)庫功能分析2 4 4數(shù)據(jù)庫設(shè)計(jì) 2 5數(shù)據(jù)庫設(shè)計(jì)小結(jié) 3 2 1數(shù)據(jù)庫系統(tǒng)需求分析 1 數(shù)據(jù)庫系統(tǒng)的系統(tǒng)功能分析數(shù)據(jù)庫設(shè)計(jì)的最初階段 必須對(duì)用戶的需求有十分清楚的了解 針對(duì)未來用戶對(duì)數(shù)據(jù)的要求是什么這一問題 設(shè)計(jì)前必須與用戶進(jìn)行深入的溝通 與有經(jīng)驗(yàn)的設(shè)計(jì)人員交流是十分重要的 2 數(shù)據(jù)庫需求分析數(shù)據(jù)庫的需求分析是建立在整個(gè)系統(tǒng)的需求基礎(chǔ)上的 所以前期的系統(tǒng)需求調(diào)研十分重要 4 2 2數(shù)據(jù)庫設(shè)計(jì) 數(shù)據(jù)庫設(shè)計(jì)的步驟包括用戶需求分析 概念結(jié)構(gòu)設(shè)計(jì) 邏輯結(jié)構(gòu)設(shè)計(jì)和物理結(jié)構(gòu)設(shè)計(jì)四個(gè)階段 用戶需求分析就是對(duì)現(xiàn)實(shí)世界的了解 概念結(jié)構(gòu)設(shè)計(jì)是根據(jù)用戶需求設(shè)計(jì)的數(shù)據(jù)庫模型 也稱它為概念模型 概念模型可用實(shí)體聯(lián)系模型 E R模型 表示 邏輯結(jié)構(gòu)設(shè)計(jì)是將概念模型轉(zhuǎn)換成某種數(shù)據(jù)庫管理系統(tǒng) DBMS 支持的數(shù)據(jù)模型 轉(zhuǎn)換為關(guān)系數(shù)據(jù)庫的二維表 物理結(jié)構(gòu)設(shè)計(jì)是為數(shù)據(jù)模型在設(shè)備上選定合適的存儲(chǔ)結(jié)構(gòu)和存取方法 5 只要將所有的屬性表示為不可分的數(shù)據(jù)項(xiàng) 轉(zhuǎn)化后的關(guān)系即符合第一范式 即下圖所示 第一范式示意圖 2 3數(shù)據(jù)庫的設(shè)計(jì)規(guī)范 1 第一范式 1NF 在一個(gè)關(guān)系中 要滿足關(guān)系模型的基本性質(zhì) 消除重復(fù)字段 且各字段都是不可分的基本數(shù)據(jù)項(xiàng) 如圖 6 2 第二范式 2NF 所謂第二范式 指的是一種關(guān)系不僅滿足第一范式 而且所有非主關(guān)鍵字完全依賴于其主關(guān)鍵字 例 P28表2 5 用符號(hào) 來表示依賴關(guān)系 例如 學(xué)號(hào) 院系 就表示院系依賴于學(xué)號(hào) 課程號(hào) 學(xué)分 就表示學(xué)分依賴于課程號(hào) 選擇題 一個(gè)關(guān)系模式為Y X1 X2 X3 X4 假定該關(guān)系存在如下函數(shù)依賴 X1 X2 X1 X3 X1 X4 則該關(guān)系屬于第二范式 因?yàn)樗嬖谥耆蕾囮P(guān)系 即所有的非主屬性x2 x3 x4都完全依賴于任一個(gè)候選關(guān)鍵字x1 7 帶來問題的原因是 非主屬性 學(xué)分 僅僅依賴于 課程號(hào) 也就是說只是部分依賴于主關(guān)鍵字 學(xué)號(hào) 課程號(hào) 而不是完全依賴 出現(xiàn)冗余等諸多問題 改造成下圖后 為第二范式 8 第二范式 表的關(guān)系不僅滿足第一范式 即最基本不可再分字段 且所有非主關(guān)鍵字完全依賴于其主關(guān)鍵字 主關(guān)鍵字 復(fù)合主關(guān)鍵字 上圖中 除去學(xué)分 和學(xué)號(hào)無關(guān) 卻完全依賴于課程代碼 及綜合成績 其他項(xiàng)派生的 字段后 全部非學(xué)號(hào)字段 均和復(fù)合主關(guān)鍵字段有關(guān)系 或者說 全都完全依賴復(fù)合主關(guān)鍵字 右圖中 課程名稱和學(xué)分是完全依賴于課程代碼這個(gè)主關(guān)鍵字的 所以這兩個(gè)圖所表示的關(guān)系都是屬于第二范式的情況 刪除此兩項(xiàng)內(nèi)容 看我們?cè)谙旅娼o出的數(shù)據(jù)庫表中 將課程專門做為一個(gè)表來進(jìn)行一個(gè)設(shè)計(jì) 就是為保證滿足第二范式的要求 9 3 第三范式 3NF 關(guān)系模型在第二范式的基礎(chǔ)上 如果關(guān)系模式中的所有非主屬性對(duì)任何候選關(guān)鍵字都不存在傳遞依賴 則稱這個(gè)關(guān)系就是第三范式 分解關(guān)系 院系名稱和地址屬于院系編號(hào) 而后者又依賴于學(xué)號(hào) 第三范式必須消除這種傳遞依賴 固分解 看我們?cè)谙旅嫠O(shè)計(jì)的數(shù)據(jù)庫表中 將院系表 專業(yè)表都專門做為一個(gè)表來設(shè)計(jì) 就是為保證滿足第三范式的要求 10 4 第四范式這個(gè)范式也被稱為Boyce Codd范式 在關(guān)系數(shù)據(jù)庫中 除了函數(shù)依賴之外還有多值依賴 聯(lián)接依賴的問題 這就是第四范式所要規(guī)范的問題 如果關(guān)系模式R U F 的所有屬性 包括主屬性和非主屬性 都不傳遞依賴于R的任何候選關(guān)鍵字 那么稱關(guān)系R是屬于BCNF的 如果不符合第四范式的規(guī)則 會(huì)給插入和刪除元組等操作帶來麻煩 示例如下圖 11 學(xué)生和選課表 學(xué)生表 選課表 我們?cè)龠M(jìn)行數(shù)據(jù)表設(shè)計(jì)時(shí) 往往都將學(xué)生基本情況表做為一個(gè)表 將學(xué)生成績表做為單獨(dú)的專門的另一個(gè)表 同理還再設(shè)計(jì)一個(gè)單獨(dú)的 專門的學(xué)生健康表 而這幾張表間的聯(lián)系就是學(xué)號(hào) 12 總結(jié)范式 第一范式 每個(gè)屬性都是不可再分的 即 每個(gè)屬性必須包含可能的最小數(shù)據(jù)元素 第二范式 關(guān)系首先滿足第一范式 所有的其他屬性還必須完全依賴于主鍵 而不僅僅是只依賴組合主鍵的一部分 第三范式 關(guān)系必須滿足第一范式和第二范式 所有的其他屬性之間必須相互獨(dú)立 但都和組合主屬性有關(guān) 但可以是和組合主屬性其中的部分有關(guān) 取消任何和主屬性無關(guān)的屬性 第四范式 一定不能存在有依賴于非主屬性的屬性 即使這個(gè)被依賴的非主屬性是依賴于主屬性的 即 不允許有傳遞依賴 聯(lián)接依賴存在 結(jié)論 四個(gè)范式要求依次提高 應(yīng)滿足的約束條件也依次更加嚴(yán)格 1NF到BCNF的四種范式之間有如下關(guān)系 BCNF包含了3NF 3NF包含了2NF 2NF包含了1NF 13 P44 選擇題 1 一個(gè)關(guān)系模式為Y X1 X2 X3 X4 假定該關(guān)系存在如下函數(shù)依賴 X1 X2 X1 X3 X1 X4 則該關(guān)系屬于第二范式 因?yàn)樗嬖谥耆蕾囮P(guān)系 有一個(gè)學(xué)生關(guān)系 其關(guān)鍵字為學(xué)號(hào) 一個(gè)課程關(guān)系 其關(guān)鍵字為課程號(hào) 另有一個(gè)選修關(guān)系 其關(guān)鍵字為 學(xué)號(hào)和課程號(hào)的組合 則學(xué)號(hào)和課程號(hào)分別為選修關(guān)系的外鍵 包含在任何一個(gè)候選關(guān)鍵字中的屬性稱為主屬性 不包含在任何一個(gè)候選關(guān)鍵字中的屬性稱為非主屬性 4 一個(gè)學(xué)生可以同時(shí)借閱多本圖書 一本圖書只能由一個(gè)學(xué)生借閱 學(xué)生和圖書之間為一對(duì)多的聯(lián)系 5 關(guān)系中的元組和屬性分別對(duì)應(yīng)二維表中的行和列 數(shù)據(jù)庫表設(shè)計(jì) 14 2 4 關(guān)系數(shù)據(jù)庫設(shè)計(jì)示例 P27 建立學(xué)生成績管理系統(tǒng)的主要目的是通過系統(tǒng) 1 系統(tǒng)介紹及應(yīng)有的功能分析 1 進(jìn)行學(xué)生成績錄入 2 修改與管理 3 方便地查詢到各種分析報(bào)告和成績單 4 系統(tǒng)還要考慮對(duì)成績管理嚴(yán)格的權(quán)限分配 有輸入數(shù)據(jù) 錄入成績功能 有編輯 修改數(shù)據(jù)功能 數(shù)據(jù)檢索功能及輸出功能 例如 查詢出個(gè)人分?jǐn)?shù) 整體分?jǐn)?shù)分布 最高 最低分?jǐn)?shù)等情況 保證數(shù)據(jù)的安全性 15 2 系統(tǒng)需求分析 錄入和維護(hù)學(xué)生的各種成績 生成數(shù)據(jù)庫數(shù)據(jù) 對(duì)不及格學(xué)生的處理信息 按照各種方式方便的瀏覽成績 如按科目 按班級(jí) 按院系 按專業(yè)和按個(gè)人等 對(duì)各科考試進(jìn)行統(tǒng)計(jì)分析 例如 進(jìn)行總分 平均分 最高 最低分?jǐn)?shù) 優(yōu)秀率 及格率的統(tǒng)計(jì)分析 能夠輸出各種成績單和統(tǒng)計(jì)報(bào)表 以及成績走勢(shì)圖等 重修成績管理 根據(jù)重考的成績刷新相關(guān)課程的成績 相關(guān)課程的教師和管理員對(duì)成績進(jìn)行維護(hù) 成績的維護(hù)應(yīng)有嚴(yán)格的時(shí)間限制 例如 一定的時(shí)間后 教師不能修改學(xué)生成績 如果必須要修改 只能通過管理員修改 并詳細(xì)記錄修改結(jié)果 修改原因 修改時(shí)間等 16 3 數(shù)據(jù)庫需求分析 因?yàn)橐鰯?shù)據(jù)分析和統(tǒng)計(jì) 所以需要學(xué)生的基本信息 例如學(xué)號(hào) 姓名 性別 班級(jí) 照片 簡歷 專業(yè)和院系等信息 是統(tǒng)計(jì)數(shù)據(jù)的基本信息來源 學(xué)生相關(guān)表 考慮對(duì)數(shù)據(jù)庫的操作需要 設(shè)置課程 院系和專業(yè)數(shù)據(jù)信息 它們分別包括課程代碼及名稱 課程學(xué)分 院系代碼及名稱 電話 以及專業(yè)代碼及名稱 說明 課程 專業(yè) 院系相關(guān)表 還要做成績錄入 為以后的成績分析做好前期數(shù)據(jù)信息的準(zhǔn)備 所以成績要保存在數(shù)據(jù)庫中 數(shù)據(jù)信息應(yīng)包括學(xué)生的學(xué)號(hào) 考試課程代碼和成績屬性 對(duì)成績有約束條件 不得超過100分 成績表 考慮給學(xué)生補(bǔ)考的機(jī)會(huì) 所以需要學(xué)生補(bǔ)考的信息 應(yīng)該包含學(xué)號(hào) 課程代碼和補(bǔ)考成績 補(bǔ)考庫 17 4 數(shù)據(jù)庫設(shè)計(jì) 1 實(shí)體集的設(shè)計(jì) 共需設(shè)計(jì)6個(gè)實(shí)體 或者說表 學(xué)生基本信息實(shí)體 學(xué)生基本信息 實(shí)體具有的屬性學(xué)號(hào) 姓名 性別 籍貫 出生年月 班級(jí) 專業(yè) 院系 還可以增加備注 照片和簡歷 成績實(shí)體 成績 實(shí)體具有學(xué)號(hào) 課程代碼 期中成績 平時(shí)成績 期末成績 綜合成績和學(xué)分等屬性 補(bǔ)考成績實(shí)體 補(bǔ)考 成績實(shí)體具有學(xué)號(hào) 課程代碼 分?jǐn)?shù)和學(xué)分等屬性 專業(yè)實(shí)體 專業(yè) 實(shí)體具有專業(yè)代碼 專業(yè)名稱和專業(yè)介紹屬性 院系實(shí)體 院系 實(shí)體具有院系代碼 名稱等屬性 還可以增加辦公位置 聯(lián)系電話等屬性 課程實(shí)體 課程 實(shí)體具有課程代碼 名稱 學(xué)分等屬性 18 2 建立E R圖實(shí)體和屬性E R圖 一個(gè)實(shí)體具有多個(gè)屬性 實(shí)體和聯(lián)系E R圖 如 學(xué)生通過瀏覽和成績建立聯(lián)系 學(xué)生通過補(bǔ)考和成績建立某種聯(lián)系 屬性 實(shí)體 19 實(shí)體間聯(lián)系學(xué)生與成績之間的關(guān)系 一對(duì)多 一名學(xué)生有幾門考試成績 補(bǔ)考成績和成績間關(guān)系 一對(duì)一 一個(gè)不及格成績對(duì)一個(gè)補(bǔ)考成績 課程與成績間的關(guān)系 一對(duì)多的關(guān)系 一門課程有多個(gè)成績 課程與補(bǔ)考成績間的關(guān)系 一對(duì)多的關(guān)系 一門課程有多個(gè)補(bǔ)考成績 學(xué)生和專業(yè)間的關(guān)系 多對(duì)一的關(guān)系 多名學(xué)生就讀一個(gè)專業(yè) 學(xué)生與院系間的關(guān)系 多對(duì)一的關(guān)系 多名學(xué)生隸屬于一個(gè)學(xué)院或系 20 3 E R模式到二維表的轉(zhuǎn)換 數(shù)據(jù)模式轉(zhuǎn)化為表的結(jié)構(gòu) 21 E R模式到二維表的轉(zhuǎn)換實(shí)例 成績表 22 4 檢查數(shù)據(jù)庫的規(guī)范性 應(yīng)用第一范式檢驗(yàn)學(xué)生成績管理中的表在學(xué)生基本信息關(guān)系中 沒有可再分的屬性 滿足第一范式要求 在其他關(guān)系中 沒有可再分的屬性 應(yīng)用第二范式再檢驗(yàn)學(xué)生成績管理中的表成績關(guān)系 學(xué)號(hào) 課程代碼 平時(shí)成績 期中成績 期末成績 綜合成績 學(xué)分 其中 主鍵為組合關(guān)鍵字 學(xué)號(hào) 課程代碼 學(xué)分不完全依賴于這個(gè)組合主鍵 只依賴于主鍵的部分內(nèi)容 課程代碼 這個(gè)關(guān)系就不符合第二范式 23 使用以上關(guān)系模式至少存在以下幾個(gè)問題 產(chǎn)生數(shù)據(jù)冗余 更新異常 插入異常 刪除異常 解決方法 見P8 將原有關(guān)系分成兩個(gè)關(guān)系模式 分別是成績關(guān)系 學(xué)號(hào) 課程代碼 平時(shí)成績 期中成績 期末成績 綜合成績 和課程關(guān)系 課程代碼 課程名稱 學(xué)分 新的成績關(guān)系和課程關(guān)系之間通過成績中的外碼 外關(guān)鍵字 課程代碼與課程的課程代碼相聯(lián)系 在需要時(shí)進(jìn)行自然聯(lián)接 可以恢復(fù)原有的關(guān)系 補(bǔ)考成績關(guān)系也存在同樣問題 24 改進(jìn)的E R圖 改進(jìn)的關(guān)系表 25 應(yīng)用第三范式檢查學(xué)生成績管理的設(shè)計(jì) 在成績表中主鍵 關(guān)鍵字 為組合關(guān)鍵字 學(xué)號(hào) 課程代碼 平時(shí)成績 期中成績 期末成績依賴于主鍵 而綜合成績不依賴于主鍵 而依賴于平時(shí)成績 期中成績 期末成績 為了滿足第三范式 可以刪除綜合成績屬性 第四范式是屬性的多值依賴范式學(xué)生成績管理中的關(guān)系相對(duì)簡單 不存在多值依賴關(guān)系 不再討論 26 2 5數(shù)據(jù)庫設(shè)計(jì)小結(jié) 主要步驟如下 數(shù)據(jù)庫系統(tǒng)需求分析 數(shù)據(jù)需求分析 設(shè)計(jì)數(shù)據(jù)模式 E R圖 數(shù)據(jù)模式轉(zhuǎn)化為表 對(duì)表和模式進(jìn)行第一范式規(guī)范 檢查是否符合第二范式并修正關(guān)系 用第三范式規(guī)范關(guān)系 如果存在組合多值依賴傳遞關(guān)系 還要規(guī)范其滿足第四范式 27 P44 選擇題 1 一個(gè)關(guān)系模式為Y X1 X2 X3 X4 假定該關(guān)系存在如下函數(shù)依賴 X1 X2 X1 X3 X1 X4 則該關(guān)系屬于第二范式 因?yàn)樗嬖谥耆蕾囮P(guān)系 有一個(gè)學(xué)生關(guān)系 其關(guān)鍵字為學(xué)號(hào) 一個(gè)課程關(guān)系 其關(guān)鍵字為課程號(hào) 另有一個(gè)選修關(guān)系 其關(guān)鍵字為 學(xué)號(hào)和課程號(hào)的組合 則學(xué)號(hào)和課程號(hào)分別為選修關(guān)系的外鍵 包含在任何一個(gè)候選關(guān)鍵字中的屬性稱為主屬性 不包含在任何一個(gè)候選關(guān)鍵字中的屬性稱為非主屬性 4 一個(gè)學(xué)生可以同時(shí)借閱多本圖書 一本圖書只能由一個(gè)學(xué)生借閱 學(xué)生和圖書之間為一對(duì)多的聯(lián)系 5 關(guān)系中的元組和屬性分別對(duì)應(yīng)二