Databases Reference
In-Depth Information
x1 x2 x3 y24
y21 x2 y13 y14
We don't obtain a tuple of all
x
's, and so the “target” JD doesn't follow from the given one; in fact, we now
have a sample relation (of five tuples) that satisfies the latter and not the former.
11.3
Here first is a proof of part (b) of the extended theorem:
1.
X
→
Y
(given)
2.
XZ
→
YZ
(augmentation)
3.
XZ
→
XZ
(self determination)
4.
XZ
→
XYZ
(2 and 3, union)
Hence
XZ
is a superkey for
R
.
As for the converse, suppose relvar
R
contains the following tuples:
x y1 z1
x y2 z2
Thanks to the JD
{
XY
,
XZ
}, the following tuples must then also appear:
x y1 z2
x y2 z1
But
XZ
is a superkey and so
XZ
→
Y
holds, so
y1
=
y2
; hence
X
→
Y
holds.
11.4 This exercise is discussed further in Chapter 14, but I give a preliminary discussion here. First of all,
suppose such a decomposition (i.e., on the basis of the second JD) were done. Let the projections so obtained be
labeled SNC and CTN in the obvious way. Then the projections of SNC and CTN on {SNAME,CITY} are clearly
equal; that is, the following equality dependency holds (see Chapter 13)─
CONSTRAINT ... SNC { SNAME , CITY } = CTN { SNAME , CITY } ;
─and relvars SNC and CTN thus suffer from redundancy.
Observe further that the FD {CITY} → {STATUS} holds in CTN. By Heath's Theorem, therefore, we can
decompose CTN into its projections CT and CN on {CITY,STATUS} and {CITY,SNAME}, respectively. It
follows that the JD
{ { SNO , SNAME , CITY } , { CITY , STATUS , SNAME } }
implies the JD
{ { SNO , SNAME , CITY } , { CITY , STATUS } , { CITY , SNAME } }
In this latter JD, however, the {CITY,SNAME} component is clearly irrelevant, since it's a proper subset of the
{SNO,SNAME,CITY} component; it can therefore be dropped without significant loss. (In fact, of course, this
latter JD is identical to the first of the two JDs as given in the original exercise.)
