UNIT 3 ENTITY RELATIONSHIP MODEL
3.0 Introduction
3.1 Objective
3.2 Entity Relationship (E-R) Model
3.2.1 Entities
3.2.2 Attributes
3.2.3 Relationships
3.2.4 E-R diagram Basics
3.2.5 More about Relationships
3.2.6 Extended E-R Features
3.2.7 Defining Relationship for College Database
3.3 An Example
3.4 Conversion of E-R Diagram to Relational Database
3.5 Enhanced E-R Model
3.6 Converting E-R and EER Diagram to Relations
3.7 Summary
3.8 Solution/Answers
3.0 INTRODUCTION
In the previous unit of this block, you have gone through the concept of relational
database management systems and one of the important languages for relational
database – relational algebra. This unit explains, you about of an analysis model of
the database system, known as Entity-Relationship (E-R) model. The E-R model is a
widely used model for analysis of data requirements of an organisation. The E-R
model is primarily a semantic model and is very useful in creating raw database
design that can be further normalised. With the availability of object-oriented
technologies, the E-R model has been extended to include object-oriented features.
This unit also discusses these E-R extensions. We will also discuss the conversion of
E-R diagrams to tables, in this unit.
3.1 OBJECTIVES
After going through this unit, you should be able to:
• Define and explain various components of E-R model;
• draw an E-R diagram for a given problem;
• convert an E-R diagram to a relational database;
• Explain the role of extended features of E-R model;
• Convert an EER diagram to relations.
,3.2 ENTITY RELATIONSHIP (E-R) MODEL
Some of the important characteristics of E-R model are listed below:
• Entity relationship model is a high-level conceptual data model.
• It allows you to describe the data involved in a real-world enterprise in terms of
entities and their relationships.
• It is widely used to create an initial design of a database.
• It provides a set of useful concepts that make it convenient for a developer to
move from a basic set of information to a detailed and precise description of
information that can be easily implemented in a database system.
• It describes data as a collection of entities, relationships and attributes.
In the following sections, we explain the basic terms used in the E-R model.
3.2.1 Entities
First let us answer the question: What are entities?
• An entity is an object of concern, which is used to represent the things in the real
world, e.g., car, table, book, etc.
• An entity need not be a physical entity, it can also represent a concept in the real
world, e.g., project, loan, etc.
• It represents a class of things, not any one instance, e.g., ‘STUDENT’ entity has
instances of ‘Ramesh’ and ‘Mohan’.
Entity Set or Entity Type: A collection of a similar kind of entity is called an
Entity Set or entity type.
For the COLLEGE database, the objects of concern are Student, Faculty, Course
and Department. The collections of all the students’ entities form an entity set
STUDENT. Similarly, collection of all the courses form an entity set COURSE.
You may please note that entity sets need not be disjoint. For example – an entity,
say Mohan, may be part of the entity set STUDENT, the entity set FACULTY and
the entity set PERSON.
Entity identifier - key attributes: An entity set usually has one or more attributes,
which attains unique value for every distinct entity in a given entity set. Such an
attribute or set of attributes is/are called key attribute(s) and its values can be used to
identify each entity uniquely in the given entity set.
Strong entity set: An entity set which contains at least one key attribute is a Strong
entity set. For example, a Student entity set would contain at least one key attribute
Enrolment number, which is unique for every student, thus, the entity set Student is
a strong entity set.
Weak entity set: Entity sets that do not contain any key attribute, and hence cannot
be identified independently, are called weak entity sets. A weak entity cannot be
identified uniquely by its attributes, therefore, are recognised in conjunction with the
, primary key attributes of another strong entity on which its existence is dependent
(called owner entity set).
Generally, a primary key of an owner entity set is attached to a weak entity set,
which has identifying attributes, called discriminator attributes. These two together
form the primary key of the weak entity set. The following restrictions must hold for
the above:
• The owner entity set and the weak entity set must participate in one to many
relationship set. This relationship set is called the identifying relationship
set of the weak entity set.
• The weak entity set must have total participation in the identifying
relationship.
One of the most common examples about the weak entity set is an entity set
Dependent and the related Strong entity set Employee in an organisation. The
Dependent entity set is used to list all the dependents of each employee of an
organisation. The attributes of the Dependent entity set are: Dependent name, birth
date, gender and relationship with the employee. Each Employee entity is said to
own the dependent entities that are related to it. However, please note that the
‘Dependent’ entity does not exist of its own, it is dependent on the Employee entity.
In other words, you can say that in case an employee leaves the organization, all
dependents related to him/her also leave along with this employee. Thus, a
‘Dependent’ entity has no significance without the entity ‘Employee’. Thus, it is a
weak entity.
3.2.2 Attributes
Let us first define - What is an attribute?
An attribute is an element of an entity, which can contain a representative value. In
other words, an entity is represented by a set of attributes.
For example, a Student entity set may consist of attributes - Roll no, student’s name,
age, address, course, etc. An entity will have a value for each of its attributes. For
example, for a particular student, the following values can be assigned:
Roll No: 1234
Name: Mohan
Age: 18
Address: Z-894, Maidan Garhi, Delhi.
Course: B.Sc. (H)
Domains: Each simple attribute of an entity type contains a possible set of values
that can be attached to it. This is called the domain of an attribute. An attribute
cannot contain a value outside this domain.
EXAMPLE- for STUDENT entity Age has a specific domain, integer values say
from 15 to 90.
3.0 Introduction
3.1 Objective
3.2 Entity Relationship (E-R) Model
3.2.1 Entities
3.2.2 Attributes
3.2.3 Relationships
3.2.4 E-R diagram Basics
3.2.5 More about Relationships
3.2.6 Extended E-R Features
3.2.7 Defining Relationship for College Database
3.3 An Example
3.4 Conversion of E-R Diagram to Relational Database
3.5 Enhanced E-R Model
3.6 Converting E-R and EER Diagram to Relations
3.7 Summary
3.8 Solution/Answers
3.0 INTRODUCTION
In the previous unit of this block, you have gone through the concept of relational
database management systems and one of the important languages for relational
database – relational algebra. This unit explains, you about of an analysis model of
the database system, known as Entity-Relationship (E-R) model. The E-R model is a
widely used model for analysis of data requirements of an organisation. The E-R
model is primarily a semantic model and is very useful in creating raw database
design that can be further normalised. With the availability of object-oriented
technologies, the E-R model has been extended to include object-oriented features.
This unit also discusses these E-R extensions. We will also discuss the conversion of
E-R diagrams to tables, in this unit.
3.1 OBJECTIVES
After going through this unit, you should be able to:
• Define and explain various components of E-R model;
• draw an E-R diagram for a given problem;
• convert an E-R diagram to a relational database;
• Explain the role of extended features of E-R model;
• Convert an EER diagram to relations.
,3.2 ENTITY RELATIONSHIP (E-R) MODEL
Some of the important characteristics of E-R model are listed below:
• Entity relationship model is a high-level conceptual data model.
• It allows you to describe the data involved in a real-world enterprise in terms of
entities and their relationships.
• It is widely used to create an initial design of a database.
• It provides a set of useful concepts that make it convenient for a developer to
move from a basic set of information to a detailed and precise description of
information that can be easily implemented in a database system.
• It describes data as a collection of entities, relationships and attributes.
In the following sections, we explain the basic terms used in the E-R model.
3.2.1 Entities
First let us answer the question: What are entities?
• An entity is an object of concern, which is used to represent the things in the real
world, e.g., car, table, book, etc.
• An entity need not be a physical entity, it can also represent a concept in the real
world, e.g., project, loan, etc.
• It represents a class of things, not any one instance, e.g., ‘STUDENT’ entity has
instances of ‘Ramesh’ and ‘Mohan’.
Entity Set or Entity Type: A collection of a similar kind of entity is called an
Entity Set or entity type.
For the COLLEGE database, the objects of concern are Student, Faculty, Course
and Department. The collections of all the students’ entities form an entity set
STUDENT. Similarly, collection of all the courses form an entity set COURSE.
You may please note that entity sets need not be disjoint. For example – an entity,
say Mohan, may be part of the entity set STUDENT, the entity set FACULTY and
the entity set PERSON.
Entity identifier - key attributes: An entity set usually has one or more attributes,
which attains unique value for every distinct entity in a given entity set. Such an
attribute or set of attributes is/are called key attribute(s) and its values can be used to
identify each entity uniquely in the given entity set.
Strong entity set: An entity set which contains at least one key attribute is a Strong
entity set. For example, a Student entity set would contain at least one key attribute
Enrolment number, which is unique for every student, thus, the entity set Student is
a strong entity set.
Weak entity set: Entity sets that do not contain any key attribute, and hence cannot
be identified independently, are called weak entity sets. A weak entity cannot be
identified uniquely by its attributes, therefore, are recognised in conjunction with the
, primary key attributes of another strong entity on which its existence is dependent
(called owner entity set).
Generally, a primary key of an owner entity set is attached to a weak entity set,
which has identifying attributes, called discriminator attributes. These two together
form the primary key of the weak entity set. The following restrictions must hold for
the above:
• The owner entity set and the weak entity set must participate in one to many
relationship set. This relationship set is called the identifying relationship
set of the weak entity set.
• The weak entity set must have total participation in the identifying
relationship.
One of the most common examples about the weak entity set is an entity set
Dependent and the related Strong entity set Employee in an organisation. The
Dependent entity set is used to list all the dependents of each employee of an
organisation. The attributes of the Dependent entity set are: Dependent name, birth
date, gender and relationship with the employee. Each Employee entity is said to
own the dependent entities that are related to it. However, please note that the
‘Dependent’ entity does not exist of its own, it is dependent on the Employee entity.
In other words, you can say that in case an employee leaves the organization, all
dependents related to him/her also leave along with this employee. Thus, a
‘Dependent’ entity has no significance without the entity ‘Employee’. Thus, it is a
weak entity.
3.2.2 Attributes
Let us first define - What is an attribute?
An attribute is an element of an entity, which can contain a representative value. In
other words, an entity is represented by a set of attributes.
For example, a Student entity set may consist of attributes - Roll no, student’s name,
age, address, course, etc. An entity will have a value for each of its attributes. For
example, for a particular student, the following values can be assigned:
Roll No: 1234
Name: Mohan
Age: 18
Address: Z-894, Maidan Garhi, Delhi.
Course: B.Sc. (H)
Domains: Each simple attribute of an entity type contains a possible set of values
that can be attached to it. This is called the domain of an attribute. An attribute
cannot contain a value outside this domain.
EXAMPLE- for STUDENT entity Age has a specific domain, integer values say
from 15 to 90.
