Mapping Rules from ERD to a relational database schema

1
Chapter 16

• Mapping Rules from ERD to a relational database
  schema

2
ERD
3
Mapping a strong entity type

• Name a relation with the name of the entity type
  and place each single entity attribute in its
  relation schema. For composite attributes,
  include constituent simple attributes. Example

Staff(staffno, fname, lname, position, sex, DOB,
) Owner(ownerno, address, telno, )
Others attributes may be added later. Primary key
is the same key identified in the ERD.
4
Mapping the weak entity types

• Create a relation that include all single
  attributes
• Primary key still has to be identified after the
  relationship types associated to it have been
  mapped.
• Relation may not survive after relationship types
  are mapped.
• Example

Preference(preftype, maxrent, )
5
Relationship types to Tables

• M-to-M binary and higher degree relationship
  types
• Create a new relation with the same name as the
  relationship type or rename if it is necessary.
• Place the primary keys of each participating
  entity types to act as foreign keys in the
  relation schema.
• Place the attributes of the relationship type in
  the relation schema.
• Identify primary key (usually a composite key
  made from the foreign keys).
• Example

Viewing (propertyno, clientno, viewdate,
comment) Registration(branchno, clientno,
staffno, datejoined) Last relation derived from
Figure 12.8
6
1-to-M relationship types

• Entity on 1 side is designated the parent
  entity and the entity on M side is the child
  entity
• Place the primary key of the parent entity into
  the relation schema representing the child entity
  (acting as a foreign key)
• Place the attributes of the relationship type in
  the relation schema representing the child entity

Example Staff(staffno, fname, lname, position,
sex, DOB, branchno, )
7
1-to-1 relationship type

• (a) Mandatory participation on both sides
• Combine entities into one relation and choose one
  of the primary keys of the original entities
• Example States relationship type
• Client(clientno, fname, lname, telno, staffno,
  preftype, maxrent) remove Preference relation
• (b) Mandatory participation on one side
• Place primary key of entity with optional
  participation (parent entity) to act as foreign
  key in relation representing entity with
  mandatory participation (child entity)
• Place attributes of the relationship on the child
  entity

8
1-to-1 relationship type, cont.

• Example of a type (b) 1-to-1 relationship
• Uses relationship type below

Car CardID Model Mileage
Since
1..1
0..1
Uses
Has optional participation has mandatory
participation (parent entity) (child
entity) Car(CarID, model, mileage, staffID,
usedsince)
9
1-to-1 relationship type, cont.

• c) Optional participation on both sides of a 11
  relationship
• Designation of the optional participation
  (parent) and mandatory participation (child)
  entities is arbitrary unless can find out more
  about the relationship.
• Consider 11 Staff Uses Car relationship with
  optional participation on both sides. Assume
  majority of cars, but not all, are used by staff
  and only minority of staff use cars.
• Car entity, although optional, is closer to being
  mandatory than Staff entity. Therefore designate
  Staff as having optional participation (parent)
  entity and Car as having optional participation
  (child) entity.

10
Multi-valued attribute

• Create a new relation to represent the
  multi-valued attribute and place a copy of the
  primary key of the owner entity into the new
  relation (to act as a foreign key.
• Primary key of the new relation is usually the
  primary key of the owner entity plus the
  multi-valued attribute, unless the multi-valued
  attribute is unique for all the rows.
• Example Telephone(telfno, branchno)

11
Translating Generalization/Specialization to
Relations

• General approach Create a relation for the
  superclass and each subclass.
• Add primary key of superclass relation into each
  subclass relation
• Queries involving superclass records are easy,
  those involving a subclass may require a join to
  get some superclass attributes.
• Alternative Just relations for each subclass.
• Works for mandatory participation.
• Superclass atributes must be added to each
  subclass relation.

12
Mapping Aggregations to Relations

• The relation for the aggregation is represented
  by the relationship type in the aggregation

13