Databases Reference
In-Depth Information
Aside:
Now I can take care of a small piece of unfinished business from earlier chapters,
having to do with view LS (“London suppliers”). In those chapters, I said that in practice
we would probably drop attribute CITY from that relvar. In fact, we can and probably
should decompose that relvar into its projections (a) SNT, on SNO, SNAME, and
STATUS, and (b) C, on CITY. (Of course, this latter projection has just one tuple,
containing the single value London.) As explained in
Database Design and Relational
Theory
, the FD {} → {CITY} holds in relvar LS;
11
thus, Heath's Theorem, mentioned in a
footnote earlier in this chapter, guarantees that the decomposition is nonloss. So relvar
SNT can be updated in accordance with these rules:
ON INSERT
i
INTO SNT : INSERT (
i
JOIN C ) INTO LS ;
ON DELETE
d
FROM SNT : DELETE (
d
MATCHING C ) FROM LS ;
(assuming, of course, that relvar LS is updatable in turn).
Note:
I'll leave consideration of
updates on relvar C here as an exercise for you.
End of aside
.
Now, for completeness I do plan to round out my discussion of Example 2, as I did with
Example 1, by (a) considering relvars SC and CT independently of relvar S; (b) considering what
happens if the user sees just one of those two relvars (i.e., if part of the total picture is hidden);
and (c) considering what happens if SC and CT are actually views. However, I think it's only
fair to say up front that there aren't going to be any surprises in those discussions; indeed, I think
it would be surprising if there were any surprises. In fact, let me draw your attention to a more
important point: namely that, as you might have already realized, the update rules I've given for
Example 2 are essentially identical to their counterparts for Example 1—or, to state the matter a
little more precisely, the rules for Example 1 are a degenerate special case of those for Example
2. What's more, this state of affairs is only to be expected, given that the situation illustrated by
Example 1 is a degenerate special case of that illustrated by Example 2. (Recall that Example 1
also involved a nonloss decomposition of relvar S, albeit into different projections.)
The Projection Relvars
So let's continue to assume that relvars S, SC, and CT are all base relvars (still no views yet).
Now consider a user who sees just the projection relvars SC and CT (and I observe again that the
11
Implying, incidentally, that LS isn't even in second normal form (2NF).
