Databases Reference
In-Depth Information
But it doesn't mean that at all! If the relvar wasn't in 5NF before the surrogate was introduced, it won't be in 5NF
afterward. In particular, if it had a composite key before the surrogate was introduced, it'll still have one afterward.
FDs AREN'T JDs
Statements to the effect that every FD is a JD, or that (as I put it in Chapter 9) JDs are a kind of generalized FD, are
quite common in the less formal parts of the literature; indeed, I've said such things myself in previous topics and
other previous writings. But such talk is strictly incorrect. It would be better to say that every FD
implies
a JD
(which in fact is something we already know to be the case from Heath's Theorem). In other words, if
R
is subject
to a certain FD,
F
say, then it's certainly subject to a certain JD,
J
say. However, the converse is false─
R
can be
subject to that same JD
J
without being subject to that same FD
F
, as I now show:
Let relvar
R
have attributes
A
,
B
, and
C
(only), let
F
be the FD
AB
→
C
, and let
R
be subject to
F
(Heath
notation once again).
By Heath's Theorem, then,
R
is subject to the JD
{
ABC
,
AB
}. (With reference to the formulation of
Heath's Theorem given in Chapter 9, take
X
to be
AB
,
Y
to be
C
, and
Z
to be the empty set of attributes.) Call
this JD
J
.
But this JD
J
is trivial─it holds in every relvar
R
that has heading
ABC
, regardless of whether that relvar is
subject to the FD
AB
→
C
.
UPDATE ANOMALIES REVISITED
In Chapter 3, we took a brief look at certain update anomalies that can be caused by FDs: specifically, FDs that hold
in a relvar that's not in BCNF. To be frank, however, the update anomaly concept was never very precisely defined
(at least, not in that context); probably the best that could be said about it is that the update anomaly problem is just
the redundancy problem looked at from another point of view. So what about JDs?─specifically, JDs that hold in a
relvar that's not in 5NF? Such JDs do cause redundancy, as we've seen, and so we can expect them to give rise to
update anomalies as well. And indeed they do; what's more, the concept can be (or at any rate, is) more precisely
defined in that context, as we'll see.
Consider Fig. 10.2, which shows two possible values for relvar SPJ; the one on the left is a repeat of the
relation from Fig. 10.1, the one on the right is obtained from the one on the left by removing two tuples.
┌─────┬─────┬─────┐ ┌─────┬─────┬─────┐
SPJ │ SNO │ PNO │ JNO │ SPJ │ SNO │ PNO │ JNO │
├═════┼═════┼═════┤ ├═════┼═════┼═════┤
│ S1 │ P1 │ J2 │ │ S1 │ P1 │ J2 │
│ S1 │ P2 │ J1 │ │ S1 │ P2 │ J1 │
│ S2 │ P1 │ J1 │ └─────┴─────┴─────┘
│ S1 │ P1 │ J1 │
└─────┴─────┴─────┘
Fig. 10.2: Two possible values for relvar SPJ
Observe now that if the current value of relvar SPJ is the relation on the left of the figure, there's a
deletion
anomaly:
We can't delete just the tuple (S1,P1,J1), because what results after that deletion violates the JD and is
