Databases Reference
In-Depth Information
Thus, relvar P can be nonloss decomposed into its projections on the components of this JD. Whether we would
actually want to perform that decomposition is another matter, of course. We know we could if we wanted to, that's
all.
Let me close this section by revisiting the SPJ example from Chapter 9. For convenience, a sample value of
that relvar is shown in Fig. 10.1 (a repeat of Fig. 9.1). The predicate is
Supplier SNO supplies part PNO to project
JNO
, and the following business rule is in effect:
If supplier
s
supplies part
p
and part
p
is supplied to project
j
and project
j
is supplied by supplier
s
, then
supplier
s
supplies part
p
to project
j
.
┌─────┬─────┬─────┐
SPJ │ SNO │ PNO │ JNO │
├═════┼═════┼═════┤
│ S1 │ P1 │ J2 │
│ S1 │ P2 │ J1 │
│ S2 │ P1 │ J1 │
│ S1 │ P1 │ J1 │
└─────┴─────┴─────┘
Fig.10.1: Relvar SPJ—sample value
Now, we know from Chapter 9 that (as I put it in that chapter) the following JD captures the essence of this
business rule and so holds in SPJ:
{ { SNO , PNO } , { PNO , JNO } , { JNO , SNO } }
Now we can see this JD isn't implied by the sole key (viz., {SNO,PNO,JNO}) of the relvar, because the
membership algorithm fails, and so SPJ isn't in 5NF. So it can be nonloss decomposed into its three binary
projections, and probably should be, if we want to reduce redundancy. Those three projections are all in 5NF (no
JDs hold in them at all apart from trivial ones).
A USEFUL THEOREM
I said in Chapter 9 that in practice it's quite unusual to find a relvar that's in BCNF and not in 5NF. In fact, there's a
theorem that addresses this issue:
Theorem:
Let
R
be a BCNF relvar and let
R
have no composite keys; then
R
is in 5NF. (Recall from
Chapter 1 that a composite key is a key consisting of two or more attributes.)
This theorem is quite useful. What it says is, if you can get to BCNF (which is easy enough), and if there
aren't any composite keys in your BCNF relvar (which is often but not always the case), then you don't have to
worry about the complexities of JDs and 5NF in general─you know without having to think about the matter any
further that the relvar simply
is
in 5NF.
Note:
Actually the theorem applies to
3NF
, not BCNF; that is, it really says
a
3NF
relvar with no composite keys is in 5NF. But every BCNF relvar is in 3NF, and in any case BCNF is much
more important than 3NF, pragmatically speaking (as well as being conceptually simpler).
Caveat:
I don't know why, but people often misinterpret the foregoing theorem. To be specific, given that a
BCNF relvar with no composite keys is “automatically” in 5NF, people often seem to think that simply introducing
a surrogate key (noncomposite by definition) into a BCNF relvar “automatically” means the relvar is now in 5NF.
