Entity Relationship (ER) Diagram?

ER Diagram stands for Entity Relationship Diagram, also known as ERD is a diagram that
displays the relationship of entity sets stored in a database.
In other words, ER diagrams help to explain the logical structure of databases.
ER diagrams are created based on three basic concepts: entities, attributes and
relationships.
ER Diagrams contain different symbols that use:
• rectangles to represent entities,
• ovals to define attributes and
• diamond shapes to represent relationships.
At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram
includes many specialized symbols, and its meanings make this model unique.
The purpose of ER Diagram is to represent the entity framework infrastructure.
 Entity Relationship Diagram Example

What is ER Model?
ER Model stands for Entity Relationship Model is a high-level conceptual data model
diagram.
ER model helps to systematically analyze data requirements to produce a well-designed
database.
The ER Model represents real-world entities and the relationships between them.
Creating an ER Model in DBMS is considered as a best practice before implementing your
database.
ER Modeling helps you to analyze data requirements systematically to produce a well-
designed database.
So, it is considered a best practice to complete ER modeling before implementing your
database.

History of ER models
ER diagrams are visual tools that are helpful to represent the ER model. Peter Chen proposed ER
Diagram in 1971 to create a uniform convention that can be used for relational databases and
networks. He aimed to use an ER model as a conceptual modeling approach.

Why use ER Diagrams?

Here, are prime reasons for using the ER Diagram

• Helps you to define terms related to entity relationship modeling

• Provide a preview of how all your tables should connect, what fields are going to be on each
table
• Helps to describe entities, attributes, relationships
• ER diagrams are translatable into relational tables which allows you to build databases quickly
• ER diagrams can be used by database designers as a blueprint for implementing data in specific
software applications
• The database designer gains a better understanding of the information to be contained in the
database with the help of ERP diagram
• ERD Diagram allows you to communicate with the logical structure of the database to users
Facts about ER Diagram Model
• ER model allows you to draw Database Design
• It is an easy-to-use graphical tool for modeling data
• Widely used in Database Design
• It is a GUI representation of the logical structure of a Database
• It helps you to identify the entities which exist in a system and the relationships between those
entities

ER Diagrams Symbols & Notations

Entity Relationship Diagram Symbols & Notations mainly contain three basic symbols, which are
rectangle, oval, and diamond, to represent relationships between elements, entities, and attributes.

There are some sub-elements that are based on the main elements in the ERD Diagram.

ER Diagram is a visual representation of data that describes how data is related to each other using
different ERD Symbols and Notations.

Following are the main components and its symbols in ER Diagrams:

• Rectangles: This Entity Relationship Diagram symbol represents entity types

• Ellipses: Symbol represent attributes
• Diamonds: This symbol represents relationship types
• Lines: It links attributes to entity types and entity types with other relationship types
• Primary key: attributes are underlined
• Double Ellipses: Represent multi-valued attributes
 ER Diagram Symbols
Components of the ER Diagram
This model is based on three basic concepts:

1. Entities
• Weak Entity
2. Attributes
• Key Attribute
• Composite Attribute
• Multivalued Attribute
• Derived Attribute
3. Relationships
• One-to-one Relationships
• One-to-Many Relationships
• Many-to-Many Relationships
ER Diagram Examples

For example, in a University database, we might have entities for Students, Courses, and Lecturers.
Students entity can have attributes like Rollno, Name, and DeptID. They might have relationships with
Courses and Lecturers.

Components of the ER Diagram

WHAT IS ENTITY?
A real-world thing either living or non-living that is easily recognizable and nonrecognizable. I

t is anything in the enterprise that is to be represented in our database.

It may be a physical thing or simply a fact about the enterprise or an event that happens in the real
world.

An entity can be place, person, object, event or a concept, which stores data in the database.

The characteristics of entities are must have an attribute, and a unique key.

Every entity is made up of some ‘attributes’ which represent that entity.

Examples of entities:

• Person: Employee, Student, Patient

• Place: Store, Building
• Object: Machine, product, and Car
• Event: Sale, Registration, Renewal
• Concept: Account, Course
 Notation of an Entity
Entity set
Student entity

An entity set is a group of similar kind of entities. It may contain entities with attribute sharing similar
values. Entities are represented by their properties, which also called attributes. All attributes have their
separate values. For example, a student entity may have a name, age, class, as attributes.

Example of Entities:

A university may have some departments. All these departments employ various lecturers and offer
several programs.

Some courses make up each program. Students register in a particular program and enroll in various
courses. A lecturer from the specific department takes each course, and each lecturer teaches a various
group of students.
Relationship
Relationship is nothing but an association among two or more entities. E.g., Tom works in the Chemistry
department.

Entities take part in relationships. We can often identify relationships with verbs or verb phrases.

For example:

• You are attending this lecture

• I am giving the lecture
• Just loke entities, we can classify relationships according to relationship-types:
• A student attends a lecture
• A lecturer is giving a lecture.

Weak Entities
A weak entity is a type of entity which doesn’t have its key attribute. It can be identified uniquely by
considering the primary key of another entity. For that, weak entity sets need to have participation.
In above ER Diagram examples, “Trans No” is a discriminator within a group of transactions in an ATM.

Let’s learn more about a weak entity by comparing it with a Strong Entity

Strong Entity Set Weak Entity Set

Strong entity set always has a primary key. It does not have enough attributes to build a primary key.
It is represented by a rectangle symbol. It is represented by a double rectangle symbol.
It contains a Primary key represented by the It contains a Partial Key which is represented by a dashed
underline symbol. underline symbol.
The member of a strong entity set is called as The member of a weak entity set called as a subordinate
dominant entity set. entity set.
Primary Key is one of its attributes which In a weak entity set, it is a combination of primary key
helps to identify its member. and partial key of the strong entity set.
In the ER diagram the relationship between
The relationship between one strong and a weak entity
two strong entity set shown by using a
set shown by using the double diamond symbol.
diamond symbol.
The connecting line of the strong entity set The line connecting the weak entity set for identifying
with the relationship is single. relationship is double.
Attributes
It is a single-valued property of either an entity-type or a relationship-type.

For example, a lecture might have attributes: time, date, duration, place, etc.

An attribute in ER Diagram examples, is represented by an Ellipse

 Types of
Description
Attributes
Simple Simple attributes can’t be divided any further. For example, a student’s contact
attribute number. It is also called an atomic value.
Composite It is possible to break down composite attribute. For example, a student’s full
attribute name may be further divided into first name, second name, and last name.
This type of attribute does not include in the physical database. However, their
Derived values are derived from other attributes present in the database. For example,
attribute age should not be stored directly. Instead, it should be derived from the DOB of
that employee.
Multivalue Multivalued attributes can have more than one values. For example, a student can
d attribute have more than one mobile number, email address, etc.

Cardinality
Defines the numerical attributes of the relationship between two entities or entity sets.

Different types of cardinal relationships are:

• One-to-One Relationships
• One-to-Many Relationships
• May to One Relationships
• Many-to-Many Relationships
1.One-to-one:

One entity from entity set X can be associated with at most one entity of entity set Y and vice versa.

Example: One student can register for numerous courses. However, all those courses have a single line
back to that one student.
2.One-to-many:

One entity from entity set X can be associated with multiple entities of entity set Y, but an entity from
entity set Y can be associated with at least one entity.

For example, one class is consisting of multiple students.

3. Many to One

More than one entity from entity set X can be associated with at most one entity of entity set Y. However,
an entity from entity set Y may or may not be associated with more than one entity from entity set X.

For example, many students belong to the same class.

4. Many to Many:

One entity from X can be associated with more than one entity from Y and vice versa.

For example, Students as a group are associated with multiple faculty members, and faculty members
can be associated with multiple students.

How to Create an Entity Relationship Diagram (ERD)

Now in this ERD Diagram Tutorial, we will learn how to create an ER Diagram. Following are the steps to
create an ER Diagram:

Steps to Create an ER Diagram

Let’s study them with an Entity Relationship Diagram Example:
Example: In a university, a Student enrolls in Courses. A student must be assigned to at
least one or more Courses. Each course is taught by a single Professor. To maintain
instruction quality, a Professor can deliver only one course
Step 1) Entity Identification
We have three entities

• Student
• Course
• Professor

Step 2) Relationship Identification

We have the following two relationships

• The student is assigned a course

• Professor delivers a course
Step 3) Cardinality Identification
For them problem statement we know that,

• A student can be assigned multiple courses

• A Professor can deliver only one course

Step 4) Identify Attributes

You need to study the files, forms, reports, data currently maintained by the organization to identify
attributes. You can also conduct interviews with various stakeholders to identify entities. Initially, it’s
important to identify the attributes without mapping them to a particular entity.

Once, you have a list of Attributes, you need to map them to the identified entities. Ensure an attribute is
to be paired with exactly one entity. If you think an attribute should belong to more than one entity, use
a modifier to make it unique.

Once the mapping is done, identify the primary Keys. If a unique key is not readily available, create one.
Entity Primary Key Attribute
Student Student_ID StudentName
Professor Employee_ID ProfessorName
Course Course_ID CourseName
For Course Entity, attributes could be Duration, Credits, Assignments, etc. For the sake of ease we have
considered just one attribute.

Step 5) Create the ERD Diagram

A more modern representation of Entity Relationship Diagram Example

Best Practices for Developing Effective ER Diagrams

Here are some best practice or example for Developing Effective ER Diagrams.

• Eliminate any redundant entities or relationships

• You need to make sure that all your entities and relationships are properly labeled
• There may be various valid approaches to an ER diagram. You need to make sure that the ER
diagram supports all the data you need to store
 • You should assure that each entity only appears a single time in the ER diagram
• Name every relationship, entity, and attribute are represented on your diagram
• Never connect relationships to each other
• You should use colors to highlight important portions of the ER diagram

Summary
• ER Model in DBMS stands for an Entity-Relationship model
• The ER model is a high-level data model diagram
• ER diagrams are a visual tool which is helpful to represent the ER model
• ER diagrams in DBMS are blueprint of a database
• Entity relationship diagram DBMS displays the relationships of entity set stored in a database
• ER diagrams help you to define terms related to entity relationship modeling
• ER Model in DBMS is based on three basic concepts: Entities, Attributes & Relationships
• An entity can be place, person, object, event or a concept, which stores data in the database (DBMS)
• Relationship is nothing but an association among two or more entities
• A weak entity is a type of entity which doesn’t have its key attribute
• It is a single-valued property of either an entity-type or a relationship-type
• It helps you to defines the numerical attributes of the relationship between two entities or entity
sets
• ER- Diagram DBMS is a visual representation of data that describe how data is related to each other
• While Drawing ER diagrams in DBMS, you need to make sure all your entities and relationships are
properly labeled.
 More Examples:
# ER Diagrams Examples of Common Scenarios
#2 ER Diagram of Hotel Management System

Entities and their Attributes

The main entities of the Hotel Management System are a hotel, rooms, services, payments,
bookings, and customers.

• Hotel Entity: Attributes are hotel_type, hotel_name, hotel_rent, hotel_ID and

hotel_description.
• Payments Entity: Attributes are payment customer ID, payment_ID, payment_description,
payment_date, payment_amount.
• Customer Entity: Attributes are customer_pass, customer_email, customer_mobile,
customer_ID, customer_name, customer_address.
• Booking Entity: Attributes are booking_description, booking_type, booking_ID.

All the entities are normalized, and the duplicity of records is reduced. Furthermore, there are one-
to-one and one-to-many relationships.
#3 ER Diagram of Database - University Database

A university database consists of all the information regarding a student. Although such a
database is not suitable for a large institution, it illustrates relationships that help resolve queries.

Entities and their Attributes

• Student Entity: Attributes are a name, age, birthplace, birthday, etc.

• Course Entity: Attributes are course title, course description, credit hours, course teacher.
• Professor Entity: Attributes are tenure status, job title, name, age.

A student enrolls in many courses, so it's a one-to-many relationship.

 #4 ER Diagram of Library Management System
The library management system has a database that shows the relationships between the
entities such as a book, publisher, and member. Since this is a simple system, it has only three
entities. Other systems can be more complex with a more significant number of entities such as
staff etc.

Entities and their Attributes

• Book Entity: It has author, book_ID, title, price, and availability.

• Publisher Entity: It has publisher_ID, publisher_address, and publisher_name.
• Member Entity: It has member_ID, member_date, member_type, member_address,
member_name, and expiry_date.

In this example, we can again see one-to-one and one-to-many relationships. A member can
borrow many books, but only one reader can borrow one book. So, the relationship is 1: Many.
#5 ER Diagram of Online Shopping System

The ER diagram given below is for an Online Shopping Management System. The purpose of this
ER diagram is to use a database and Java to create a good project.

Entities and their Attributes

• Website/Application Entity: Attributes are domain name and app name.

• Customer Entity: Attributes are username, password, email address, mobile number, country
name, district, address, C_id.
• Products Entity: Attributes are product ID, P_name, P_category, P_price, P_availability.
• Admin Entity: Attributes are username, password, A_id.
• Shopping Cart Entity: Attributes are product id, product name, total price, billing products.
• Registration Entity: Attributes are user id, username, password, forgot password, signup.

We can observe some relationships between the entity and its attributes, such as the entity
mobile app and its domain name attribute. This shows that the project head will create the project
based on software. Besides, we can also find other relationships between customers and admin,
products and admin, etc.
#6 ER Diagram of One-to-Many Relationship
Following is an example of an ER diagram that is showing a one-to-many relationship.

Entities and their Attributes

• Delivery Entity: Attributes are delivery ID, order ID, type, status, departure, arrival.
• Customer Entity: Attributes are customer ID, phone, email, customer’s address.
 • Payment Entity: Attributes are payment id, payment date, type, order ID, customer ID, total
payment.
• Order Header Entity: Attributes are order ID, order date, order time, and customer ID.
• Order Line Entity: Attributes are order id, line id, pizza id, and quantity.
• Pizza Entity: Attributes are pizza id, name, specification, and price.

You can observe the one-to-many relationships such as order time of order header entity is linked
to the payment type, payment date, and order id of the payment entity.

#7 ER Diagram of Banking System

The ER diagram given below is for the Bank Management System. It illustrates critical information
about the bank.

Entities and their Attributes

• Bank Entity: Attributes are bank name, code, and address.

• Customer Entity: Attributes are customer_id, name, phone number, and customer's address.
• Branch Entity: Attributes are branch_id, branch name, and branch address.
• Account Entity: Attributes are account_number, account_type, and account balance.
• Loan Entity: Attributes are loan_id, loan_type, and loan amount.

You can observe different relationships, such as a branch offering many loans, so 1: N relationship.
Other relationships such as M: N can also be observed.
#8 ER Diagram of Hospital Management System
The below ER diagram is for a hospital management system. You can see the different entities and
how they relate to each other.
Entities and their Attributes

• Patient Entity: Attributes are social security number, name, insurance, date of admission,
date of checking out.
• Doctors Entity: Attributes are dss#, name, specialization.
• Test Entity: Attributes are test_id, test_name, test date, test time, test result.
 Specialization/generalization
We have discussed different types of relationships that can occur between entities.
Some entities have relationships that form a hierarchy.
For example, a shipping company can have different types of ships for its business. The relationship that
exists between the concept of the ship and the specific types of ships forms a hierarchy. The ship is called a
superclass. The specific types of ships are called subclasses.
Superclass: An entity type that represents a general concept at a high level.
Subclass: An entity type that represents a specific concept at lower levels.
A subclass is said to inherit from a superclass.
A subclass can inherit from many superclasses in the hierarchy.
When a subclass inherits from one or more superclasses, it inherits all their attributes.
In addition to the inherited attributes, a subclass can also define its own specific attributes.
A subclass also inherits participation in the relationship sets in which its superclass (higher-level entity)
participates.
The process of making a superclass from a group of subclasses is called generalization.
The process of making subclasses from a general concept is called specialization.
Specialization: A means of identifying sub-groups within an entity set which have attributes that are not
shared by all the entities (top-down).
Generalization: Multiple entity sets are synthesized into a higher-level entity set, based on common
features (bottom-up).
Representation of specialization/generalization in ER diagrams
A diamond notation is a common representation of specialization/generalization relationships in ER
diagrams.

Figure 1
As an example, let’s consider the following scenario:
Africa holds many historical artefacts in different locations. Each artefact is kept in a specific location. A
location can be a point, province, country or sub-region of Africa.
The scenario has a specialization relationship between the location and different specific types of locations
(i.e. point, province, country and sub-region). This specialization relationship is represented in the ER
diagram below.

Figure 2
To demonstrate generalization, let’s imagine that an Artefact is one of the examples of the African cultural
items. Another type of a cultural item is an Artist. It is clear to see that a cultural item is a superclass of an
artefact and artist. This generalization relationship can be represented in the ER diagram as show below.

Figure 3

Constraints on specialization/generalization
There are three constraints that may apply to a specialization/generalization: membership constraints,
disjoint constraints and completeness constraints.
1. Membership constraints
i. Condition defined: Membership of a specialization/generalization relationship can be defined as
a condition in the requirements e.g. tanker is a ship where cargo can be oil.
ii. User defined: Sometimes the designer can define the superclass-subclass relationship. This can
be done to simplify the design model or represent a complex relationship that exists between
entities.
2. Disjoint constraints
i. Disjoint: The disjoint constraint only applies when a superclass has more than one subclass. If the
subclasses are disjoint, then an entity occurrence can be a member of only one of the subclasses,
 e.g. postgrads or undergrads, you cannot be both. To represent a disjoint superclass/subclass
relationship, {Or} is used.

Figure 4
ii. Overlapping: This applies when an entity occurrence may be a member of more than one subclass,
e.g., student and staff; some people are both. {And} is used to represent the overlapping
specialization/ generalization relationship in the ER diagram.

Figure 5
3. Completeness constraints
i. Total: Each superclass (higher-level entity) must belong to subclasses (lower-level entity sets), e.g.
a student must be postgrad or undergrad. To represent completeness in the
specialization/generalization relationship, the keyword {Mandatory} is used.
 Figure 6
ii. Partial: Some superclasses may not belong to subclasses (lower-level entity sets), e.g. some
people at University are neither student nor staff. The keyword {Optional} is used to represent a
partial specialization/generalization relationship.

Figure 7
We can show both disjoint and completeness constraints in the ER diagram. Following our examples, we
can combine disjoint and completeness constraints.
 Figure 8
Some members of a university are both students and staff. Not all members of the university are staff and
students.

Figure 9
A student in the university must be either an undergraduate or postgraduate, but not both.

Mapping specialization/generalization to relational tables

Specialization/generalization relationship can be mapped to relational tables in three methods. To
demonstrate the methods, we will take the student, postgraduate and undergraduate relationship. A
student in the university has a registration number and a name. Only postgraduate students have
supervisors. Undergraduates accumulates points through their coursework.

Figure 10
Method 1
All the entities in the relationship are mapped to individual tables.
Student (Regno, name)
PosGrad (Regno, supervisor)
UnderGrad (Regno, points)
Method 2
Only subclasses are mapped to tables. The attributes in the superclass are duplicated in all subclasses.
PosGrad (Regno, name, supervisor)
UnderGrad (Regno, name, points)
This method is most preferred when inheritance is disjoint and complete, e.g. every student is either
PosGrad or UnderGrad and nobody is both.
Method 3
Only the superclass is mapped to a table. The attributes in the subclasses are taken to the superclass.
Student (Regno, name, supervisor, points)
This method will introduce null values. When we insert an undergraduate record in the table, the
supervisor column value will be null. In the same way, when we insert a postgraduate record in the table,
the points value will be null.

Aggregation
Aggregation represents a {has} relationship between entity types, where one represents the {whole} and
the other the {partial}.
An example of aggregation is the Car and Engine entities. A car is made up of an engine. The car is the
whole and the engine is the part. Aggregation does not represent strong ownership. This means, a part can
exist on its own without the whole. There is no stronger ownership between a car and the engine. An
engine of a car can be moved to another car.

Representation of aggregation in ER diagrams

A line with a diamond at the end is used to represent aggregation.

Figure 11
The “whole” part must be put at the end of the diamond. For example, the Car-Engine relationship would
be represented as shown below:
 Figure 12

Composition
Composition is a form of aggregation that represents an association between entities, where there is a
strong ownership between the “whole” and the “partial”. For example, a tree and a branch have a
composition relationship. A branch is 'part' of a 'whole' tree - we cannot cut the branch and add it to
another tree.

Representation of composition in ER diagrams

A line with a filled diamond at the end is used to represent composition.

Figure 13
The example of the Tree-Branch relationship can be represented as shown below:

Figure 14