EER to Realtional Mapping. (Lecture 12)

1.

IE301
Analysis and Design of Data Systems
Lecture 12
EER to Relational Mapping
Aram Keryan

2. Mapping Specialization (Step 8)

a2
a1
...
k
an
C
S1
S2
...
Sm
Let’s denote:
R – relation
Attrs(R) – attributes of R
PK(R) – primary key of R
C – superclass
{S1, S2, … , Sm} – m subclasses
{k, a1, a2 ...an} – attributes of C
k – primary key

3. Mapping Specialization

Option 8A: Multiple relations—superclass and subclasses
Create a relation L for C with attributes Attrs(L) = {k, a1, ..., an} and PK(L) = k.
Create a relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes
Attrs(Li) = {k} ∪ {attributes of Si} and PK(Li) = k
C
k
a1 a2 . . . an
S1
k atr1 . . . attr2
S2
k atr1 . . . attr2
Sm
k atr1 . . . attr2
This option works for any specialization (total or partial, disjoint or overlapping)

4.

Option 8A: Example

5.

Mapping Specialization
Option 8B: Multiple relations—subclass relations only
Create a relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes
Attrs(Li) = {attributes of Si} ∪ {k, a1, ..., an} and PK(Li) = k
S1
k
a1 a2 . . . an atr1 . . . attr2
S2
k
a1 a2 . . . an atr1 . . . attr2
...
S3
k
a1 a2 . . . an atr1 . . . attr2
This option works for total disjoint specializations

6.

Option 8B: Example

7.

Mapping Specialization
Option 8C: Single relation with one type attribute
Create a single relation L with attributes
Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attributes of Sm} ∪ {t} and
PK(L) = k. The attribute t is called a type attribute whose value indicates the
subclass to which each tuple belongs, if any.
This option works only for disjoint specializations, and has the potential for
generating many NULL values
These options is not recommended if many specific attributes are defined
for the subclasses

8.

Option 8C: Example

9.

Mapping Specialization
Option 8D: Single relation with multiple type attributes
Create a single relation schema L with attributes
Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attributes of S m} ∪
∪ {t1, t2, ..., tm} and PK(L) = k.
Each t, 1 ≤ i ≤ m, is a Boolean type attribute indicating whether a tuple belongs
to subclass Si
This option works for overlapping specializations, and has the potential for
generating many NULL values (but will also work for a disjoint specialization).
These options is not recommended if many specific attributes are defined for
the subclasses

10.

Option 8D: Example

11.

12.

Option 8A is used for PERSON/{EMPLOYEE, ALUMNUS, STUDENT} relation

13.

Option 8C is used for the EMPLOYEE/ {STAFF, FACULTY, STUDENT_ASSISTANT} relation
Option 8D is used for the STUDENT_ASSISTTANT/{RESEARCH_ASSISTANT,
TEACHING_ASSISTANT}

14.

15.

Option 8D is used for the STUDENT relation

16. Mapping of Union Subclasses (Step 9)

17. Mapping of Union Subclasses (Step 9)

When a union subclass is defined by superclasses that have different keys a
surrogate key is used. For union subclass a separate relation is created with a
surrogate key in a role of a primary key. Also a surrogate key is included as a foreign
key in each relation corresponding to superclass. It is also recommended to add a
type attribute to the relation corresponding to union subclass to indicate the
particular entity type to which each tuple belongs.