Ch06_

CH06 正規化概述

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

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

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

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

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

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

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

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

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

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

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

函數相依範例說明 13 OrderProduct 會員編號購物車產生時間商品編號訂購數量商品名稱定價種類 mid carttime pno amount pname unitprice catalog IR1:X Y X Y IR2:X Y XZ YZ IR3:X Y, Y Z X Z (b)

泛封閉集合 14 一個屬性集合的泛封閉集合 : 給定一些函數相依, 我們可以推導出這些屬性可以決定的所有屬性, 稱為泛封閉集合 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 15 由 OrderProduct 的函數相依, 計算 {mid, pno} + Ans: {pno}+ = {pno, pname, unitprice, catalog} {mid, pno}+ = {mid, pno, pname, unitprice, catalog} 8-15

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

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

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

19 多值屬性處理方式二

課堂練習 3 20 考慮 Transaction_Product, 請用第二種方式將其變成 1NF Ans: Transaction tno transmid method transtime 91100 a091123 4 92666 c092777 7 92333 c092777 7 91888 a091000 1 90111 b090555 5 92555 b092246 8 cart 2005-02-02:18:30:00 cart 2005-10-10:22:10:30 email 2005-10-15:09:00:00 fax 2005-09-10:10:10:00 cart 2005-05-05:12:30:30 cart 2005-11-11:09:10:00 TransactionProdu ct tno product 91100 b30999 92666 d11222 92666 d20777 92666 v00111 92333 b51111 91888 b40555 91888 d03333 90111 v10888 92555 b10234 92555 b40555 8-20

21 第二正規化 (2NF) 第二正規化 ( 簡稱 2NF) 是根據完全函數相依的概念 一個函數相依 X Y, 如果我們可以從 X 裡找出部分元素 X, 倘若 X Y 仍然成立, 則稱 X Y 為一個部分函數相依, 否則稱 X Y 為完全函數相依 下圖 OrderProduct 關聯為例 : {mid, carttime, pno} {pname} 是一個部分函數相依 因為 {pno} {pname} {mid, carttime, pno} {amount} 是一個完全函數相依 8-21

22 第二正規化 (2NF) 一個關聯綱目 R 滿足第二正規化 (2NF) 如果 對於 R 的每一個關聯鍵 K, 所有非 K 的屬性都完全函數相依於 K 下圖的 OrderProduct 關聯綱目不滿足 2NF, 因為 pname 部分函數相依於主鍵 因為存在 {pno} {pname} OrderProduct 會員編號 mid 購物車產生時間 carttime 商品編號 pno 訂購數量 amount 商品名稱 pname 定價 unitprice 種類 catalog 8-22

23 第二正規化 (2NF) 為滿足 2NF,OrderProduct 可以進行分解如下 : 8-23

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

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

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

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

28 第三正規化 (3NF) 可分解如下 : 8-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 8-29

30 Boyce-Codd 正規化 (BCNF) 一個關聯綱目 R 滿足 BCNF, 如果 對於 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 不是超級鍵 所以 Y 可被非超級鍵 X 所決定

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

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

Boyce-Codd 正規化 (BCNF) 33 上頁 Transaction 關聯滿足 3NF( 但不滿足 BCNF) 上頁 Transaction1 和 Transaction2 滿足 BCNF 何者較好? 1. 考慮所有函數相依 (tno, pno) amount) 2. (tno, pno) saleprice 3. (tno, pno) invno 4. invno tno 5. (invno, pno) amount 6. (invno, pno) saleprice

Boyce-Codd 正規化 (BCNF) 34 只有 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 35 有人可能認為 8-12(b) 的關聯綱目沒有比較省空間, 請修改圖 8-12 範例關聯裡的記錄, 使得空間的節省會較多 Ans: 3NF 交易編號 tno 產品編號 pno 數量 amoun t 單價 unitprice 報價單編號 invno c01 bk001 3 500 A c01 bk200 2 400 A 報價單編號 invno BCNF 產品編號 pno 數量 amount 單價 unitprice A bk001 3 500 A bk200 2 400 A cd100 5 350 A vcd888 3 450 A bk005 1 250 c01 cd100 5 350 A c01 vcd888 3 450 A c01 bk005 1 250 A 報價單編號 invno A 交易編號 tno c01 8-35

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

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

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

The End 1-39