Databases Reference
In-Depth Information
VAR SCP VIRTUAL ( ( JOIN { S , SP , P } ) { SNO , PNO , CITY } ) ;
The following FD holds in this view:
{ SNO , PNO } { CITY }
(in fact, {SNO,PNO} is a key). The following (nontrivial) MVDs also hold:
{ CITY } →→ { SNO } | { PNO }
Because of these MVDs, relvar SCP isn't in 4NF, though it is in BCNF. As for “conventional wisdom,” this
example gives the lie to another popular misconception: viz., that a relvar consisting of a single key and a single
nonkey attribute is necessarily in 6NF, or at least 5NF (see Exercise 1.8).
13.11 For the definition, see the body of the chapter. As for an example, suppose relvar SP is subject to a
constraint to the effect that odd numbered parts can be supplied only by odd numbered suppliers and even numbered
parts only by even numbered suppliers (the example is very contrived, of course, but it suffices for the purpose at
hand). Then this constraint is clearly not implied by the domain and key constraints that hold in relvar SP, and so
the relvar isn't in DK/NF; yet it's certainly in 6NF.
13.12 There certainly is a difference, since overstrong PJ/NF implies 5NF and 5NF implies SKNF and the reverse
implications don't hold. But it's easy to confuse the two, because the following superficially similar observations
are both true (note the boldface ):
Relvar R is in SKNF if and only if, for every irreducible JD { X1 ,..., Xn } that holds in R , each Xi ( i = 1, ..., n )
includes some key K of R .
Relvar R is in overstrong PJ/NF if and only if, for every irreducible JD { X1 ,..., Xn } that holds in R , each Xi
( i = 1, ..., n ) includes the same key K of R .
13.13 Apologies if you think these definitions a little late in coming:
Definition: Let r be the relation < H , h > and let c be a boolean expression in which every attribute reference
identifies some attribute of r and there aren't any relvar references. Then c is a restriction condition , and
the restriction of r according to c , r WHERE c , is the relation < H , x >, where x is the set of all tuples of r for
which c evaluates to TRUE.
Definition: Let relations r1 , ..., rn ( n ≥ 0) all have the same heading H . Then the union of r1 , ..., rn ,
UNION { r1 ,..., rn }, is a relation with heading H and body the set of all tuples t such that t appears in at least
one of r1, r2, ..., rn . (If n = 0, some syntactic mechanism, not shown here, is needed to specify the pertinent
heading H , and the result is the unique empty relation having that heading.) Observe that union as here