Mapping ERM to relational database

1
Mapping ERM to relational database

• Mapping the ER model to the relational model
• Logical Database Design
• Reading
• e.g. Connolly/Begg DB systems (4th ed) Ch 16
  step 2.2 and/or (3rd ed) Ch 15.1 Rob et al
  Section 11.2

2

• What does this ERM model?
• Work with your neighbour and identify
• Entities (strong, weak)
• Attributes (simple, composite, derived,
  multi-valued)
• Relationships (cardinality, participation,
  attributes)

3
Mapping the conceptual model

• Step-by-step cookbook recipe
• Details how to create a logical (relational)
  model from a conceptual (ERM) model
• Main ideas
• Entity with attributes ? Relation with fields
• Relationship ? Foreign key
• Lets start with the steps we need for the books
  example

4
Steps 12 Entities

• For each entity type create a relation
• include all simple attributes
• include composite attributes
• usually use one field per component,
• or single field
• Leave out multi-valued attributes
• Strong entity Choose a primary key
• Weak entity will get its primary key later
• Now apply this to the books example

5
Mapping binary relationships

• Identify one entity as parent
• other entity as child
• as general rule, PK of parent is added to child
  as FK
• Any attributes of the relationship
• are added to child relation

6
Step 3 (1) Relationships

• Relation at 1 end is parent
• Relation at many end is child
• include parent's PK in child as foreign key.
• Any attributes of the relationship are added to
  child
• Apply this to the (1) relationships in the
  books example now
• Note for recursive (1) relationships, use same
  rule. Add primary key of the relation a second
  time as foreign key

7
Step 7 () relationships

• For each binary many-to-many relationship type
  create a new relation.
• Add PKs of both parents to the new relation (as
  FKs) and also any attributes of the relationship.
  
• PK of the new relation is usually composite
  simply combine both FKs. If this is not unique,
  include additional fields as needed
• Apply this to the (1) relationships in the
  books example now
• Note Apply same method for unary create a
  new table, with two foreign keys both linking to
  the original PKs

8
Step 9 Multi-valued attributes

• For each multi-valued attribute
• create a new relation that contains
• the attribute itself, plus
• the primary key of the parent entity as foreign
  key.
• The primary key of the new relation is usually
  made up of all its attributes.
• Sometimes, not all attributes may be needed
• Apply this to the books ERM now

9
Steps Summary (from Connolly/Begg)

• 1. Strong entities
• 2. Weak entities
• 3. Binary 1 relationships
• 4. Binary 11 relationships
• 5. Recursive 11 relationships
• 6. Super- and subclasses ? later!!
• 7. Binary relationships
• 8. Complex relationships
• 9. Multi-valued attributes
• Check all relations have PK there are at least
  (n-1) FKs if you have n relations, and all
  relations are in 3NF

10
Steps Summary (from Rob et al)

• 1. Strong entities
• 2. Weak entities
• 3. Multi-valued attributes
• 4. Binary relationships
• 1
• 11 (also discusses 11 recursive)
• 5. Ternary relationships
• 6. Super- and subclasses ? later!!

How does that compare with Connolly/Begg? Which
do you prefer?
11
Step 8 complex relationships

• For each ternary (and higher order) relationship
• Create a new relation
• made up of the primary keys from the n
  participating relations, as foreign keys
• plus any attributes of the relationship.
• PK of the new relation
• usually the combination of all FKs
• But may be able to use just a subset of the
  attributes
• Or may need to add in other attributes to
  guarantee uniqueness.

12
Step 8 Example
1..
Orders
1.
1..

• Map this ERM now!

13
Step 4 Binary (11)

• Three options, depending on participation
• Option A (mandatory participation on both sides)
• Merge both entities into a single entity
• choose either of the original PKs as the new PK
• include any attributes of the relationship
• Option B (mandatory participation on one side)
• relation with optional participation is parent
• Relation with mandatory participation is child
• add PK of parent to child as FK.
• add any attribute of the relationship to child

14
Step 4 Binary (11) ctd.

• Option C (optional participation on both sides)
• Arbitrarily choose one entity as child, the other
  as parent
• proceed as usual - add PK of parent to child as
  FK also add any attributes of the relationship
• also use this option if mandatory participation
  on both sides and want to keep separate relations
• If one entity is close to mandatory
  participation, choose that one as child.

15
Step 4 Example
How would you map this?
16
Step 5 Unary (Recursive) 11

• Follow Step 4 in principle, but same entity on
  both sides of relationship
• Mandatory participation both sides
• keep single relation
• add new attribute - copy of PK, to act as FK
• Optional on both sides
• create a new relation with just two copies of PK.
  They act as composite PK and separately as FKs to
  link back to entity
• Optional one side
• follow either of the two methods above.

17
Step 5 example
1..1
Each staff member has one supervisor and may
supervise one staff member
Supervises
Staff ID PK name department
0..1
One relation Staff(ID, supervisorID, name,
department) Or Two relations Staff(ID, name,
department)
Supervision(staffID, supervisorID)
18
Directed Reading

• Connolly/Begg Database Systems
• (4th ed) Ch 16 step 2.2 or (3rd ed) Ch 15.1
• Ignore Step 6 (super- and sub-classes) for now
• or
• Connolly/Begg Database Solutions
• Chapter 10 step 2.1
• or
• Rob et al Section 11.2
• 
  
• Note that if you read any other database textbook
  or access any websites you may see other
  descriptions of the mapping recipe. They all do
  the same thing! We use the numbering of Connolly
  and Begg DB Systems. DB solutions uses the same
  method but no numbering.
• 
  

19
What about unary (recursive) relationships?

• Textbooks usually discuss recursive 11
  relationships explicitly
• Often don't mention 1 or recursive
  relationships
• Essentially, treat all recursive relationships
  like the equivalent binary ones, except the
  entities at both ends are one and the same.

20
Summary

• Follow 9 Step procedure
• should result in a good relational model, i.e.
  at least in 3rd Normal Form
• Be careful!!!
• Remember to identify Primary Keys
• For relationships
• Identify one relation as parent, one as child
• post parents PK to child as FK

21
Summary ctd.

• In the relational model,
• entities are represented as relations
• simple attributes are represented as fields
• multi-valued attributes represented as a relation
• derived attributes are not usually stored
• Composite attributes are usually split into
  several fields
• relationships are represented indirectly through
  the use of foreign keys
• 11 relationship ? foreign key or single relation
• 1 relationship ? foreign key in child
• relationship ? relation and two foreign keys