Databases Reference
In-Depth Information
CHAPTER 12
12.1 (a) Relvar CTX in the body of the chapter is an example, of course, but it would be better if you could come
up with an example from your own work environment. (b) Let
C
be a certain club, and let relvar
R
{
A
,
B
} be such
that the tuple (
a
,
b
) appears in
R
if and only if
a
and
b
are both members of
C
. Then
R
is equal to the cartesian
product of its projections
R
{
A
} and
R
{
B
}; thus, it's subject to the JD
{
A
,
B
} and, equivalently, to the following
MVDs:
{ }
→→
A
|
B
These MVDs aren't trivial, since they certainly don't hold in all binary relvars, and they're not implied by a
superkey either (the only key in
R
is the entire heading). It follows that
R
isn't in 4NF. However, it's certainly in
BCNF, because it's “all key.”
12.2
Possible formulations:
a.
CONSTRAINT ... CTX = JOIN { CTX { CNO , TNO } ,
CTX { CNO , XNO } } ;
b.
CONSTRAINT ... CTXD { CNO , TNO , XNO } =
JOIN { CTXD { CNO , TNO } , CTXD { CNO , XNO } } ;
12.3 (a) Suppose the current value of CTX is as given in Fig. 12.1. Then none of the four tuples shown can be
deleted in isolation: a deletion anomaly. (b) Suppose the current value of CTX contains just “the first two” of the
tuples shown in Fig. 12.1. Then neither “the third” nor “the fourth” tuple shown can be inserted in isolation: an
insertion anomaly.
12.4 Relvar SPJ from Chapter 9 is an example (no MVDs hold in that relvar at all, apart from trivial ones, and so
the relvar is certainly in 4NF).
12.5 The following proof might be thought to make very heavy weather of such an obvious point: Let the
projection in question be
R′
. The FD
X
→
Y
holds in
R′
if and only if, whenever tuples
t1′
and
t2′
of
R′
have the
same
X
value, they also have the same
Y
value. Let
T1
and
T2
be, respectively, the set of tuples in
R
from which
t1′
is derived and the set of tuples in
R
from which
t2′
is derived. By the definition of projection, every tuple
t1
in
T1
has the same
X
and
Y
values as
t1′
; likewise, every tuple
t2
in
T2
has the same
X
and
Y
values as
t2′
. It follows that
whenever tuples
t1
and
t2
of
R
have the same
X
value, they also have the same
Y
value; thus the FD
X
→
Y
holds in
R
. And it further follows that
X
→
Y
holds in
R′
if and only if it holds in
R
.
12.6 This result is immediate from Heath's Theorem: If
R
is subject to the FD
X
→
Y
, it's also subject to the JD
{
XY
,
XZ
}, where
Z
is “the other” attributes of
R
, and therefore it's subject to the MVDs
X
→→
Y
|
Z
.
12.7 The JD
{
XY
,
XZ
} is trivial if and only if
XY
=
H
or
XZ
=
H
. If
XY
=
H
, we have Case (b). If
XZ
=
H
, then
Z
=
H
-
X
; but
Z
=
H
-
X
-
Y
by definition, so
Y
is a subset of
X
, and we have Case (a).
12.8 The rule amounts to saying: If we start with a relvar with two or more independent relation valued attributes
(RVAs) and we want to eliminate them─which we usually but not invariably do want to do (see the answer to
Exercise 4.11)─then the first thing we should do is separate those RVAs. Using the notation of the exercise, this
step will give us relvars with headings
XY
and
XZ
, respectively. The next thing we should do is ungroup the RVA in