Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem
From: tom.rmadilo on 5 May 2010 19:21
On May 5, 2:44 pm, Arnold Snarb <asn...(a)fdip.bad-monkeys.org> wrote:
> Arnold Snarb just wrote:
> > tom.rmadilo wrote:
> >> If we had real SQL, as originally defined,
> >> then all views would be updateable
> > Wait, what?
> > [...]
> > but I have *never* heard that "Real SQL" should
> > allow unrestricted updates through views.
>
> > That's just crazy talk!
>
> Well I'll be!  That's actually Codd's Rule 6:
>
> "All views that are theoretically updatable must be updatable"
>            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> I stand corrected.
>
> My apologies to tom.rmadillo, I failed to read between the lines when
> you said that in "real SQL [...] all {theoretically updatable} views
> are {actually} updateable".  The intersquigglebraced elisions
> should have been obvious.
>
> Sorry, won't happen again.

Yes, Rule 6, as explained by Pascal in Chapter 9 of _SQL and
Relational Basics_.

But there are still limitations, just like my example on a single base
table:

"Because views do not have their own data but rather reflect data in
base tables, updating a view actually means changing the data in the
underlying base tables 'through' the view. This is possible only if
the base rows affected and the effect on them can be precisely
identified by the DBMS. As it turns out, this in not possible for all
views.

"Consider, for example, single-table views that contain not just
straight data from base tables but also data _derived_ from the base
table in some way..."

--page 132-133 (section 9.2).

The interesting thing here is that the DBMS could easily determine
that the view is not updateable and if an object system was built,
update operations would not be produced.
From: Kevin Kenny on 7 May 2010 07:38
tom.rmadilo wrote:
> The interesting thing here is that the DBMS could easily determine
> that the view is not updateable and if an object system was built,
> update operations would not be produced.

There's an interesting grey area: view update operations in which the
affected row set and the effect that the update would have upon it
could in theory be determined, but the techniques that a particular
database manager uses are insufficiently powerful to determine them.
I'm reasonably certain that the general question -- in actual SQL,
not Codd's relational calculus -- of determining
all effects of a view update is undecidable, or at the very least
computationally intractable. So the SHOULD in the statement,
"all views SHOULD be updatable" is necessarily a weak constraint.

For that reason, beyond a fairly limited set of constraints, the
standard treats the question of what view update operations are
permitted as a "quality of implementation" issue.

Codd's relational calculus is much more tightly constructed
than SQL. It may well be possible either to update all views
that it can present, or to determine (with reasonable effort)
unambiguously whether a view expressed in it may be updated.
SQL, for weal or woe, has departed from Codd's vision in a number
of significant ways and is considerably thornier to deal with.

--
73 de ke9tv/2, Kevin
First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem