6
實體關聯的正規化
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
本章重點在於規劃資料庫的檢查工作。雖然設計資料庫沒有一定的準則，或是 非得如此設計不可的道理，但在設計階段的最後，則必須檢查資料是否有重複 存放的可能，避免資料在新增、修改、刪除時重複動作，這是很重要的事。
本章將說明何謂「正規化」，以及如何利用現有的設計檢查資料庫是否符合正 規化，並說明何謂「反正規化」。
6-1 資料庫的正規化
正規化 (Normalization) 是指在資料庫設計時，會遵循相同的設計模式，檢查資 料是否重複存放，其目的是建立「良好結構的關聯表」，這是一種沒有重複資 料的關聯表，而且在新增、刪除或更新資料時，不會造成錯誤或資料不一致。
你是否曾經覺得設計出來的資料庫外觀很相似，都有很多實體的關聯，那是因 為所採用的方法論相同的緣故。資料庫正規化可說是一種資料庫的正規型式，
它可以檢查資料庫，也能在設計資料庫時，同步進行設計及檢查的動作。
透過資料庫的正規化，可減少重複資料存放，增進資料一致性。而愈正規化的 資料庫，日後產生的資料表個數也會愈來愈多。雖說適當的正規化可以減少重 複，但太多的正規化也會導致查詢不方便的困擾。
資料庫的正規化理論，共有第一∼五種正規化、Boyce/Codd正規化、反正規 化，但實務中經常只使用到第三正規化與反正規化，故本章範例將盡量限制在 第三正規化。此外，實作時也需注意是否因為過多的正規化（或不夠），而導 致資料庫產能效能與缺乏一致性的問題。
為何資料庫需要正規化
01 02 03 04 05 6-2 07 08 6-2 為何資料庫需要正規化
資料的重複存放，不但在新增資料時需要重複輸入，也會造成刪除、更新時，
必須執行多次才能達成目的，對資料異動皆有不利的情況，而正規化便是解決 這種異動會發生重複動作的檢查方式。
資料不一致也是資料處理常見的問題，以顧客與購買經歷兩個實體有關聯為 例，如圖6-1：
圖6-1：違反正規化範例
我們可以發現圖6-1的資料庫沒有經過正規化，所以存在許多重複資料。如果顧 客楊脆瓜的地址有所更動，在「顧客」與「購買經歷」實體中都有楊脆瓜先生 的地址資料，若需修改資料就得更改兩次，而姓名也是如此。若高詣菁小姐嫁 給楊脆瓜先生，要冠夫姓，因為姓名存在於顧客與購買經歷實體，因此也需要 更改兩次。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
如果資料庫沒有設計顧客實體，只有購買記錄，如圖6-2：
圖6-2：只有購買記錄的設計
圖6-2只有「購買記錄」實體，沒有「顧客」實體的設計，若清除購買記錄，將 可能造成顧客資料永久喪失的現象。例如：想要刪除圖6-2中陳怡嘉的購買記 錄，結果卻導致顧客資訊永遠被刪除，這就是資料庫所謂的「不一致」。
此外，則是管理結構的問題。資料庫設計期望能符合未來的需求，若「顧客」
與「訂單」設計成如圖6-3，將可能會造成結構上的問題。
圖6-3：不良的資料庫結構設計
圖6-3中，訂單存放多個商品，就設計而言，應該分成「訂單」與「商品」兩個 實體。如果硬要放在一起，反而會造成資料庫的結構問題。
第一正規化
01 02 03 04 05 6-3 07 08
設計完成的資料庫，經由資料庫正規化的檢查，將可確保資料的一致。若設計 者都使用相同的方式規劃資料庫，則設計出的資料庫結構差異性就會較小。接 下來，將針對資料庫的正規化進行詳細的說明。
6-3 第一正規化
第一正規化 (First Normal Form；1NF) 是要排除重複群 (Repeating Group) 的出 現，每個屬性只能存放單一值，而且每筆記錄都要利用主索引鍵來加以識別。
每個屬性只能擁有一個值
屬性的意義必須是單一 姓名不可同時放姓名與薪水
不允許多重值 屬性中存放結案日期與專案狀態
圖6-4：第一正規化定義
6-3-1∣重複群
我們以如圖6-5為例來說明重複群：
圖6-5：違反第一正規化的資料庫設計
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
圖6-5的資料庫設計已違反第一正規化，也就是有所謂的「重複群」。以左圖為 例，興趣分為「興趣1」與「興趣2」，造成很多學生若只有一個興趣，只能在
「興趣2」的地方填入NULL；而右圖則是在「興趣」中填入「撞球,游泳」，若 超過1個值以上，這種設計皆違反了第一正規化。必須把興趣獨立成一個實體，
再與學生實體做數量上的關聯比較，應將設計結果更改如圖6-6：
圖6-6：修改後的資料庫設計圖
經由分析之後，發現興趣與學生之間是多對多關係，所以，產生第三實體「學生 興趣」，將雙方主索引鍵放到「學生興趣」，此時的資料庫已具備第一正規化。
如果不做第一正規化會產生什麼問題呢？以本例而言，若要修改學生興趣將會 非常不方便，因為一個屬性有兩個值，會衍生出不少問題。
資料庫設計若如圖6-7所示，也會有違反第一正規化的問題。
第一正規化
01 02 03 04 05 6-3 07 08
在圖6-7中，為了補齊多門科目的資料，必須重複填寫學生及導師姓名，此時，
無法用主索引鍵加以識別一列學生資料，故會有重複群的出現，必須將「科 目」與「導師」獨立出來。
在經過比對科目與學生，以及學生與導師的關係之後，設計結果如圖6-8：
圖6-8：合乎第一正規化的資料庫設計
6-3-2∣缺乏唯一識別碼
資料庫設計除了不能有重複群之外，也不能缺乏唯一識別碼，如圖6-9則是交易 實體中，因為缺乏識別碼而導致資料異動的不便：
圖6-9：不合乎第一正規化的資料庫設計
在圖6-9的範例，顧客楊脆瓜同一天買了相同數量的商品，因此，資料庫記錄了 兩筆交易。但此例並不符合第一正規化，因為我們無法從現有資訊中，判斷這 兩筆記錄的差異處。若將資料刪除，則相同條件的兩筆資料也會一併被刪除，
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
如此會違反資料的一致性，此時，需要有一識別屬性來判斷資料列的不同。我 們將圖6-9加以修改為圖6-10：
圖6-10：合乎第一正規化的資料庫設計
圖6-10設定了交易代號為主索引鍵，辨別同一天消費的兩筆記錄，也不會再有資 料誤刪的情況發生。
若在《第5章：實體的關聯與條件附加關係》中設計實體與屬性時，已經設定每 個實體均有主索引鍵的話，就不會發生此例缺乏唯一識別碼的問題。若發現違 反第一正規化，其原因為重複群時，設計原則是將所有會重複的群組獨立為實 體，再和原實體間數量的關聯做比較，以產生新的實體關聯圖。
當你在產生實體與屬性時，注意到屬性或實體描述，以本例而言，不必因為違 反第一正規化而需要重新拆解實體。
6-4 第二正規化
先滿足第一正規化的條件之後，再分析非主索引鍵的屬性，必須要完全依存於 主索引鍵。所謂的完全依存，是指該屬性是否屬於該實體，且是否在其他實體 也有屬性，如果重複，則代表不具相依性。
此規則也適用於複合主索引鍵的每一個屬性。舉例來說，在訂單明細實體中，
利用「訂單編號」與「產品編號」兩個屬性，共同組成主索引鍵。
我們先列舉一個不符合第二正規化的範例，如圖6-11所示：
第二正規化
01 02 03 04 05 6-4 07 08
圖6-11：不符合第二正規化的範例
在圖6-11中，地址屬性在顧客與訂單實體都有出現，若以地址相依顧客的話，代 表地址只能在顧客實體中出現，若也在訂單實體中出現，則修改地址資訊要修 改兩次，會造成地址的不一致。以胡小強為例，地址是桃園南崁，但在訂單中 的地址卻是台北市忠孝東路，產生了資料的不一致，應該改成圖6-12或圖6-13的 方式才是正確的作法。
圖6-12：符合第二正規化的範例1
圖6-13：符合第二正規化的範例2
圖6-12中，將地址從訂單中移除，以符合第二正規化，而圖6-13則是把訂單中的 地址改成「送貨地址」，顧客中的地址改成「通訊地址」，這代表兩個地址各 相依自己的實體，意即兩個地址不是同一個意思。最後資料庫設計者可能選擇 圖6-13，因為較符合情境。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
相依性在資料庫設計中較不易理解，簡單來說，就是「此屬性是否只放在此實 體中」，若發現其他實體也有相同定義的屬性，解決方式就是移除重複屬性或 變更屬性定義，使雙方定義不同。
再以產品與訂單為例，圖6-14具有重複的售價屬性。
圖6-14：不符合第二正規化的範例
由圖6-14看出，售價只能出現在產品或是訂單實體中，若兩者同時出現，則違反 了第二正規化。其解決方式如圖6-15所示，將訂單實體中的售價移除，或是改變 原本屬性的定義如圖6-16所示。
圖6-15：刪除價格以符合第二正規化
圖6-16：改變屬性定義以符合第二正規化
第三正規化
01 02 03 04 05 6-5 07 08
若以售價屬性而言，在產品或訂單實體都重複放置了，因而違反第二正規化。
其解決方式是將售價從訂單中移除，或是如圖6-16將產品實體中的售價改成定 價，將訂單實體中的售價改成購買價格，透過不同的名稱，以區分兩個屬性。
6-5 第三正規化
滿足第二正規化的條件後，再分析非主索引鍵之間不能有相依性，若有相依 性，則必須拆解成新的實體。圖6-17的例子即不符合第三正規化。
圖6-17：不符合第三正規化的範例
在圖6-17中，訂單編號是主索引鍵，而顧客編號與顧客名稱彼此之間有相依性，
此時，應該把顧客獨立為實體，以避免違反第三正規化，如圖6-18：
圖6-18：符合第三正規化的範例
將顧客從訂單實體中獨立成為單獨實體，如此就不會違反第三正規化。
簡單來說，實體中不能再有「次實體」的存在，若有次實體的存在，則需獨立 次實體，以避免違反第三正規化。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
6-6 Boyce-Codd正規化
一般而言，當實務上完成第三正規化後就不會再往下分析了，因為正規化會使 實體愈分愈多，不但資料查詢不易，也會加重程式撰寫的難度。
而實務中需要更進一步的正規化，多半是因為實體中的屬性很多，但實際上，
分析到第三正規化時，才發現實體裡的屬性已經很少了，因此，彼此間是否相 依也都一目瞭然，自然也無法再正規化了。
若要再到下一個正規化，會先遇到Boyce-Codd正規化 (簡稱BC正規化)。這是第 三正規化的加強版，於1974年由Raymond F. Boyce和Edgar F. Codd所發表，號稱
「比第三正規化更嚴格」。其詳細說明如下：
絕大多數的第三正規化資料也符合BC正規化，只有極少數的情況需要利用BC
正規化將實體再次拆解。
若實體中的主鍵由單一屬性所組合，則符合第三正規化，也符合BC正規化。
如果一個符合第三正規化的實體沒有重疊的候選鍵 (Candidate Key)，那它一
定符合BC正規化。若是一個符合第三正規化的實體有兩個或多個重疊的候選 鍵，則視其功能相依來判斷是否符合BC正規化。
換言之，若要符合BC正規化，必須完成如圖6-19之特性：
符合第二正規化格式
•
各屬性與主鍵沒有間接相依
•
主鍵中的各屬性不可以相依於其他非主鍵的屬性
•
單一屬性為主索引鍵
多個屬性所組合的主索引鍵只有一 個，無重疊的候選鍵
重疊的複合候選鍵，符合下列條件
圖6-19：符合BC正規化的條件
Boyce-Codd正規化
01 02 03 04 05 6-6 07 08
圖6-20是一個符合第三正規化的實體，但不符合BC正規化的範例。
圖6-20：符合第三正規化，但不符合BC正規化的範例
圖6-20是會議室預約的實體，每列資料均代表會議室預約的狀況，共有兩間會議 室，分別是101與102。由會議室的時間可知哪一段時間會議室有預約。而會議 類型有四種，分別是預算、課程規劃、檢討會，以及工作會報。其中，預算與 課程規劃一定是配置會議室101，檢討會與工作會報則是配置會議室102。
以本例而言，沒有一個屬性可以稱為主索引鍵，因為無論是會議室、會議開 始、會議結束，以及會議類型都可能重複，必須用多屬性組合而成的候選鍵，
才能確保實體不重複。
因此，本例多屬性組合的候選鍵如表6-1所示：
{會議室，會議開始}
{會議室，會議結束}
{會議類型，會議開始}
{會議類型，會議結束}
表6-1：多屬性組合的候選鍵
從表6-1可以發現，多個候選鍵之間彼此有相同的屬性存在，而會議類型屬性又 與會議室有相依性 (因為預算一定配置在某某會議室，已經證明它們彼此之間有 相依性存在)，我們將以圖6-21來說明：
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
1. 候選鍵之間彼此有重疊的屬性 2. 候選鍵之間又有相依性發生
(會議類型相依於會議室)
違反BC正規化的圖示
圖6-21：違反BC正規化的圖示
在圖6-21中，候選鍵彼此有重疊的屬性，像 {會議類型,會議開始} 就與 {會議室, 會議開始} 重疊了會議開始屬性。而候選鍵之間又存在相依性 (會議室與會議類 型)。此範例條件皆符合，故違反BC正規化。
符合第三正規化但不符合BC正規化的範例並不多，主因在於正規化會導致實體 增加，與實體間相關聯的屬性相對減少，如圖6-22所示，當屬性愈來愈少的情況 之下，再找出多個候選鍵的機會也會愈來愈少。
圖6-22：正規化連帶導致實體變多，每個實體的屬性變少
Boyce-Codd正規化
01 02 03 04 05 6-6 07 08
當屬性隨著正規化而減少，由多個屬性所組合而成的主索引鍵也會愈來愈少，
這也是實務上較少討論BC正規化的原因。
以本例而言，該如何設計會議室預約以符合BC正規化？結論還是需將實體從 [會 議室預約]實體中拆開，將會議室獨立存放以達到BC正規化，如圖6-23：
圖6-23：符合BC正規化的設計
我們將 [會議室] 獨立為單獨實體，賦予會議室編號為主索引鍵，以此實體而 言，用單一屬性代表主索引鍵，絕對符合第三正規化及BC正規化。至於會議室 名稱與類型，因牽涉到101會議室可進行預算與課程規劃，而102會議室可進行 檢討會與工作會報，所以，將會議室名稱與類型合併放入會議室實體中。
原先的 [會議室預約] 在少了會議室與會議類型之後，不重複的主索引鍵為 [會議 室編號]、[會議開始]、[會議結束] 三個屬性所組合而成的主索引鍵，而沒有候選 鍵，因為 {會議開始，會議結束} 還是有可能造成資料重複。此實體也符合第三 正規化及BC正規化。
若將資料利用Excel呈現，所得到的結果如圖6-24：
圖6-24：用Excel產生資料檢查
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
圖6-24清楚表達會議室屬性以及預約資訊，但需注意，每間會議室各有重疊 (例 如：101會議室可用於預算，而102會議室也可用於預算)，則會議室編號會隨著 會議室名稱與類型的相依增加，而增加會議室編號。
6-7 反正規化
我們在進行資料庫正規化的同時 (除了三大正規化與BC正規化之外)，可能也要 測試系統執行效率，當系統效能不理想時，將原第三正規化降級成第二，甚至 第一正規化，即稱為反正規化 (Denormalization)。其特性說明如下：
當正規化愈多層，愈有利於資料的新增、修改、刪除，因為刪除、修改資料
不需執行多次。但造成的結果是資料查詢需聯合多個實體才能查詢全部結 果，連帶降低了查詢的效能。
大量的正規化，導致查詢效能的低落，是資料庫設計者必須面對的問題。而
資料的新增、修改與刪除，本來就與查詢的原理相互違背。
實務上需要把原本第三正規化的資料庫，反正規化到第二、甚至第一正規化，
主要有下列幾項因素：
OLTP系統轉換至OLAP系統。
增進系統效能的反正規化。
6-7-1∣OLTP系統轉換至OLAP系統
現行資料庫系統多半提及的是OLTP (Online Transacting Processing；線上交易系 統)，當資料愈正規化，重複的可能性愈少。若設計的是OLAP (Online Analytical Processing；線上分析系統)，資料庫則不需正規化 (或是儘量少正規化)。隨著設 計資料庫的目的不同，設計結果也不相同。
反正規化
01 02 03 04 05 6-7 07 08
OLTP
線上交易系統
新增修改刪除查詢皆有
第三正規化
明細資料
OLAP
線上分析系統
百分之百查詢為主
需進行反正規化
彙總資料
圖6-25：OLAP與OLTP的差別
如何分辨設計的資料庫是偏向OLTP或OLAP系統？建議你以查詢所佔的比率 來判斷。若系統90%以上都是查詢，則較偏向OLAP；若比例為60%，則偏向 OLTP。無論再怎麼區分，查詢所佔的比例還是會比新增、刪除、修改來得多。
如果資料100%查詢，或是需要進行資料計算、報表，或大量查詢，則需把原先 OLTP資料庫轉換成OLAP資料庫，此時就需要進行反正規化。
OLAP與OLTP資料庫關係應該如圖6-26所示：
反正規化 OLTP資料庫
第三正規化
OLAP資料庫 星狀與雪花式架構
圖6-26：OLTP與OLAP的順序
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
多數公司都是先有OLTP資料庫，如果需要報表或大量計算查詢，則需轉換成 OLAP資料庫。舉例來說，OLTP資料庫多數使用者的需求類似如表6-2：
我想利用身分證字號找尋會員基本資料 我想查詢楊脆瓜學員修的課程
陳怡嘉學員的生日記錄錯誤了，需要更改成正確的日期 昨天訂單編號10248的訂單取消，需要刪除
表6-2：OLTP使用者的需求
而OLAP資料庫的需求如表6-3：
上月銷售數量與本月銷售數量的比較報表 購買本公司商品男女所佔的比例為何 大安區選舉的民調結果與政黨傾向 講師授課的問卷調查結果
表6-3：OLAP使用者的需求
OLAP和OLTP採用的資料不太一樣，需要利用轉換工具將原本的資料轉換成 OLAP資料，因為鮮少企業一開始就設計OLAP資料庫，原因在於OLTP資料庫 存放的是明細資料，而OLAP是彙總資料，必須要先有明細資料，才會有彙總資 料。例如，需要先取得班上50名學生的個別數學成績 (明細資料)，才能產生全班 數學總平均分數 (彙總資料)。
早期並沒有OLAP的概念，而是全透過應用程式取得使用者需求後，再動態查詢 資料庫內容，將臨時加總結果傳至應用程式端，如圖6-27。
OLTP 決策支援系統
應用程式
查詢
結果
反正規化
01 02 03 04 05 6-7 07 08
在OLTP資料庫中，無論應用程式輸入何種需求，都是經由程式端運算產生後的 結果。在早期還沒有OLAP資料庫概念時，我們將此種應用程式稱為決策支援系 統 (Decision Support System)，由應用程式將儲存於資料庫中的明細資料，經 由計算加總分析，以得到使用者 (決策者) 想要的結果。
舉例來說，便利商店每天會將交易記錄儲存至資料庫，如圖6-28：
圖6-28：儲存訂單的OLTP資料
此例是符合正規化的資料庫設計，將資料分為產品與訂單。從圖6-28只看得到明 細資料，要如何得知哪個商品銷售數量最多呢？對決策者而言，想得知的資料 可能如圖6-29所示：
圖6-29：決策者想看的資料
試問這兩張圖的差異為何？圖6-28是明細資料，而圖6-29是彙總資料。對決策者 而言，明細資料只是資料，並非資訊，除非將商品個數或價格加總以得知銷售 數量與金額。此外，決策者需要知道這些彙總資料的目的，一方面是為公司評 估未來決策，例如：某商品販賣最多，如果能結合其他資訊分析 (如時間或年齡 層) ，就能利用歷史資料來分析公司行銷決策該如何進行。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
決策支援系統需要大量的資料查詢，因此，對原先OLTP資料庫設計以新增、修 改、刪除為主的資料庫而言並不太適當，所以，在現今的OLTP資料庫，若需 查詢大量彙總資料，在不影響現行系統的前提之下，會將OLTP資料庫轉換成 OLAP資料庫，如圖6-30：
轉換 彙總資料
OLAP 使用者 OLTP
圖6-30：OLTP轉換成OLAP資料庫
多數的OLAP資料庫會與OLTP分開存放在不同的伺服器，而OLTP資料庫的設計 方式也與OLAP不同。總之，會先有OLTP資料庫，再藉由反正規化的方式轉換 成OLAP資料庫，其設計的要點如下。
6-7-1-1 Fact Table的選擇
設計OLAP資料庫，首要之務是選擇Fact Table (事實資料)，也就是需要分析的資 料。其事實資料的特性如圖6-31的說明：
Fact Table(事實資料)
需要分析的資料 內含明細內容
分析事實的維度 有可能是 Factless Table
圖6-31：事實資料的特性
反正規化
01 02 03 04 05 6-7 07 08
以訂單系統為例，訂單明細就是一個很好的Fact Table，它記錄了價格與數量屬 性，這兩個屬性經由加總之後，對決策者將非常有意義，而這些經由加總後具 有意義的屬性，在OLAP設計中稱為量值 (Measure)。
Fact Table(事實資料) 量值
圖6-32：訂單明細可以是事實資料
圖6-32中，訂單明細稱為事實資料，而數量與購買價格經由加總計算，都是有意 義的數據 (產品編號加總則不具意義)，稱為量值。為此，需瞭解決策者需要分析 哪些資料，並將資料放入事實資料中。
舉例而言，若想知道員工與訂單的關係，則需將員工資料加入事實資料中；若 想知道日期與訂單關係，則需將日期加入事實資料中；至於與量值無關的數據 則需移除，其分析後的結果如圖6-33：
維度 (Dimension)
圖6-33：加入要分析的資料到事實資料
圖6-33中的訂單日期與員工姓名都是要分析的資料，將要分析的資料加入事實資 料中，即是OLAP資料庫設計的方式。欲分析的資料稱為維度 (Dimension)，而將 原先在員工實體中的員工姓名，加入訂單明細Fact Table中，此設計方式即為反 正規化。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
6-7-1-2 星狀架構與雪花式架構
雖然OLAP資料庫會對OLTP資料庫進行反正規化，但還是有限度，OLAP資料庫 最多會設計成下列兩種架構，分別為星狀架構與雪花式架構。
星狀架構
維度資料與事實資料關聯
事後再修改較為不易
查詢速度較快
較不符合正規化
雪花式架構
維度資料與維度資料關聯
事後再修改較有彈性
查詢速度較慢
較符合正規化
圖6-34：星狀架構與雪花式架構的設計
星狀架構
星狀架構 (Star schema) 是將要分析的資料獨立為單獨的實體 (稱為維度實體)，
原本維度是放置在事實資料中，為了維護方便及保持資料一致性而獨立，維度 實體與事實資料之間會維持1:M的關聯，原先OLTP的資料庫也會變成像星星一 樣的放射式發展，如圖6-35。
OLTP資料庫 OLAP資料庫
反正規化
01 02 03 04 05 6-7 07 08
由圖6-35可以看出，左圖原本是OLTP資料庫，且完全符合第三正規化，但設計 成OLAP資料庫時，維度資料直接與事實資料關聯，因此，右圖看起來像是星狀 圖。
雪花式架構
相較於星狀架構的設計，另一種設計架構為雪花式架構 (Snowflake schema)。
星狀架構在日後新增維度時，除了需產生維度實體之外，還需在事實資料中加 入外部鍵，以達成1:M關聯。一開始若沒有規劃分析維度，等到事後再加入的 話，將會造成資料處理的困難，所以，雪花式架構適用於事後加入分析維度的 作法，它不直接和事實資料關聯，而與原先的維度實體關聯，如圖6-36。
星狀架構 雪花式架構
圖6-36：雪花式架構與星狀架構的差別
圖6-36中的左圖是原先的星狀架構，若要增加產品分類維度，則需直接與訂單明 細Fact Table關聯，缺點是原訂單明細Fact Table中的外部鍵值事後需補齊。右圖 是雪花式架構，只需把產品分類與產品關聯即可，就像是雪花般一片片疊出來 的結果，並不會影響到原先的事實資料。
以上簡述了OLAP資料庫設計方式，若所設計的資料庫愈需要查詢，則資料庫就 愈有可能進行反正規化。在實務上，我們會先設計OLTP資料庫，再利用星狀架 構或雪花式架構規劃出OLAP資料。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
6-7-2∣增進系統效能的反正規化
實務中最常進行反正規化原因，多半是為了增進系統效能。雖然過多的正規化 會導致新增、刪除、修改的速度變快，但查詢就會受到影響。現在很少有系統 只有新增、修改、刪除而沒有查詢，為了遷就某些功能需要大量查詢，特別將 功能反正規化，以符合效能需求。
圖6-37是符合第三正規化的範例，在討論區內容分為標題與文章，而標題與文章 之間的關聯為1:M，即一個標題會有多個關聯。
圖6-37：符合正規化的設計
我們將標題與文章獨立成兩個實體，但使用者的需求是希望能在應用程式端看 到如圖6-38的資料。
圖6-38：使用者希望在應用程式中看到的資料
通常判斷一篇文章有多少回應，是透過計算文章實體而來的，因此，應用程式 要顯示標題，並利用標題編號進一步計算回覆篇數。當資料不多時，這樣的計 算並不會影響效能，一旦資料增加到一定限度時 (端看機器本身的負荷)，就會因 使用者等待資料計算而影響整體效能。
如果在標題實體中，增加 [回應] 屬性，以用來計算回應的文章數量，此舉將違 反了資料庫正規化，如圖6-39：
書商系統的正規化檢查
01 02 03 04 05 6-8 07 08
表6-4：先前規劃完成的實體與屬性 圖6-39：使用者希望在應用程式中看到的資料
增加回應屬性就和在員工實體中加入 [年齡] 一樣，都屬於計算屬性，此舉違反 第一正規化。若回應屬性加入後，無論文章日後新增與刪除，都必須對回應屬 性維護 (新增一篇文章，回應屬性加1，刪除一篇文章，回應屬性減1)。
但資料量太大，為了避免過度的計算與關聯 (JOIN)，可增加回應屬性讓使用者 在查詢時，無需再計算文章實體，可減少耗費過多的CPU效能。
此例是用反正規化來達到查詢效能的提升，而適用對象大多是實體預期會有很 多資料時，例如：訂單、產品等類型的資料。如果員工資料量增減不夠大 (以筆 者為例，3年前公司有3000名員工，3年後還是3000多名員工，增加不多) 時，便 建議儘量以符合正規化的方式來設計資料庫。
6-8 書商系統的正規化檢查
以書商系統為例，其規劃完成的實體與屬性如表6-4所示：
實體 包含屬性
書店 書店編號、書店名稱、書店電話、傳真、負責人、地址、地區
書籍 書籍編號、書分類、書名、ISBN碼、作者、出版社、書籍種類、成本、
訂價、成交價格、庫存狀態、銷售結果 書籍配送 書籍編號、書店編號、配送年月、配送數量 雜誌配送 雜誌編號、書店編號、配送年月、配送數量
訂戶 訂戶編號、訂戶名稱、訂戶地址、訂閱開始日、訂閱結束日
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
實體 包含屬性
雜誌 雜誌編號、雜誌名稱、頁數、主編、創刊日期、停刊日期、發行週期、發 行狀態、發行數量、定價
訂戶雜誌訂閱 雜誌代碼、訂戶代碼
表6-4：先前規劃完成的實體與屬性 (續)
單獨檢查每個實體，並查詢是否違反正規化，或是否該獨立成一個實體。最簡 單的檢查方式是查看屬性有沒有重複出現在多個實體中，或使用者有無單獨需 要維護管理某一屬性，如果不獨立成為單獨實體，恐怕將造成日後管理上的困 難。
書店實體檢查
以書店實體為例，書店名稱、書店電話、傳真、負責人、地址都與書店編號有 相依性，重複的機率不高，但地區屬性就有可能重複。使用者若想利用應用程 式透過地區查詢書店資訊，必要時，可以獨立成個別實體，也可以製造一些假 資料檢查。
地區可算是書店實體的分類，利用地區可以將書店範圍縮小在某一區成為有利 的資料查詢。但若地區資料不多 (例如：只有北、中、南、東四區)，則沒必要獨 立成實體，只需用代碼來表示即可 (1代表北，2代表中…依此類推)，除非還有進 一步的需求。
書籍實體中的書名、ISBN碼、書籍種類、成本、訂價、庫存狀態和書籍有直接 相依，但書籍的分類可獨立成分類實體 (必要時，還可設定次分類)，如圖6-40：
6-40：增加書籍分類實體
書商系統的正規化檢查
01 02 03 04 05 6-8 07 08
除了增加書籍分類實體之外，還可自行加入 [分類配送數量] 屬性，規定該分類 預設會配送的書籍數量。而此屬性的設定和 [書籍配送] 實體中的 [配送數量] 看 似有衝突，但兩者的意思卻不同。書籍配送實體中的配送數量是實際配送到書 店的數量，而書籍分類實體中的分類配送數量屬性則是預設該分類若要配送到 書店應配送的數量，並沒有違反正規化的情形。
書籍實體中的作者與出版社實體可以獨立成單獨實體，主要考量作者與出版社 數量日後會增加，和書店所在地只有北、中、南、東四區不同，故需要獨立成 單一實體。如果不將作者與出版社獨立成單一實體，可能會導致書籍資料刪除 時，作者與出版社資料也永遠被刪除的情況，此為違反第三正規化。獨立出來 的實體與書籍之間的關聯如圖6-41：
圖6-41：作者與書籍的實體關聯 (M:M)
作者與書籍之間的關聯為M:M，即一名作者可寫多本書，而一本書可能會有多 名作者合著之關係。而出版社與書籍之間的關係為1:M，則一家出版社會出版多 本書籍，而該書籍的出版社只對應到一家。為此，作者、出版社與書籍間的實 體關聯，如圖6-42所示。
圖6-42：出版社與書籍的實體關聯 (1:M)
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
成交價格和書籍沒有相依性，書籍訂價若是100元，成交價格可能是80元或60 元，如果把成交價格放在書籍實體中，就違反了第一正規化。若要記錄書籍實 際成交價格，則必須獨立成訂單實體，可視情境需要再設計 (請當作本章的練 習)。
訂戶實體檢查
訂戶實體中的訂戶名稱、訂戶住址都與訂戶編號有相依性，而訂閱開始日、訂 閱結束日則與訂戶實體沒有相依性。因為一名訂戶可以訂閱多本雜誌，而訂閱 開始日與訂閱結束日針對的應該是雜誌的訂閱，而不是訂戶，因此，原先訂戶 雜誌訂閱與訂戶實體，應該改成如表6-5：
實體 包含屬性
訂戶 訂戶編號、訂戶名稱、訂戶地址
訂戶雜誌訂閱 雜誌代碼、訂戶代碼、訂閱開始日、訂閱結束日 表6-5：更改後的訂戶與訂戶雜誌訂閱實體
在雜誌實體中的雜誌名稱、主編、創刊日期、停刊日期、發行週期、發行狀態 屬性都與雜誌有相依性，但頁數、發行數量與定價則未必與雜誌有相依性。就 頁數而言，沒有一本雜誌可以控制每期發刊的頁數都相同 (發行數量與定價的道 理也相同)，所以，應該獨立出「各月號雜誌」實體，才能充分描述之間關聯，
如圖6-43：
圖6-43：雜誌與各月號雜誌實體關聯
書商系統的正規化檢查
01 02 03 04 05 6-8 07 08
因此，將原本描述雜誌實體的頁數、發行數量與定價，改成描述各月號雜誌；
至於訂戶與雜誌實體的關聯則保持不變，因為訂戶是訂閱該本雜誌，而非只訂 閱該月號，故產生的第三實體訂戶雜誌訂閱並無改變。
經由正規化的檢查之後，書商系統的資料庫規劃如表6-6：
實體 包含屬性
書店 書店編號、書店名稱、書店電話、傳真、負責人、地址、地區 書籍分類 分類編號、分類名稱、分類配送數量
書籍 書籍編號、書名、ISBN碼、書籍種類、成本、訂價、庫存狀態、分類編 號、出版社編號
出版社 出版社編號、出版社名稱 作者 作者編號、作者姓名等基本資料 作者著書 作者編號、書籍編號
書籍配送 書籍編號、書店編號、配送年月、配送數量 雜誌配送 雜誌編號、書店編號、配送年月、配送數量 訂戶 訂戶編號、訂戶名稱、訂戶地址
雜誌 雜誌編號、雜誌名稱、主編、創刊日期、停刊日期、發行週期、發行狀態 各月號雜誌 雜誌編號、出刊年月、頁數、發行數量、定價
訂戶雜誌訂閱 雜誌代碼、訂戶代碼、訂閱開始日、訂閱結束日 表6-6：正規化後的書商資料庫設計
資料庫經由正規化檢查之後，不相依的情況會減少許多，建議你可以參考《第7 章：事後的檢討與修正》，讓資料庫設計更為完善且符合情境。
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
6-9 校務系統的正規化檢查
接著以「校務系統」為例，將先前規劃出的實體與關聯整理如表6-7：
實體 包含屬性
教授 教授編號、系代號、姓名、生日、聯絡地址等基本資料 學生選課 學號、課程編號
課程 課程編號、課名、學分數、教授編號、上課地點 學生 學號、姓名、生日、聯絡地址、導師代號、系代號 系所 系代號、系名、畢業學分數、班級
導師 導師代號、姓名、生日、聯絡地址、教授編號 表6-7：先前規劃完成的實體與屬性
課程實體檢查
在教授、學生選課實體中的屬性皆與教授有相依性，其課程實體中的課名、學 分數與課程也有相依性。但上課地點並沒有直接與課程有相依性，若從資料庫 將課程移除，則上課地點也會消失，除非上課地點只有北、中、南、東四個地 點，且未來也不會再增加，否則就需要獨立上課地點，以避免因為刪除課程資 料，而導致上課地點永久消失。
圖6-44：教室與課程之間的關聯
校務系統的正規化檢查
01 02 03 04 05 6-9 07 08
系所實體檢查
在系所實體中，系名與畢業學分數與系代號均有直接相依，但班級則不相依於 系所。例如：資管系每個年級都有3個班級，四個年級就有12班，所以無法直接 用班級描述系所實體，需獨立成單一實體。但此例較為麻煩之處在於，系所的 學生和公司的員工不同，因為幾乎都是學生入學之後，從大一升大二，直到大 四畢業 (有的系所會念5年、7年)，如何呈現「該年」的學生狀態呢？建議你必須 使用「學年度」屬性來加以描述，如此才不會出現日後資料重複的問題。
此外，若增加班級實體，也會造成原先設計的變動。原本與學生相依的導師代 號，變成與班級相依，一個班級有一個導師。原先導師是相依在學生，若同系 同班可以有不同導師，則導師自然是相依學生，但一個班級只有一個導師，所 以，將導師改成相依教室而非學生。
如果我們增加一個班級，所產生的變動如圖6-45所示：
圖6-45：增加班級之後的實體關聯將會改變
資料庫的邏輯設計—實體關聯的正規化
01 02 03 04 05 06 07 08
經由正規化的檢查，校務系統的資料庫規劃如表6-8：
實體 包含屬性
教授 教授編號、系代號、姓名、生日、聯絡地址 學生選課 學號、課程編號
課程 課程編號、課名、學分數、教授編號、教室編號 教室 教室編號、教室名稱、教室容納人數
學生 學號、姓名、生日、聯絡地址、班級代號 系所 系代號、系名、畢業學分數
班級 班級代號、學年度、系所代號、班級名稱、導師代號 導師 導師代號、姓名、生日、聯絡地址、教授編號
表6-8：正規化後的校務系統資料庫設計
結語
本章主要說明資料庫的正規化，利用正規化檢查設計的實體與屬性，以期能符 合資料不重複存放的目的。然而，資料庫設計並非只有正規化，必要時，可能 也要測試系統的執行效率，當效率不理想時，必須做適當的反正規化，以提升 查詢效能。因此，建議你唯有多練習，並輔助範例實作才能掌握其中的訣竅。
本章重點
介紹資料庫正規化。
第一、二、三正規化，以及Boyce/Codd (BC) 正規化的檢查方式。
當效率不理想時，必須做適當的反正規化。
學習評量
01 02 03 04 05 06 07 08
學習評量
情境內容
延續《第3章：使用者需求蒐集》學習評量的內容。
情境開始
你需要做下列的練習：
1. 檢討《第5章：實體的關聯與條件附加關係》設計出的快遞公司資料庫是否合 乎第三正規化。
2. 承上題，若不符合第三正規化，該如何改進？是否應該為了效能而設計反正 規化？
3. 在《第6-8節：書商系統的正規化檢查》的情境中，將成交價格與銷售結果從 書籍實體中移除，若系統情境需記錄書商將書賣出的成交價格與銷售結果，
請擴充此部分的資料庫設計。
