Database Management System

BCAC 0020
Topic: ER Diagram
Presented by: Sanjiv Agrawal
Assistant Professor
Computer Engineering & Applications
Department,
GLA University, Mathura

• [email protected], +91-
 ER diagram
• ER diagram or Entity Relationship diagram is a conceptual
model that gives the graphical representation of the logical
structure of the database.

• It shows all the constraints and relationships that exist among the
different components.
 Why Use ER Diagrams In DBMS?

• ER diagrams are used to represent the E-R model in a database,

which makes them easy to be converted into relations (tables).

• ER diagrams require no technical knowledge and no hardware

support.

• These diagrams are very easy to understand and easy to create

even for a naive user.

• It gives a standard solution for visualizing the data logically.

 Symbols Used in ER Model

Rectangles: Rectangles represent Entities in the ER Model.

Ellipses: Ellipses represent Attributes in the ER Model.

Diamond: Diamonds represent Relationships among Entities.

Lines: Lines represent attributes to entities and entity sets with

other relationship types.

Double Ellipse: Double Ellipses represent Multi-Valued Attributes.

Double Rectangle: Double Rectangle represents a Weak Entity.

 Representation as ER Diagram

Student Table
Roll_no Name Age
1 Akshay 20
2 Rahul 19
3 Pooja 20
4 Aarti 19
 Components of ER diagram
• An ER diagram is mainly composed of following three
components-
– Entity
– Attribute
– Relationship
 ER Diagram Symbols- Entity Sets

1. Entity: An Entity may be an object with a physical existence – a

particular person, car, house, or employee – or it may be an object
with a conceptual existence – a company, a job, or a university
course.

Entity Set: An Entity is an object of Entity Type and a set of all

entities is called an entity set.

• An entity set may be of the following two types-

– Strong entity set
– Weak entity set
 Entity Sets in DBMS
• An entity refers to any object having-
– Either a physical existence such as a particular person, office, house or
car.
– Or a conceptual existence such as a school, a university, a company or
a job.

• In ER diagram,
– Attributes are associated with an entity set.
– Attributes describe the properties of entities in the entity set.
– Based on the values of certain attributes, an entity can be identified
uniquely.
 ER Diagram Symbols- Entity Sets

I. Strong Entity Set-

• A Strong Entity is a type of entity that has a key Attribute.
• It does not depend on other Entity in the Schema.
• It has a primary key, that helps in identifying it uniquely, and
• it is represented by a rectangle
.
II. Weak Entity Set-
• A weak entity set do not have its own primary key.
• It is represented using a double rectangle.
 1. Strong Entity Set
• A strong entity set is an entity set that contains sufficient
attributes to uniquely identify all its entities.
• In other words, a primary key exists for a strong entity
set.
• Primary key of a strong entity set is represented by
underlining it.
 1. Strong Entity Set
Symbols Used-
• A single rectangle is used for representing a strong entity set.
• A diamond symbol is used for representing the relationship
that exists between two strong entity sets.
• A single line is used for representing the connection of the
strong entity set with the relationship set.
• A double line is used for representing the total participation
of an entity set with the relationship set.
• Total participation may or may not exist in the relationship.
1. Strong Entity Set
In this ER diagram,
• Two strong entity sets “Student” and “Course” are related to each other.
• Student ID and Student name are the attributes of entity set “Student”.
• Student ID is the primary key using which any student can be identified
uniquely.
• Course ID and Course name are the attributes of entity set “Course”.
• Course ID is the primary key using which any course can be identified
uniquely.
• Double line between Student and relationship set signifies total
participation.
• It suggests that each student must be enrolled in at least one
course.
• Single line between Course and relationship set signifies partial
participation.
• It suggests that there might exist some courses for which no enrollments
 2. Weak Entity Set
• A weak entity set is an entity set that does not contain
sufficient attributes to uniquely identify its entities.
• In other words, a primary key does not exist for a weak entity
set.
• However, it contains a partial key called as a discriminator.
• Discriminator can identify a group of entities from the entity
set.
• Discriminator is represented by underlining with a dashed
line
 2. Weak Entity Set
NOTE-
•The combination of discriminator and primary key of the
strong entity set makes it possible to uniquely identify all
entities of the weak entity set.
•Thus, this combination serves as a primary key for the weak
entity set.
• Clearly, this primary key is not formed by the weak entity
set
completely.
 2. Weak Entity Set
Symbols Used-
• A double rectangle is used for representing a weak entity set.
• A double diamond symbol is used for representing the
relationship that exists between the strong and weak entity
sets and this relationship is known as identifying relationship.
• A double line is used for representing the connection of the
weak entity set with the relationship set.
• Total participation always exists in the identifying relationship.
2. Weak Entity Set
• In this ER diagram,

• One strong entity set “Building” and one weak entity set “Apartment” are
related to each other.
• Strong entity set “Building” has building number as its primary key.
• Door number is the discriminator of the weak entity set “Apartment”.
• This is because door number alone can not identify an apartment uniquely
as there may be several other buildings having the same door number.
• Double line between Apartment and relationship set signifies total
participation.
• It suggests that each apartment must be present in at least one
building.
• Single line between Building and relationship set signifies partial
participation.
• It suggests that there might exist some buildings which has no
apartment.
 2. Weak Entity Set
• To uniquely identify any apartment,
– First, building number is required to identify the particular building.
– Secondly, door number of the apartment is required to uniquely
identify the apartment.

• Thus, Primary key of Apartment

= Primary key of Building + Its own discriminator
= Building number + Door number
 Strong entity set VS Weak entity set

Strong entity set Weak entity set

A single rectangle is used for the A double rectangle is used for the
representation of a strong entity set. representation of a weak entity set.

It contains sufficient attributes to form its It does not contain sufficient attributes to
primary key. form its primary key.

A double diamond symbol is used for the

A diamond symbol is used for the representation of the identifying
representation of the relationship that relationship that exists between the strong
exists between the two strong entity sets. and weak entity set.

A single line is used for the representation A double line is used for the representation
of the connection between the strong of the connection between the weak entity
entity set and the relationship. set and the relationship set.

Total participation may or may not exist in Total participation always exists in the
the relationship. identifying relationship.
 Types of Attributes
• Attributes are the descriptive properties which are owned by
each entity of an Entity Set.
• There exist a specific domain or set of values for each
attribute from where the attribute can take its values.
 ER Diagram Symbols - Attributes

• Attributes are the properties which describes the entities of

an entity set.
• There are several types of attributes.
1. Simple Attributes-
• Simple attributes are those attributes which can not be
divided further.

Example

• Here, all the attributes are simple attributes as they can not
be divided further.
2. Composite Attributes-
• Composite attributes are those attributes which are
composed of many other simple attributes.

Example-

• Here, the attributes “Name” and “Address” are composite attributes as

they are composed of many other simple attributes.
3. Single Valued Attributes-
• Single valued attributes are those attributes which can take
only one value for a given entity from an entity set.

• Here, all the attributes are single valued attributes as they can
take only one specific value for each entity.
4. Multi Valued Attributes-
• Multi valued attributes are those attributes which can take
more than one value for a given entity from an entity set.

Example-

• Here, the attributes “Mob_no” and “Email_id” are multi valued attributes
as they can take more than one values for a given entity.
5. Derived Attributes-
• Derived attributes are those attributes which can be derived
from other attribute(s).

Example-

• Here, the attribute “Age” is a derived attribute as it can be derived from

the attribute “DOB”.
6. Key Attributes-
• Key attributes are those attributes which can identify an
entity uniquely in an entity set.

Example-

• Here, the attribute “Roll_no” is a key attribute as it can

identify any student uniquely.
 ER Diagram Symbols-Relationship Sets

• Relationship defines an association among several entities.

• A relationship set is a set of same type of relationships.

• A relationship set may be of the following two types-

– Strong relationship set
– Weak relationship set
 ER Diagram Symbols-Relationship Sets

1. Strong Relationship Set-

• A strong relationship exists between two strong entity sets.
• It is represented using a diamond symbol.
2. Weak Relationship Set-
• A weak relationship exists between the strong and weak
entity set.
• It is represented using a double diamond symbol.
 Relationship in DBMS
• A relationship is defined as an association among several
entities.
Example
• ‘Enrolled in’ is a relationship that exists between
entities Student and Course.
Relationship Set-
• A relationship set is a set of relationships of same type.
Example
• Set representation of above ER diagram is-
 Degree of a Relationship Set
• The number of entity sets that participate in a relationship set
is termed as the degree of that relationship set.

• Thus, Degree of a relationship set

= Number of entity sets participating in a relationship set
Types of Relationship Sets-

• On the basis of degree of a relationship set, a relationship set

can be classified into the following types-
– Unary relationship set
– Binary relationship set
– Ternary relationship set
1. Unary Relationship Set-
• Unary relationship set is a relationship set where only one
entity set participates in a relationship set.
Example-
2. Binary Relationship Set-

• Binary relationship set is a relationship set where two entity

sets participate in a relationship set.
3. Ternary Relationship Set-
• Ternary relationship set is a relationship set where three
entity sets participate in a relationship set.
 Cardinality in ER Diagram
• Cardinality constraint defines the maximum number of
relationship instances in which an entity can participate.

• There are 4 types of cardinality ratios-

– Many-to-Many cardinality (m:n)
– Many-to-One cardinality (m:1)
– One-to-Many cardinality (1:n)
– One-to-One cardinality (1:1 )
 ER Diagram Symbols –
Cardinality Constraints / Ratios
1. Many-to-Many Cardinality
• By this cardinality constraint,
• An entity in set A can be associated with any number (zero or
more) of entities in set B.
• An entity in set B can be associated with any number (zero or
more) of entities in set A.
• One student can enroll in any number (zero or more) of
courses.
• One course can be enrolled by any number (zero or more) of
students.

Representation of Many-To-Many Cardinality

2. Many-to-One Cardinality
• An entity in set A can be associated with at most one entity in
set B.
• An entity in set B can be associated with any number (zero or
more) of entities in set A.
• One student can enroll in at most one course.
• One course can be enrolled by any number (zero or more) of
students.

Representation of Many-To-One Cardinality

3. One-to-Many Cardinality
• An entity in set A can be associated with any number (zero or
more) of entities in set B.
• An entity in set B can be associated with at most one entity in
set A.
• One student can enroll in any number (zero or more) of courses.
• One course can be enrolled by at most one student.

Representation of One-To-Many Cardinality

4. One-to-One Cardinality
• An entity in set A can be associated with at most one entity in set B.
• An entity in set B can be associated with at most one entity in set A.

Representation of One-To-One Cardinality

• One student can enroll in at most one course.
• One course can be enrolled by at most one student.
 Participation Constraints
• Participation constraints define the least number of
relationship instances in which an entity must compulsorily
participate.

• There are two types of participation constraints-

– Total participation
– Partial participation
1. Total Participation-
• It specifies that each entity in the entity set must
compulsorily participate in at least one relationship instance
in that relationship set.
• That is why, it is also called as mandatory participation.
• Total participation is represented using a double line
between the entity set and relationship set.
• Double line between the entity set “Student” and relationship
set “Enrolled in” signifies total participation.
• It specifies that each student must be enrolled in at least one
course.
2. Partial Participation
• It specifies that each entity in the entity set may or may not
participate in the relationship instance in that relationship set.
• That is why, it is also called as optional participation.
• Partial participation is represented using a single line between
the entity set and relationship set.
• Single line between the entity set “Course” and relationship
set “Enrolled in” signifies partial participation.
• It specifies that there might exist some courses for which no
enrollments are made.
Relationship between Cardinality and Participation Constraints-

• Minimum cardinality tells whether the participation is partial

or total.
– If minimum cardinality = 0, then it signifies partial
participation.
– If minimum cardinality = 1, then it signifies total
participation.
• Maximum cardinality tells the maximum number of entities
that participates in a relationship set.
Steps to Create an ERD
 Steps to Create an ERD
A) Identify all the entities (like strong entity or weak entity),

B) Decide relationships (like unary, binary or ternary),

C) Decide cardinality (like One-to-One, One-to-Many, Many-to-One,

or Many-to-Many),

D) Identify the type of attributes (like key attributes, multivalued

attributes, simple attributes, derived attributes, composite
attributes etc.),

E) Draw ER diagram.
 ERD Example-1
• 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.
• 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
Step 5) Create the ERD
• A more modern representation of ERD Diagram
 Example 2
• Suppose you are given the following requirements for a
simple database for the National Hockey League (NHL):
– the NHL has many teams,
– each team has a name, a city, a coach, a captain, and a set of
players,
– each player belongs to only one team,
– each player has a name, a position (such as left wing or goalie),
a skill level, and a set of injury records,
– a team captain is also a player,
– a game is played between two teams (referred to as host_team
and guest_team) and has a date (such as May 11th, 1999) and a
score (such as 4 to 2).
• Entities:
– Team(t_name, city, coach )
– Player(p_name, position, skill_level)
– Injury record (Weak entity, depend on player)
• Relationships:
– Each team has a captain which is also a player
– Each team has many player
– A game is played between two teams(host and guest),
and has date and score(attributes)
– A player has injury record
 Date
Score

Game Captain Has

1 1 m
1 Host Guest 1 Injury Record
1
n Player
Team 1 Belongs
_To
P_no

Coach Description
T_name

City
Position
P_name
P_no Skill_level
 Example 3
• A university registrar’s office maintains data about the following
entities:
– courses, including number, title, credits, syllabus, and
prerequisites;
– course offerings, including course number, year, semester, section
number, instructor(s), timings, and classroom;
– students, including student-id, name, and program;
– instructors, including identification number, name, department,
and
title.
• Further, the enrollment of students in courses and grades awarded
to students in each course they are enrolled for must be
appropriately modeled.
• Construct an E-R diagram for the registrar’s office.
• Document all assumptions that you make about the mapping
constraints.
Entities
• Student(sid, name, program)
• Course(C_number, title, credits, syllabus)
• course offerings( c_number, year, semester, section_number, timings, and
classroom)
• Instructor(iid, name, department, title)

Relationships
• Students enrolls in course offerings, then grade is allotted.
• Instructor teaches course offerings.
• A course is offered Course offerings
• A main course required A prerequisite
course.
 Example 4
• Construct an E-R diagram for a car-insurance company whose
customers own one or more cars each.
• Each car has associated with it zero to any number of
recorded accidents.
• Construct appropriate tables for the above ER
Diagram ?
• Car insurance tables:
– person (driver-id, name, address)
– car (license, year,model)
– accident (report-number, date, location)
– participated(driver-id, license, report-number,
damage-amount)
 Example 5
• Construct an E-R diagram for a hospital with a set of patients
and a set of medical doctors.
• Associate with each patient a log of the various tests and
examinations conducted.
• Construct appropriate tables for the above ER
Diagram :
– Patient(SS#, name, insurance)
– Physician ( name, specialization)
– Test-log( SS#, test-name, date, time)
– Doctor-patient (physician-name, SS#)
– Patient-history(SS#, test-name, date)
 Example 6
• Draw the E-R diagram which models an online
bookstore.
 Converting ER Diagrams to Tables
• After designing an ER Diagram
• ER diagram is converted into the tables in relational model.
• This is because relational models can be easily implemented
by RDBMS like MySQL , Oracle etc.
 Rules

Rule-01: For Strong Entity Set With Only Simple Attributes

• A strong entity set with only simple attributes will require only one
table in relational model.
• Attributes of the table will be the attributes of the entity set.
• The primary key of the table will be the key attribute of the entity
set.

Schema : Student ( Roll_no , Gender , Age )

Roll_no Gender Age
Rule-02: For Strong Entity Set With Composite Attributes
• A strong entity set with any number of composite attributes
will require only one table in relational model.
• While conversion, simple attributes of the composite attributes
are taken into account and not the composite attribute itself.

Roll_no First_name Last_name House_no Street City

Rule-03: For Strong Entity Set With Multi Valued Attributes
• A strong entity set with any number of multi valued attributes will
require two tables in relational model.
• One table will contain all the simple attributes with the
primary key.
• Other table will contain the primary key and all the multi
valued attributes.

Roll_no City Roll_no Mobile_No

Rule-04: Translating Relationship Set into a Table
• A relationship set will require one table in the relational model.
• Attributes of the table are-
• Primary key attributes of the participating entity sets
• Its own descriptive attributes if any.
• Set of non-descriptive attributes will be the primary key.

Emp_no Dept_id Since Schema : Works in ( Emp_no , Dept_id , since )

Rule-05: For Binary Relationships With Cardinality Ratios
The following four cases are possible-

– Case-01: Binary relationship with cardinality ratio m:n

– Case-02: Binary relationship with cardinality ratio 1:n
– Case-03: Binary relationship with cardinality ratio m:1
– Case-04: Binary relationship with cardinality ratio 1:1
Case-01: For Binary Relationship With Cardinality Ratio m:n

Here, three tables will be required-

• A ( a1 , a2 )
• R ( a1 , b1 )
• B ( b1 , b2 )
Case-02: For Binary Relationship With Cardinality Ratio 1:n

Here, two tables will be required-

• A ( a1 , a2 )
• BR ( a1 , b1 , b2 )
NOTE- Here, combined table will be drawn for the entity set B
and relationship set R.
Case-03: For Binary Relationship With Cardinality Ratio m:1

Here, two tables will be required-

• AR ( a1 , a2 , b1 )
• B ( b1 , b2 )
NOTE- Here, combined table will be drawn for the entity set A
and relationship set R.
Case-04: For Binary Relationship With Cardinality Ratio 1:1

Here, two tables will be required.

Either combine ‘R’ with ‘A’ or ‘B’

Way-01:
AR ( a1 , a2 , b1 )
B ( b1 , b2 )

Way-02:
A ( a1 , a2 )
BR ( a1 , b1 , b2 )
Thumb Rules to Remember
• While determining the minimum number of tables required
for binary relationships with given cardinality ratios, following
thumb rules must be kept in mind-
– For binary relationship with cardinality ration m : n , separate and
individual tables will be drawn for each entity set and relationship.
– For binary relationship with cardinality ratio either m : 1 or 1 : n ,
always remember “many side will consume the relationship” i.e. a
combined table will be drawn for many side entity set and relationship
set.
– For binary relationship with cardinality ratio 1 : 1 , two tables will be
required. You can combine the relationship set with any one of the
entity sets.
 Rule-06: For Binary Relationship With Both Cardinality
Constraints and Participation Constraints

• Cardinality constraints will be implemented as discussed in

Rule-05.
• Because of the total participation constraint, foreign key
acquires NOT NULL constraint i.e. now foreign key can not be
null.
• Case-01: For Binary Relationship With Cardinality Constraint
and Total Participation Constraint From One Side
• Because cardinality ratio = 1 : n , so we will combine the entity set B
and relationship set R.
• Then, two tables will be required-
A ( a1 , a2 )
BR ( a1 , b1 , b2 )
• Because of total participation, foreign key a1 has acquired NOT
NULL constraint, so it can’t be null now.
• Case-02: For Binary Relationship With Cardinality Constraint
and Total Participation Constraint From Both Sides
• If there is a key constraint from both the sides of an entity set
with total participation, then that binary relationship is
represented using only single table.
• Here, Only one table is required.
ARB ( a1 , a2 , b1 , b2 )
Rule-07: For Binary Relationship With Weak Entity Set
• Weak entity set always appears in association with identifying
relationship with total participation constraint.
• Here, two tables will be required-
A ( a1 , a2 )
BR ( a1 , b1 , b2 )
 Problem-01
• Find the minimum number of tables required for the following
ER diagram in relational model-
 Solution
• Applying the rules, minimum 3 tables will be required-
MR1 (M1 , M2 , M3 ,
P1) P (P1 , P2)
NR2 (P1 , N1 , N2)
 Problem-02
• Find the minimum number of tables required to represent the
given ER diagram in relational model
 Solution
• Applying the rules, minimum 4 tables will be required-
– AR1R2 (a1 , a2 , b1 , c1)
– B (b1 , b2)
– C (c1 , c2)
– R3 (b1 , c1)
Problem-03
 Solution
• BR1R4R5 (b1 , b2 , a1 , c1 , d1)
• A (a1 , a2)
• R2 (a1 , c1)
• CR3 (c1 , c2 , d1)
• D (d1 , d2)
Problem-04
 Solution
• Applying the rules, minimum 3 tables will be required
• E1 (a1 , a2)
• E2R1R2 (b1 , b2 , a1 , c1 , b3)
• E3 (c1 , c2)
Problem-05
 Solution
• Applying the rules that we have learnt, minimum 6 tables will
be required-
Account (Ac_no , Balance , b_name)
Branch (b_name , b_city , Assets)
Loan (L_no , Amt , b_name)
Borrower (C_name , L_no)
Customer (C_name , C_street , C_city)
Depositor (C_name , Ac_no)