Identifying the Entity Types and Attributes from the Requirements

For Complete YouTube Video: Click Here

In this class, we will understand Identifying the Entity Types and Attributes from the Requirements.

We have discussed the concepts of Entity, Attributes, and Entity Types.

The above are essential for us to understand the concepts that we discuss in this class.

Identifying the Entity Types and Attributes from the Requirements is an important step of the database design.

Identifying the Entity Types and Attributes from the Requirements

Below are the requirements gathered from the company.

Requirements

“The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.

A department controls a number of projects, each of which has a unique name, a unique number, and a single location.

The database will store each employee’s name, Social Security number, address, salary, sex (gender), and birth date. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each employee (who is another employee).

The database will keep track of the dependents of each employee for insurance purposes, including each dependent’s first name, sex, birth date, and relationship to the employee.”

We take each line in the requirements and convert them into ER-Diagram.

Line 1: 

“The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department.”

The above requirements indicate that the company is organized into departments.

There are several departments, and each department is an entity.

The collection of all those entities is called an entity type.

So the department is an entity type.

The requirements are also specifying the attributes.

The attributes are name, number, manager of the department.

The requirements also state that the name and number two key attributes.

Line 2: 

“We keep track of the start date when that employee began managing the department. A department may have several locations.”

Apart from the above attributes, we also keep track of the start date of the manager managing the department.

Each department has several locations that mean the departments are in multiple places. 

The locations attribute is a multi-valued attribute.

The ER-Diagram for the above requirements is as shown below.

Line 3: 

“A department controls a number of projects, each of which has a unique name, a unique number, and a single location.”

From the above requirements, we have identified that the company will undertake projects.

Projects are the entities, and the collection of all the projects is a projects entity type.

The attributes for the project’s entity type are name, number, locations, and controlling department.

The ER-Diagram for the above requirements is as shown below.

Line 4: 

“The database will store each employee’s name, Social Security number, address, salary, sex (gender), and birth date.”

We also store the details of the employee. So the employee is an entity type.

The attributes of an employee are name, SSN, address, salary, sex, and birth date.

Line 5: 

“An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each employee (who is another employee).”

The other attributes of the employee are the department in which he works.

Similarly, works_on is a multi-valued composite attribute representing the projects in which an employee works and the number of hours he works on each project.

The ER-Diagram is as shown below.

Line 6: 

“The database will keep track of the dependents of each employee for insurance purposes, including each dependent’s first name, sex, birth date, and relationship to the employee.”

The company also wants to store the details of the dependents of the employee to provide insurance.

So dependents is an entity type.

The attributes of the dependents are first name, sex, birth date, and relationship.

The ER-Diagram is as shown below.

Identifying the Entity Types and Attributes from the Requirements has been done.