第六章關聯式資料庫設計與 實體關係模型 資料庫系統設計理論李紹綸著
本章內容 資料庫設計方法論 實體關係模型 實體關係模型與自然語言 實體 (Entity) 和實體類型 (Entity Type) 關係 (Relationship) 和關係類型 (Relationship Type) 設計實體關係圖時常犯的錯誤 實體關係圖的各種圖形符號 加強型實體關係模型 (Enhanced-ER Model,EER Model) 超類別 (Superclass) 和子類別 (Subclass) 特殊化 (Specialization) 和一般化 (Generalization) 特殊化和一般化的限制 (Constraints on Specialization and Generalization) 點陣 (Lattices) 和共享子類別 (Shared Subclasses) 種類 (Categories) 和聯合類型 (Union Types) 聚合集 (Aggregation) 2
公司資料庫實體關係圖 直屬上司上下薪水部門代號姓名司 1 N屬 N 1 工作於身分證號碼員工部門管理地址 1 1 生日電話到職日 1 城市街道 M 1 擁有參加控制 N N 工作時數 N 部門名稱 員工人數 眷屬姓名 眷屬 計劃代號 計劃 委託單位 關係 計劃名稱 執行地點 3
資料庫設計的步驟 企業組織 (Organization of Enterprise) DBMS 獨立 (DBMS-Independent) 特定 DBMS (DBMS-Specific) 資料庫需求 (Database Requirement) 實體關係圖 (Entity Relationship Diagram) ER 圖轉換關聯綱目 (ER to Relational Mapping) 使用 SQL 建立資料庫 (Use SQL to Construct Database) 需求收集與分析 (Requirement Collection and Analysis) 觀念綱目設計 (Conceptual Schema Design) 邏輯綱目設計 (Logical Schema Design) 實體綱目設計 (Physical Schema Design) 實作 (Implementation) 資料庫 (Database) 4
實體關係模型與自然語言 There are A in B. 則可轉換成 B has A. A 和 B 是實體類型,has 是關係類型 There are many employees in every department. 則可轉換成 Every department has many employees., employees 和 department 是實體類型,has 是關係類型 One employee works for only one department., 則 employee 和 department 是實體類型 works-for 是關係類型 5
實體關係模型與自然語言 The A of B is C. 如果 C 是專有名詞, 則 B 是實體,A 是 B 和 C 之間的關係類型 The supervisor of John Smith is James Bong., James Bong 是一個專有名詞, 屬於經理實體類型中的一個案例, 而 John Smith 則屬於員工實體類型中的一個案例,supervisor 則是關係類型, 用來描述經理實體類型和員工實體類型之間的關聯性 6
實體關係模型與自然語言 The A of B is C. 如果 C 不是專有名詞, 則 A 是 B 的屬性 The birthday of the employee is 06/30/1988., 因為 06/30/1988 不是專有名詞, 所以 birthday 是 employee 的屬性 7
文法結構和實體關係模型對應原則 專有名詞 (Proper Noun) 對應到實體 (Entity), 普通名詞 (Common Noun) 則對應到實體類型 (Entity Type) 形容詞 (Adjective) 或形容詞片語 (Adjective Phrase) 對應到實體類型上的屬性 (Properties) 及物動詞 (Transitive Verb) 對應到關係類型 (Relationship Type) 副詞 (Adverb) 或副詞片語 (Adverb Phrase) 對應到關係類型上的屬性 (Properties) 8
實體和實體類型 實體類型在實體關係圖中通常是以 長方形 表示 員工 ( 身分證號碼, 姓名, 生日, 年齡, 電話, 地址, 薪水 ) 實體類型 (Entity Type) e 1 (A123456789, 李鴻章, 61/09/01, 38, {89511111, 89512222}, 板橋市忠孝路 100 號, 28000) e 2 (E123456789, 李紹綸, 58/11/01, 41, NULL, 台北市信義路 200 號, 50000) e 3 (F123123123, 簡嘉祐, 61/07/11, 38, NULL, 台北市仁愛路 168 號, 43000) e 4 (F212121212, 賴怡君, 63/07/03, 36, 77388888, 板橋市四川路 120 號, NULL) 實體集合 (Entity Set) e 5 (F232323232, 莊雅玫, 62/12/04, 37, 27041234, 台北市信義路 100 號, 43000) 9
弱實體類型 沒有主鍵的實體類型 弱實體類型的存在都必須完全依附於其他的強實體類型 當該弱實體所依附的物主實體被刪除時, 此弱實體也會立刻被刪除 其所依附的強實體類型, 我們稱之為 確認物主 (Identifying Owner) 弱實體類型與其 確認物主 間的關係類型又被稱之為 確認關係類型 (Identifying Relationship Type) 在同一 確認物主 實體中, 我們可利用鑑別元 (Discriminator) 的值區分不同的弱實體, 此鑑別元即 部分鍵 (Partial Key) 10
員工和眷屬之間的 擁有 關係 弱實體類型在實體關係圖中通常是以 雙線的長方形 表示 確認關係類型在實體關係圖中通常是以 雙線的菱形 表示 部分鍵屬性在實體關係圖中通常是以 在屬性名稱下加虛底線的橢圓形 表示 身分證號碼 眷屬姓名 員工 1 擁有 N 眷屬 e 1 A123456789 r 1 d 1 李嘉誠 e 2 E123456789 r 2 d 2 李嘉誠 e 3 F123123123 r 3 d 3 李筱嵐 e 4 F212121212 r 4 d 4 簡哲維 e 5 F232323232 r 5 d 5 李筱彤 11
實體類型的屬性 每一個實體皆會藉由一組屬性來描述該實體的特性 員工實體各個屬性的表示法 員工 ( 身分證號碼, 姓名, 生日, 年齡, { 電話 }, 地址 ( 城市, 街道 ), 薪水 ) 員工實體 e 1 之屬性值 e 1 身分證號碼 =A123456789 姓名 = 李鴻章生日 =61/09/01 年齡 =34 電話 ={89511111, 89512222} 地址城市 = 板橋市街道 = 忠孝路 100 號薪水 =28000 12
不同類型的屬性 簡單型屬性 (Simple Attribute) : 當屬性的值是單一而且不可分時 複合型屬性 (Composite Attribute) : 當一個屬性的值是由其它幾個屬性的值所組合而成時 多重值屬性 (Multi-valued Attribute) : 當一個屬性的值不是單元值, 而是具有多重值時 推導型屬性 (Derived Attribute) : 當一個屬性的值可經由某種方式計算或由另一個屬性推論而得時 鍵屬性 (Key Attribute) : 當一個屬性是實體類型中用來識別實體的屬性時 13
員工實體類型的實體關係圖 姓名 身分證號碼 生日 年齡 員工 電話 薪水 地址 城市 街道 14
關係和關係類型 關係類型在實體關係圖中通常是以 菱形 表示 關係 是指幾個實體間的連結 (Association) 關係類型 則是指具有相同性質的關係所成的集合 15
關係類型牽涉到兩個不同的實體類型 管理 二次元關係類型的案例 到職日 二次元關係類型 員工 1 管理 1 部門 e 1 李鴻章 e 2 李紹綸 r 1 89/08/01 d 1 資訊 e 3 簡嘉祐 e 4 賴怡君 r 2 92/02/01 d 2 研發 e 5 莊雅玫 16
二次元關係類型 工作於 二次元關係類型的案例 員工 N 工作於 1 部門 e 1 李鴻章 r 1 e 2 李紹綸 r 2 d 1 資訊 e 3 簡嘉祐 r 3 e 4 賴怡君 r 4 d 2 研發 e 5 莊雅玫 r 5 17
高次元關係類型 關係類型所牽涉的實體類型超過兩個以上時供應商 供應 三次元關係類型的案例 s 1 萬恭 s 2 福喜 s 3 金發 s 4 安財計劃 p 1 A p 2 B 供應 r 1 r 2 r 3 r 4 r 5 r 6 r 7 元件 c 1 CPU c 2 記憶體 c 3 主機板 c 4 硬碟 c 5 顯示器 p 3 C p 4 D 18
遞迴關係類型 實體類型參與同一個關係類型不只一次時 直屬上司 遞迴關係類型的案例 員工 1 N 上司 下屬 直屬上司 e 1 李鴻章 e 2 李紹綸 e 3 簡嘉祐 下屬 r 1 上司上司 r 2 下屬 e 4 賴怡君 下屬 r 3 上司 e 5 莊雅玫 19
婚姻 遞迴關係類型的案例 遞迴關係類型 人 1 1 嫁 娶 婚姻 e 1 李鴻章 e 2 李紹綸 e 3 簡嘉祐 娶 r 1 嫁娶 e 4 賴怡君 嫁 r 2 e 5 莊雅玫 20
關係類型的屬性 在關係類型中, 亦可以有描述性的屬性存在 在 管理 關係類型中, 利用 到職日 屬性來描述某位員工開始任職於某部門的日期 到職日 員工 1 管理 1 部門 e 1 李鴻章 e 2 李紹綸 r 1 89/08/01 d 1 資訊 e 3 簡嘉祐 e 4 賴怡君 r 2 92/02/01 d 2 研發 e 5 莊雅玫 21
關係類型的屬性 參加 二元關係類型的 工作時數 屬性案例 工作時數 員工 M 參加 N 計劃 e 1 李鴻章 r 1 10 小時 p 1 P 1 e 2 李紹綸 r 2 8 小時 p 2 P 2 e 3 簡嘉祐 r 3 12 小時 p 3 P 3 e 4 賴怡君 r 4 8 小時 p 4 P 4 e 5 莊雅玫 r 5 12 小時 p 5 P 5 r 6 7 小時 r 7 5 小時 r 8 11 小時 r 9 6 小時 22
關係類型的各種限制 基數比例限制 (Cardinality Ratio Constraints) 一對一 (One-to-one Relationship,1:1) 一對多 (One-to-many Relationship,1:N) 多對一 (Many-to-one Relationship,N:1) 多對多 (Many-to-many Relationship,M:N) 參與限制 (Participation Constraint) 全部參與 (Total Participation) 部分參與 (Partial Participation) 結構性限制 (Structure Constraint) 利用一個 (Min, Max) 有序對來表示 23
基數比例限制 基數比例限制 是用來限制此關係類型的 最大基數 (Maximum Cardinality) 管理 關係類型是屬於一對一關係 工作於 關係類型則是屬於多對一 ( 一對多 ) 關係 參加 關係類型則是屬於多對多關係 24
參與限制 參與限制 則是用來限制此關係類型的 最小基數 (Minimum Cardinality) 員工 實體類型參與 管理 關係類型是屬於 部分參與 關係 實體關係圖中通常是以 單線 連接實體類型和關係類型表示 部門 實體類型參與 管理 關係類型是屬於 全部參與 關係 實體關係圖中通常是以 雙線 " 連接實體類型和關係類型表示 25
結構性限制 基數比例限制和參與限制這兩種限制又可合稱為結構性限制 表示法的優點是可以清楚且精確地表示出各種基數比例限制和參與限制 對基數比例限制而言, 當 Max > 1 時, 則表示 多 的關係, 當 Max = 1 時, 則表示 1 的關係 對參與限制而言, 當 Min = 0 時, 則表示 部分參與, 當 Min > 0 時, 則表示 全部參與 26
使用結構性限制表示法 直屬上司上下薪水部門代號部門名稱姓名司 (0:N) (0:1) 屬 (1:1) (2:N) 工作於身分證號碼員工部門員工人數管理地址 (0:1) (1:1) 生日電話到職日 (0:N) 城市街道 (0:N) (1:3) 擁有參加控制 (1:N) (1:1) 工作時數 (1:1) 眷屬姓名 眷屬 計劃代號 計劃 委託單位 關係 計劃名稱 執行地點 27
設計實體關係圖時常犯的錯誤 扇型陷阱 (Fan Trap) 扇型陷阱 通常發生在各個實體類型之間存在著數個一對多關係, 這時候就可能使得資料庫設計者對於實體間的關係產生混淆 斷層陷阱 (Chasm Trap) 斷層陷阱 通常發生在各個實體類型之間應該存在的關係, 實際上根本是不存在的, 也就是說在兩個實體間根本無法找到一條路徑來連接 28
扇型陷阱 一位教師可以教授多位學生 一位教師可以講授多門課程 一個課程可以有多位學生選修 含扇型陷阱的學生 教師 課程實體關係圖 教師 1 1 教授 N 講授 N 學生 課程 29
扇型陷阱 學生實體 教授關係 教師實體 講授關係和課程實體案例 學生 N 1 1 教授教師 講授 N 課程 s 1 馬孝武 r 1 t 1 李紹綸 r 1 c 1 系統分析 s 2 陳中山 r 2 t 2 陳鵬文 r 2 c 2 資料庫 s 3 蘇文善 r 3 t 3 王毅新 r 3 c 3 會計學 s 4 謝大川 r 4 r 4 c 4 經濟學 s 5 張庭宇 r 5 r 5 c 5 英文 30
正確的學生 教師 課程實體關係圖 扇型陷阱 課程 1 N 選修講授 N 1 學生 教師 31
扇型陷阱 學生 N 1 N 選修課程 講授 1 教師 s 1 馬孝武 r 1 c 1 系統分析 r 1 t 1 李紹綸 s 2 陳中山 r 2 c 2 資料庫 r 2 t 2 陳鵬文 s 3 蘇文善 r 3 c 3 會計學 r 3 t 3 王毅新 s 4 謝大川 r 4 c 4 經濟學 r 4 s 5 張庭宇 r 5 c 5 英文 r 5 32
一位學生可以選修多門課程 每一位學生可以購買多本教科書 斷層陷阱 含斷層陷阱的學生 課程 教科書實體關係圖 學生 1 1 購買 N 選修 N 教科書 課程 33
斷層陷阱 教科書實體 購買關係 學生實體 選修關係和課程實體案例 教科書 N 1 1 購買學生 選修 N 課程 t 1 資料庫系統 r 1 s 1 馬孝武 r 1 c 1 資料庫 t 2 英語會話 r 2 s 2 陳中山 r 2 c 2 大一英文 t 3 英語入門 r 3 s 3 蘇文善 r 3 c 3 商業英文 t 4 初級會計學 r 4 s 4 謝大川 r 4 c 4 會計學 t 5 個體經濟學 r 5 r 5 c 5 經濟學 r 6 r 6 34
斷層陷阱 不含斷層陷阱的學生 課程 教科書實體關係圖 學生 1 1 購買選修 N N 教科書 N 1 指定 課程 35
斷層陷阱 教科書 N 1 1 購買學生 選修 N 課程 t 1 資料庫系統 r 1 s 1 馬孝武 r 1 c 1 資料庫 t 2 英語會話 r 2 s 2 陳中山 r 2 c 2 大一英文 t 3 英語入門 r 3 s 3 蘇文善 r 3 c 3 商業英文 t 4 初級會計學 r 4 s 4 謝大川 r 4 c 4 會計學 t 5 個體經濟學 r 5 r 5 c 5 經濟學 r 6 r 6 N 指定 1 r 1 r 2 r 3 r 4 r 5 36
實體關係圖的各種圖形符號圖形符號代表意義 實體類型 (Entity Type) 弱實體類型 (Weak Entity Type) 關係類型 (Relationship Type) 確認關係類型 (Identifying Relationship Type) 鍵屬性 (Key Attribute) 部分鍵屬性 (Partial Key Attribute) 簡單型屬性 (Simple Attribute) 複合型屬性 (Composite Attribute) 多重值屬性 (Multi-valued Attribute) 推導型屬性 (Derived Attribute) E R 1 E 2 1 1 E R 1 E 2 1 N E R 1 E 2 M N E R 1 E 2 (min,max)(min,max) E 1 R E 2 在 R 中 E 1 是全部參與 (Total Participation) 而 E 2 是部分參與 (Partial Participation) 在 R 中 E 1 與 E 2 間是一對一關係 (Cardinality Ratio 1:1) 在 R 中 E 1 與 E 2 間是一對多關係 (Cardinality Ratio 1:N) 在 R 中 E 1 與 E 2 間是多對多關係 (Cardinality Ratio M:N) 在 R 中 E 1 與 E 2 間的結構性限制 (Structure Constraint) 37
加強型實體關係模型 沿用所有的實體關係模型的觀念 超類別 (Superclass) 和子類別 (Subclass) 特殊化 (Specialization) 和一般化 (Generalization) 種類 (Categories) 屬性繼承 (Attribute Inheritance) 38
超類別和子類別 將某些實體類別更進一步地區分成若干個有意義的子群組 員工 (Employee) 實體類別可以進一步地區分成秘書 (Secretary) 工程師 (Engineer) 技師 (Technician) 或管理者 (Manager) 員工稱為這些子群組的一個超類別 (Superclass) 秘書 工程師 技師或管理者都可稱為是員工的一個子類別 (Subclass) 稱為 超類別 / 子類別 關係 (Superclass / Subclass Relationships) 稱為 是 IS-A 關係 (IS-A Relationships) 39
特殊化 (Specialization) 將一個超類別區分成數個子類別的過程 是一種由上而下 (Top Down) 的處理過程 強調各實體類型之間的差異性 尚未經過特殊化處理的員工實體類型 姓名 生日 身分證號碼 員工 電話 地址 打字速度專長責任區 40
特殊化 (Specialization) 經過特殊化處理後的員工實體類型 姓名 生日 身分證號碼員工工作類型 IS-A 電話 地址 秘書工程師推銷員 打字速度專長責任區 41
一般化 (Generalization) 將擁有共同特徵的數個類別聚合成一個超類別, 而原始的各個類別變成為這個超類別其中一個子類別的過程 是一種由下而上 (Bottom Up) 的處理過程 強調各實體類型之間的共同性 尚未經過一般化處理的秘書 工程師和推銷員實體類型 姓名 生日 身分證號碼 秘書 電話 地址 姓名 生日 身分證號碼 工程師 電話 地址 姓名 生日 身分證號碼 推銷員 電話 地址 打字速度 專長 責任區 42
一般化 (Generalization) 經過一般化處理的秘書 工程師和推銷員實體類型 姓名 生日 身分證號碼 員工 工作類型 電話 地址 IS-A 秘書工程師推銷員 打字速度專長責任區 43
特殊化和一般化的限制 非連結限制 (Disjointness Constraint) 非連結 (Disjoint) 限制, 以 d " 連接表示 重疊 (Overlapping) 限制, 以 o " 連接表示 完全限制 (Completeness Constraint) 全部特殊化 (Total Specialization), 以 雙線 " 連接表示 部份特殊化 (Partial Specialization), 以 單線 " 連接表示 44
特殊化和一般化的限制 限制每一個員工最多只能擔任一種職務時 經過特殊化處理後三個子類別之間是部份 (Partial) 且非連結 (Disjoint) 的 姓名 生日 身分證號碼 員工 電話 地址 d 秘書工程師推銷員 打字速度專長責任區 45
特殊化和一般化的限制 允許某些員工可同時擔任 秘書 工程師 或 推銷員 兩種以上不同的職務時 經過特殊化處理後三個子類別之間是全部 (Total) 且重疊 (Overlapping) 的 姓名 生日 身分證號碼 員工 電話 地址 o 秘書工程師推銷員 打字速度專長責任區 46
特殊化和一般化的限制 限制每一個員工不是 秘書 就是 工程師 或 推銷員, 亦即只能是這三種子類別中的一種職務, 而且每一個員工最多只能擔任一種職務時 經過特殊化處理後三個子類別之間是全部 (Total) 且非連結 (Disjoint) 的 姓名 生日 身分證號碼 員工 電話 地址 d 秘書工程師推銷員 打字速度專長責任區 47
特殊化和一般化的限制 沒有限制每一個員工一定是 秘書 工程師 或 推銷員, 亦即可以擔任這三種子類別以外的職務, 而且允許某些員工可同時擔任 秘書 工程師 或 推銷員 兩種以上不同的職務時 經過特殊化處理後三個子類別之間是部份 (Partial) 且重疊 (Overlapping) 的 姓名 身分證號碼 電話 生日 員工 地址 o 秘書工程師推銷員 打字速度專長責任區 48
點陣和共享子類別 階層型式 或 點陣型式..當一個子類別的實體類型中也可以擁有自己的子類別時 階層型式 (Hierarchy Form)..每一個子類別限制只能隸屬於某一個超類別時稱之, 即所謂單一繼承 (Single Inheritance) 點陣型式 (Lattice Form)..每一的子類別可以隸屬於多個超類別時稱之, 即所謂多重繼承 (Multiple Inheritance) 共享子類別 (Shared Subclasses)..一個子類別同時隸屬於一個以上的超類別時 子類別不僅會繼承其直接超類別 (Direct Superclass) 的所有屬性, 同時也會繼承其所有祖先超類別 (Predecessor Superclass) 的所有屬性 49
大學資料庫加強型實體關係圖 點陣和共享子類別 姓名 生日 身分證號碼 人員 電話 地址 o 薪水 員工 學生 科系 d 工讀時數 d 職員 教師 學生助理研究生大學生 職稱 職級 學位 班級 d 課程 教學助理 研究助理 計劃 50
種類和聯合類型 透過一個 超類別 / 子類別 關係連接到一個以上的超類別實體類型, 而這個子類別我們就稱之為 種類 (Category) 或 聯合類型 (Union Types), 以 u 連接表示 聯合類型 的子類別實體可以只隸屬於 超類別 / 子類別 關係當中的 某一個 超類別實體 而 共享子類別 的子類別實體則必須隸屬於 超類別 / 子類別 關係當中的 所有的 " 超類別實體 51
種類和聯合類型 擁有者聯合類型和車輛登記聯合類型加強型實體關係圖 駕照號碼姓名地址公司名稱銀行名稱個人地址公司銀行地址 u 擁有者 M 擁有 購買日期 N 車輛登記 行照號碼 年份 車牌號碼 u 車牌號碼 年份 人數 轎車 卡車 噸數 52
聚合集 (Aggregation) 公司 求職者 工作三實體類型和 面試 三元關係類型實體關係圖 公司 面試 求職者 得到工作 53
聚合集 (Aggregation) 公司面試求職者時, 求職者需要看面試的結果, 才決定求職者會不會得到工作 錯誤的實體關係圖表示法 公司 1 N 面試 求職者 1 結果 N 得到工作 54
聚合集 (Aggregation) 利用聚合集觀念的加強型實體關係圖表示法 公司 1 N 面試 求職者 1 結果 N 得到工作 55
正確的實體關係圖表示法 聚合集 (Aggregation) 公司 參加 求職者 面試 日期 1 結果 N 得到工作 56