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
 
Search WWH ::




Custom Search