Construct an ER diagram for a company in DBMS?

DBMSDatabaseBig Data Analytics

Problem

Draw of ER model for company considering the following constraints −

  • In a company, an employee works on many projects which are controlled by one department.

  • One employee supervises many employees.

  • An employee has one or more dependents.

  • One employee manages one department.

Solution

Follow the steps given below to draw an ER model for the company −

Step 1 − Identify the entity sets

The entity set has multiple instances in a given business scenario.

As per the given constraints the entity sets are as follows:

  • Employee

  • Department

  • Project

  • Dependent

Step 2 − Identify the attributes for the given entities

  • Employee − The relevant attributes are name, ssn, sex, address, salary.

  • Department − The relevant attributes are Name, number of employees, location.

  • Project − The relevant attributes are number, name, location.

  • Dependent − The relevant attributes are name, sex, birth date, relationship.

Step 3 − Identify the Key attributes

  • SSN is the key attribute for Employee.

  • Number is the key attribute for the Department.

  • Number is the key attribute for Project.

  • Name is the key attribute for a dependent entity.

Step 4 − Identify the relationship between entity sets

  • Multiple employees work for a single department and one department has multiple employees. Hence, the relationship between employee and department is many to one.

  • Single employee manages the entire department and one department is handled by one manager.

  • Each department is controlled by the number of projects and the number of projects handled by a single department. Hence, the relationship between department and project is one-to-many.

  • One employee dependent is of multiple dependent and multiple dependents are of single employee. Hence, the relationship between employee and dependent is one- to-many.

Dependent is a weak entity which is denoted by a double rectangle, the entity which does not contain a primary key is known as weak entity.

Step 5 − Complete ER diagram

The complete ER diagram is as follows −

raja
Published on 03-Jul-2021 15:12:26
Advertisements