Identifying the Entity Types and Attributes from the Requirements || Lesson 11 || DBMS ||

Identifying the Entity Types and Attributes from the Requirements
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

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.

#learningmonkey #dbms #placements #gatecse #gatedbms #campusplacements

Link for playlists:

[arfatshaikh]

Wow❤ explained it so simply thanku so much sir.

[muhammadsudais]

At 4:11 Sir in department entity manager start date should be in dashed circle because it is derived attribute and should b keep track number of hrs and date so it will be in dashed circle correct me if I'm wrong
Thanks for the explanation!

[juanmarrerotorrez]

For dependents entity type would be a good practice consider the atrributes: dependent name, sex, birthdate and relationship like multivalued attributes? Because a Unique employee may have more than one dependent and for these attributes this employee will have more than one value.

[priyadharshini]

Need ms excel from basics to advanced plz

[ikqsckl]

Hi Sir! Can' t we include project in entity of department, as it is written that every department controls No, . of projects so it can be an attribute of department. Secondly don't you think dependent should be an attribute of employee. Kindly answer these queries and thanks for the lectures