Databases Reference
In-Depth Information
{ SNO }
→
{ CLASS }
{ CLASS }
→
{ CITY }
{ CITY }
→
{ STATUS }
The 3NF procedure yields {SNO,CLASS}, {CLASS,CITY}, and {CITY,STATUS}.
Finally RX2. Irreducible cover:
{ SNO }
→
{ CLASS }
{ SNO }
→
{ CITY }
{ CLASS }
→
{ STATUS }
{ CITY }
→
{ STATUS }
The 3NF procedure yields {SNO,CLASS,CITY}, {CLASS,STATUS}, and {CITY,STATUS}. The interesting
thing about this example is that (as was shown in the body of the chapter) if we decompose on the basis of the FD
{SNO} → {CLASS,CITY}, we obtain {SNO,CLASS,CITY} and {CLASS,CITY,STATUS} as the 3NF projection
headings, and that's
not
what we get from the 3NF procedure. In fact, the result of the 3NF procedure requires the
following rather complicated multirelvar constraint to be maintained:
CONSTRAINT ... JOIN { SLC , LT } = JOIN { SLC , CT } ;
(“for a given supplier, class status = city status”; SLC, LT, and CT here denote the projections of RX2 on
{SNO,CLASS,CITY}, {CLASS,STATUS}, and {CITY,STATUS}, respectively). The example thus illustrates the
point that although the 3NF procedure is certainly guaranteed to yield 3NF projections and not to lose any FDs, it
probably shouldn't be followed too blindly.
Note:
Suppose we were to name the status attributes in relvars LT and CT differently, thus:
LT { CLASS , CLASS_STATUS }
CT { CITY , CITY_STATUS }
Then the constraint that the two status values must be equal for any given supplier might be stated thus:
CONSTRAINT ... IS_EMPTY ( ( JOIN { SLC , LT , CT } )
WHERE CLASS_STATUS ≠ CITY_STATUS ) ;
(The
Tutorial D
expression IS_EMPTY (
r
) returns TRUE if relation
r
is empty and FALSE otherwise.)
Alternatively:
CONSTRAINT ...
AND ( JOIN { SLC , LT , CT } , CLASS_STATUS = CITY_STATUS ) ;
The overall message of this example might be put this way: This whole business of losing or preserving FDs
in particular is really just a special case of a more general phenomenon. In fact, it should be obvious that, in general,
if we start with some design
DBD1
and map it into some logically equivalent design
DBD2
, then that process will
necessarily involve some restructuring of constraints as well as of relvars.
6.5
Assumptions:
No star plays more than one role in any given movie; no movie has more than one director.
(Are these assumptions reasonable?) FDs:
