Chapter-3 Data Modeling using ER Model

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 Outcome:
• 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
16 March 2021 2

Organization of this Chapter:
• Introduction
• Phases of Database Design
• A 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.
16 March 2021 3

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.
16 March 2021 4

Phases of Database Design
16 March 2021 5

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.
16 March 2021 6

– 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.
16 March 2021 7

• 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.
16 March 2021 8

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 came up with 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 a number of 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.
16 March 2021 9

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.
16 March 2021 10

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.
16 March 2021 11

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.
16 March 2021 12

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.
16 March 2021 13

contd..
» “Degree” is an attribute that only applies to those employee
who has a degree. There may be employees without a
degree. In this case, NULL value will be stored for them
against the attribute “Degree”.
• Complex Attribute: Composite and multi-valued attributes
can be nested for grouping components of a composite
attribute between ( ) with commas, and by displaying multi-
valued attributes between { }. Such attributes are known as
complex attributes.
– Ex:{Address_Phone({Phone(Area_code,Phone_number)},
Address(Street_address(Street_Name,Apartment_Number),
City,State,PIN))}
16 March 2021 14

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.
16 March 2021 15

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.
16 March 2021 16

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.
16 March 2021 17

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.
• In ER diagram, each key attribute has its name underlined
inside the oval.
• No key attribute for two individual entity from an entity set
can have same value as key attribute uniquely identifies each
entity.
16 March 2021 18

Value Set or Domain of an Attribute
16 March 2021 20
• 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, Location, Manager, and
Manager_start_date
• PROJECT (Entity Type)
– Attribute: Name, Number, Location, Controlling_dept
• DEPENDENT (Entity Type)
– Attribute: Name, gender, DOB, Employee details, Relationship
16 March 2021 21

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.
16 March 2021 23

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”.
16 March 2021 24

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.
16 March 2021 25

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.
16 March 2021 26

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.
16 March 2021 27

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 n
to 1.
16 March 2021 28

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.
16 March 2021 29

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.
16 March 2021 30

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.
16 March 2021 31

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 rectangular box, whereas weak entity type is represented
using double line rectangular box.
16 March 2021 33

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.
16 March 2021 34

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)
16 March 2021 35

contd..
– SUPERVISION:
• Entity types: EMPLOYEE
• Participation: EMPLOYEE (Partial);
– WORKS_ON:
• Cardinality: M:N
• Entity types: EMPLOYEE and PROJECT
• Participation: EMPLOYEE (Total); PROJECT (Total)
– DEPENDENTS_OF:
• Entity types: EMPLOYEE and DEPENDENT
• Participation: EMPLOYEE (Partial); DEPENDENT (Total)
16 March 2021 36

Entity Relationship Diagram
16 March 2021 37

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.
16 March 2021 38

Naming Conventions
– The naming conventions used in a 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.
16 March 2021 39

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
16 March 2021 40

Design Issues in ER Model
• The ER model usually faces following challenges:
– Use of Entity set vs. Attributes: In the real world
situations, sometimes it is difficult to select the property as
an attribute or an entity set.
• For ex: In EMPLOYEE entity type, telephone can be an attribute,
but the same can be represented as a separate entity type named
TELEPHONE.
16 March 2021 41

contd..
– Use of Entity sets vs. Relationship sets: 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 set or a relationship set.
16 March 2021 42

contd..
– Binary vs. n-ary relationship sets: Relationships in databases
are often binary. Some relationships that appear to be non-binary
could actually be better represented by several binary
relationships.
16 March 2021 43

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.
16 March 2021 44

The Flight Database
• The Flight database stores detail about an airline’s fleet,
flights, and seat bookings.
• Consider the following requirements list:
– The airline has one or more airplanes.
– An airplane has a model number, a unique registration
number, and the capacity to take one or more passengers.
– An airplane flight has a unique flight number, a departure
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 has given names, a surname, and a unique
email address.
– A passenger can book a seat on a flight.
16 March 2021 45

contd..
• Entity Types and its attributes:
– AIRPLANE
• Attributes: Model, Registration_number, Capacity
– FLIGHT
• Attributes: Flight No., From_airport, To_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, Address, Email_ID
16 March 2021 46

contd..
• Relationship Type
– Flies
• Participating entity types: AIRPLANE, FLIGHT
• Cardinality: 1:1
• Participation: AIRPLANE (Partial), FLIGHT (Total)
– HasBooking
• Participating entity types: FLIGHT, BOOKING
• Participation: FLIGHT (Total), BOOKING (Total)
– Books
• Participating entity types: PASSENGER, BOOKING
• Cardinality: N:1
• Participation: PASSENGER (Total), BOOKING (Total)
16 March 2021 47

Exercise Problems for Practice
• Please refer “Fundamentals of Database Systems” by Navathe
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.
16 March 2021 49

Chapter-3 Data Modeling using ER Model

More Related Content

What's hot

Similar to Chapter-3 Data Modeling using ER Model

More from Kunal Anand

Recently uploaded

In this document

Chapter-3 Data Modeling using ER Model