第 2 章 規劃關聯式資料庫.

Presentation on theme: "第 2 章 規劃關聯式資料庫."— Presentation transcript:

1 第 2 章 規劃關聯式資料庫

2 本章提要 2-1 簡易的規劃流程 2-2 如何設計一個完善的資料庫 2-3 收集資料項並轉換成欄位
2-4 認識關聯、Primary Key 與 Foreign Key 2-5 資料的完整性 2-6 資料表的關聯種類 2-7 資料庫的正規化分析 2-8 資料庫規劃實戰

3 2-1 簡易的規劃流程 資料庫的規劃, 說起來是一門大學問, 市面上常可看到一本本厚達上千頁的原文書, 內容就只在介紹資料庫的原理及規劃而已。 當然, 本書沒有那麼多的篇幅為各位講述資料庫理論, 因此經過一番的去蕪存菁, 以下將以比較輕鬆、簡單、實用、並且不偏離資料庫理論的觀點, 來為各位介紹規劃資料庫的工作。

4 簡易的規劃流程 首先, 我們將關聯式資料庫的規劃工作概略分為兩個階段：
第一階段：收集完整且必要的資料項, 並轉換成資料表的欄位形式。 第二階段：將收集的欄位做適當分類後, 歸入不同的資料表中, 並建立資料表間的關聯。 從上面兩個階段的敘述, 各位應該不難看出, 關聯式資料庫的規劃工作, 主要就是在找出資料庫所需的資料表, 以及各資料表之間的關聯。

5 2-2 如何設計一個完善的資料庫 資料庫設計包含兩大部分：一是操作介面設計；另一則是結構設計。
2-2 如何設計一個完善的資料庫 資料庫設計包含兩大部分：一是操作介面設計；另一則是結構設計。 操作介面設計：就 SQL Server 而言, 操作介面就是表單的設計, 或是以程式語言 (例如 Visual Basic) 所操寫的操作介面。讓使用者不必接觸資料庫的結構, 就能操作資料庫, 如新增、刪除資料...等等工作。 結構設計：結構設計是指設計出適當且最佳化的資料表。一個結構良好的資料庫可提升其整體的存取效率及儲存效率。

6 資料庫的設計流程 資料庫發展初期, 資料規劃的完善與否, 通常依設計者的經驗、方法及知識水準不同而有所差別, 且最後的成果未必能符合使用者的需求。 近年來, 隨著電腦普及, 加上使用者對資料庫的需求愈來愈高, 使得資料庫的應用範圍愈來愈廣、愈來愈複雜。為了避免設計者閉門造車, 直到規劃後期才發錯誤, 所以在規劃的過程中, 應分為數階段分別執行, 並隨時與使用者溝通, 方可設計出既完善又符合需求的資料庫。

7 資料庫的設計流程 資料庫的規劃過程大致可分為 4 個階段： 以下我們就分別說明各階段的工作。

8 了解客戶需求 在此階段, 設計者最主要的工作是收集建立資料庫所需的資訊, 做為後續設計的基礎。本階段的主要工作包含以下兩項：
針對客戶需求, 確定設計範圍 在規劃資料庫之前, 當然要先拜訪客戶, 了解他們實際的工作流程、各部門執掌範圍及資料的處理方式, 以確定資料庫設計的範圍及應具備的功能。 收集和分析資料 在調查過程中, 除了要明確而具體地找出客戶需求外, 還要盡量收集他們平時使用的各類表單、報表、檔案..., 這些都是規劃資料庫的重要依據。

9 了解客戶需求 此外, 進行電腦化後可能會產生一些新的需求, 例如每個月各產品的銷售分析；或改變部分現行的作業流程, 這些都要事先和客戶討論, 看看是否有此需求。

10 概念設計階段 在此階段, 設計者不需考慮資料的儲存及處理等與電腦有關的問題。
主要工作是分析及整理收集到的資料, 產生一個能符合使用者需求的資料庫模型, 並以簡單的形式表現出來。 主要流程如下：

11 資料庫的設計流程

12 資料庫的設計流程 通常我們將概念設計分為兩個階段：第一個階段是建立分區的概念設計；其次是將分區設計整合為一個全區的概念設計。
概念設計的第一個步驟要分別針對不同需求的使用者, 確定使用範圍。例如公司的資料庫系統必須面對業務部、財務部、產品部...等不同部門的使用者, 這些使用者牽涉到資料庫中的資料及處理的方式各不相同, 所以應針對不同的需求, 設計不同的概念模型。

13 整合為全區概念設計圖 完成分區的概念模型後, 便要將它們整合為一個全區概念模型。 整合過程必須注意下列幾點：
解決各分區概念設計之間不一致的情形：由於分區概念設計所面對的使用者不同, 所以對於共用資料看法及重要性有時會出現差異, 而此步驟最主要的工作就是消弭各分區模型之間的不一致。 刪除概念設計中重複或多餘的物件, 以免造成後續設計時的困擾。

14 邏輯設計階段 邏輯設計階段的主要工作, 是將概念設計階設產生的結果, 轉換為實際使用的資料表。 主要的流程如右：

15 邏輯設計階段 以實際的操作來說, 此階段的工作可分為轉換為資料表及資料表正規化兩項： 轉換為資料表
完成概念設計階段後, 我們還必須遵循規則, 將原本的資料轉換為實際使用的資料表, 才能為資料庫所使用。 資料表正規化 為了達到資料庫最佳化的目的, 在轉換資料表後, 能依照正規化的步驟重新檢驗一次, 最好讓每一個資料表都能符合 Boyce-Codd 正規化 (Boyce-Codd Normal Form, 簡稱為 BCNF) 的規範。

16 建立資料庫 經過邏輯設計階段之後, 紙上的分析工作即已完成。接著要將結果建立到資料庫中。

17 2-3 收集資料項並轉換成欄位 收集必要且完整的資料項 轉換成資料表的欄位

18 收集必要且完整的資料項 在設計資料庫之前, 我們應該先 “收集” 所有需要存入資料庫的資料, 以建立一個完整的資料集 (Complete Data Set)。 假若資料庫中的資料不完整, 那麼就無法對使用者提供充份的資訊了；例如在一個訂單系統中, 假如沒有產品的訂價或訂購數量等資料項目, 那就無法算出該筆訂單中的銷貨總價了。

19 收集必要且完整的資料項 收集了完整的資料項之後, 我們還要再加以 "過濾", 目的在移除多餘的資料項目, 例如在客戶資料中, 嗜好項目若永遠都用不到, 便可將之移除, 以節省儲存空間。總而言之, 資料庫設計的先決條件是 ― 讓完整而且必要的資料可以存入資料庫中！ 收集資料項目的方法很多, 例如可以約談相關的工作人員、查閱歷史資料、觀摹實際運作情況 ... 等等, 不過若是從現有的各種手寫表單來尋找, 倒也不失為一種快又有效率的方法, 因為通常這些表單中的項目即是我們所需要的資料項目。

20 收集必要且完整的資料項

21 轉換成資料表的欄位 收集好資料項目之後, 可以先替它們稍做分類並為資料項目加上一些簡單的描述, 例如屬於什麼樣的資料型別 (整數、文字或者是日期)、有沒有什麼特殊限制 ... 等。 這樣便算是完成資料表欄位的初步雛型了：

22 轉換成資料表的欄位

23 2-4 認識關聯、Primary Key 與 Foreign Key
規劃資料庫的第二階段 ― 將收集的欄位做適當分類後, 歸入不同的資料表中, 並建立資料表間的關聯。這個階段需要比較多的觀念與技術, 底下我們將逐一介紹。 關聯 分割資料表並建立關聯的優點 資料表的 Primary key 與 Foreign key

24 關聯 如上一章所述, 關聯式資料庫是由一個或多個資料表 (Table) 所構成, 每個資料表與其它的資料表之間, 因為某些欄位的相關性而產生關聯 (Relationship)。 例如下圖的訂單資料表與客戶資料表, 便因為客戶編號欄位而產生關聯：

25 關聯 或許您會覺得, 上述兩個資料表的關聯來得不費吹灰之力, 這當然是因為我們事先設計過。
在實際分析資料表的關聯時, 一般都是使用分割資料表的方式：先將所有需要的欄位大略歸類, 然後再透過正規化分析將重複的資料一一挑出來, 另外產生新的資料表, 並建立與原資料表的關聯。

26 分割資料表並建立關聯的優點 『好好的一個資料表幹嘛要分割呢？將所有的資料項都存放在一個資料表中, 資料庫還是可以運作的嘛！』話雖如此, 不過這就喪失了關聯式資料庫的優點了。 至於關聯式資料庫的優點在哪裏？就請您看看下面的說明。

27 節省儲存空間 因為資料庫中有相當多的資料會產生重複的情況, 如果每一次都要輸入相同的資料, 則容易浪費磁碟儲存的空間, 例如：

28 節省儲存空間 很明顯地, 在作者姓名欄位與分類欄位中, 有相當多的資料是重複的, 例如 “施施研究室” 輸入 5 次、“Windows” 輸入 3 次, 而 “影像處理” 則輸入 2 次。 如果一個資料庫中有數千或上萬筆記錄, 這些重複輸入的資料所造成的磁碟空間浪費就很可怕了。

29 節省儲存空間 若是我們將作者姓名與分類欄位抽離, 另外獨立成作者資料表與分類資料表, 並建立這 3 個資料表間的關聯, 那麼在作者資料表中, 各作者名稱只需記錄一次, 而在分類資料表中每種分類也只需記錄一次即可。

30 節省儲存空間 當書籍資料表需要使用到作者名稱或分類時, 則可以經由關聯, 到作者資料表與分類資料表中選取：

31 節省儲存空間 想想看, 若是原本有 500 本書的作者姓名是 “施施研究室” , 那就要在資料表中記錄 500 次的 “施施研究室”。
而分割資料表並建立關聯之後, 只要在作者資料表中記錄一次 "施施研究室" 就好了, 這對於空間的節省不可謂不大呀！

32 減少輸入錯誤 同樣的資料經常重複輸入時, 難免會漏打或是打錯字, 使得原本應該是相同的資料, 卻變成 2 筆不同的資料：

33 減少輸入錯誤 以後當用 “施施研究室” 字串來查詢資料時, Windows 系統秘笈這一本恐怕就查不到了。
然而若是使用關聯式資料庫, 則作者姓名這一欄的資料實際上是來自於作者資料表, 因此只要確定作者資料表中的 "施施研究室" 這筆記錄是正確的, 就不需要重複輸入 "施施研究室", 自然就減少輸入錯誤的機會。

34 方便資料修改 “方便資料修改” 也是分割資料表一個重要的優點！
如果有一天要將 “施施研究室” 改為 “旗旗研究室”, 在沒有分割資料表的狀況下需要一筆一筆記錄去修改, 相當耗費時間與精力。 若有適當的分割資料表, 則只要將作者資料表的 "施施研究室" 改為 "旗旗研究室", 則書籍資料表中所有關聯到作者姓名欄位的值, 便都會改為 "旗旗研究室" 了。

35 資料表的 Primary key 與 Foreign key
資料表之間的關聯是由所謂的鍵 (Key) 來建立的。Key 可分為兩種：一種是 Primary key, 另一種是 Foreign key, 以下分別說明。

36 Primary key Primary key 是用來辨識記錄的欄位, 具有唯一性, 且不允許重複。

37 Primary key 雖然資料表不一定要有 Primary key, 但一般都建議最好要有。不過資料表中並不是每一個欄位都適合當做 Primary key, 例如書籍資料表的作者姓名, 因為可能會遇到同名同姓的人, 所以就不具有唯一性了。

38 Primary key 通常每個資料表只有一個欄位設定為 Primary key , 但有時候可能沒有一個欄位具有唯一性, 此時可以考慮使用兩個或多個欄位組合起來做為 Primary key。請看下面的範例：

39 Primary key 上例中好幾筆記錄具有相同的訂購者編號或訂單編號, 使得沒有一個欄位具有唯一性, 因此找不到一個單獨的欄位來當 Primary key。 其實這可說是資料表設計的問題, 若我們在設計欄位時, 不管由哪一個訂購者所下的每一筆訂單, 都給一個唯一的編號時, 就可以用訂單編號欄位來做 Primary key 了。

40 Primary key 但在不修改資料表的設計的狀況下, 我們發現, 其實將訂購者編號與訂單編號這兩個欄位組合起來也具有唯一性 ― 因為同一個訂購者不會有 2 個相同的訂單號碼。 因此我們可以將這 2 個欄位同時設為 Primary key, 那麼就具有辨識唯一一筆記錄的特性了。

41 Foreign key 在關聯式資料庫中, 資料表之間的關係是藉由 Foreign key 來建立的, 例如：

42 Foreign key 書籍編號與作者編號欄位, 分別是書籍資料表與作者資料表的 Primary key。
為了建立兩資料表之間的關聯, 則在書籍資料表需要有一個欄位參考或對應到作者資料表的 Primary key, 所以便在書籍資料表中設置了作者編號欄位, 此欄位便是 Foreign key 。

43 Primary Key 和 Foreign Key 的名稱一定要相同嗎？
前文所述書籍資料表與作者資料表中的作者編號欄位, 前者是 Foreign key, 後者是 Primary key。 這兩個欄位的資料型別、寬度等屬性必須相同, 但名稱不一定要一樣, 只是我們習慣上都會取相同的名稱。 另外, Foreign key 中的資料可以重複 (例如多本書作者可能是同一人), 這點和 Primary key 不同。

44 2-5 資料的完整性 關於關聯, 還有一項重要的觀念是資料的完整性。
所謂資料的完整性 (Data Integrity) 是用來確保資料庫中資料的正確性與可靠性。 例如在某一資料表中更新了一筆資料, 則所有使用到此資料的地方也都要更新。尤其是在多人使用的系統中, 許多資料是共用的, 倘若資料不正確或不一致, 那就麻煩了。

45 資料的完整性 SQL Server 具有強制達成資料完整性的功能, 以避免資料的錯誤, 我們會在第 7 章說明。現在先來看看資料完整性可分為哪幾種類型： 實體完整性 (Entity Integrity)： 實體完整性是為了確保資料表中的記錄是 "唯一" 的, 我們設定 Primary key 就是為了達成實體完整性。例如每本書都有一個書籍編號, 不同的書若使用相同的書籍編號是不被允許的, 會被 SQL Server 拒絕。

46 資料的完整性 區域完整性 (Domain Integrity)：
區域完整性是為了確保資料在允許的範圍中。例如限制某一個整數值欄位的資料範圍在 100 ~ 999 之間, 若是輸入的內容不在此範圍內, 便不符合區域完整性, 會被 SQL Server 拒絕。

47 資料的完整性 參考完整性 (Referential Integrity)：
參考完整性是用於確保相關聯資料表間的資料一致, 避免因一個資料表的記錄改變時, 造成另一個資料表的內容變成無效的值。以上一節的書籍資料表和作者資料表為例, 假設要在作者資料表中刪除一筆記錄, 若是在書籍資料表中的 Foreign key 已經參考到這一筆記錄時, 則刪除的動作會失敗, 以避免書籍資料表中的資料失去連結。

48 資料的完整性 使用者定義的完整性 (User-defined Integrity)：
顧名思義, 這是由使用者自行定義, 而又不屬於前面 3 種的完整性。例如若某客戶欠款超過六個月, 則下次他再下訂單就不賣他, 這就是由使用者自訂的完整性限制。

49 2-6 資料表的關聯種類 前面我們已經初步瞭解什麼是關聯、Primary key 以及 Foreign key, 現在則要再進一步介紹關聯的種類, 以利下一節正規化分析的進行。 關聯還可分為一對一關聯、一對多關聯與多對多關聯等 3 種對應方式。您可能還聽過多對一關聯, 其實多對一關聯與一對多關聯是一樣的, 只是角色調換一下而已。 下面就分別說明這三種關聯種類。

50 一對一關聯 (one-to-one) 當兩個資料表之間是一對一關聯時, 表示甲資料表的一筆記錄只能對應到乙資料表中的一筆記錄；而乙資料表的一筆記錄也只能對應到甲資料表中的一筆記錄。例如：

51 一對一關聯 (one-to-one) 在上圖的一對一關聯中, 每個作者所抽版稅的比率只與該作者有關, 且皆以作者編號欄位做為 Primary key。 實際上既然 Primary key 都相同, 我們也可以考慮乾脆將這兩個資料表合併成一個資料表：

52 一對一關聯 (one-to-one) 但若有其它安全性的特殊考量, 一定要用兩個資料表來儲存資料時, 則可建立一對一關聯, 然後設定作者機密資料表只允許某些人查閱, 其他資料庫的使用者都看不到內容。. 一般來說, 一對一關聯比較少用到。

53 一對多關聯 (one-to-many) 當兩個資料表之間是一對多關聯時, 表示甲資料表的一筆記錄可以對應到乙資料表中的多筆記錄；而乙資料表的一筆記錄只能對應到甲資料表中的一筆記錄, 這是最常見的關聯方式。例如：

54 一對多關聯 (one-to-many) 客戶資料表與訂單資料表是一對多關聯, 表示一筆客戶資料表的記錄可以對應到訂單資料表中的多筆記錄；而一筆訂單資料表的記錄, 只能夠對應到客戶資料表中的一筆記錄。

55 多對多關聯 (many-to-many) 當兩個資料表之間是多對多關聯時, 表示甲資料表的一筆記錄能夠對應到乙資料表中的多筆記錄；而乙資料表的一筆記錄也能對應到甲資料表中的多筆記錄。 例如一位作者可以寫好幾本書, 而一本書也可以由好幾個作者來寫, 若要將兩者建立關聯, 那就是多對多關聯了。例如：

56 多對多關聯 (many-to-many) 多對多關聯在處理資料時因為彼此間的關係太複雜, 較容易有問題, 因此通常會將這 2 個資料表重新設計, 或是在 2 個資料表之間再加上一個資料表, 使兩兩之間成為一對多關聯, 以避免多對多關聯的情況, 例如：

57 多對多關聯 (many-to-many) 左邊的 2 個資料表的關聯為：一本書可以有多份合約書, 而每份合約書只代表一本書。
右邊的 2 個資料表的關聯則為：每份合約書只簽一位作者, 但每位作者可以簽好幾本書的合約書。如此就避免掉多對多的問題了。

58 2-7 資料庫的正規化分析 關聯式資料庫不管設計得好壞, 都可以儲存資料, 但是存取效率上可能會有很大的差別。想提升關聯式資料庫的效率, 在設計資料庫的時候, 可以利用正規化 (Normalization) 的方法來協助我們修改資料表的結構。

59 何謂正規化 正規化到底在做什麼？其實簡單的說, 正規化就是要讓資料庫中重複的資料減到最少, 讓我們能夠快速地找到所要的資料, 以提高關聯式資料庫的效能。 E.F. Codd 博士的關聯式資料庫正規化理論, 將正規化的步驟歸納成幾個階段, 讓我們有具體可循的方法來建全資料表的結構。

60 何謂正規化 資料庫的正規化共可分為 第一階正規化 (1st Normal Form, 1NF)、第二階正規化 (2NF)、第三階正規化 (3NF)、BCNF (Boyce-Codd Normal Form)、第四階正規化 (4NF)、第五階正規化 (5NF) 等多個階段, 不過對於一般資料庫設計來說, 通常只要執行到 BCNF 即可, 其他更高階的正規化只有在特殊的情況下才用得到。

61 第一階正規化 正規化的過程是循序漸進的, 資料表必須在滿足第一階正規化的條件之下, 才能進行第二階正規化。
也就是說, 第二階正規化必須建立在符合第一階正規化的資料表上, 依此類推。因此, 第一階正規化是所有正規化的基礎。

62 第一階正規化 第一階正規化的規則 第一階正規化 (1st Normal Form, 以下簡稱 1NF) 有以下幾個規則：
1. 資料表中必須有 Primary Key, 而其他所有的欄位都 『相依』 於 Primary Key。 『相依』是指一個資料表中, 若欄位 B 的值必須搭配欄位 A 才有意義, 就是「B 欄位相依於 A 欄位」。舉例來說, 某一員工資料表如下：

63 第一階正規化 上表的員工編號欄為 Primary Key, 做為唯一辨識該筆記錄的欄位。對此資料表來說, 地址欄必須要相依於員工編號欄才有意義。否則 "孟庭訶" 和 "楊咩咩" 的地址都相同, 若是以地址做為 Primary Key, 就無法從地址來區別是哪一個人了！同樣地, 姓名欄也必須相依於員工編號欄。

64 第一階正規化 反之, 若資料表的欄位不符合以上規則, 則稱為 『非正規化』 的資料表。
2. 每個欄位中都只儲存單一值, 例如同一筆記錄的姓名欄位中不能存放 2 個人的姓名。 3. 資料表中沒有意義相同的多個欄位, 例如姓名 1、姓名 等重複的欄位。 反之, 若資料表的欄位不符合以上規則, 則稱為 『非正規化』 的資料表。

65 第一階正規化 不符合 1NF 資料表的缺點 首先我們來看一個非正規化的訂單資料表：

66 第一階正規化 這個資料表乍看之下清楚明瞭, 但卻有以下兩個缺點：
1.『書號』、『書籍名稱』及『數量』欄的長度無法確定：由於訂購的書籍種類可多可少, 不同的客戶訂購的種類也不相同, 所以必須預留很大的空間給這些欄位, 如此反而造成儲存空間的浪費。 2. 降低存取資料的效率：例如要找出 "威力導演" 的訂購數量, 必須先在書籍名稱欄中找出 "威力導演" 所在的位置, 然後才能從數量欄中擷取出對應的數量資料, 這不僅減緩了資料處理的速度, 而且也增加了程式出錯的機會。

67 第一階正規化 顯然的, 此資料表違反了 1NF 的第 2 個規則。
另外, 兩家客戶同時購買了『威力導演』 這本書, 也可能同一家客戶在不同訂單中購買同一本書, 因此這個資料表缺少具有唯一性的 Primary Key, 也違反了 1NF 的第 1 個條件。

68 第一階正規化 接著再看一個不符合 1NF 第 3 個規則的例子：
像書籍 1、書籍 2、書籍 3 這樣一群意義相同的欄位, 其問題同樣是無法確定要有多少個重複的欄位, 而且存取效率低落。例如要找 "Linux 實務應用" 的數量, 必須在書籍群組的每一個欄位中搜尋, 找到後還得要到數量群組中的相同位置欄位中讀取, 相當麻煩。

69 第一階正規化 建構 1NF 資料表的方法 對於不具 1NF 形式的訂單資料表, 我們可將重複的資料項分別儲存到不同的記錄中, 並加上適當的 Primary Key (標示 * 符號者為 Primary Key), 產生如下的訂單資料表：

70 第一階正規化 如此一來, 雖然增加了許多筆記錄, 但每一個欄位的長度及數目都可以固定, 而且我們可用訂單編號欄加上書號欄做為 Primary Key, 那麼在查詢某家客戶訂購某本書的數量時, 就非常地方便快速了。

71 第二階正規化 將上述訂單資料表執行 1NF 之後, 應該很容易察覺：我們輸入了許多重複的資料。如此, 不但浪費儲存的空間, 更容易造成新增、刪除或更新資料時的異常狀況。 所以, 我們必須接著進行第二階正規化, 來消除這些問題。

72 第二階正規化 第二階正規化的規則 第二階正規化 (2nd Normal Form, 以下簡稱 2NF) 有以下幾個規則：
2. 各欄位與 Primary Key 間沒有『部分相依』的關係。 『部分相依』 只有在 Primary Key是由多個欄位組成時才會發生, 它是指某些欄位只與 Primary Key 中的部分欄位有相依性, 而與另一部分的欄位沒有相依性。

73 第二階正規化 以前例的訂單資料表來說, 其 Primary Key 為訂單編號 + 書號欄位, 但客戶名稱欄只和訂單編號欄有相依性 (一筆訂單只對應一家客戶), 而書籍名稱欄只和書號欄有相依性 (一個書號只對應一本書)：

74 第二階正規化

75 第二階正規化 部分相依會造成下列問題： 新增資料時可能會無法輸入：若有一新出的書籍 "Linux 架站實務", 但還沒有任何客戶訂購, 那麼它的資料將無法輸入 (因為 Primary Key 中的欄位值不允許 NULL 值, 但此時根本沒有訂單編號可輸入)。 更改資料時沒有效率：當客戶名稱 "十全書店" 更改為 "大補書店" 時, 必須搜尋整個資料庫並一一更改, 非常沒有效率。

76 第二階正規化 刪除資料時可能會同時刪除仍有用的資料：由於 "XOOPS 架站王" 僅在 "OD101" 這筆訂單中被訂購, 如果將該筆記錄刪除, 那麼 "XOOPS 架站王" 的資料也就跟著消失了。 另外, 部分相依也會造成資料重複出現的問題, 例如 "OD101, F5301" 這組資料每次都必須同時輸入, 不但浪費時間及儲存空間, 而且也容易因疏忽而造成資料不一致的錯誤。

77 第二階正規化 建構 2NF 資料表的方法 要除去資料表中的部分相依性, 只需將部份相依的欄位分割成另外的資料表即可。例如我們將訂單資料表分割成 3 個較小的資料表 (標示 "*" 號的欄位為 Primary Key)：

78 第二階正規化

79 第二階正規化 分成 3 個資料表後, 便去除了原本資料表的 "部份相依性", 我們來看看關聯式資表可以更容易明白：

80 第三階正規化 經過 2NF 後的資料表, 其實還存在一些問題： 基於上述理由, 我們必須再執行第三階正規化。
在訂單資料表中, 如果有新進業務同仁 "陳圓圓", 在該員尚未安排負責客戶之前, 我們無法輸入該員的資料。 若要刪除十全書店的這家客戶, 勢必會將負責該客戶的業務同仁 "孟庭訶" 一併刪除。 若要更改威力導演這本書的名稱, 則必須同時更改多筆記錄 (同一本書, 會有多筆訂單), 造成不便。 基於上述理由, 我們必須再執行第三階正規化。

81 第三階正規化 第三階正規化的規則 第三階正規化 (3rd Normal Form, 以下簡稱 3NF) 有以下幾個要件：
2. 各欄位與 Primary Key 間沒有 "間接相依" 的關係 "間接相依" 是指二個欄位間並非直接相依, 而是借助第三個欄位來達成資料相依的關係, 例如 A 相依於 B；而 B 又相依於 C, 如此 A 與 C 之間就是間接相依的關係。

82 第三階正規化 要找出各欄位與 Primary Key 間的間接相依性, 最簡單的方式就是看看資料表中有沒有 "與 Primary Key 無關的相依性" 存在。例如在訂單資料表中：

83 第三階正規化 由於每筆訂單都會有一位業務員負責, 所以員工編號欄和負責業務員欄都相依於訂單編號欄。
但負責業務員又同時相依於員工編號欄, 而這個相依性是與 Primary Key 完全無關的：

84 第三階正規化 事實上, 它們之間的相依關係為：
由此可知, 負責業務員與 Primary Key 存在著無關的相依性, 也就是有 "間接相依" 的關係存在。

85 第三階正規化 建構 3NF 資料表的方法 要除去資料表中的 “間接相依性”, 其方法和除去 “部分相依性” 完全相同。
例如訂單資料表可再分割成兩個資料表：

86 第三階正規化 我們來看看這 2 個資料表的關聯： 這樣負責業務員的 "間接相依性" 便被去除了。

87 與直覺式的分割技巧做比較 當您設計資料庫一段時間, 累積了經驗及技術後, 您便可依照自己的經驗, 以直覺的方式對資料表執行最佳化, 底下是兩種方法在功能上的對照：

88 Boyce-Codd 正規化 對於大部分資料庫來說, 通常只需要執行到 3NF 即足夠了。
但如果資料表的 Primary Key是由多個欄位組成的, 則可以 Boyce-Codd 正規化 (Boyce-Codd Normal Form, 以下簡稱 BCNF) 繼續做檢查。

89 Boyce-Codd 正規化 若資料表的 Primary Key 由多個欄位組成, 則資料表只要符合下列規則, 那麼這個資料表便符合『BCNF』： 1. 符合 2NF 的格式 2. 各欄位與 Primary Key 沒有『間接相依』 的關係 3. Primary Key 中的各欄位不可以相依於其他非 Primary Key 的欄位

90 Boyce-Codd 正規化 我們利用 Boyce-Codd 正規化的條件, 來檢驗 Primary Key 由多個欄位組成的出貨記錄表資料表：

91 Boyce-Codd 正規化 數量欄相依於訂單編號及書號欄, 對訂單編號而言, 並無相依於數量欄；對書號欄而言, 也無相依於數量欄。
所以出貨記錄表是符合 BCNF 的資料表。

92 2-8 資料庫規劃實戰 看完了前面幾節的介紹, 相信您已經對關聯式資料庫有了相當的概念。現在我們要利用一個範例, 將前幾節的觀念連貫起來, 並透過將資料記錄轉換為資料表的過程, 讓您對資料庫規劃有更完整的概念。 在這一節, 我們要從一份訂購單開始著手, 試著將訂購單轉換成資料表, 並執行正規化分析, 讓訂購單成為實際可用的資料表。 以下是我們要進行轉換的訂購單：

93 資料庫規劃實戰

94 資料庫規劃實戰 接著, 我們將列出的資料項目整理成如右的資料表欄位：
現在我們得到了第一份資料表, 但其中還有許多的問題。所以接著要利用正規化分析來檢查及改造這份資料表。

95 實戰一 - 第一階正規化 我們再來看一次第一階正規化的規則：
1. 資料表中必須有 Primary Key, 而其他所有的欄位都『相依』 於 Primary Key 2. 每個欄位中都只儲存單一值 3. 資料表中沒有意義相同的多個欄位

96 實戰一 - 第一階正規化 接著我們要跟著規則逐一做調整。首先我們要在的訂單資料表中找出一個具有 “唯一性” 的欄位, 作為 Primary Key。 可是就上表而言, 並沒有這樣的欄位, 因此我們增加一個訂單編號欄位來作為 Primary Key：

97 實戰一 - 第一階正規化 再來我們要將資料表中意義相同的欄位去除掉。
由訂單資料表可以看出訂購的書籍、價格與數量, 會因為同一張訂單中訂購了多本不同的書籍, 而重複出現書籍名稱 _1、書籍名稱 _2 與書籍名稱 _3 ... 等, 造成同類型的欄位重複出現, 而且還不確定一個資料表中需要多少個欄位才夠儲存一筆訂單。

98 實戰一 - 第一階正規化 因此, 要讓訂單資料表符合『第一階正規化』, 則可將項目編號、書籍名稱、價格、數量與出版公司等 5 個欄位獨立成另一個資料表：

99 實戰一 - 第一階正規化 現在我們還要回頭看看, 分割後的資料表是不是仍然滿足第一階正規化的規則。
在訂購項目資料表中, 我們發現到訂單編號欄位並不足以代表唯一性, 因為同一個訂單編號還是會有不同的項目編號與書籍名稱。 因此我們將訂單編號與項目編號欄位組合成訂購項目資料表的 Primary Key, 這樣就可明確找到某一筆訂購項目的記錄了：

100 實戰一 - 第一階正規化

101 實戰二 - 第二階正規化 我們先來複習一下第二階正規化的規則： 1. 必須符合 1NF 的格式。
2. 各欄位與 Primary Key 間沒有 "部分相依" 的關係。

102 實戰二 - 第二階正規化 再來看看我們剛才所得到的訂購項目資料表, 若輸入資料後應該如下：

103 實戰二 - 第二階正規化 在此資料表中, 書籍名稱、價格與出版公司等 3 個欄位與項目編號並沒有必然的關係存在 ― 亦即項目編號改變了, 也不會影響這 3 個欄位的值。 因此, 根據『第二階正規化』, 我們可以將這 3 個欄位另外獨立成一個書籍資料表, 然後在訂購項目資料表中增加一個書籍編號欄位與新資料表建立關聯：

104 實戰二 - 第二階正規化

105 實戰三 - 第三階正規化 同樣地, 我們先來看一下第三階正規化的規則： 我們再回頭看看訂單資料表： 1. 符合 2NF 的格式
2. 各欄位與 Primary Key間沒有『間接相依』 的關係 我們再回頭看看訂單資料表：

106 實戰三 - 第三階正規化 從上圖我們看出, 實際上送貨地址欄位是由聯絡人欄位所決定的, 與訂單編號欄位並無直接關係, 即不符合『3NF』。
因此我們將聯絡人與送貨地址欄位另外產生一個客戶資料表, 並藉由新增的客戶編號欄位來建立關聯：

107 實戰三 - 第三階正規化 步驟進行到此, 我們已經完成第三階正規化了。將前面的結果整理一下, 我們可得到如下的資料表與關聯：

108 實戰三 - 第三階正規化 完成第三階正規化後, 資料表大致已經完成了。最後我們要利用『Boyce-Codd 正規化』做最後一次檢查。

109 實戰三 - 第三階正規化 若資料表的 Primary Key 由多個欄位組成, 則資料表要符合下列規則, 才是符合『Boyce-Codd 正規化』的資料表： 1. 符合 2NF 的格式 2. 各欄位與 Primary Key 沒有 "間接相依" 的關係 3. Primary Key 中的各欄位不可以相依於其他非 Primary Key 的欄位

110 實戰三 - 第三階正規化 我們完成的資料表中, 只有訂購項目資料表是由多個欄位組成 Primary Key。
在訂購項目資料表中, 書籍編號和各其他欄位都沒有相依的關係, 所以可以忽略該欄位不看。 而數量欄相依於訂單編號及項目編號欄, 對訂單編號而言, 並無相依於數量欄；對項目編號欄而言, 也無相依於數量欄。 所以訂單項目資料表是符合 BCNF 的資料表。

111 實戰四 - 正規化的另類思考 正規化固然是設計資料庫的好方法, 但它只是一個基本原則而已, 在原則之外, 我們還是可以依照系統的需求自行做一些變化, 例如下面二種狀況： 不必要的分割 正規化的工作有時不必做得非常徹底, 我們拿下圖的地址資料表來看：

112 實戰四 - 正規化的另類思考 按照正規化原理, 郵遞區號與縣市、區有從屬關係, 因此這個資料表必須再進行分割才能符合第 3 階正規化的要求。
但是在實際的作業上, 我們每次都一定會查詢全部的欄位, 如果將它們分割了, 那麼每次在查詢時就都要多一道還原的手續, 這樣做實非明智之舉。

113 實戰四 - 正規化的另類思考 人工的分割 有時為了增加資料處理的效率, 我們會將已經符合第三階正規化的資料表再做分割。.
例如一個資料表擁有非常多的欄位, 而其中又有許多欄位根本很少用到, 那麼就可以將那些少用的欄位分離出來, 存放到另外一個資料表之中。 在下面的例子中, 第一個資料表存放的是常用的資料, 而另一個資料表則用來存放罕用的資料：

114 實戰四 - 正規化的另類思考 在分割後, 由於員工資料 1 的欄位變少了, 因此可以有效地提升存取效率。

115 實戰四 - 正規化的另類思考 經過正規化分析的洗禮, 我們應該可以得到結構不錯的資料表, 以及資料表之間的關聯, 而資料庫的規劃也到此告一段落。