CHAPTER

6
Database Design using the E-R
Model

Pra ti e Exer ises

6.1 Constru t an E-R diagram for a ar insuran e ompany whose ustomers own
one or more ars ea h. Ea h ar has asso iated with it zero to any number of
re orded a idents. Ea h insuran e poli y overs one or more ars and has one
or more premium payments asso iated with it. Ea h payment is for a parti ular
period of time, and has an asso iated due date, and the date when the payment
was re eived.
Answer:

One possible E-R diagram is shown in Figure 6.101. Payments are modeled as
weak entities sin e they are related to a spe i poli y.
Note that the parti ipation of a ident in the relationship parti ipated is not
total, sin e it is possible that there is an a ident report where the parti ipating
ar is unknown.
6.2 Consider a database that in ludes the entity sets student, ourse, and se tion
from the university s hema and that additionally re ords the marks that students
re eive in dierent exams of dierent se tions.

a. Constru t an diagram that models exams as entities and uses a ternary

E-R

relationship as part of the design.

b. Constru t an alternative diagram that uses only a binary relationship
E-R

between student and se tion. Make sure that only one relationship exists
between a parti ular student and se tion pair, yet you an represent the
marks that a student gets in dierent exams.

Answer:

43
44 Chapter 6 Database Design using the E-R Model

customer car policy

1..* 1..1
customer_id owns license_no covers policy_id
name model
address

participated payment

accident premium_ payment

report_id payment_no
date due_date
place amount
received_on

Figure 6.101 E-R diagram for a ar insuran e ompany.

a. The diagram is shown in Figure 6.102. Note that an alternative is to

E-R

model examinations as weak entities related to a se tion, rather than as

strong entities. The marks relationship would then be a binary relation-
ship between student and exam, without dire tly involving se tion.
b. The diagram is shown in Figure 6.103. Note that here we have not
E-R

modeled the name, pla e, and time of the exam as part of the relationship
attributes. Doing so would result in dupli ation of the information, on e
per student, and we would not be able to re ord this information without
an asso iated student. If we wish to represent this information, we need
to retain a separate entity orresponding to ea h exam.
6.3 Design an diagram for keeping tra k of the s oring statisti s of your favorite
E-R

sports team. You should store the mat hes played, the s ores in ea h mat h, the
players in ea h mat h, and individual player s oring statisti s for ea h mat h.

marks
student
section course
student_id
name sec_id course_id
dept_name exam_marks semester sec_course title
tot_cred year credits

exam
exam_id
name
place
time

Figure 6.102 E-R diagram for marks database.

 Pra ti e Exer ise 45

{exam_marks
exam_id
marks
}
student
section course
student_id
name sec_id course_id
dept_name exam_marks semester sec_course title
tot_cred year credits

Figure 6.103 Another E-R diagram for marks database.

Summary statisti s should be modeled as derived attributes with an explanation

as to how they are omputed.
Answer:
The diagram is shown in Figure 6.104. The derived attribute season s ore is
omputed by summing the s ore values asso iated with the player entity set via
the played relationship set.
6.4 Consider an E-Rdiagram in whi h the same entity set appears several times,
with its attributes repeated in more than one o urren e. Why is allowing this
redundan y a bad pra ti e that one should avoid?
Answer:
The reason an entity set would appear more than on e is if one is drawing a
diagram that spans multiple pages.
The dierent o urren es of an entity set may have dierent sets of at-
tributes, leading to an in onsistent diagram. Instead, the attributes of an entity
set should be spe ied only on e. All other o urren es of the entity should
omit attributes. Sin e it is not possible to have an entity set without any at-
tributes, an o urren e of an entity set without attributes learly indi ates that
the attributes are spe ied elsewhere.

score
match
match_id player
date player_id
stadium played
name
opponent age
own_score season_score()
opp_score

Figure 6.104 E-R diagram for favorite team statisti s.

46 Chapter 6 Database Design using the E-R Model

A RA

B R C B RB E RC C

(a) (b)

RAB A RAC

B RBC C

(c)

Figure 6.29 Representation of a ternary relationship using binary relationships.

6.5 An E-R diagram an be viewed as a graph. What do the following mean in terms
of the stru ture of an enterprise s hema?
a. The graph is dis onne ted.
b. The graph has a y le.

Answer:

a. If a pair of entity sets are onne ted by a path in an diagram, the

E-R

entity sets are related, though perhaps indire tly. A dis onne ted graph
implies that there are pairs of entity sets that are unrelated to ea h other.
In an enterprise, we an say that the two parts of the enterprise are om-
pletely independent of ea h other. If we split the graph into onne ted
omponents, we have, in ee t, a separate database orresponding to ea h
independent part of the enterprise.
b. As indi ated in the answer to the previous part, a path in the graph be-
tween a pair of entity sets indi ates a (possibly indire t) relationship be-
tween the two entity sets. If there is a y le in the graph, then every pair
of entity sets on the y le are related to ea h other in at least two distin t
ways. If the E-Rdiagram is a y li , then there is a unique path between
every pair of entity sets and thus a unique relationship between every pair
of entity sets.
 Pra ti e Exer ise 47

RA

B RB E RC C

Figure 6.105 E-R diagram for Exer ise Exer ise 6.6b.

6.6 Consider the representation of the ternary relationship of Figure 6.29a using
the binary relationships illustrated in Figure 6.29b (attributes not shown).
a. Show a simple instan e of E , A, B, C , RA , RB , and RC that annot orre-
spond to any instan e of A, B, C , and R.
b. Modify the diagram of Figure 6.29b to introdu e onstraints that will
E-R

guarantee that any instan e of E , A, B, C , RA , RB , and RC that satises the

onstraints will orrespond to an instan e of A, B, C , and R.
. Modify the pre eding translation to handle total parti ipation onstraints
on the ternary relationship.

Answer:

a. Let E = ^e1 , e2 `, A = ^a1 , a2 `, B = ^b1 `, C = ^ 1 `, RA =

^(e1 , a1 ), (e2 , a2 )`, RB = ^(e1 , b1 )`, and RC = ^(e1 , 1 )`. We see that
be ause of the tuple (e2 , a2 ), no instan e of A, B, C , and R exists that or-
responds to E , RA , RB and RC .
b. See Figure 6.105. The idea is to introdu e total parti ipation onstraints
between E and the relationships RA , RB , RC so that every tuple in E has a
relationship with A, B, and C .
. Suppose A totally parti ipates in the relationhip R, then introdu e a total
parti ipation onstraint between A and RA , and similarly for B and C .
6.7 A weak entity set an always be made into a strong entity set by adding to its
attributes the primary-key attributes of its identifying entity set. Outline what
sort of redundan y will result if we do so.
Answer:
The primary key of a weak entity set an be inferred from its relationship with
the strong entity set. If we add primary-key attributes to the weak entity set, they
will be present in both the entity set, and the relationship set and they have to
be the same. Hen e there will be redundan y.
48 Chapter 6 Database Design using the E-R Model

6.8 Consider a relation su h as se ourse, generated from a many-to-one relation-

ship set se ourse. Do the primary and foreign key onstraints reated on the
relation enfor e the many-to-one ardinality onstraint? Explain why.
Answer:
In this example, the primary key of se tion onsists of the attributes ( ourse id ,
se id , semester , year ),
whi h would also be the primary key of se ourse, while
ourse id is a foreign key from se ourse referen ing ourse. These onstraints
ensure that a parti ular se tion an only orrespond to one ourse, and thus the
many-to-one ardinality onstraint is enfor ed.
However, these onstraints annot enfor e a total parti ipation onstraint, sin e
a ourse or a se tion may not parti ipate in the se ourse relationship.
6.9 Suppose the advisor relationship set were one-to-one. What extra onstraints
are required on the relation advisor to ensure that the one-to-one ardinality
onstraint is enfor ed?
Answer:

In addition to de laring s ID as primary key for advisor, we de lare i ID as a

superkey for advisor (this an be done in SQLusing the unique onstraint on
i ID).

6.10 Consider a many-to-one relationship R between entity sets A and B. Suppose

the relation reated from R is ombined with the relation reated from A. In
SQL , attributes parti ipating in a foreign key onstraint an be null. Explain
how a onstraint on total parti ipation of A in R an be enfor ed using not null
onstraints in .
SQL

Answer:
The foreign-key attribute in R orresponding to the primary key of B should be
made not null. This ensures that no tuple of A whi h is not related to any entry
in B under R an ome in R. For example, say a is a tuple in A whi h has no
orresponding entry in R. This means when R is ombined with A, it would have
a foreign-key attribute orresponding to B as null, whi h is not allowed.
6.11 In SQL , foreign key onstraints an referen e only the primary key attributes of
the referen ed relation or other attributes de lared to be a superkey using the
unique onstraint. As a result, total parti ipation onstraints on a many-to-many
relationship set (or on the “one” side of a one-to-many relationship set) annot
be enfor ed on the relations reated from the relationship set, using primary
key, foreign key, and not null onstraints on the relations.

a. Explain why.
b. Explain how to enfor e total parti ipation onstraints using omplex
he k onstraints or assertions (see Se tion 4.4.8). (Unfortunately, these
features are not supported on any widely used database urrently.)
 Pra ti e Exer ise 49

Answer:

a. For the many-to-many ase, the relationship set must be represented as a

separate relation that annot be ombined with either parti ipating entity.
Now, there is no way in SQLto ensure that a primary-key value o urring
in an entity E 1 also o urs in a many-to-many relationship R, sin e the
orresponding attribute in R is not unique; foreign keys an only
SQL

refer to the primary key or some other unique key.

Similarly, for the one-to-many ase, there is no way to ensure that an at-
tribute on the one side appears in the relation orresponding to the many
side, for the same reason.
b. Let the relation R be many-to-one from entity A to entity B with a and b as
their respe tive primary keys. We an put the following he k onstraints
on the "one" side relation B:
onstraint total part he k (b in (sele t b from A));
set onstraints total part deferred;

Note that the onstraint should be set to deferred so that it is only he ked
at the end of the transa tion; otherwise if we insert a b value in B before
it is inserted in A, the onstraint would be violated, and if we insert it in
A before we insert it in B, a foreign-key violation would o ur.

6.12 Consider the following latti e stru ture of generalization and spe ialization (at-
tributes not shown).

X Y

A B C

For entity sets A, B, and C , explain how attributes are inherited from the higher-
level entity sets X and Y . Dis uss how to handle a ase where an attribute of X
has the same name as some attribute of Y .
Answer:

A inherits all the attributes of X, plus it may dene its own attributes. Similarly,
C inherits all the attributes of Y plus its own attributes. B inherits the attributes
of both X and Y. If there is some attribute name whi h belongs to both X and Y,
it may be referred to in B by the qualied name X.name or Y.name.
6.13 An diagram usually models the state of an enterprise at a point in time.
E-R

Suppose we wish to tra k temporal hanges, that is, hanges to data over time.
For example, Zhang may have been a student between September 2015 and
50 Chapter 6 Database Design using the E-R Model

May 2019, while Shankar may have had instru tor Einstein as advisor from May
2018 to De ember 2018, and again from June 2019 to January 2020. Similarly,
attribute values of an entity or relationship, su h as title and redits of ourse,
salary, or even name of instru tor , and tot red of student, an hange over time.
One way to model temporal hanges is as follows: We dene a new data type
alled valid time, whi h is a time interval, or a set of time intervals. We then
asso iate a valid time attribute with ea h entity and relationship, re ording the
time periods during whi h the entity or relationship is valid. The end time of an
interval an be innity; for example, if Shankar be ame a student in September
2018, and is still a student, we an represent the end time of the valid time in-
terval as innity for the Shankar entity. Similarly, we model attributes that an
hange over time as a set of values, ea h with its own valid time.

a. Draw an diagram with the student and instru tor entities, and the ad-
E-R

visor relationship, with the above extensions to tra k temporal hanges.

b. Convert the E-R diagram dis ussed above into a set of relations.

It should be lear that the set of relations generated is rather omplex, leading
to di ulties in tasks su h as writing queries in . An alternative approa h,
SQL

whi h is used more widely, is to ignore temporal hanges when designing the
E-R model (in parti ular, temporal hanges to attribute values), and to modify
the relations generated from the model to tra k temporal hanges.
E-R

Answer:
.

a. The E-Rdiagram is shown in Figure 6.106.

The primary key attributes student id and instru tor id are assumed to be
immutable, that is, they are not allowed to hange with time. All other
attributes are assumed to potentially hange with time.
Note that the diagram uses multivalued omposite attributes su h as
valid times or name, with subattributes su h as start time or value. The
value attribute is a subattribute of several attributes su h as name, tot red
and salary, and refers to the name, total redits or salary during a parti -
ular interval of time.
b. The generated relations are as shown below. Ea h multivalued attribute
has turned into a relation, with the relation name onsisting of the orig-
inal relation name on atenated with the name of the multivalued at-
tribute. The relation orresponding to the entity has only the primary-key
attribute, and this is needed to ensure uniqueness.
 Pra ti e Exer ise 51

student(student id)
student valid times(student id, start time, end time)
student name(student id, value, start time, end time
student dept name(student id, value, start time, end time
student tot red(student id, value, start time, end time
instru tor(instru tor id)
instru tor valid times(instru tor id, start time, end time)
instru tor name(instru tor id, value, start time, end time
instru tor dept name(instru tor id, value, start time, end time
instru tor salary(instru tor id, value, start time, end time
advisor(student id, instru tor id, start time, end time)

The primary keys shown are derived dire tly from the diagram. If we
E-R

add the additional onstraint that time intervals annot overlap (or even
the weaker ondition that one start time annot have two end times), we
an remove the end time from all the above primary keys.

student instructor
student_id instructor_id
{valid_times {valid_times
start_time {valid_time start_time
end_time start_time end_time
} end_time }
{name } {name
value value
start_time start_time
end_time end_time
} advisor }
{dept_name {dept_name
value value
start_time start_time
end_time end_time
} }
{tot_cred {salary
value value
start_time start_time
end_time end_time
} }

Figure 6.106 E-R diagram for Exer ise 6.13