Databases Reference
In-Depth Information
2. Declare the Constraint
In this approach Constraint C12 is explicitly declared to the DBMS and the DBMS takes the responsibility for
enforcing it. Maintaining the derived data is still the user's responsibility, though, exactly as it was under the
previous approach. What's more, if the user carries out this task reliably and correctly, the constraint checking will
never fail, and it will thus, in effect, constitute pure overhead on the user's updates. But we can't dispense with the
constraint, precisely because we need the system to check that the user
is
carrying out the maintenance task reliably
and correctly.
3. Use a View
Clearly it would be better if, instead of simply declaring the constraint, we could actually inform the system of the
rule by which the derived data is defined and have the system perform the derivation process automatically. And we
can; that's exactly what the view mechanism does. To be specific, we can replace the base relvar TOTALS by a
view (or “virtual relvar”) of the same name, thus:
VAR TOTALS VIRTUAL
/*
Tutorial D
syntax for defining a view */
( SUMMARIZE PAYMENTS BY { CUSTNO } :
{ TOTAL := SUM ( AMOUNT ) } ) ;
Now the user no longer has to worry about maintaining the derived data; moreover, there's now no way that
Constraint C12 can possibly be violated, and there's no need even to state it any more, except perhaps informally (as
a means of telling the user the semantics of the view, perhaps). Note, however, that the user does have to be
explicitly told not to try to maintain the totals! This fact doesn't mean the user has to be told that relvar TOTALS is
a view, though; it just means the user has to be told that the maintenance task will effectively be performed by the
system.
4. Use a Snapshot
The drawback to the view solution, however, is that the derivation process is performed every time the view is
referenced (even if no updates have been done since the last time it was referenced). Thus, if the object of the
exercise is in to do the derivation work at update time in order to improve subsequent query performance, the view
solution is clearly inadequate. In that case, we should use a
snapshot
instead of a view:
VAR TOTALS SNAPSHOT
( SUMMARIZE PAYMENTS BY { CUSTNO } :
{ TOTAL := SUM ( AMOUNT ) } )
REFRESH ON EVERY UPDATE ;
The snapshot concept has its origins in a paper by Michel Adiba.
20
Basically, snapshots, like views, are
derived relvars; unlike views, however, they're real, not virtual─that is, they're represented not just by their
definition in terms of other relvars, but also (at least conceptually) by their own separately materialized copy of the
data. In other words, defining a snapshot is much like executing a query, except that:
20
Michel Adiba: “Derived Relations: A Unified Mechanism for Views, Snapshots, and Distributed Data,” Proc. 1981 Int. Conf. on Very Large
Data Bases, Cannes, France (September 1981). See also the earlier version “Database Snapshots,” by Michel E. Adiba and Bruce G. Lindsay,
IBM Research Report RJ2772 (March 7th, 1980).
