Entity and relationship example

Step 1: Identify entities and relationships

ASA SQL User's Guide
Designing Your Database
The design process

Entity and relationship example

Example

ACME Corporation is a small company with offices in five locations. Currently, 75 employees work for ACME. The company is preparing for rapid growth and has identified nine departments, each with its own department head.

To help in its search for new employees, the personnel department has identified 68 skills that it believes the company will need in its future employee base. When an employee is hired, the employee's level of expertise for each skill is identified.

Define high-level activities

Some of the high-level activities for ACME Corporation are:

Hire employees.
Terminate employees.
Maintain personal employee information.
Maintain information on skills required for the company.
Maintain information on which employees have which skills.
Maintain information on departments.
Maintain information on offices.

Identify the entities and relationships

Identify the entities (subjects) and the relationships (roles) that connect them. Create a diagram based on the description and high-level activities.

Use boxes to show entities and lines to show relationships. Use the two roles to label each relationship. You should also identify those relationships that are one-to-many, one-to-one, and many-to-many using the appropriate annotation.

Following is a rough entity-relationship diagram. It will be refined throughout the chapter.

The entity relationships are: a skill is acquired by an employee, and an employee is capable of having skills. An employee manages departments, and a department is headed by an employee. An employee is a member of a department, and a department contains employees. An employee works out of an office, and an office contains employees. An employee manages employees, and employees report to an employee.

Break down the high-level activities

The following lower-level activities below are based on the high-level activities listed above:

Add or delete an employee.
Add or delete an office.
List employees for a department.
Add a skill to the skill list.
Identify the skills of an employee.
Identify an employee's skill level for each skill.
Identify all employees that have the same skill level for a particular skill.
Change an employee's skill level.

These lower-level activities can be used to identify if any new tables or relationships are needed.

Identify business rules

Business rules often identify one-to-many, one-to-one, and many-to-many relationships.

The kind of business rules that may be relevant include the following:

There are now five offices; expansion plans allow for a maximum of ten.
Employees can change department or office.
Each department has one department head.
Each office has a maximum of three telephone numbers.
Each telephone number has one or more extensions.
When an employee is hired, the level of expertise in each of several skills is identified.
Each employee can have from three to twenty skills.
An employee may or may not be assigned to an office.