Steps for Converting an E-R Diagram into a Relational Database Schema
Database I

These steps are adapted from the sections on Relational Database Design Using ER-to-Relational Mapping from Chapter 9 of the text.

STEP 1: For each non-weak entity, create a relation (or table) that includes all of the simple attributes of that entity. Do not include multivalued attributes or derived attributes at this time. If you have a composite attribute, inc lude only the component attributes. (For example, if you had a composite attribute ADDRESS made up of the component attributes STREET, CITY, STATE, and ZIP, you would include the 4 components and not the composite as fields.) Choose one of the candidate keys to be the primary key of the table. If the candidate key you choose to be the primary key is a composite attribute, then all of its component attributes together will become the primary key.

EXAMPLE: From the Company E-R diagram on p. 46 of the text, you would get the following table definitions by following step 1:

EMPLOYEE

DEPARTMENT

PROJECT

STEP 2: For each weak entity, create a relation that includes all simple attributes (or simple components of composite attributes) of the weak entity. In addition, include as a foreign key attribute the primary key of the owning entity. The primary key of this relation will be the combination of the primary key of the owning entity and the partial key of the weak entity.

EXAMPLE: The relation for the weak entity DEPENDENT would look like this after step 2:

DEPENDENT

STEP 3: For each binary 1:1 relationship, identify the two entities that participate in that relationship. Choose one of the entities -- preferably the one with total participation in the relationship -- and think of it as E1. The other entity is E2. Take the primary key from E2 and include it as a foreign key in E1. If the relationship has simple attributes, include those in the relation for E1.

EXAMPLE: Manages is a 1:1 relationship between EMPLOYEE and DEPARTMENT. We choose DEPARTMENT as E1, because it participates totally in the relationship. So after step 3, the EMPLOYEE and DEPARTMENT relations would look like this:

DEPARTMENT (E1)

EMPLOYEE (E2) -- NOTE: The EMPLOYEE relation is unchanged from step 1

STEP 4: For each non-weak binary 1:N relationship, identify the entity E1 that is at the N-side (the "many" side) of the relationship. The other entity in the relationship is E2. Include as a foreign key in E1 the primary key of E2. Include any simple attributes (or simple components of composite attributes) of the relationship as attributes of E1.

EXAMPLE: We have three 1:N relationships: Works_for, Controls, and Supervision. 

Works_for: EMPLOYEE is on the N-side of the relationship, so after doing step 4 for Works_for, it will look like the following:

EMPLOYEE

Controls: PROJECT is on the N-side of the relationship, so after doing step 4 for Controls, it will look like the following:

PROJECT

Supervision: EMPLOYEE is on the N-side of the relationship in the supervisee role, so after doing step 4 for Supervision, it will look like the following:

EMPLOYEE

Note that in this case the Foreign Key Super_SSN has actually come from the SSN in the EMPLOYEE relation, since the EMPLOYEE entity is acting in two different roles in the Supervision relationship.

Step 5: For each binary M:N relationship, create a new relation to represent the relationship. Include in this relation as foreign keys the primary keys of each of the entities that participates in the relationship. The combination of these foreign keys will make up the primary key for the relation. Also include any simple attributes (or simple components of composite attributes) of the relationship.

EXAMPLE: We have one M:N relationship, Works_On. After step 5, we will have a relation for WORKS_ON that looks like this:

WORKS_ON

Step 6: For each multivalued attribute, create a new relation that includes that attribute, plus the primary key of the entity to whom that attribute belongs as a foreign key. The primary key of this new relation will be the combination of the foreign key and the attribute itself. If the multivalued attribute is also composite, we include only its simple components.

EXAMPLE: We have only one multivalued attribute, the Locations attribute of DEPARTMENT. We create a relation called DEPT_LOCATIONS that will look like this:

DEPT_LOCATIONS

(At this point we have completed the conversion of the E-R diagram on p. 46 to relational tables -- see fig. 7.5 on p. 204 to see the complete schema.)

Step 7: For each relationship with 3 or more participating entities, create a new relation to represent the relationship. Include the primary keys of each of the participating entities in the new relation as foreign keys. Also include any simple attributes (or simple components of composite attributes) of the relationship. The primary key will usually be a combination of all of the foreign keys that represent the entities that participate in the relationship. However, if any of the participating entities are on the 1-side of the relationship, then the primary key of the relation should not include the foreign key from that entity.

EXAMPLE: Remember back to our Supplier - Part - Project ternary relationship. (See the E-R diagram in fig. 4.13(a), p. 97) After following the above steps, the relations would look like the following:

SUPPLIER

PART

PROJECT

SUPPLY

Step 8: If you have a superclass/subclass structure, there are four options for how to translate it into relations. Note that each of these options is mutually exclusive.

Option 8a: Can be used with any combination of disjoint vs. overlapping, total vs. partial. Create a new relation for the superclass entity and include the attributes of the superclass entity. The primary key will be the primary key of the superclass entity. Create a new relation for each of the subclass entitites, including the attributes of the particular subclass and the primary key of the superclass entity as a foreign key. The primary key of each subclass relation will be the foreign key from the superclass relation.

Option 8b: Can be used only with disjoint subclasses with total participation. Create a new relation for each subclass entity that includes all of the attributes of the particular subclass and all of the attributes of the superclass. The primary key will be the primary key of the superclass entity.

Option 8c: Can only be used for disjoint subclasses. Not recommended if there are many attributes defined at the subclass level. Create one new relation with all of the attributes from the superclass entity and all of the attributes from each of the subclass entities. Also include a "type" attribute that will indicate the subclass to which each instance belongs.

Option 8d: Can only be used for overlapping subclasses. Not recommended if there are many attributes defined at the subclass level. Create one new relation with all of the attributes from the superclass entity and all of the attributes from each of the subclass entities. For each subclass, there must be a True/False (Boolean) attribute for each whose value will depend on whether or not a particular instance belongs to that subclass.

See the examples in fig. 9.2 (p. 296).

If, at the end of these steps, you find you have made a table that has only one attribute, discard that table but be sure that the attribute exists in one of your other tables. This is an issue primarily in situations where you are converting superclasses/subclasses to relations using Step 8.

.
© 2000 Sheila O. Denn, Bradley Hemminger