Mapping from ERD to Relational database schema

1
Lecture 3

• Mapping from ERD to Relational database schema

2
ER model used for mapping
3
1. Mapping strong entity types

• Create 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
2. Mapping weak entity types

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

Preference(preftype, maxrent, )
5
3. Mapping binary 1-to-1 relationship types

• (a) Mandatory (total) 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
  (partial) 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

6
1-to-1 relationship type, cont.

• Example of a type (b) 1-to-1 relationship
• Suppose the States relationship type had partial
  participation on the Client side
• That is, not every client specifies preferences
• A copy of clientNo would be placed in the
  Preference (child) relation, giving
• Preference(clientNo, prefType, maxRent)
• Primary key clientNo
• Foreign key clientNo references Client(clientNo)

7
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 Client States Preference
  relationship with optional participation on both
  sides. Assume a majority of preferences, but not
  all, are used by clients and few clients state
  preferences.
• Preferences entity, although optional, is closer
  to being mandatory than Client entity. Therefore
  designate Client as the parent entity and
  Preferences as the child entity.

8
11 recursive relationships

• Follow the rules for participation as described
  above for a 11 relationship
• Type (a) represent the recursive relationship as
  a single relation with two copies of the primary
  key.
• Type(b) option to create a single relation with
  two copies of the primary key, or to create a new
  relation to represent the relationship (with only
  two attributes).
• Type (c) create a new relation as described
  above.

9
4. Mapping binary 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 Map Registers relationship
type Client(clientNo, fName, lName, telNo,
staffNo) Primary key clientNo Foreign key staffNo
references Staff(staffNo)
10
5. Mapping binary relationship types

1. Create a new relation with the same name as the
   relationship type or rename if it is necessary.
2. Place the primary keys of each participating
   entity types to act as foreign keys in the
   relation schema.
3. Place the simple attributes of the relationship
   type in the relation schema.
4. Identify primary key (usually a composite key
   made from the foreign keys).

Example Map Views and rename it to Viewing
Viewing (clientNo, propertyNo, viewDate,
comment) Primary key clientNo,
propertyNo Foreign key clientNo references
Client(clientNo) Foreign key propertyNo
reference PropertyForRent(propertyNo)
11
6. Mapping Multi-valued attributes

• For each multivalued attribute create a new
  relation to represent this 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)

12
7. Mapping complex relationship types

1. Create a relation to represent the relationship
   and include any attributes that are part of the
   relationship.
2. Post a copy of the primary key attribute(s) of
   the entities that participate in the complex
   relationship into the new relation, to act as
   foreign keys.
3. Identify primary key, which it is usually the
   combination of the foreign keys.

13
Example for Step 7.

• Given the EERD in slide 4 of Chapter 4 for a Real
  Estate organization, the ternary relationship
  Registers is mapped into
• Registers(clientNo, branchNo, staffNo,
  dateJoined)
• Registers could be renamed to Registration.
• Primary key clientNo
• Foreign keys clientNo, branchNo, staffNo

14
8. Mapping Generalization/Specialization to
Relations

• General approach Create a relation for the
  superclass and each subclass.
• Add primary key of superclass- into each subclass
  relation
• Queries involving superclass records are easy,
  harder for accessing subclass attributes (queries
  require joins).
• Works for any specialization
• Alternative 1 Just relations for each subclass.
• Superclass atributes are added to subclass
  relations.
• - Works for total participation
• Alternative 2 Single relation with one type
  attribute.
• Use a type attribute that indicate the type of a
  subclass.
• Works for disjoint subclasses

15
Examples for Step 8 (slide 12, Ch. 4).

• Relations for Staff and its subclasses.
• General approach (adding attributes to slide)
• Staff(staffNo, fname, lname, position)
• Supervisor(staffNo, group)
• Manager(staffNo, bonus, mgrStartDate)
• Alternative 1
• Supervisor(staffNo, fname, lname, position,
  group)
• Manager(staffNo, fname, lname, position, bonus,
  mgrStartDate)
• Alternative 2
• Staff(staffNo, fname, lname, position, jobType,
  group, bonus, mgrStartDate)

16
Step 9. Mapping shared subclasses.

• Use the general approach described before.
• Add a surrogate key to the relations of the
  superclasses and subclass.
• The surrogate key links the subclass to all the
  superclasses is related to.
• For a UNION type
• Identical mapping of a shared subclass.
• The surrogate key links one of the superclasses
  to the UNION shared subclass.

17
Example- mapping a Union type

• Given the diagram
• Mapped entities to relations
• Person(SSN, name, ownerID)
• Company(cname, address, ownerID)
• Owner(ownerID)

PERSON SSN name
COMPANY cname address
OWNER
18
10. Mapping Aggregations to Relations

• The relation for the aggregation is represented
  by the relationship type in the aggregation
• That is, the relation created after the
  relationship type has been mapped represents the
  agregation.