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