Transcript Step 1

•Mapping from conceptual model (EER-M)
into a relational schema
prepared by chaamwe
1
Lifecycle of Database system
development
Mapping an EER-Model
into a relational schema


This approach involves applying
transformation rules (or steps) to the EER
model in order to achieve a relational
logical schema.
NB: the steps below should not be taken as
golden rules.
prepared by chaamwe
3
Mapping an EER-Model
into a relational schema


Application requirements vary from one
domain to another and from one user to
another.
Hence, the following steps should be
only taken as guidelines
Step 1


For each regular entity type (ignore
those with subclasses at this point) on
your diagram
create a table and nominate a primary
key (PK) for that relation.
prepared by chaamwe
6
Step 1 Cont:
Step 2:


For each weak entity type, create a
relation.
The PK is a combination of the identifier
of the parent entity and the identifier of
the weak or dependent entity (i.e. a
composite PK).
prepared by chaamwe
8
Step 2 cont
Step 4:



Each unary or binary type relationship
with a one-to-one cardinality is mapped
by placing a foreign key attribute in one
of the relational as the linking attribute.
If the relationship is mandatory OR
optional from both sides,
then it does not make a difference as to
where to place the foreign key.
prepared by chaamwe
10
Step 4 Cont:


If it is mandatory (total participation)
from one side and optional (partial
participation) from the other side,
then the PK of the optional side is
inserted as a foreign key at the
mandatory side.
prepared by chaamwe
11
Step 4 cont:
Step 5:


For Each unary or binary one-to-many
relationship type.
The PK from the 1-end is inserted as a
FK at the N-end.
prepared by chaamwe
13
Step 5 cont:
Step 6:


Each N:M relationship of any degree is
mapped to a new linking relation whose
PK includes the keys of all participating
relations (a composite PK).
Include all relationship’s attributes in
the new relation.
prepared by chaamwe
15
Step 6 cont:
Step 7:


Each multi-value attribute mapped to a
new relation.
The new relation should include an
attribute pertaining to the main relation
as a foreign key.
prepared by chaamwe
17
Step 7 cont:
Step 3.0

If we have a single optional (always!)
subtype then:




Create a relation for the superclass and
identify a PK for it;
Create a relation for the single subtype.
The Subtype’s PK is the same as the one
for the Superclass entity.
Add all other specific attributes to the
subclass.
Example
Step 3/ Multi Subclasses


If we have more than one subtype, there are
four different possibilities, depends on
Business Rules (Constraints).
These are:




Disjoint Optional
Disjoint Mandatory
Overlapping Optional
Overlapping Mandatory
Step 3a:


If the participation is disjoint optional,
then create a relation for the superclass
and identify a PK for it.
You also need to create a relation for
each subclass.
prepared by chaamwe
22
Step 3a: cont


The subclass PK is the same as the one
for the superclass entity.
Add all other specific attributes.
disjoint optional
EMPLOYEE
D
SECRETARY
TECHNICIAN
ENGINEER
prepared by chaamwe
24
Step 3a Results




EMPLOYEE (SSN, FNAME, MINT,
LNAME,...);
SECRETARY (SSN, TYPINGSPEED);
TECHNICIAN (SSN, TGRADE);
ENGINEER (SSN, ENGTYPE);
prepared by chaamwe
25
Step 3b:



If the participation is disjoint
mandatory, then create a relation for
each of the subclasses (no relation for
the superclass)
with the same PK you have chosen
earlier for the superclass.
Add all the specific attributes to the
appropriate subclass.
prepared by chaamwe
26
disjoint mandatory

EG
EMPLOYEE
D
SALARIEDEMPLOYEEE
HOURLYEMPLOYEE
prepared by chaamwe
27
Step 3b Results


SALARIED_EMPLOYEE (SSN, SALARY,
FNAME, MINT, LNAME,...);
HOURLY_EMPLOYEE (SSN, PAYSCALE,
FNAME, MINT, LNAME,...);
prepared by chaamwe
28
Step 3c:



If the participation is overlapping optional,
then create a single relation to represent
the superclass and all its subclasses.
Identify the PK, as well as, a type or flag
attribute to specify class membership.
The type attribute is used to indicate the
participation occurrences of the superclass
in the subclasses.
prepared by chaamwe
29
Overlapping Optional



The mapping is not recommended if
many specific attributes are defined at
the subclass, or
if the subclasses are involved in
relationships among themselves or with
other entities.
In these cases, this option should be
treated as a disjoint optional (step 3a).
prepared by chaamwe
30
overlapping optional

EG
EMPLOYEE
O
SECRETARY
TECHNICIAN
ENGINEER
prepared by chaamwe
31
Step 3c Result



EMPLOYEE (SSN, FNAME, MINT,
LNAME, … JOBTYPE , TYPINGSPEED,
TGRADE, ENGTYPE).
OR
EMPLOYEE (SSN, FNAME, MINT,
LNAME, …,
EMP_JOB (SSN, TYPINGSPEED,
TGRADE, ENGTYPE)
prepared by chaamwe
32
Step 3d



The overlapping mandatory case.
In this case you need to create a single
relation to represent the superclass and
all it subclasses.
Identify the PK as well as a type or flag
attribute to specify class membership.
prepared by chaamwe
33
Step 3d cont:



If the flag is “on” for a specific subclass
means that the superclass has a
specialisation.
At least one of the flags should be “on”.
Other flags could be “on” or “off”.
overlapping mandatory



The mapping is not recommended if
many specific attributes are defined at
the subclass, or
if the subclasses are involved in
relationships among themselves or with
other entities.
In these cases, this option should be
treated as a disjoint optional (step 3a).
prepared by chaamwe
35
overlapping mandatory

EG1
PART
O
MANUFACTURED
PART
PURCHASED
PART
prepared by chaamwe
36
Step 3d Result

PART (PartNo, Description,
MFlag, DrawingNo, ManufactureDate,
BatchNo,
PFlag, SupplierName, ListPrice);
prepared by chaamwe
37
Other rules


There are few semantic concepts that
exist in the EER model that
need to be resolved in order to be
mapped easily into a relational logical
model.
prepared by chaamwe
38
Remove Complex Relationship


This step involves removing or
decomposing any relationship of degree
d,
where d is greater than two into d oneto-many relationships linked via a weak
entity type.
prepared by chaamwe
39
A ternary relationship
TENANT
PROPERTY
LEASES
prepared by chaamwe
STAFF
40
A Decomposed ternary
relationship
TENANT
HOLDS
ASSOCIATED WITH
LEASE AGREEMENT
AGREEMENT
LEASE
ORGANISES
STAFF
PROPERTY
prepared by chaamwe
41
Remove Recursive
Relationships

This step is to decompose any recursive
relationship by creating a weak entity
type
prepared by chaamwe
42
Recursive Relationships
Employee
supervises
prepared by chaamwe
43
Recursive Relationships
supervises
Allocated Staff
Employee
Supervised by
prepared by chaamwe
44
prepared by chaamwe
45