CS计算机代考程序代写 database ER CS3402 Database Systems

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)