While working on a db core on these last years, one of the question
that gave me most difficulty was what would be an efficient taxonomy
to list prequisites for a system to support updateable views. I would
be happy to exchange on that subject.

On Oct 2, 9:29 am, Cimode <cim... (AT) hotmail (DOT) com> wrote:
View updatability is a very interesting area imo. Perhaps you would
like to offer your initial thoughts?

On Oct 4, 1:21 am, JOG <j... (AT) cs (DOT) nott.ac.uk> wrote:
Sure...A little history to clarify the viewpoint...Some years ago, I
started coding an assembler db core that would respect as much as
possible the fundamental principle of independence between logical and
physical layer as defined in RM. The purpose of that effort was to
identify some computing (non fundamental) issues that would be faced
by any developper that would attempt to develop a trdbms...

The primary serious issue I faced was to attempt to establish a
computing model that would *coherently* support the concept of unique
tuple identification *without* using directly physical pointers. The
idea was to establish a *logical* solution to the problem of how a
tuple could be identified within a given domain of value or a specific
relation. At that point, I used some trigonometrics to establish
mathematical functions that establish *discernability* over elements
in a degree N relation. I started first with a degree 2 relation
tuple that I reduced to a single numeric value. Then, that function
could allow to uniquely identify that specific tuple and that tuple
only single value (in fact, it could reestablish the identifiers of
the 2 value of each separate attributes within their respective
domain). I later attempted to determine a manthematical function that
would allow a generalization of the property of discernability to a
degree N relation tuple.

For testing such solution and making sure it does not have aspects I
have not thought of, I started imagining a test scenario that would
reveal the limits of such solution through how it would be (or not be)
practical to allow the support of view updates in a system. So I
started focusing on establishing a taxonomy of requirements that would
allow view updates. So here's the taxonomy I came up with so far:

So far I have focused onto answering the first part and here is what I
came up with:

Identification requirements

I really hope this makes sense. I find it more and more difficult to
use online media to precisely explain what I am getting at.

18.02. Updatable and Read-Only VIEWs

Unlike base tables, VIEWs are either updatable or read-only, but not
both. INSERT, UPDATE, and DELETE operations are allowed on updatable
VIEWs and base tables, subject to any other constraints. INSERT,
UPDATE, and DELETE are not allowed on read-only VIEWs, but you can
change their base tables, as you would expect.

An updatable VIEW is one that can have each of its rows associated
with exactly one row in an underlying base table. When the VIEW is
changed, the changes pass through the VIEW to that underlying base
table unambiguously. Updatable VIEWs in Standard SQL are defined only
for queries that meet these criteria

1) They are built on only one table,
2) No GROUP BY clause
3) No HAVING clause
4) No aggregate functions
5) No calculated columns
6) No UNION, INTERSECT or EXCEPT
7) No SELECT DISTINCT clause
8) Any columns excluded from the VIEW must be NULL-able or have a
DEFAULT in the base table, so that a whole row can be constructed for
insertion.

By implication, the VIEW must also contain a key of the table. In
short, we are absolutely sure that each row in the VIEW maps back to
one and only one row in the base table.

Some updating is handled by the CASCADE option in the referential
integrity constraints on the base tables, not by the VIEW
declaration.

The definition of updatability in Standard SQL is actually pretty
limited, but very safe. The database system could look at information
it has in the referential integrity constraints to widen the set of
allowed updatable VIEWs. You will find that some implementations are
now doing just that, but it is not common yet. The SQL standard
definition of an updatable VIEW is actually a subset of the possible
updatable VIEWs, and a very small subset at that. The major advantage
of this definition is that it is based on syntax and not semantics.
For example, these VIEWs are logically identical:

CREATE VIEW Foo1 -- updatable, has a key!
AS SELECT *
FROM Foobar
WHERE x IN (1,2);

CREATE VIEW Foo2 -- not updateable!
AS SELECT *
FROM Foobar
WHERE x = 1
UNION ALL
SELECT *
FROM Foobar
WHERE x = 2;

But Foo1 is updateable and Foo2 is not. While I know of no formal
proof, I suspect that determining if a complex query resolves to an
updatable query for allowed sets of data values possible in the table
is an NP-complete problem.

Without going into details, here is a list of types of queries that
can yield updatable VIEWs, as taken from "VIEW Update Is Practical",
(Goodman 1990):

1. Projection from a single table (Standard SQL)
2. Restriction/projection from a single table (Standard SQL)
3. UNION VIEWs
4. Set difference VIEWs
5. One-to-one joins
6. One-to-one outer joins
7. One-to-many joins
8. One-to-many outer joins
9. Many-to-many joins
10. Translated and coded fields

The CREATE TRIGGER mechanism for tables an action to be performed
BEFORE, AFTER, or INSTEAD OF a regular INSERT, UPDATE, or DELETE to
that table. It is possible for a user to write INSTEAD OF triggers on
VIEWs, which catch the changes and route them to the base tables that
make up the VIEW. The database designer has complete control over the
way VIEWs are handled.

Quoth -CELKO-:
Base tables are both updatable and read-only?
--
Jon