Extracting Tables from ERD

1. Extracting Tables from ERD

2. A Table Example • t1 = (foo,bar,baz,{x,y}) • t2 = (quz,bar,foo,{y,z}) • 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 • A short representation: k1 kn a1 am E k̅ = (k1,…,kn) a̅ = (a1,…,am) k̅ a̅ E

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

5. Entities • We assume k̅ can not be empty • a̅ may be empty k̅ a̅ E

6. Entities k1 kn a1 am • Any ai may be multi-valued • Multi-valued attributes cannot be a part of the key • 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 year genre name screenshots • A table without multi-valued attributes: • Tables - one for each multi-valued attribute: • Specifically for screenshots a power set won't work well movie

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

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

10. 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

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

12. Example name Birth_date name type connected user network interested1 tracking cause tracking tracker name country intrested1

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

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

15. Example name1 company Address Number Department

16. ISA • ISA – a branching weak entity without key components in the subclass k̅1 a̅1 superclass E1 ISA subclass subclass E2 E3 a̅2 a̅3

17. ISA – Translations and Constraints name calories Dessert ISA ISA Ice cream Cake flavor type Constraints: πname(Cake)⊆ πname(Dessert) πname(Ice cream)⊆ πname(Dessert) Dessert: Ice cream: Cake:

18. Exclusive ISA Ice cream: Sorbet: Frozen_yogurt: name calories Ice cream ISA Sorbet Frozen_yogurt additions fruit Constraints: πname(Sorbet)⊆ πname(Ice cream) • πname(Frozen_yogurt)⊆ πname(Ice cream) πname(Sorbet)∩πname(Frozen_yogurt)=∅

19. Covering All ISA Ice cream: Sorebt: Frozen_yogurt: NO Table! Name Calories Ice cream Thick lines ISA Sorbet Frozen_yogurt fruit additions Constraints: πname(Sorbet)∩πname(Frozen_yogurt)=∅