Update through views: a seminal paper - appreciation, commentary, critique

My gold standard for expressing principles to do with updating-through-views is Dayal & Bernstein 1978 "On the Updatability of Relational Views", section "Correctness Criteria for View Updates". That's not to say I entirely agree with them, but I'm impressed that they have expressed their approach in one sentence of ~50 words, plus 4 paragraphs to explain each key term. The surprise is how soon this was published after Codd's papers elaborating the RM.

Contrast that Codd's 12 Rules, including the prescription for update-through-view, and definition for "theoretically updatable" was published in 1985. Codd says "Although I have defined each rule in earlier papers, I believe this to be the first occurrence of all twelve of them together.", and cites a paper "conceived in 1981".

Another influential paper of the same vintage, Bancilhon & Spyratos 1981, introduced the "constant complement" approach (which anticipates Date's Principle of Interchangeability).

Correctness Criteria for View Updates

The model sentence:

Given a semantically consistent schema extension $*, we say that an update operation u on view extension V* is translatable if there is a unique set of updates U on $* producing the desired update on V*, such that there are no extraneous updates in U, no side effects on V*, and U preserves semantic consistency.

Explanations:

An update is extraneous in U if U produces the same effect on the view extension with or without the extraneous update.

The set of updates U has no side effects if only the desired update is performed on the view.

A set of update operations on a semantically consistent schema extension preserves semantic consistency if the resulting schema extension is also semantically consistent.

The uniqueness criterion is controversial and requires some justification. This criterion says that the inverse mapping (of the view definition) from view extensions to schema extensions must be a function.

They add:

There is another uncomfortable dimension to the issue of translatability. In our examples, we translated a view insertion only into insertions on the base relations. Should we permit an insertion to map to deletions and replacements as well?

Their conclusion I find elusive (and it appears only at the end of the abstract, rather than in the Conclusion section):

In summary, our theorems show that there are very few situations in which view updates are possible--even fewer, in fact, than intuition might suggest.

How few are "very few"? Compared to what? How few "might intuition suggest"? Was the prevailing opinion at the time that all or nearly all updates through views were possible?

Commentary, Critique

Taking the requirements in sequence presented.

no extraneous updates

In other words (and to put it positively), each "update" in U must be necessary to achieving the required effect on the extension. (But this doesn't say what an "update" is. Presumably any tuple inserted, deleted or replaced. The only example given is a tuple insert into base where a value is to be concocted for an attribute projected out of the view. D&B regard such a value as arbitrary, because any other valid value would do as well. Curiously, they instead propose inserting NULL -- as if that's not arbitrary!) So both tuples and attribute values can be extraneous.

no side effects

That is, the effect on the view must be exactly what was requested in the update. This is "no side effects" in the sense "A secondary, typically undesirable effect" [Oxford online dictionary]. This would be an uncontroversial criterion were it not that:

• Date & McGoveran's 1994 treatment did not require it.

(And Date had to correct that by giving a grand title "Assignment Principle".)

• SQL makes it optional (WITH CHECK OPTION defined on the view) -- that is, with the CHECK opted off, it is possible (for example) to 'successfully' insert tuples that do not subsequently appear in the view. ('Successfully' in the sense there is no error message, and the tuples are inserted into base.)

This is a strange sort of non-optional OPTION: for some views opting off prevents any attempts at certain updates through the view -- even for tuples that could validly appear.

preserves semantic consistency

I.e. all constraints must hold; Date's "Golden Rule". Again uncontroversial. D&B consider only key/Functional Dependencies, not Foreign Key constraints/INclusion Dependencies, nor more exotic constraints. "Since keys are syntactic constructs, they will give us a compile-time check on update translatabililty." [Section 5.] And their detailed workings do not take any opportunity to consider at run-time the content of the base relations.

uniqueness

D&B admit up front this is controversial. The three sentences immediately following the explanation above:

The alternative, of course, is that there are several distinct sets of schema updates that will alter the view extension as desired. The problem in the latter case is how to choose which set to apply. An arbitrary choice seems unacceptable.

I agree "an arbitrary choice seems unacceptable" -- this is the same thinking as rejecting extraneous updates.

As to "how to choose", I think D&B have simply made an error in logic (their "of course" does not follow). For there to be a unique such "set of schema updates", yes it would be sufficient for there to be an inverse mapping from view to schema (base) -- that's an ideal; but it is not necessary. To be unique, all that's needed is there be one set of non-extraneous schema updates that wil alter the view extension as desired. That is, one set that alters the view extension as desired for this particular update request (delete, insert, etc). The inverse mapping criterion amounts to not only achieving this particular request, but also being able to reverse that request, returning both the view and base to status quo ante.

map insertions only into insertions

From later in the explanation:

Some additional semantics are required. We have chosen to map view updates of one type (insertions, deletions, replacements) to schema updates of the same type. Theoretically, this choice is arbitrary. Intuitively, though, it makes good sense ...

I cannot see it makes sense in all cases: the only way to ensure insert-through-MINUS is to insert into the minuend, but delete from the subtrahend -- otherwise the insertion will not appear in the result, i.e. a side effect.

The examples D&B consider here (such as replacing a tuple for a requested insert, in order to preserve semantic consistency) all fail on other of their criteria -- especially causing side effects, or risking causing side effects depending on other content of the base schema.

Furthermore since they're concluding "there are very few situations in which view updates are possible", there is no requirement for "some additional semantics". 'Back off' the semantic requirements, and more views will be updatable-through.

The paper's contribution to Update through views: a possible approach

The preserves semantic consistency ("Golden Rule") and no side effects ("Assignment Principle") are my prescription 1 and 2.

D&B's aim for "compile time check on update translatability" is another implicit criterion/restraint on algorithms. It appears as my prescription 3.

no extraneous updates and unique set of updates are my prescription 4 (necessary and sufficient/essential tuples and attribute values) -- which is more explicit about what constitutes a "set of updates" and what it is for something in that set to be extraneous.

Can I express the prescriptions as succinctly as D&B's criteria?

Given a semantically consistent schema extension $*, we say that an update operation u on view extension V* is translatable if there is a set of tuple inserts, deletes and replacements U on $*, together sufficient to produce the desired update on V*, such that each tuple operation is necessary in U, the precise effect on V* is as requested, U preserves semantic consistency, and the translation is statically determinable purely from the form of the update request, the schema and view definition and the semantic integrity constraints.

References

Dayal and Bernstein 1978 "On the Updatability of Relational Views” https://pdfs.semanticscholar.org/39f4/853a35a913c714c5f3956dbefd02916447c4.pdf

See also Update through views: a possible approach#References