ER to
Relational
Mapping
Chap 7
5th Edition
By Shweta Wadhera
Assistant Professor ( C.S )
DDUC
We will see that how we will create different tables from ER
diagram .
The different elements of the ER diagram are :-
Entity ( Strong )
Weak Entity
Attributes
Multivalued Attributes
Composite Attributes
Relationships
One : One
One : Many
Many : Many
Mapping ER –Diagram to Relation
E-R Diagram Relation
Eid
Name
Employee
(Eid,Name,Address,DOB,Salary)
EMPLOYEE
DOB Salary
Address
Mapping for Composite Attributes
E-R Diagram Relation
Eid
Employee
Name
(Eid,Name,Hno,Street,Locality,
DOB,Salary)
EMPLOYEE
DOB Salary
Address
Street
Hno Locality
Mapping for Multivalued Attributes
E-R Diagram Relations Formed
Eid Salary Employee
Name
(Eid,Name,Address,DOB,Salary)
EMPLOYEE
Emp-degrees
( Eid ,Degree )
DOB
Address
Degrees
• For each Multivalued Attribute A , create a new relation R.
• Relation R will include attribute A + P.key attribute K as foreign
key in R.
• This P.Key is of the Relation that represents the entity type that
has A as an attribute.
• Example of Phone no can also be taken ……
Mapping WEAK ENTITIES …..
Eid Name BirthDt
Name
EMPLOYEE Has DEPENDENT
DOB Salary Relationship
Address
Employee Emp-Dependent
(Eid,Name,Address,DOB,Salary) (Eid , Name , BirthDt,
Relationship )
What if we keep all dependents in the Employee table itself ?
Attributes of Relationship Type
The Relationship type can have attributes like
Entity Types .
For 1: 1 and 1:N relationship types, the attributes
of these relationship can be migrated to one of
the participating entity types. ( Bcoz a new
relation is not created corresponding to their
relationship type )
For M : N relationship types , attributes must be
specified as the relationship attribute. Infact they
are then part of the new created relation
corresponding to the relationship type.
Mapping 1 : 1 Binary Relationship Type
Approach 1 : Using foreign Key ( Most popular approach )
Choose one of the participating relation A ( usually one with
total participation );
Include in A, as foreign key , the primary key of B.
If R has attributes , put them in relation A aswell.
In our example we map 1:1 relationship type MANAGES.
We choose for A …… the Entity type DEPARTMENT .( Total
participation in MANAGES .)
In DEPARTMENT , we take Primary Key of EMPLOYEE , and
rename it as Mgr_id.
WHAT if we choose for A …… EMPLOYEE ENTITY ?????
We include in A, the P.key of B . i.e in EMPLOYEE relation we
include the P.key of DEPARTMENT.
P. key will be renamed as Dept_managed .
But every Employee is not a Manager .
So for many tuples The Dept_managed attribute would be
NULL .
Hence , the ENTITY choosen for A , should be the one
which has TOTAL PARTICIPATION IN THE RELATIONSHIP .
MERGED RELATIONSHIP Approach ( 2 ) :
• In this we merge the two ENTITY Types and the
relationship into one single Relation.
• This would be appropriate when both Participations
are Total .
Mapping 1 : 1 Binary Relationship Type ( contd )
Approach 3 : Cross Reference or Defining Relationship’s
Relation Approach
Define a relation R to represent the relationship.
All attributes of relationship are included in R .
In addition , put the primary key of two relations A and B
into R.
The Primary key of R is a combination of the primary key
of A and B.
The relationship R is called Relationship’s relation or
sometimes LOOKup Table .
Generally used in M : N relation .
Relation schema Approach 3 ( Example …..)
Defining Relationship’s Relation
Employee ( Eid, Name , Address, DOB, Salary )
Department (Dnum , Dname, Location )
Manages (Eid , Dnum )
Mapping 1 : 1 Binary Relationship
Relationship Manages Between EMPLOYEE & DEPARTMENT
StartDt
Eid Dnum Location
Name
1 1
EMPLOYEE Manages DEPARTMENT
DOB Salary DeptName
Address
Relational schema using Approach 1 ( Foreign Key Approach ) :
Employee ( Eid, Name , Address, DOB, Salary ) ………..B
Department ( Dnum , Dname, Location , Mgr_id , StartDt) ….A
Mapping 1: N Binary Relation
DoJ
Eid Dnum Location
Name
N 1
Works
EMPLOYEE IN DEPARTMENT
DOB Salary DeptName
Address
On the N-side we will keep the attribute from the 1-side.
Employee is N-side. So we keep the Dnum from Department
(1-side) in the Employee Relation .
To represent the relation WORKS IN , Dnum stored in Employee .
DoJ will also migrate on the N-side
Foreign key Approach 1 :
Employee ( Eid, Name , Address, DOB, Salary, Dnum , DoJ )
Department (Dnum , Dname, Location )
Approach used for 1 : N Binary Relation
Mapping……
A represents Entity type at 1-side ( Department )
B represents entity type at N-side (Employee )
Include the Primary key of A as foreign key in B.
This foreign key will represent the relationship type
R , here R is WORKS In .
Mapping M : N Binary Relationship Type
Create a New Relation S to represent R.
Include in S, as foreign keys the P.Key of A and B
and all simple attributes of R.
The combination of P.keys of A and B will make
the primary key of S.
StartDt Name Pcode
Eid Name
M N
EMPLOYEE Joins IN PROJECT
DOB Salary
Address Budget
Relations formed …..
Employee (Eid,Name, Address, DOB, Salary)
Project (Pcode, Name , Budget )
JoinsIn (Eid,Pcode ,StartDt )
Mapping Unary Relationship
Name
Eid
Supervisor
EMPLOYEE Supervises
DOB
Supervisee
Address
Salary
Relation
EMPLOYEE ( Eid , Name , Address , DOB, Salary, super-eid )
EER to Relational
Mapping
Option 1 : Universal
Superclass and each subclass get a Relation
The Rule ……….
It creates a relation for the Superclass and its Attributes
seperately
PLUS
A relation for each subclass includes the specific ( or local )
Attributes of it
PLUS
The P.key of the superclass which is propogated to subclass
relation and becomes the subclass’ s P . Key also.
Option 1 : Universal
Superclass and each subclass get a Relation
DOB
Name Address
Essn
Jobtype
EMPLOYEE
Jobtype
d
Typing
Speed Etype
TGrade
SECRETARY TECHNICIAN ENGINEER
Employee ( Essn,Fname,Minit , Lname, DOB, Address, Jobtype)
Secretary ( Essn , Typing speed )
Technician ( Essn , Tgrade )
Engineer ( Essn , Etype )
Option 2 : Multiple Relations
Subclass relations only
- This option works well only when both the disjoint and
Total participation by subclasses .
- Creates a relation for each subclass .
- Includes the specific or local attributes and all the
attributes of the superclass .
- The P.key of the superclass is propagated to subclass
relations and becomes its P.Key .
Option 2 : Multiple Relations
Subclass relations only .
Vehicle Price License
id Plate No
VEHICLE
No of
Passengers d No of
Max Axles
Speed
CAR TRUCK
CAR ( Vehicle id, License Plate No, Price , Maxspeed , No of
Passengers )
TRUCK ( Vehicle id , License Plate No, Price , No_of_Axles )
Option 1 :
Option 3 : ALL – in- One
Single Relation with one type Attribute
- It creates a single relation to represent the Superclass and
all its subclasses .
- This option is NOT recommended if many specific
attributes are defined for the subclasses .
- An entity that does not belong to some of the subclasses
will have NULL values for the specific attributes of these
subclasses .
- If few subclass attributes exist, then however these
mappings are preferable to option 1 & option 2.
- Is used to handle disjoint subclasses by including a single
type or discriminating Attribute ‘ t ‘ to indicate that to
which of the subclasses each tuple belongs .
Option 3 :
DOB
Name Address
Essn Jobtype
EMPLOYEE
Jobtype
Secretary d Engineer
Typing
Speed Etype
TGrade
Technician
SECRETARY ENGINEER
TECHNICIAN
EMPLOYEE ( Ssn , Fname , Minit , Lname , DOB , Address , Jobtype ,
Typing speed , Tgrade ,EngType )
Option 4 : Single Relation
with multiple type attributes
- Is used to handle Overlapping subclasses by including
Boolean type flag field , one for each subclass .
- It can also be used for disjoint subclases .
- Each type field can have a domain { yes , no }
- Where a value yes indicates that the tuple is a member of
subclass s [ i ] .
Option 4 : Single Relation
with multiple type attributes
Description
Part No
PART
List Price
Batch No
Mfd Dt
O Supplier
Name
Manufactured Part Purchased Part
PART ( Part No , Description , Mflag , Mfd_dt , Batch no , Pflag ,
S_name , List Price )
