CHAPTER 11: RELATIONAL DATABASE
DESIGN ALGORITHMS AND FURTHER DEPENDENCIES
Answers to Selected Exercises
11.15 Show that the relation schemas
produced by Algorithm 11.2 are in 3NF.
Answer:
We give a proof by contradiction.
Suppose that one of the relations R i resulting from
Algorithm 13.1 is not in 3NF. Then a
FD Y -> A holds R i in where: (a) Y is not a
superkey of R, and (b) A is not a
prime attribute. But according to step 2 of the
algorithm, R i will contain a set of
attributes X union A 1 union A 2 union ... union A n ,
where X -> A i for i=1, 2, ...,
n, implying that X is a key of R i and the A i are the only
non-prime attributes of R i . Hence,
if an FD Y -> A holds in R i where A is non-prime and
Y is not a superkey of R i , Y must
be a proper subset of X (otherwise Y would contain X
and hence be a superkey). If both Y
-> A and X -> A hold and Y is a proper subset of X,
this contradicts that X -> A is a
FD in a minimal set of FDs that is input to the algorithm,
since removing an attribute from X leaves
a valid FD, thus violating one of the
minimality conditions. This produces
a contradiction of our assumptions. Hence, R i must
be in 3NF.
11.16 Show that if the matrix S
resulting from Algorithm 11.1 does not have a row
that is all "a" symbols,
then projecting S on the decomposition and joining it back will always produce
at least one spurious tuple.
Answer:
The matrix S initially has one row
for each relation R i in the decomposition, with "a"
symbols under the columns for the
attributes in R i . Since we never change an "a" symbol
into a "b" symbol during
the application of the algorithm, then projecting S on each R i at
the end of applying the algorithm
will produce one row consisting of all "a" symbols in
each S(R i ). Joining these back
together again will produce at least one row of all "a"
symbols (resulting from joining the
all "a" rows in each projection S(R i )). Hence, if
after applying the algorithm, S does
not have a row that is all "a", projecting S over the
R i 's and joining will result in at
least one all "a" row, which will be a spurious tuple
(since it did not exist in S but
will exist after projecting and joining over the R i 's).
11.17 Show that the relation schemas
produced by Algorithm 11.3 are in BCNF.
Answer:
This is trivial, since the algorithm
loop will continue to be applied until all relation
schemas are in BCNF.
11.18 Show that the relation schemas
produced by Algorithm 11.4 are in 3NF.
Answer:
The proof is similar to Exercise
15.15. The possible additional relation that contains a
key of R will be in 3NF since all
its attributes will be prime attributes.
11.19 Specify a template dependency
for join dependencies.
Answer:
The following template specifies a
join dependency JD(X,Y,Z).
11.20 Specify all the inclusion
dependencies for the relational schema of Figure 5.5.
Answer:
The inclusion dependencies will
correspond to the foreign keys shown in Figure 5.7.
11.21 Prove that a functional
dependency is also a multivalued dependency.
Answer:
Suppose that a functional dependency
X -> Y exists in a relation R={X, Y, Z}, and suppose
there are two tuples with the same
value of X. Because of the functional dependency, they
must also have the same value of Y.
Suppose the tuples are t 1 = < x, y, z 1 > and t 2 = < x,
y, z 2 >. Then, according to the
definition of multivalued dependency, we must have two
tuples t 3 and t 4 (not necessarily
distinct from t 1 and t 2 ) satisfying: t 3 [X]= t 4 [X]=
t 1 [X]= t 2 [X], t 3 [Y]= t 2 [Y],
t 4 [Y]= t 1 [Y], t 3 [Z]= t 1 [Z], and t 4 [Z]= t 2 [Z]. Two tuples
satisfying this are t 2 (satisfies
conditions for t 4 ) and t 1 (satisfies conditions for t 3 ).
Hence, whenever the condition for
functional dependency holds, so does the condition for
multivalued dependency.
11.22 No solution provided.
11.23 No solution provided.
11.24 No solution provided.
11.25 No solution provided.
11.26 No solution provided.
11.27 No solution provided.
11.28 No solution provided.
11.29 No solution provided.
11.30
Consider the relation REFRIG(MODEL#, YEAR, PRICE, MANUF_PLANT, COLOR), which is
abbreviated as REFRIG(M, Y, P, MP, C), and the following set of F of functional
dependencies: F={M -> MP, {M,Y} -> P, MP -> C}
(a)
Evaluate each of the following as a candidate key for REFRIG, giving reasons
why it can or cannot be a key: {M}, {M,Y}, {M.C}
(b) Based
on the above key determination, state whether the relation REFRIG is in 3NF and
in BCNF, giving proper reasons.
(c)
Consider the decomposition of REFRIG into D={R1(M,Y,P), R2(M,MP,C)}. Is this decomposition
lossless? Show why. (You may consult the test under Property LJ1 in Section
11.1.4)
Answers:
(a)
- {M} IS NOT a candidate key since
it does not functionally determine attributes Y or P.
- {M, Y} IS a candidate key since it
functionally determines the remaining attributes P, MP,
and C.
i.e.
{M, Y} P, But M MP
By augmentation {M, Y} MP
Since MP C, by transitivity M MP, MP
C, gives M C
By augmentation {M, Y} C
Thus {M, Y} P, MP, C and {M, Y} can
be a candidiate key
- {M, C} IS NOT a candidate key
since it does not functionally determine attributes Y or P.
(b)
REFRIG is not in 2NF, due to the
partial dependency {M, Y} MP (since {M} MP
holds). Therefore REFRIG is neither
in 3NF nor in BCNF.
Alternatively: BCNF can be directly
tested by using all of the given dependencies and
finding out if the left hand side of
each is a superkey (or if the right hand side is a prime
attribute). In the two fields in
REFRIG: M MP and MP C. Since neither M nor MP
is a superkey, we can conclude that
REFRIG is is neither in 3NF nor in BCNF.
(c)
