E-R MODEL

KCS-501
Dr. Monika Sainger
 Entity-Relationship Model
◦ The entity-relationship model (or ER model) is a way of graphically representing the
logical relationships of entities (or objects) in order to create a database.

◦ The entity-relationship (ER) data model allows us to describe the data involved in a real-
world enterprise in terms of objects and their relationships.

◦ ER data model is widely used to develop an initial database design.

◦ In ER modeling, the structure for a database is portrayed as a diagram, called an entity-

relationship diagram (or ER diagram), that resembles the graphical breakdown of a
sentence into its grammatical parts.
Dr. Monika Sainger KCS-501 DBMS
 Entity-Relationship Model
◦ This model consists of two concepts: entity and relationships.
◦ Entity: An entity may be defined as a thing which is recognized as being capable of an
independent existence and which can be uniquely identified.
◦ An entity is an abstraction from the complexities of some domain.
◦ When we speak of an entity we normally speak of some aspect of the real world which can be
distinguished from other aspects of the real world.
◦ An entity may be concrete, such as a person or a book, or it may be abstract, such as a loan,
or a holiday, or a concept.
◦ An entity set is a set of entities of the same type that share the same properties, or attributes.
◦ The set of all persons who are customers at a given bank, for example, can be defined as the
entity set customer.

Dr. Monika Sainger KCS-501 DBMS

 Definition of Entity
An entity can be a real-world object, either animate or
inanimate, that can be easily identifiable.
Example : School Database
Teachers Teachers
Students
Student
Classes
Courses offered Classes Courses
◦ Entity set
◦ An entity set is a collection of similar types of entities. An
entity set may contain entities with attribute sharing similar
values.
◦ An entity is represented by a rectangle in ER diagram.

Dr. Monika Sainger KCS-501 DBMS

 What are the Attributes?
◦ An entity is represented by a set of attributes.
◦ Attributes are descriptive properties possessed by each member of an
entity set. Ex: Example : Student :- Name, Class, Age
◦ An attribute for an entity set expresses that the database stores similar
information concerning each entity in the entity set; however, each entity
may have its own value for each attribute.

◦ Each entity has a value for each of its attributes represented by oval shape.
EName Roll_no. DOB

Dr. Monika Sainger KCS-501 DBMS

 Entity with Attributes

Sname

RollNo. Dept

Student

Dr. Monika Sainger KCS-501 DBMS

 Types of Attributes
◦ Simple attribute − Simple attributes are atomic values, which cannot be
divided further. For example, a student's phone number is an atomic
value of 10 digits.

◦ Single-value attribute − Single-value attributes contain single value.

example −: Social_Security_Number.

Dr. Monika Sainger KCS-501 DBMS

 Composite attribute − example, a student's complete name may have first_name
and last_name.

Last DOB

RollNo
First
Name

Student

Dr. Monika Sainger KCS-501 DBMS

Derived attribute − example, average_salary in a department should not be saved
directly in the database, instead it can be derived. For another example, age can be
derived from data_of_birth.

Last Birth_Dat
e

Roll
First Nam
e
Studen
t

Age

Dr. Monika Sainger KCS-501 DBMS

Phone_No
◦ Multi-value attribute − Multi-value attributes may contain more than one values. Example: a person
can have more than one phone number, email_address, etc.

Phone
DOB No.
Last

RollNo
First
Name

Student

Dr. Monika Sainger KCS-501 DBMS

Relationship
The association among entities is called relationship.
Ex: Employee works_at department, Student enrolls in a course.
◦ A relationship set is a set of relationships of the same type.
◦ The number of entity sets that participate in a relationship set is also
the degree of the relationship set.
◦ In ER diagrams a relationship is represented by a diamond symbol. Ex.

Works_at Enrolls_in

Dr. Monika Sainger KCS-501 DBMS

 A simple ER Model

Dr. Monika Sainger KCS-501 DBMS

Cardinality Constraints (Type of Relationships)
◦ Cardinality defines the number of entities in one entity set, which can be
associated with the number of entities of other set via relationship set.
◦ A relationship that exists between two entities X & Y, is a correspondence or
mapping between members of the two entity sets.
◦ A possible relationship that may exists between any two sets may be one of the
following:
1. One-to-one Cardinality
2. One-to-many Cardinality
3. Many-to-one Cardinality
4. Many-to-many Cardinality

Dr. Monika Sainger KCS-501 DBMS

 One-to-One Cardinality
◦ By this cardinality constraint,
◦ One entity in entity set A can be associated with at most one entity in entity set B.
◦ One entity in entity set B can be associated with at most one entity in entity set A.

Entity set A Entity set B

Dr. Monika Sainger KCS-501 DBMS

 Ex: One-to-one Relationship

1 1
Customer Having Loan

Dr. Monika Sainger KCS-501 DBMS

 One-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 at most one entity in set A.
Entity set A Entity set B

Dr. Monika Sainger KCS-501 DBMS

Ex: One-to-many Relationship

1 N
Can_issue
Student Book

Dr. Monika Sainger KCS-501 DBMS

 Many-to-One Cardinality
◦ By this cardinality constraint,
• 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.

Entity set A Entity set B

Dr. Monika Sainger KCS-501 DBMS

Ex: Many-to-one Relationship

N 1

Faculty Reporting_ in Dept

Dr. Monika Sainger KCS-501 DBMS

 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.
Entity set A Entity set B

Dr. Monika Sainger KCS-501 DBMS

Ex: Many-to-many Relationship

M N

Author Has_authored Book

Dr. Monika Sainger KCS-501 DBMS

 General Steps to create an ERD

Draw the
Identify ERD
Identify the
Identify the entity's
the Cardinality attributes
◦
Identify relation constraint
the between
entity entities
Dr. Monika Sainger KCS-501 DBMS
 A Simple Example
A company as several department. Each department has a
supervisor and at least one employee. Every supervisor has only
one department under him. Employees must be assigned to at
least one, but possibly more departments. At least one employee
is assigned to a project, but an employee may be on vacation
and not assigned to any projects. The important data fields are
the names of the departments, projects, supervisors and
employees, as well as the supervisor and employee number and a
unique project number.

Dr. Monika Sainger KCS-501 DBMS

 Identify Entities
• A company has several departments. Each department has
a supervisor and at least one employee. Every supervisor
has only one department under him. Employees must be
assigned to at least one, but possibly more departments. At
least one employee is assigned to a project, but an
employee may be on vacation and not assigned to any
projects. The important data fields are the names of the
departments, projects, supervisors and employees, as well
as the supervisor and employee number and a unique
project number.

Dr. Monika Sainger KCS-501 DBMS

 Identify Relationships
◦ A Department is assigned an employee.
◦ A Department is run by a supervisor.
◦ An employee belongs to a department.
◦ An employee works on a project.
◦ A supervisor runs a department.
◦ A project uses an employee.
Dr. Monika Sainger KCS-501 DBMS
 Start Drawing Rough ER Diagram

Dr. Monika Sainger KCS-501 DBMS

 Identify the Cardinality Constraints
• Supervisor
– Each department has one supervisor.
• Department
– Each supervisor has one department.
– Each employee can belong to one or more departments
• Employee
– Each department must have one or more employees
– Each project must have one or more employees
• Project
– Each employee can have 0 or more projects.

Dr. Monika Sainger KCS-501 DBMS

Putting Cardinality Constraints into ER Diagram

1 1

M N

Dr. Monika Sainger KCS-501 DBMS

 Identify the Entitiy’s Attributes
Department: Did, Dname
Employee: Eid, Ename
Supervisor: Sid, Sname
Project: Pid, Pname

Dr. Monika Sainger KCS-501 DBMS

 Draw the Final ER Diagram

Dname Sid
Did Sname

Pno. Pname
Eid

Ename

Dr. Monika Sainger KCS-501 DBMS

Concept of Weak Entity in ER Model
◦ Entity type that do not have key attributes of their own are called
weak entity types.
◦ In contrast, regular entity types have a key attribute which include
all the examples we discussed so far are called strong entity type.
◦ A weak entity is identified by being related to a strong entity.
◦ That strong entity is known as identifying or owner entity.
◦ And we call the relationship between a weak entity and its owner
entity, the identifying relationship of weak entity.

Dr. Monika Sainger KCS-501 DBMS

Concept of Weak Entity in ER Model
◦ For example: the dependents of an employee in an organization.
◦ Here Dependents is a weak entity associated with strong entity Employee.
◦ In ER diagrams, both a weak entity and its identifying relationship are
distinguished by surrounding the boxes and diamonds with double lines.

◦ 1 N
Employee Has Dependents

Dr. Monika Sainger KCS-501 DBMS

Generalization and Specialization
◦ The ER Model has the power of expressing database entities in a
conceptual hierarchical manner.

◦ As the hierarchy goes up, it generalizes the view of entities, and is

called Generalization.

◦ As we go deep in the hierarchy, it gives us the detail of every entity

included.

◦Going up in this structure is called Generalization Reverse is called

Specialization

Dr. Monika Sainger KCS-501 DBMS

 Example of Generalization

Sparrow Pigeon Peacock

Birds

Dr. Monika Sainger KCS-501 DBMS

 Example of Generalization

Employee

Part-time Full-time
Employee Employee

Dr. Monika Sainger KCS-501 DBMS

 Example of Specialization
Person

IS A

Student Teacher

Dr. Monika Sainger KCS-501 DBMS

 Example of Specialization
Vehicle

Generalization Specialization
IS-A

Two Four
Wheelers Wheeler

Dr. Monika Sainger KCS-501 DBMS

Aggregation: compiling information to entity

Dr. Monika Sainger KCS-501 DBMS

What are Keys in DBMS?
◦ KEYS in DBMS is an attribute or set of attributes which helps you to identify
a row(tuple) in a relation(table).

◦ Key allows you to find the relation between two tables.

`
◦ Keys help you uniquely identify a row in a table by a combination of one or
more columns in that table.
Employee ID FirstName Salary
E001 Andrew 50000
E002 Tom 60000
E003 Tom 62000

Dr. Monika Sainger KCS-501 DBMS

Types of Keys in Database Management System
• Primary Key - is a single attribute or a group of attributes in a table that
uniquely identify every row in that table. Ex. EmpId, Adhar_no., PAN etc.
• Candidate Key - is an attribute or a set of attributes that uniquely identify
tuples in a table and have a candidature to become a primary key. One of
candidate keys is chosen as primary key. Ex. EmpId, Adhar_no., PAN,
Account_no etc.
• Alternate Key - is a column or group of columns in a table that uniquely
identify every row in that table. A table can have multiple choices for a
primary key but only one can be set as the primary key. All the keys which
are not primary key are called an Alternate Key.

Dr. Monika Sainger KCS-501 DBMS

Super Key
◦ A superkey is a set of attributes within a table whose values can be used
to uniquely identify a tuple.
◦ Super-key is defined as a set of attributes of a relation such that there are
no two distinct tuples (rows) that have the same values for the attributes in
this set.
◦ A candidate key is a minimal super key.

Dr. Monika Sainger KCS-501 DBMS

English Monarchs

1. First, list out all the sets of attributes:

• {Monarch Name}
• {Monarch Number}
• {Royal House}
• {Monarch Name, Monarch Number}
• {Monarch Name, Royal House}
• {Monarch Number, Royal House}
• {Monarch Name, Monarch Number, Royal House}

Dr. Monika Sainger KCS-501 DBMS

2. Eliminate all the sets which do not meet superkey's requirement. For
example, {Monarch Name, Royal House} cannot be a superkey
because for the same attribute values (Edward, Plantagenet), there
are two distinct tuples:
(Edward, II, Plantagenet)
(Edward, III, Plantagenet)
3. Finally, after elimination, the remaining sets of attributes are the only
possible superkeys in this example:
{Monarch Name, Monarch Number} (Candidate Key)
{Monarch Name, Monarch Number, Royal House}

◦ If a relation contains 'n' attributes then maximum number of superkeys possible is 2n.
Dr. Monika Sainger KCS-501 DBMS
Reduction of ER Diagram to Database
• Each entity type becomes a table.

• Relationship having its attributes also becomes table.

Dr. Monika Sainger KCS-501 DBMS

Converting the ER diagram in Database

Employees Works_in Departments

SSn Name lot Ssn Did Since Did Dname Budget

Dr. Monika Sainger KCS-501 DBMS

Converting Weak Entity into tables
• Weak entity types are converted into a table of their own, with the
primary key of the strong entity acting as a foreign key in the table.

• This foreign key along with the key of the weak entity form the composite
primary key of this table

• Ex; key of Dependent table will be EID and Dpendentname.

Dr. Monika Sainger KCS-501 DBMS

 Converting Weak Entity into tables
Eid Ename name Age

Employee Has Dependents

Employee Dependents

Eid Ename Eid name Age

Dr. Monika Sainger KCS-501 DBMS

 Converting 1:1 Type Relationship
Cname Lno Amount
Cid
1 1
Customer Having Loan

Cid Cname Lno Amount Cid

Dr. Monika Sainger KCS-501 DBMS

 Converting 1:N Type Relationship
◦ The primary key of the relation on the “1” side of the relationship becomes a foreign key in the
relation on the “N” side
Accno type
Cname
Cid
1 N
Customer Having Account

Acc Type Cid

Cid Cname
no

Dr. Monika Sainger KCS-501 DBMS

 Converting M:N Type Relationship
◦ A new table is created to represent therelationship that contains two foreign keys - one from each of the
participants in the relationship.
Bno title
Aname
Aid
M N
Author Authored Book

Author Book
Authored
Aid Aname Bno Title
Aid Bno

Dr. Monika Sainger KCS-501 DBMS

Converting self-Referencing Relationship
• In case of self referencing relationship, the primary key field itself will
become foreign key in the same table.

• If self referencing relationship is m:n type then there will be two resulting
tables. One to represent the entity and another to represent the M:N
relationship asfollows:

Dr. Monika Sainger KCS-501 DBMS

Conversion of Attributes in ER diagram
• Each single-valued attribute becomes a column

• Derived attributes are ignored

• Composite attributes are represented by components

• Multi-valued attributes are represented by a separate table

• The key attribute of the entiry type becomes the primary key of thetable

Dr. Monika Sainger KCS-501 DBMS

Dr. Monika Sainger KCS-501 DBMS