Entity-Relation Mapping Algorithm

Written by @andrewlalis. Adapted from Fundamentals of Database Systems, 7th Edition.

1. Mapping of Regular Entity Types

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 the primary key for R. If the chosen key is a composite, then the set of simple attributes that form it will together form the primary key of R.

If multiple keys were identified for E during the design of the schema, then the information describing the attributes that form each additional key should be kept in order to specify additional (unique) keys of the relation R. Knowledge about keys is also kept for indexing purposes and other types of analyses.

2. Mapping of Weak Entity Types

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 mapping 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. If there is a weak entity type E2 whose owner is also a weak entity type E1, then E1 should be mapped first, to determine the primary key(s) that will be required by E2.

3. Mapping of Binary 1:1 Relationship Types

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. There are three possible approaches, the first of which is the most useful and should be followed unless special conditions exist:

4. Mapping of Binary 1:N Relationship Types

There are two possible approaches, the first of which is generally preferred as it reduces the number of tables.

5. Mapping of Binary M:N Relationship Types

The only option for M:N relationships in the traditional relational model is the relationship relation. For each binary M:N relationship type R, create a new relation S to represent R. Include foreign key attributes in S for the primary keys of both participating entities. The combination of both foreign keys forms the primary key of S. Also include any simple attributes of R as attributes of S.

6. Mapping of Multivalued Attributes

For each multivalued attribute A from an entity E, create a new relation that contains a foreign key to E, and an attribute representing a single instance of A. The primary key of the new relation is the combination of the foreign key and the single attribute.

7. Mapping of N-ary Relationship Types

For each N-ary relationship type R, where n > 2, create a new relationship relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship type (or simple components of the composite attributes) as attributes of S. The primary key of S is usually a combination of all the foreign keys that reference the participating entities (except the foreign keys to entities that participate in the relationship with a cardinality constraint of 1).

8. Options for Mapping Specialization or Generalization

There are a few different options for mapping specializations and generalizations. The four most common are given here:

9. Mapping of Union Types (Categories)

For mapping a union type, we create a surrogate key attribute which should be appended to the relation of any entity that participates in the union. A relation is made for the union type itself, and that relation uses the surrogate key as its primary key. We then also declare the surrogate key in each participating entity relation as a foreign key to the union type's primary key.