CH06 正規化概述

課程大綱 何謂好的關聯綱目 函數相依 基本正規化 進階正規式 意義明確 資料不重複儲存 屬性值很少有空值 關聯JOIN後不會產生虛假序列值 第一正規式(1NF) 第二正規式(2NF) 第三正規式(3NF) 進階正規式 Boyce-Codd正規式(BCNF) 第四正規式(4NF) 第五正規式(5NF)

好的關聯綱目特性 好的關聯綱目特性 意義明確 資料不重複儲存 屬性值很少有空值 關聯的JOIN不會產生虛假序列值

何謂好的關聯綱目? 意義明確 儲存資料庫應用系統裡的同一類實體或關係。 以下列的交易會員(TransactionMember)關聯表包括資料庫應用系統 中的交易和會員資料，所以並意義不明確。 可將交易與會員資料分割為兩個關聯表以改善。

何謂好的關聯綱目? 資料不重複儲存 以下產品訂購關聯表（OrderProduct)裡「任賢齊專輯三」的資料重複儲存。 空間浪費 更新異常 以下產品訂購關聯表（OrderProduct)裡「任賢齊專輯三」的資料重複儲存。 資料重複儲存，若有需要修正將更新多筆資料。

何謂好的關聯綱目? 更新異常有三種 新增異常 刪除異常 修改異常 C092777的資料已經存在。 影響到現有資料。 INSERT INTO TransactionMember(tNo, mId, name) VALUES (‘93000’, ‘c0927777’, ‘Chen’) ; 刪除異常 影響到現有資料。 DELETE　TransactionMember WHERE 　tNo = ‘ 90111’; 修改異常 Huang 的資料已經存在。 UPDATE TransactionMember SET name = ‘Huang’ WHERE tNo = ‘92333;

課堂練習1 考慮第六張投影片的關聯範例OrderProduct ，請 問刪除哪一筆記錄後會造成刪除異常？ Ans: 只有刪除第3筆和第8筆任賢齊的專輯記錄不會有刪除異 常 ，刪除其餘的記錄，因都只有單獨一筆資料，因此 都會造成該商品資訊消失，而形成刪除異常。

何謂好的關聯綱目? 屬性值很少有空值 一個設計不良的綱目可能會造成有些屬性有許多空值。 大量空值的缺點: 浪費空間 。 彙總函數應用在有空值的屬性時，其意義不明確，無法計算。 空值的含義不唯一 。

何謂好的關聯綱目? 關聯經過JOIN後不會 產生虛假序列值。 以上三種問題都是因為 一個關聯綱目包含過多 的屬性 若將一個關聯綱目分解 成好幾個，分解的方式 要多加留意。

上例產生了兩筆虛假序列值（以紅色字體顯示） 無損JOIN的分解（Lossless join decomposition）： 交易編號 tNo 商品編號 pNo 交易數量 amount 售價 salePrice 商品名稱 pName 定價 unitPrice 種類 catalog 91100 b30999 1 450 資料庫理論與實務 500 Book 管理資訊系統概論 600 92555 b10234 5 3000 92666 d11222 300 任賢齊專輯三 CD 上例產生了兩筆虛假序列值（以紅色字體顯示） 無損JOIN的分解（Lossless join decomposition）： 切割之後的兩個關聯，其共同屬性必須是其中一個關聯的關 聯鍵 以上的分解（Product2和Record2關聯）不滿足無損JOIN的分 解，因為共同屬性「catalog」在兩個關聯裡都不是關聯鍵。

函數相依 函數相依之定義： 對於函數相依XY。 兩個（群）屬性間存在的一種類似函數（function） 裡定義域（domain）和對應域（co-domain）的關係。 例如:{mId}{name, birthday} 每個會員編號((mId)剛好有一組{名字(name), 生日(birthday)} 不會有一個mId有兩組或更多的{name, birthday} 其中{name} {mId}不成立。 (因為可能有同名同姓的會員) 對於函數相依XY。 我們稱 X決定Y 或Y 函數相依於X 。

函數相依 根據函數相依，一個關聯的任何關聯鍵皆可決定 其所有屬性。 屬性和屬性間的函數相依是一種語意的關係，必 須由人工仔細推敲後來訂定。 有些函數相依仍然可以由規則可以推論可得 。

函數相依範例說明 IR1：X Y  XY IR2：XY XZYZ IR3：XY , YZ  XZ

泛封閉集合 一個屬性集合的泛封閉集合： 給定一些函數相依，我們可以推導出這些屬性可以決 定的所有屬性，稱為泛封閉集合。 TranctionNumber關聯綱目為例: {tNo}+ = {tNo, transmId, method, transTime, pId, name, birthday} 。 {transMid}+ = {transMid, pId, name, birthday} 。 OrderProduct關聯綱目為例: {pNo}+ = {pNo, pName, unitPrice, catalog}。 {mId, cartTime, pNo}+ = {mId, cartTime, pNo, amount, pName, unitPrice, catalog} 。

課堂練習2 由OrderProduct的函數相依，計算{mId, pNo}+ Ans: 15 由OrderProduct的函數相依，計算{mId, pNo}+ Ans: {pNo}+ = {pNo, pName, unitPrice, catalog} {mId, pNo}+ = {mId, pNo, pName, unitPrice, catalog}

正規化 正規化(Normal Form)： 基本正規化 高等正規化 將關聯綱目改寫成為正規化的過程。 第一正規化(1NF) BCNF 第四正規化(4NF) 第五正規化(5NF)

第一正規化(1NF) R滿足第一正規式(1NF)當 每個合法的關聯綱目都滿足第一正規式 如何分解多值屬性 R的每個屬性都是簡單且單值 處理方式一： 維持同樣的屬性，但關聯主鍵要加上該多值屬性。 處理方式二： 產生另一個關聯，此關聯包括原主鍵和該多值屬性。

多值屬性處理方式一 18 Transaction_Products 交易編號 tNo 會員編號 transmId 交易方式 method 交易時間 transTime 商品 products 91100 a0911234 cart 2005-02-02:18:30:00 {b30999 } 92666 c0927777 2005-10-10:22:10:30 {d11222,d20777,v00111} 92333 email 2005-10-15:09:00:00 {b51111} 91888 a0910001 fax 2005-09-10:10:10:00 {b40555, d03333} 90111 b0905555 2005-05-05:12:30:30 {v01888} 92555 b0922468 2005-11-11:09:10:00 {b10234, b40555}

多值屬性處理方式二

課堂練習3 考慮Transaction_Product，請用第 二種方式將其變成1NF Ans: Transaction tNo 20 考慮Transaction_Product，請用第 二種方式將其變成1NF Ans: Transaction tNo transMid method transTime 91100 a0911234 cart 2005-02-02:18:30:00 92666 c0927777 2005-10-10:22:10:30 92333 email 2005-10-15:09:00:00 91888 a0910001 fax 2005-09-10:10:10:00 90111 b0905555 2005-05-05:12:30:30 92555 b0922468 2005-11-11:09:10:00 TransactionProduct tNo product 91100 b30999 92666 d11222 d20777 v00111 92333 b51111 91888 b40555 d03333 90111 v10888 92555 b10234

第二正規化(2NF) 第二正規 化（簡稱2NF）是根據完全函數相依的概念。 一個函數相依XY，如果我們可以從X裡找出部分元素X’， 21 第二正規 化（簡稱2NF）是根據完全函數相依的概念。 一個函數相依XY，如果我們可以從X裡找出部分元素X’， 倘若X’Y仍然成立，則稱XY為一個部分函數相依，否則稱XY 為完全函數相依。 下圖OrderProduct關聯為例: {mId, cartTime, pNo}{pName}是一個部分函數相依 因為{pNo}{pName} {mId, cartTime, pNo}{amount}是一個完全函數相依

第二正規化（2NF） 一個關聯綱目R滿足第二正規化(2NF)如果 22 一個關聯綱目R滿足第二正規化(2NF)如果 對於R的每一個關聯鍵K，所有非K的屬性都完全函數相 依於K。 下圖的OrderProduct關聯綱目不滿足2NF，因為 pName部分函數相依於主鍵 因為存在{pNo} {pName}

第二正規化（2NF） 23 為滿足2NF ，OrderProduct可以進行分解如下:

第二正規化範例（2NF） 假設TransactionMember 關聯表有一個次要鍵（transMid, seq） 24 假設TransactionMember 關聯表有一個次要鍵（transMid, seq） {name}部分函數相依於{transMid, seq } ，因此不滿足2NF 可分解如下，以滿足2NF

課堂練習4 考慮Transaction_Product關聯綱目，請問其是否 滿足2NF? Ans: 因為{tNo,product}  { transMid, method, transTime} ， 而且存在{tNo}  { transMid, method, transTime} 因此{ transMid, method, transTime}為部份函數相依於 {tNo, product}，所以並不符合第二正規化。

第三正規化(3NF) 第三正規化（簡稱3NF）是根據遞移函數相依的 概念進行。 26 第三正規化（簡稱3NF）是根據遞移函數相依的 概念進行。 對於一個函數相依XY，如果存在著另外兩個函數相 依：XZ和ZY，且Z不為超級鍵，則稱XY為遞移函 數相依 下圖TransactionMember關聯 {tNo}{name}是一個遞移函數相依 因為{tNo}{transMid}且 {transMid} {name}

第三正規化(3NF) 在下列條件下一個關聯綱目R滿足3NF: R滿足2NF。 27 在下列條件下一個關聯綱目R滿足3NF: R滿足2NF。 對於每一個關聯鍵K，R的每一個非鍵屬性都不可遞移 函數相依於K 。 例如:TransactionMember不滿足3NF {tNo}{name}是一個遞移函數相依，而tNo為主鍵

第三正規化(3NF) 28 可分解如下：

課堂練習5 29 考慮下圖Member關聯的函數相依，有人說此關聯不滿足 3NF，因為{mId}{name}是遞移函數相依，可以由 {mId}{pId}和{pId}{name}推論而得。你認為呢？ Ans: 如上圖函數相依所示, 雖然{mId}{pId}且{pId}{name} , 但是考慮遞移函數相 依的定義：若存在XZ和ZY，且Z不為超級鍵，則稱XY為遞移函數相依。 因為{pId}是屬於Member的身分證字號,已是唯一的資料，也就是超級鍵。所以 {mId}{name}並不是遞移函數相依。 因此Member關聯滿足3NF。

Boyce-Codd正規化(BCNF) 一個關聯綱目R滿足BCNF，如果 滿足BCNF的關聯綱目必然也滿足2NF和3NF。 對於R的每一個函數相依AB，A都是超級鍵。 滿足BCNF的關聯綱目必然也滿足2NF和3NF。 若R不滿足2NF，則對於一個關聯鍵X來說，存在著部分函 數相依XY ，也就是X’Y ，X’X 。 所以Y可被非超級鍵X’所決定。 若R不滿足3NF，則存在著非鍵屬性Y是遞移函數相依於關 聯鍵X。也就是有XZ和ZY，且Z不是超級鍵。

Boyce-Codd正規化（BCNF) 有些關聯綱目滿足3NF但卻不滿足BCNF

Boyce-Codd正規化(BCNF) A c01 Transaction1 Transaction2 商品編號 pNo 報價單編號 invNo 數量 amount 單價 unitPrice bk001 A 3 500 bk200 D 2 400 cd100 C 5 350 vcd888 450 B 1 250 報價單編號 invNo 交易編號 tNo A c01 D C c03 B f04

Boyce-Codd正規化(BCNF) 上頁Transaction關聯滿足3NF（但不滿足BCNF) 上頁Transaction1和Transaction2滿足BCNF 何者較好？ 考慮所有函數相依 （tNo, pNo）amount （tNo, pNo）salePrice （tNo, pNo）invNo invNotNo （invNo, pNo）amount （invNo, pNo）salePrice

Boyce-Codd正規化(BCNF) 保留函數相依的分解 : 只有Transaction關聯時，設定主鍵可決定 （tNo, pNo）amount （tNo, pNo）salePrice （tNo, pNo）invNo 分解成Transaction1和Transaction2關聯時，設定主鍵可決定 （invNo, pNo）amount （invNo, pNo）salePrice invNotNo 當分解成Transaction1和Transaction2關聯時，若仍想維持函數相依 （tNo, pNo）amount ，則需做跨關聯的檢查。 保留函數相依的分解 : 關聯綱目分解後各函數相依仍可藉由檢查單一關聯來確定。 BCNF常無法保留所有函數相依。

課堂練習6 有人可能認為8-12(b)的關聯 綱目沒有比較省空間，請修 改圖8-12範例關聯裡的記錄， 使得空間的節省會較多 。 Ans: 35 有人可能認為8-12(b)的關聯 綱目沒有比較省空間，請修 改圖8-12範例關聯裡的記錄， 使得空間的節省會較多 。 Ans: BCNF 報價單編號 invNo 產品編號 pNo 數量 amount 單價 unitPrice A bk001 3 500 bk200 2 400 cd100 5 350 vcd888 450 bk005 1 250 3NF 交易編號 tNo 產品編號 pNo 數量 amount 單價 unitPrice 報價單編號 invNo c01 bk001 3 500 A bk200 2 400 cd100 5 350 vcd888 450 bk005 1 250 報價單編號 invNo 交易編號 tNo A c01

第四正規化(4NF) 第四正規式（簡稱4NF）是依據多值相依的概念而來。 多值相依X↠Y： 36 第四正規式（簡稱4NF）是依據多值相依的概念而來。 多值相依X↠Y： 給定一個X的屬性值，便有一組Y的屬性值 pNo↠author (同一商品編號有許多個創作者) pNo↠transaction(同一商品編號有許多筆交易) 商品編號 pNo 創作者 author 交易 transaction c01 ABC 50 XYZ 42 m05 DEF 51 33

第四正規化(4NF) 商品編號 pNo 創作者 author c01 ABC XYZ m05 DEF ProductTransaction 商品編號 pNo 交易 transaction c01 50 42 m05 51 33 ProductAuthor 在ProductAuthor關聯裡的多值相依pNo↠author和 ProductTransaction關聯裡的多值相依pNo↠transaction稱為 微不足道的多值相依。 一個關聯綱目R滿足4NF，如果 每一個R的非微不足道的多值相依其左方都是超級鍵。 滿足4NF也必定滿足BCNF 。

第五正規化(5NF) 一個關聯綱目R滿足5 NF： 5NF限制條件的發現和檢查不易，也因此在實 作上通常不予考慮 。 R無法再被分解成數個關聯R1, R2, …, Rk，使得 R1*R2* …*Rk=R。 R可以被分解成數個關聯R1, R2, …, Rk，使得 R1*R2* …*Rk=R，但是每一個Ri, 1ik，都是R 的超級鍵。 5NF限制條件的發現和檢查不易，也因此在實 作上通常不予考慮 。

The End