E-R Data Model

Definition

• An Entity–relationship model (ER model) describes the

structure of a database with the help of a diagram, which is
known as Entity Relationship Diagram (ER Diagram).

• An ER model is a design or blueprint of a database that can

later be implemented as a database.

• The main components of E-R model are: entity set and

relationship set.
 Continue..

• An ER diagram shows the relationship among entity sets. An

entity set is a group of similar entities and these entities can
have attributes.

• In terms of DBMS, an entity is a table or attribute of a table in

database.

• So by showing relationship among tables and their attributes,

ER diagram shows the complete logical structure of a
database.
 Sample E-R Diagram

• In the following diagram we have two entities Student and College and their
relationship.
• The relationship between Student and College is many to one as a college can
have many students however a student cannot study in multiple colleges at the
same time.
• Student entity has attributes such as Stu_Id, Stu_Name & Stu_Addr and
College entity has attributes such as Col_ID & Col_Name.
 Components of E-R Diagram
• Rectangle: Represents Entity sets.
Ellipses: Attributes
Diamonds: Relationship Set
Lines: They link attributes to Entity Sets and Entity sets to
Relationship Set
Double Ellipses: Multivalued Attributes
Dashed Ellipses: Derived Attributes
Double Rectangles: Weak Entity Sets
Double Lines: Total participation of an entity in a relationship
set.
E-R Model

Entity Attribute Relation

 Continue…

1. Entity : An entity is an object or component of data. An

entity is represented as rectangle in an ER diagram.
• Weak Entity : An entity that cannot be uniquely identified by
its own attributes and relies on the relationship with other
entity is called weak entity.
• The weak entity is represented by a double rectangle. For
example – a bank account cannot be uniquely identified
without knowing the bank to which the account belongs, so
bank account is a weak entity.
• Example
Bank_Acc Bank
2. Attribute : An attribute describes the property of an
entity. An attribute is represented as Oval in an ER
diagram. There are four types of attributes:
• Key attribute
• Composite attribute
• Multivalued attribute
• Derived attribute
• Key Attribute : A key attribute can uniquely identify an entity from an
entity set. For example, student roll number can uniquely identify a
student from a set of students. Key attribute is represented by oval same
as other attributes however the text of key attribute is underlined.

• Composite Attribute : An attribute that is a combination of other

attributes is known as composite attribute. For example, In student entity,
the student address is a composite attribute as an address is composed of
other attributes such as pin code, state, country.

• Multivalued Attribute : An attribute that can hold multiple values is

known as multivalued attribute. It is represented with double ovals in an
ER Diagram. For example – A person can have more than one phone
numbers so the phone number attribute is multivalued.

• Derived Attribute : A derived attribute is one whose value is dynamic and

derived from another attribute. It is represented by dashed oval in an ER
Diagram. For example – Person age is a derived attribute as it changes
over time and can be derived from another attribute (Date of birth).
Example : Student Entity with various attributes
3. Relationship : A relationship is represented by diamond shape
in ER diagram, it shows the relationship among entities. There
are four types of relationships:

1. One to One
2. One to Many
3. Many to One
4. Many to Many
1. One-to-One Relationship : When a single instance of an entity is
associated with a single instance of another entity then it is called one to
one relationship. For example, a person has only one passport and a
passport is given to one person.
2. One-to-Many Relationship : When a single instance of an entity is
associated with more than one instances of another entity then it is
called one to many relationship. For example – a customer can place
many orders but a order cannot be placed by many customers.
3. Many-to-One Relationship : When more than one instances of an entity
is associated with a single instance of another entity then it is called
many to one relationship. For example – many students can study in a
single college but a student cannot study in many colleges at the same
time.
4. Many-to-Many Relationship : When more than one instances of an
entity is associated with more than one instances of another entity then
it is called many to many relationship. For example, a student can be
assigned to many projects and a project can be assigned to many
students.
Various Relationship Diagram
 Participation

1. Total : Total participation of an entity set represents that

each entity in entity set must have at least one relationship
in a relationship set. It is also called mandatory
participation.
For example: In the following diagram each college must have
at-least one associated Student. Total participation is
represented using a double line between the entity set and
relationship set.
2. Partial participation of an entity set represents that each
entity in the entity set may or may not participate in the
relationship instance in that relationship set. It is also called
as optional participation
• Partial participation is represented using a single line between
the entity set and relationship set.
 Participation Continue..

Consider an example of an IT company. There are many

employees working for the company. Let’s take the example
of relationship between employee and role software
engineer. Every software engineer is an employee but not
every employee is software engineer as there are employees
for other roles as well, such as housekeeping, managers, CEO
etc. so we can say that participation of employee entity set
to the software engineer relationship is partial.
Example
 Problem

Drawing of ER model of university database application

considering the constraints −

• A university has many departments.

• Each department has multiple instructors (one person is HOD). Here the
HOD refers to the head of department.
• An instructor belongs to only one department.
• Each department offers multiple courses, each subject is taught by a single
instructor.
• A student may enroll for many courses offered by different departments.
• Solution
• Follow the steps given below to draw an Entity Relationship (ER)
diagram for a University database application −
• Step 1 − Identifying 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 −
• Department
• Course
• Student
• Instructor
• Head of the Department (HOD) is not an entity set. It is a relationship
between the instructor and department entities.
Step 1 − Identifying the entity sets.
• The entity set has multiple instances in a given
university database.
• As per the given constraints the entity sets are as
follows −
• Department
• Course
• Student
• Instructor
Step 2 − Identifying the attributes for the given entities
• Department − the relevant attributes are
department Name and location.
• Course − The relevant attributes are course_No,
course Name, Duration, and prerequisite.
• Teacher − The relevant attributes are Teacher Name,
Room No, and telephone number.
• Student − The relevant attributes are Student
Roll_No, Student Name, and date of birth.
Step 3 − Identifying the Key attributes
• Department Name is the key attribute for
Department.
• Course_No is the key attribute for Course entity.
• Teacher_Name is the key attribute for the
Teacher entity.
• Student_No is the key attribute for Student
entities.
E-R Diagram for University
E-R Diagram for Banking System
 Strong & Weak Entity Set
Strong Entity:
A strong entity is not dependent on any other entity in the schema. A
strong entity will always have a primary key. Strong entities are
represented by a single rectangle. The relationship of two strong entities is
represented by a single diamond.
Various strong entities, when combined together, create a strong entity
set.
Weak Entity:
A weak entity is dependent on a strong entity to ensure its existence.
Unlike a strong entity, a weak entity does not have any primary key. It
instead has a partial discriminator key. A weak entity is represented by a
double rectangle.
The relation between one strong and one weak entity is represented by a
double diamond. This relationship is also known as identifying relationship.
Strong Entity set & Relationship
Weak Entity set & Relationship
Example
Difference between Weak & Strong entity