Database design using the entity-relationship model

  1. Get a feel for the problem by sketching E-R diagrams. Don't worry about getting all the details correct at this point.
  2. Prepare a preliminary list of functions and transactions which the model must support.
  3. Write down a list of entities and select the key or identifier for each.
  4. Prepare a list of attributes.
    Use attributes that cannot be divided further; for example, use Last name and First name rather than Name. Do not use attributes that can be derived using other attributes; for example, Age from Date of birth. Use single-value attributes, not multi-value attributes such as First author, Second author, etc.; if multi-value attributes occur, try promoting them to entities.
  5. Draw an E-R diagram showing the known relationships between entity classes. Include maximum cardinality and membership class details.
  6. Make a preliminary check that your E-R diagram will support the transactions, and amend the diagram if necessary. Assign attributes to entities and relationships.
  7. Using the rules for converting entity-relationship diagrams to a set of relations, develop skeleton tables corresponding to your E-R diagram.
  8. Check off all attributes in your attribute list that are used in the skeleton table identifiers.
  9. Add the remaining attributes to appropriate tables and check them off in the list.
  10. If there are any attributes left over which cannot be assigned, define further entity classes or relationships necessary to accommodate them.
  11. Decide whether there are any other attributes or transactions which must be included now to accommodate future developments.
  12. Check that your choices of entity classes, relationships, and attributes still seem appropriate. Check that all transactions can be supported at the attribute level. If necessary repeat the procedure from the beginning.
  13. Delete any superfluous entity classes.


Last updated July 5, 2001.
This page maintained by Prof. Tim Craven
E-mail (text/plain only):
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7