From Relational DB Design to E-R Model
Date last modified: 23 April, 2014
Here is a summary of the steps needed to retroactively derive an E-R model from a given relational database design (what M359 calls a "relational model") The exercise is sometimes useful (a) if no E-R model exists and one is desired, (b) to verify a design that has been derived from an E-R model (do we get the same model?).
Warning: This first draft might be incomplete. Please let me know if you think I've missed anything. You might like to try these steps out on Exercises 2.28, 2.30 and 2.31 in Block 2.
1. First stab at entity types
For each of the given relation definitions, draw an entity type box, using the relation name as entity type name.
2. Write down the entity type definitions
For each entity type box drawn in Step 1, write an entry in the Entity Types section, listing the attributes of that entity type. For now, just take the attribute names of the corresponding relation attributes.
3. Identifiers
For each entry in Entity Types, underline the attributes corresponding to the primary key of the relation from which that entry was derived.
4. Remove all "posted foreign keys"
Very important! For each attribute that (a) is not a member of the primary key of its relation, and (b) is a member of some foreign key specified for its relation, delete the corresponding attribute from the corresponding entry in Entity Types. See here for the reason for doing this.
5. First stab at relationship lines
For each foreign key specified in a relation definition, draw a relationship line between the two entity types corresponding to the referencing relation and the referenced relation. Put a blob at the end corresponding to the referencing relation and put a tentative circle at the other end. Unless the foreign key is also the whole of a key (primary or alternate) for the referencing relation, put a crow's foot at the end corresponding to the referencing relation.
6. Removal of surplus entity types
This step is highly recommended though not absolutely essential.
For each entity type e3 that participates in exactly two relationships, with entity types e1 and e2, consider whether e3 and the two relationship lines can be safely deleted and replaced by a single relationship line between e1 and e2. This is the case when every attribute of e3 is derived from one of the two foreign keys that gave rise to those two relationships. Annotate the new relationship line as follows:
(a) A crow's foot on the e3 end of the connection with e1 becomes a crow's foot on e2.
(b) A crow's foot on the e3 end of the connection with e2 becomes a crow's foot on e1.
(c) The participation conditions for e1 and e2 are as for the original relationships with e3.
7. Finalising the participation conditions
Some of those "tentative circles" might have to become blobs as a consequence of constraints other than foreign key constraints. In particular look for constraints having the following pattern:
constraint ( project r1 over a1, ..., an )
difference
( project r2 over a1, ..., an ) is emptyIn such a case, the circle at the end corresponding to the relation r1 becomes a blob.
8. Relationship names
Write a unique relationship name on each relationship line. Watch out for relationship names that the question is already telling you, like the ones that are being declared using the relation for relationship method. Any relation that’s not representing one of the entity types must be a relationship.
If the question has comments above the foreign keys in the form of {mandatory participation of B in Relates relationship}, this too is telling you that that relationship name is “Relates”.
9. Additional Constraints
Translate into English each constraint that has not been dealt with under any of the steps above. This will include alternate keys not dealt with in Step 5 (i.e., alternate keys that are not also foreign keys).