ER 及 EER Model 轉換為關聯表格 國立聯合大學資訊管理學系陳士杰老師
Outlines 如何利用 E-R Model 來從事正規化 將 E-R Model 對映到關聯 轉換細則 將 EER Model 對映到關聯 講義 :Ch. 2, Section 3 原文 :Ch. 7 補充講義 2 2
如何利用 E-R Model 來從事正規化 E-R Model Normalization E-R Model Relation Normalization (Ch. 4) 3
將 E-R Model 轉換到關聯 Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 4
Step 1: Mapping of Regular Entity Types 針對一般的個體, 為其建立新的關聯表格 該關聯表格的 Key 即為個體的主鍵 (Primary Key) 該個體的所有 Attributes 均需納入表格中 此時不考慮 Foreign Key; 關係上的屬性 ; 衍生屬性 ; 多值屬性 Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 5
6
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types 針對弱個體, 建立一個新的關聯表格 Key ( 所有者 ) + Key (Weak entity) 做為此新表格的複合主鍵 以 Key( 所有者 ) 做為外來鍵, 以關連到原本所屬的一般個體 該弱個體上所有的 Attributes 均需納入 Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 7
8
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types 若兩個個體中, 只有一個個體為 Total participation ( 全部參與 ), 則 1:1 關係上的屬性加入到全部參與的個體所構成的關聯表格中 ; 同時, 將部份參與的個體上之主鍵, 加入到全部參與的個體所構成的關聯表格中, 並將之設定為 Foreign Key 若為其它的狀況, 通常可以將 1:1 關係上的屬性向左或向右轉移到任一個參與的個體型態中 ; 同時, 此個體並加入另一個體的主鍵以做為外來鍵 Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 9
10
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types 針對 Non-Weak 1 對多的關係 N 端 ( 多端 ) 的個體所產生的表格中, 加入 1 端的個體之主鍵以成為其 Foreign Key 若有關係上的屬性, 則將該屬性加入 N 端的個體所產生之關聯表格中 Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 11
12
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types 針對此多對多的關係, 建立一個新的關聯表格 Key(M 端 ) + Key (N 端 ) 做為此新關聯表格的複合主鍵 Key(M 端 ) 與 Key (N 端 ) 在此新的表格中, 也分別表示為外來鍵, 以關連到原本所屬的一般個體 Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 13
14
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes 針對此多值屬性, 建立一個新的關聯表格 Key( 原本所在之個體 )+Multivalued Attribute 做為此新關聯表格的複合主鍵 以該 Key( 原本所在之個體 ) 為外來鍵, 以關連到原本所屬的一般個體 Step 7: Mapping of N-ary Relationship Types 15
16
Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued Attributes Step 7: Mapping of N-ary Relationship Types 針對此多元關係, 建立一個新的關聯表格 將所有關連到此關係的個體之主鍵, 做為此新關聯表格的複合主鍵 而通常這些主鍵同時也是外來鍵, 分別與其所屬的一般個體有所關連 17
範例 1 18
Step 1: Mapping of Regular Entity Types 針對一般的個體, 為其建立新的關聯表格 該關聯表格的 Key 即為個體的主鍵 (Primary Key) 該個體的所有 Attributes 均需納入表格中 此時不考慮 Foreign Key; 關係上的屬性 ; 衍生屬性 ; 多值屬性 員工表 部門表 專案表 19
Step 2: Mapping of Weak Entity Types 針對弱個體, 建立一個新的關聯表格 Key ( 所有者 ) + Key (Weak entity) 做為此新表格的複合主鍵 以 Key( 所有者 ) 做為外來鍵, 以關連到原本所屬的一般個體 該弱個體上所有的 Attributes 均需納入 眷屬表 20
Step 3: Mapping of Binary 1:1 Relationship Types 若兩個個體中, 只有一個個體為 Total participation ( 全部參與 ), 則 1:1 關係上的屬性加入到全部參與的個體所構成的關聯表格中 ; 同時, 將部份參與的個體上之主鍵, 加入到全部參與的個體所構成的關聯表格中, 並將之設定為 Foreign Key 若為其它的狀況, 通常可以將 1:1 關係上的屬性向左或向右轉移到任一個參與的個體型態中 ; 同時, 此個體並加入另一個體的主鍵以做為外來鍵 部門表 21
Step 4: Mapping of Binary 1:N Relationship Types 針對 Non-Weak 1 對多的關係 N 端 ( 多端 ) 的個體所產生的表格中, 加入 1 端的個體之主鍵以成為其 Foreign Key 若有關係上的屬性, 則將該屬性加入 N 端的個體所產生之關聯表格中 員工表 專案表 來自員工表 來自部門表 來自部門表 22
Step 5: Mapping of Binary M:N Relationship Types 針對此多對多的關係, 建立一個新的關聯表格 Key(M 端 ) + Key (N 端 ) 做為此新關聯表格的複合主鍵 Key(M 端 ) 與 Key (N 端 ) 在此新的表格中, 也分別表示為外來鍵, 以關連到原本所屬的一般個體 專案員工表 23
Step 6: Mapping of Multivalued Attributes 針對此多值屬性, 建立一個新的關聯表格 Key( 原本所在之個體 )+Multivalued Attribute 做為此新關聯表格的複合主鍵 以該 Key( 原本所在之個體 ) 為外來鍵, 以關連到原本所屬的一般個體 部門位置表 24
本範例的結果為 : 員工表 ( 員工代號, 姓名, 出生日, 性別, 地址, 薪給, 部門代號, 領導員工代號 ) 部門表 ( 部門代號, 部門名, 員工代號, 起始日 ) 眷屬表 ( 員工代號, 眷屬姓名, 性別, 出生日, 關係 ) 專案表 ( 專案代號, 專案名稱, 部門代號 ) 專案員工表 ( 員工代號, 專案代號, 工作時數 ) 部門位置表 ( 部門代號, 部門位置 ) 以上六個 Relations 皆符合 1NF, 2NF, 3NF, BCNF 25
範例 2 供應商代號供應商名稱數量專案代號 供應商 供應 專案 專案名稱 零件 零件代號 零件名稱 26
Step 1: Mapping of Regular Entity Types 針對一般的個體, 為其建立新的關聯表格 該關聯表格的 Key 即為個體的主鍵 (Primary Key) 該個體的所有 Attributes 均需納入表格中 此時不考慮 Foreign Key; 關係上的屬性 ; 衍生屬性 ; 多值屬性供應商表 專案表 零件表 27
Step 7: Mapping of N-ary Relationship Types 針對此多元關係, 建立一個新的關聯表格 將所有關連到此關係的個體之主鍵, 做為此新關聯表格的複合主鍵 而通常這些主鍵同時也是外來鍵, 分別與其所屬的一般個體有所關連 供應表 28
本範例的結果為 : 供應商表 ( 供應商代號, 供應商名稱 ) 專案表 ( 專案代號, 專案名稱 ) 零件表 ( 零件代號, 零件名稱 ) 供應表 ( 供應商代號, 專案代號, 零件代號, 數量 ) 以上四個 Relations 皆符合 1NF, 2NF, 3NF, BCNF 29
範例 3 總價出貨地址 訂單代號 訂單日 客戶代號 折扣數量 訂單 M 有 M 1 下 出貨日 客戶 發票地址 N 單價 產品 產品代號 品名 30
根據前面所介紹的 7 個轉換步驟, 本範例的轉換結果如下所示 : 產品表 : 產品代號 品名 單價 產品訂單表 : 產品代號 訂單代號 數量 訂單表 : 訂單代號 訂單日 出貨日 折扣 出貨地點 客戶代號 客戶表 : 客戶代號 發票地址 31
轉換細則 ( ) ( ) Step 1 Step 2 Step 1 Step 6 視需求而定不轉換 ( ) Step 3 Step 4 Step 5 Step 3, 4, 5, 7 ( ) ( ) ( ) ( ) Step 3, 4, 5 Step 7 Step 7 32
複合屬性 (Composite Attributes) 此屬性處理與否則視需要而定 Fname Lname Name (Fname, Lname) 33
衍生屬性 (Derived Attributes) 因為衍生屬性可完全由其它屬性求得, 一個設計良好的資料庫並不會儲存衍生屬性的資料 因此, 在 ER Model 轉換到關聯表格的過程不會處理衍生屬性 34
一對一關係 的轉換 此關係在 Step 3 處理, 主要的轉換是將某一端的主鍵當成另一端的外來鍵 轉換時, 會受兩端的個體是否為 完全參與 (Total Participation;T) 或 部份參與 (Partial Participation; P) 之影響而分成三種情況 : 一端為 完全參與, 另一端為 部份參與 兩端均為 部份參與 兩端均為 完全參與 35
一端為 完全參與, 另一端為 部份參與 在完全參與的個體 (T) 中, 加入部份參與個體 (P) 之主鍵做為 Foreign Key 若有關係上的屬性, 亦加入至完全參與的個體 Fname Lname Address Name Sex Salary Dname Dno Locations Bdate Ssn Employee 1 Manages 1 Department StartDate Department(Dno, Dname, Ssn, StartDate) 36
兩端均為 部份參與 在實例 (Instance) 數目較少的個體中, 加入實例數目較多的個體之主鍵為 Foreign Key 若有關係上的屬性, 亦加入至實例數目較少的個體 右圖為一些桌上型電腦被授予某些工程師, 但不是每個工程師都必須有電腦 轉換結果 : Engineer(Empno) Desktop(Dsk_no, Empno) Empno Engineer 1 Has 1 Desktop Dsk_no 37
兩端均為 完全參與 將兩個體與關係合併成一個表格 若有關係上的屬性, 亦加入至該關聯表格 右圖假設每一個報告皆有一個簡介, 且每一個簡介都只對應一個報告 ( 兩端為完全參與 ) 轉換結果 : Report_Abbreviation(R_no, Abb_no, R_name) ( 誰當主鍵都 O.K.!!) R_no Reprot 1 Has_abbr 1 Abbreviation Abb_no R_name 38
遞迴關係 (Recursive Relationships) 二元關係中如果關係兩端的個體為同一個體, 則我們稱為遞迴關係 (Recursive relationship) 無論是 完全參與 (Total Participation) 或是 部分參與 (Partial Participation) 都不會影響其轉換方法 Empno Ename Employee 1 1 is_married_to 39
一對一遞迴關係 可視為一對一關係中的特例相似於一端為 完全參與 一端為 部份參與 的一對一關係 轉換結果 : 因為兩端是相同的個體, 所以用一個表格即可表示 右圖為某公司中任一員工允許與其他員工結婚 Empno Ename Employee 1 1 is_married_to Employee(Empno, Ename, Spouse_id) 為避免與原來的 Empno 混淆, 將外來鍵 Empno 依其關係意義更名為 Spouse_id( 配偶的員工代碼 ) 來取代 40
一對多遞迴關係 可視為一對多關係中的特例, 轉換方法與原一對多關係的轉換方法相同 將 1 端個體的主鍵當作 N 端個體所轉成的表格中之外來鍵 Empno Engineer 因為兩端的個體相同, 因此用一個關聯表格即可表示 右圖為某公司中的工程師團隊, 此團隊有一個領導者 轉換結果 : Engineer(Empno, Leader_id) 1 N is_ group_leader_ of 為避免與原來的 Empno 混淆, 將外來鍵 Empno 依其關係意義更名為 Leader_id( 領導者的員工代碼 ) 來取代 41
多對多遞迴關係 可視為多對多關係中的特例, 轉換方法與 Empno Ename 原多對多關係的轉換方法相同 將多對多關係轉成一個新的表格 右圖為某公司中的員工可以與另一位或多 Employee 位員工合寫一篇報告, 或是自已獨立完成 轉換結果 : Employee(Empno, Ename) Coauthor(Author_id, Coauthor_id) M is_ coauthor_with N 新表格的主鍵為多對多關係兩端的個體所 轉換成之表格的主鍵所組成 因為關係的 兩端都是同一個體 Employee, 為了避免與 原來的 Empno 混淆, 將依其意義更名為 (Author_id, Coauthor_id) 42
三元關係 (Ternary Relationships) 此關係主要是在 Step 7 處理 其處理步驟如下 : 1. 首先, 將三元關係中的所有一般個體都轉換成表格 2. 再將三元關係轉成一個關聯表格, 並將原本的三個一般個體之主鍵加入新產生的表格, 這三個欄位在新表格中扮演外來鍵的角色, 分別與其所屬的一般個體的表格有所關連 而新表格的主鍵則由這三個欄位來決定 簡單作法 : 所有關連到此關係的個體, 它們的主鍵做為此新關聯表格的複合主 鍵 而這些主鍵同時也是外來鍵, 分別與其所屬的一般個體有所關連 Aid A name Bid A 1 R_Name 1 1 C Cid B 43
1:1:1 的關係可決定出 3 組新表格的候選鍵 由三個 1 端個體中的主鍵分別搭配組合而成 新建立的表格為 :R_Name(Aid, Bid, Cid) 如果有關係上的屬性, 則一併放入新表格中 由下圖得知可構成新表格的主鍵之候選鍵有 : (Aid, Bid), (Aid, Cid), (Bid, Cid), 可任選其一做為主鍵, 其餘的為替代鍵 ( 仍需具備唯一性與 Non-Null) 這三個欄位在新表格中同時也是外來鍵, 分別與其原個體的 表格有所關連 Aid A name Bid A R_Name 1 1 1 C Cid B 44
1:1:N 的關係可決定出 2 組新表格的候選鍵 由兩個 1 端個體中之主鍵與 N 端個體中之主鍵分別組成 新建立的表格為 :R_Name(Aid, Bid, Cid) 如果有關係上的屬性, 則一併放入新表格中 由下圖得知可構成新表格的主鍵之候選鍵有 : (Aid, Cid), (Bid, Cid), 我們可任選其一做為主鍵, 其餘的則為替代鍵 ( 仍需具備唯一性與 Non- Null) 這三個欄位在新表格中同時也是外來鍵, 分別與其原個體的表格 有所關連 Aid A name Bid A R_Name 1 N 1 C Cid B 45
1:M:N 的關係可決定出 1 組新表格的候選鍵 由兩個 多 端個體中之主鍵組成 新建立的表格為 :R_Name(Aid, Bid, Cid) 如果有關係上的屬性, 則一併放入新表格中 由下圖得知可構成新表格的主鍵之候選鍵有 : (Aid, Cid), 我們選擇它做為主鍵 這三個欄位在新表格中同時也是外來鍵, 分別與其原個體的表格有所關連 Aid A name Bid A M R_Name N 1 C Cid B 46
P:M:N 的關係可決定 1 組新表格的候選鍵 由三個 多 端的原始表格中之主鍵組成 新建立的表格為 :R_Name(Aid, Bid, Cid) 如果有關係上的屬性, 則一併放入新表格中 由下圖得知可構成新表格的主鍵之候選鍵有 : (Aid, Bid, Cid), 我們選擇它做為主鍵 這三個欄位在新表格中同時也是外來鍵, 分別與其原個 體的表格有所關連 Aid A name Bid A M R_Name N P B C Cid 47
多元關係 (N-ary Relationships) 此關係在 Step 7 處理 其處理步驟如下 : 1. 將關係中所有的一般個體都轉換成表格 2. 再將多元關係轉成一個新的關聯表格, 並將各個一般個體之主鍵加入此新的表格中, 這些欄位在新表格中扮演外來鍵的角色, 分別與其所屬的一般個體的表格有所關連 而新表格的候選鍵 ( 主鍵 ) 則是由這些新加入之欄位產生 ( 同三元關係 ) 簡單作法 : 所有關連到此關係的個體, 它們的主鍵做為此新關聯表格的複合主鍵 而這些主鍵同時也是外來鍵, 分別與其所屬的一般個體有所關連 48
將 EER Model 對映到關聯 子類別 (Subclass) v.s. 超類別 (Superclass) 一般化 是用來強調各個體類型間的共同性 特殊化 是用來強調各個體類型間的差異性 借閱人 教職人員 u 教師 o 職員 學生 職員 教師 49
範例 EER Model 的關係類型 姓氏 名字 轉換成關聯表格有三種 身份證字號 住址 常見的方法, 以下利用 分離限制 與 全部 員工 特殊化 的例子來分別 說明這三種方法 定義屬性值 分離限制 " 時薪 " d 全部特殊化薪資型態 " 月薪 " 定義屬性 定義屬性值 時薪員工 月薪員工 時薪 月薪 50
方法 1 作法 : 1. 對上層的超類別個體建立一個關聯表格 R; 此時, 該表格 R 包含了上層個體類型中所有的屬性 2. 對於每一個低層的子類別個體而言, 分別建立新的關聯表格 R i 而在 R i 中, 除了包含該低層個體中所有的屬性外, 同時亦包含了該個體所屬的超類別個體中的主鍵 轉換結果 : 員工 ( 身份証字號, 姓氏, 名字, 住址, 薪資型態 ) 月薪員工 ( 身份証字號, 月薪 ) 時薪員工 ( 身份証字號, 時薪 ) 說明 : 好處 : 不論子類別是否為 允許重疊 或是是否為 全部特殊化 皆適合!! 本例子中 薪資型態 可不用放到超類別的表格中 但一般而言, 若 EER Model 中有註明特殊化屬性時, 通常會選擇將該屬性加入 一般情況下, 最常採用這一種轉換方法 51
方法 2 作法 : 直接對上層的超類別個體建立一個關聯表格 R, 而不另外分別再對下層子類別個體建立其所屬的關聯表格 R i 而在關聯表格 R 中, 除了包含該類別個體中所有的屬性外, 尚須包含該個體所屬的子類別個體中所有的屬性 轉換結果 : 員工 ( 身份証字號, 姓氏, 名字, 住址, 薪資型態, 月薪, 時薪 ) 說明 : 一個員工的薪資不可能同時為月薪與時薪, 因此會造成部份欄位空間的浪費 此方法易造成部份欄位空間的浪費 52
方法 3 作法 : 直接對每一個低層的子類別個體都建立一個關聯表格 R i, 而不另外再對 轉換結果 : 上層超類別個體建立其所屬的關聯表格 R 而在關聯表格 R i 中, 除了包 含該子類別個體中所有的屬性外, 尚須包含該子類別個體所屬的超類別 個體之所有屬性 月薪員工 ( 身份証字號, 姓氏, 名字, 住址, 薪資型態, 月薪 ) 時薪員工 ( 身份証字號, 姓氏, 名字, 住址, 薪資型態, 時薪 ) 說明 : 本例子中 薪資型態 可不用放到表格中 但一般而言, 若 EER Model 中有註明特殊化屬性時, 通常會選擇將該屬性加入 此方法只限用在全部特殊化限制全部特殊化限制與具分離限制具分離限制情況 53
若允許子類別間有重疊時 ( 具重疊限制, 違反分離限制 ): 會造成所有的關聯表格中資料重複出現 如 : 若某一員工的薪資型態既有時薪又有月薪 ( 即 : 此 EER Model 具重疊限制 ), 則在這兩個表格中會重複出現兩次該員工的多項個人記錄 若為部份特殊化限制 ( 具部份特殊化, 違反全部特殊化限制 ) : 會造成關聯表格中資料的遺失 如 : 若該公司的薪資型態不只時薪 月薪兩種 ( 如 : 算週薪 等 ), 且這些其它型態的薪資未出現於 EER Model 中 ( 即 : 此 EER Model 為部份特殊化 ), 則這些薪資型態不屬於這時薪 月薪兩類之員工的個人資料將不被存入資料庫中 54
Summary 一般情形下, 通常會採用第一種方法 對於同時有數個超類別的共享子類別而言, 其轉換方式可採用前述方法的任何一種 55