Databases Reference
In-Depth Information
NOT EXISTS
( SELECT *
FROM ( SELECT CUSTNO , SUM ( AMT ) AS TOTAL
FROM PAYMENTS
GROUP BY CUSTNO ) AS TEMP
WHERE NOT EXISTS
( SELECT *
FROM TOTALS
WHERE TOTALS.CUSTNO = TEMP.CUSTNO ) ) ) ;
For further explanation of SUMMARIZE, see
SQL and Relational Theory
.
Now, derived data is clearly redundant─though note once again that there are no violations of either
normalization or orthogonality here (in particular, relvars PAYMENTS and TOTALS are both in 6NF). I'll analyze
this example in more detail in the section immediately following.
MANAGING REDUNDANCY
The fact that the design of Example 12 from the previous section is redundant is clearly shown by the fact that the
specified equality dependency holds (constraint C12). And there are, at least in principle, four basic approaches to
dealing with the kind of redundancy illustrated by that example:
1.
Raw design only
2.
Declare the constraint
3.
Use a view
4.
Use a snapshot
Let's take a closer look.
1. Raw Design Only
This is perhaps the approach most likely to be encountered in practice, given the limited functionality provided by
most of today's DBMS implementations. The idea is simply that:
a.
Relvars PAYMENTS and TOTALS are defined exactly as shown in the previous section.
b.
Constraint C12 is
not
declared to the DBMS.
c.
Maintaining the derived data is the user's responsibility one hundred percent. (Or some user's responsibility,
at any rate; the maintenance might be done by means of a triggered procedure, but some user still has to write
the code for that procedure.)
19
In effect, this approach trades off (a) the extra work involved on the part of the user─or some user, at any
rate─in executing certain updates (as well as the associated performance hit) against (b) the improved performance
obtained when executing certain queries. But there are no guarantees; if the user makes a mistake during some
update that (in effect) causes Constraint C12 to be violated, well, tough.
19
Note in particular that relvar TOTALS ought never to be updated at all, except for the updates that are needed to keep the two relvars “in
synch,” as it were. (As a matter of fact, an analogous observation applies to the other three approaches as well, mutatis mutandis.)
