Re: question: approaches to view update problem

Joe Celko wrote:
>
> >> I am looking for existing approaches to the view update problem in
> RDB and OODB. Does anyone know where I could find the most up-to-date
> references that deal with (or, survey) the approaches? <<
>
> The SQL-92 standard is actually very conservative about what VIEWs are
> updatable. They have to be based on:
>
> (1) a SELECT statement on one and only one table; this can go thru
> several layers of VIEWs on top of VIEWs, however.
>
> (2) The VIEW must include all the columns of a key (i.e. a UNIQUE or
> PRIMARY KEY constraint) in the base table
>
> (3) All columns not shown in the VIEW must have default values or be
> NULL-able (if you think about it for a minute, this lets you construct
> and insert a complete row into the base table).
>
> The whole idea is that an updatable VIEW looks and behaves pretty much
> like a base table, but slightly restricted as to what you can see. One
> row in the VIEW maps to exactly one row in the base table from which it
> is drawn.
>
> However, other views are updatable and some vendors support more than
> the basic version given in the SQL-92 Standard. The VIEW must have an
> INSERT, UPDATE and DELETE rule under the covers which maps its rows
> back to a single row in the base table(s).
>
> for practical information with the fastest reading time is Nathan
> Goodman in InfoDB. He discusses:
>
> 1) Projection from a single table
> 2) Selection/Projection from a single table
> 3) UNION VIEWs
> 4) Set Difference VIEWs
> 5) One-to-one joins
> 6) One-to-one outer joins
> 7) One-to-many outer joins
> 8) One-to-one outer joins
> 8) Many-to-many joins
> 9) Translated and coded fields
>
> Codd, E. F.; THE RELATIONAL MODEL FOR DATABASE MANAGEMENT: VERSION
> TWO; "RV-6 VIEW Updating"; (ISBN 0-201-14192-2).
>
> Date, C. J. & Hugh Darwen; RELATIONAL DATABASE WRITINGS -1989-
> 1991; "Role of Functional Dependencies in Query Decomposition"; (ISBN 0-
> 201-54303-6).
>
> Date, C. J.; RELATIONAL DATABASE: SELECTED WRITINGS; "Updating VIEWs";
> (ISBN 0-201-14196-5).
Date would regard this article as obsolete.

> Goodman, Nathan; INFODB Vol 5, No 2 (Summer 1990); "VIEW Update is
> Practical".
>
> Umeshar, Dayal & P. A. Bernstein; ACM TRANSACTIONS ON DATABASE SYSTEMS
> Vol 7, No 3 (Dec 1982); "On the Correct Translation of Update
> Operations on Relational VIEWs".
>
> Another feature, which is not used enough, is the WITH CHECK OPTION
> clause on a VIEW. It is a bit tricky, but if you would like I can post
> a section from SQL FOR SMARTIES on this feature.

An interesting list of references. Date also has a discussion of WITH CHECK
OPTION in one of his SELECTED WRITINGS series.

Much more interesting is Date's treatment of UNION, INTERSECT and DIFFERENCE
views in the 1991-1994 book (work done with David McGoveran). Interestingly,
this book is not listed at Amazon, not even as out of print. My copy is at
home or I'd give the ISBN, etc.

There are 3 chapters on view updating, corresponding to the 3 articles which
originally appears in Database Programming & Design. In combination with
either the 7th Edition of Date's "Introduction to Database Systems" or "The
Third Manifesto", I believe you'd find this to be what Date regards as his current beliefs on view updating. They seem cogent and valid to me.

Do you happen to know where article such as Goodman's INFODB one are available
online?

-- 
Yours,
Jonathan Leffler (Jonathan.Leffler_at_Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"