Ch06 EER-Relational Transformation

嘉義大學管理資訊系 — 李延憲教授｜ EER 模型轉換為關聯式資料庫

進度：0%0 / 0 題

📖 教學✓

✏️ 練習✓

📚 術語表✓

🏆 成就✓

Rule 1✓

Rule 2✓

Rule 3✓

Rule 4✓

Rule 5✓

Rule 6✓

Rule 7✓

Rule 8✓

Rule 9✓

Rule 1: Regular Entity Type → Relation

✦ 每個 regular (strong) entity type E 建立一個 relation R

✦ E 的所有 single-valued simple attributes 成為 R 的 attributes

✦ E 的 primary key 成為 R 的 primary key

✦ Composite attributes 只取其 simple components

範例

Student(SSN, FName, LName, Address)

SSN 為 primary key（底線標示），其餘為 simple attributes。

Q1: Regular entity type 轉換時，composite attribute 如何處理？

(A) 整個 composite attribute 作為一個欄位 (B) 只取其 simple component attributes (C) 忽略不轉換

Q2: Entity type 的 PK 在轉換後的 Relation 中扮演什麼角色？

(A) 成為 Relation 的 Primary Key (B) 成為 Foreign Key (C) 成為一般 attribute

Rule 2: Weak Entity Type → Relation

✦ 為 weak entity type W 建立 relation R

✦ 包含 W 的所有 simple attributes

✦ 複製 identifying (owner) entity 的 PK 作為 R 的 foreign key

✦ R 的 PK = owner entity 的 PK + W 的 partial key

範例

Dependent(SSN, Name, Gender, Relation)

SSN 是來自 Employee 的 FK，Name 是 partial key。PK = {SSN, Name}。

Q1: Weak entity 的 PK 由什麼組成？

(A) 只有 partial key (B) 只有 owner entity 的 PK (C) Owner entity 的 PK + partial key

Q2: Identifying entity 的 PK 在 weak entity relation 中是什麼？

(A) 只是 PK 的一部分 (B) 同時是 FK 也是 PK 的一部分 (C) 只是 FK

Rule 3: 1:1 Association Relationship

✦ 選擇其中一邊（通常是 total participation 那邊）

✦ 把另一邊的 PK 作為 FK 加入被選的 relation

✦ Relationship 本身的 attributes 也加入該 relation

✦ 也可以用 merge 或 cross-reference 方式

範例（FK 加在 Department 端）

Department(DNo, DName, MgrSSN, MgrStartDate)

MgrSSN 是 FK 參照 Employee(SSN)，MgrStartDate 是 relationship attribute。

Q1: 1:1 relationship 轉換時，FK 通常加在哪一邊？

(A) 隨便選一邊 (B) Total participation 那一邊（減少 NULL） (C) 一定要建新 relation

Q2: 1:1 relationship 的 attributes 放在哪裡？

(A) 和 FK 放在同一個 relation (B) 建立新的 relation 存放 (C) 丟棄不轉換

Rule 4: 1:M Association Relationship

✦ 把 "one-side" entity 的 PK 加入 "many-side" entity 的 relation 作為 FK

✦ Relationship 的 attributes 也加入 many-side 的 relation

✦ 不需要建立新的 relation

範例

Employee(SSN, FName, LName, DNo)

DNo 是 FK 參照 Department(DNo)。FK 加在 many-side (Employee)。

Q1: 1:M relationship 中，FK 加在哪一邊？

(A) One-side (B) Many-side (C) 建立新 relation

Q2: Department(1) — WorkFor — Employee(M)，轉換後 DNo 出現在哪？

(A) Employee relation 中作為 FK (B) 新建的 WorkFor relation 中 (C) Department relation 中作為 FK

Rule 5: M:M Association Relationship

✦ 建立新的 relation R 代表此 relationship

✦ 兩邊 entity 的 PK 都作為 R 的 FK

✦ R 的 PK = 兩個 FK 的組合

✦ Relationship 的 attributes 也加入 R

範例

Works_On(SSN, PNumber, Hours)

SSN 是 FK→Employee，PNumber 是 FK→Project。PK = {SSN, PNumber}。Hours 是 relationship attribute。

Q1: M:N relationship 轉換時需要？

(A) 在其中一邊加 FK (B) 合併兩個 entity (C) 建立新的 relation

Q2: 新建 relation 的 PK 是什麼？

(A) 其中一邊的 PK (B) 兩邊 PK 的組合 (C) 新增一個 surrogate key

Rule 6: N-ary Relationship

✦ 建立新的 relation R

✦ 所有參與 entity 的 PK 都作為 R 的 FK

✦ PK 通常是所有 FK 的組合

✦ 例外：若某 entity 的 max cardinality 是 1，其 FK 不加入 PK

範例

Work_on(S_SSN, A_SSN, Title)

三個 FK 分別參照 Student、Advisor、Thesis。PK = 三者組合。

Q1: N-ary relationship 中，若某 entity 的 max cardinality 是 1，其 FK 是否加入 PK？

(A) 是，一律加入 (B) 否，max=1 的不加入 PK (C) 視情況而定，沒有規則

Q2: Ternary relationship 轉換後，新 relation 至少有幾個 FK？

(A) 1 個 (B) 2 個 (C) 3 個

Rule 7: Specialization / Generalization

有 4 種轉換方式（Options A, B, C-1, C-2）：

Option A: Superclass + Subclass 各一個 Relation

每個 subclass relation 包含 superclass 的 PK 作為 FK（也是自己的 PK），加上 subclass 特有的 attributes。

Person(SSN, Name, BDate)

Employee(SSN, Salary) — SSN 是 FK→Person

Student(SSN, GPA) — SSN 是 FK→Person

Option B: 只有 Subclass 有 Relation

每個 subclass relation 包含 superclass 的所有 attributes（複製）。適用於 total, disjoint specialization。

Employee(SSN, Name, BDate, Salary)

Student(SSN, Name, BDate, GPA)

Option C-1: 只有 Superclass 有 Relation（Disjoint）

加一個 type attribute 區分 subclass，所有 subclass attributes 都放在 superclass relation。

Person(SSN, Name, BDate, Type, Salary, GPA)

Type ∈ {'Employee', 'Student'}。不屬於該 type 的欄位為 NULL。

Option C-2: 只有 Superclass 有 Relation（Overlapping）

加 boolean flags（每個 subclass 一個 flag），所有 subclass attributes 都放在 superclass relation。

Person(SSN, Name, BDate, IsEmployee, IsStudent, Salary, GPA)

一個人可以同時是 Employee 和 Student。

Q1: 哪個 Option 適合 total, disjoint specialization 且不想保留 superclass relation？

(A) Option A (B) Option B (C) Option C-1

Q2: Overlapping specialization 適合用哪個 Option？

(A) Option B (B) Option C-1 (C) Option C-2（boolean flags）

Q3: Option A 中，subclass relation 的 PK 是什麼？

(A) Superclass 的 PK（同時也是 FK） (B) 新增的 surrogate key (C) Subclass 特有的 attribute

Rule 8: Multi-valued Attribute

✦ 為每個 multi-valued attribute A 建立新的 relation R

✦ R 包含 A 本身 + owner entity 的 PK 作為 FK

✦ R 的 PK = FK + A

範例

Phones(SSN, Phone)

SSN 是 FK→Employee。PK = {SSN, Phone}。

Q1: Multi-valued attribute 為什麼不能直接放在原 relation？

(A) 會違反命名規則 (B) 違反 1NF（一個欄位只能有一個值） (C) 會造成 PK 重複

Q2: Multi-valued attribute relation 的 PK 是什麼？

(A) 只有 owner entity 的 PK (B) 只有 multi-valued attribute (C) Owner PK + multi-valued attribute 的組合

Rule 9: Category (Union Type)

✦ 為 category 建立新的 relation，加入 surrogate key

✦ 每個 superclass 的 relation 加入 FK 指向 category relation 的 surrogate key

✦ Category relation 包含 category 共有的 attributes

範例

Owner(OwnerId, ...category attributes...)

Person(SSN, Name, OwnerId)

Company(CName, Address, OwnerId)

OwnerId 是 surrogate key。Person 和 Company 各加 FK 指向 Owner。

Q1: Category (Union Type) 為什麼需要 surrogate key？

(A) 因為不同 superclass 的 PK 可能不同型別 (B) 為了提高查詢效率 (C) 因為 category 沒有 attributes

Q2: Category 轉換後，FK 加在哪裡？

(A) Category relation 中 (B) 每個 superclass 的 relation 中 (C) 不需要 FK

Ex1 外送✓

Ex2 課程✓

Ex3 拍賣✓

Ex4 醫院✓

Ex5 住院✓

Ex6 ERD工具✓

Exercise #1：外送餐食訂購系統

ERD 描述

• Employee（strong entity）：eId(PK), pId, name, birthday, department, balance, phones(多值)

• Store（strong entity）：sName(PK), address, phone, description

• FoodItem（weak entity, owner=Store）：fName(partial key), description, unitPrice, discount

• Order（weak entity, owner=Employee）：date(partial key), description, salePrice

• Deposit（weak entity, owner=Employee）：dTime(partial key), amount

• Contains（M:N between Order & FoodItem）：num

Relational Schema（共 7 個 Relations）

1. Employee(eId, pId, name, birthday, department, balance)

2. Store(sName, address, phone, description)

3. FoodItem(sName, fName, description, unitPrice, discount)

→ Rule 2: sName FK→Store, PK={sName, fName}

4. Order(eId, date, description, salePrice)

→ Rule 2: eId FK→Employee, PK={eId, date}

5. Deposit(eId, dTime, amount)

→ Rule 2: eId FK→Employee, PK={eId, dTime}

6. Contains(eId, date, sName, fName, num)

→ Rule 5: M:N between Order & FoodItem

7. Phones(eId, phone)

→ Rule 8: multi-valued attribute

Q1: FoodItem 是 weak entity，它的 PK 是什麼？

(A) fName (B) {sName, fName} (C) sName

Q2: Contains relation 的 PK 包含幾個 attributes？

(A) 2 個 (B) 3 個 (C) 4 個（eId, date, sName, fName）

Q3: 這個系統總共產生幾個 Relations？

(A) 6 個 (B) 7 個 (C) 8 個

Exercise #2：課程資料庫

ERD 描述

• Course：cNo(PK), cName, cDesc

• Teacher：tNo(PK), tName, title, departments(多值)

• Student：sId(PK), sName, gender, bDate, email

• Item（weak entity, owner=Course）：iName(partial key), dueDate

• Teaches（M:N, Teacher-Course）

• Takes（M:N, Student-Course）：finalScore

• Evaluated（ternary: Student-Course-Item）：score

Relational Schema（共 8 個 Relations）

1. Course(cNo, cName, cDesc)

2. Teacher(tNo, tName, title)

3. Student(sId, sName, gender, bDate, email)

4. Item(cNo, iName, dueDate)

→ Rule 2: cNo FK→Course, PK={cNo, iName}

5. Teaches(tNo, cNo)

→ Rule 5: M:N

6. Takes(sId, cNo, finalScore)

→ Rule 5: M:N

7. Evaluated(sId, cNo, iName, score)

→ Rule 6: ternary relationship

8. Departments(tNo, department)

→ Rule 8: multi-valued attribute

Q1: Evaluated 是什麼類型的 relationship？

(A) Binary 1:M (B) Binary M:N (C) Ternary（三元）relationship

Q2: Teacher 的 departments 用哪條規則轉換？

(A) Rule 5 (M:N) (B) Rule 8 (Multi-valued Attribute) (C) Rule 2 (Weak Entity)

Exercise #3：拍賣網站

ERD 描述

• Member：mId(PK), name, email, startDate

• Merchandise（weak entity, owner=Member）：seqNo(partial key), name, description, expired, bottomPrice

• Category：cId(PK), description, parentCId(FK, self-referencing)

• Bid：bId(PK), price, dateTime, mId(FK→bidder), ownerMId+seqNo(FK→Merchandise)

• Purchases（M:N, Member-Merchandise）：price

• Categorized（M:N, Merchandise-Category）

Relational Schema（共 6 個 Relations）

1. Member(mId, name, email, startDate)

2. Merchandise(mId, seqNo, name, description, expired, bottomPrice)

→ Rule 2: mId FK→Member (owner), PK={mId, seqNo}

3. Category(cId, description, parentCId)

→ Rule 1 + self-referencing FK

4. Bid(bId, price, dateTime, mId, ownerMId, seqNo)

→ Rule 1: mId FK→Member(bidder), {ownerMId,seqNo} FK→Merchandise

5. Purchases(mId, ownerMId, seqNo, price)

→ Rule 5: M:N

6. Categorized(ownerMId, seqNo, cId)

→ Rule 5: M:N

Q1: Category 的 parentCId 是什麼類型的 FK？

(A) 參照 Member (B) Self-referencing FK（參照自己的 cId） (C) 參照 Merchandise

Q2: Bid relation 中有幾個 FK？

(A) 1 個 (B) 2 個（mId→Member, {ownerMId,seqNo}→Merchandise） (C) 3 個

Exercise #4：醫院掛號系統

ERD 描述

• Department：dNo(PK), dName, dCat, startDate

• Doctor：dId(PK), pId, name, birthday, position, dNo(FK→Department), chiefDNo(FK→Department)

• DiagnosisTime（weak entity, owner=Doctor）：startDateTime(partial key), capacity

• Patient：pNo(PK), pId, name, sex, birthday

• Registers（M:N, Patient-DiagnosisTime）：no, estimatedTime

Relational Schema（共 5 個 Relations）

1. Department(dNo, dName, dCat, startDate)

2. Doctor(dId, pId, name, birthday, position, dNo, chiefDNo)

→ Rule 1 + Rule 4: dNo FK→Department(所屬), chiefDNo FK→Department(主管)

3. DiagnosisTime(dId, startDateTime, capacity)

→ Rule 2: dId FK→Doctor, PK={dId, startDateTime}

4. Patient(pNo, pId, name, sex, birthday)

5. Registers(pNo, dId, startDateTime, no, estimatedTime)

→ Rule 5: M:N between Patient & DiagnosisTime

Q1: Doctor relation 中有幾個 FK？

(A) 1 個 (B) 2 個（dNo 和 chiefDNo 都指向 Department） (C) 3 個

Q2: Registers 的 PK 包含哪些 attributes？

(A) {pNo, dId} (B) {pNo, startDateTime} (C) {pNo, dId, startDateTime}

Exercise #5：擴充住院 EERD

ERD 描述（在 Exercise #4 基礎上擴充）

• 繼承 #4 的所有 entities

• Doctor specialization（disjoint, Option A）：

- Resident：residenceDate, mentorDId(FK→Doctor)

- AttendingDoc：position

- ChiefDoc：chargeDate, position

• Specialties（multi-valued attribute of Doctor）

• Inpatient（subtype of Patient）：startDate, treatingDId(FK), assistingDId(FK)

• Room：rNo(PK), loc

• Occupies（N:1, Inpatient-Room）

Relational Schema（在 #4 基礎上新增 6 個 Relations）

（保留 #4 的 5 個 Relations，以下為新增）

6. Resident(dId, residenceDate, mentorDId)

→ Rule 7 Option A: dId FK→Doctor, mentorDId FK→Doctor

7. AttendingDoc(dId, position)

→ Rule 7 Option A: dId FK→Doctor

8. ChiefDoc(dId, chargeDate, position)

→ Rule 7 Option A: dId FK→Doctor

9. Specialties(dId, specialty)

→ Rule 8: multi-valued attribute

10. Inpatient(pNo, startDate, treatingDId, assistingDId, rNo)

→ Rule 7 Option A + Rule 4: rNo FK→Room (Occupies N:1)

11. Room(rNo, loc)

Q1: Doctor 的 specialization 使用哪個 Option？

(A) Option A（superclass + subclass 各一個 relation） (B) Option B（只有 subclass） (C) Option C-1（只有 superclass + type）

Q2: Inpatient 的 Occupies (N:1) 關係如何轉換？

(A) 把 Room 的 PK (rNo) 加入 Inpatient 作為 FK (B) 建立新的 Occupies relation (C) 把 Inpatient 的 PK 加入 Room

Exercise #6：ERD 製作工具

ERD 描述

• EntityType：eName(PK), color, location

• WeakEntityType：wName(PK), color, location

• RelationshipType：rName(PK), color, location, degree

• Attribute：aId(PK), aName, domain, isSingleValued

• Has（1:N, EntityType-Attribute）：isKey

• Involves（M:N, EntityType-RelationshipType）：min, max

• R_Has（1:N, RelationshipType-Attribute）

• Identifies（1:1, WeakEntityType-RelationshipType）

• W_Has（1:N, WeakEntityType-Attribute）：isPartialKey

• W_Involves（M:N, WeakEntityType-RelationshipType）：min, max

• Composes（1:N self-referencing on Attribute）

Relational Schema（共 11 個 Relations）

1. EntityType(eName, color, location)

2. WeakEntityType(wName, color, location)

3. RelationshipType(rName, color, location, degree)

4. Attribute(aId, aName, domain, isSingleValued, compositeAId)

→ Rule 1 + Composes(1:N self): compositeAId FK→Attribute(aId)

5. Has(aId, eName, isKey)

→ Rule 4: 1:N, eName FK→EntityType。因 Attribute 可被多個 Entity 擁有，用 aId 作 PK 或加入 eName 作為 FK

6. Involves(eName, rName, min, max)

→ Rule 5: M:N

7. R_Has(aId, rName)

→ Rule 4: 1:N, rName FK→RelationshipType

8. Identifies(wName, rName)

→ Rule 3: 1:1

9. W_Has(aId, wName, isPartialKey)

→ Rule 4: 1:N

10. W_Involves(wName, rName, min, max)

→ Rule 5: M:N

11. Composes — 已合併到 Attribute 的 compositeAId

→ Rule 4: 1:N self-referencing

Q1: Composes 是什麼類型的 relationship？

(A) M:N binary (B) 1:1 binary (C) 1:N self-referencing（Attribute 參照自己）

Q2: 這個系統總共產生幾個 Relations？

(A) 9 個 (B) 11 個 (C) 13 個

📚 術語表

Relation

關聯式資料庫中的一個表格（table），由 rows (tuples) 和 columns (attributes) 組成。

Primary Key (PK)

唯一識別 relation 中每個 tuple 的 attribute 或 attribute 組合。不可為 NULL。

Foreign Key (FK)

參照另一個 relation 的 PK，用來建立 relations 之間的關聯。

Regular Entity Type

Strong entity，有自己的 PK，不依賴其他 entity 存在。對應 Rule 1。

Weak Entity Type

沒有自己完整 PK 的 entity，依賴 identifying (owner) entity。需要 partial key + owner PK 組成 PK。對應 Rule 2。

Partial Key

Weak entity 的部分識別 attribute，需搭配 owner entity 的 PK 才能唯一識別。

Identifying Relationship

連接 weak entity 與其 owner entity 的 relationship。

1:1 Relationship

兩個 entity 之間一對一的關聯。轉換時選一邊加 FK。對應 Rule 3。

1:M Relationship

一對多關聯。one-side 的 PK 加入 many-side 作為 FK。對應 Rule 4。

M:N Relationship

多對多關聯。需建立新的 relation，兩邊 PK 作為 FK 組成新 PK。對應 Rule 5。

N-ary Relationship

涉及三個或以上 entity 的 relationship。建立新 relation，所有參與者的 PK 作為 FK。對應 Rule 6。

Specialization / Generalization

EER 中的繼承關係。Superclass 與 subclass 的轉換有 4 種 Options (A, B, C-1, C-2)。對應 Rule 7。

Disjoint Specialization

一個 entity 只能屬於一個 subclass。用 'd' 標記。適合 Option B 或 C-1。

Overlapping Specialization

一個 entity 可以同時屬於多個 subclass。用 'o' 標記。適合 Option A 或 C-2。

Total Specialization

每個 superclass entity 必須屬於至少一個 subclass。用雙線標記。

Partial Specialization

superclass entity 可以不屬於任何 subclass。用單線標記。

Multi-valued Attribute

一個 entity 可以有多個值的 attribute（如電話號碼）。需建立新 relation。對應 Rule 8。

Composite Attribute

由多個 simple attributes 組成的 attribute（如 Name = FName + LName）。轉換時只取 simple components。

Category (Union Type)

多個 superclass 的聯集。需要 surrogate key。對應 Rule 9。

Surrogate Key

系統自動產生的人工 PK，用於 category 等無法用自然 key 的情況。

Total Participation

entity 的每個 instance 都必須參與 relationship。用雙線標記。影響 FK 放置位置。

Self-referencing FK

FK 參照同一個 relation 的 PK。如 Category 的 parentCId 參照自己的 cId。

🏆 成就系統

完成學習任務解鎖成就！

🎯

初試身手

答對第一題

📐

規則大師

完成所有 9 條規則的測驗

🍱

外送達人

完成 Exercise #1

📚

課程通

完成 Exercise #2

🔨

拍賣高手

完成 Exercise #3

🏥

醫院管理

完成 Exercise #4

🩺

住院專家

完成 Exercise #5

🛠️

ERD 工匠

完成 Exercise #6

💎

完美無瑕

所有題目全部答對

⭐

半程達標

完成 50% 的題目

📖

術語學者

瀏覽術語表

🔥

三連勝

連續答對 3 題