ERD Example

“The company employee administrative database stores information about the employees, the department and the projects of a company. The following data have been identified in the requirements collection and analysis phase and they are to be represented in the enterprise. The company is organized into departments. Each department has a unique name, a unique number and a particular employee who manages the department. The database keeps track of the start date when an employee began managing the department. A department controls number of projects. Each project has a unique name, unique number and a single location. The database also stores the number of work hours budgeted for each project. The database stores each employees name, pin number, address, salary, sex, birth date, date hired and date he/she terminates employment. An employee is assigned to one department but he/she may work on several projects, which are not necessarily controlled by the same department. The database also keeps tracks of the number of hours an employee already worked on a project and the direct supervisor of each employee if he/she has one. (Note that an employee need not have an assigned supervisor).”

First of all, we have to identify the entities of the scenario. The entities would be…

• Employee
• Department
• Project

Note

Supervisor is not considered as an entity, because a supervisor is an employee himself and no need to store data separately.

Then the attributes for each entity should be traced.

Note

It is said that a department has a manager. But it is NOT an attribute of a Department, as manager should already be an instance of Employee.

( We DO NOT use an instance of another entity as an attribute)

Number of hours an employee worked on a project is neither an attribute of Project nor Employee. it is a Relationship attribute and should only be mentioned in the ERD.

Start date of managing a department is not an attribute of Employee or Departments, but it is a Relationship attribute.

The ERD would be as following…

Assumptions

• A project can’t exist without having employees in it.
• A supervisor MUST have at least one employee under him.
• An employee MUST have a department that he is assigned to.
• A department can not exist without having employees assigned to it.

NOTE

When you are drawing an ERD, if the description doesn’t provide enough information to complete the diagram, you have the right to assume those things.