6. Concluding Remarks

 

Here we just summarize the major points from that paper, for ease of subsequent reference.  Refer to the previous paper for further explanation.

 

Of those principles, the first and overriding one is as follows: 

 

A given row can appear in a given table only if that row does not cause the table predicate for that table to be violated -- and this observation is just as true for a view as it is for a base table. 

 

The table predicate for a given table is, loosely speaking, "what the table means"; it is the criterion for update acceptability for that table.  In other words, the table predicate for a given table constitutes the criterion for deciding whether or not some proposed update is in fact valid, or at least plausible, for that table.  In the case of a base table, the table predicate is the logical AND of all column constraints and table constraints that apply to the base table in question.  In the case of a derived table, the table predicate is derived in a straightforward way from the table predicate(s) for the table(s) from which the table in question is derived; for example, the table predicate PC for C = A INTERSECT B is (PA) AND (PB), where PA and PB are the table predicates for A and B respectively.

 

The remaining principles are as follows. 

 

1.   All tables must be genuine relations (i.e., duplicate rows are not allowed).

 

2.   The updatability or otherwise of a given view is a semantic issue, not a syntactic one (i.e., it must not depend on the particular form in which the view definition happens to be stated).

 

3.   The view updatability rules must work correctly in the special case when the "view" is in fact a base table. 

 

4.   The rules must preserve symmetry where applicable.

 

5.   The rules must take into account any applicable triggered actions, such as cascade DELETE. 

 

6.   For most purposes, UPDATE can be regarded as shorthand for a DELETE-then-INSERT sequence.  Note, however, that it must be understood that no checking of table predicates is done "in the middle of" any given update; that is, the expansion of UPDATE is DELETE-INSERT-check, not DELETE-check-INSERT-check.  It must also be understood that triggered actions are likewise never performed "in the middle of" any given update.  Finally, it must also be understood that some slight refinement is required to the foregoing shorthand in the case of projection views (see the section "Updating Projections" later for further discussion).

 

7.   All update operations on views are implemented by the same kind of update operations on the underlying tables.  That is, INSERTs map to INSERTs and DELETEs to DELETEs (we can ignore UPDATEs, thanks to the previous point). 

 

8.   The rules must be capable of recursive application.

 

9.   The rules cannot assume that the database is well designed (though they might on occasion produce a slightly surprising result if the database is not well designed -- a fact that can be seen in itself as an additional argument in support of good design). 

 

10.  If a view is updatable, there should be no prima facie reason for permitting some updates but not others (e.g., DELETEs but not INSERTs). 

 

11.  INSERT and DELETE should be inverses of each other, where possible.

 

We have applied the systematic view updating scheme first described in the previous three part article to the question of updating restriction, projection, extension, and join views.  Regarding join views in particular, we offer the following additional comments:

 

Ø       It is well known that intersection is a special case of natural join.  To be specific, if tables A and B are type-compatible (Type-compatibility is usually referred to as union-compatibility in the literature.  We prefer our term for reasons that are beyond the scope of the present discussion), the expressions A INTERSECT B and A JOIN B are semantically identical; they should thus display identical update behavior if treated as view definitions, and so they do (exercise for the reader!).

 

Ø       It is well known also that Cartesian product is a special case of natural join.  To be specific, if tables A and B have no common columns at all, the expressions A TIMES B and A JOIN B are semantically identical; they should thus display identical update behavior if treated as view definitions, and so they do (another exercise for the reader).

 

Ø       The reader will observe that we have said nothing regarding T-joins.  The reason is, of course, that T-join is not a primitive operation; in fact, it is defined as a restriction of a Cartesian product.  The update rules for T-join can therefore be derived from the rules for restriction and Cartesian product.

 

We have now discussed all of the operators that are usually regarded as part of the relational algebra except for RENAME, SUMMARIZE, and DIVIDE.  RENAME is trivial.  Regarding SUMMARIZE, we remark that (in general) the SUMMARIZE operation is not information preserving -- that is, there is no unambiguous reverse mapping from the result of a SUMMARIZE back to the original table.  As a consequence, views whose definition involves SUMMARIZE are (in general) not updatable.  Finally, DIVIDE (like T-join) is not primitive, and hence the relevant update rules can be derived from those already given (specifically those for difference, projection, and Cartesian product); the details are left as yet another exercise for the reader, but we observe that in practice it seems likely that most division views will not be updatable at all (why, exactly?).

 

One final observation:  Throughout this paper and its predecessor, we have implied, but never quite stated explicitly, that the target of a given update operation need not be a named table (i.e., a base table or a view), but can instead be any arbitrary relational expression.  By way of illustration, suppose we have a view LSSP defined as

 

(S WHERE CITY = ‘London’) JOIN SP

 

With our usual sample values, an attempt to insert the row <S6,Green,20,London,P6,100> into this view will succeed (it will have the effect of inserting the row <S6,Green,20,London> into table S and the row <S6,P6,100> into table SP).  More precisely, the first of these two rows -- <S6,Green,20,London> -- will be inserted, not directly into base table S, but rather into the restriction S WHERE CITY = 'London'; the rule for inserting a row into a restriction will then come into play, with the desired final effect.  The point is, however, that the target of the intermediate INSERT is represented by a restriction expression, not by a named relation.

 

It follows that there is no reason why the syntax of the usual INSERT, DELETE, and UPDATE operations need be limited (as it is in SQL today, for example) to designating the relevant target table by means of a table name.  Rather, it should be extended to permit that target to be designated by means of an arbitrary relational expression.

 

(The authors would like to thank Nagraj Alur, Hugh Darwen, Fabian Pascal, and Paul Winsberg for their helpful comments on earlier drafts of this paper.)

 

 

Comments On Republication: Originally published in Database Programming & Design 7, No. 6 (June 1994) and published as a two-part article in RELATIONAL DATABASE WRITINGS1991-94. It is republished here by permission of David McGoveran, Miller Freeman Inc. and Pearson Education, Inc. © All rights reserved by C.J. Date. Research has shown that certain detail level corrections might be needed, which we may undertake in the future. However, we still believe strongly that the overall approach is sound.

 

 

References And Bibliography

 

E. F. Codd: "Recent Investigations in Relational Data Base Systems," Proc. IFIP Congress, Stockholm, Sweden (August 1974).

 

Hugh Darwen: "Without Check Option," in C. J. Date and Hugh Darwen, RELATIONAL DATABASE WRITINGS 1989-1991.  Reading, Mass.: Addison-Wesley (1992).

 

C. J. Date: AN INTRODUCTION TO DATABASE SYSTEMS (6TH EDITION).  Reading, Mass.: Addison-Wesley (1994).

 

C. J. Date: "Notes Toward a Reconstituted Definition of the Relational Model Version 1 (RM/V1)," in C. J. Date and Hugh Darwen, RELATIONAL DATABASE WRITINGS 1989-1991.  Reading, Mass.: Addison-Wesley (1992).

 

David McGoveran: Nothing from Nothing (in four parts), Database Programming & Design 6, No. 12 and 7, Nos. 1-3 (December 1993 - March 1994).

 

Posted 01/24/03

 

 

 

[ABOUT] [QUOTES] [LINKS]