Learning Resource

On
Database Management Systems

Chapter-3
Data Modeling using Entity Relationship Model

Prepared By:
Kunal Anand, Asst. Professor
SCE, KIIT, DU, Bhubaneswar-24
 Chapter Outcomes

• After the completion of this chapter, the students

will be able to:
– Explain different phases of Database Design
– Describe several terminologies associated with
ER Model
– Describe different relationship types, its
cardinality, and constraints.
– Draw ER diagram for any database application
– List out design issues in ER Model
 Organization of this Chapter

• Introduction
• Phases of Database Design
• Sample Database Application
• Entity types, Entity, Entity sets, and Attributes
• Relationship types, Relationship sets, Roles and
Structure Constraints
• ER Diagrams, Naming Conventions, Design issues
• Exercise problems for practice.
 Introduction
• A Database application refers to a particular database and
associated programs that implement the database queries and
updates.
– For Example: A bank database application keeps track of
customer accounts using database updates corresponding to
customer withdrawals and deposits.

• These programs provide a user-friendly interface like menu

based, form based, and GUI to its users so that they can
perform the operations to meet their requirements.

• Hence, a part of database application also require the design,

implementation, and testing of these programs.
Phases of Database Design
 Phases of Database Design (contd..)
• Requirement Collection and Analysis
– During this phase, the database designer talks to all the
possible users to understand and document their
requirements.
– These requirements are analyzed in detail to specify the
data and functional requirements for the users.
– This must be done very carefully as all the future phases
depend on the correctness of this phase.

• Conceptual Design
– Next, by applying a high-level data model like ER Model,
the designer translates these requirements into a conceptual
schema of the database.
 Phases of Database Design (contd..)
– The designer reviews the schema to confirm that all data
requirements are satisfied and are not in conflict with one
another.

• Logical Design
– The next step, known as logical design, is the actual
implementation of the database using any DBMS.
– Here, using an implementational data model like relational
model, conceptual schema is transformed into logical
schema. This is known as Data Model Mapping.
 Phases of Database Design (contd..)
• Internal Design
– Once the logical schema is obtained the last phase, known
as internal design, is performed.
– During this phase, the internal storage structures, file
organizations, indexes, access paths, and physical design
parameters for the database files are specified.

• Development of Application Programs

– In parallel with these activities, application programs are
designed and implemented.
Note: The phases before logical design are DBMS
independent whereas, the afterward phases are DBMS
specific.
 Sample Database Application
• A database application, called COMPANY, keeps track of its
EMPLOYEEs, DEPARTMENTs, and PROJECTs.

• After the requirement collection and analysis phase, the

designers produced the following description of the database
COMPANY.
– The company is organized into departments. Each
department has a unique name, a unique number, and an
employee that manages the department.
– A department controls several projects, each of which has a
unique name, unique number, and a location.
– The database stores each Employee Name, Employee ID,
SSN, age, address, gender, salary, DOB, and supervisor.
 Sample Database Application
– An employee is assigned to one department, but may work
on multiple projects, which are not necessarily controlled
by the same department.

– The database also keeps the track of current number of

hours per week that an employee works on each project,
along with the direct supervisor of each employee.

– The database also records the dependents of each employee

for insurance purpose, including each dependent's first
name, gender, DOB, and relationship to the employee.
 Entity Type and Attribute
• Entity Type
– It is a basic object represented in a ER model.
– An entity type represents any real world object that can be
represented in a database.
• Attributes
– Each entity type can be described into a database with the
help of some features or properties or characteristics
known as attributes.
• For example: EMPLOYEE is an entity type with
attributes like employee's name, Emp_ID, SSN, address,
gender, salary, and DOB.
– A particular entity in an entity type will have value for each
of its attributes.
 Types of Attributes
• In ER Model, following types of attributes exist:
– Simple versus Composite: Attributes that can't be sub-
divided further are known as simple attribute, whereas the
composite attributes can be sub-divided further.
• Ex: “SSN” attribute can't be sub-divided further
whereas “Address” attribute of EMPLOYEE can be
divided further into Street_address, City, State, and PIN.
– Single-valued versus Multi-valued: Attribute which have
single value for a particular entity is known as single-
valued attribute, whereas attribute which may have a set of
values for the same entity is known as multi-valued
attribute.
• Ex: “Age” is a single-valued attribute whereas “Mobile
Number” is a multi-valued attribute.
 contd..
• Stored versus Derived: Attribute for which the values are
directly stored, is known as stored attribute. Similarly, there
are attributes for which direct values can not be stored, rather
the values are derived from the stored attributes. They are
known as derived attributes.
– For ex: “DOB” is the attribute that will be stored directly
for an employee. On the other hand, “Age” is a derived
attribute as it will be derived from the DOB attribute.
• NULL Values:
• In some cases, a particular entity may not have any
applicable value for an attribute. In this situation, a special
value NULL is created. It can also be used when the value
of an attribute is unknown.
 Entity Types and Entity Sets
• Entity Type
– It is a collection of entities that have the same attributes.
However, each entity has its own value for each attribute.
– In a database, each entity type is represented by its name
and attributes.
– Ex: EMPLOYEE is an entity type with attributes Emp_ID,
Name, Age, Salary, etc.

• Entity Set
– The collection of all entities of a particular entity type in
the database at any point of time is referred to as entity set.
– Entity set is also referred by the same name as the entity
type.
 contd..

• Entity type and entity set are

represented here.

• An entity type is represented

in ER diagram as a
“Rectangular Box” enclosing
the entity type name.

• Attributes are enclosed in

ovals and are attached to
their entity with straight
lines.
 contd..

• Composite attributes are

connected to their
components by straight
lines.
• Multi-valued attributes
are displayed using double
oval.
• Derived attribute is
represented as dashed oval.
• A STUDENT entity type is
shown here along with its
attributes.
 Key Attribute of an Entity Type
• An entity type usually have one or more attributes whose
values are distinct for each individual entity in the entity set.
Such attribute is known as Key Attribute.
• Key attributes can be used to identify each entity uniquely.
– For example: Consider an entity type CAR with attributes
like Model, Make, Color, Year, Reg., Vehicle_ID. Here,
Reg., and Vehicle_ID can be the key attribute.
• Key attribute for two individual entities from an entity set
can’t have same value as key attribute uniquely identifies each
entity.
• In ER diagram, each key attribute has its name underlined
inside the oval.
contd..
 Value Set or Domain of an Attribute
• Each simple attribute of an entity type is associated with a
value set that specifies the set of values which may be
assigned to that attribute for each individual entity.
• For ex:
– Age attribute of an entity type EMPLOYEE may have the
range between 21 to 65. Hence, the value set here is 21 to 65
for the attribute Age.
– Similarly, DOJ or DOB is an attribute where the values will
be given from a pre-defined set like {Day, Month, Year}.
Hence, the value set can be {(1-31),(1-12),(1950-2020)}.
• Value set is not displayed in the ER diagram and are typically
specified using the basic data type available in most
programming language.
 Initial Conceptual Design of COMPANY database

• Based on the requirements discussed in our sample problem,

following entity types can be defined along with their
attributes.
• EMPLOYEE (Entity Type)
– Attribute: Emp_name, Emp_ID, SSN, age, address,
gender, salary, DOB, and supervisor
• DEPARTMENT (Entity Type)
– Attribute: Name, Number, and Manager
• PROJECT (Entity Type)
– Attribute: Name, Number, Location, and Controlling_dept
• DEPENDENT (Entity Type)
– Attribute: Name, gender, DOB, Employee details, and
Relationship with the employee
 Relationship Types, Relationship Sets, Roles, and
Structural Constraints
• Multiple entity types are associated to each other in some form
of relationship. This association is known as relationship
type. The relationship name is a verb.

• A relationship set is a set of relationship instances of the same

type. Relationship sets are represented by diamonds and are
connected to the participant entity sets.
 contd..
• A relationship type R among
n entity types e1, e2,
e3,....,en defines a set of
relationship set among
entities.
– For ex: EMPLOYEE
works for
DEPARTMENT; here,
the entity type
EMPLOYEE is
associated with the entity
type DEPARTMENT and
the relationship type is
“Works for”.
 contd..

• Each relationship instance ri is connected to the EMPLOYEE

and DEPARTMENT entity types.
• The employees e1,e3, and e6 work for department d1; the
employees e2 and e4 works for department d2; and the
employees e5 and e7 work for department d3.
• The degree of a relationship type is the number of entity types
that participate in a relationship. For example: The degree of
“Works for” relationship type is 2.
• A relationship type of degree 2 is called binary relationship
whereas, the relationship type of degree 3 is known as ternary
relationship.
 contd..
• Each entity type participating in a relationship type plays a
role in that relationship.
– For ex: In works for relationship EMPLOYEE plays the
role of worker and DEPARTMENT plays the role of
employer.
• Recursive Relationship: Role names are significant where
the same entity type participates more than once in a
relationship, but in different roles. Such relationship types are
known as recursive relationship or self referencing
relationship.
– For ex: each employee has a supervisor which is again an
employee i.e. The EMPLOYEE entity type is associated to
this “supervises” relationship in two roles, one as a
supervisor and another as Subordinate.
 Constraints in Relationship
• Mapping Constraint or Cardinality: The 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 (1:1): When each entity in each entity set can
take part only once in the relationship, the cardinality is
one to one.
 contd..
• Many to one (M:1) When entities in one entity set can take
part only once in the relationship set and entities in other
entity set 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
M: 1.
 contd..
• Many to many (M:N) – 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:N).
 contd..
• One to Many (1:M)- An entity in A is associated with any
number (zero or more) of entities in B; an entity in B,
however, is associated with no more than 1 entity of A.
– For example: A customer can have multiple loans.
However, a loan can only have one customer. Hence, its a
1:M relationship.
 Participation Constraints
• Participation Constraint is applied on the entity participating in
the relationship set.
• Total Participation: Each entity in the entity set must
participate in the relationship. If each student must enroll in a
course, the participation of student will be total. Total
participation is shown by “double line” in ER diagram.
• Partial Participation: The entity in the entity set may or may
not participate in the relationship. If some courses are not
enrolled by any of the student, the participation of course will
be partial. It is shown by “single line”.
– The diagram depicts the ‘Enrolled in’ relationship set with
STUDENT Entity set having total participation and
COURSE Entity set having partial participation.
contd..
 Strong and Weak Entity Type
• An entity type that has its own KEY attribute, is known as
strong entity type.
– Ex: In COMPANY database, the entity types EMPLOYEE,
DEPARTMENT, and PROJECT are strong as they do have
their own key attribute.
• On the other hand, Weak entity type does not have their own
KEY attribute.
– Ex: In COMPANY database, the DEPENDENT entity type
is a weak entity type as it does not have a key attribute of its
own. It has a partial key which may be used to uniquely
identify the weak entity that are related to the same owner
entity.
• In ER diagram, strong entity type is represented using a single
line rectangle, whereas weak entity type is represented using
double line rectangle.
 contd..
• For a weak entity set to be meaningful, it must be associated
with another strong entity set called identifying or owner
entity set.

• The relationship associating the weak entity set with the

identifying entity set is called the identifying relationship.

• The identifying relationship is usually many-to-one and the

participation of the weak entity set in the relationship is total
participation.
– Ex: DEPENDENT entity type is related to the
EMPLOYEE entity type using M:1 relationship type and
the participation of the weak entity set is total.
Refined Conceptual Design for COMPANY database
• Now, the refined ER design for the COMPANY database is
below:
– MANAGES:
• Cardinality: 1:1
• Entity types: EMPLOYEE and DEPARTMENT
• Participation: EMPLOYEE (Partial); DEPARTMENT (Total)
– WORKS_FOR:
• Cardinality: N:1
• Entity types: EMPLOYEE and DEPARTMENT
• Participation: EMPLOYEE (Total); DEPARTMENT (Total)
– CONTROLS:
• Cardinality: 1:N
• Entity types: DEPARTMENT and PROJECT
• Participation: DEPARTMENT (Partial); PROJECT (Total)
 contd..

– SUPERVISION:
• Cardinality: 1:N
• Entity types: EMPLOYEE
• Participation: EMPLOYEE (Partial);
– WORKS_ON:
• Cardinality: M:N
• Entity types: EMPLOYEE and PROJECT
• Participation: EMPLOYEE (Total); PROJECT (Total)
– DEPENDENTS_OF:
• Cardinality: 1:N
• Entity types: EMPLOYEE and DEPENDENT
• Participation: EMPLOYEE (Partial); DEPENDENT (Total)
Entity Relationship Diagram
 ER Diagram Notations
• In ER diagram, the
emphasis is on to represent
the schema rather than
instances.

• This is because the schema

changes rarely, but the
instances keep on changing.

• The different symbols used

in ER diagram is as shown
in this diagram.
 Naming Conventions

–The naming conventions used in an ER diagram is as below:

• Entity types are represented in uppercase letters.
• Attributes are written using Initial letter in uppercase and
remaining in lowercase.
• Roles of entity types in a relationship type is written in
lowercase letters.
• Relationship types are written using verbs.
• Binary relationship is readable from left to right or top to
bottom.
 ER Design Methodology
•The guidelines that should be followed while designing an ER
diagram are discussed below:
• Recognize entity types
• Recognize relationship types and participating entity types.
• Recognize attributes of entity type and attributes of
relationship types.
• Define binary relationship types and existence
dependencies.
• Define general cardinality, constraints, keys, and
discriminators (for weak entity type).
• Design diagram
 Design Issues in ER Model
• The ER model usually faces following challenges:
– Use of Entity type vs. Attributes: In the real-world
situations, sometimes it is difficult to select the property as
an attribute or an entity type.
• For ex: In EMPLOYEE entity type, telephone can be an attribute,
but the same can be represented as a separate entity type named
TELEPHONE.
 contd..
– Use of Entity type vs. Relationship type: Sometimes, an
entity set can be better expressed in relationship set. Thus,
it is not always clear whether an object is best expressed by
an entity type or a relationship type.
 contd..
–Binary vs. n-ary relationship sets: Relationships in databases
are often binary. Some relationships that appear to be non-binary
could be better represented by several binary relationships.
 contd..
•Placement of Relationship Attributes: The cardinality ratio of
a relationship can affect the placement of relationship attributes.
• One-to-Many: Attributes of 1:M relationship set can be
repositioned to only the entity set on the many side of the
relationship.

• One-to-One: The relationship attribute can be associated with

either one of the participating entities

• Many-to-Many: Here, the relationship attributes can not be

represented to the entity sets; rather they will be represented by
the entity set to be created for the relationship set.
 The Flight Database
• The Flight database stores detail about an airline’s fleet,
flights, and seat bookings.
• Consider the following requirements list:
– An airplane has a model number, a unique registration number,
and the capacity to carry one or more passengers.
– An airplane flies flight where each flight has a unique flight
number, a source airport, a destination airport, a departure date
and time, and an arrival date and time. Each flight is carried out
by a single airplane.
– A passenger having name that is stored in the First Name and
Surname manner, unique email, can make at most one booking.
– A booking has booking_id, booking date, source city, destination
city, and booked_by details.
– Each flight has many bookings, but each booking can be
assigned to only one flight.
 contd..
• Entity Types and its attributes:
– AIRPLANE
• Attributes: Model, Registration_number, Capacity
– FLIGHT
• Attributes: Flight No., Source_airport,
Destination_airport, Dep_date, Dep_time, Arr_date,
Arr_time
– BOOKING
• Attributes: Booking_number, Booking_date,
From_city, To_city, Booking_by
– PASSENGER
• Attributes: Passenger_name (First name, Surname),
Email_ID
 contd..
• Relationship Type
– Flies
• Participating entity types: AIRPLANE, FLIGHT
• Cardinality: 1:1
• Participation: AIRPLANE (Partial), FLIGHT (Total)

– HasBooking
• Participating entity types: FLIGHT, BOOKING
• Cardinality: 1:N
• Participation: FLIGHT (Total), BOOKING (Total)
 contd..
• Relationship Type
– Books
• Participating entity types: PASSENGER, BOOKING
• Cardinality: N:1
• Participation: PASSENGER (Total), BOOKING
(Total)

– Carries
• Participating entity types: AIRPLANE, PASSENGER
• Cardinality: 1:M
• Participation: AIRPLANE (Partial), PASSENGER
(Total)
ER Diagram
 Exercise Problems for Practice
• Please refer “Fundamentals of Database Systems” by Navathe;
7th Edition for the given problems.
– Please refer UNIVERSITY database application from
section 3.10; pp. 92-94
– Please go through the Q. 3.23 BANK database application;
pp. 99 and answer the questions.
– Repeat the questions of 3.23 for the ER diagram of “Airline
database schema”; pp. 98.
– Refer the Q. 3.28 MOVIE database and answer the
questions by referring to the given ER diagram; pp. 101
– Draw the ER diagrams for the Q. 3.32, Q. 3.33, and Q.
3.34; pp. 103-104.