From E-R Model to Relational DB Design
Date last modified: 23 April, 2014
Here is a summary of the steps needed to derive a relational database design (what M359 calls a "relational model") from a given E-R model.
1. First stab at relation headings
Make a preliminary list of relation definitions, one for each entity type, using the given attribute names and ignoring domains for now.
[If entity subtyping is used, make relation headings for the bottom-level entity types only, being sure to include all supertype attributes in each case.]
2. Domains
Make a list of domains, sufficient for all attributes mentioned in Step 1.
3. Complete the attribute definitions
Add domains to the attribute definitions written down in Step 1.
4. Primary keys
For each of the relations defined so far, specify the primary key as consisting of the attributes corresponding to those of the entity type's identifier. See here if you are still a bit shaky on the concept of keys.
[For a relation corresponding to an entity subtype, take the identifier of the top-level supertype.]
5. Relationships via "posted foreign key" method
For each relationship that is either:
(a) many-to-one with a blob at the many end, or
(b) 1:1 with a blob at either end (see the bracketed note below for 1:1 with blobs at both ends),
add attribute definitions as needed to the relation representing the many end (or blobbed "1" end) to make a foreign key referencing the relation representing the other end ‒ in some cases these attributes will be there already if they are part of the relevant entity type's identifier. The attribute definitions can be straight copies of those of the primary key of the referenced relation, though you can choose different attribute names if necessary. Write down the foreign key declaration too and if the question asks you to include comments, write them down in the form of: {mandatory participation of A in Relates relationship} where A is the blobbed entity where the foreign key is written. In the 1:1 case the attributes of this foreign key should also be specified as a key (probably an alternate key but it really doesn't matter which key you designate as primary), and if the question asks you to include comments, write them down in the form of: {relationship Relates is 1:1} on top of the alternate key declaration.
[For a relationship that is 1:1 with a blob at both ends, you might as well use a single relation to represent the two entity types and the relationship: if every tuple in the one relation must have exactly one matching tuple in the other, and vice versa, then the two relations might as well be joined together.]
6. Additional constraints for mandatory participation
For each blob not already addressed by a foreign key declaration, write down an appropriate relational constraint of the form constraint (project r1 over ... ) difference (project r2 over ... ) is empty.
If the question asks you to include comments, write them down in the form of: {mandatory participation of A in Relates relationship} where A is the blobbed entity where the constraint is written.
7. Relationships via "relation for relationship" method
For each relationship not covered under Step 5, write down the definition of a relation whose attribute definitions are copies of those of the primary keys of the two relations representing the participating entity types. For each of those two primary key copies, write down the declaration of a foreign key referencing the relation of which it is the primary key. See here if you need further information on the relation for relationship method.
8. More primary keys
For each relation defined in Step 6, specify the primary key as follows:
(a) If the relationship is many-to-many, specify all the attributes.
(b) If the relationship is many-to-one, include just the attributes taken from the relation corresponding to the "many" end.
(c) Otherwise the relationship is one-to-one and the primary key can be the same as that of either of the relations representing the participating entity types.
9. Alternate keys
Specify any alternate keys that might be needed. The most likely ones will be for relations dealt with under Step 7(c), where the attributes not participating in the primary key should form an alternate key.
10. Constraints for Additional Constraints
Finally, examine each entry in Additional Constraints. Some of these, especially those concerning "weak" entity types, will have been addressed by foreign keys in a previous step. Others might be already addressed by alternate keys. For each of the constraints not already addressed, write down a suitable relational constraint definition. See here if you need further information on defining constraints on relational databases.
For Step 10, the constraint ... is empty approach is the general-purpose one that can always be used, but you probably want to take advantage of the tuple constraint approach for certain simple constraints that can be treated that way.