資料庫系統(tǒng)課程-課件

第四章

資料庫正規(guī)化課程名稱：資料庫系統(tǒng)授課老師：李春雄博士第四章

資料庫正規(guī)化課程名稱：資料庫系統(tǒng)本章學(xué)習(xí)目標(biāo)

1.讓讀者瞭解資料庫正規(guī)化的概念及目的。2.讓讀者瞭解資料庫正規(guī)化(Normalization)

程序及規(guī)則。本章學(xué)習(xí)目標(biāo)1.讓讀者瞭解資料庫正規(guī)化的概念及目的。本章內(nèi)容4-1正規(guī)化的概念4-2正規(guī)化的目的4-3功能相依(FunctionalDependence;FD)4-4資料庫正規(guī)化(Normalization)4-5反正規(guī)化(De-normalization)4-6結(jié)語本章內(nèi)容4-1正規(guī)化的概念前言一般的初學(xué)者在進(jìn)行資料庫設(shè)計時，以為用一個資料表就可以儲存全部的資料，或憑著自己的直覺而沒有經(jīng)過完整的規(guī)劃，就隨意的將資料表分割成許多小的資料表，這種設(shè)計方法，不但浪費儲存空間，更嚴(yán)重影響到資料庫不一致的現(xiàn)象，以致於DBA(資料庫管理師)維護(hù)困難。為了避免以上的問題產(chǎn)生，唯一的方法，就是在設(shè)計關(guān)聯(lián)式資料庫之前，一定先要完成資料的正規(guī)化(Normalization)。前言一般的初學(xué)者在進(jìn)行資料庫設(shè)計時，以為用4-1正規(guī)化的概念

資料庫是用來存放資料的地方，因此，如何妥善的規(guī)劃資料庫綱要

(DatabaseSchema)是一件很重要的工作，但是，資料庫綱要的設(shè)計必須要配合實務(wù)上的需要，因此，當(dāng)資料庫綱要設(shè)計完成後，如何檢視設(shè)計是否良好，就必需要使用正規(guī)化(Normalization)的方法論了。何謂正規(guī)化(Normalization)？就是結(jié)構(gòu)化分析與設(shè)計中，建構(gòu)「資料模式」所運用的一個技術(shù)，其目的是為了降低資料的「重覆性」與避免「更新異?！沟那闆r發(fā)生。

因此，就必須將整個資料表中重複性的資料剔除，否則在關(guān)聯(lián)表中會造成新增異常、刪除異常、修改異常的狀況發(fā)生。4-1正規(guī)化的概念資料庫是用來存放資料4-2正規(guī)化的目的一般而言，正規(guī)化的精神就是讓資料庫中重複的欄位資料減到最少，並且能快速的找到資料，以提高關(guān)聯(lián)性資料庫的效能?！灸康摹?.降低資料重複性(DataRedundancy)。2.避免資料更新異常(Anomalies)。4-2正規(guī)化的目的一般而言，正規(guī)化的精神一、降低資料重複性(DataRedundancy)

正規(guī)化的目的是什麼呢？簡單來說，就是降低資料重複的狀況發(fā)生。試想，當(dāng)校務(wù)系統(tǒng)的「學(xué)籍資料」分別存放在「教務(wù)處」與「學(xué)務(wù)處」時，不僅資料重覆儲存，浪費空間，更嚴(yán)重的是，當(dāng)學(xué)生姓名變更時，就必須要同時更改「教務(wù)處」與「學(xué)務(wù)處」的「學(xué)籍資料」，否則將導(dǎo)致資料不一致的現(xiàn)象，因此，資料庫如果沒有事先進(jìn)行正規(guī)化，將會增加應(yīng)用系統(tǒng)撰寫的困難，同時也會增加資料庫的處理負(fù)擔(dān)，所以降低資料重複性是「正規(guī)化」的重要工作。一、降低資料重複性(DataRedundancy)【方法】

將「教務(wù)處」與「學(xué)務(wù)處」中，把相同的資料項，抽出來組成一個新的資料表(學(xué)籍資料表)，如下圖所示：正規(guī)化：將兩個表格切成三個資料表說明：在正規(guī)化之後，「學(xué)籍資料表」的主鍵(P.K.)分別與「學(xué)務(wù)處資料表」的

外鍵(F.K.)及「教務(wù)處資料表」的外鍵(F.K.)進(jìn)行關(guān)聯(lián)，以產(chǎn)生關(guān)聯(lián)式資料庫?！痉椒ā繉ⅰ附虅?wù)處」與「學(xué)務(wù)處」中，把相同的資料項，抽出二、避免資料更新異常(Anomalies)

(一)新增異常(InsertAnomalies)新增某些資料時必須同時新增其他的資料，否則會產(chǎn)生新增異?，F(xiàn)象。亦即在另一個實體的資料尚未插入之前，無法插入目前這個實體的資料。(二)修改異常(UpdateAnomalies)修改某些資料時必須一併修改其他的資料，否則會產(chǎn)生修改異?，F(xiàn)象。(三)刪除異常(DeleteAnomalies)刪除某些資料時必須同時刪除其他的資料，否則會產(chǎn)生刪除異常現(xiàn)象。亦即刪除單一資料列造成多個實體的資訊遺失。二、避免資料更新異常(Anomalies)(一)新增異常(【實例】

假設(shè)某國立大學(xué)開設(shè)「網(wǎng)路碩士學(xué)分班」，其學(xué)員課程收費表如下所示。學(xué)員課程收費表【說明】在上面的學(xué)員課程收費表中雖然僅僅只有三個欄位，但是已不算是

一個良好的儲存結(jié)構(gòu)，因為此表格中有資料重覆現(xiàn)象?！纠纭坑行┱n程的費用在許多學(xué)員身上重覆出現(xiàn)(S0001與S0003；S0002與S0005)，因此可能會造成錯誤或不一致的異常(Anomalies)現(xiàn)象?！痉治觥繌南乱豁撻_始學(xué)號課號學(xué)分費S0001C0013000S0002C0024000S0003C0013000S0004C0035000S0005C0024000學(xué)員的選課需知如下：1.每一位學(xué)員只能選修一門課程。2.每一門課程均有收費標(biāo)準(zhǔn)。(C001為3000元，C002為4000元，C003為5000元)【實例】假設(shè)某國立大學(xué)開設(shè)「網(wǎng)路碩士學(xué)分班」，其學(xué)員課程【分析】三種可能的異常(Anomalies)現(xiàn)象

(一)新增異常

假設(shè)學(xué)校又要新增C004課程，但此課程無法立即新增到資料表中，除非至少有一位學(xué)員選修了C004這門課程?！痉治觥咳N可能的異常(Anomalies)現(xiàn)象(一)新增(二)修改異常

假如C002課程的學(xué)分費由4000元調(diào)整為4500元時，若「C002課程」有多位學(xué)員選修時，因此，修改「S0002」學(xué)員的學(xué)分費時，可能有些記錄未修改到(S0005)，造成資料的不一致現(xiàn)象。(二)修改異常(三)刪除異常

假設(shè)學(xué)員S0004退選時，同時也刪除C003這門課程，由於該課程只有S0004這位學(xué)員選修，因此若把這一筆記錄刪除，從此我們將失去C003這門課程及其學(xué)分費的資訊。(三)刪除異?！窘鉀Q方法】正規(guī)化

由於上述的分析，發(fā)現(xiàn)學(xué)員課程收費表並不是一個良好的儲存結(jié)構(gòu)，因此，我們就必須要採用4-4節(jié)所要討論的正規(guī)化，將學(xué)員課程收費表分割成兩個資料表，即「選課表」與「課程收費對照表」，因此，才不會發(fā)生上述的異?，F(xiàn)象。學(xué)號課號學(xué)分費S0001C0013000S0002C0024000S0003C0013000S0004C0035000S0005C0024000課程收費表學(xué)號課號S0001C001S0002C002S0003C001S0004C003S0005C002選課表課號學(xué)分費C0013000C0024000C0035000課程收費對照表正規(guī)化正規(guī)化【解決方法】正規(guī)化由於上述的分析，發(fā)現(xiàn)學(xué)員課程收費表並不題目：建立「選課表」與「課程收費對照表」的關(guān)聯(lián)圖先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-2B.accdb

」步驟二：資料庫工具／資料庫關(guān)聯(lián)圖／「選課表」的「課號」外鍵拖曵到「課程收費對照表」的「課號」主鍵，此外，在「編輯關(guān)聯(lián)」對話方塊中，說明「關(guān)聯(lián)類型：一對多」，其中一的那方代表「主鍵」，而多的那方代表「外鍵」。步驟三：測試三種可能的異?，F(xiàn)象。1.新增異常測試2.修改異常測試3.刪除異常測試您可以參考「完成檔\ch4-2B.accdb」【Access上機實作1】題目：建立「選課表」與「課程收費對照表」的關(guān)聯(lián)圖【Acce【

Access上機實作】請建立「正規(guī)化」後的兩個表格，「選課表」與「課程收費對照表」，並建立關(guān)聯(lián)圖學(xué)號課號S0001C001S0002C002S0003C001S0004C003S0005C002選課表課號學(xué)分費C0013000C0024000C0035000課程收費對照表資料庫名稱：ch4-2B.accdb【Access上機實作】請建立「正規(guī)化」後的兩個表格，4-3功能相依(FunctionalDependence;FD)

一、功能相依的概念【定義】

是指資料表中各欄位之間的相依性。亦即某欄位不能單獨存在，必須要和其他欄位一起存在時才有意義，稱這兩個欄位具有功能相依?！纠纭繉W(xué)生資料表【說明】在上面的資料表中,「姓名」欄位的值必須搭配「學(xué)號」欄位才有意義,則我們說『姓名欄位相依於學(xué)號欄位』。姓名學(xué)號性別系所電話地址4-3功能相依(FunctionalDependence換言之，在「學(xué)生資料表」中，「學(xué)號」決定了「姓名」，也決定了「性別」、「系所」、「電話」、「地址」等資訊，我們可以用以下圖示的方法來表示這些功能相依性?！痉治觥?/p>

1.學(xué)號→姓名

2.學(xué)號→{姓名，性別，系所，電話，地址}

3.學(xué)號：為決定因素(∵學(xué)號姓名)4.姓名，性別，系所，電話，地址：為相依因素因此，「學(xué)號」欄位為主鍵,做為唯一辨識該筆記錄的欄位。「姓名」欄位必須要相依於「學(xué)號」欄位,對此資料表來說「姓名」欄位才有意義；同理可證,「地址」欄位亦必須相依於「學(xué)號」欄位,才有意義。換言之，在「學(xué)生資料表」中，「學(xué)號」決定了「姓名」，也決定了二、功能相依(FD)的表示方式1.假設(shè)有一個資料表R，並且有三個欄位，分別為X,Y,Z，因此，我們就

可以利用一條數(shù)學(xué)式來表示：R={X,Y,Z}2.假設(shè)在R={X,Y,Z}數(shù)學(xué)式中，X和Y之間存在「功能相依」時，並且

存在Y功能相依於X，則我們可以利用以下的表示式：

(1)Y∝X(Y功能相依於X)(2)XY(X決定Y)若XY時，在FD的左邊X稱為決定因素(Determinant)

在FD的右邊Y稱為相依因素(Dependent)3.示意圖：學(xué)號(X)姓名(Y)二、功能相依(FD)的表示方式學(xué)號(X)姓名(Y)4-3.1完全功能相依

（FullFunctionalDependency）

【定義】假設(shè)在關(guān)聯(lián)表R(X,Y,Z)中，包含一組功能相依(X,Y)Z，如果我們從關(guān)聯(lián)表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z不存在，此時我們稱Z為「完全功能相依」於(X,Y)。反之，若(X,Y)Z存在，我們稱Z為「部份功能相依」於(X,Y)。【例如】{學(xué)號(X)，課號(Y)}→成績(Z)

這是「完全功能相依」如果從關(guān)聯(lián)表中移除課號(Y)，則功能相依(X)Z不存在因為，「學(xué)號」和「課號」兩者一起決定了「成績」，缺一不可。否則，只有一個學(xué)號對應(yīng)一個成績，無法得知該成績是那一門課程的分?jǐn)?shù)。亦即成績(Z)完全功能相依於{學(xué)號(X)，課號(Y)}4-3.1完全功能相依

（FullFun4-3.2部份功能相依

（PartialFunctionalDependency）

【定義】假設(shè)在關(guān)聯(lián)表R(X,Y,Z)中包含一組功能相依(X,Y)Z，如果我們從關(guān)聯(lián)表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z存在，此時我們稱Z為「部份功能相依」於(X,Y)?！纠纭縶學(xué)號(X)，身份證字號(Y)}→姓名(Z)

這是「部份功能相依」如果從關(guān)聯(lián)表中移除身份證字號(Y)，則功能相依(X)Z存在因為，「學(xué)號」也可以決定「姓名」，他們之間也具有功能相依性。4-3.2部份功能相依

（PartialF4-3.3遞移相依

（TransitiveDependency）

【定義】是指在二個欄位間並非直接相依,而是借助第三個欄位來達(dá)成資料

相依的關(guān)係。【例如】Y相依於X；而Z又相依於Y,如此X與Z之間就是遞移相依的關(guān)係?！臼疽鈭D】在上面的關(guān)聯(lián)表R(X,Y,Z)中包含一組相依XY,YZ，則XZ，此時我們稱Z遞移相依於X。

4-3.3遞移相依

（Transitive【舉例】

課程代號→老師編號老師編號→老師姓名

這是遞移相依因為，「課程代號」可以決定「老師編號」，並且「老師編號」又可以決定「老師姓名」，因此，「課程代號」與「老師姓名」之間存在遞移相依性。遞移相依性【舉例】課程代號→老師編號遞移相依性4-4資料庫正規(guī)化(Normalization)

【定義】是指將原先關(guān)聯(lián)(表格)的所有資訊，在「分解」之後，仍能由數(shù)個新關(guān)聯(lián)(表格)中經(jīng)過「合併」得到相同的資訊。即所謂的「無損失分解(Losslessdecomposition)」的觀念。

【無損失分解觀念】當(dāng)關(guān)聯(lián)表R被「分解」成數(shù)個關(guān)聯(lián)表R1,R2,…,Rn時，則可以再透過「合併」R1R2…Rn得到相同的資訊R。如下圖所示。

分解合併合併合併分解分解4-4資料庫正規(guī)化(Normalization)【定義】【實例】分解合併註：分解：是指透過「正規(guī)化」技術(shù)，將一個大資料表分割成二個小資料表?！侗菊陆榻B》

合併：是指透過「合併」理論，將數(shù)個小資料表整合成一個大資料表?！兜诎苏陆榻B》【實例】分解合併註：分解：是指透過「正規(guī)化」技術(shù)，將一個大資題目：利用「分割」後的表格，再進(jìn)行「合併」先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-4.accdb

」步驟二：將分割後的兩個表格，利用SQL合併(已完成)步驟三：以證明無損失分解觀念，亦即將原先關(guān)聯(lián)(表格)的所有資訊，在「分解」之後，仍能由數(shù)個新

關(guān)聯(lián)(表格)中經(jīng)過「合併」得到相同的資訊。您可以參考「完成檔\ch4-4.accdb」【Access上機實作2】題目：利用「分割」後的表格，再進(jìn)行「合併」【Access上4-4.1正規(guī)化示意圖正規(guī)化就是對一個「非正規(guī)化」的原始資料表，進(jìn)行一連串的「分割」，並且分割成數(shù)個「不重複」儲存的資料表。如下圖所示：在上圖中，利用一連串的「分割」，亦即利用所謂的「正規(guī)化的規(guī)則」，循序漸進(jìn)的將一個「重複性高」的資料表分割成數(shù)個「重複性低」或「沒有重複性」的資料表。分割4-4.1正規(guī)化示意圖正規(guī)化就是對一個「非正規(guī)化」的原始4-4.2正規(guī)化的規(guī)則引言

資料庫在正規(guī)化時會有一些規(guī)則，並且每條規(guī)則都稱為「正規(guī)形式」。如果符合第一條規(guī)則，則資料庫就稱為「第一正規(guī)化形式(1NF)」。如果符合前二條規(guī)則，則資料庫就被視為屬於「第二正規(guī)化形式(2NF)」。雖然資料庫的正規(guī)化最多可以進(jìn)行到第五正規(guī)化形式，但是在實務(wù)上，

BCNF被視為大部分應(yīng)用程式所需的最高階正規(guī)形式。4-4.2正規(guī)化的規(guī)則引言從上圖中，我們可以清楚得知，正規(guī)化是循序漸進(jìn)的過程，亦即資料表必須滿足第一正規(guī)化的條件之後，才能進(jìn)行第二正規(guī)化。換言之，第二正規(guī)化必須建立在符合第一正規(guī)化的資料表上，依此類推。從上圖中，我們可以清楚得知，正規(guī)化是循序漸進(jìn)正規(guī)化步驟

在資料表正規(guī)化的過程(1NF到BCNF)中,每一個階段都是以欄位的「相依性」,做為分割資料表的依據(jù)之一。其完整的正規(guī)化步驟如下圖所示：正規(guī)化步驟在資料表正規(guī)化的過程(1NF到BCNF正規(guī)化步驟<續(xù)>

1.第一正規(guī)化(FirstNormalForm;1NF)：由E.F.Codd提出。滿足所有記錄中的屬性內(nèi)含值都是基元值(AtomicValue)。即無重覆項目群。2.第二正規(guī)化(SecondNormalForm;2NF)：由E.F.Codd提出。

符合1NF且每一非鍵值欄位「完全功能相依」於主鍵。

即不可「部分功能相依」於主鍵。3.第三正規(guī)化(ThirdNormalForm;3NF)：由E.F.Codd提出。

符合2NF且每一非鍵值欄位非「遞移相依」於主鍵。

即除去「遞移相依」問題。正規(guī)化步驟<續(xù)>1.第一正規(guī)化(FirstNormal正規(guī)化步驟<續(xù)>

4.Boyce-Codd正規(guī)化型式(Boyce-CoddNormalForm;BCNF)：

由R.F.Boyce與E.F.Codd共同提出。

符合3NF且每一決定因素(Determinant)皆是候選鍵，簡稱為BCNF。5.第四正規(guī)化(FourthNormalForm;4NF)：由R.Fagin提出。

符合BCNF，再除去所有的多值相依。6.第五正規(guī)化(FifthNormalForm;5NF)：由R.Fagin提出。

符合4NF，且沒有合併相依。正規(guī)化步驟<續(xù)>4.Boyce-Codd正規(guī)化型式(Bo4-4.3第一正規(guī)化(1NF)

【定義】

是指在資料表中的所有記錄之屬性內(nèi)含值都是基元值(AtomicValue)。

亦即無重覆項目群。【實例】假設(shè)現(xiàn)在有一份某某科技大學(xué)的學(xué)生選課資料表，如表4-1(a)所示：表4-1(a)學(xué)生選課資料表4-4.3第一正規(guī)化(1NF)【定義】我們可以將表4-1(a)的原始資料利用二維表格來儲存，如表4-1(b)。表4-1(a)學(xué)生選課資料表表4-1(b)未正規(guī)化的資料表：學(xué)生選課資料報表二維表格來儲存我們可以將表4-1(a)的原始資料利用二維表格來儲存，如表4因此，我們發(fā)現(xiàn)有許多屬性的內(nèi)含值都具有二個或二個以上的值(亦稱為重複資料項目)，其原因：尚未進(jìn)行第一正規(guī)化。表4-1(b)未正規(guī)化的資料表：學(xué)生選課資料報表■未符合1NF資料表的「缺點」以上資料表中的『課程代碼』、『課程名稱』、『學(xué)分?jǐn)?shù)』、『必選修』、『成績』、『老師編號』及「老師姓名」欄位的長度無法確定，因為學(xué)生要選修多少門課程，無法事先得知(李碩安同學(xué)選了2門，李碩崴同學(xué)選了3門)，因此，必須要預(yù)留很大的空間給這七個欄位,如此反而造成儲存空間的浪費。重複資料項目因此，我們發(fā)現(xiàn)有許多屬性的內(nèi)含值都具有二個或二個以上的值(亦第一正規(guī)化的規(guī)則1.每一個欄位只能有一個基元值(Atomic)即單一值。例如：課程名稱欄位中不能存入兩科或兩科以上的課程名稱。2.沒有任何兩筆以上的資料是完全重覆。3.資料表中有主鍵,而其他所有的欄位都相依於「主鍵」。例如1：姓名與性別欄位都相依於「學(xué)號」欄位。例如2：課程名稱、學(xué)分?jǐn)?shù)、必選修、老師編號及老師姓名相依於「課程代

碼」欄位。例如3：「成績」欄位相依於「學(xué)號」與「課程代碼」欄位?！渡钊胩接懺谙乱豁摗返谝徽?guī)化的規(guī)則1.每一個欄位只能有一個基元值(Atom《深入探討》

Q：為什麼「成績」欄位一定要相依於「學(xué)號」與「課程代碼」欄位？分析一：如果「成績」欄位本身單獨存在時，則沒有意義，因為只有「成績」卻無法讓同學(xué)或老師清楚得知該「成績」是屬於哪一位學(xué)生的哪一門課的成績。分析二：如果「成績」欄位只相依於「課程編號」也是沒有意義的，因為只有「成績」也是無法讓同學(xué)或老師清楚得知該「成績」是屬於哪一位學(xué)生所修課的成績。課程代碼成績C00174C00293沒有意義成績7493沒有意義《深入探討》Q：為什麼「成績」欄位一定要相依於「學(xué)號」與分析三：如果「成績」欄位只相依於「學(xué)號」也是沒有意義的，因為只有「成績」也是無法讓同學(xué)或老師清楚得知該「成績」是屬於哪一門課的成績。分析四：但是，如果「成績」欄位相依於「課程編號」及「學(xué)號」二個欄位時,就可以了解某個學(xué)生修某堂課的成績,這樣的成績資料才有意義。學(xué)號課程代碼成績001C00174001C00293有意義學(xué)號成績0017400193沒有意義分析三：學(xué)號課程代碼成績93有意義學(xué)號成績00193沒有意義第一正規(guī)化的作法：【作法】將重複的資料項分別儲存到不同的記錄中,並加上適當(dāng)?shù)闹麈I。步驟一：檢查是否存在「重複資料項」第一正規(guī)化的作法：【作法】將重複的資料項分別儲存到不同的步驟二：將重複資料項分別儲存到不同的記錄中,並加上適當(dāng)?shù)闹麈I未經(jīng)正規(guī)化前的學(xué)生選課表經(jīng)過正規(guī)化後的學(xué)生選課表(1NF)重複資料項儲存到不同的記錄步驟二：將重複資料項分別儲存到不同的記錄中,並加上適當(dāng)?shù)闹鹘?jīng)過正規(guī)化後的學(xué)生選課表(1NF)在經(jīng)由第一正規(guī)化之後，使得每一個欄位內(nèi)只能有一個資料(基元值)。雖然增加了許多記錄,但每一個欄位的「長度」及「數(shù)目」都可以固定,而且我們可用「課程代碼」欄位加上「學(xué)號」欄位當(dāng)作主鍵,使得在查詢某學(xué)生修某課程的「成績」時,就非常方便而快速了。經(jīng)過正規(guī)化後的學(xué)生選課表(1NF)題目：第一正規(guī)化後的結(jié)果先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-4-3(1NF)」步驟二：以證明在1NF之後，1.每一個欄位只能有一個基元值。例如：課程名稱欄位中不能存入兩科或兩科以上的課程名稱。2.沒有任何兩筆以上的資料是完全重覆。3.資料表中有主鍵,而其他所有的欄位都相依於「主鍵」您可以參考「完成檔\ch4-4-3(1NF)」【Access上機實作3】題目：第一正規(guī)化後的結(jié)果【Access上機實作3】4-4.4第二正規(guī)化(2NF)

在完成了第一正規(guī)化之後，讀者是否發(fā)現(xiàn)在資料表中產(chǎn)生許多重複的資料。如此,不但浪費儲存的空間,更容易造成新增、修改及刪除資料時的異常狀況，說明如下。(1)新增異常檢查(InsertAnomaly)

無法先新增課程資料，如「課程代碼」及「課程名稱」，要等選課之後，才能新增。原因：以上的新增動作違反「實體完整性規(guī)則」，因為，主鍵或複合主鍵不可以為空值NULL。4-4.4第二正規(guī)化(2NF)在完成了第一(2)修改異常檢查(UpdateAnomaly)「網(wǎng)頁設(shè)計」課程重覆多次，因此，修改「網(wǎng)頁設(shè)計」課程的成績時，可能有些記錄未修改到，造成資料的不一致現(xiàn)象。例如：有選「網(wǎng)頁設(shè)計」課程的同學(xué)之成績各加5分，可能會有些同學(xué)

有加分，而有些同學(xué)卻沒有加分，導(dǎo)致資料不一致的情況。(2)修改異常檢查(UpdateAnomaly)(3)刪除異常檢查(DeleteAnomaly)當(dāng)刪除#4學(xué)生的記錄時，同時也會刪除課程名稱、學(xué)分?jǐn)?shù)及相關(guān)的資料。所以導(dǎo)致「計概」課程的2學(xué)分?jǐn)?shù)也同時被刪除了。綜合上述的三種異常現(xiàn)象，所以,我們必須進(jìn)行「第二階正規(guī)化」,來消除這些問題。(3)刪除異常檢查(DeleteAnomaly)第二正規(guī)化的規(guī)則如果資料表符合以下的條件,我們說這個資料表符合第二階正規(guī)化的形式(SecondNormalForm,簡稱2NF)：符合1NF每一非鍵屬性(如：姓名、性別…)必須「完全相依」於主鍵(學(xué)號)；即不可「部分功能相依」於主鍵。

換言之，「部分功能相依」只有當(dāng)「主鍵」是由「多個欄位」組成時才會發(fā)生(亦即複合主鍵)，也就是當(dāng)某些欄位只與「主鍵中的部分欄位」有「相依性」,而與另一部分的欄位沒有相依性。第二正規(guī)化的規(guī)則如果資料表符合以下的條件,我們說這個資第二正規(guī)化的作法分割資料表；亦即將「部分功能相依」的欄位「分割」出去，

再另外組成「新的資料表」。其步驟如下：步驟一：檢查是否存在「部分功能相依」「姓名」只相依於「學(xué)號」「課程名稱」只相依於「課程代碼」在上面的資料表中，主鍵是由「學(xué)號+課程代碼」兩個欄位所組成，但「姓名」和「性別」只與「學(xué)號」有「相依性」，亦即(姓名，性別)相依於學(xué)號，而「課程名稱」只與「課程代碼」有「相依性」，亦即(課程名稱，學(xué)分?jǐn)?shù)，必選修，老師編號，老師姓名)相依於課程代碼。因此，學(xué)號是複合主鍵(學(xué)號,課程代碼)的一部份?！啻嬖诓糠止δ芟嘁?。第二正規(guī)化的作法分割資料表；亦即將「部分功能相依」的欄步驟二：將「部分功能相依」的欄位分割出去，再另外組成新的資料表我們將「選課資料表」分割成三個較小的資料表(加「底線」的欄位為主鍵)：一、學(xué)生資料表(學(xué)號，姓名，性別)二、成績資料表(學(xué)號，課程代碼，成績)學(xué)號姓名性別001李碩安男002李碩崴男學(xué)號課程代碼成績001C00174001C00293002C00263002C00382002C00594步驟二：將「部分功能相依」的欄位分割出去，再另外組成新的資三、課程資料表(課程代碼，課程名稱，學(xué)分?jǐn)?shù)，必選修，

老師編號，老師姓名)在第二正規(guī)化之後，產(chǎn)生三個資料表，分別為學(xué)生資料表、成績資料表及課程資料表，除了「課程資料表」之外，其餘兩個資料表(學(xué)生資料表與成績資料表)都已符合2NF,3NF及BCNF。課程代碼課程名稱學(xué)分?jǐn)?shù)必選修老師編號老師姓名C001程式語言4必T001李安C002網(wǎng)頁設(shè)計3選T002張三C003計概2必T003李四C005網(wǎng)路教學(xué)4選T005王五三、課程資料表(課程代碼，課程名稱，學(xué)分?jǐn)?shù)，必選修，課程代碼題目：第二正規(guī)化後的結(jié)果先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-4-3(2NF)」步驟二：以證明在2NF之後，將「部分功能相依」的欄位「分割」出去，另外分割成三個資料表「學(xué)生資料表」、

「成績資料表」及「課程資料表」。

您可以參考「完成檔\ch4-4-3(2NF)」【Access上機實作4】題目：第二正規(guī)化後的結(jié)果【Access上機實作4】4-4.5第三正規(guī)化(3NF)

在完成了第二正規(guī)化之後，其實還存在以下三種異常現(xiàn)象,亦即新增、刪除或更新資料時的異常狀況，說明如下：(1)新增異常(InsertAnomaly)

以上無法先新增老師資料，要等確定課程代碼之後，才能輸入。原因為：新增動作違反「實體完整性規(guī)則」，因為主鍵或複合主鍵不可以為空值NULL。4-4.5第三正規(guī)化(3NF)在完成了第二正規(guī)化之後，其(2)修改異常(UpdateAnomaly)

假如「李安」老師開設(shè)多門課程時，則欲修改「李安」老師姓名為「李碩安」時，可能有些記錄未修改到，造成資料的不一致現(xiàn)象。未修改到(2)修改異常(UpdateAnomaly)未修改到(3)刪除異常(DeleteAnomaly)

當(dāng)刪除#1課程的記錄時，同時也刪除老師編號T001。

所以導(dǎo)致老師編號T001及老師姓名的資料也同時被刪除了。綜合上述的三種異?，F(xiàn)象，所以,我們必須進(jìn)行「第三階正規(guī)化」,來消除這些問題。記錄課程代碼課程名稱學(xué)分?jǐn)?shù)必選修老師編號老師姓名#1C001程式語言4必T001李安#2C002網(wǎng)頁設(shè)計3選T002張三#3C003計概2必T003李四#4C005網(wǎng)路教學(xué)4選T005王五(3)刪除異常(DeleteAnomaly)記錄課程代碼課第三正規(guī)化的規(guī)則如果資料表符合以下條件,我們就說這個資料表符合第三階正規(guī)化的形式(ThirdNormalForm,簡稱3NF)：符合2NF各欄位與「主鍵」之間沒有「遞移相依」的關(guān)係?！救绾握疫f移相依呢？】若要找出資料表中各欄位與「主鍵」之間的遞移相依性,最簡單的方法就是從左到右掃瞄資料表中各欄位有沒有『與主鍵無關(guān)的相依性』存在?？赡艿那闆r如下：如果有存在時，則代表有「遞移相依」的關(guān)係2.如果有不存在時，則代表沒有「遞移相依」的關(guān)係第三正規(guī)化的規(guī)則如果資料表符合以下條件,我們就說這個資第三正規(guī)化的作法分割資料表；亦即將「遞移相依」或「間接相依」的欄位「分割」

出去，再另外組成「新的資料表」。其步驟如下：步驟一：檢查是否存在「遞移相依」由於每一門課程都會有授課的老師,因此，「老師編號」相依於「課程代碼」。並且「老師姓名」相依於「教師編號」,因此，存在有『與主鍵無關(guān)的相依性』。亦即存在「老師姓名」與主鍵(課程代碼)無關(guān)的相依性?！啻嬖谶f移相依。第三正規(guī)化的作法分割資料表；亦即將「遞移相依」或「間接

上述「課程資料表」中的[課程名稱]、[學(xué)分?jǐn)?shù)]、[必選修]、[老師編號]都直接相依於主鍵[課程代碼](簡單的說，這些都是課程資料的必需欄位)，而[老師姓名]是直接相依於[老師編號]，然後才間接相依於[課程代碼]，它並不是直接相依於[課程代碼]，稱為「遞移相依」『TransitiveDependency』或「間接相依」。例如：當(dāng)AB,BC，則AC(稱為遞移相依)。因此，在「課程資料表」中存在「遞移相依」關(guān)係現(xiàn)象，

步驟二：將「遞移相依」的欄位「分割」出去，再另外組成「新的資料表」因此，我們將「課程資料表」分割為二個資料表，並且利用外鍵(F.K.)來連接二個資料表。如下圖所示。步驟二：將「遞移相依」的欄位「分割」出去，再另外組成「新的資在我們完成第三正規(guī)化後，共產(chǎn)生了四個表格，如下表所示：第三正規(guī)化後的四個表格第二正規(guī)化產(chǎn)生的表格第三正規(guī)化產(chǎn)生的表格在我們完成第三正規(guī)化後，共產(chǎn)生了四個表格，如下表所示：第三題目：第三正規(guī)化後的結(jié)果，並建立關(guān)聯(lián)圖先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-4-3(3NF)」步驟二：以證明在3NF之後，將「遞移相依」的欄位「分割」出去，再另外組成「新的資料表」。因此，我們將「課程資

料表」分割為二個資料表，分別為「課程資料表」與「老師資料表」。步驟三：在3NF之後，總共產(chǎn)生了四個資料表(「學(xué)生資料表」、「成績資料表」、「課程資料表」

與「老師資料表」)

您可以參考「完成檔\ch4-4-3(3NF)」【Access上機實作5】題目：第三正規(guī)化後的結(jié)果，並建立關(guān)聯(lián)圖【Access上機實4-4.6BCNF正規(guī)化是由Boyce和Codd於1974年所提出來的3NF的改良式。其條件比3NF更加嚴(yán)苛。因此每一個符合BCNF的關(guān)聯(lián)一定也是3NF。對於大部分資料庫來說,通常只需要執(zhí)行到第三階段的正規(guī)化就足夠了?！具m用時機】如果資料表的「主鍵」是由「多個欄位」組成的,則必須再執(zhí)行Boyce-Codd正規(guī)化。4-4.6BCNF正規(guī)化是由BoyceBCNF的規(guī)則如果資料表的「主鍵」只由「單一欄位」組合而成,則符合第三階正

規(guī)化的資料表,亦符合BCNF(Boyce-CoddNormalForm)正規(guī)化。如果資料表的「主鍵」由「多個欄位」組成(又稱為複合主鍵),則資料

表就必須要符合以下條件,我們就說這個資料表符合BCNF(Boyce-CoddNormalForm)正規(guī)化的形式。

1.符合3NF的格式。2.「主鍵」中的各欄位不可以相依於其他非主鍵的欄位。BCNF的規(guī)則如果資料表的「主鍵」只由「單一欄位」組合檢驗「成績資料表」是否滿足

BCNF規(guī)範(fàn)

由於在我們完成第三正規(guī)化之後，已經(jīng)分割成四個資料表，其中「成績資料表」的主鍵是由「多個欄位」組成(又稱為複合主鍵)。

因此，我們利用BCNF(Boyce-CoddNormalForm)正規(guī)化的條件,來檢驗「成績資料表」：成績資料表(學(xué)號，課程代碼，成績)說明：「成績」欄位相依於「課程代碼」及「學(xué)號」欄位，對「課程代碼」欄位而言,並沒有相依於「成績」欄位；對「學(xué)號」欄位而言,也沒有相依於「成績」欄位。所以成績資料表是符合『Boyce-Codd正規(guī)化的形式』的資料表。學(xué)號課程代碼成績001C00174001C00293002C00263002C00382002C00594檢驗「成績資料表」是否滿足BCNF規(guī)範(fàn)由於在我們完成4-4.7怎樣才叫做是好的關(guān)聯(lián)？正規(guī)化就是將一個大資料表「分割」成數(shù)個不重複的小資料表。從1NF到3NF，再利用BCNF來逐步檢驗資料表中「主鍵」由「多個欄位」組成的相依性問題，這是一連串改良關(guān)聯(lián)的過程。

可是，究竟要做到哪一個程度才算「足夠好」呢？通常我們會要求：就算不能作到BCNF，也要做到3NF才可以。4-4.7怎樣才叫做是好的關(guān)聯(lián)？正規(guī)化就4-5反正規(guī)化【引言】正規(guī)化只是建立資料表的原則，而非鐵律。如果過度正規(guī)化，反而導(dǎo)致資料存取的效率下降。因此，如果要以執(zhí)行效率(查詢速度)為優(yōu)先考量時，則我們還必須適當(dāng)?shù)姆凑?guī)化（De-normalization）。有時，過度的正規(guī)化，反而會造成資料處理速度上的困擾，因此，當(dāng)我們在進(jìn)行資料庫正規(guī)化的同時，可能也必須要測試系統(tǒng)執(zhí)行效率，當(dāng)效率不理想時，必須做適當(dāng)?shù)姆凑?guī)化，亦即將原來的第三階正規(guī)化降級為第二階正規(guī)化，甚至降到第一階正規(guī)化。但是，在進(jìn)行反正規(guī)化的同時，可能也會造成的資料重覆性問題。4-5反正規(guī)化【引言】4-5反正規(guī)化<續(xù)>

【定義】將原來的第三階正規(guī)化降級為第二階正規(guī)化，甚至降到第一階

正規(guī)化?！臼褂脮r機】查詢比例較大的環(huán)境?！痉治觥?.對「資料異動」觀點當(dāng)正規(guī)化愈多層，愈有利於資料的異動(包括：新增、修改及刪除)，

因為異動時只需針對某一個較小的資料表，可以避免資料的異?，F(xiàn)象。2.對「資料查詢」觀點當(dāng)正規(guī)化愈多層，愈不利於資料的查詢功能，因為資料查詢時往往

會合併許多個資料表，導(dǎo)致查詢效能降低。因此，「正規(guī)化論理」與「查詢合併原理」是存在相互衝突。4-5反正規(guī)化<續(xù)>【定義】將原來的第三階正規(guī)化降級為【舉例】假設(shè)我們在進(jìn)行正規(guī)化時，特別將「客戶資料表」中的「地址」分割成以下欄位：<1>正規(guī)化關(guān)聯(lián)客戶資料表(編號，姓名，郵遞區(qū)號)地址明細(xì)表(郵遞區(qū)號、城市、路名)【優(yōu)點】可以直接從每一個欄位當(dāng)作「關(guān)鍵字」來查詢?！纠纭坎樵儭父咝凼小够虿樵儭?06」或查詢「和平路」等?！具m用時機】租屋網(wǎng)站；可以讓使用者進(jìn)行「進(jìn)階」查詢?！救秉c】如果要查詢的資訊是要合併多個資料表時，將會影響執(zhí)行效率。因此，一般的做法還是讓地址「反正規(guī)化」。<2>反正規(guī)化關(guān)聯(lián)客戶資料表(編號，姓名，郵遞區(qū)號、城市、路名)【舉例】4-6結(jié)語基本上，建立E-RModel後已經(jīng)可以達(dá)到正規(guī)化的前三階(1NF,2NF,3NF)或是BCNF的步驟。因此，我們必須瞭解建立完整的資料庫結(jié)構(gòu)，可以用兩種方法來建構(gòu)：1.E-RModel(第三章介紹)2.資料庫正規(guī)化(第四章介紹)驗證E-RModel是否達(dá)到最佳化4-6結(jié)語基本上，建立E-RModel後已經(jīng)可以達(dá)到4-6結(jié)語<續(xù)>

1.如果是剛成立的企業(yè)想要電腦化，則是要從需求訪談開始，將訪談的資料需求進(jìn)行分析(情境)，然後建立實體關(guān)係模式(ER圖)，接下來依照關(guān)聯(lián)式的規(guī)則，對映成資料表。筆者認(rèn)為如果步驟一到步驟三都有確實時，對映後的資料表會與正規(guī)化的表格是一樣的。所以，正規(guī)化的步驟就不一定要進(jìn)行。如下圖所示：4-6結(jié)語<續(xù)>1.如果是剛成立的企業(yè)想要電腦化，則是4-6結(jié)語<續(xù)>

2.如果某一企業(yè)早期已經(jīng)人工作業(yè)，並且使用許多表單，筆者建議，可以直接進(jìn)行正規(guī)化。但是，如果人工作業(yè)的表單沒有完全依照企業(yè)的需求的設(shè)計時，則筆者建議，還是要依照步驟一到步驟三來進(jìn)行。如下圖所示：4-6結(jié)語<續(xù)>2.如果某一企業(yè)早期已經(jīng)人工作業(yè)，並且第四章

資料庫正規(guī)化課程名稱：資料庫系統(tǒng)授課老師：李春雄博士第四章

資料庫正規(guī)化課程名稱：資料庫系統(tǒng)本章學(xué)習(xí)目標(biāo)

1.讓讀者瞭解資料庫正規(guī)化的概念及目的。2.讓讀者瞭解資料庫正規(guī)化(Normalization)

程序及規(guī)則。本章學(xué)習(xí)目標(biāo)1.讓讀者瞭解資料庫正規(guī)化的概念及目的。本章內(nèi)容4-1正規(guī)化的概念4-2正規(guī)化的目的4-3功能相依(FunctionalDependence;FD)4-4資料庫正規(guī)化(Normalization)4-5反正規(guī)化(De-normalization)4-6結(jié)語本章內(nèi)容4-1正規(guī)化的概念前言一般的初學(xué)者在進(jìn)行資料庫設(shè)計時，以為用一個資料表就可以儲存全部的資料，或憑著自己的直覺而沒有經(jīng)過完整的規(guī)劃，就隨意的將資料表分割成許多小的資料表，這種設(shè)計方法，不但浪費儲存空間，更嚴(yán)重影響到資料庫不一致的現(xiàn)象，以致於DBA(資料庫管理師)維護(hù)困難。為了避免以上的問題產(chǎn)生，唯一的方法，就是在設(shè)計關(guān)聯(lián)式資料庫之前，一定先要完成資料的正規(guī)化(Normalization)。前言一般的初學(xué)者在進(jìn)行資料庫設(shè)計時，以為用4-1正規(guī)化的概念

資料庫是用來存放資料的地方，因此，如何妥善的規(guī)劃資料庫綱要

(DatabaseSchema)是一件很重要的工作，但是，資料庫綱要的設(shè)計必須要配合實務(wù)上的需要，因此，當(dāng)資料庫綱要設(shè)計完成後，如何檢視設(shè)計是否良好，就必需要使用正規(guī)化(Normalization)的方法論了。何謂正規(guī)化(Normalization)？就是結(jié)構(gòu)化分析與設(shè)計中，建構(gòu)「資料模式」所運用的一個技術(shù)，其目的是為了降低資料的「重覆性」與避免「更新異?！沟那闆r發(fā)生。

因此，就必須將整個資料表中重複性的資料剔除，否則在關(guān)聯(lián)表中會造成新增異常、刪除異常、修改異常的狀況發(fā)生。4-1正規(guī)化的概念資料庫是用來存放資料4-2正規(guī)化的目的一般而言，正規(guī)化的精神就是讓資料庫中重複的欄位資料減到最少，並且能快速的找到資料，以提高關(guān)聯(lián)性資料庫的效能?！灸康摹?.降低資料重複性(DataRedundancy)。2.避免資料更新異常(Anomalies)。4-2正規(guī)化的目的一般而言，正規(guī)化的精神一、降低資料重複性(DataRedundancy)

正規(guī)化的目的是什麼呢？簡單來說，就是降低資料重複的狀況發(fā)生。試想，當(dāng)校務(wù)系統(tǒng)的「學(xué)籍資料」分別存放在「教務(wù)處」與「學(xué)務(wù)處」時，不僅資料重覆儲存，浪費空間，更嚴(yán)重的是，當(dāng)學(xué)生姓名變更時，就必須要同時更改「教務(wù)處」與「學(xué)務(wù)處」的「學(xué)籍資料」，否則將導(dǎo)致資料不一致的現(xiàn)象，因此，資料庫如果沒有事先進(jìn)行正規(guī)化，將會增加應(yīng)用系統(tǒng)撰寫的困難，同時也會增加資料庫的處理負(fù)擔(dān)，所以降低資料重複性是「正規(guī)化」的重要工作。一、降低資料重複性(DataRedundancy)【方法】

將「教務(wù)處」與「學(xué)務(wù)處」中，把相同的資料項，抽出來組成一個新的資料表(學(xué)籍資料表)，如下圖所示：正規(guī)化：將兩個表格切成三個資料表說明：在正規(guī)化之後，「學(xué)籍資料表」的主鍵(P.K.)分別與「學(xué)務(wù)處資料表」的

外鍵(F.K.)及「教務(wù)處資料表」的外鍵(F.K.)進(jìn)行關(guān)聯(lián)，以產(chǎn)生關(guān)聯(lián)式資料庫?！痉椒ā繉ⅰ附虅?wù)處」與「學(xué)務(wù)處」中，把相同的資料項，抽出二、避免資料更新異常(Anomalies)

(一)新增異常(InsertAnomalies)新增某些資料時必須同時新增其他的資料，否則會產(chǎn)生新增異常現(xiàn)象。亦即在另一個實體的資料尚未插入之前，無法插入目前這個實體的資料。(二)修改異常(UpdateAnomalies)修改某些資料時必須一併修改其他的資料，否則會產(chǎn)生修改異?，F(xiàn)象。(三)刪除異常(DeleteAnomalies)刪除某些資料時必須同時刪除其他的資料，否則會產(chǎn)生刪除異?，F(xiàn)象。亦即刪除單一資料列造成多個實體的資訊遺失。二、避免資料更新異常(Anomalies)(一)新增異常(【實例】

假設(shè)某國立大學(xué)開設(shè)「網(wǎng)路碩士學(xué)分班」，其學(xué)員課程收費表如下所示。學(xué)員課程收費表【說明】在上面的學(xué)員課程收費表中雖然僅僅只有三個欄位，但是已不算是

一個良好的儲存結(jié)構(gòu)，因為此表格中有資料重覆現(xiàn)象?！纠纭坑行┱n程的費用在許多學(xué)員身上重覆出現(xiàn)(S0001與S0003；S0002與S0005)，因此可能會造成錯誤或不一致的異常(Anomalies)現(xiàn)象。【分析】從下一頁開始學(xué)號課號學(xué)分費S0001C0013000S0002C0024000S0003C0013000S0004C0035000S0005C0024000學(xué)員的選課需知如下：1.每一位學(xué)員只能選修一門課程。2.每一門課程均有收費標(biāo)準(zhǔn)。(C001為3000元，C002為4000元，C003為5000元)【實例】假設(shè)某國立大學(xué)開設(shè)「網(wǎng)路碩士學(xué)分班」，其學(xué)員課程【分析】三種可能的異常(Anomalies)現(xiàn)象

(一)新增異常

假設(shè)學(xué)校又要新增C004課程，但此課程無法立即新增到資料表中，除非至少有一位學(xué)員選修了C004這門課程?！痉治觥咳N可能的異常(Anomalies)現(xiàn)象(一)新增(二)修改異常

假如C002課程的學(xué)分費由4000元調(diào)整為4500元時，若「C002課程」有多位學(xué)員選修時，因此，修改「S0002」學(xué)員的學(xué)分費時，可能有些記錄未修改到(S0005)，造成資料的不一致現(xiàn)象。(二)修改異常(三)刪除異常

假設(shè)學(xué)員S0004退選時，同時也刪除C003這門課程，由於該課程只有S0004這位學(xué)員選修，因此若把這一筆記錄刪除，從此我們將失去C003這門課程及其學(xué)分費的資訊。(三)刪除異?！窘鉀Q方法】正規(guī)化

由於上述的分析，發(fā)現(xiàn)學(xué)員課程收費表並不是一個良好的儲存結(jié)構(gòu)，因此，我們就必須要採用4-4節(jié)所要討論的正規(guī)化，將學(xué)員課程收費表分割成兩個資料表，即「選課表」與「課程收費對照表」，因此，才不會發(fā)生上述的異常現(xiàn)象。學(xué)號課號學(xué)分費S0001C0013000S0002C0024000S0003C0013000S0004C0035000S0005C0024000課程收費表學(xué)號課號S0001C001S0002C002S0003C001S0004C003S0005C002選課表課號學(xué)分費C0013000C0024000C0035000課程收費對照表正規(guī)化正規(guī)化【解決方法】正規(guī)化由於上述的分析，發(fā)現(xiàn)學(xué)員課程收費表並不題目：建立「選課表」與「課程收費對照表」的關(guān)聯(lián)圖先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-2B.accdb

」步驟二：資料庫工具／資料庫關(guān)聯(lián)圖／「選課表」的「課號」外鍵拖曵到「課程收費對照表」的「課號」主鍵，此外，在「編輯關(guān)聯(lián)」對話方塊中，說明「關(guān)聯(lián)類型：一對多」，其中一的那方代表「主鍵」，而多的那方代表「外鍵」。步驟三：測試三種可能的異常現(xiàn)象。1.新增異常測試2.修改異常測試3.刪除異常測試您可以參考「完成檔\ch4-2B.accdb」【Access上機實作1】題目：建立「選課表」與「課程收費對照表」的關(guān)聯(lián)圖【Acce【

Access上機實作】請建立「正規(guī)化」後的兩個表格，「選課表」與「課程收費對照表」，並建立關(guān)聯(lián)圖學(xué)號課號S0001C001S0002C002S0003C001S0004C003S0005C002選課表課號學(xué)分費C0013000C0024000C0035000課程收費對照表資料庫名稱：ch4-2B.accdb【Access上機實作】請建立「正規(guī)化」後的兩個表格，4-3功能相依(FunctionalDependence;FD)

一、功能相依的概念【定義】

是指資料表中各欄位之間的相依性。亦即某欄位不能單獨存在，必須要和其他欄位一起存在時才有意義，稱這兩個欄位具有功能相依?！纠纭繉W(xué)生資料表【說明】在上面的資料表中,「姓名」欄位的值必須搭配「學(xué)號」欄位才有意義,則我們說『姓名欄位相依於學(xué)號欄位』。姓名學(xué)號性別系所電話地址4-3功能相依(FunctionalDependence換言之，在「學(xué)生資料表」中，「學(xué)號」決定了「姓名」，也決定了「性別」、「系所」、「電話」、「地址」等資訊，我們可以用以下圖示的方法來表示這些功能相依性?！痉治觥?/p>

1.學(xué)號→姓名

2.學(xué)號→{姓名，性別，系所，電話，地址}

3.學(xué)號：為決定因素(∵學(xué)號姓名)4.姓名，性別，系所，電話，地址：為相依因素因此，「學(xué)號」欄位為主鍵,做為唯一辨識該筆記錄的欄位?！感彰箼谖槐仨氁嘁漓丁笇W(xué)號」欄位,對此資料表來說「姓名」欄位才有意義；同理可證,「地址」欄位亦必須相依於「學(xué)號」欄位,才有意義。換言之，在「學(xué)生資料表」中，「學(xué)號」決定了「姓名」，也決定了二、功能相依(FD)的表示方式1.假設(shè)有一個資料表R，並且有三個欄位，分別為X,Y,Z，因此，我們就

可以利用一條數(shù)學(xué)式來表示：R={X,Y,Z}2.假設(shè)在R={X,Y,Z}數(shù)學(xué)式中，X和Y之間存在「功能相依」時，並且

存在Y功能相依於X，則我們可以利用以下的表示式：

(1)Y∝X(Y功能相依於X)(2)XY(X決定Y)若XY時，在FD的左邊X稱為決定因素(Determinant)

在FD的右邊Y稱為相依因素(Dependent)3.示意圖：學(xué)號(X)姓名(Y)二、功能相依(FD)的表示方式學(xué)號(X)姓名(Y)4-3.1完全功能相依

（FullFunctionalDependency）

【定義】假設(shè)在關(guān)聯(lián)表R(X,Y,Z)中，包含一組功能相依(X,Y)Z，如果我們從關(guān)聯(lián)表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z不存在，此時我們稱Z為「完全功能相依」於(X,Y)。反之，若(X,Y)Z存在，我們稱Z為「部份功能相依」於(X,Y)。【例如】{學(xué)號(X)，課號(Y)}→成績(Z)

這是「完全功能相依」如果從關(guān)聯(lián)表中移除課號(Y)，則功能相依(X)Z不存在因為，「學(xué)號」和「課號」兩者一起決定了「成績」，缺一不可。否則，只有一個學(xué)號對應(yīng)一個成績，無法得知該成績是那一門課程的分?jǐn)?shù)。亦即成績(Z)完全功能相依於{學(xué)號(X)，課號(Y)}4-3.1完全功能相依

（FullFun4-3.2部份功能相依

（PartialFunctionalDependency）

【定義】假設(shè)在關(guān)聯(lián)表R(X,Y,Z)中包含一組功能相依(X,Y)Z，如果我們從關(guān)聯(lián)表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z存在，此時我們稱Z為「部份功能相依」於(X,Y)?！纠纭縶學(xué)號(X)，身份證字號(Y)}→姓名(Z)

這是「部份功能相依」如果從關(guān)聯(lián)表中移除身份證字號(Y)，則功能相依(X)Z存在因為，「學(xué)號」也可以決定「姓名」，他們之間也具有功能相依性。4-3.2部份功能相依

（PartialF4-3.3遞移相依

（TransitiveDependency）

【定義】是指在二個欄位間並非直接相依,而是借助第三個欄位來達(dá)成資料

相依的關(guān)係?！纠纭縔相依於X；而Z又相依於Y,如此X與Z之間就是遞移相依的關(guān)係?！臼疽鈭D】在上面的關(guān)聯(lián)表R(X,Y,Z)中包含一組相依XY,YZ，則XZ，此時我們稱Z遞移相依於X。

4-3.3遞移相依

（Transitive【舉例】

課程代號→老師編號老師編號→老師姓名

這是遞移相依因為，「課程代號」可以決定「老師編號」，並且「老師編號」又可以決定「老師姓名」，因此，「課程代號」與「老師姓名」之間存在遞移相依性。遞移相依性【舉例】課程代號→老師編號遞移相依性4-4資料庫正規(guī)化(Normalization)

【定義】是指將原先關(guān)聯(lián)(表格)的所有資訊，在「分解」之後，仍能由數(shù)個新關(guān)聯(lián)(表格)中經(jīng)過「合併」得到相同的資訊。即所謂的「無損失分解(Losslessdecomposition)」的觀念。

【無損失分解觀念】當(dāng)關(guān)聯(lián)表R被「分解」成數(shù)個關(guān)聯(lián)表R1,R2,…,Rn時，則可以再透過「合併」R1R2…Rn得到相同的資訊R。如下圖所示。

分解合併合併合併分解分解4-4資料庫正規(guī)化(Normalization)【定義】【實例】分解合併註：分解：是指透過「正規(guī)化」技術(shù)，將一個大資料表分割成二個小資料表?！侗菊陆榻B》

合併：是指透過「合併」理論，將數(shù)個小資料表整合成一個大資料表?！兜诎苏陆榻B》【實例】分解合併註：分解：是指透過「正規(guī)化」技術(shù)，將一個大資題目：利用「分割」後的表格，再進(jìn)行「合併」先備技能：1.參考附錄一Access2010的基本操作2.建立關(guān)聯(lián)式資料庫實作步驟：步驟一：開啟資料庫

開啟附書光碟中「01_Access2010(範(fàn)例資料庫)\ch4\素材檔\

ch4-4.accdb

」步驟二：將分割後的兩個表格，利用SQL合併(已完成)步驟三：以證明無損失分解觀念，亦即將原先關(guān)聯(lián)(表格)的所有資訊，在「分解」之後，仍能由數(shù)個新

關(guān)聯(lián)(表格)中經(jīng)過「合併」得到相同的資訊。您可以參考「完成檔\ch4-4.accdb」【Access上機實作2】題目：利用「分割」後的表格，再進(jìn)行「合併」【Access上4-4.1正規(guī)化示意圖正規(guī)化就是對一個「非正規(guī)化」的原始資料表，進(jìn)行一連串的「分割」，並且分割成數(shù)個「不重複」儲存的資料表。如下圖所示：在上圖中，利用一連串的「分割」，亦即利用所謂的「正規(guī)化的規(guī)則」，循序漸進(jìn)的將一個「重複性高」的資料表分割成數(shù)個「重複性低」或「沒有重複性」的資料表。分割4-4.1正規(guī)化示意圖正規(guī)化就是對一個「非正規(guī)化」的原始4-4.2正規(guī)化的規(guī)則引言

資料庫在正規(guī)化時會有一些規(guī)則，並且每條規(guī)則都稱為「正規(guī)形式」。如果符合第一條規(guī)則，則資料庫就稱為「第一正規(guī)化形式(1NF)」。如果符合前二條規(guī)則，則資料庫就被視為屬於「第二正規(guī)化形式(2NF)」。雖然資料庫的正規(guī)化最多可以進(jìn)行到第五正規(guī)化形式，但是在實務(wù)上，

BCNF被視為大部分應(yīng)用程式所需的最高階正規(guī)形式。4-4.2正規(guī)化的規(guī)則引言從上圖中，我們可以清楚得知，正規(guī)化是循序漸進(jìn)的過程，亦即資料表必須滿足第一正規(guī)化的條件之後，才能進(jìn)行第二正規(guī)化。換言之，第二正規(guī)化必須建立在符合第一正規(guī)化的資料表上，依此類推。從上圖中，我們可以清楚得知，正規(guī)化是循序漸進(jìn)正規(guī)化步驟

在資料表正規(guī)化的過程(1NF到BCNF)中,每一個階段都是以欄位的「相依性」,做為分割資料表的依據(jù)之一。其完整的正規(guī)化步驟如下圖所示：正規(guī)化步驟在資料表正規(guī)化的過程(1NF到BCNF正規(guī)化步驟<續(xù)>

1.第一正規(guī)化(FirstNormalForm;1NF)：由E.F.Codd提出。滿足所有記錄中的屬性內(nèi)含值都是基元值(AtomicValue)。即無重覆項目群。2.第二正規(guī)化(SecondNormalForm;2NF)：由E.F.Codd提出。

符合1NF且每一非鍵值欄位「完全功能相依」於主鍵。

即不可「部分功能相依」於主鍵。3.第三正規(guī)化(ThirdNormalForm;3NF)：由E.F.Codd提出。

符合2NF且每一非鍵值欄位非「遞移相依」於主鍵。

即除去「遞移相依」問題。正規(guī)化步驟<續(xù)>1.第一正規(guī)化(FirstNormal正規(guī)化步驟<續(xù)>

4.Boyce-Codd正規(guī)化型式(Boyce-CoddNormalForm;BCNF)：

由R.F.Boyce與E.F.Codd共同提出。

符合3NF且每一決定因素(Determinant)皆是候選鍵，簡稱為BCNF。5.第四正規(guī)化(FourthNormalForm;4NF)：由R.Fagin提出。

符合BCNF，再除去所有的多值相依。6.第五正規(guī)化(FifthNormalForm;5NF)：由R.Fagin提出。

符合4NF，且沒有合併相依。正規(guī)化步驟<續(xù)>4.Boyce-Codd正規(guī)化型式(Bo4-4.3第一正規(guī)化(1NF)

【定義】

是指在資料表中的所有記錄之屬性內(nèi)含值都是基元值(AtomicValue)。

亦即無重覆項目群?！緦嵗考僭O(shè)現(xiàn)在有一份某某科技大學(xué)的學(xué)生選課資料表，如表4-1(a)所示：表4-1(a)學(xué)生選課資料表4-4.3第一正規(guī)化(1NF)【定義】我們可以將表4-1