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)