Relational Database Design by ER and EER-to-Relational Mapping

Question 1

STEP 1: Mapping of Strong Entity Types

Answer 1

For each strong (regular) entity type E:

1. Create a relation R that includes all the atomic attributes. This can also
   be called an entity relation.
   o If there are composite attributes, include only their corresponding
   atomic attributes.
2. Choose one of the key/s of E as the primary key for R. It will remain underlined and will be listed first in the relation.
   o If the chosen key is composite, then the set of its corresponding atomic attributes will form the primary key.
   o If multiple keys were identified for E in the ER model, the
   unselected key/s will be documented as unique key/s but they will NOT be underlined in the relation anymore.

Question 2

Step 2: Mapping of Weak Entity Types

Answer 2

For each weak entity type W with owner entity type E:

1. Create a relation R that includes all the atomic attributes (or corresponding atomic attributes of composite attributes) of W.
2. Include the primary key of E. It and the partial key of W (if any) will form the primary key of R which is underlined and listed first in the relation.

o Note that this primary key of E that was included in R is also a
foreign key and will be documented as such.

Ex: DEPENDENT(** Ssn, Dependent name**, Sex, Birth date, Relationship )

Question 3

3 Approaches in Mapping of Binary 1:1 Relationship Types

Answer 3

1. Foreign key approach
   <- Preferred
2. Merged relation approach
3. Cross-reference approach

Question 4

Step 3 (for 1:1): Foreign Key Approach

Answer 4

With R, S and T identified:

1. Choose one of the relations (preferably one with total participation), say S,
   and include as foreign key in S the primary key of T.
2. Include all the atomic attributes (or corresponding atomic attributes of
   composite attributes) of the 1:1 relationship type R as attributes of S.

Question 5

Step 3 (for 1:1): Merged Relation Approach

Answer 5

This is only possible when both participations are total. With R, S and T identified:

1. Merge S and T into a single relation U.
2. Include all the atomic attributes (or corresponding atomic attributes of
   composite attributes) of the 1:1 relationship type R as attributes of U.
3. Pick one of the primary keys from the original relations as the primary key of U. The other key will be a unique key and will be documented as such.

NOTE: This approach cannot be done if there are additional relationships between the original relations which prevent the merge into a single relation.

Question 6

Step 3 (for 1:1): Cross-reference Approach

Answer 6

This is also called relationship relation approach and more appropriate when both participations are partial. With R, S and T identified:

1. Create a 3rd relation U containing both primary keys from S and T as foreign keys. This will be sort of a lookup table.
2. Include all the atomic attributes (or corresponding atomic attributes of composite attributes) of the 1:1 relationship type R as attributes of U.
3. Pick one of the primary keys from the original relations as the primary key of U. The other key will be a unique key and will be documented as such.

NOTE: The obvious disadvantage of this approach is the extra relation which requires extra join operation/s.

Question 7

Step 4: Mapping of Binary 1:N Relationship Types

Answer 7

For each binary 1:N relationship type R:

1. Identify the relation S that represents the participating entity type on the
   N − side of the relationship type.
2. Include as foreign key in S the primary key of T that represents the other participating entity type (on the 1 − side).
3. Include all the atomic attributes (or corresponding atomic attributes of
   composite attributes) of the 1:N relationship type R as attributes of S.

Question 8

Step 5: Mapping of Binary M:N Relationship Types

Answer 8

For each binary M:N relationship type R:

1. Create a new relation S to represent R.
2. Include as foreign keys in S the primary keys of the relations that represent the participating entity types.
3. Include all the atomic attributes (or corresponding atomic attributes of
   composite attributes) of the M:N relationship type R as attributes of S.
4. All those added in #2 and #3 above will form the primary key of S and will need to be underlined.

Question 9

Step 6: Mapping of Multivalued Attributes

Answer 9

For each multivalued attribute A:

1. Create a new relation R.
2. Include as foreign key in R the primary key of the relation that
   represents the entity type or relationship type that has A as attribute.
3. Include an atomic attribute in R corresponding to single value of A. If A is composite, include its corresponding atomic attributes instead.
4. All those added in #2 and #3 above will form the primary key of R and will need to be underlined.

ex: DEPT_LOCATION( Dnumber, Dlocation )

Question 10

Step 7: Mapping of N-ary Relationship Types

Answer 10

For each n-ary relationship type R (where n > 2):

1. Create a new relationship relation S to represent R.
2. Include as foreign keys in S the primary keys of the relations that represent the participating entity types.
3. Include any atomic attributes (or corresponding atomic attributes of composite attributes) of R as attributes of S.
4. All those added in #2 and #3 above will form the primary key of S and will need to be underlined.

Ex.
SUPPLIER( Sname )
PART( Part no )
PROJECT( Proj name )
SUPPLY( Sname, Part no, Proj name, Quantity )

Question 11

Mapping of Specialization / Generalization, 4 common options to convert a specialization (generalization)
into relation schemas:

Answer 11

Option 8A: Multiple relations – superclass and subclasses
Option 8B: Multiple relations – subclass relations only
Option 8C: Single relation with one type attribute
Option 8D: Single relation with multiple type attributes

Question 12

Option 8A: Multiple Relations – Superclass and Subclasses

Answer 12

This option works for ANY specialization (Disjoint/Overlapping Disjointedness,
Total/Partial Specialization)

1. Create a new relation for the superclass (with all its attributes).
2. For each of subclass:
   a. Create a new relation for the subclass with the primary key of the superclass as its primary key (also a foreign key).
   b. Include the local attributes of the subclass in the new relation.

Ex.
EMPLOYEE( Ssn, Fname, Minit, Lname, Birth date, Address, Job type )
SECRETARY( Ssn, Typing speed )
TECHNICIAN( Ssn, Tgrade )
ENGINEER( Ssn, Eng type )

Question 13

Option 8B:
Multiple Relations – Subclass Relations Only

Answer 13

This option works only for Total specializations and recommended for Disjoint
Disjointedness because those with Overlapping Disjointedness may have same entity duplicated in several relations.

For each subclass:
1. Create a new relation for the subclass.
2. Include all attributes of the superclass in the new relation; The primary key of the new relation is the same as that of the superclass.
3. Include the local attributes of the subclass in the new relation.

Question 14

Option 8C:
Single Relation with One Type Attribute

Answer 14

This option works only for Disjoint Disjointedness.

1. Create a new relation for the superclass (with all its attributes).
2. Include a type (or discriminating) attribute in the new relation; Its value
   will indicate the subclass the tuple belongs to (if any).
3. For each of subclass, include its local attribute/s in the new relation.

NOTE: The drawback to this is the potential for generating many NULL values
if many local attributes exist in the subclasses.

ex. EMPLOYEE( Ssn, Fname, Minit, Lname, Birth date, Address, Job type, Typing speed, Tgrade, Eng type )

Question 15

Option 8D: Single Relation with Multiple Type Attribute

Answer 15

This option works for ANY specialization but more appropriate for those with
Overlapping Disjointedness.

1. Create a new relation for the superclass (with all its attributes).
2. For each subclass:
   a. Include a Boolean type attribute in the new relation indicating whether
   or not the tuple belongs to the subclass
   b. Include the local attribute/s of the subclass in the new relation.

NOTE: Same drawback as the previous option.

Ex. PART( Part no, Description, Is manufactured, Drawing no, Batch no, Manufacture date, Is purchased, Supplier name, List price )

Question 16

An option in Mapping of Specialization / Generalization that works for any specialization

Answer 16

Option A Superclass and Subclasses

Question 17

An option in Mapping of Specialization / Generalization that only works for Total Specializations and recommended for Disjoin Disjointedness

Answer 17

Option B Subclass Relations Only

Question 18

This option works only for Disjoint Disjointedness.

Answer 18

Option 8C:
Single Relation with One Type Attribute

Question 19

This option works for ANY specialization but more appropriate for those with
Overlapping Disjointedness.

Answer 19

Option 8D:
Single Relation with Multiple Type Attribute

Question 20

Step 9: Mapping of Union Types (Categories)

Answer 20

1. Create a new relation U for the Union Type with all its attributes.
2. Include a surrogate key in U and make it the primary key of the relation.
3. Include a type attribute to indicate the particular entity type to which the
   tuple belongs to.
4. For each superclass:
   a. Create a new relation Si for the superclass (with its attributes).

b. Include as foreign key in Si
the surrogate key to specify the
correspondence between it and the original key of Si

Ex:

PERSON( Ssn, Driver license no, Name, Address, Owner id )
BANK( Bname, Baddress, Owner id )
COMPANY( Cname, Caddress, Owner id )
OWNER( Owner id, Owner type)

Question 21

is another term if the keys happen to be the row ids or sequential numbers.

Answer 21

Surrogate Keys