Data Base Management System

Chapter at a Glance
Introduction to ER Model
Entity
Entity Type
Entity Instance
Entity Set
Attribute and its Type

Single Valued Attribute

Multi Value Attribute

Composite Attribute

Atomic Attribute

Derived Attribute

Stored Attribute

Relationship

Degree of Relationship
o Unary Relationship
o Binary Relationship
o Ternary Relationship

Cardinality of Relationship
o One to One
o One to Many
o Many to One
o Many to Many

Concept of Key

Super Key

Candidate Key

Primary Key

Introduction to ER Diagram and Useful Notations

Role and Participations

o Recursive Relation
Authored By: Prof. Soumik Das

Data Base Management System

o Participation Constraints

Cardinality Constraints Revisited

Degree of Relations Revisited

Multiple Relationship

Attribute of a Relationship

Enhanced Features of ER Modeling

Specialization & Generalization

o Subclass
o IS-A relationship
o Attribute Inheritance
o Multiple Specialization
o Multi Level Specialization

Constraints on Specialization & Generalization

o Overlapping Constraint
o Covering Constraint

Aggregation

Solved Assignments
Exercises

Authored By: Prof. Soumik Das

Data Base Management System

CHAPTER 2
Entity Relationship Model (ER Model)

Introduction
Entity-relationship modeling is a high-level data modeling technique that helps
designers to create appropriate, accurate and useful conceptual models. E-R
models are best expressed using E-R diagrams (a graphical representation). In
the year of 1976 Professor Peter Chen has invented this technique to serve it as
a tool for useful communication between database designers and database
users. It is recognized the problems that are caused when developers and users
fail to understand each other. It is typical for developers and users to think that
they each know exactly what the other is thinking. Otherwise it leads
misunderstanding; sometimes developers build information systems that do not
meet user needs. The result is the failure of the system that was developed.

E-R diagrams provide a visual, graphical model of the information content of a

system. Developers create E-R diagrams that represent their view of user
requirements. Users then carefully evaluate and check the E-R diagrams to
make sure that their needs are being met or not. Once the E-R diagram has been
approved by the user community, the diagram provides the specification of what
must be accomplished by the developers. In the presence of accurate models,
developers can be confident that they are building useful systems.

Authored By: Prof. Soumik Das

Data Base Management System

The key features of ER Modeling at a glance:

o It is simple and easily understood by non-specialists.
o It is easily conceptualized; the basic constructs (entities and relationships)
are highly intuitive and thus provide a very natural way of representing a
user's information requirements.
o It is a model that describes a world in terms of entities and attributes that
is most suitable for computer-nave end users.

2.1 Entities, Attributes & Relationships

The Entity Relationship (ER) model is a method based on which database
systems can be built by considering data as entities, relationships and attributes.
The object, depending on which ER model is developed, is an entity that has an
independent existence. Every entity is described through certain properties called
attributes. And by which two or more entities are related among each other is
called relationship.

2.1.1 Entity
Definition: An entity is an object that exists in real world with independent
existence having certain properties and distinguishable from other objects.

For example, Samir Das is a student of BCA stream. We can say he is an entity
because he has certain properties like name, roll, year, class by which he is
distinguishable from other students. Similarly Dipak Sharma, an account holder
in Axis Bank is an entity because he has a name, a/c number, a/c opening date
by which he is distinguishable from other account holders.
Authored By: Prof. Soumik Das

Data Base Management System

2.1.2 Entity Type

Definition: Entity Type is an abstraction, defining the properties of a similar set
of entities.

For example, student is an entity type and account holder is another entity type.
If Samir Das and Dipak Sharma both are BCA students then they will be having
some common properties.

2.1.3 Entity Instances

Definition: Initiation of entity types is called entity instances.

For example, if Samir Das is an account holder in Axis Bank and a BCA student
as well then he becomes an entity instance of student and account holder entity
type.

2.1.4 Entity Set

Definition: A set of same type of entity instances called the entity set.

For example, all account holders of Axis Bank conforms an entity set. In the
same way we can say all the BCA student is an entity set.

2.1.5 Attributes
Definition: Entities are having certain properties by which they exist
independently in the real world called attributes.

Authored By: Prof. Soumik Das

Data Base Management System

For example, jersey number, team name are attributes of a player entity; ISBN
number, author, publisher, title are the attributes of a book entity; roll, name,
stream, year are the attributes of a student entity. Different type of attribute is
discussed hereinafter.

Single-valued Attribute:

If a certain value is possible for a given attribute, we can say that is a

single valued attribute.
For example, roll; account number can not contain more than one value.

Multi-valued Attribute:

If more than one value is possible for a given attribute, we can say that is
a multi valued attribute.
For example, favorite food, languages known may have more than one
value.

Simple / Atomic Attribute:

An attribute that can not be divided is called simple or atomic attribute.

.

For example, roll number, account number are the atomic attributes
because they can not be divided any further.

Composite Attribute:

An attribute which can be divided in smaller sub parts is called composite

attribute.
For example, address is a composite attribute because it can be further
divided in to house number, street name, district, city and PIN.

Derived Attribute:

An attribute that we can derive from other attribute is called derived

attribute.

Authored By: Prof. Soumik Das

Data Base Management System

For example, age is a derived attribute because it can be derived from
DOB (Date of Birth). Similarly rank is a derived attribute from marks.

Stored Attribute:

An attribute from where the derived attributes are derived. For example,
age is derived from DOB, so DOB is the stored attribute.

2.1.6 Relationships
While entity types describe independent artifacts, relationship types describe
meaningful associations between entity types. To be very precise, the
relationship type describes that entities of entity types participating in the
relationship can build a meaningful association. The actual occurrence of the
association between entities is called a relationship. It is important to understand
that although we defined a relationship type, this does not mean that every pair
of entities builds a relationship. A relationship type defines only that relationships
can occur.

For example teacher entity type is associated with student entity type with
teaches relation in such a way teacher teaches student. So we can say these
two entity type teacher and student builds a meaningful association with the
relation teaches. On the other hand student entity type associated with
examination entity type with sit for relation in a way student sit for examination.
But teacher entity type is not associated with examination, because if they
associated no meaningful association will be made.

2.1.6.1 Degree of Relationships

The number of entity sets that participate in a relationship is called the degree of
relationship. The three most common degrees of a relationship in a database are
unary (degree 1), binary (degree 2), and ternary (degree 3). We will briefly define
Authored By: Prof. Soumik Das

Data Base Management System

these degrees and then explore each kind of relationship in detail in subsequent
sections.
Let E1, E2, . . . , En denote n entity sets and let R be the relationship. The
degree of the relationship can also be expressed as follows:

Unary Relationship:

A unary relationship R is an association between two instances of the

same entity type (i.e., R E1 E1). For example, two students are
roommates and stay together in an apartment. Because they share the
same address, a unary relationship exists between them for the attribute
Address.

Binary Relationship:

A binary relationship R is an association between two instances of two

different entity types (i.e., R E1 E2). For example, in a university, a
binary relationship exists between a student (STUDENT entity) and an
instructor (FACULTY entity) of a single class; an instructor teaches a
student.

Ternary Relationship:

A ternary relationship R is an association between three instances of three

different entity types (i.e., R E1 E2 E3). For example, consider a
student using certain equipment for a project. In this case, the STUDENT,
PROJECT, and EQUIPMENT entity types relate to each other with ternary
relationships: a student checks out equipment for a project.

3.7
2.1.6.2 Cardinality of Relationships:

The cardinality of relationships can be defined as the actual number of related

occurrences for each of the two entities A and B (that means how many numbers
of instances of A is associated with how many numbers of instances of B). Then
the maximum cardinality represents the maximum number of instances of entity
Authored By: Prof. Soumik Das

Data Base Management System

B that can be associated with any instance of entity A. Figure 2.1 illustrates
possible cardinalities of relationships.

One-to-One Relationship:
In a one-to-one relationship, at most one instance of entity B can be
associated with a given instance of entity A and vice versa.

Figure 2.1

Four types of cardinality between A and B

One-to-Many Relationship:
In a one-to-many relationship, many instances of entity B can be
associated with a given instance of entity A. However, only one instance
of entity A can be associated with a given instance of entity B. For
example, while a customer of a company can make many orders, an order
can only be related to a single customer.

Many-to-One Relationship:
In a many-to-one relationship, one instance of entity B can be associated
with a given instance of entity A. However, many instances of entity A can
be associated with a given instance of entity B.
Authored By: Prof. Soumik Das

Data Base Management System

Many-to-Many Relationship:
In a many-to-many relationship, many instances of entity A can be
associated with a given instance of entity B, and, likewise, many instances
of entity B can be associated with a given instance of entity A. For
example, a machine may have different parts, while each individual part
may be used in different machines.

2.2 Keys
A key attribute is a single attribute or a combination of attributes that uniquely
identify an individual instance of an entity type. No two instances within an entity
set can have the same key attribute value. For the STUDENT entity roll is the key
attribute since each student roll number is unique. Name, by contrast, cannot be
an identifier because two students can have the same name.

Sometimes no single attribute can uniquely identify an instance of an entity type.

However, in these circumstances, we identify a set of attributes that, when
combined, is unique for each entity instance. In this case the key attribute, also
known as composite key.

Super Key:

A super key is a set of one or more attributes which taken collectively,

allows us to identify uniquely an entity in the entity set.

For example for the entity set student roll and name is a super key. The
name alone is not a super key because two students can have the same
name. Note that an entity set can have more than one super key.

Candidate Key:

Candidate key is a super key whose no proper subset is a super key. In

other words we can also say the minimal super key is the candidate key.
Authored By: Prof. Soumik Das

Data Base Management System

For example roll and name can jointly identify a student uniquely. But roll
alone can also distinguish a student so roll is definitely another super key
of student entity set. Moreover it is the minimal super key. So we can say
roll is the candidate key of entity set student.

Primary Key:

A primary key is a candidate key chosen by database designer to identify

entities in an entity set. The primary key is used as the primary access
mechanism for the entity set.

Weak Vs Strong Entity Set:

If an entity set doesnt have sufficient attributes to form a primary key is

called weak entity set. In contrast one entity set having a primary key is
called a strong entity set. The strong entity sets can exist on their own,
without participating in any relationship. But for a weak entity set, each
entity instance has to participate in a relationship with a strong entity set in
order to exist. The strong entity set is also called the owner of the weak
entity set.

2.3 ER Diagram
ER diagram is a toll that represents the database in a graphical, symbolic and
easy to understand manner. It standardizes the concept of ER modeling and also
very useful for mapping the ER model to relational DBMS implementation. There
are some accepted standard to prepare an ER diagram for the conceptual design
phase of database. Actually ER diagrams enable designers and users to express
their understanding of what the planned database is intended to do and how it
might work, and to communicate about the database through a common
language.

Authored By: Prof. Soumik Das

Data Base Management System

Here we will learn how to graphically represent the concepts introduced in the
previous sections of this chapter. Figure 2.2 represents the usual notations that
are used to construct an ER diagram.

Figure 2.2

Usual ER diagram notations

CASE STUDY I

Let us assume a situation where BCA and MCA students are attending classes
of many subjects. So if we want to represent this fact with an ER diagram, we
need to go through the following phases

First of all we need to identify the entity set for the given case. Here we
can identify two entity sets STUDENT and SUBJECT.

After identifying the entity sets we need to identify the properties or

attributes of the entity sets. Also inspect for the key attribute by which a
entity instance is distinguishable from other entity instances of the same
entity set. A STUDENT entity set can have attributes like Name, Roll,
DOB, Age, Address, PhoneNo, Stream and Year. On the other hand
SUBJECT can be described by SubjectName, SubjectCode. For
STUDENT entity set Roll can be chosen as the key attribute because
Authored By: Prof. Soumik Das

Data Base Management System

university roll number of two students can not be same. In the same way
we can identify the SubjectCode as the key attribute of SUBJECT.

Now we need to find if there exist any relationships by which a meaningful

association can be done among entity sets. In this context we can easily
identify a relation LEARNING.

Figure 2.3 represents the ER diagram for the aforesaid case.

Name

DOB

Roll

Phone
No

Subject
Name

Age

LEARNING

STUDENT

Stream

Street
Name

Address

SUBJECT

SubjectCode

Year
City

House
No

Figure 2.3

PIN

ER diagram for CASE STUDY-I

Note that we designate the PhoneNo attribute as a multivalued attribute as a

student can have more than one phone number. The Age is designated as a
derived attribute because from DOB attribute we can derive it. On the other hand
Address can be divided in sub parts like HouseNo, StreetName, City and PIN.
So we can consider Address as a composite attribute. As already told the Roll
and SubjectCode are designated as the key attributes of the entity sets
STUDENT and SUBJECT respectively.
Authored By: Prof. Soumik Das

Data Base Management System

2.3.1 Role & Participation

Every entity type that participates in a relationship plays a definite role in that
relationship. For example, consider a relationship IS-FATHER-OF, that defines a
binary relationship between two same entity sets. Kabir is an entity instance ISFATHER-OF Alam and Alam is an entity instance IS-FATHER-OF Rubina. So we
can see in the first instance Alam plays a role as a son whereas in the other
instance Alam plays a role as a father. A role is not necessary if the participating
entity sets are distinct. But it is very important to identify the roles when entity
sets are not distinct.

Recursive Relationship:

When the participating entity sets are not distinct in the relationship types,
they are referred recursive relationships. Figure 2.4 shows an example of
recursive relationship.

EMPLOYEE
N

1
Manager

Subordinate

MANAGES

Figure 2.4

Recursive Relationship

Authored By: Prof. Soumik Das

Data Base Management System

Participation Constraints:

The participation Constraint specifies how the occurrence of an entity

depends on the other entity in the relationship. This constraint is of two types
total and partial.
Figure 2.5 illustrates an example of participation constraint clearly. Consider a
situation where every student must be a member of a team, or, in other words, a
student entity is of interest only if it participates in a MemberOf relationship.
Thus, we can include in an ER diagram a participation constraint in which
participation of student in MemberOf is total. A double line (sometime a strong
line) indicates the total participation constraint in an ER diagram. However, the
participation of student in LeaderOf is partial, because a student might be a team
leader.

MemberOf
N

STUDENTS

TEAM
1

LeaderOf

Figure 2.5

Total & Partial participation constraints

2.3.2 Cardinality Constraints

In section 2.1.6 we have seen that in a binary relationship, cardinality constraint
depict whether the relation is one-to-one, one-to-many, many-to-one and manyto-many. The following examples show how the relation ships are designated in
ER diagram. Some times arrow is used to designate the relationship cardinality
and some times we label the link by 1 or N.
Authored By: Prof. Soumik Das

Data Base Management System

One-to-One:

PRINCIPAL

HAS

PLAYER

WORKSON

PROJECT

TEACHES

STUDENT

Many-to- One:

ENGINEER

COLLEGE

One-to-Many:

TEAM

MANAGES

Many-to- Many:

TEACHER

2.3.3 Degree / Types of Relationship

In section 2.1.6 we have learnt about degree or types of relation like unary,
binary and ternary relationship. The following examples describe how to
represent the relationships having different degrees in ER diagram.
Authored By: Prof. Soumik Das

Data Base Management System

Unary Relationship:

Figure 2.6(A) and 2.6(B) represents several relationship instances of

IsMarriedTo relationship. Each relationship instance (r1, r2, r3, and r4)
connects two instances of PERSON entity set. The lines allow us to read
relationships between entity instances. For example, r2 suggests that person
p4 is married to person p8 and so on.

PERSON
IsMarriedTo

p1
p2

r1

p3

r2

p4

r3

p5

r4

p6
p7
p8
Figure 2.6(A)

Unary relationship with One-to-One cardinality

PERSON

Figure 2.6(B)

IsMarriedTo

ER Diagram of Unary relationship with One-to-One cardinality

Authored By: Prof. Soumik Das

Data Base Management System

For example, a person Rajiv is an entity instance of PERSON entity set is
married to Sujata, another instance of the same entity set PERSON. According to
definition of unary relationship it falls in a category of degree 1.

Binary Relationship:

Figure 2.7(A) and 2.7(B) represents several relationship instances of

Manages relationship. Each relationship instance (r1, r2, r3, r4 and r5)
connects one instance of MANAGER entity set to one instance of
DEPARTMENT entity set. The lines allow us to read relationships between
entity instances. For example, r1 suggests that manager m1 manages
department d2 and so on.

MANAGER

DEPARTMENT
Manages

m1

d1
r1

m2

r2

d2

m3

r3

d3

m4

r4

d4

r5
m5

d5

Figure 2.7(A)

MANAGER

Figure 2.7(B)

Binary relationship with One-to-One cardinality

MANAGES

DEPARTMENT

ER Diagram of Binary relationship with One-to-One cardinality

Authored By: Prof. Soumik Das

Data Base Management System

Ternary Relationship:

Figure 2.8(A) and 2.8(B) represents several relationship instances of Teaches

relationship. Each relationship instance (r1 and r2) connects one instance of
TEACHER entity set to several instances of STUDENT entity set and one
instance of COURSE entity set. The lines allow us to read relationships
between entity instances. For example, r1 suggests that teacher t1 teaches
s1 and s2 students in course c1 and so on.

TEACHER

TEACHES

t1

COURSE

c1
r1

t2

c2
r2

s1
s2
s3

STUDENT
Figure 2.8(A)

Ternary Relationship

TEACHES

TEACHER

STUDENT
Figure 2.8(B)

ER Diagram of Ternary Relationship

Authored By: Prof. Soumik Das

COURSE

Data Base Management System

2.3.4 Multiple Relationship

A set of entity sets can be meaningfully associated among themselves with more
than one relationship. This is referred as multiple relationship. Figure 2.9
illustrates a case where two entity sets ENGINEERS and PROJECT associated
with two relationships WorksOn and Manages.

Salary

ENGINEER

WorksOn

ID

Name

Manages
EmpID

Figure 2.9

Name

PROJECT
Duration
Budget

Multiple Relationship

2.3.5 Attribute of a Relationship

An attribute on a relationship stores information related to the relationship. In
Figure 2.10, the attribute Date stores the date of marriage.

PERSON

IsMarriedTo

Figure 2.10

Attribute of Relationship

Authored By: Prof. Soumik Das

Date

Data Base Management System

2.4 Enhanced Features of ER Modeling

The ER model provides adequate mechanism for modeling most of the traditional
database applications. But some cases from newer application domain can not
be designed by the set of concept we discussed in earlier sections. In current
context some enhanced mechanism of ER modeling will be discussed. We can
say this enhancement as an extension of ER model to object oriented paradigm.

2.4.1 Specialization & Generalization

Let us consider a situation where BCA students are the entity instances of
STUDENT entity set. At the same time MCA students are also the instances of
STUDENT entity set. In this kind of situation if we want to represent this with the
help of ER diagram, we need to consider two different entity sets BCA
STUDENT and MCA STUDENT. Although they are having many attributes
common among themselves. If we visualize this situation as object orientation,
then we can easily identify that BCA and MCA both entity sets are the subclass
of entity set STUDENT.

Subclass:

A subclass of entity set is a set entities with all the attributes found in the base
entity set and with some other attribute may associated with it. The
relationships in which the base class is participating, the subclass will
definitely participate in those relationships, with addition to that subclass may
be associated with other relationships.

For example, BCA STUDENT will have the attributes of STUDENT entity set.
More over some other attributes may also be associated with it.

Authored By: Prof. Soumik Das

Data Base Management System

IS-A Relationship:

In Enhanced ER (EER) diagram the base class, subclass hierarchy is

represented with a special kind of relationship called IS-A relationship. Figure
2.11 illustrates the concept of IS-A relationship.

Name

DOB

Roll

Age
Subject
Name
LEARNING

STUDENT

IS-A

BCA
STUDENT

SUBJECT

IS-A

GroupID

MCA
STUDENT

Figure 2.11

SubjectCode

THEORITICAL

PRACTICAL

IS-A Relationship

In Figure 2.11 we can see, BCA STUDENT and MCA STUDENT entity sets are
the subclasses of entity set STUDENT. The entity set BCA STUDENT will have
all the attributes of STUDENT entity set such as Roll, Name, DOB and Age.
Similarly entity set MCA STUDENT will have all the attributes of STUDENT entity
set, with addition to that MCA STUDENT will have GroupID as its attribute. On
the other hand a subject can be either theoretical or practical so the entity set
SUBJECT will have two entity sets as its subclass THEORITICAL and
PRACTICAL. These two entity set will inherit all the attributes from its base class
like BCA STUDENT and MCA STUDENT. This phenomenon is known as
attribute inheritance.
Authored By: Prof. Soumik Das

Data Base Management System

Specialization:

Imparting IS-A relationship in ER diagram helps us to categorize our entity set

in a specialized way. For example, the entity sets BCA STUDENT and MCA
STUDENT are the specialized category of entity set STUDENT. Informally
this top-down approach from base class to subclass is called specialization.

Definition: Specialization is a process f identifying subsets of an entity set

that share some distinguishing characteristics.

Note that, we can have more than one specialization from a given entity set. This
is known as multiple specialization. For example, entity set STUDENT can be
specialized with IS-A relationship as BCA STUDENT and MCA STUDENT. And
at the same time it can also be specialized as DAY SCHOLAR and
HOSTELLER. Figure 2.12 depicts multiple specialization.
Name

DOB

Roll

Age

STUDENT

IS-A

BCA
STUDENT

IS-A

GroupID

MCA
STUDENT

Figure 2.12

DAY SCHOLAR

Multiple Specialization

Authored By: Prof. Soumik Das

HOSTELLER

Data Base Management System

We can also have hierarchy of IS-A relationship. This is known as multilevel
specialization. For example, we can have BCA STUDENT entity set from
STUDENT entity set through IS-A relationship. Again the entity set BCA
STUDENT can be further categorized as YEAR-I, YEAR-II and YEAR-III through
specialization. Figure 2.13 illustrates multilevel specialization.

Name

DOB

Roll

Age

STUDENT

IS-A

BCA
STUDENT

IS-A

YEAR-I
Figure 2.13

YEAR-II

YEAR-III

Multilevel Specialization

Generalization:

IS-A relationship gives us the specialized entity sets from a given entity set. If
we approach in reverse direction of specialization, that means bottom-up
manner, we will get an entity set that is generalized form of specialized entity
sets. Figure 2.14 illustrates the generalization clearly.

Authored By: Prof. Soumik Das

Data Base Management System

Definition: Generalization is concerned with identifying some common
attributes of a collection of entity sets and forming a new entity set that
contains entities possessing these common attributes.

Name

DOB

Roll
G
E
N
E
R
A
L
I
Z
A
T
I
O
N

Age

STUDENT

IS-A

BCA
STUDENT

Figure 2.14

S
P
E
C
I
A
L
I
Z
A
T
I
O
N

MCA
STUDENT

Generalization & Specialization

2.4.2 Constraints on Specialization & Generalization

Consider the following cases

Is it possible that a subject is an elective subject as well as a core subject?

Is it possible that a customer is a depositor as well as a borrower?

Is it possible that a student is day scholar as well as a hosteller?

It is possible for a bank customer that he deposits money to his account as well
as he has taken a loan from the bank. So he is a borrower as well. Similarly a
subject may be an elective subject for BCA students at the same time it can be a
core subject for MCA students. But at the same time a student can not be a day
scholar and a hosteller.
Authored By: Prof. Soumik Das

Data Base Management System

These observations are nothing but some constraints on specialization and
generalization. And now we need to know how to impart these constraints in IS-A
relationships.

Overlap Constraint:

Overlap constraints determine whether two subclasses are allowed to contain

the same entity instance. In other words there may exist two situations the
subclasses may have overlapping entity or are disjoint among themselves.
For example, consider the second case mentioned above. A subject may be
an elective subject for a group of students at the same time it can be a core
subject for other group of students. Here overlapping is allowed so we label
the link with a o and otherwise it is labeled with d. Figure 2.15 depicts
overlap constraint.
Subject
Name

SubjectCode

SUBJECT

O
IS-A

ELECTIVE

Figure 2.15

CORE

Overlap Constraint

Authored By: Prof. Soumik Das

Data Base Management System

Covering Constraint:

Covering constraint determine whether the entities in the specialized

subclasses collectively include all the entities of base class. We can say
covering is total if the subclasses cover all the entities of base class and it is
denoted with double line. Otherwise we say the covering is partial and
denoted with a single line. Figure 2.16 illustrates the total and partial covering
constraint.
STUDENT
PARTIAL

TOTAL

IS-A

IS-A

BCA
STUDENT

DAY SCHOLAR

Figure 2.16

HOSTELLER

Covering Constraint

The entities of subclass BCA STUDENT do not cover all the entities of base
class STUDENT. Because there exist many student entities those are not BCA
students. Thats why the covering is partial. On the other hand an entity of
STUDENT is either an entity of DAY SCHOLAR subclass or an entity of
HOSTELLER subclass. So collectively all the entities of these two subclasses
cover all the entities of base class STUDENT. Thats why here the covering is
total.

Authored By: Prof. Soumik Das

Data Base Management System

2.4.3 Aggregation
Definition: Aggregation - a feature of the entity relationship model that allows a
relationship set to participate in another relationship set.

Consider the case illustrated in Figure 2.17.

ENGINEER

WORKS
ON

PROJECT

USES

COMPUTER

Figure 2.17

An Example

If we observe the case, we can see WORKS ON is a binary relationship whereas

USES is a ternary relationship. By Aggregation we can correlate these two
relationships. Using aggregation, we can view the relationships as a higher level
entity. Figure 2.18 illustrates the aggregation.

Authored By: Prof. Soumik Das

Data Base Management System

ENGINEER

WORKS
ON

USES

COMPUTER
Figure 2.18

Aggregation

Authored By: Prof. Soumik Das

PROJECT

Data Base Management System

SOLVED ASSIGNMENT

ASSIGNMENT I:

Construct an E-R diagram for a car-insurance company

whose customers own one or more cars each. Each car has
associated with it zero to any number of recorded accidents.

SOLUTION:

ASSIGNMENT II:

Construct an E-R diagram for a hospital with a set of patients

and a set of medical doctors. Associate with each patient a
log of the various tests and examinations conducted.

SOLUTION:

Authored By: Prof. Soumik Das

Data Base Management System

ASSIGNMENT III: A university registrars office maintains data about the
following entities: (a) courses, including number, title, credits,
syllabus, and prerequisites; (b) course offerings, including
course

number,

year,

semester,

section

number,

instructor(s), timings, and classroom; (c) students, including

student-id, name, and program; and (d) instructors, including
identification number, name, department, and title. Further,
the enrollment of students in courses and grades awarded to
students in each course they are enrolled for must be
appropriately modeled.
Construct an E-R diagram for the registrars office. Document all
assumptions that you make about the mapping constraints.
SOLUTION:
The main entity sets are student, course, course-offering, and instructor. The
entity set course-offering is a weak entity set dependent on course. The
assumptions made are:
a. A class meets only at one particular place and time. This E-R diagram
cannot model a class meeting at different places at different times.
b. There is no guarantee that the database does not have two classes
meeting at the same place and time.

Authored By: Prof. Soumik Das

Data Base Management System

ASSIGNMENT IV: Consider a database used to record the marks that students
get in different exams of different course offerings.
a. Construct an E-R diagram that models exams as
entities, and uses a ternary relationship, for the above
database.

b. Construct an alternative E-R diagram that uses

only a binary relationship between students and
course-offerings. Make sure that only one relationship
exists between a particular student and courseoffering pair, yet you can represent the marks that a
student gets in different exams of a course offering.

**Note that, identifying the primary key of relations and imparting cardinality
ratios for all relations of the assignments are left for the students.

Authored By: Prof. Soumik Das

Data Base Management System

SOLUTION:
a.

b.

Authored By: Prof. Soumik Das

Data Base Management System

Exercises

Multiple Answer Type Questions

1. Properties of an entity called

a.

Instances

b.

Attributes

c.

Parameters

d.

None of these

2. If DOB is stored in database then Age falls in which category?

a.

Multi valued

b.

Stored

c.

Derived

d.

Composite

3. R is an association between two instances of the same entity type; R is

a
a.

Unary relation

b.

Binary relation

c.

Ternary relation

d.

None of these

4. Candidate key is a subset of super key

a.

True

b.

False

5. When two entity sets are related with two atomic relations then it is
called
a.

Recursive relationship

b.

Multiple relationship

c.

Binary relationship

d.

All of these

6. Attribute inheritance occurs in

a.

Generalization

b.

Specialization

c.

Aggregation

d.

None of these

Authored By: Prof. Soumik Das

Data Base Management System

Short Answer Type Questions

1.

What is cardinality of a relationship?

2.

Describe Attribute inheritance with proper diagram.

3.

Differentiate overlapping and covering constraints with suitable

examples.

4.

With a proper example describe aggregation.

5.

What is weak entity set? Explain.

Broad Answer Type Questions

1.

What is degree of a relationship? Describe with examples.

2.

Design an ER diagram of a banking enterprise.

3.

What is recursive relationship? Describe role and participation.

4.

What is multilevel specialization? Explain using a suitable example and

proper diagram.

5.

Design an ER diagram of your college considering following points

i.

Final year students can assist professors in lab classes.

ii.

Final year students have major project.

iii.

A teacher can take class of different departments.

iv.

A student can have hostel accommodation.

Authored By: Prof. Soumik Das