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

defined is an
n-
adic operator, not a dyadic operator merely.

13.14 Loosely speaking, a disjunction of predicates is the OR of two or more other predicates. If some relvar
R
had

a disjunctive relvar predicate, then each of the individual predicates that are OR'd together would have to have the

same parameters (because the tuples that satisfy them would all have to be of the same type). Reducing such a

predicate to simple predicates would involve decomposition of the relvar via restriction instead of projection (and