EER to Realtional Mapping. (Lecture 12) презентация

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

Let’s denote:

specialization (total or partial, disjoint or overlapping)

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

k

a1

a2

. . .

an

C

k

atr1

attr2

. . .

S1

k

atr1

attr2

. . .

S2

k

atr1

attr2

. . .

Sm

disjoint specializations

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

atr1

attr2

. . .

S1

k

a1

a2

. . .

an

atr1

attr2

. . .

S2

k

a1

a2

. . .

an

atr1

attr2

. . .

S3

k

a1

a2

. . .

an

. . .

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

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 ti, 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

8D is used for the STUDENT_ASSISTTANT/{RESEARCH_ASSISTANT, TEACHING_ASSISTANT}

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.