數(shù)據(jù)庫(kù)設(shè)計(jì)方法與原則

1、數(shù)據(jù)庫(kù)設(shè)計(jì)方法與原則數(shù)據(jù)庫(kù)設(shè)計(jì)方法與原則-周波周波學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)5.4.3.2.1.數(shù)據(jù)庫(kù)設(shè)計(jì)概述數(shù)據(jù)庫(kù)設(shè)計(jì)概述規(guī)范化規(guī)范化第一范式第一范式第二范式第二范式第三范式第三范式數(shù)據(jù)庫(kù)設(shè)計(jì)概述數(shù)據(jù)庫(kù)設(shè)計(jì)概述v 概述 這里我們思考兩個(gè)問(wèn)題：修建茅屋需要設(shè)計(jì)嗎？修建大廈需要設(shè)計(jì)嗎？結(jié)論是：當(dāng)數(shù)據(jù)庫(kù)比較復(fù)雜（如數(shù)據(jù)量大，表較多，業(yè)務(wù)關(guān)系復(fù)雜）時(shí)，我們需要先設(shè)計(jì)數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)設(shè)計(jì)概述數(shù)據(jù)庫(kù)設(shè)計(jì)概述n良好的數(shù)據(jù)庫(kù)設(shè)計(jì)能夠：n節(jié)省數(shù)據(jù)的存儲(chǔ)空間。n能夠保證數(shù)據(jù)的完整性。n方便進(jìn)行數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)。n糟糕的數(shù)據(jù)庫(kù)設(shè)計(jì)：n數(shù)據(jù)冗余、存儲(chǔ)空間浪費(fèi)。n內(nèi)存空間浪費(fèi)。n數(shù)據(jù)更新和插入的異常。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化

2、v規(guī)范化 在設(shè)計(jì)和操作維護(hù)數(shù)據(jù)庫(kù)時(shí)，關(guān)鍵的步驟就是要確保數(shù)據(jù)正確地分布到數(shù)據(jù)庫(kù)的表中。使用正確的數(shù)據(jù)結(jié)構(gòu)，不僅便于對(duì)數(shù)據(jù)庫(kù)進(jìn)行相應(yīng)的存取操作，而且可以極大地簡(jiǎn)化應(yīng)用程序的其他內(nèi)容（查詢、窗體、報(bào)表、代碼等）。正確進(jìn)行表設(shè)計(jì)的正式名稱就是“數(shù)據(jù)庫(kù)規(guī)范化”。后面我們將通過(guò)實(shí)例來(lái)說(shuō)明具體的規(guī)范化的工程。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化CREATE TABLE Student ( stuid INTEGER NOT NULL, -學(xué)生編號(hào) stuname VARCHAR(200), -學(xué)生姓名 sclass VARCHAR(200),-班級(jí)名稱 coursename VARCHAR(20),-課程名稱 sde

3、gree FLOAT -課程分?jǐn)?shù) tname VARCHAR(20)-老師名字)請(qǐng)同學(xué)們結(jié)合表中的數(shù)據(jù)和結(jié)構(gòu)觀察這張表會(huì)存在哪些缺點(diǎn)。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v缺點(diǎn)：n數(shù)據(jù)冗余、存儲(chǔ)空間浪費(fèi)。n內(nèi)存空間浪費(fèi)。n數(shù)據(jù)更新和插入的異常。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v設(shè)計(jì)數(shù)據(jù)庫(kù)的一般步驟： 收集信息。 抽取對(duì)象。 抽取每個(gè)對(duì)象的屬性。 考慮對(duì)象之間的關(guān)系。 繪制E-R圖。 將E-R圖轉(zhuǎn)換為表格。 最后應(yīng)用三大范式規(guī)范化表格。下面我們以一個(gè)BBS簡(jiǎn)易論壇的數(shù)據(jù)庫(kù)設(shè)計(jì)為例來(lái)看看設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟：數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v收集信息（與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理解數(shù)據(jù)庫(kù)需要完成的任務(wù)） BBS論壇的基

4、本功能： 用戶注冊(cè)和登錄，后臺(tái)數(shù)據(jù)庫(kù)需要存放用戶的注冊(cè)信息和在線狀態(tài)信息； 用戶發(fā)貼，后臺(tái)數(shù)據(jù)庫(kù)需要存放貼子相關(guān)信息，如貼子內(nèi)容、標(biāo)題等； 論壇版塊管理：后臺(tái)數(shù)據(jù)庫(kù)需要存放各個(gè)版塊信息，如版主、版塊名稱、貼子數(shù)等；數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v標(biāo)識(shí)對(duì)象（標(biāo)識(shí)數(shù)據(jù)庫(kù)要管理的關(guān)鍵對(duì)象或?qū)嶓w） 實(shí)體一般是名詞： 用戶：論壇普通用戶、各版塊的版主。 用戶發(fā)的主貼。 論用戶發(fā)的跟貼（回貼）。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v標(biāo)識(shí)每個(gè)實(shí)體的屬性（Attribute）數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v標(biāo)志對(duì)象之間的關(guān)系（Attribute） 跟貼和主貼有主從關(guān)系：我們需要在跟貼對(duì)象中表明它是誰(shuí)的跟貼。 版塊和用戶有關(guān)系：從用戶

5、對(duì)象中可以根據(jù)版塊對(duì)象查出對(duì)應(yīng)的版主用戶的情況。 主貼和版塊有主從關(guān)系：需要表明發(fā)貼是屬于哪個(gè)版塊的。 跟貼和版塊有主從關(guān)系：需要表明跟貼是屬于哪個(gè)版塊的。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v繪制ER圖數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v將ER圖裝換為表格 將各實(shí)體轉(zhuǎn)換為對(duì)應(yīng)的表，將各屬性轉(zhuǎn)換為各表對(duì)應(yīng)的列。 標(biāo)識(shí)每個(gè)表的主鍵列，需要注意的是：沒(méi)有主鍵的表添加ID編號(hào)列，它沒(méi)有實(shí)際含義，用于做主鍵或外鍵，例如用戶表中的“UID”列，版塊表中添加“SID”列，發(fā)貼表和跟貼表中的“TID”列 在表之間建立主外鍵，體現(xiàn)實(shí)體之間的映射關(guān)系 。數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v將ER圖裝換為表格數(shù)據(jù)庫(kù)規(guī)范化數(shù)據(jù)庫(kù)規(guī)范化v將ER圖裝

6、換為表格使用范式使用范式v數(shù)據(jù)庫(kù)基本概念回顧： 實(shí)體：現(xiàn)實(shí)世界中客觀存在并可以被區(qū)別的事物。比如“一個(gè)學(xué)生”、“一本書(shū)”、“一門(mén)課”等等。它也可以是虛擬的，比如說(shuō)“老師與學(xué)校的關(guān)系”。 屬性：實(shí)體所具有的某一特性，比如說(shuō)，“性別”是“人”的一個(gè)屬性。在關(guān)系數(shù)據(jù)庫(kù)中，屬性又是個(gè)物理概念，屬性可以看作是“表的一列”。 使用范式使用范式v數(shù)據(jù)庫(kù)基本概念回顧： 元組：表中的一行就是一個(gè)元組。 分量：元組的某個(gè)屬性值。在一個(gè)關(guān)系數(shù)據(jù)庫(kù)中，它是一個(gè)操作原子，即關(guān)系數(shù)據(jù)庫(kù)在做任何操作的時(shí)候，屬性是“不可分的”。否則就不是關(guān)系數(shù)據(jù)庫(kù)了。 碼：表中可以唯一確定一個(gè)元組的某個(gè)屬性（或者屬性組），如果這樣的碼有不止

7、一個(gè)，那么大家都叫候選碼，我們從候選碼中挑一個(gè)出來(lái)做老大，它就叫主碼。 使用范式使用范式v數(shù)據(jù)庫(kù)基本概念回顧： 主屬性：一個(gè)屬性只要在任何一個(gè)候選碼中出現(xiàn)過(guò)，這個(gè)屬性就是主屬性。 非主屬性：與上面相反，沒(méi)有在任何候選碼中出現(xiàn)過(guò)，這個(gè)屬性就是非主屬性。 外碼：一個(gè)屬性（或?qū)傩越M），它不是碼，但是它別的表的碼，它就是外碼。課堂示例一課堂示例一v范式（范式（6個(gè)）個(gè)）好了，上面已經(jīng)介紹了我們掌握范式所需要的全部基礎(chǔ)概念，下面我們就來(lái)講范式。范式可以理解為一種規(guī)范等級(jí)，范式的包含關(guān)系。一個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)如果符合第二范式，一定也符合第一范式。如果符合第三范式，一定也符合第二范式第一范式第一范式v第一范式（1

8、NF）：屬性不可分。v第一范式的特點(diǎn)： 有主關(guān)鍵字 主鍵不能為空。 主鍵不能重復(fù)。 字段不可以再分。v下表是否滿足第一范式?StudyNo|Name | Sex | Contact20040901 john Male Email:,phone:22245620040901 mary Famale Email: phone:123455 違反第一范式的示例違反第一范式的示例以上的表格是否又滿足第一范式呢？課堂示例一課堂示例一注意：這兩種情況都不滿足第一范式。不滿足第一范式的數(shù)據(jù)庫(kù)，不是關(guān)系數(shù)據(jù)庫(kù)！所以，我們?cè)谌魏侮P(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中，做不出這樣的“表”來(lái)。第二范式第二范式v第二范式（2NF）：非

9、主屬性完全依賴于碼。 定義：如果關(guān)系模式R是第一范式的，而且關(guān)系中每一個(gè)非主屬性不部分依賴于主鍵，稱R是第二范式的。所以第二范式的主要任務(wù)就是滿足第一范式的前提下，消除部分函數(shù)依賴。 觀察這個(gè)表格是否滿足第二范式：StudyNo|Name | Sex | Email|Phone | ClassNo | ClassAddress01 john Male 222456 A樓2單元02 mary Famale 123455 A樓3第二范式的例子第二范式的例子v這個(gè)表完全滿足于第一范式主鍵由StudyNo和ClassNo組成，這樣才能定位到指定行，但是,ClassAddress部分依賴于關(guān)鍵字(Cla

10、ssNo-ClassAddress),所以要變?yōu)閮蓚€(gè)表：表一StudyNo|Name | Sex | Email | Phone | ClassNo 01 john Male 222456 200401 02 mary famale 123455 200402 表二ClassNo | ClassAddress200401 A樓2200402 A樓3第二范式的例子第二范式的例子再來(lái)看一個(gè)例子：違反第二范式的例子違反第二范式的例子分析：一個(gè)學(xué)生上一門(mén)課，一定在特定某個(gè)教室。所以有（學(xué)生，課程）教室一個(gè)學(xué)生上一門(mén)課，一定是特定某個(gè)老師教。所以有（學(xué)生，課程）老師一個(gè)學(xué)生上一門(mén)課，他老師的職稱可以確定

11、。所以有（學(xué)生，課程）老師稱一個(gè)學(xué)生上一門(mén)課，一定是特定某個(gè)教材。所以有（學(xué)生，課程）教材一個(gè)學(xué)生上一門(mén)課，一定在特定時(shí)間。所以有（學(xué)生，課程）上課時(shí)間因此（學(xué)生，課程）是一個(gè)碼。然而，一個(gè)課程，一定指定了某個(gè)教材，一年級(jí)語(yǔ)文肯定用的是小學(xué)語(yǔ)文1那么就有課程教材。（學(xué)生，課程）是個(gè)碼，課程卻決定了教材，這就叫不完全依賴，或者說(shuō)部分依賴。出現(xiàn)這樣的情況，就不滿足第二范式！思考： 那么，如果我們希望他滿足第二范式，仿照剛才的例子我們應(yīng)該怎樣做了？違反第二范式的例子違反第二范式的例子解決：1、校長(zhǎng)要新增加一門(mén)課程叫“微積分”，教材是大學(xué)數(shù)學(xué)，怎么辦？學(xué)生還沒(méi)選課，而學(xué)生又是主屬性，主屬性不能空，課程

12、怎么記錄呢，教材記到哪呢(插入異常)2、下學(xué)期沒(méi)學(xué)生學(xué)一年級(jí)語(yǔ)文（上）了，學(xué)一年級(jí)語(yǔ)文（下）去了，那么表中將不存在一年級(jí)語(yǔ)文（上），也就沒(méi)了小學(xué)語(yǔ)文1。這時(shí)候，校長(zhǎng)問(wèn)：一年級(jí)語(yǔ)文（上）用的什么教材??？郁悶了吧?(刪除異常)3、校長(zhǎng)說(shuō)：一年級(jí)語(yǔ)文（上）換教材，換成大學(xué)語(yǔ)文。有10000個(gè)學(xué)生選了這么課，改動(dòng)好大??！改累死了郁悶了吧？（修改異常）那應(yīng)該怎么解決呢？投影分解，將一個(gè)表分解成兩個(gè)或若干個(gè)表。違反第二范式的例子違反第二范式的例子解決：第三范式第三范式v第三范式（3NF）（不依賴于其它非主屬性消除傳遞依賴 ）。例:StudyNo|Name|Sex| Email|bounsLevel|bouns40901 john M 良 $100040902 mary famale 差 $600這個(gè)完全滿足了第二范式,但是bounsLevel和bouns存在傳遞依賴。第三范式第三范式解決：更改為：表一：StudyNo|Name |Sex| Email | bouunsNo20040901 john Male 120040902 mary Famale 2表二：bounsNo | bounsLevel | bouns1 優(yōu)秀 $10002 良 $600 第三范式第三范式在來(lái)看一個(gè)