Databases Reference

In-Depth Information

thus not a legal value for the relvar. Likewise, if the current value of relvar SPJ is the relation on the right of the

figure, there's an
insertion anomaly:
We can't insert just the tuple (S2,P1,J1), because what results after that

insertion is (again, and for the same reason) not a legal value for the relvar.

Now, the JD in this example is
tuple forcing
. (Recall from Chapter 9 that a JD is tuple forcing if it's such

that, if certain tuples appear, certain additional tuples are forced to appear as well.) And the notion of tuple forcing

JDs (or the intuition behind that notion, rather) allows us to give definitions of the kinds of update anomalies that

can occur in the presence of such a JD─definitions that are more precise than their FD counterparts (such as they

are).
5
To be specific:

Definition:
Let the JD
J
hold in relvar
R
. Then
R
suffers from a
deletion anomaly with respect to
J
if and

only if there exist a relation
r
and a tuple
t
, each with the same heading as
R
, such that:

a.

r
satisfies
J
, and

b.

The relation
r′
whose body is obtained from that of
r
by removing
t
violates
J
.

Definition:
Let the JD
J
hold in relvar
R
. Then
R
suffers from an
insertion anomaly with respect to
J
if

and only if there exist a relation
r
and a tuple
t
, each with the same heading as
R
, such that:

a.

r
satisfies
J
, and

b.

The relation
r′
whose body is obtained from that of
r
by appending
t
satisfies
R
's key constraints but

violates
J
.

Points arising:

Note carefully that the foregoing anomalies are specifically defined in terms of some JD
J
, and they can

certainly occur if
J
is tuple forcing, as we've seen. In Chapter 13, however, we'll see that a relvar can suffer

from an insertion anomaly (though not a deletion anomaly) with respect to
J
even if
J
isn't tuple forcing.

Although they're more precisely defined than their FD counterparts, the foregoing anomalies can still be

regarded as the redundancy problem looked at from another point of view─though here, of course, we're

referring to redundancy caused by a JD, not by an FD.

If relvar
R
is subject to update anomalies and those anomalies are caused by a JD (tuple forcing or

otherwise), then replacing
R
by a set of 5NF projections will solve the problem. That is, such anomalies

can't occur with a 5NF relvar.

Please note carefully, however, that not all update anomalies are caused by FDs and JDs. In fact, it's

probably true to say that most integrity constraints (though not all) can give rise to an insertion anomaly, in the sense

that there always exists a tuple whose insertion would cause the constraint in question to be violated. (As a simple

example, suppose there's a constraint to the effect that supplier status values must lie in the range 1 to 100,

inclusive.) By contrast, comparatively few constraints can give rise to a deletion anomaly. (One that does would be

a constraint to the effect that there must always be at least two distinct suppliers. Another is a foreign key

5
They might be more precise, but they're also slightly suspect, inasmuch as they talk about inserting or deleting an individual tuple. As

explained in
SQL and
Relational Theory
, INSERT and DELETE really work on entire relations, not on individual tuples.