• ER-to-Relational Mapping Algorithm

• STEP 1: For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E. Include only the simple component attributes of a composite attribute. Choose one of the key attributes of E as primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R.

Example: From Fig. 3.2 to Fig. 7.5

NOTE: The foreign key and relationship attributes, if any, are not included yet at this step.

• STEP 2: For each weak entity type W in the ER schema with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes) of W as attributes of R. In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s); this takes care of the identifying relationship type of W. The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any. 

Example: The DEPENDENT relation

NOTE: It is common to choose the propagate (CASCADE) option for the referential triggered action (see Section 8.1) on the foreign key in the relation corresponding to the weak entity type.

• STEP 3: For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R. Choose one of the relations—S, say—and include as foreign key in S the primary key of T.  Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S.  

NOTE: It is better to choose an entity type with total participation in R in the role of S. (WHY?)

Note:When both participations are total, an alternative mapping of a 1:1 relationship type is possible by merging the two entity types and the relationship into a single relation.   (WHY?)

Example: The MANAGE relationship

• STEP 4: For each regular binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R.  (WHY?)

Examples: WORKS_FOR, CONTROLS, and SUPERVISION

• STEP 5: For each binary M:N relationship type R, create a new relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S.
  
  Note: We cannot represent an M:N relationship type by a single foreign key attribute in one of the participating relations—as we did for 1:1 or 1:N relationship types .  ( Why not?)
  
  Example: WORKS_ON

NOTE: The propagate (CASCADE) option for the referential triggered action (see Section 8.1) should be specified on the foreign keys in the relation corresponding to the relationship R, since each relationship instance has an existence dependency on each of the entities it relates. This can be used for both ON UPDATE and ON DELETE.

• STEP 6: For each multivalued attribute A , create a new relation R. This relation R will include an attribute corresponding to A, plus the primary key attribute K—as a foreign key in R—of the relation that represents the entity type or relationship type that has A as an attribute. The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.

Example: a new relation DEPT_LOCATIONS

NOTE: The propagate (CASCADE) option for the referential triggered action (see Section 8.1) should be specified on the foreign key in the relation corresponding to the multivalued attribute for both ON UPDATE and ON DELETE.

• STEP 7: For each n-ary relationship type R, where n > 2, create a new relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. 

• Summary of the Mapping
  

Ø Go to the Index

• STEP 8: Convert each specialization with m subclasses {S1, S2, . . ., Sm} and (generalized) superclass C, where the attributes of C are {k, a1, . . ., an} and k is the (primary) key, into relation schemas using one of the four following options:
  
  Option 8A: Create a relation L for C with attributes Attrs(L) = {k, a1, . . ., an} and PK(L) = k. Create a relation Li for each subclass Si, 1 1 i 1 m, with the attributes Attrs(Li) = {k}D {attributes of Si} and PK(Li) = k.

Example: Compare Fig. 9.2(a) with the EER in Fig. 4.4