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.
Some of the high-level activities for ACME Corporation are:
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 (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 example.
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.
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.