Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Construct an ER diagram for a company in DBMS?
An Entity-Relationship (ER) diagram is a visual representation of the data model for a database. It shows entities, their attributes, and the relationships between them. In this article, we construct an ER diagram for a company database step by step.
Problem
Draw an ER model for a 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
An entity set represents a collection of similar objects in the business scenario. As per the given constraints, the entity sets are −
Employee
Department
Project
Dependent (weak entity)
Step 2 − Identify the Attributes
| Entity | Attributes | Key Attribute |
|---|---|---|
| Employee | Name, SSN, Sex, Address, Salary | SSN |
| Department | Name, Number, Location | Number |
| Project | Name, Number, Location | Number |
| Dependent | Name, Sex, Birth Date, Relationship | Name (partial key) |
Dependent is a weak entity because it cannot be uniquely identified by its own attributes alone ? it depends on the Employee entity for identification. In ER diagrams, weak entities are denoted by a double rectangle.
Step 3 − Identify the Relationships
The relationships between entity sets along with their cardinality are −
Works For − Multiple employees work for a single department, and one department has multiple employees. This is a many-to-one (N:1) relationship.
Manages − A single employee manages one department, and one department is managed by one employee. This is a one-to-one (1:1) relationship.
Controls − Each department controls multiple projects, and each project is controlled by a single department. This is a one-to-many (1:N) relationship.
Dependent Of − One employee has multiple dependents, and each dependent belongs to a single employee. This is a one-to-many (1:N) relationship. Since Dependent is a weak entity, this is an identifying relationship.
Supervises − One employee supervises many other employees. This is a recursive one-to-many (1:N) relationship on the Employee entity.
Step 4 − Relationship Summary
| Relationship | Between | Cardinality |
|---|---|---|
| Works For | Employee ? Department | N : 1 |
| Manages | Employee ? Department | 1 : 1 |
| Controls | Department ? Project | 1 : N |
| Dependent Of | Employee ? Dependent | 1 : N |
| Supervises | Employee ? Employee | 1 : N (recursive) |
Step 5 − Complete ER Diagram
The complete ER diagram combining all entities, attributes, and relationships is as follows −
Conclusion
Constructing an ER diagram involves identifying entities, their attributes and key attributes, and then establishing the relationships with proper cardinality between them. In this company database, Employee, Department, Project, and Dependent are the main entities connected through relationships like Works For, Manages, Controls, and Dependent Of. Dependent is modeled as a weak entity since it relies on Employee for unique identification.
16K+ Views
