ch9

Question 1

a) Discuss the correspondences between the ER model constructs and the relational model constructs. Show how each ER model construct can be mapped to the relational model and discuss any alternative mappings.

Answer 1

|| ER MODEL || RELATIONAL MODEL ||
entity type | entity relation
1:1 or 1:N relationship type | foreign key (or relationship relation)
M:N relationship type | relationship relation and two foreign keys
n-ary relationship type | relationship relation and n foreign keys
simple attribute | attribute
composite attribute | set of simple component attributes
multivalued attributes | relation and foreign key
value set | domain
key attribute | primary (or secondary) key

Question 2

b) Discuss the options for mapping EER model constructs to relations, and the conditions under which each option could be used.

Answer 2

When mapping binary 1:1 relationship types:
option 1: foreign key approach; most useful, should be used unless special conditions exist
option 2: merged relation approach; possible when both participations are total
option 3: cross-reference AKA relationship relations; required for binary M:N relationships, drawback: having extra relation to maintain and having to do extra join

When mapping binary 1:N relationship types:
option 1: foreign key approach; preferred, reduces number of tables
option 2: relationship relation approach; can be used if few tuples participate in the relationship to avoid excessive NULL values in the foreign key

When mapping specialization or generalization:
option 8A: multiple relations - superclass and subclass; can be used for total/partial participation, disjoint/overlapping subtypes
option 8B: multiple relations - subclass relations only; only use if the specialization is disjoint, if used with overlapping you could have duplication
option 8C: single relation with one type attribute; only works for disjoint specialization, has potential to generate many NULL values if too many specific local attrs exist in the subclasses
option 8D: single relation with multiple type attributes; used for overlapping specializations but can also be used for disjoint

Question 3

what are the steps in the EER-to-Relational Mapping algorithm?

Answer 3

1. Map regular entity types
2. Map weak entity types
3. Map binary 1:1 relationship types
4. Map binary 1:N relationship types
5. Map binary M:N relationship types
6. Map multivalued attributes
7. Map n-ary relationship types
8. Map specializations or generalizations
9. Map union types (categories)