Module 3

Introduction of ER Model
The Entity-Relationship Model (ER Model) is
a conceptual model for designing a database.
This model represents the logical structure of a
database, including entities, their attributes
and relationships between them.
 Entity: An object that is stored as data
such as Student, Course or Company.
 Attribute: Properties that describes an
entity such as StudentID, CourseName,
or EmployeeEmail.
 Relationship: A connection between
entities such as "a Student enrolls in
a Course".
Why Use ER Diagrams In DBMS?
 ER diagrams represent the E-R model in a
database, making them easy to convert into
relations (tables).
 These diagrams serve the purpose of real-
world modeling of objects which makes
them intently useful.
 Unlike technical schemas, ER diagrams
require no technical knowledge of the
underlying DBMS used.
 They visually model data and its
relationships, making complex systems
easier to understand.
Symbols Used in ER Model
ER Model is used to model the logical view of
the system from a data perspective which
consists of these symbols:
 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, such as a student's
multiple phone numbers
 Double Rectangle: Represents weak
entities, which depend on other entities for
identification.
What is an Entity?
An Entity represents a real-world object,
concept or thing about which data is stored in
a database. It acts as a building block of a
database. Tables in relational database
represent these entities.
Example of entities:
 Real-World Objects: Person, Car,
Employee etc.
 Concepts: Course, Event, Reservation etc.
 Things: Product, Document, Device etc.
The entity type defines the structure of an
entity, while individual instances of that type
represent specific entities.
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.
Types of Entity
There are two main types of entities:
1. Strong Entity
A Strong Entity is a type of entity that has a
key Attribute that can uniquely identify each
instance of the entity. A Strong Entity does not
depend on any other Entity in the Schema for
its identification. It has a primary key that
ensures its uniqueness and is represented by a
rectangle in an ER diagram.
2. Weak Entity
A Weak Entity cannot be uniquely identified
by its own attributes alone. It depends on a
strong entity to be identified. A weak entity is
associated with an identifying entity (strong
entity), which helps in its identification. A
weak entity is represented by a double
rectangle. The participation of weak entity
types is always total. 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.

Example:
A company may store the information of
dependents (Parents, Children, Spouse) of an
Employee. But the dependents can't exist
without the employee. So dependent will be a
Weak Entity Type and Employee will be
identifying entity type for dependent, which
means it is Strong Entity Type.

Attributes in ER Model
Attributes are the properties that define the
entity type. For example, for a Student entity
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.

Types of Attributes
1. 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 an
underline.

2. 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.

3. Multivalued 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.

4. Derived Attribute
An attribute that can be derived from other
attributes of the entity type is known as a
derived attribute. e.g.; Age (can be derived
from DOB). In ER diagram, the derived
attribute is represented by a dashed oval.

The Complete Entity Type Student with its

Attributes can be represented as:

Relationship Type and Relationship Set

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.

Degree of a Relationship Set

The number of different entity sets
participating in a relationship set is called
the degree of a relationship set.
1. 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.
2. 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.
3. Ternary Relationship: When there are
three entity sets participating in a relationship,
the relationship is called a ternary relationship.
4. N-ary Relationship: When there are n
entities set participating in a relationship, the
relationship is called an n-ary relationship.
Cardinality in ER Model
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:
1. One-to-One
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.
2. One-to-Many
In one-to-many mapping as well where each
entity can be related to more than one entity.
Let us assume that one surgeon department
can accommodate many doctors. So the
Cardinality will be 1 to M. It means one
department has many Doctors.

3. Many-to-One
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, cardinality is many to
one.
Let us assume that a student can take only one
course but one course can be taken by many
students. So the cardinality will be n to 1. It
means that for one course there can be n
students but for one student, there will be only
one course.

4. Many-to-Many
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.

How to Draw an ER Diagram

1. Identify Entities: The very first step is to
identify all the Entities. Represent these
entities in a Rectangle and label them
accordingly.
2. Identify Relationships: The next step is to
identify the relationship between them and
represent them accordingly using the Diamond
shape. Ensure that relationships are not
directly connected to each other.
3. Add Attributes: Attach attributes to the
entities by using ovals. Each entity can have
multiple attributes (such as name, age, etc.),
which are connected to the respective entity.
4. Define Primary Keys: Assign primary keys
to each entity. These are unique identifiers that
help distinguish each instance of the entity.
Represent them with underlined attributes.
5. Remove Redundancies: Review the
diagram and eliminate unnecessary or
repetitive entities and relationships.
6. Review for Clarity: Review the diagram
make sure it is clear and effectively conveys
the relationships between the entities.
Removing Redundant Attributes in Entity
Sets
Definition of Redundant Attributes- A
redundant attribute is an attribute in an entity
set that does not provide any new information
but instead can be derived from other
attributes or relationships. Including redundant
attributes increases the risk of inconsistent
data and anomalies in the database.
Why Remove Redundant Attributes?
Data Consistency: Keeping redundant
attributes increases the chances of
inconsistency. For example, if you store a
department name in both the Employee and
Department entities, any update to the
department name in one table may not be
reflected in the other, leading to discrepancies.
Storage Efficiency: Storing redundant data
wastes storage space. By eliminating
redundancy, you can make the database
schema more efficient.
Simplification: A cleaner, less cluttered
schema makes it easier to maintain and
understand.
How to Remove Redundant Attributes?
To remove redundant attributes in an ER
diagram, follow these steps:
Identify dependencies: Check if the attribute
depends on other attributes or entities. If an
attribute is determined by other data, it’s likely
redundant.
Establish relationships: Use relationships
between entities to represent dependencies
rather than storing derived data. For example,
if the department name is dependent on the
department ID, create a relationship between
Employee and Department, and store the
department name in the Department entity
only.
Normalization: Normalization is a process
that involves organizing the data to minimize
redundancy. It’s directly related to removing
redundant attributes. By applying
normalization rules such as 1NF (First
Normal Form), 2NF (Second Normal Form),
and 3NF (Third Normal Form), you ensure
that the data is structured in the most efficient
way, without unnecessary duplication.

Example:
Consider the following example from the ER
model:
Employee (emp_id, name, dept_id,
dept_name)
Here, dept_name is redundant because it can
be derived from the Department entity using
the dept_id. You can eliminate dept_name
from the Employee entity and create a
relationship between Employee and
Department, which stores the department
information.
Instead of:
Employee (emp_id, name, dept_id,
dept_name)
Department (dept_id, dept_name)
You could simply have:
Employee (emp_id, name, dept_id)
Department (dept_id, dept_name)
Benefits of Removing Redundant Attributes
Improved Data Integrity: Keeps the data
accurate and consistent across the system,
reducing the chances of errors or conflicts.
Optimized Storage: Reduces the database’s
size and increases storage efficiency by
eliminating unnecessary duplication.
Easier Maintenance: Simplifies database
maintenance and updates, as changes need to
be made only in one place, reducing the risk of
discrepancies.
Better Performance: A more efficient
schema means faster query execution since
there is less data to process.
Aligns with Database Design Best Practices:
Removing redundant attributes helps align
your design with the principles of
normalization, ensuring a robust, scalable
database.
Summary
Removing redundant attributes in entity sets is
essential for creating efficient, consistent, and
easy-to-maintain database designs. By
identifying redundant data and establishing
clear relationships between entities, you
reduce duplication, improve data integrity, and
optimize your database’s storage and
performance. The process of normalization
plays a key role in this, ensuring the design
adheres to best practices in relational database
design.

Reducing ER diagram to relational schema

Converting an Entity-Relationship (ER)
diagram to a Relational Model is a crucial step
in database design. The ER model represents
the conceptual structure of a database, while
the Relational Model is a physical
representation that can be directly
implemented using a Relational Database
Management System (RDBMS) like Oracle or
MySQL. In this article, we will explore how to
convert an ER diagram to a Relational Model
for different scenarios, including binary
relationships with various cardinalities and
participation constraints.
Case 1: Binary Relationship with 1:1
cardinality with total participation of an
entity

A person has 0 or 1 passport number and

Passport is always owned by 1 person. So it is
1:1 cardinality with full participation
constraint from Passport.

First Convert each entity and relationship to

tables. Person table corresponds to Person
Entity with key as Per-Id. Similarly, Passport
table corresponds to Passport Entity with key
as Pass-No. Has Table represents relationship
between Person and Passport (Which person
has which passport). So, it will take attribute
Per-Id from Person and Pass-No from
Passport.
Case 2: Binary Relationship with n: 1
cardinality

In this scenario, every student can enroll only

in one elective course but for an elective
course there can be more than one student.
First Convert each entity and relationship to
tables. Student table corresponds to Student
Entity with key as S-Id. Similarly
Elective_Course table corresponds to
Elective_Course Entity with key as E-Id.
Enrolls Table represents relationship between
Student and Elective_Course (Which student
enrolls in which course). So, it will take
attribute S-Id from Student and E-Id from
Elective_Course.
Entity relationship design issues
Here are some of the issues that can occur
while ER diagram design process:

1. Choosing Entity Set vs Attributes

Here we will discuss how choosing an entity
set vs an attribute can change the whole ER
design semantics. To understand this lets take
an example, let’s say we have an entity set
Student with attributes such as student-name
and student-id. Now we can say that the
student-id itself can be an entity with the
attributes like student-class and student-
section.
Now if we compare the two cases we
discussed above, in the first case we can say
that the student can have only one student id,
however in the second case when we chose
student id as an entity it implied that a student
can have more than one student id.
2. Choosing Entity Set vs. Relationship Sets
It is hard to decide that an object can be best
represented by an entity set or relationship set.
To comprehend and decide the perfect choice
between these two (entity vs relationship), the
user needs to understand whether the entity
would need a new relationship if a
requirement arise in future, if this is the case
then it is better to choose entity set rather than
relationship set.
Let’s take an example to understand it better:
A person takes a loan from a bank, here we
have two entities person and bank and their
relationship is loan. This is fine until there is a
need to disburse a joint loan, in such case a
new relationship needs to be created to define
the relationship between the two individuals
who have taken joint loan. In this scenario, it
is better to choose loan as an entity set rather
than a relationship set.
3. Choosing Binary vs n-ary Relationship
Sets
In most cases, the relationships described in
an ER diagrams are binary. The n-
ary relationships are those where entity sets
are more than two, if the entity sets are only
two, their relationship can be termed as binary
relationship.
The n-ary relationships can make ER design
complex, however the good news is that we
can convert and represent any n-ary
relationship using multiple binary
relationships.
This may sound confusing so lets take an
example to understand how we can convert an
n-ary relationship to multiple binary
relationships. Now lets say we have to
describe a relationship between four family
members: father, mother, son and daughter.
This can easily be represented in forms of
multiple binary relationships, father-mother
relationship as “spouse”, son and daughter
relationship as “siblings” and father and
mother relationship with their child as “child”.
4. Placing Relationship Attributes
The cardinality ratio in DBMS can help us
determine in which scenarios we need to place
relationship attributes. It is recommended to
represent the attributes of one to one or one to
many relationship sets with any participating
entity sets rather than a relationship set.
For example, if an entity cannot be determined
as a separate entity rather it is represented by
the combination of participating entity sets. In
such case it is better to associate these entities
to many-to-many relationship sets.