From ER to Tables

1
From ER to Tables
Mapping ER Diagrams to Tables
2
Designing the Tables

• First approach
• Draw a dependency diagram
• Each statement is a single path through the
  diagram
• Tables are formed by traversing the dependency
  diagram

3
Simpler method Use ER Diagram

• Paths are hard to follow
• Create an ER Diagram using the Dependencies
• Transform Entities into Tables
• Transform Relationships into keys
• Youre done!
• No paths to follow!

4
Example ER Diagram
Fname
Lname
Number
Name
N
1
Gender
Locations
WORKS_FOR
Salary
Name
Address
DEPARTMENT
StartDate
Snn
EMPLOYEE
1
1
MANAGES
Birthdate
CONTROLS
Hours
N
N
1
WORKS_ON
PROJECT
1
N
SUPERVISION
DEPENDENTS_OF
Location
Name
N
Number
DEPENDENT
Name
Gender
Birthdate
5
Review Key Constraints
budget
did
Departments
Works_In

• An employee can work in many departments a
  dept can have many employees. In contrast, each
  dept has at most one manager, according to the
  key constraint on Manages.

6
Review Participation Constraints

• Does every employee work in a department?
• If so, this is a participation constraint
• Basically means at least one

since
since
name
dname
name
dname
ssn
lot
budget
did
budget
did
Departments
Employees
Manages
Works_In
since
7
Identify Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another (owner)
  entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set
  (one owner, many weak entities).
• Weak entity set must have total participation in
  this identifying relationship set.

Weak entities have only a partial key (dashed
underline)
8
Review Relationships

• If each policy is owned by just 1 employee

Bad design
Key constraint on Policies would mean policy
can only cover 1 dependent!
9
Review Relationships

• Previous example illustrated a case when two
  binary relationships were better than one ternary
  relationship.
• An example in the other direction a ternary
  relation Contracts relates entity sets Parts,
  Departments and Suppliers, and has descriptive
  attribute qty. No combination of binary
  relationships is an adequate substitute.

10
Review Relationships
qty
Departments
Parts
Contract
VS.
Suppliers
Parts
Departments
needs
can-supply
deals-with
Suppliers

• S can-supply P, D needs P, and D
  deals-with S does not imply that D has agreed
  to buy P from S.

11
How do you make the Tables?
Fname
Lname
Number
Name
N
1
Gender
Locations
WORKS_FOR
Salary
Name
Address
DEPARTMENT
StartDate
Snn
EMPLOYEE
1
1
MANAGES
Birthdate
CONTROLS
Hours
N
N
1
WORKS_ON
PROJECT
1
N
SUPERVISION
DEPENDENTS_OF
Location
Name
N
Number
DEPENDENT
Name
Gender
Birthdate
12
Each Entity becomes a Table
ENTITY TABLE PRIMARY
KEY Employee Employee Employee_SSN Departme
nt Department Department_Number Project
Project Project Number
13
1N Relationships - Create the Primary Key
For each 1N relationship create the Primary
Key from the entity on the 1 side to the entity
on the N side where it will become a foreign
key. EMPLOYEE WORKS_FOR DEPARTMENT
N
1 Department Number is moved into the
Employee entity
14
Examine Weak Entities

• The DEPENDENT entity cannot exist on its own and
  must have a parent entity (EMPLOYEE).
• They are identified in a MN relation as the
  intersect entity with the primary key the
  composite of the primary keys of the two (or
  more) other strong entities

15
Weak Entities Become Tables as Well
For each weak entity create a table - include
the Primary Key of the owner tables The Primary
Key becomes the owner key plus the
weak entity key Entity Table
Primary Key Dependent
Dependent Employee_SSN

Dependent_No
16
What about MN Relationships?
For each MN relationship create a new table with
the Primary Key being the the Primary Key of
both entities involved in the relationship EMPLOY
EE WORKS_ON PROJECT Empno
Hours
Projno
E1 P1 3 E1 P2
4 E2 P1 5 E3 P2
3
17
Representing the MN Relationships
18
What about Multi-valued Attributes?
For each multi-valued attribute create a new
relation. The Primary Key is the Primary Key of
the entity plus the Multi-valued
attribute. DEPARTMENT
LOCATION
TABLE
PRIMARY KEY DEPARTMENT_LOCATIONS DEPARTMENT
LOCATION
D1 MELB

D1 SYD

D2 MELB
19
What about N-ary Relationships?
For N-ary Relationships create a new entity and
create the Primary Key of each entity involved in
the relationship to the new entity Supplier
supplies Parts from Cities TABLES
PRIMARY KEY SUPPLIER_PARTS_CI
TIES SNo PNo City

S1 NUT
MELB
S1 NUT SYD
20
The Finished Table Design
EMPLOYEE
Fname Lname Essn Bdate Address M/F
Salary Superssn Dno
p.k
DEPARTMENT
f.k
Dname Dnumber
Mgrssn Mgrstartdate
p.k
DEPT_LOCATIONS
Dnumber Dlocation
PROJECT
WORKS_ON
Pname Pnumber Plocation Dnumber
f.k
f.k
Essn Pnumber Hours
DEPENDENT
f.k
NOT NULL
NOT NULL
Essn Dependent_name M/F Bdate