ER → Relational Mapping

Mark Fontenot, PhD

Northeastern University

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

How We Will Describe Tables

3

SSN

FName

LName

BDate

Employee

SSN

SeqNum

Salary NOT NULL

From

To

SalaryInfo

Table Name

Underline PK Attributes

Arrows for relationships point from child table to parent table.

If the names of the FK/PK columns are different between the two tables, label the arrow with which columns are involved (as in a DataGrip diagram)

Indicate any additional constraints you know from the design process

The Process - Overview

• Step 1: Handle Strong Entity Types
• Step 2: Handle Weak Entity Types
• Step 3: Handle 1:1 Relationships
• Step 4: Handle 1:M Relationships
• Step 5: Handle M:N Relationships
• Step 6: Handle Multivalued Attributes

4

Step 1: Handling Strong Entity Types

• For each strong entity type, create a corresponding relation that includes all the simple attributes
• include simple attributes of composite attributes.
• Choose one of the candidate keys as the primary key.
• primary key may be a composite key.
• Note any other candidate keys will be marked as unique attributes when creating tables.
• useful in performance tuning / indexing later in physical design

5

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 1: Result for Example ERD

7

IDNum

bDate

fName

midInitial

lName

address

salary

Employee

deptID

name

Department

projID

name

location

Project

Step 2: Handling Weak Entity Types

• For each weak entity type, create a corresponding relation that includes all simple attributes.
• Add as a foreign key all of the primary key attribute(s) from the corresponding strong entity type
• The primary key of the new relation is the combination of the PK attributes of the owner entity type and the partial key (discriminator) of the weak entity

8

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 2: Result for Example ERD

10

IDNum

bDate

fName

midInitial

lName

address

salary

Employee

deptID

name

Department

projID

name

location

Project

IDNum

dep_id

name

relationship

bDate

Dependent

Step 3 - Handling Binary 1:1 Relationships

​

General Idea: Foreign Key used to map the relationship

​

1. Choose one relation as S, the other as T
• Better if S has mandatory participation b/c it reduces the number of null values.
2. Add to S all the simple attributes of the relationship
3. Add as a foreign key in S the primary key attributes of T

​

11

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 3: Result for Example ERD

13

IDNum

bDate

fName

midInitial

lName

address

salary

Employee

deptID

name

mgrIDNum

mgrStartDate

Department

projID

name

location

Project

Dependent

IDNum:mgrIDNum

IDNum

dep_id

name

relationship

bDate

Step 4: Handling Binary 1:N Relationships

• Assume S is the relation on the many side of the relationship; the other relation is T
• Add as a foreign key in S all of the primary key attribute(s) of T.
• Add any simple attributes of the relationship to S.

14

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 4: Result for Example ERD

16

IDNum

bDate

fName

midInitial

lName

address

salary

supIDNum

deptID

Employee

deptID

name

mgrIDNum

mgrStartDate

Department

projID

name

location

deptID

Project

Dependent

IDNum:mgrIDNum

empIDNum

dep_id

name

relationship

bDate

IDNum:supIDNum

IDNum:empIDNum

Step 5: Handling Binary M:N Relationships

• Create a new relation S (sometimes called the bridge table)
• Add as foreign keys to S the primary keys of both relations; their combination forms the PK of S.
• Add any simple attributes of the M:N relationship to S.

17

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 5: Result for Example ERD

19

IDNum

bDate

fName

midInitial

lName

address

salary

supIDNum

deptID

Employee

deptID

name

mgrIDNum

mgrStartDate

Department

projID

name

location

deptID

Project

Dependent

IDNum:mgrIDNum

empIDNum

name

relationship

bDate

IDNum:supIDNum

IDNum:empIDNum

IDNum

projID

hoursAssigned

Emp_Proj

Step 6 - Handling MultiValued Attributes

• If number of instances is unbounded or unknown
• Create a new relation S
• Add as foreign keys to S the primary keys of the corresponding relation
• Add the attribute to S (if composite, the simple attributes). The combination of all attributes in S forms the primary key of S.
• If number of instances is bounded and known
• Add additional attributes to relation, one for each potential instance.

20

emp_dep

(depends on)

locations

Department

Employee

Project

Dependent

name

fName

midInitial

lName

bDate

IDNum

address

salary

locations

deptID

name

location

projID

name

name

bDate

relationship

dept_emp

(houses)

dept_mgr

(manages)

emp_proj

(works_on)

dept_proj

(controls)

hours

startDate

emp_dep

(depends on)

emp_mgr

(supervises)

supervisor

supervisee

1…1

0…N

1…1

0…N

1…1

1…N

1…1

0…1

0…M

0…N

1…1

0…N

Dependent

dep_id

Step 5: Result for Example ERD

22

IDNum

bDate

fName

midInitial

lName

address

salary

supIDNum

deptID

Employee

deptID

name

mgrIDNum

mgrStartDate

Department

projID

name

location

deptID

Project

Dependent

IDNum:mgrIDNum

empIDNum

dep_id

name

relationship

bDate

IDNum:supIDNum

IDNum:empIDNum

IDNum

projID

hoursAssigned

Emp_Proj

deptID

location

Dept_Locs

Review

• Step 1: Handle Strong Entity Types
• Step 2: Handle Weak Entity Types
• Step 3: Handle 1:1 Relationships
• Step 4: Handle 1:M Relationships
• Step 5: Handle M:N Relationships
• Step 6: Handle Multivalued Attributes

23