Databases Reference
In-Depth Information
b.
By contrast, derived propositions─at least, derived propositions that correspond to tuples obtained by taking
projections of tuples in the given relvars─do involve at least one existential quantifier.
Now, we surely don't want to say our usual shipments relvar SP is intrinsically redundant (do we?). So it
looks as if what we might want to say is something along the lines of:
If the same proposition is represented twice, but that proposition is existentially quantified, then that
repetition doesn't count as redundancy.
But wait a minute─what about the suppliers relvar S, with its FD {CITY} → {STATUS}? An argument
exactly analogous to the foregoing would seem to suggest that, e.g., the tuple (London,20), which appears as a
subtuple in two of the supplier tuples depicted in Fig. 1.1, represents the proposition:
There exists some supplier SNO such that there exists some name SNAME such that
supplier SNO is
named SNAME, has status 20, and is located in city London.
Clearly this proposition is existentially quantified; yet it's represented twice, and we
do
want that repetition to count
as redundancy. (As we know, relvar S isn't in BCNF.) So what's going on?
As is so often the case, I believe the answer to this question can be found by taking a closer look at the
predicates. First, recall from Chapter 13 that a predicate is
simple
if it involves no connectives, and
conjunctive
if
it's a set of other predicates connected by AND (and I'll assume for present purposes that those other predicates are
all simple ones). Now, the predicate for relvar SP,
Supplier SNO supplies part PNO in quantity QTY
, is simple in
the foregoing sense. By contrast, the predicate for relvar S is conjunctive─it can be decomposed into a set of simple
predicates. I can make this latter fact more immediately obvious by stating the predicate in the following slightly
stilted but actually more logical form:
Supplier SNO is named SNAME
and
Supplier SNO is located in city CITY
and
City CITY has status STATUS.
It should be clear, then, given this version of the predicate, that:
a.
Relvar S is subject to the nontrivial, irreducible JD
{SN,SC,CT}, where the names SN, SC, and CT denote
the headings {SNO,SNAME}, {SNO,CITY}, and {CITY,STATUS}, respectively. (By contrast, the only
JDs that hold in relvar SP are trivial ones, and SP is in 6NF. Relvar S, of course, isn't even in BCNF.)
b.
Relvar S can therefore be nonloss decomposed in accordance with that JD. The predicates for the
corresponding projections are as follows:
SN:
Supplier SNO is named SNAME.
SC:
Supplier SNO is located in city CITY.
CT:
City CITY has status STATUS.
