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.
Follow the steps given below to draw an ER model for the company −
The entity set has multiple instances in a given business scenario.
As per the given constraints the entity sets are as follows:
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.
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.
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.
The complete ER diagram is as follows −