Database Management

System

Module: 02
Entity - Relationship Model
 …Topics to be Covered…
❖ ER Model Basic Concepts.
❖ Design Process: Attributes.
❖ Design Process: Entity.
❖ Design Process: Relationship.
❖ ER Constraints.
❖ Weak Entity Sets.
❖ Extended ER Features: Generalization.
❖ Extended ER Features: Specialization.
❖ Extended ER Features: Aggregation.
❖ Conversion of ER Diagram into database schema.

2
 Introduction of ER Diagram
Definition:
➢ The Entity Relationship Model is a model for identifying entities
(like student, car or company) to be represented in the database
and representation of how those entities are related.

➢ The ER data model specifies enterprise schema that represents

the overall logical structure of a database graphically.

SUN DBMS (SECE2210)

3
B. Tech - Sem3
 Why use the ER Diagram?
➢ ER diagrams represent the E-R model in a database, making
them easy to convert into relations (tables).
➢ ER diagrams serve the purpose of real-world modeling of
objects which makes them intently useful.
➢ ER diagrams require no technical knowledge of the underlying
DBMS used.
➢ It gives a standard solution for visualizing the data logically.

SUN DBMS (SECE2210)

4
B. Tech - Sem3
 Steps to create ER Diagram
➢ Gather the requirements (functional and data) by asking
questions to the database users.
➢ Create a logical or conceptual design of the database. This is
where ER model plays a role. It is the most used graphical
representation of the conceptual design of a database.
➢ After this, focus on Physical Database Design (like indexing) and
external design (like views).

SUN DBMS (SECE2210)

5
B. Tech - Sem3
Symbols used in ER Model

SUN DBMS (SECE2210)

6
B. Tech - Sem3
Components of ER Diagram

SUN DBMS (SECE2210)

7
B. Tech - Sem3
 Design Process: Entity
What is an 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.

What is an Entity Set?

An entity refers to an individual object of an entity type, and the
collection of all entities of a particular type is called an entity set.
For example, E1 is an entity that belongs to the entity type
“Student,” and the group of all students forms the entity set.

SUN DBMS (SECE2210)

8
B. Tech - Sem3
 Design Process: Entity
Types of Entity:
There are a total of two types of Entity.
Strong Entity:
A Strong Entity is a type of entity that has a key Attribute. Strong
Entity 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. These are called Strong Entity Types.

Weak Strong Entity:

If an entity is depends on another entity then it is known as Weak
Entity.
For Example, an attribute Age is depends on DOB so, Age is
considered as Weak Entity and DOB will be considered as Strong
Entity Type.
SUN DBMS (SECE2210)
9
B. Tech - Sem3
 Design Process: Entity
➢ A weak entity type is represented by a double rectangle.
➢ The relationship between the weak entity type and its
identifying strong entity type is called identifying relationship
and it is represented by a double diamond.

SUN DBMS (SECE2210)

10
B. Tech - Sem3
 Design Process: Attributes
➢ Attributes are the properties that define the entity type.

➢ For example, Roll_No, Name, DOB, Age, Address, and Mobile_No

are the attributes that define entity type Student.

➢ In ER diagram, the attribute is represented by an oval.

SUN DBMS (SECE2210)

11
B. Tech - Sem3
 Design Process: Attributes

Attributes

Key Attribute Composite Attribute

Multi-valued Attribute Derived Attribute

SUN DBMS (SECE2210)

12
B. Tech - Sem3
 Design Process: Attributes
Key Attribute

➢ The attribute which uniquely identifies each entity in the entity

set is called the key attribute.
➢ For example, Roll_No will be unique for each student.
➢ In ER diagram, the key attribute is represented by an oval with
underlying lines.

SUN DBMS (SECE2210)

13
B. Tech - Sem3
 Design Process: Attributes
Multi-valued Attribute

➢ An attribute consisting of more than one value for a given entity.

➢ For example, Phone_No (can be more than one for a given
student).
➢ In ER diagram, a multivalued attribute is represented by a double
oval.

SUN DBMS (SECE2210)

14
B. Tech - Sem3
 Design Process: Attributes
Derived Attribute

➢ An attribute that can be derived from other attributes of the

entity type is known as a derived attribute.
➢ For Example: Age (can be derived from DOB).
➢ In ER diagram, the derived attribute is represented by a dashed
oval.

SUN DBMS (SECE2210)

15
B. Tech - Sem3
 Design Process: Attributes
Composite Attribute

➢ An attribute composed of many other attributes is called a

composite attribute.
➢ For example, the Address attribute of the student Entity type
consists of Street, City, State, and Country.
➢ In ER diagram, the composite attribute is represented by an oval
comprising of ovals.

SUN DBMS (SECE2210)

16
B. Tech - Sem3
 Example of Complete Entity Type
So, the complete entity type Student with it’s attributes can be
represented as:

SUN DBMS (SECE2210)

17
B. Tech - Sem3
ER Diagram of Hospital Management
System

SUN DBMS (SECE2210)

18
B. Tech - Sem3
Limitations of ER Diagram

SUN DBMS (SECE2210)

19
B. Tech - Sem3
 Design Process: Relationship
Relationship Type:
➢ A Relationship Type represents the association between
entity types.
➢ For example, ‘Enrolled in’ is a relationship type that exists
between entity type Student and Course.
➢ In ER diagram, the relationship type is represented by a
diamond and connecting the entities with lines.

SUN DBMS (SECE2210)

20
B. Tech - Sem3
 Design Process: Relationship
Relationship Set:
➢ A set of relationships of the same type is known as a
relationship set.
➢ The following relationship set depicts S1 as enrolled in C2, S2
as enrolled in C1, and S3 as registered in C3.

SUN DBMS (SECE2210)

21
B. Tech - Sem3
 Design Process: Relationship
Degree
Degree of a Relationship Set:
The number of different entity sets participating in a relationship
set is called the degree of a relationship set.
Unary Relationship:
When there is only ONE entity set participating in a relation, the
relationship is called a unary relationship. For example, one
person is married to only one person.

SUN DBMS (SECE2210)

22
B. Tech - Sem3
 Design Process: Relationship
Binary Relationship:
When there are TWO entities set participating in a relationship, the
relationship is called a binary relationship. For example, a Student is
enrolled in a Course.

Ternary Relationship:
When there are three entity sets participating in a relationship, the
relationship is called a ternary relationship.

SUN DBMS (SECE2210)

23
B. Tech - Sem3
Design Process: Relationship

SUN DBMS (SECE2210)

24
B. Tech - Sem3
 Design Process: Relationship
N-ary Relationship:
When there are n entities set participating in a relationship, the
relationship is called an n-ary relationship.

Cardinality

The maximum number of times an entity of an entity set participates

in a relationship set is known as Cardinality.
Cardinality can be of different types:
➢ One to One Relationship.
➢ One to Many Relationship.
➢ Many to One Relationship.
➢ Many to Many Relationship.
SUN DBMS (SECE2210)
25
B. Tech - Sem3
 Design Process: Relationship
One to One (1:1) Cardinality:
When each entity in each entity set can take part only once in the
relationship, the cardinality is one-to-one.
Let us assume, that a male can marry one female and a female
can marry one male. So the relationship will be one-to-one.

1 Can 1
Male Female
Marry

SUN DBMS (SECE2210)

26
B. Tech - Sem3
 Design Process: Relationship
One to Many (1:M) Cardinality:
In one-to-many mapping as well where each entity can be related to
more than one entity.
Let us assume, one department can accommodate many doctors.
So, the Cardinality will be 1 to M. It means one department has
many Doctors.

1 M
Department Has Doctors

SUN DBMS (SECE2210)

27
B. Tech - Sem3
 Design Process: Relationship
Many to One (M:1) Cardinality:
When entities in one entity set can take part only once in the
relationship set and entities in other entity sets can take part more
than once in the relationship set, then the cardinality is many to
one.
Let us assume, that a student can take only one Stream but one
Stream can be taken by many students. So the cardinality will be
n to 1. It means that for one Stream there can be n students but for
one student, there will be only one Stream.

M 1
Student Enroll Stream

SUN DBMS (SECE2210)

28
B. Tech - Sem3
 Design Process: Relationship
Many to Many (M:M) Cardinality:
When entities in all entity sets can take part more than once in the
relationship cardinality is many to many.
Let us assume, that a student can take more than one course and
one course can be taken by many students. So the relationship will
be many to many.

M M
Student Enroll Course

SUN DBMS (SECE2210)

29
B. Tech - Sem3
 Participation Constraints in ER Model
Participation Constraints tells us the participation in a relationship.
It either may be a Total Participation or a Partial Participation.

Total Participation

➢ When each entity in an entity set participates in a relation, it

is called Total Participation.
➢ In ER modeling, total participation exists when the instances of
the entity must necessarily participate in at least one relationship
instance.
➢ For example, in a university system, where every student must
be enrolled in at least one course, so, total participation exists in
the relationship between “Student” and “Course”.
➢ Similarly, if each professor teaches at least one course, then the
relationship between “Professor” and “Course” also displays total
participation. SUN DBMS (SECE2210)
30
B. Tech - Sem3
Participation Constraints in ER Model
Partial Participation

➢ When some of the entities in the given entity set do not

participate in a relation, it is called Partial Participation.
➢ Partial participation allows some components of a particular
entity to be present without being part of any relation.
➢ Some entities may or may not be part of the relationship, and, in
case of a single line in the ER diagram, that is indicated.

➢ For Example, in the university system, if some professors do not

teach any courses, then their participation would be partial in the
“Teaches” relationship.

SUN DBMS (SECE2210)

31
B. Tech - Sem3
 Diagram: Partial vs Total

Diagram which shows Partial Participation and Total

Participation.

SUN DBMS (SECE2210)

32
B. Tech - Sem3
 Weak Entity Sets in ER Diagram
➢ An entity type should have a key attribute which uniquely
identifies each entity in the entity set, but there exists some
entity type for which key attribute can’t be defined. These are
called Weak Entity type.
➢ The entity sets which do not have sufficient attributes to form a
Primary Key are known as weak entity sets and the entity sets
which have a primary key are known as strong entity sets.

➢ An Weak entity always has total participation but Strong

entity may not have total participation.

SUN DBMS (SECE2210)

33
B. Tech - Sem3
 Example
➢ Weak entity is depend on strong entity to ensure the
existence of weak entity. Like Strong Entity, Weak Entity does
not have any primary key, It has partial discriminator key.
➢ Weak entity is represented by double rectangle.
➢ The relation between one strong and one weak entity is
represented by double diamond.

SUN DBMS (SECE2210)

34
B. Tech - Sem3
 Extended ER Features: Generalization
➢ Generalization is the process of extracting common
properties from a set of entities and creating a generalized
entity from it.
➢ It is a bottom-up approach in which two or more entities can
be generalized to a higher-level entity if they have some
attributes in common.
➢ For Example, STUDENT and FACULTY can be generalized to a
higher-level entity called PERSON.
➢ In this case, common attributes like P_NAME, and P_ADD
become part of a higher Entity (PERSON), and specialized
Attributes like S_Name, S_Eno become part of a specialized
entity (STUDENT).
➢ Generalization is also called as “Bottom-up approach”.
SUN DBMS (SECE2210)
35
B. Tech - Sem3
Extended ER Features: Generalization

SUN DBMS (SECE2210)

36
B. Tech - Sem3
 Extended ER Features: Specialization
➢ In specialization, an entity is divided into sub-entities based on
its characteristics.
➢ It is a top-down approach where the higher-level entity is
specialized into two or more lower-level entities.
➢ For Example, an EMPLOYEE entity in an Employee
management system can be specialized into DEVELOPER,
TESTER, etc.
➢ In this case, common attributes like E_NAME, E_SAL, etc.
become part of a higher entity (EMPLOYEE), and specialized
attributes like TES_TYPE become part of a specialized entity
(TESTER).
➢ Specialization is also called as ”Top-Down approch”.

SUN DBMS (SECE2210)

37
B. Tech - Sem3
Extended ER Features: Specialization

SUN DBMS (SECE2210)

38
B. Tech - Sem3
 Extended ER Features: Aggregation

➢ An ER diagram is not capable of representing the relationship

between an entity and a relationship which may be required in
some scenarios.
➢ In those cases, a relationship with its corresponding entities is
aggregated into a higher-level entity.
➢ Aggregation is an abstraction through which we can represent
relationships as higher-level entity sets.
➢ For Example, an Employee working on a project may require
some machinery. So, REQUIRE relationship is needed between
the relationship WORKS_FOR and entity MACHINERY.

SUN DBMS (SECE2210)

39
B. Tech - Sem3
Extended ER Features: Aggregation

SUN DBMS (SECE2210)

40
B. Tech - Sem3
 ER Diagram into Database Schema

Definition:
Converting an ER diagram to a database schema involves
mapping entities to tables, attributes to columns, and
relationships to foreign keys.
Steps to convert ER diagram to Database Schema:
➢ Identify Entities and Create Tables.
➢ Choose Primary Keys.
➢ Identify Relationships and Create Foreign Keys.
➢ Handle Relationships with Attributes.

SUN DBMS (SECE2210)

41
B. Tech - Sem3
ER Diagram into Database Schema

SUN DBMS (SECE2210)

42
B. Tech - Sem3
 Exercise
Problem: 01
Draw an ER Diagram for Exam System of P P Savani University.
Problem: 02
Construct E-R diagram of the bank. It provides different kinds
of bank accounts and loans. It operates number of branches.
Problem: 03
Give Symbol used in E-R Diagram and Draw the E-R diagram of
Library Management System.
Problem: 04
Construct an E-R Diagram for an insurance company with a set
of customers, each of whom owns number of cars, also each
can have number of recorded accident associated with it.
SUN DBMS (SECE2210)
43
B. Tech - Sem3
 … THANK YOU …
See you in the Next Module.

SUN DBMS (SECE2210)

44
B. Tech - Sem3