Steps to create ER Diagram

Submitted by sandesh on Sun, 06/19/2022 - 21:46

In this article, we will go through the steps that one should take while modeling an ER diagram. To understand those steps better we will use an example of a company in the following question:

Design ER model of a company database which keeps track of it's departments and employees. The database should maintain information about projects and employees working on them. Company is interested in dependants of the employees.

Steps to create ER Diagram

Identify Entity sets
Figure out the Relationships among the Entity sets
List the Attributes of Entity sets and determine the Primary Key for each of them
Find out the Regular/Strong Entity sets and Weak Entity sets
List the Attributes of Relationship sets
Specify the Mapping Cardinality Constraints for each Relationship set
Specify the Participation Constraints of each Entity set in each Relationship set.
Final ER Diagram

Note: These steps are not always followed in the same order. To solve any real-world problem, a database designer would likely do back and forth of these steps multiple times.

1. Identify Entity sets

The first and the most crucial step is to identify what are the entity sets that should be present in the database. It can be a bit confusing to beginners because there is no thumb rule to determine the entities. The general rule is that the nouns, the things or objects from the real world, are entities. They are easily distinguishable from each other. The entities can be:

Concrete or Physical Objects	People	Teachers, Students, Doctors, Patients, Customers, Authors
	Things	Products, Books, Devices, Cars, Food
	Places	Countries, Cities, Districts, Areas, Localities
Teams or Institutions
	Groups	Clubs, Classes, Cricket teams, Facebook groups
	Organizations	Colleges, Universities, Hospitals, Companies
Abstract or Conceptual Objects	Events	Sales, Purchase, Transaction, Book-review, Registration
Abstract or Conceptual Objects	Concepts	Accounts, Loans, Courses, Insurance policy, Orders

These entity sets are represented with Rectangles in the ER diagram.

Let's identify the entity sets in our example question. It says:

.. keeps track of it's departments and employees ..

This clearly states two entity sets:

Employee
Department

Then it says

.. information about projects and employees working on them ..

That means we have another entity set:

Project

It again says:

.. Company is interested in dependants of the employees ..

We can hence include one more entity:

Dependant

At the end of step 1, we have the following entity sets for our company's database.

Employee
Department
Project
Dependant

Let's represent them in our ER diagram:

2. Figure out the Relationships among the Entity sets

The next step is to analyze how the entity sets are related to each other. Generally, the verb, the action that goes on among two or more entities is a Relationship.

To put an extremely simple example, if they say "Person eats Food.", then, in this case:

Person and Food are entities. Eats is the relationship between two entities Person and Food.

The real-world scenarios aren't this simple but I hope you get the hint.

In ER diagrams, relationships are represented using a diamond shape which is connected with two or more entities with lines.

If the relationships are stated clearly in the given question (or user's requirements), then great, we are lucky. Otherwise, we have to use our existing knowledge and common sense and make some assumptions.

In the above case, the question doesn't say much about relationships. Based on our knowledge of how a company works, we assume the following relationships and represent them.

Department appoints Employee

Employee works on Project

Department manages Project

Employee supports Dependant

Let's represent all the relationships in our ER diagram.

3. List the Attributes of Entity sets and determine the Primary Key for each of them

Attributes are properties that define the entities. Similar to the previous two steps, the list of attributes(for entity sets) may or may not be provided to us. If not provided we will have to assume a few attributes for each entity set.

For displaying attributes, ellipses are used.

In the above question, the list of attributes is not provided. Thus we will assume a few basic attributes to define our entity sets. We will also set a primary key for each entity set.

Employee(Emp_ID, Emp_Name, DOB, Email, Phone)

Department(Dept_ID, Dept_Name, Contact)

Project(Proj_ID, Proj_Name, Proj_Type)

Dependant(Dpd_Name, Gender, Relation)

Let's add those attributes to our ER diagram.

4. Find out the Regular/Strong Entity sets and Weak Entity sets

Identifying weak entity sets is another step for which one should understand the difference between a Strong/Regular entity set and a Weak entity set. I hope to publish a separate article on the same soon.

In short, if an entity set B exists in the database system because of another entity set A then the entity set B is a weak entity set. The entity set B is existence-dependant on an entity set A and lacks prime attributes of its own.

The weak entity sets are denoted with double rectangles and the identifying relationships are denoted with double diamonds. The primary key for such entity sets is formed by combining the discriminator(partial key) of weak entity sets and the primary key of the identifying entity set.

In our example, the entity set Dependant is a weak entity set. Its existence in the company's database is because of the Employee entity set.

Let's make the required changes to the Dependant entity set in ER Diagram. For making it simpler to understand, I will show only the relevant entity sets and attributes here.

The changes done in this section of our ER diagram are:

The weak entity set Dependant is denoted with double rectangles.
The discriminator attribute Dpd_Name has a dashed underline.
The identifying relationship "Employee supports Dependant" is denoted with double diamonds.
Double lines are used between the entity set Dependant and relationship supports. This is to display the total participation of the Dependant entity set with the Employee entity set.

Note: We are considering 'Dpd_Name' the discriminator/partial key because we are assuming that for an Employee, the name of the family members(Dpd_Name) is unique. If this isn't unique for an employee, we can add another attribute Relation to the discriminator. Suppose, an Employee's brother's name and cousin's name are the same and both of them are dependants. For such cases, the attributes (Dpd_Name, Relation) would be suitable to become discriminator attributes. But for now, let's keep it simple and consider only the Dpd_Name as the discriminator(partial key).

5. List the Attributes of Relationship sets

Entity sets always have attributes. The Relationship sets may or may not have attributes.

In the above example, we have a relationship set:

Employee works_on Project

As Employees work on multiple projects, the company could be interested in knowing "How many hours each employee spends on each project". The attribute "Hours" depends on both Employee and Project. Adding the "Hours" attribute either on Employee or Project will not be possible here. Hence we add "Hour" to the works_on relationship set.

So far, our ER diagram looks like this.

6. Specify the Mapping Cardinality Constraints for each Relationship set

The Mapping Cardinality Constraints can be confusing for beginners. In short, to determine the mapping cardinality constraints, ask yourself this question.

How many times an entity can participate in a relationship with another entity?

In the above question, let's check:

Cardinality Constraint for the "Department appoints Employee" relationship.

Q: One Department can appoint maximum "How many Employees"?

A: Many. A department can appoint many Employees.Again:

Q: One Employee can be appointed by maximum "How many Departments"?

A: One. An employee is not appointed by more than one department.

Thus, the Cardinality Constraints for the relationship "Department appoints Employee" is One-to-Many. In other words, "Department appoints Employee" is a One-to-Many relationship.

Note: It is totally fine to put Employee on the left and Department on the right. In that case, the relationship "Employee appointed by Department" would be a Many-to-One relationship.

Cardinality Constraints for the "Department manages Project" relationship.

Q: One Department can manage (maximum) "How many Projects"?

A: Many.Again:

Q: One Project can be managed by (maximum) "How many Departments"?

A: One.

Therefore, "Department manages Project" is a One-to-Many relationship.

Cardinality Constraints for the "Employee works on Project" relationship.

Q: One Employee can work on maximum "How many Projects"?

A: Many.

Also,

Q: One Project can be worked on by maximum "How many Employees"?

A: Many.Thus, "Employee works on Project" is a Many-to-Many relationship.

Cardinality Constraints for the "Employee supports Dependants" relationship.

Q: One Employee can support maximum "How many Dependants"?

A: Many.Again:

Q: One Dependant can be supported by maximum "How many Employees"?

A: One. Here, we are assuming that a Dependant can be a Dependant of only one employee. This is a common policy seen in many companies.

Thus, "Employee supports Dependants" is a One-to-Many relationship.

Let's represent those Cardinality Constraints in our ER diagram.

To keep the diagram simple and easy to understand, I am temporarily removing the attributes and adding colours to differentiate Entity sets and Relationship sets.

Note: These Mapping Cardinality Constraints could have been different if specified differently in the given question.

7. Specify the Participation Constraints of each Entity set in each Relationship set.

Finally, we will specify Participation Constraints. To figure out if the participation is partial or total, ask yourself this question.

Is it necessary for every entity in entity set A to participate in a relationship with at least one entity of entity set B?

If the answer is Yes, then the Participation is Total.

If the answer is No, then the Participation is Partial.

For Partial Participation, we don't need to change anything. For Total Participation, we draw double lines between the relationship and the entity set having total participation.

Participation of Department and Employee in the relationship appoints.

Q: Is it mandatory for every Department to appoint at least one Employee?

A: Yes.Thus the participation of entity set Department in the relationship appoints is Total.

Note: We are assuming there are no newly formed Departments. Hence all instances in the Department entity set have at least one Employee.

Again:

Q: Is it mandatory for every Employee to be appointed by at least one Department?

A: Yes.

Thus, the participation of the entity set Employee in the relationship appoints is also Total.

Participation of Employee and Project in the relationship works on.

Q: Is it mandatory for every Employee to work on at least one Project?

A: No. Some employees could be working on Company's regular maintenance work instead of the Projects.

Thus, the participation of the Employee in the relationship 'works on' is Partial.

Also:

Q: Is it mandatory for every Project to have at least one Employee working on it?

A: Yes.

Thus, The participation of the Project in the relationship 'works on' is also Total.

Participation of Department and Project in the relationship 'manages'.

Q: Is it mandatory for every Department to manage at least one Project?

A: No. Some departments could be involved in regular maitenance work instead of projects.

Thus, The participation of the Department in the relationship 'manages' is also Partial.

Also:

Q: Is it mandatory for every Project to be managed by at least one Department?

A: Yes. If a Project exists, then it must be managed by at least one Department.Thus, The participation of the Project in the relationship 'manages' is Total.