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
