資料表關聯與正規化

大綱 關聯式資料庫 資料表格正規化 資料表關聯

資料庫的類型 階層式資料庫 (Hierarchical Database) 網狀式資料庫 (Network Database) 關聯式資料庫 (Relational Database) 物件導向式資料庫 (Object-Oriented Database)

階層式資料庫類型 階層式資料庫是採用樹狀的結構, 將資料分門別類儲存在不同的階層下。此類型的優點是資料結構類似金字塔, 對於在同一類型中不同階層資料的描述非常簡單且清楚。 缺點:當資料的關係變得複雜時, 會造成管理及維護的不便。

階層式資料庫類型

網狀式資料庫類型 網狀式資料庫其實就是階層式資料庫的擴充, 我們可將每筆記錄想像成一個節點, 節點與節點間可以建立關聯 (也就是建立記錄和記錄間的關聯), 形成一個複雜的網狀架構。它的優點是避免了階層式資料庫中資料重複的問題, 缺點是關聯比較複雜, 尤其當資料庫的內容愈來愈多時, 要維護之間的關聯性就會變得非常複雜。

網狀式資料庫類型 在上圖, 我們利用作者姓名可查到他寫過的書, 這些書又由哪些出版社出版的關係, 當記錄的數量增加, 彼此的關係就容易變得牽扯不清。

關聯式資料庫類型 關聯式資料庫是將資料儲存在二維表格, 這些表格統稱為資料表 (Table), 所有的資料處理都是以資料表為單位, 利用資料表中行與列的關係來找出所要的記錄 (不像網狀式及階層式是以每一筆記錄為單位)。

關聯式資料庫類型 利用資料表與資料表之間的相同欄位的關聯, 也可查詢位於其他資料表中的相關資料。其優點為每個資料表都可獨立運作, 進行資料的新增、修改及刪除, 而不會影響到其他的資料表；查詢時, 亦可藉由各資料表之間的關聯性, 從多個資料表中萃取出相關的資料。Microsoft Access及 SQL Server 就是屬於此類。

關聯式資料庫類型 "關聯" (Relationship) 是指藉由表格的形式找出資料的方法。例如我們想從下圖查詢訂單序號 4 的訂購數量, 就可由橫的一列 (記錄) 與縱的一行 (欄位) 的關聯而取得：

認識 Access 關聯式資料庫

認識 Access 關聯式資料庫

關聯式資料庫類型

物件導向式資料庫 物件導向式資料庫是比較新的一種資料庫架構, 它是以物件導向的方式來設計資料庫, 其中包含了物件的屬性、方法、類別及繼承等特性。

主鍵 資料表之間是由『鍵 (Key)』來建立彼此的關聯。主鍵是用來辨識記錄的欄位, 具有唯一性, 且不允許重複。例如在書籍資料表中加入書籍編號欄位, 給每一本書一個唯一的編號, 那麼這個書籍編號欄位就可用來當作主鍵, 使用者即可依據此主鍵找到特定書籍的詳細記錄。

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

主鍵 通常每個資料表只有一個欄位設定為主鍵, 但有時可能沒有一個欄位具有唯一性, 此時可考慮使用兩個或多個欄位組合起來做為主鍵。如下圖的範例：

主鍵 上表中好幾筆記錄具有相同的訂單編號或書籍編號, 使得沒有一個欄位具有唯一性, 因此找不到一個單獨的欄位做為主鍵。不過我們發現：將訂單編號及書籍編號這 2 個欄位組合起來具有唯一性, 因為同一個訂單不會有 2 個相同的書籍編號。因此, 我們可將這 2 個欄位同時設為主鍵, 那麼就具有辨識唯一一筆記錄的特性了。

連外鍵 連外鍵是用來連結其他資料表之用, 如果甲資料表中的某個欄位必須對照到乙資料表中的主鍵, 才會使得該欄位的值變的有意義, 則我們稱該欄位為甲資料表的連外鍵。

連外鍵 在關聯式資料庫中, 資料表之間的關係是藉由連外鍵來建立的, 例如：

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

連外鍵 書籍和作者資料表中的作者編號欄位, 前者為主鍵, 後者為連外鍵。兩個欄位的類型、寬度等屬性必須相同, 但名稱不一定要一樣, 只是習慣上都會取相同的名稱。此外, 一個資料表中可包含多個連外鍵, 且其資料可重複 (例如很多本書的作者可能是同一人), 這點和主鍵不同。

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

資料完整性的幾種類型 實體完整性 (Entity Integrity), 是為了確保資料表中的記錄是" 唯一" 的。我們設定主鍵就是為了達成實體完整性。例如每一本書都有一個書籍編號, 不同的書若使用相同的編號是不被允許的, 會被 Access 拒絕。

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

資料完整性的幾種類型

資料完整性的幾種類型 若輸入的資料不在此範圍內, 即不符合區域完整性, 會被 Access 拒絕：

資料完整性的幾種類型 參考完整性 (Referential Integrity), 是用來確保相關資料表間的資料一致, 避免因一個資料表的記錄改變, 而造成另一個資料表的內容變成無效值。

資料完整性的幾種類型

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

資料庫設計步驟 決定資料庫使用目的 設計資料表 決定欄位 決定關聯 改善設計 輸入資料

規劃資料表步驟 收集完整資料集﹐並轉換為欄位 依欄位分類﹐建立不同資料表﹐並建立關聯性

資料表設計 ex. 訂單系統 (訂單序號﹑日期﹑客戶名稱﹑書籍名稱﹑單價﹑數量﹑是否付款﹑備註) 收集資料項 轉成欄位 定格式型別

訂單資料表

資料表分割(正規化理論) 資料表的正規化理論 (Normalization Theory) 其實是一套資料表分割的法則。 正規化的目的, 則是要避免資料重複或相互矛盾的情形, 並使資料庫在使用時能更有效率、更容易維護

設計不當的問題 重覆輸入 容易錯誤 資料不一致 更新複雜性

改善後的設計

實體 - 關係圖與正規化的關係 資料表的正規化理論 (Normalization Theory) 其實是一套資料表分割的法則。在資料庫設計之初, 我們可將所有的資料欄位合併成一個大資料表, 然後依尋正規化理論所提出的各個步驟, 逐步分割, 進而形成許多獨立、但彼此關聯的小資料表。

資料庫的正規化 正規化的目的, 則是要避免資料重複或相互矛盾的情形, 並使資料庫在使用時能更有效率、更容易維護。

資料庫的正規化 資料庫的正規化共可分為 1NF (Normal Form)、2NF、3NF、BCNF (Boyce-Codd Normal Form)、4NF、5NF 等多個階段, 不過對於一般資料庫設計來說, 通常只需要執行到 BCNF 即可。 在資料表正規化的過程 (1NF 到 BCNF) 中, 每個階段都是以欄位的相依性, 做為分割資料表的依據之一。

欄位相依 在一個資料表中, 若乙欄位的值必須搭配甲欄位才有意義, 則我們說『乙欄位相依於甲欄位』。舉例來說, 一個員工資料表如下：

欄位相依 員工編號欄為主鍵, 做為唯一辨識該筆記錄的欄位。姓名欄必須要相依於員工編號欄, 對此資料表來說, 姓名欄才有意義。否則同名同姓的陳鐵雄都可對應到該筆記錄；同理可證, 地址欄亦必須相依於員工編號欄, 才有意義。

欄位相依 我們再看看下面的例子：

欄位相依 成績欄本身如果單獨存在是沒有意義的, 因為不曉得是哪一門課、哪個學生的成績。不過, 當成績欄相依於課程編號及學號二欄時, 就可以了解某個學生修某堂課的成績, 這樣的成績資料才有意義。

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

第一階正規化 (1NF) 第一階正規化的規則 未符合 1NF 資料表的缺點 建構 1NF 資料表的方法

第一階正規化的規則 如果資料表符合以下條件, 我們就說這個資料表符合第一階正規化的形式(First Normal Form, 簡稱 1NF)： 資料表中有主鍵, 而其他所有的欄位都相依於主鍵。 每個欄位中都只有儲存單一值, 例如姓名欄位中不能存放 2 個人的姓名。 資料表中沒有意義相同的多個欄位, 例如姓名1、姓名2...等重複的欄位。

未符合 1NF 資料表的缺點 首先我們來看一個非正規化的資料表：

未符合 1NF 資料表的缺點 『學號』、『學生姓名』及『成績』欄的長度無法確定 由於修課學生的人數可多可少, 所以必須預留很大的空間給這兩個欄位, 如此反而造成儲存空間的浪費。

未符合 1NF 資料表的缺點 降低存取資料的效率 例如要找出『陳鐵雄』的成績, 必須先在學生姓名欄中找出『陳鐵雄』所在的位置, 然後才能從成績欄中擷取出對應的成績資料, 這不僅減緩了資料處理的速度, 而且也增加了程式出錯的機會。

未符合 1NF 資料表的缺點 很明顯的, 此資料表違反了第一階正規化的第 2 個條件。另外, 可能有兩個老師同時都開了『資料庫系統』的課程, 也可能有兩個學生都叫做『王大明』, 因此這個資料表缺少具有唯一性的主鍵, 也違反了第一階正規化的第 1 個條件。

未符合 1NF 資料表的缺點 接下來看一個違反第一階正規化第 3 個條件的例子：

未符合 1NF 資料表的缺點 像學生1、學生2、學生3 這樣一群意義相同的欄位, 其問題同樣是無法確定要有多少個重複的欄位, 而且存取效率低落, 例如要找『陳鐵雄』的成績, 必須在學生群組的每一個欄位中搜尋, 找到後還得要到成績群組中的相同位置欄位中讀取, 相當麻煩。

建構 1NF 資料表的方法 對於不具第一階正規化形式的資料表, 我們可將重複的資料項分別儲存到不同的記錄中, 並加上適當的主鍵：

建構 1NF 資料表的方法 如此一來, 雖然增加了許多記錄, 但每一個欄位的長度及數目都可以固定, 而且我們可用課程編號欄加上學號欄做為主鍵, 那麼在查詢某學生修某堂課的成績時, 就非常方便而快速了。

第二階正規化 (2NF) 在執行符合一階正規化的資料表時, 應該會發覺：我們輸入了許多重複的資料。如此, 不但浪費儲存的空間, 更容易造成新增、刪除或更新資料時的異常狀況。所以, 我們必須進行第二階正規化, 來消除這些問題。

第二階正規化 (2NF) 第二階正規化的規則 建構 2NF 資料表的方法

第二階正規化的規則 如果資料表符合以下的條件, 我們說這個資料表符合第二階正規化的形式 (Second Normal Form, 簡稱 2NF)： 符合 1NF 的格式。 各欄位與主鍵間沒有部分相依的關係。

第二階正規化的規則 部分相依只有在主鍵是由多個欄位組成時才會發生, 它是指某些欄位只與主鍵中的部分欄位有相依性, 而與另一部分的欄位沒有相依性。 以選課資料表來說, 其主鍵為課程編號+學號欄位, 但課程名稱欄只和課程編號欄有相依性, 而學生姓名欄只和學號欄有相依性：

第二階正規化的規則

部分相依的問題 新增資料時：若有一個新來的轉學生『吳技安』, 但還沒有選修任何課程, 那麼它的資料將無法輸入 (因為主鍵中的欄位值是不允許有空白的, 但此時根本沒有課程編號可輸入)。 更改資料時：當我們想要將課程名稱『資料庫系統』更改為『資料庫管理』時, 必須搜尋整個資料庫並一一更改, 非常沒有效率。

部分相依的問題 刪除資料時：由於陳鐵雄只修了『CS101』一門課, 如果將該筆記錄刪除, 那麼陳鐵雄的資料也就跟著消失了。

部分相依的問題 另外, 部分相依也會造成資料重複出現的問題, 例如『CS101, 資料庫系統』這組資料每次都必須同時輸入, 不但浪費時間及儲存空間, 而且也容易因疏忽而造成資料不一致的錯誤。例如：

建構 2NF 資料表的方法 要除去資料表中的部分相依性, 只需將部份相依的欄位分割成另外的資料表即可。例如我們將選課資料表分割成 3 個較小的資料表 (加 "*" 號的欄位為主鍵)：

第二正規化之後(例)

第二正規化(練習) 訂單(訂單編號,產品名稱,客戶名稱, 客戶地址, 客戶電話, 日期, 單價,數量)  訂單(訂單編號, 產品編號,客戶編號, 日期, 數量) 客戶(客戶編號, 客戶名稱, 客戶地址, 客戶電話) 產品(產品編號, 產品名稱,單價)

第三階正規化 (3NF) 經過了第二階正規化後的資料表, 其實還存在一些問題： 在課程資料表中, 如果新來了一位教師『甄蒡』, 在尚未安排他教授的課程之前, 我們無法輸入該教師的資料。 若要刪除影像處理的課程, 勢必會將教授該門課程的教師一併刪除。 若要更改影像處理課程的名稱, 則必須同時更改多筆記錄, 造成不便。

第三階正規化 (3NF) 第三階正規化的規則 建構 3NF 資料表的方法

第三階正規化的規則 如果資料表符合以下條件, 我們就說這個資料表符合第三階正規化的形式 (Third Normal Form, 簡稱 3NF)： 符合 2NF 的格式。 各欄位與主鍵間沒有間接相依的關係。

第三階正規化的規則 間接相依是指在二個欄位間並非直接相依, 而是借助第三個欄位來達成資料相依的關係, 例如 A 相依於 B；而 B 又相依於 C, 如此 A 與 C 之間就是間接相依的關係。 要找出各欄位與主鍵間的間接相依性, 最簡單的方式就是看看資料表中有沒有『與主鍵無關的相依性』存在。

第三階正規化的規則 例如在課程資料表中：

第三階正規化的規則 由於每一門課程都會有授課的教師, 所以教師編號欄和教師姓名欄都相依於課程編號欄。但教師姓名又同時相依於教師編號欄, 而這個相依性是與主鍵完全無關的：

建構 3NF 資料表的方法 要除去資料表中的間接相依性, 其方法和除去部分相依性完全相同。例如課程資料表可再分割成兩個資料表：

第三正規化(練習) 水果(水果編號, 品名, 產地, 產季, 進貨成本, 運費) 水果(水果編號, 品名, 產地編號, 產季, 進貨成本) 產地(產地編號, 產地, 運費)

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

Boyce-Codd 正規化 (BCNF) 對於大部分資料庫來說, 通常只需要執行到第三階段的正規化即足夠了。如果資料表的主鍵是由多個欄位組成的, 則必須再執行 Boyce-Codd 正規化。

Boyce-Codd 正規化 (BCNF) Boyce-Codd 正規化的規則 檢驗『成績』資料表是否滿足 BCNF 規範

Boyce-Codd 正規化的規則 如果資料表的主鍵只由單一欄位組成, 則符合第三階正規化的資料表, 亦符合 Boyce-Codd 正規化。但若資料表的主鍵由多個欄位組成, 則資料表只要符合以下條件, 我們就說這個資料表符合Boyce-Codd 正規化的形式 (Boyce-Codd Normal Form, 簡稱 BCNF)：

Boyce-Codd 正規化的規則 符合 2NF 的格式。 各欄位與主鍵沒有間接相依的關係。 主鍵中的各欄位不可以相依於其他非主鍵的欄位。

檢驗『成績』資料表是否滿足 BCNF 規範 我們利用 Boyce-Codd 正規化的條件, 來檢驗主鍵由多個欄位組成的成績資料表：

檢驗『成績』資料表是否滿足 BCNF 規範 成績欄相依於課程編號及學號欄, 對課程編號欄而言, 並無相依於成績欄；對學號欄而言, 也無相依於成績欄。所以成績資料表是符合『Boyce-Codd 正規化的形式』的資料表。

檢驗『成績』資料表是否滿足 BCNF 規範

資料表正規化的過程

正規化的另類思考 不必要的分割 正規化的工作有時不必做的非常徹底, 因為過多的資料表可能會降低系統執行的效能。我們以學生資料表來看：

正規化的另類思考 按照正規化的原理, 郵遞區號其實是相依於『鄉鎮市(區)』, 因此這個資料表可再進行分割, 才能符合正規化的要求。如圖所示：

資料關聯的種類 一對一 一對多 多對多

一對一關聯 某資料表中的一筆記錄﹐只能對應到另一資料表的一筆記錄 例如對『員工資料』來說, 我們可以將之分為 "可公開" 與 "機密" 二類, 然後分別存放在二個資料表中：

一對一關聯

一對多關聯 這是最常見的一種關聯, 當兩個資料表之間是一對多關聯時, 表示甲資料表中的一筆記錄可對應到乙資料表中的多筆記錄；而乙資料表中的一筆記錄只能對應甲資料表中的一筆記錄。例如：

一對多關聯

一對多關聯 我們對照到實際的資料表：

一對多(例)

多對多關聯 甲資料表的一筆記錄能夠對應到乙資料表中的多筆記錄；而乙資料表中的一筆記錄也能對應到甲資料表中的多筆記錄。

多對多 EX. 選課與學生 每門課有許多學生選﹐每個學生可選許多門課 EX. 訂單與商品 每份訂單可訂多商品﹐每個商品可由許多訂單訂購

多對多關聯

多對多 實際處理時會有許多問題﹐故會加入中介資料表﹐以二個「一對多」關聯表示

多對多(例)