IS 263/ IS264: TUTORIAL TWO
ERD to Relation Mapping
Objective
This tutorial will familiarize you on the algorithm used to map an ER schema to
the corresponding relational model constructs.
Note that the algorithm should be applied step by step in the given order to
obtain the correct relational schema.
Steps of ER-to-Relational Mapping Algorithm
The mapping algorithm has the following 7 steps.
Step 1: Mapping of Regular Entity Types
Step 2: Mapping of Weak Entity Types
Step 3: Mapping of Binary 1:1 Relation Types
Step 4: Mapping of Binary 1:N Relationship Types.
Step 5: Mapping of Binary M:N Relationship Types.
Step 6: Mapping of Multivalued attributes.
Step 7: Mapping of N-ary Relationship Types.
Note that not all the steps may need to be applied to a given ERD. For example, if
the ERD does not have a weak entity then step 2 is irrelevant (not applied).
Question One:
Convert the ER Diagram given below to relation schemas.
1
Question Two:
The ER diagram given below is for a sports database. Use the
given ER diagram to determine the schema.
Question Three:
Convert the ER Diagram given below to relation schemas.
2
Question Four:
Convert the ER Diagram given below to relation schemas.
Question Five
Convert the ER Diagram given below to relation schemas. Assume
a customer and supplier may have more than one phone number
and make the appropriate correction during mapping.
3
Question Six
Suppose that you are designing a schema to record information
about reality shows on TV. Your database needs to record the
following information:
● For each reality show, its name, genre, basic_info and
participants name. Any reality show has at least two or
more participants.
● For each producer, the company name, company country. A
show is produced by exactly one producer. And one
producer produces exactly one show.
● For each television, its name, start year, head office.
A television may broadcast multiple shows. Each show is
broadcasted by exactly one television.
● For each user, his/her username, password, and age. A
user may rate multiple shows, and a show may be rated by
multiple users. Each rating has a score of 0 to 10.
Draw an ER diagram for the given scenario above and convert
the ER Diagram to Relation Schemas;