Understanding Entity-Relationship Model Translations to Tables

1. ERD Translation to tables

2. A Table Example • t1 = (foo,bar,baz,x) • t2 = (quz,bar,foo,y) • t1[a1] = (baz) • t2[a2] = ({y,z}) • t1[a̅] = (baz,{x,y}) • t2[k̅] = (quz,bar) t1 t2 Vector of all key attributes k̅ = (k1,k2) a̅ = (a1,a2) Vector of all non-key attributes

3. Entities k1 kn a1 am • A short representation: E k̅ = (k1,…,kn) a̅ = (a1,…,am) k̅ a̅ E

4. Entities • Translation to a table: k̅ a̅ E t: • Constraints: • t1[k̅] = t2[k̅] ⇒ t1[a̅] = t2[a̅]

5. Entities • k̅, a̅ may be empty • “No Key” – empty a̅ ! • Every attribute is a part of the key (underlined) • What is the meaning of empty k̅ ? k̅ a̅ E

6. Entities k1 kn a1 am • Any ai (or ki) may be multi-valued • In domain D, for a table row t: • for an attribute ai: t[ai] ∈ D • for a multi-valued attribute aj: t[aj] ∈ P(D) • a powerset. E ai

7. Another representation of a multi-valued attribute • A table without multi-valued attributes: • Tables - one for each multi-valued attribute: • Multi-valued attributes cannot be a part of the key k1 kn a1 am E

8. Relationships k̅R a̅R k̅1 a̅1 k̅2 a̅2 • Each k̅, a̅ may be empty • Translation to a table: R E1 E2 t:

9. Relationships k̅R a̅R k̅1 a̅1 k̅2 a̅2 • Constraints: • K1∧KR→K2∧AR(1) • K2∧KR→K1∧AR(2) • (K1∧KR→K2∧AR)∧(K2∧KR→K1∧AR) (1+2) 1 2 R E1 E2 K1≔ t1[k̅1]=t2[k̅1] K2≔ t1[k̅2]=t2[k̅2] KR≔ t1[k̅R]=t2[k̅R] AR≔ t1[a̅R]=t2[a̅R]

10. Relationships k̅R a̅R k̅1 a̅1 k̅2 a̅2 • Constraints: • πk2(E2)⊆ πk2(R) • πk2(R)⊆ πk2(E2) (true for any relationship!) R E1 E2

11. Ternary Relationships k̅R a̅R k̅1 a̅1 k̅2 a̅2 • Constraints: • K2∧K3∧KR→K1∧AR(1) • K1∧K3∧KR→K2∧AR(2) • (K2∧K3∧KR→K1∧AR)∧(K1∧K3∧KR→K2∧AR) (1+2) • Each arrow is a layer and several arrows are a conjunction (∧) of layers 1 2 R E1 E2 3 K1≔ t1[k̅1]=t2[k̅1] K2≔ t1[k̅2]=t2[k̅2] • K3≔ t1[k̅3]=t2[k̅3] KR≔ t1[k̅R]=t2[k̅R] AR≔ t1[a̅R]=t2[a̅R] E3 k̅1 a̅1

12. Roles k̅ a̅ • Translation to a table: role1 k̅R R E1 a̅R role2 t: • Constraints are the same

13. Aggregations ER k̅R a̅R k̅1 a̅1 k̅2 a̅2 • Turns the relationship into an entity with attributes of the relationship R E1 E2 k̅S S a̅S E3 k̅3 a̅3

14. Aggregations R: S: E1: E2: E3:

15. Weak Entities Translation to tables: k̅1 E1 a̅1 k̅2 E2 a̅2 k̅3 E3 a̅3

16. Weak Entities Constraints: πk1(E2)⊆ πk1(E1) πk1,k2(E3)⊆ πk1,k2(E2) k̅1 E1 a̅1 k̅2 E2 a̅2 k̅3 E3 a̅3

17. ISA • ISA – a branching weak entity without key components in the subtype. k̅1 a̅1 supertype E1 ISA subtype subtype E2 E3 a̅2 a̅3

18. ISA – Translations and Constraints k̅1 a̅1 E1: E2: E3: Constraints: πk1(E3)⊆ πk1(E1) πk1(E2)⊆ πk1(E1) E1 ISA ISA E2 E3 a̅2 a̅3

19. Exclusive ISA k̅1 a̅1 E1: E2: E3: Constraints: πk1(E3)⊆ πk1(E1) πk1(E2)⊆ πk1(E1) πk1(E2)∩πk1(E3)=∅ E1 ISA E2 E3 a̅2 a̅3

20. Covering All ISA k̅1 a̅1 NO Table! E1: E2: E3: Constraints: πk1(E2)∩πk1(E3)=∅ E1 Thick lines ISA E2 E3 a̅2 a̅3