Microsoft PowerPoint - Ch8

Size: px

Start display at page:

Download "Microsoft PowerPoint - Ch8"

懿骑乌
4 years ago
Views:

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

2 好的關聯綱目特性 (Cont.) 資料不重複儲存空間浪費更新異常以下關聯 (OrderProduct) 裡任賢齊專輯三的資料重複儲存 8-3 好的關聯綱目特性 (Cont.) 有三種更新異常新增異常 INSERT INTO TransactionMember(tNo, mid, name) VLUES (`93000', `c ', `Chen') ; 刪除異常 DELETE WHERE TransactionMember tno = ` 90111'; 修改異常 UPDTE TransactionMember SET name = `Huang' WHERE tno = `92333; 8-4 2

3 練習 8-1 考慮圖 8-2(b) 的 OrderProduct 範例關聯, 請問刪除哪一筆記錄後會造成刪除異常? ns: 除了第 3 筆和第 8 筆記錄外, 刪除其他記錄都會造成該商品資訊消失, 而形成刪除異常 8-5 好的關聯綱目特性 (Cont.) 屬性值很少有空值一個設計不良的綱目可能會造成有些屬性有許多空值缺點浪費空間彙總函數應用在有空值的屬性時, 其意義不明確空值的含義不唯一 8-6 3

4 好的關聯綱目特性 (Cont.) 關聯的 JOIN 不會產生虛假序列值以上三種問題都是因為一個關聯綱目包含過多的屬性若將一個關聯綱目分解成數個, 分解的方式要注意 8-7 交易編號 tno 商品編號 pno 交易數量 amount 售價 saleprice 商品名稱 pname 定價 unitprice 種類 catalog b 資料庫理論與實務 500 Book b 管理資訊系統概論 600 Book b 資料庫理論與實務 500 Book b 管理資訊系統概論 600 Book d 任賢齊專輯三 300 CD 上例產生了兩筆虛假序列值 ( 以綠色底色顯示 ) 無損 JOIN 的分解 (Lossless join decomposition): 切割之後的兩個關聯, 其共同屬性必須是其中一個關聯的關聯鍵以上的分解 (Product2 和 Record2 關聯 ) 不滿足無損 JOIN 的分解, 因為共同屬性 catalog 在兩個關聯裡都不是關聯鍵 8-8 4

5 函數相依定義 : 兩個 ( 群 ) 屬性間存在的一種類似函數 (function) 裡定義域 (domain) 和對應域 (codomain) 的關係 {mid} {name, birthday} 每個 mid 剛好有一組 {name, birthday} 不會有一個 mid 有兩組或以上的 {name, birthday} 請注意 {name} {mid} 不成立因為可能有同名同姓的會員對於函數相依 X Y, 我們稱 X 決定 Y 或 Y 函數相依於 X 8-9 函數相依 (Cont.) 根據定義, 一個關聯的任何關聯鍵皆可決定其所有屬性屬性和屬性間的函數相依是一種語意的關係, 必須由人工仔細推敲來訂定但有些函數相依仍然可以有規則可以推論而得

method, transtime, pid, name, birthday} {transmid} + = {transmid, pid, name, birthday} 圖 8-7(b)

6 函數相依 (Cont.) IR1:X Y X Y IR2:X Y XZ YZ IR3:X Y, Y Z X Z 8-11 函數相依 (Cont.) 一個屬性集合的泛封閉集合 : 給定一些函數相依, 我們可以推導出這些屬性可以決定的所有屬性, 稱為泛封閉集合圖 8-7(a) {tno} + = {tno, transmid, method, transtime, pid, name, birthday} {transmid} + = {transmid, pid, name, birthday} 圖 8-7(b) {pno} + = {pno, pname, unitprice, catalog} {mid, carttime, pno} + = {mid, carttime, pno, amount, pname, unitprice, catalog}

7 練習 8-2 由圖 8-7(b) 的函數相依, 計算 {mid, pno} + ns: {pno}+ = {pno, pname, unitprice, catalog} {mid, pno}+ = {mid, pno, pname, unitprice, catalog} 8-13 正規化定義 : 將關聯綱目轉化成正規式的過程基本正規式第一正規式第二正規式第三正規式高等正規式 BCNF 第四正規式第五正規式

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

9 多值屬性處理方式二 8-17 練習 8-3 考慮圖 8-8(a) 的 Transaction_Product, 請用第二種方式將其變成 1NF ns:

10 第二正規式第二正規式 ( 簡稱 2NF) 是根據完全函數相依的概念一個函數相依 X Y, 如果我們可以從 X 裡找出部分元素 X', 使得 X' Y 仍然成立, 則稱 X Y 為一個部分函數相依否則稱 X Y 為完全函數相依下圖 OrderProduct 關聯裡 OrderProduct 會員編號 mid 購物車產生時間 carttime 商品編號 pno 訂購數量 amount 商品名稱 pname 定價種類 unitprice catalog (b) {mid, carttime, pno} {pname} 是一個部分函數相依因為 {pno} {pname} {mid, carttime, pno} {amount} 是一個完全函數相依 8-19 第二正規式 (Cont.) 一個關聯綱目 R 滿足第二正規式 (2NF) 如果對於 R 的每一個關聯鍵 K, 所有非 K 的屬性都完全函數相依於 K 下圖的 OrderProduct 關聯綱目不滿足 2NF, 因為 pname 部分函數相依於主鍵 Because {pno} {pname} OrderProduct 會員編號 mid 購物車產生時間 carttime 商品編號 pno 訂購數量 amount 商品名稱 pname 定價種類 unitprice catalog (b)

11 第二正規式 (Cont.) 可以分解如下, 以滿足 2NF 8-21 第二正規式 (Cont.) 假設圖 8-10(b) 的 TransactionMember' 關聯有一個次要鍵 (transmid, seq) {name} 部分函數相依於 {transmid, seq }, 因此不滿足 2NF 可分解如下, 以滿足 2NF 交易編號交易方式 TransactionMember tno method 交易時間 transtime 會員編號 transmid 流水號 seq 姓名 name Transaction 交易編號交易方式 tno method 交易時間 transtime 會員編號 transmid 流水號 seq Member 會員編號 transmid 姓名 name

12 練習 8-4 考慮圖 8-8(b) 的 Transaction_Product 關聯綱目, 請問其是否滿足 2NF? ns 因為 {tno,product} { transmid, method, transtime}, 且 {tno} { transmid, method, transtime} 所以, { transmid, method, transtime} 為部份函數相依於 {tno, product}, 並不符合第二正規化 8-23 第三正規式第三正規式 ( 簡稱 3NF) 是根據遞移函數相依的概念對於一個函數相依 X Y, 如果存在著另外兩個函數相依 :X Z 和 Z Y, 且 Z 不為超級鍵, 則稱 X Y 為遞移函數相依下圖 TransactionMember 關聯 {tno} {name} 是一個遞移函數相依因為 {tno} {transmid} 且 {transmid} {name}

13 第三正規式 (Cont.) 一個關聯綱目 R 滿足 3NF, 如果 R 滿足 2NF 對於每一個關聯鍵 K,R 的每一個非鍵屬性都不可遞移函數相依於 K TransactionMember 不滿足 3NF {tno} {name} 是一個遞移函數相依, 而 tno 為主鍵 8-25 第三正規式 (Cont.) 可分解如下 :

14 練習 8-5 考慮下圖 Member 關聯的函數相依, 有人說此關聯不滿足 3NF, 因為 {mid} {name} 是遞移函數相依, 可以由 {mid} {pid} 和 {pid} {name} 推導而得你認為呢? ns: 如上圖函數相依所示, 雖然 {mid} {pid} 且 {pid} {name}, 但是考慮遞移函數相依的定義 : 若存在 X Z 和 Z Y, 且 Z 不為超級鍵, 則稱 X Y 為遞移函數相依因為 {pid} 是屬於 Member 的身分證字號, 已是唯一的資料, 也就是超級鍵所以 {mid} {name} 並不是遞移函數相依因此 Member 關聯滿足 3NF 8-27 Boyce-Codd 正規式一個關聯綱目 R 滿足 BCNF, 如果對於 R 的每一個函數相依 B, 都是超級鍵滿足 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' 所決定

15 Boyce-Codd 正規式 (Cont.) 有些關聯綱目滿足 3NF 但卻不滿足 BCNF 8-29 Boyce-Codd 正規式 (Cont.)

16 Boyce-Codd 正規式 (Cont.) 上頁 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 8-31 Boyce-Codd 正規式 (Cont.) 只有 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 常無法保留所有函數相依

17 練習 8-6 有人可能認為 8-12(b) 的關聯綱目沒有比較省空間, 請修改圖 8-12 範例關聯裡的記錄, 使得空間的節省會較多 ns: 交易編號 tno 產品編號 pno 3NF 數量 amount 單價 unitprice 報價單編號 invno 報價單編號 invno BCNF 產品編號 pno bk001 bk200 cd100 數量 amount 單價 unitprice bk001 bk vcd888 bk cd100 vcd 報價單編號 invno 交易編號 tno bk 第四正規式第四正規式 ( 簡稱 4NF) 是源於多值相依的概念多值相依 X Y: 給定一個 X 的屬性值, 便有一組 Y 的屬性值 pno author pno transaction 商品編號 pno m05 m05 m05 m05 創作者 author BC XYZ BC XYZ DEF XYZ DEF XYZ 交易 transaction

18 第四正規式 (Cont.) Productuthor 商品編號 pno m05 m05 創作者 author BC XYZ DEF XYZ ProductTransaction 商品編號 pno m05 m05 交易 transactio n 在 Productuthor 關聯裡的多值相依 pno author 和 ProductTransaction 關聯裡的多值相依 pno transaction 稱為微不足道的多值相依一個關聯綱目 R 滿足 4NF, 如果每一個 R 的非微不足道的多值相依其左方都是超級鍵滿足 4NF 也必定滿足 BCNF 8-35 第五正規式一個關聯綱目 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 限制條件的發現和檢查不易, 也因此在實作上通常不予考慮

Ch06_

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