CSE 880 : Database Systems

1. CSE 880: Database Systems Lecture : EER to Relational Mapping Reference: Read Chapter 9 of the textbook

2. EER to Relation Mapping • Mapping of superclass/subclass relationships • Mapping of shared subclasses • Mapping of union types (categories)

3. Mapping of Superclass/Subclasses • Let R be the superclass • {S1, S2,….,Sm} are the subclasses • Each subclass can have its own local attributes R a1 k a2 b1 … c b2 S1 S2 Sm

4. EER to Relation Mapping • Step 8: 4 possible approaches • Option 8A: Multiple relations – Superclass and subclasses • Option 8B: Multiple relations – Subclass relations only • Option 8C: Single relation with one type attribute • Option 8D: Single relation with multiple type attributes

5. Mapping EER Constructs to Relations R k a1 a2 S1 k b1 b2 Sm k c1 c2 • Option 8A: Multiple relations - superclass and subclass • Create superclass relation with attributes {k,a1,a2} and primary key (PK) = k • Create a relation Si for each subclass Si, with attributes {k} U {attributes of Si} with PK = k. R a1 k a2 c1 … b1 … c2 b2 S1 S2 Sm

6. Example

7. MySQL Workbench Example Click on subclass first before superclass. Repeat this for all 3 subclasses. Click on 1-1 identifying relationship type

8. MySQL Workbench Example

9. Mapping EER Constructs to Relations R a1 k S1 a2 k b1 b2 a1 a2 Sm c1 b1 k c1 c2 a1 a2 … c2 b2 S1 S2 Sm • Option 8B: Multiple relations –subclass relations only • Create a relation for each subclass Si, with the attributes of Si and the superclass • Works for total specialization (i.e., every entity in the superclass must belong to (at least) one of the subclasses) …

10. Tonnage Example Total specialization

11. Limitation of previous two approaches • Query: What does John Doe do as an employee? To answer this query, we need to scan all three subclass relations, which is inefficient!

12. R … k b1 b2 c1 c2 a1 a2 t EER to Relations Mapping • Option 8C: Single relation – with one type attribute (t) • Create a single relation with attributes {k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t} with primary key, PK = k • The attribute t is called a type (or discriminating) attribute • This option works for disjoint specialization C a1 k a2 t d c1 b1 … c2 b2 S1 S2 Sm

13. EngType Example d

14. R … … k b1 b2 c1 c2 a1 a2 t1 tm EER to Relations Mapping • Option 8D: Single relation – with multiple type attributes • Create a single relation with attributes {k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t1, t2,…,tm} and PK = k • ti is a Boolean attribute indicating whether a tuple belongs to Si. • This option works for overlapping specialization C a1 k a2 o c1 b1 … c2 b2 S1 S2 Sm

15. Example

16. So which option is better? • Depends on applications; must consider tradeoff: • Too many relations (options 8A and 8B) • Inefficient query processing • Single relation (options 8C and 8D) • Lots of nulls; may “lose” some meaningful relationships If everything is mapped to the Employee relation, we lose the relationship between hourly employee and trade union

17. EER to Relations Mapping • Mapping of Shared Subclasses (Multiple Inheritance) • A shared subclass is a subclass of several superclasses, indicating multiple inheritance. • These superclasses must have the same key attribute • Can apply any of the four options (subject to their restrictions – total/partial, overlapping/disjoint)

18. o Example STUDENT_ASSISTANT is a shared subclass of the EMPLOYEE and STUDENT entity types

19. Example • Since there are usually separate queries for employees, alumni, and students, we can use options 8A or 8B • Relations for option 8A: Person, Employee, Alumnus, Student • Relations for option 8B: Employee, Alumnus, Student

20. Example Suppose we want to use option 8C and 8D to group all employees into a single relation called EMPLOYEE o

21. o Example Suppose we want to use option 8C to group all students into a single relation called STUDENT

22. Putting it all together…