課程名稱：資料庫系統 授課老師：李春雄 博士

Presentation on theme: "課程名稱：資料庫系統 授課老師：李春雄 博士"— Presentation transcript:

1 課程名稱：資料庫系統 授課老師：李春雄 博士
第 四 章 資料庫正規化 課程名稱：資料庫系統 授課老師：李春雄 博士 各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」， 今天所要為各位介紹的是第一章「資料結構導論」

2 本章學習目標 1.讓讀者瞭解資料庫正規化的概念及目的。 2.讓讀者瞭解資料庫正規化(Normalization) 程序及規則。 開始：
本章學習目標 有二項：

3 本章內容 4-1 正規化的概念 4-2 正規化的目的 4-3 功能相依(Functional Dependence; FD)
4-1 正規化的概念 4-2 正規化的目的 4-3 功能相依(Functional Dependence; FD) 4-4 資料庫正規化(Normalization) 4-5 反正規化(De-normalization) 4-6 結語 1-1 認識資料與資訊的關係: 其中，「資料」轉換成「資訊」必須要經過一連串處理過程，而這一連串的處理過程就是透過「程式」來處理。 1-2 何謂資料結構？ 「資料結構」(Data Structures)主要是探討如何將資料更有組織地存放到電腦記憶體中，以提昇程式之執行效率的一 門學問。 1-3 何謂演算法?演算法就是「解決問題的方法」 1-4 程式設計概念: 步驟1. 分析所要解決的問題 步驟2. 設計解題的步驟 步驟3. 編寫程式 步驟4. 上機測試、偵測錯誤 步驟5. 編寫程 式說明書 1-5 結構化程式設計 利用「由上而下」的技巧，將程式分解成許多個獨立功能的模組。並且每一個模組都是由三種結構所組成。分別為循序結構、選擇結構及重複結構。 1-6 演算法的效率評估 指用來計算某些演算法所撰寫的程式，在經過編譯之後，實際執行所需要的時間。

4 前言　 一般的初學者在進行資料庫設計時，以為用一個資料表就可以儲存全部的資料，或憑著自己的直覺而沒有經過完整的規劃，就隨意的將資料表分割成許多小的資料表，這種設計方法，不但浪費儲存空間，更嚴重影響到資料庫不一致的現象，以致於DBA(資料庫管理師)維護困難。為了避免以上的問題產生，唯一的方法，就是在設計關聯式資料庫之前，一定先要完成資料的正規化(Normalization)。

5 4-1 正規化的概念　 資料庫是用來存放資料的地方，因此，如何妥善的規劃資料庫綱要 ( Database Schema )是一件很重要的工作，但是，資料庫綱要的設計必須要配合實務上的需要，因此，當資料庫綱要設計完成後，如何檢視設計是否良好，就必需要使用正規化(Normalization)的方法論了。 何謂正規化(Normalization)？就是結構化分析與設計中，建構「資料模式」所運用的一個技術，其目的是為了降低資料的「重覆性」與避免「更新異常」的情況發生。 因此，就必須將整個資料表中重複性的資料剔除，否則在關聯表中會造成新增異常、刪除異常、修改異常的狀況發生。

6 4-2 正規化的目的 一般而言，正規化的精神就是讓資料庫中重複的欄位資料減到最少，並且能快速的找到資料，以提高關聯性資料庫的效能。 【目的】
4-2 正規化的目的　 一般而言，正規化的精神就是讓資料庫中重複的欄位資料減到最少，並且能快速的找到資料，以提高關聯性資料庫的效能。 【目的】 1.降低資料重複性(Data Redundancy)。 2.避免資料更新異常(Anomalies)。

7 一、降低資料重複性(Data Redundancy)
正規化的目的是什麼呢？簡單來說，就是降低資料重複的狀況發生。 試想，當校務系統的「學籍資料」分別存放在「教務處」與「學務處」時，不僅資料重覆儲存，浪費空間，更嚴重的是，當學生姓名變更時，就必須要同時更改「教務處」與「學務處」的「學籍資料」，否則將導致資料不一致的現象，因此，資料庫如果沒有事先進行正規化，將會增加應用系統撰寫的困難，同時也會增加資料庫的處理負擔，所以降低資料重複性是「正規化」的重要工作。

8 【方法】 將「教務處」與「學務處」中，把相同的資料項，抽出來組成一個新的資料表 (學籍資料表)，如下圖所示：
【方法】 　 將「教務處」與「學務處」中，把相同的資料項，抽出來組成一個新的資料表 (學籍資料表)，如下圖所示： 正規化：將兩個表格切成三個資料表 說明：在正規化之後，「學籍資料表」的主鍵(P.K.)分別與「學務處資料表」的 外鍵(F.K.)及「教務處資料表」的外鍵(F.K.)進行關聯，以產生關聯式資料庫。

9 二、避免資料更新異常(Anomalies)
(一)新增異常(Insert Anomalies) 新增某些資料時必須同時新增其他的資料，否則會產生新增異常現象。亦即在另一個實體的資料尚未插入之前，無法插入目前這個實體的資料。 (二)修改異常(Update Anomalies) 修改某些資料時必須一併修改其他的資料，否則會產生修改異常現象。 (三)刪除異常(Delete Anomalies) 刪除某些資料時必須同時刪除其他的資料，否則會產生刪除異常現象。亦即刪除單一資料列造成多個實體的資訊遺失。

10 【實例】 【分析】從下一頁開始 假設某國立大學開設「網路碩士學分班」，其學員課程收費表如下所示。 學員課程收費表 學員的選課需知如下：
【實例】 　 假設某國立大學開設「網路碩士學分班」，其學員課程收費表如下所示。 學員課程收費表 【說明】在上面的學員課程收費表中雖然僅僅只有三個欄位，但是已不算是 一個良好的儲存結構，因為此表格中有資料重覆現象。 【例如】有些課程的費用在許多學員身上重覆出現(S0001與S0003；S0002與 S0005)，因此可能會造成錯誤或不一致的異常(Anomalies)現象。 【分析】從下一頁開始 學員的選課需知如下： 1. 每一位學員只能選修一門課程。 2. 每一門課程均有收費標準。(C001為3000元， C002為4000元，C003為5000元) 學號 課號 學分費 S0001 C001 3000 S0002 C002 4000 S0003 S0004 C003 5000 S0005

11 【分析】三種可能的異常(Anomalies)現象
(一)新增異常 假設學校又要新增C004課程，但此課程無法立即新增到資料表中，除非至少有一位學員選修了C004這門課程。

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

13 (三)刪除異常 假設學員S0004退選時，同時也刪除C003這門課程，由於該課程只有S0004這位學員選修，因此若把這一筆記錄刪除，從此我們將失去C003這門課程及其學分費的資訊。

14 【解決方法】正規化　 由於上述的分析，發現學員課程收費表並不是一個良好的儲存結構，因此，我們就必須要採用4-4節所要討論的正規化，將學員課程收費表分割成兩個資料表，即「選課表」與「課程收費對照表」，因此，才不會發生上述的異常現象。 課程收費表 學號 課號 學分費 S0001 C001 3000 S0002 C002 4000 S0003 S0004 C003 5000 S0005 正規化 正規化 選課表 課程收費對照表 學號 課號 S0001 C001 S0002 C002 S0003 S0004 C003 S0005 課號 學分費 C001 3000 C002 4000 C003 5000

15 【Access上機實作1】 題目：建立「選課表」與「課程收費對照表」的關聯圖 先備技能：1.參考附錄一 Access 2010的基本操作
2.建立關聯式資料庫 實作步驟： 步驟一：開啟資料庫 開啟附書光碟中「 01_Access 2010(範例資料庫)\ch4\素材檔\ ch4-2B.accdb 」 步驟二： 資料庫工具／資料庫關聯圖／ 「選課表」的「課號」外鍵拖曵到「課程收費對照表」的「課號」主鍵，此外，在「編輯關聯」對話方塊中，說明「關聯類型：一對多」，其中一的那方代表「主鍵」，而多的那方代表「外鍵」 。 步驟三：測試三種可能的異常現象。 1.新增異常測試 2.修改異常測試 3.刪除異常測試 您可以參考「完成檔\ ch4-2B.accdb 」

16 【 Access 上機實作】 資料庫名稱：ch4-2B.accdb
請建立「正規化」後的兩個表格， 「選課表」與「課程收費對照表」，並建立關聯圖 選課表 課程收費對照表 學號 課號 S0001 C001 S0002 C002 S0003 S0004 C003 S0005 課號 學分費 C001 3000 C002 4000 C003 5000 圖： 接下來，我們可以從圖1-1來說明「資料與資訊的關係」 當我們「輸入原始成績」之後，如何輸出一張成績單呢？那就必須要透過「程式」來進行處理， 而在資料結構中，程式=資料結構+演算法

17 4-3 功能相依(Functional Dependence; FD)
一、功能相依的概念 【定義】 是指資料表中各欄位之間的相依性。亦即某欄位不能單獨存在，必須要和其他欄位一起存在時才有意義，稱這兩個欄位具有功能相依。 【例如】學生資料表 【說明】 在上面的資料表中, 「姓名」欄位的值必須搭配「學號」欄位才有意義, 則我們說『姓名欄位相依於學號欄位』。 姓名 學號 性別 系所 電話 地址

18 換言之，在「學生資料表」中，「學號」決定了「姓名」，也決定了「性別」、「系所」、「電話」、「地址」等資訊，我們可以用以下圖示的方法來表示這些功能相依性。
【分析】 1. 學號 → 姓名 2. 學號 → {姓名，性別，系所，電話，地址} 3. 學號：為決定因素(∵學號姓名) 4. 姓名，性別，系所，電話，地址：為相依因素 因此，「學號」欄位為主鍵, 做為唯一辨識該筆記錄的欄位。「姓名」欄位必須要相依於「學號」欄位, 對此資料表來說「姓名」欄位才有意義；同理可證,「地址」欄位亦必須相依於「學號」欄位, 才有意義。

19 1.假設有一個資料表R，並且有三個欄位，分別為X,Y,Z，因此，我們就 可以利用一條數學式來表示：R={X,Y,Z}
二、功能相依(FD)的表示方式 1.假設有一個資料表R，並且有三個欄位，分別為X,Y,Z，因此，我們就 可以利用一條數學式來表示：R={X,Y,Z} 2.假設在R={X,Y,Z}數學式中，X和Y之間存在「功能相依」時，並且 存在Y功能相依於X，則我們可以利用以下的表示式： (1) Y ∝ X (Y功能相依於X) (2) XY (X決定Y) 若XY時，在FD的左邊X稱為決定因素(Determinant) 在FD的右邊Y稱為相依因素(Dependent) 3.示意圖： 學號(X) 姓名(Y)

20 4-3.1 完全功能相依 （Full Functional Dependency）
【定義】 假設在關聯表R(X,Y,Z)中，包含一組功能相依(X,Y)Z，如果我們從關聯表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z不存在，此時我們稱Z為「完全功能相依」於(X,Y)。 反之，若(X,Y)Z存在，我們稱Z為「部份功能相依」於(X,Y) 。 【例如】 {學號(X)，課號(Y)} → 成績(Z) 這是「完全功能相依」 如果從關聯表中移除課號(Y)，則功能相依(X) Z不存在 因為，「學號」和「課號」兩者一起決定了「成績」，缺一不可。 否則，只有一個學號對應一個成績，無法得知該成績是那一門課程的分數。 亦即成績(Z)完全功能相依於{學號(X)，課號(Y)}

21 4-3.2 部份功能相依 （Partial Functional Dependency）
【定義】 假設在關聯表R(X,Y,Z)中包含一組功能相依(X,Y)Z，如果我們從關聯表R中移除任一屬性X或Y時，則使得這個功能相依(X,Y)Z存在，此時我們稱Z為「部份功能相依」於(X,Y)。 【例如】 {學號(X) ，身份證字號(Y)} → 姓名(Z)  這是「部份功能相依」 如果從關聯表中移除身份證字號(Y)，則功能相依(X)Z存在 因為，「學號」也可以決定「姓名」，他們之間也具有功能相依性。

22 4-3.3 遞移相依 （Transitive Dependency）
【定義】 是指在二個欄位間並非直接相依, 而是借助第三個欄位來達成資料 相依的關係。 【例如】 Y 相依於 X；而 Z 又相依於Y, 如此 X 與 Z 之間就是遞移相依的關係。 【示意圖】 在上面的關聯表R(X,Y,Z)中包含一組相依XY,YZ，則XZ，此時我們稱Z遞移相依於X。

23 【舉例】 課程代號 → 老師編號 老師編號→ 老師姓名  這是遞移相依
【舉例】　 課程代號 → 老師編號 老師編號→ 老師姓名  這是遞移相依 因為，「課程代號」可以決定「老師編號」，並且「老師編號」又可以決定「老師姓名」，因此，「課程代號」與「老師姓名」之間存在遞移相依性。 遞移相依性

24 4-4 資料庫正規化(Normalization)
【定義】 是指將原先關聯(表格)的所有資訊，在「分解」之後，仍能由數個新關聯(表格)中經過「合併」得到相同的資訊。即所謂的「無損失分解(Lossless decomposition)」的觀念。 【無損失分解觀念】 當關聯表R被「分解」成數個關聯表R1, R2, …, Rn 時，則可以再透過「合併」R1 R … Rn得到相同的資訊R。如下圖所示。 分解 合併 合併 合併 分解 分解

25 【實例】 註：分解：是指透過「正規化」技術，將一個大資料表分割成二個小資料表。 分解 合併 《本章介紹》
合併：是指透過「合併」理論，將數個小資料表整合成一個大資料表。 《第八章介紹》

26 【Access上機實作2】 題目：利用「分割」後的表格，再進行「合併」 先備技能：1.參考附錄一 Access 2010的基本操作
2.建立關聯式資料庫 實作步驟： 步驟一：開啟資料庫 開啟附書光碟中「 01_Access 2010(範例資料庫)\ch4\素材檔\ ch4-4.accdb 」 步驟二： 將分割後的兩個表格，利用SQL合併(已完成) 步驟三：以證明無損失分解觀念，亦即將原先關聯(表格)的所有資訊，在「分解」之後，仍能由數個新 關聯(表格)中經過「合併」得到相同的資訊。 您可以參考「完成檔\ ch4-4.accdb 」

27 4-4.1 正規化示意圖 正規化就是對一個「非正規化」的原始資料表，進行一連串的「分割」，並且分割成數個「不重複」儲存的資料表。如下圖所示：
4-4.1 正規化示意圖　 正規化就是對一個「非正規化」的原始資料表，進行一連串的「分割」，並且分割成數個「不重複」儲存的資料表。如下圖所示： 在上圖中，利用一連串的「分割」，亦即利用所謂的「正規化的規則」，循序漸進的將一個「重複性高」的資料表分割成數個「重複性低」或「沒有重複性」的資料表。 分割

28 4-4.2 正規化的規則　 引言 資料庫在正規化時會有一些規則，並且每條規則都稱為「正規形式」。如果符合第一條規則，則資料庫就稱為「第一正規化形式(1NF)」。如果符合前二條規則，則資料庫就被視為屬於「第二正規化形式(2NF)」。雖然資料庫的正規化最多可以進行到第五正規化形式，但是在實務上， BCNF被視為大部分應用程式所需的最高階正規形式。

29 從上圖中，我們可以清楚得知，正規化是循序漸進的過程，亦即資料表必須滿足第一正規化的條件之後，才能進行第二正規化。換言之，第二正規化必須建立在符合第一正規化的資料表上，依此類推。

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

31 正規化步驟<續> 1.第一正規化(First Normal Form; 1NF) ：由 E.F.Codd 提出。
正規化步驟<續>　 1.第一正規化(First Normal Form; 1NF) ：由 E.F.Codd 提出。 滿足所有記錄中的屬性內含值都是基元值(Atomic Value)。 即無重覆項目群。 2.第二正規化(Second Normal Form; 2NF) ：由 E.F.Codd 提出。 符合1NF且每一非鍵值欄位「完全功能相依」於主鍵。 即不可「部分功能相依」於主鍵。 3.第三正規化(Third Normal Form; 3NF) ：由 E.F.Codd 提出。 符合2NF且每一非鍵值欄位非「遞移相依」於主鍵。 即除去「遞移相依」問題。

32 正規化步驟<續> 4.Boyce-Codd正規化型式(Boyce-Codd Normal Form ;BCNF) ：
正規化步驟<續>　 4.Boyce-Codd正規化型式(Boyce-Codd Normal Form ;BCNF) ： 由 R.F. Boyce 與 E.F.Codd 共同提出。 符合3NF且每一決定因素(Determinant)皆是候選鍵，簡稱為BCNF。 5.第四正規化(Fourth Normal Form; 4NF) ：由 R. Fagin 提出。 符合BCNF，再除去所有的多值相依。 6.第五正規化(Fifth Normal Form; 5NF) ：由 R. Fagin 提出。 符合4NF，且沒有合併相依。

33 4-4.3 第一正規化(1NF) 【定義】 是指在資料表中的所有記錄之屬性內含值都是基元值(Atomic Value)。 亦即無重覆項目群。
【實例】 假設現在有一份某某科技大學的學生選課資料表，如表4-1(a)所示： 表4-1(a) 學生選課資料表

34 我們可以將表4-1(a)的原始資料利用二維表格來儲存，如表4-1(b)。 表4-1(a) 學生選課資料表

35 因此，我們發現有許多屬性的內含值都具有二個或二個以上的值(亦稱為重複資料項目)，其原因：尚未進行第一正規化。
表4-1(b)未正規化的資料表：學生選課資料報表 ■未符合 1NF 資料表的「缺點」 以上資料表中的『課程代碼』、『課程名稱』、『學分數』、『必選修』、『成績』、『老師編號』及「老師姓名」欄位的長度無法確定，因為學生要選修多少門課程，無法事先得知(李碩安同學選了2門，李碩崴同學選了3門)，因此，必須要預留很大的空間給這七個欄位, 如此反而造成儲存空間的浪費。 重複資料項目

36 第一正規化的規則 1.每一個欄位只能有一個基元值(Atomic)即單一值。 例如：課程名稱欄位中不能存入兩科或兩科以上的課程名稱。
第一正規化的規則　 1.每一個欄位只能有一個基元值(Atomic)即單一值。 例如：課程名稱欄位中不能存入兩科或兩科以上的課程名稱。 2. 沒有任何兩筆以上的資料是完全重覆。 3.資料表中有主鍵, 而其他所有的欄位都相依於「主鍵」。 例如1：姓名與性別欄位都相依於「學號」欄位。 例如2：課程名稱、學分數、必選修、老師編號及老師姓名相依於「課程代 碼」欄位。 例如3：「成績」欄位相依於「學號」與「課程代碼」欄位。 《深入探討在下一頁》

37 《深入探討》 Q：為什麼「成績」欄位一定要相依於「學號」與「課程代碼」欄位？ 分析一：
《深入探討》 　 Q：為什麼「成績」欄位一定要相依於「學號」與「課程代碼」欄位？ 分析一： 如果「成績」欄位本身單獨存在時，則沒有意義，因為只有「成績」卻無法讓同學或老師清楚得知該「成績」是屬於哪一位學生的哪一門課的成績。 分析二： 如果「成績」欄位只相依於「課程編號」也是沒有意義的，因為只有「成績」也是無法讓同學或老師清楚得知該「成績」是屬於哪一位學生所修課的成績。 成績 74 93 沒有意義 課程代碼 成績 C001 74 C002 93 沒有意義

38 分析三： 如果「成績」欄位只相依於「學號」也是沒有意義的，因為只有「成績」也是無法讓同學或老師清楚得知該「成績」是屬於哪一門課的成績。 分析四： 但是，如果「成績」欄位相依於「課程編號」及「學號」二個欄位時, 就可 以了解某個學生修某堂課的成績, 這樣的成績資料才有意義。 學號 成績 001 74 93 沒有意義 學號 課程代碼 成績 001 C001 74 C002 93 有意義

39 第一正規化的作法：　 【作法】將重複的資料項分別儲存到不同的記錄中, 並加上適當的主鍵。 步驟一：檢查是否存在「重複資料項」

40 步驟二：將重複資料項分別儲存到不同的記錄中, 並加上適當的主鍵
未經正規化前的學生選課表 經過正規化後的學生選課表(1NF) 重複資料項 儲存到不同的記錄

41 經過正規化後的學生選課表(1NF) 在經由第一正規化之後，使得每一個欄位內只能有一個資料(基元值)。雖然增加了許多記錄, 但每一個欄位的「長度」及「數目」都可以固定, 而且我們可用「課程代碼」欄位加上「學號」欄位當作主鍵,使得在查詢某學生修某課程的「成績」時, 就非常方便而快速了。

42 【Access上機實作3】 題目：第一正規化後的結果 先備技能：1.參考附錄一 Access 2010的基本操作 2.建立關聯式資料庫
實作步驟： 步驟一：開啟資料庫 開啟附書光碟中「 01_Access 2010(範例資料庫)\ch4\素材檔\ ch4-4-3(1NF) 」 步驟二： 以證明在1NF之後， 1.每一個欄位只能有一個基元值。 例如：課程名稱欄位中不能存入兩科或兩科以上的課程名稱。 2. 沒有任何兩筆以上的資料是完全重覆。 3.資料表中有主鍵, 而其他所有的欄位都相依於「主鍵」 您可以參考「完成檔\ ch4-4-3(1NF)」

43 4-4.4 第二正規化(2NF)　 在完成了第一正規化之後，讀者是否發現在資料表中產生許多重複的資料。如此, 不但浪費儲存的空間, 更容易造成新增、修改及刪除資料時的異常狀況，說明如下。 (1) 新增異常檢查(Insert Anomaly) 無法先新增課程資料，如「課程代碼」及「課程名稱」，要等選課之後，才能新增。 原因：以上的新增動作違反「實體完整性規則」，因為，主鍵或複合主鍵不可以為空值 NULL。

44 (2)修改異常檢查(Update Anomaly)
「網頁設計」課程重覆多次，因此，修改「網頁設計」課程的成績時，可能有些記錄未修改到，造成資料的不一致現象。 例如：有選「網頁設計」課程的同學之成績各加5分，可能會有些同學 有加分，而有些同學卻沒有加分，導致資料不一致的情況。

45 綜合上述的三種異常現象，所以, 我們必須進行「第二階正規化」, 來消除這些問題。
(3)刪除異常檢查(Delete Anomaly) 當刪除#4學生的記錄時，同時也會刪除課程名稱、學分數及相關的資料。 所以導致「計概」課程的2學分數也同時被刪除了。 綜合上述的三種異常現象，所以, 我們必須進行「第二階正規化」, 來消除這些問題。

46 第二正規化的規則　 如果資料表符合以下的條件, 我們說這個資料表符合第二階正規化的形式 (Second Normal Form, 簡稱 2NF)： 符合1NF 每一非鍵屬性(如：姓名、性別…)必須「完全相依」於主鍵(學號)；即不可「部分功能相依」於主鍵。 換言之，「部分功能相依」只有當「主鍵」是由「多個欄位」組成時才會發生(亦即複合主鍵)，也就是當某些欄位只與「主鍵中的部分欄位」有「相依性」, 而與另一部分的欄位沒有相依性。

47 第二正規化的作法 分割資料表；亦即將「部分功能相依」的欄位「分割」出去， 再另外組成「新的資料表」。其步驟如下：
第二正規化的作法　 分割資料表；亦即將「部分功能相依」的欄位「分割」出去， 再另外組成「新的資料表」。其步驟如下： 步驟一：檢查是否存在「部分功能相依」 「姓名」只相依於「學號」 「課程名稱」只相依於「課程代碼」 在上面的資料表中，主鍵是由「學號+課程代碼」兩個欄位所組成，但「姓名」和「性別」只與「學號」有「相依性」，亦即(姓名，性別)相依於學號，而「課程名稱」只與「課程代碼」有「相依性」，亦即(課程名稱，學分數，必選修，老師編號，老師姓名)相依於課程代碼。 因此，學號是複合主鍵(學號,課程代碼)的一部份。 ∴存在部分功能相依。

48 步驟二： 將「部分功能相依」的欄位分割出去，再另外組成新的資料表 我們將「選課資料表」分割成三個較小的資料表 (加「底線」的欄位為主鍵)：
一、學生資料表(學號，姓名，性別) 二、成績資料表(學號，課程代碼，成績) 學號 姓名 性別 001 李碩安 男 002 李碩崴 學號 課程代碼 成績 001 C001 74 C002 93 002 63 C003 82 C005 94

49 三、課程資料表(課程代碼，課程名稱，學分數，必選修， 老師編號，老師姓名)
在第二正規化之後，產生三個資料表，分別為學生資料表、成績資料表及課程資料表，除了「課程資料表」之外，其餘兩個資料表(學生資料表與成績資料表)都已符合2NF, 3NF及BCNF。 課程代碼 課程名稱 學分數 必選修 老師編號 老師姓名 C001 程式語言 4 必 T001 李安 C002 網頁設計 3 選 T002 張三 C003 計　　概 2 T003 李四 C005 網路教學 T005 王五

50 【Access上機實作4】 題目：第二正規化後的結果 先備技能：1.參考附錄一 Access 2010的基本操作 2.建立關聯式資料庫
實作步驟： 步驟一：開啟資料庫 開啟附書光碟中「 01_Access 2010(範例資料庫)\ch4\素材檔\ ch4-4-3(2NF) 」 步驟二： 以證明在2NF之後， 將「部分功能相依」的欄位「分割」出去，另外分割成三個資料表「學生資料表」、 「成績資料表」及「課程資料表」。 您可以參考「完成檔\ ch4-4-3(2NF) 」

51 4-4.5 第三正規化(3NF) 在完成了第二正規化之後，其實還存在以下三種異常現象,亦即新增、刪除或更新資料時的異常狀況，說明如下：
(1)新增異常(Insert Anomaly) 以上無法先新增老師資料，要等確定課程代碼之後，才能輸入。 原因為：新增動作違反「實體完整性規則」 ，因為主鍵或複合主鍵不可以為空值NULL。

52 (2)修改異常(Update Anomaly)
假如「李安」老師開設多門課程時，則欲修改「李安」老師姓名為「李碩安」時，可能有些記錄未修改到，造成資料的不一致現象。 未修改到

53 綜合上述的三種異常現象，所以, 我們必須進行「第三階正規化」, 來消除這些問題。
(3)刪除異常(Delete Anomaly) 當刪除#1課程的記錄時，同時也刪除老師編號T001。 所以導致老師編號T001及老師姓名的資料也同時被刪除了。 綜合上述的三種異常現象，所以, 我們必須進行「第三階正規化」, 來消除這些問題。 記錄 課程代碼 課程名稱 學分數 必選修 老師編號 老師姓名 #1 C001 程式語言 4 必 T001 李安 #2 C002 網頁設計 3 選 T002 張三 #3 C003 計　　概 2 T003 李四 #4 C005 網路教學 T005 王五

54 第三正規化的規則　 如果資料表符合以下條件, 我們就說這個資料表符合第三階正規化的形式 (Third Normal Form, 簡稱 3NF)： 符合2NF 各欄位與「主鍵」之間沒有「遞移相依」的關係。 【如何找遞移相依呢？ 】 若要找出資料表中各欄位與「主鍵」之間的遞移相依性, 最簡單的方法就是從左到右掃瞄資料表中各欄位有沒有『與主鍵無關的相依性』存在。 可能的情況如下： 如果有存在時，則代表有「遞移相依」的關係 2. 如果有不存在時，則代表沒有「遞移相依」的關係

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

56 上述「課程資料表」中的[課程名稱]、[學分數]、[必選修]、[老師編號]都直接相依於主鍵[課程代碼](簡單的說，這些都是課程資料的必需欄位)，而[老師姓名]是直接相依於[老師編號]，然後才間接相依於[課程代碼]，它並不是直接相依於[課程代碼]，稱為「遞移相依」『Transitive Dependency』或「間接相依」。例如：當AB, BC，則AC(稱為遞移相依)。因此，在「課程資料表」中存在「遞移相依」關係現象，

57 因此，我們將「課程資料表」分割為二個資料表，並且利用外鍵(F.K.)來連接二個資料表。如下圖所示。
步驟二：將「遞移相依」的欄位「分割」出去，再另外組成「新的資料表」 因此，我們將「課程資料表」分割為二個資料表，並且利用外鍵(F.K.)來連接二個資料表。如下圖所示。

58 第三正規化後的四個表格　 在我們完成第三正規化後，共產生了四個表格，如下表所示： 第二正規化產生的表格 第三正規化產生的表格

59 【Access上機實作5】 題目：第三正規化後的結果，並建立關聯圖 先備技能：1.參考附錄一 Access 2010的基本操作
2.建立關聯式資料庫 實作步驟： 步驟一：開啟資料庫 開啟附書光碟中「 01_Access 2010(範例資料庫)\ch4\素材檔\ ch4-4-3(3NF) 」 步驟二： 以證明在3NF之後， 將「遞移相依」的欄位「分割」出去，再另外組成「新的資料表」。因此，我們將「課程資 料表」分割為二個資料表，分別為「課程資料表」與「老師資料表」。 步驟三：在3NF之後，總共產生了四個資料表(「學生資料表」、「成績資料表」、「課程資料表」 與「老師資料表」) 您可以參考「完成檔\ ch4-4-3(3NF) 」

60 BCNF正規化　 是由Boyce和Codd於1974年所提出來的3NF的改良式。其條件比3NF更加嚴苛。因此每一個符合BCNF的關聯一定也是3NF。 對於大部分資料庫來說, 通常只需要執行到第三階段的正規化就足夠了。【適用時機】 如果資料表的「主鍵」是由「多個欄位」組成的, 則必須再執行 Boyce-Codd 正規化。

61 BCNF的規則 如果資料表的「主鍵」只由「單一欄位」組合而成, 則符合第三階正
規化的資料表, 亦符合 BCNF(Boyce-Codd Normal Form)正規化。 如果資料表的「主鍵」由「多個欄位」組成(又稱為複合主鍵), 則資料 表就必須要符合以下條件, 我們就說這個資料表符合BCNF(Boyce- Codd Normal Form)正規化的形式。 1.符合3NF 的格式。 2.「主鍵」中的各欄位不可以相依於其他非主鍵的欄位。

62 檢驗「成績資料表」是否滿足 BCNF 規範
由於在我們完成第三正規化之後，已經分割成四個資料表，其中「成績資料表」的主鍵是由「多個欄位」組成(又稱為複合主鍵)。 因此，我們利用BCNF(Boyce-Codd Normal Form)正規化的條件, 來檢驗「成績資料表」： 成績資料表(學號，課程代碼，成績) 說明： 「成績」欄位相依於「課程代碼」及「學號」欄位，對「課程代碼」欄位而言, 並沒有相依於「成績」欄位；對「學號」欄位而言, 也沒有相依於「成績」欄位。所以成績資料表是符合『Boyce-Codd 正規化的形式』的資料表。 學號 課程代碼 成績 001 C001 74 C002 93 002 63 C003 82 C005 94

63 4-4.7 怎樣才叫做是好的關聯？　 正規化就是將一個大資料表「分割」成數個不重複的小資料表。從1NF到3NF，再利用BCNF來逐步檢驗資料表中「主鍵」由「多個欄位」組成的相依性問題，這是一連串改良關聯的過程。 可是，究竟要做到哪一個程度才算「足夠好」呢？通常我們會要求：就算不能作到BCNF，也要做到3NF才可以。

64 4-5 反正規化　 【引言】 正規化只是建立資料表的原則，而非鐵律。如果過度正規化，反而導致資料存取的效率下降。因此，如果要以執行效率(查詢速度)為優先考量時，則我們還必須適當的反正規化（De-normalization）。 有時，過度的正規化，反而會造成資料處理速度上的困擾，因此，當我們在進行資料庫正規化的同時，可能也必須要測試系統執行效率，當效率不理想時，必須做適當的反正規化，亦即將原來的第三階正規化降級為第二階正規化，甚至降到第一階正規化。但是，在進行反正規化的同時，可能也會造成的資料重覆性問題。

65 4-5 反正規化<續> 【定義】將原來的第三階正規化降級為第二階正規化，甚至降到第一階 正規化。
4-5 反正規化<續>　 【定義】將原來的第三階正規化降級為第二階正規化，甚至降到第一階 正規化。 【使用時機】查詢比例較大的環境。 【分析】 1.對「資料異動」觀點 當正規化愈多層，愈有利於資料的異動(包括：新增、修改及刪除)， 因為異動時只需針對某一個較小的資料表，可以避免資料的異常現象。 2.對「資料查詢」觀點 當正規化愈多層，愈不利於資料的查詢功能，因為資料查詢時往往 會合併許多個資料表，導致查詢效能降低。 因此，「正規化論理」與「查詢合併原理」是存在相互衝突。

66 【舉例】 假設我們在進行正規化時，特別將「客戶資料表」中的「地址」分割成以下欄位： <1>正規化關聯 客戶資料表(編號，姓名，郵遞區號) 地址明細表(郵遞區號、城市、路名) 【優點】可以直接從每一個欄位當作「關鍵字」來查詢。 【例如】查詢「高雄市」或查詢「806」或查詢「和平路」等。 【適用時機】租屋網站；可以讓使用者進行「進階」查詢。 【缺點】如果要查詢的資訊是要合併多個資料表時，將會影響執行效率。 因此，一般的做法還是讓地址「反正規化」。 <2>反正規化關聯 客戶資料表(編號，姓名，郵遞區號、城市、路名)

67 4-6 結語　 基本上，建立E-R Model後已經可以達到正規化的前三階(1NF,2NF,3NF)或是BCNF的步驟。因此，我們必須瞭解建立完整的資料庫結構，可以用兩種方法來建構： 1. E-R Model (第三章介紹) 2. 資料庫正規化 (第四章介紹)驗證E-R Model是否達到最佳化

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

69 4-6 結語<續>　 2.如果某一企業早期已經人工作業，並且使用許多表單，筆者建議，可以直接進行正規化。但是，如果人工作業的表單沒有完全依照企業的需求的設計時，則筆者建議，還是要依照步驟一到步驟三來進行。如下圖所示：