Analysis and Design of Data Systems. ER to Relational Mapping. (Lecture 10)

1.

IE301
Analysis and Design of Data Systems
Lecture 10
ER to Relational Mapping
Aram Keryan

2. Phases of Database Design

Miniworld
Relational
DBMS
Requirements
Collection and
Analysis
Conceptual
Design
Relational
Database
Schema
We are here

3. Entity-Relationship Diagram

4. Relational Database Schema

5. Step 1: Mapping Strong Entity Types

For each strong entity type E in the ER schema create a
corresponding relation R and include:
1.1) Include all the simple attributes of E:
Simple attributes of EMPLOYEE entity type are:
Bdate, Address, Salary, Sex
EMPLOYEE (Bdate, Address, Salary, Sex)

6. Step 1: Mapping Strong Entity Types

1.2) Include only the simple component attributes of a composite attribute:
Simple component attributes of a composite attribute Name of
EMPLOYEE entity type are: Fname, Minit, Lname
EMPLOYEE (Bdate, Address, Salary, Sex, Fname, Minit, Lname)

7.

Step 1: Mapping Strong Entity Types
1.3) Choose one of the key attributes of E as the primary key for R. If the
chosen key of E is a composite, then the set of simple attributes
form it will together form the primary key of R.
that
The key attribute of EMPLOYEE is Ssn
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)

8. Step 1: Mapping Strong Entity Types

DEPARTMENT (Dname, Dnumber)
PROJECT (Pname, Pnumber, Plocation)

9. Step 2: Mapping Weak Entity Types

EMPLOYEE (Bdate, Sex, Salary, Address,
Fname, Minit, Lname, Ssn)
1
For each weak entity type W in the ER
schema with owner entity type E,
create a relation R and include:
2.1) Include all the simple attributes (or
simple components of composite attributes)
of W as attributes of R.
DEPENDENT(Sex, Bdate, Relationship)

10. Step 2: Mapping Weak Entity Types

EMPLOYEE (Bdate, Sex, Salary, Address,
Fname, Minit, Lname, Ssn)
For each weak entity type W in the ER
schema with owner entity type E,
create a relation R and include:
1
2.2) Include as foreign key attributes of R, the
primary key attribute(s) of the relation(s) that
correspond to the owner entity type(s) E. Also
include the attributes of partial key of W. These
are the attributes of the primary key of W.
DEPENDENT(Sex, Bdate, Relationship,
Essn, Dependent_name)

11.

Step 3: Mapping of
Binary 1:1 Relationships
Let’s say S and T are entity types participating in 1:1 Relationship R
K
S
1
R
1
T
Case 1: One of the entity types, say T, has total participation and the
second entity type has partial participation: Then Include as a foreign key
in T the primary key of S. Include in S all the simple attributes of R.
Case 2: Both entity types have partial participations: Then arbitrary select
one of the entity types, say T, and include its primary key in the relation
corresponding to the second entity type (S) as a foreign key. Also, include
in the S all the simple attributes of the 1:1 relation (K).
Case 3: Both entity types have total participations: Then merge two entity
types in a single relation

12. Step 3: Mapping of Binary 1:1 Relationships

Start_date
1
1
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn)
DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)

13. Step 4: Mapping of Binary 1:N Relationships

Let’s say S and T are entity types participating in 1:N Relationship R
K
S
1
R
N
T
Identify the relation that represents the participating entity type at the
N-side of the relationship type (T)
Include as foreign key in T the primary key of the relation S
Include any simple attributes (K) (or simple components of composite
attributes) of the 1:N relationship type as attributes of T

14. Step 4: Mapping of Binary 1:N Relationships

N
1
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)
DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date)

15. Step 5: Mapping of Binary M:N Relationships

K
S
M
R
N
T
• For each binary M:N relationship R, create a new relation F to represent R.
• Include as foreign key attributes in F the primary keys of the relations that
represent the participating entity types (S and T); their combination will
form the primary key of F.
• Include any simple attributes of the M:N relationship (K)(or simple
components of composite attributes) as attributes of F

16. Step 5: Mapping of Binary M:N Relationships

Sex
EMPLOYEE (Bdate, Sex, Salary, Address, Fname, Minit, Lname, Ssn, Dno)
PROJECT (Pname, Pnumber, Plocation)
WORKS_ON (Pno, Hours, Essn)

17. Step 6: Mapping of Multivalued Attributes

A
S
• For each multivalued attribute A, create a new relation R.
• Include in R an attribute corresponding to A
• Include as a foreign key in R the primary key attribute of the
relation that represents the entity type or relationship that has
A as a multivalued attribute.
• The primary key of R is the combination of A and K.
• If the multivalued attribute is composite, we include its simple
components.

18. Step 6: Mapping of Multivalued Attributes

DEPT_LOCATIONS (Dnumber, Dlocation)

19. Step 7: Mapping of N-ary Relationships

• For each n-ary relationship type R, where n > 2, create a new
relation S to represent R.
• Include as foreign key attributes in S the primary keys of the
relations that represent the participating entity types.
• Include any simple attributes of the n-ary relationship type
(or simple components of composite attributes) as attributes
of S
• The primary key of S is usually a combination of all the
foreign keys that reference the relations representing the
participating entity types.
• if the cardinality constraints on any of the entity types E
participating in R is 1, then the primary key of S should not
include the foreign key attribute that references the relation
E’ corresponding to E

20. Step 7: Mapping of N-ary Relationships

SUPPLY (Sname, Proj_name, Part_no, Quantity)