Ch06_

Size: px

Start display at page:

Download "Ch06_"

杨薛
5 years ago
Views:

1 CH06 正規化概述

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

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

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

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

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

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

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

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

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

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

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

13 函數相依範例說明 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 泛封閉集合 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}

15 課堂練習 2 15 由 OrderProduct 的函數相依, 計算 {mid, pno} + Ans: {pno}+ = {pno, pname, unitprice, catalog} {mid, pno}+ = {mid, pno, pname, unitprice, catalog} 8-15

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

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

18 多值屬性處理方式一 18 Transaction_Products 交易編號 tno 會員編號 transmid 交易方式 method 交易時間 transtime 商品 products a cart :18:30:00 {b30999 } c cart :22:10:30 {d11222,d20777,v00111} c :09:00:00 {b51111} a fax :10:10:00 {b40555, d03333} b cart :12:30:30 {v01888} b cart :09:10:00 {b10234, b40555} 8-18

19 19 多值屬性處理方式二

20 課堂練習 3 20 考慮 Transaction_Product, 請用第二種方式將其變成 1NF Ans: Transaction tno transmid method transtime a c c a b b cart :18:30:00 cart :22:10: :09:00:00 fax :10:10:00 cart :12:30:30 cart :09:10:00 TransactionProdu ct tno product b d d v b b d v b b

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

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

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

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

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

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

28 28 第三正規化 (3NF) 可分解如下 : 8-28

29 課堂練習 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 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 所決定

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

32 Boyce-Codd 正規化 (BCNF) 32 Transaction2 Transaction1 報價單編號 invno A D C B 交易編號 tno c01 c01 c03 f04 商品編號 pno 報價單編號 invno 數量 amount 單價 unitprice bk001 A bk200 D cd100 C vcd888 C bk001 B

33 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

34 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 常無法保留所有函數相依

35 課堂練習 6 35 有人可能認為 8-12(b) 的關聯綱目沒有比較省空間, 請修改圖 8-12 範例關聯裡的記錄, 使得空間的節省會較多 Ans: 3NF 交易編號 tno 產品編號 pno 數量 amoun t 單價 unitprice 報價單編號 invno c01 bk A c01 bk A 報價單編號 invno BCNF 產品編號 pno 數量 amount 單價 unitprice A bk A bk A cd A vcd A bk c01 cd A c01 vcd A c01 bk A 報價單編號 invno A 交易編號 tno c

36 第四正規化 (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

37 第四正規化 (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

38 第五正規化 (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 限制條件的發現和檢查不易, 也因此在實作上通常不予考慮

39 The End 1-39

Microsoft PowerPoint - Ch8

Microsoft PowerPoint - Ch8 第八章正規化好的關聯綱目特性意義明確資料不重複儲存屬性值很少有空值關聯的 JOIN 不會產生虛假序列值函數相依基本正規化第一正規式第二正規式第三正規式進階正規式 Boyce-Codd 正規式第四正規式第五正規式 8-1 好的關聯綱目特性意義明確記載迷你世界裡的同一類實體或關係以下的關聯包括現實世界裡的交易和會員實體, 所以意義不明確 8-2 1 好的關聯綱目特性 (Cont.)