Transcript EER to Relational

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


Let R be the superclass
{S1, S2,….,Sm} are the subclasses
– Each subclass can have its own local attributes
a1
R
k
a2
b1
b2
…
S1
S2
c
Sm
‹#›
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
‹#›
Mapping EER Constructs to Relations

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.
a1
R
R
k
a2
a1
a2
S1
k
b1
b2
c1
b1
b2
k
…
S1
S2
c2
Sm
Sm
k
…
c1
c2
‹#›
Example
‹#›
MySQL Workbench Example
Click on subclass first before superclass.
Repeat this for all 3 subclasses.
Click on 1-1 identifying relationship type
‹#›
MySQL Workbench Example
‹#›
Mapping EER Constructs to Relations

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)
a1
R
k
S1
a2
k
b1 b 2
a1
a2
a1
a2
…
b1
b2
Sm
c1
…
S1
S2
k
c2
c1 c2
Sm
‹#›
Example
Total specialization
Tonnage
‹#›
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!
‹#›
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
a1
C
k
R
a2
t
k
b1 b2 c 1 c 2
… a1
d
c1
b1
b2
…
S1
S2
c2
Sm
‹#›
a2
t
Example
d
EngType
‹#›
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
a1
C
k
R
a2
k
o
… a1
a2
t1
c1
b1
b2
b1 b2 c 1 c 2
…
S1
S2
c2
Sm
‹#›
…
tm
Example
‹#›
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
‹#›
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)
‹#›
Example
STUDENT_ASSISTANT is
a shared subclass of the
EMPLOYEE and STUDENT
entity types
o
‹#›
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
‹#›
Example
Suppose we want to use
option 8C and 8D to group all
employees into a single
relation called EMPLOYEE
o
‹#›
Example
Suppose we want to use
option 8C to group all students
into a single relation called
STUDENT
o
‹#›
Putting it all together…
‹#›