This document was produced for a course taught in the later half of 2000. It's six years old.
More up to date versions of this information, including some alternate media, are now available from this 2006 course page. That page includes
* A PDF document extending the material on this page on how to develop an entity relationship diagram
* Powerpoint slides and accompanying mp3 audio file giving a quick explanation of normalisation
* A PDF document that extends the how to develop an entity relationship diagram document and shows how to normalise the case.
Developing Entity Relationship Diagrams (ERDs)
WHY:
Entity Relationship Diagrams are a major data modelling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner.
INFORMATION:
Entity
A data entity is anything real or abstract about which we want to store data. Entity types fall into five classes: roles, events, locations, tangible things or concepts. E.g. employee, payment, campus, book. Specific examples of an entity are called instances. E.g. the employee John Jones, Mary Smith's payment, etc.
Relationship
A data relationship is a natural association that exists between one or more entities. E.g. Employees process payments. Cardinality defines the number of occurrences of one entity for a single occurrence of the related entity. E.g. an employee may process many payments but might not process any payments depending on the nature of her job.
Attribute

A data attribute is a characteristic common to all or most instances of a particular entity. Synonyms include property, data element, field. E.g. Name, address, Employee Number, pay rate are all attributes of the entity employee. An attribute or combination of attributes that uniquely identifies one and only one instance of an entity is called a primary key or identifier. E.g. Employee Number is a primary key for Employee.
AN ENTITY RELATIONSHIP DIAGRAM METHODOLOGY: (One way of doing it)
1. Identify Entities
Identify the roles, events, locations, tangible things or concepts about which the end-users want to store data.
2. Find Relationships
Find the natural associations between pairs of entities using a relationship matrix.
3. Draw Rough ERD
Put entities in rectangles and relationships on line segments connecting the entities.
4. Fill in Cardinality
Determine the number of occurrences of one entity for a single occurrence of the related entity.
5. Define Primary Keys
Identify the data attribute(s) that uniquely identify one and only one occurrence of each entity.
6. Draw Key-Based ERD
Eliminate Many-to-Many relationships and include primary and foreign keys in each entity.
7. Identify Attributes
Name the information details (fields) which are essential to the system under development.
8. Map Attributes
For each attribute, match it with exactly one entity that it describes.
9. Draw fully attributed ERD
Adjust the ERD from step 6 to account for entities or relationships discovered in step 8.
10. Check Results
Does the final Entity Relationship Diagram accurately depict the system data?
A SIMPLE EXAMPLE
A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number.
1. Identify Entities
The entities in this system are Department, Employee, Supervisor and Project. One is tempted to make Company an entity, but it is a false entity because it has only one instance in this problem. True entities must have more than one instance.
2. Find Relationships
We construct the following Entity Relationship Matrix:


Department
Employee
Supervisor
Project
Department

is assigned
run by

Employee
belongs to


works on
Supervisor
runs



Project

uses


3. Draw Rough ERD
We connect the entities whenever a relationship is shown in the entity Relationship Matrix.

4. Fill in Cardinality
From the description of the problem we see that: