Databases Reference
In-Depth Information
reference to Fig. 7.1), take
c
as Paris,
s
and
s′
as S2 and S3, respectively, and
p
and
p′
as P2 and
P5, respectively.
Of course, the fact that the foregoing MVDs hold in relvar SCP isn't a fluke—it will
always be the case with a many to many join that MVDs like those just shown hold. And the
reason I mention all this is that relvars in which such MVDs hold (i.e., relvars not in 4NF) are of
course subject to redundancy, and it's generally true that relvars that are subject to redundancy
can be awkward to update—as we'll see later, in the case at hand.
While I'm on the subject of dependencies, let me also point out that the following
functional dependencies (FDs) hold in relvar SCP as well:
3
{ SNO }
→
{ CITY }
{ PNO }
→
{ CITY }
These FDs are inherited from relvars S and P, respectively (where they certainly hold, because
the determinant—the set of attributes on the left side—is a key in each case).
Compensatory Actions
Consider the following INSERT on relvar S:
INSERT ( S9 , London ) INTO S ;
Given the sample values in Fig. 7.1, this insert can and will succeed, just so long as it has the
additional effect of inserting the following tuples into relvar SCP:
( S9 , London , P1 )
( S9 , London , P4 )
( S9 , London , P6 )
By contrast, consider this INSERT:
INSERT ( S9 , Madrid ) INTO S ;
Given the sample values in Fig. 7.1, this insert must fail, because there aren't any parts in Madrid
(and there's no possible compensatory action that makes sense here, either). On the other hand,
the following double INSERT can and will succeed—
INSERT ( S9 , Madrid ) INTO S ,
INSERT ( P8 , Madrid ) INTO P ;
—so long as it has the additional effect of inserting the following tuple into relvar SCP:
3
As a consequence, SCP isn't even in second normal form (2NF), let alone fourth. But it's the violation of 4NF as such that
leads to the relvar's characteristic behavior as a many to many join.
