1 / 22

CSE 880 : Database Systems

CSE 880 : Database Systems. Lecture : EER to Relational Mapping. Reference: Read Chapter 9 of the textbook. EER to Relation Mapping. Mapping of superclass/subclass relationships Mapping of shared subclasses Mapping of union types (categories). Mapping of Superclass/Subclasses.

Download Presentation

CSE 880 : Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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…

More Related