CS3402 Database Systems
Tutorials
CS3402 Tutorial 2:
1. Translate the ER diagram below to relational tables in the following steps. (a) Map strong entity type into relation
(b) Map weak entity + identifying relationship type into relation
(c) Map binary 1:1 relationship types into attributes
(d) Map binary 1:N Relationship types into attributes (e) Map binary M:N relationship type into relation (f) Map N-ary relationship type into relation
(g) Map multi-valued attribute into relation
2 Consider the following ER model with entities A and B (with attributes a and b) connected through a relationship.
2.1 Complete the table below by converting the ER model to relational schema, for all cardinality options. Write down the relations and underline their primary keys.
Hint: Map 1:1 relationship types into attributes; Map 1:N Relationship types into attributes; Map M:N relationship type into relation.
ER Model (X:Y)
Relational Schema
M:N
1:N
N:1
1:1
CS3402 Database Systems Tutorials
2.2 Suppose we want to add elements to the relations. Mark which tuples from below can be inserted into the relational schemas you created for the M:N relationship:
(a1, b1) (a1, b2) (a2, b1) (a2, b2)
2.3 How about the 1:N case? (a1, b1)
(a1, b2)
(a2, b1)
(a2, b2)
2.4 How about the 1:1 case? (a1, b1)
(a1, b2)
(a2, b1)
(a2, b2)
CS3402 Database Systems
Tutorials
CS3402 Tutorial 2: 1. Answer:
(a) Map strong entity type into relation
Include simple (or atomic) attributes of the entity
Include components of composite attributes
Identify the primary key from the attributes
Don’t include: non-simple component of composite attributes, derived
attributes, multi-valued attributes (not yet)
(b) Map weak entity + identifying relationship type into relation
Include simple (or atomic) attributes
Add the associated strong entity’s primary key as attributes (also known as
foreign key because it refers to another relation’s primary key)
Set the primary key as the combination of the foreign key and the partial
key of the weak entity
CS3402 Database Systems Tutorials
(c) Map binary 1:1 relationship types into attributes
Include the primary keys of one entity type as attributes (foreign keys) of
the other entity type (note: it is better to choose the entity in total
participation to include the other entity’s key as attribute)
Include also the simple attributes of the relationship type
(d) Map binary 1:N Relationship types into attributes
In the relation representing the N-side entity type, add the primary keys of
the 1-side entity type as attributes (foreign key)
Include also the simple attributes of the relationship type
CS3402 Database Systems Tutorials
(e) Map binary M:N relationship type into relation
Include the primary keys of the participating entity types as attributes
(foreign key)
Identify the primary key as the combination of the above foreign keys
Include the simple attributes of the relationship type
(f) Map N-ary relationship type into relation
Similar to binary M:N relationship type
(g) Map multi-valued attribute into relation
Include the given attribute
Include the primary attributes of the entity/relationship type owning the
multivalued attribute
Set the primary key to be the combination of foreign key and its original
attribute
CS3402 Database Systems Tutorials
To summarize, the ER model will be translated into the following relational tables:
2 Answer: 2.1
CS3402 Database Systems
Tutorials
2.2
√ (a1, b1) √ (a1, b2) √ (a2, b1) √ (a2, b2)
2.3 How about the 1:N case? √ (a1, b1)
√ (a1, b2)
(a2, b1) (a2, b2)
OR
(a1, b1) (a1, b2)
√ (a2, b1) √ (a2, b2)
2.4 How about the 1:1 case? √ (a1, b1)
(a1, b2)
(a2, b1)
√ (a2, b2) OR
(a1, b1)
√ (a1, b2) √ (a2, b1)
(a2, b2)