Download DataBase Development and Implementation Lec04 - EERD To Relational Schema Mapping and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DBDI/ Lecture 4DBDI/ Lecture 4
EERD To Relational
Schema Mapping
Dr. Ala Al-Zobaidie
The slides are based on the textbook:
Fundamentals of Database Systems by Elmasri & Navathe 5th^ edition
Lecture’s Objectives
• Design Methodology: The three main
phases: conceptual, logical, and
physical design.
• Mapping the Enhanced ERD into a
Relational Schema
26/02/2008 Lecture_4 / MappingIntoRel 2
Relational Schema
– Direct mapping from conceptual model
into a relational schema
• There are 7 Steps for translating an EERD into a
Relational Schema
– Simplifying the Conceptual model
Design Methodology
• Def.:
– Structured approach that uses procedures,
techniques, tools, and documentation aids
to support and facilitate the process of
design.
26/02/2008 Lecture_4 / MappingIntoRel 3
g
• Database design methodology has 3
main phases:
– Conceptual database design
– Logical database design
– Physical database design.
Mapping EER into a Relational Schema /Company Example
26/02/2008 Lecture_4 / MappingIntoRel 4
Step 1
For each regular entity type
(ignore those with subclasses at
this point) on your diagram
26/02/2008 Lecture_4 / MappingIntoRel 5
create a table and nominate a PK
for that relation.
Step 1 /Cont
26/02/2008 Lecture_4 / MappingIntoRel 6
EMPLOYEE (FNAME, MINT, LNAME, SSN, BDATE, ADDRESS,
DEPARTMENT (DNAME, DNUMBER) SEX, SALARAY)
PROJECT (PNAME, PNUMBER, PLOCATION)
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)
26/02/2008 Lecture_4 / MappingIntoRel 7
PK).
Step 2 / Cont.
EMPLOYEE (FNAME, MINT,
LNAME, SSN, BDATE,
ADDRESS, SEX, SALARAY)
DEPARTMENT (DNAME,
26/02/2008 Lecture_4 / MappingIntoRel 8
( ,
DNUMBER)
PROJECT (PNAME, PNUMBER,
PLOCATION)
DEPENDENT (ESSN, DEPENDENT_NAME, SEX, BDATE)
F.K.
Step 3
- As there are no superclass/subclass
hierarchy, in our sample model, we skip
Step 3 at this stage. We will cover it later
26/02/2008 Lecture_4 / MappingIntoRel 9
EMPLOYEE (FNAME, MINT, LNAME, SSN, BDATE, ADDRESS,
SEX, SALARAY) DEPARTMENT (DNAME, DNUMBER)
PROJECT (PNAME, PNUMBER, PLOCATION)
DEPENDENT (ESSN, DEPENDENT_NAME, SEX, BDATE)
Step 4
Binary 1:
relationship
type mapped to
a foreign key
26/02/2008 Lecture_4 / MappingIntoRel 10
a foreign key
from one
relation
referring to the
other relation.
Step 4 / Cont
EMPLOYEE (FNAME, MINT,
LNAME, SSN, BDATE,
PROJECT (PNAME, PNUMBER,
PLOCATION)
26/02/2008 Lecture_4 / MappingIntoRel 11
LNAME, SSN, BDATE,
ADDRESS, SEX, SALARAY)
DEPARTMENT (DNAME, DNUMBER,
DEPENDENT (ESSN,
DEPENDENT_NAME, SEX,
BDATE)
MgrSSN, MgrStartDate)
Step 4 / Cont.
Partial vs. Total participation determines the placement
of the linking attribute.
26/02/2008 Lecture_4 / MappingIntoRel 12
Create a new linking table (i.e. treat the relationship as
N:M relationship) in the following cases:
If historical data is required.
If the relationship has many attributes associated
with it.
Step 3/ Multi Subclasses
- If we have more than one subtype, there
are four different possibilities, depends on
Business Rules (Constraints).
Di j i t O ti l
26/02/2008 Lecture_4 / MappingIntoRel 19
- Disjoint Optional
- Disjoint Mandatory
- Overlapping Optional
- Overlapping Mandatory
Step 3/ Options for mapping
Option 1: Multiple Relations – Superclass & subclasses
Works for any specialization (total or partial, disjoint or
overlapping)
Option 2: Subclass relation only
For total disjoint specialisation
26/02/2008 Lecture_4 / MappingIntoRel 20
Option 3: Single relation with one type attribute
For disjoint specialisation that potentially generating many
NULL values.
Option 4: Single relation with multiple type attributes
Boolean type attribute for overlapping specialization (also
work for disjoint)
Step 3 / Option 1 / Example
Disjoint Optional
Employee
d
TypingSpeed TGrade EngType
SSN
26/02/2008 Lecture_4 / MappingIntoRel 21
SECRETARY TECHNICIAN ENGINEER
EMPLOYEE (SSN, FNAME, MINT, LNAME, ...)
SECRETARY (SSN, TYPINGSPEED)
TECHNICIAN (SSN, TGRADE)
ENGINEER (SSN, ENGTYPE)
Step 3 / Option 1 / Steps
Disjoint Optional
- Create a relation for the superclass and
identify a PK for it;
C t l ti f h b l
26/02/2008 Lecture_4 / MappingIntoRel 22
- Create a relation for each subclass.
Its PK is the same as the one for the
Superclass entity.
- Add all other specific attributes to each
subclass.
Step 3 /Option 2 / Example
Disjoint Mandatory (total)
Employee
d Salary PayScale
SSN
26/02/2008 Lecture_4 / MappingIntoRel 23
SALARIED_EMPLOYEE (SSN, SALARY,...) TRADE_
UNION
HOURLY_
EMPLOYEE
SALARIED_
EMPLOYEE
BELON
M GS_TO
1
HOURLY_EMPLOYEE (SSN, PAYSCALE,...)
Step 3 /Option 2 / Steps/
Disjoint Mandatory
- Create a relation for each of the subclasses
(no relation for the superclass) with the
same PK you have chosen earlier for the
26/02/2008 Lecture_4 / MappingIntoRel 24
y
superclass.
- Add all the specific attributes to the
appropriate subclass.
Step 3 /Option 3 / Example
Overlapping Optional (partial)
Employee
O
TypingSpeed TGrade EngType
SSN
26/02/2008 Lecture_4 / MappingIntoRel 25
SECRETARY TECHNICIAN ENGINEER
EMPLOYEE (SSN, FNAME, MINT, LNAME, …,
JobType_Participation_Occ,
TYPINGSPEED, TGRADE, ENGTYPE)
Alternative approach (2 tables):
EMPLOYEE (SSN, FNAME, MINT, LNAME, …,
EMP_JOB (SSN, TYPINGSPEED, TGRADE, ENGTYPE)
Step 3 /Option 3 / Steps
Overlapping Optional
- 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
26/02/2008 Lecture_4 / MappingIntoRel 26
occurrences of the superclass in the subclasses.
- Numerous null values for specific subclass attributes.
- This mapping is not recommended.
- If many specific attributes are defined.
- If the subclasses are involved in relationships among themselves
or with other entities.
- Treated as Disjoint Optional (option 1)
Step 3 /Option 4 / Example
Overlapping Mandatory
PART
O
ManufactureDate
SupplierName
PartNo
Description
DrawingNo
Li P i
BatchNo
26/02/2008 Lecture_4 / MappingIntoRel 27
PART (PartNo, Description,
PURCHASED
_PART
MANUFACTURED
_PART
ListPrice
PFlag, SupplierName, ListPrice)
MFlag, DrawingNo, ManufactureDate, BatchNo,
Single relation
with multiple
Boolean type
attribute
Step 3 /Option 4 / Steps
Overlapping Mandatory
- 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.
If the flag is “on” for a specific subclass means
26/02/2008 Lecture_4 / MappingIntoRel 28
- 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”.
As in Option 3
Treated as Disjoint Optional (option 1)
Recap (3 rd^ part of lecture…after break)
- EERD can be done directly mapped into a
relational schema by following a set of
steps.
f
26/02/2008 Lecture_4 / MappingIntoRel 29
- The next and last part of this lecture is
looking at alternative way of mapping CDM
into a relational schema.
Alternative Mapping procedure:
Simplifying the Conceptual model
- How to remove features from a local conceptual
model that are not compatible with the relational
model.
- It is an alternative approach to the direct
mapping that we covered earlier in this lecture.
- It consists of few steps:
26/02/2008 Lecture_4 / MappingIntoRel 30
- It consists of few steps:
- Remove M:N relationships (Influenced by the
Relational Model)
- Remove Complex Relationship
- Remove Recursive Relationships
- Remove Relationships with attributes
- Re-examine 1:1 Relationship
- Remove Redundant Relationship
Summary
Mapping CDM into a Relational Schema can
be applied directly on the CDM by following
the 7 systematic steps.
OR
26/02/2008 Lecture_4 / MappingIntoRel 37
OR
CDM can be simplified to make it one step
closer to the Relational model and then
map according to steps into a Relational
Schema.
