Conversion to tables

1
Conversion to tables
Database Design Concepts
2
Introduction

• The entities in our ERDs become tables in our
  design
• How do the relationships in our ERDs affect our
  table designs?
• In this lecture we will look at posting
  identifiers to give foreign keys to link the
  tables together
• Dealing with the relationships one at a time.

3
1M many end Obligatory
works on
1
M
?
Employee
Machine
?

• Every Machine has exactly one Employee, so we
  can post the primary key of Employee into the
  Machine table, thus forming a foreign key
• Emp (empNo, .)
• Machine (machineNo, empNo, ..)
• This is the most common pattern on any ERD
• The dot by the 1 is irrelevant to the tables.

4
1M Many end optional
table required
works on
1
?
M
?
Employee
Machine
We cant use POSTING so we need a relationship
table
5

• Put the Primary Keys from each end in the new
  table
• Emp (empNo, .)
• Machine (machineNo, ..)
• Works_on (machineNo, empNo, ..)
• For a 1M relationship table the many end always
  provides the Primary Key (Why? Remember the
  primary key determines each attribute)
• Again, the dot by the 1 is irrelevant to the
  tables
• We add a table required note to the diagram,
  but we do not add a new entity.

6
MM Relationships
All MM relationships should have been decomposed
during modelling. Refer to the lecture last
week.
7
11 OptionalObligatory

• Every machine has exactly one employee, so we
  can post the employee number into the Machine
  table
• (optional ends identifier into the mandatory
  end)
• Emp (empNo, .)
• Machine (machineNo, empNo, ...).

8
11 OptionalOptional
table required

• We cant use POSTING
• Therefore, the relationship must be represented
  by a new table

9

• Emp (empNo, .)
• Machine (machineNo, .., )
• Works_on (empNo, machineNo, ..)
• or
• Works_on (machineNo ,empNo, ..) Either can be
  the identifier.

10
11 ObligatoryObligatory

• Every employee works on exactly one machine, and
  every machine is worked on by exactly one
  employee
• We could post both ways!

11

• This should be collapsed into one table
• Emp (empNo, emp_name,.., machineNo,
  machine_location..)
• Basically avoid this pattern in ERDs - it usually
  means the two entities are really the same thing,
  and is rarely correct.

12
Summary of Mapping Rules

• Here is an important summary of the
  Entity-Relationship Diagram to Table types
  mapping rules

The only dots (participation condition) shown are
those which affect the structure of the tables
Your final ER diagram should not show
un-decomposed MM relationships
13
Completing the tables

• You should now have several tables from your ER
  model
• Each table will have a unique identifier or key
  field
• Some tables will have a posted or foreign key
• Finally allocate the attributes to a table- We
  will practice this in the tutorials

14
Some Examples

• Represent the relationships on the following
  slides in the correct way, i.e. by either
• collapsing into one table
• posting the identifier of one table into the
  other
• creating a table for the relationship.

15
Example 1
Game
Player
M
1
plays
Player (playerNo, name, . . .) Game (gameNo,
date, . . .)
16

• Player (playerNo, name, . . .)
• Game (gameNo, date, playerNo . .)

17
Example 2
Store
Manager
1
1
runs
Manager (staffNo, name, . . .) Store (storeNo,
location,. . .)
18

• Manager (staffNo, name, . . .)
• Store (storeNo, location,. . .)
• Run by (storeNo, staffNo )
• Note either can be the identifier

19
Example 3
Sales Rep
Vehicle
1
M
allocated to
Vehicle (vehicleNo, type, . . .) Sales
Rep (staffNo, area, . . .)
20

• Vehicle (vehicleNo, type, . . .)
• Sales Rep (staffNo, area, . . .)
• Vehicle-allocation (staffNo ,VehicleNo, ..)

21
Example 4
Projector
Lecture Theatre
1
1
fitted with
Lecture Theatre (roomNo, capacity, . .
.) Projector (equipmentNo, date_purchased, . . .)
22

• Lecture Theatre (roomNo, capacity, . . .)
• Projector (equipmentNo, date_purchased, roomNo. .
  .)

23
Summary

• Conversion to tables is the last stage of the the
  ER modelling process.
• Learn the rules of what to do based on the
  properties of the relation ship.
• The tables given will form the basis of the
  implementation.
• The primary and foreign keys will have been
  identified
• Take care to make sure all other attributes are
  only allocated to one table