Model Transformation

Given an ER Model, the next activity in the database design process is transformation of the logical representation expressed by the ER Model into a schema. For a relational database design this essentially means transformation into relational tables.

A series of transformation rules are presented below. These are for each of the entity types and relationship types discussed previously. This material should be read in conjunction with the readings outlined for Chapter 6 below.

Note: The phrase foreign key refers to a primary key that appears as an attribute/column of a table other than its own. Also, the terms parent and child refer to the entities that participate in 1:N relationships. The parent is the entity on the one side and the child the entity on the many side.

Strong Entities

For each strong entity:

• Create a table that includes all its attributes as columns.
• Choose an identifier attribute to be the primary key.

Weak Entities

For each weak entity:

• Create a table that includes all its attributes as columns.
• Include the primary key of the table corresponding to its parent entity as a foreign key.
• Let the primary key be a combination of the foreign key and the identifier of the weak entity.
  Note: If the weak entity was created from a multi-valued attribute an identifier may not have been identified in which case the attribute itself (or a surrogate such as a sequence number) may be used in combination with the foreign key.

1:1 Relationships

For each binary 1:1 relationship:

• Identify the tables corresponding to the participating entities.
• Choose one of the tables and include the primary key of the other table as a foreign key.
• Include any attributes of the relationship as columns of the chosen table above.

1:N Relationships

For each binary 1:N relationship:

• Identify the tables corresponding to the participating entities.
• Include the primary key of the parent table as a foreign key in the child table.
• Include any attributes of the relationship as columns of the child table.

M:N Relationships

For each binary M:N relationship:

• Create a new table to represent the relationship.
• Identify the tables corresponding to the participating entities. Include the primary key of these tables as foreign keys in the new table.
• Let the primary key be a combination of the foreign keys.
• Include any attributes of the relationship as columns of the new table.

Notes:

1. We may use the above rules to address recursive relationships. The only difference is that in the case of 1:1 and 1:N recursive relationships the foreign key is added to the same table. We usually choose a name for the foreign key that indicates its use. See pages 161 - 163.
2. Subtype structures, or IS-A relationships, are a little different. For the tables corresponding to the subtypes, include the primary key of the table corresponding to the supertype. Let this key be the primary key of the subtype. Notice that each subtype instance corresponds to one and only one supertype instance and so the key of the subtype must be the same as the key of the supertype. See pages 165 - 167.
3. Foreign keys will be NOT NULL except for 1:1 and 1:N relationships, when the minimum cardinality of the referenced table is zero, in which case the foreign key must allow for nulls.

Readings: Chapter 6, pg 151-163, 165-167