Databases Reference
In-Depth Information
APR { ANO , PNO , RNO } KEY { ANO , PNO }
in which (to state the FD explicitly)
{ ANO , PNO }
→
{ RNO }
(Specification of {ANO,PNO} as a key is sufficient to express this FD.) However, relvars RA, RP, and AP are now
all redundant, since they're all projections of APR; they can therefore all be dropped. In place of constraint C1 we
now need constraint C2:
CONSTRAINT C2 APR { ANO , PNO } = JOIN { A { ANO } , P { PNO } } ;
This constraint must be separately and explicitly stated, since it isn't “implied by keys.”
Also, since every representative sells all of that representative's products in all of that representative's areas,
we have the additional constraint C3 on relvar APR:
{ RNO }
→→
{ ANO } | { PNO }
(These MVDs are nontrivial and not implied by keys, and relvar APR is thus not in 4NF.)
11
Again the constraint
must be separately and explicitly stated.
Thus the final design consists of the relvars R, A, P, and APR, together with the constraints C2 and C3 (both
of which are in fact equality dependencies once again):
CONSTRAINT C2 APR { ANO , PNO } = JOIN { A { ANO } , P { PNO } } ;
CONSTRAINT C3 APR = JOIN { APR { RNO , ANO } , APR { RNO , PNO } } ;
(There are also some foreign key constraints from APR to the other three relvars, but the details are straightforward
and I omit them here.)
This exercise illustrates very nicely the point that, in general, normalization might be adequate to represent
some of the semantic aspects of a given problem (basically, FDs, MVDs, and JDs that are implied by keys), but
explicit statement of additional constraints might be needed for other aspects. It also illustrates the point that it
might not always be desirable to normalize “all the way” (relvar APR is in BCNF but not in 4NF).
As a subsidiary exercise, you might like to consider whether a design involving RVAs might be appropriate
for the problem under consideration. Might such a design mean that some of the comments in the previous
paragraph no longer apply?
12.10 The first point to note here is that the MVDs
A
→→
B
|
C
and
A
→→
C
|
D
make no mention of attributes
D
and
B
, respectively. But didn't I say the union of
X
,
Y
, and
Z
, given the generic pair of MVDs
X
→→
Y
|
Z
, had to
be equal to the heading? Well, yes, I did─but I must now explain that we allow a certain shorthand notation as well,
illustrated in this exercise. For definiteness, let's focus on the expression
A
→→
B
|
C
. By definition, this
expression means
A
→→
B
and
A
→→
C
; and
A
→→
B
implies
A
→→
CD
, and
A
→→
C
implies
A
→→
BD
.
Moreover, since
A
,
B
,
C
, and
D
are single attributes and hence mutually disjoint, the decomposition rule for MVDs
11
Note, therefore, that relvar APR gives the lie to another popular misconception: viz., that a relvar consisting of a single key and a single nonkey
attribute is necessarily in 4NF. See also the answer to Exercise 13.10, later in this appendix.
