Re: more on delete from join

On Aug 27, 4:24 am, Nilone <rea..._at_gmail.com> wrote:
> On Aug 26, 5:16 pm, Kevin Kirkpatrick <kvnkrkpt..._at_gmail.com> wrote:
>
>
>
>
>
> > On Aug 25, 9:47 pm, paul c <toledobythe..._at_oohay.ac> wrote:
>
> > > Bob Badour wrote:
> > > > paul c wrote:
>
> > > >> Mr. Scott wrote:
>
> > > ...
> > > >>> I don't think this is right.  It assumes that a delete from J
> > > >>> translates into a delete from both A and B, when a delete from either
> > > >>> A or B would suffice.  ...
> > > >> Yes, it does make that assumption, consequence of logical independence
> > > >> I think.
>
> > > > Preservation of symmetry as well. However, I see no theory to support
> > > > either choice, which is why I prefer the pragmatism of allowing an
> > > > expert user to specify how the delete should operate in this situation.
>
> > > No argument that people who design the predicates in the first place
> > > should be able to trump any dbms conventions they want to, although I
> > > doubt if more than a few at most of today's dbms'es can express
> > > constraints flexibly enough to always allow that.  Maybe I'm talking
> > > about a convention rather than theory, but my basic point seems logical
> > > to me and might go like this:  While a relation represents the extension
> > > of a predicate, there is no way for a dbms designer (as opposed to a db
> > > designer) to choose whether a particular relation value formed by <OR>
> > > or UNION has a predicate that involves disjunction, at least in the
> > > absence of explicit constraints to prevent or void results that are not
> > > wanted.  Since all propositions represented by the tuples of a relation
> > > (at least tuples in named relations) are taken to be true as far as that
> > > relation is concerned (maybe false in the face of other relations, but
> > > if false, they're not individually false, rather their logical
> > > conjunction is false), the fact that a named relation might have been
> > > formed by means of disjunction isn't of any consistent use to a
> > > mechanical engine.  Most base relations are formed with disjunction and
> > > I don't see why views should be treated differently.  In the absence of
> > > contrary constraints, it seems most reasonable to me for a dbms, say
> > > within the scope of a named view, to 'assume' identical predicates for
> > > relations with equal headers.
>
> > > My view of RT is extremely narrow, partly to avoid mysticism and partly
> > > to see the minimum machinery a dbms really needs.  I think a relation's
> > > 'definition' consists of nothing more than a header and constraints and
> > > the constraints must all be expressible in the chosen algebra (this
> > > doesn't mean I think the dbms needs to directly execute the algebra).
>
> > By the narrowest interpretation of RT, shouldn't the whole "view
> > update" problem just be tossed out once and for all?  Tuples in base
> > relvars represent business meaningful propositions, and tuples in
> > virtural relvars represent business meaningful conclusions drawn from
> > base relvar propositions.    As such, view updates amount to letting
> > end-users assert conclusions and having the DBMS make "educated
> > guesses" at the appropriate modifications to business meaningful
> > propositions such that those conclusions will be reached.  Seems
> > inherently mystical to me.
>
> > In short, before delving into, "*how* should the DBMS handle view
> > updates?", I'd like to see a discussion about the question, "*should*
> > the DBMS handle view updates?".
>
> Let's clearly separate the relational model from the DBMS.  The
> relational model requires no addition or modification to support
> updateable views, since a view is just another relvar.  The problem is
> not in updating the view (which is considered transient by the DBMS),
> but in updating the persistent base relations accordingly.  As such,
> it resembles constraint satisfaction problems, a completely different
> (but related) branch of logic.
>

I'll have to disagree. Updating a base table is asserting/denying propositions which are part of the data model. Updating a view is asserting conclusions that should be reachable by the database, and hoping the dbms correctly guesses at appropriate changes to the propositions such that the new conclusion will be reached.

Please tell me how there is a theoretically correct way to handle this real-life example:

SQL> CREATE TABLE T1 (C1 NUMBER, C2 NUMBER)   2 /

Table created.

SQL> CREATE VIEW V1 AS SELECT C1 FROM T1 WHERE C1 < 10   2 /

View created.

SQL> INSERT INTO V1 VALUES (10)
  2 /

1 row created.

I mean, on how many levels is this just *wrong*?

> Whether the DBMS should support updateable views depends on the
> implementer, I'd say, but it's certainly a powerful and useful feature
> that I want in any DBMS I use.  Even though the derivation of a view
> is not always reversible, it is still useful when it is, and I can
> often design a view to be so.

Similarly, table triggers are powerful and "useful" features... but the question that allows me to say, "leave em out" applies to updatable views, too: can the same ends be accomplished with means that are on theoretically sound ground?

>
> My biggest frustration with views in existing products is not that
> some views aren't updateable, but that it is difficult to see whether
> a view is updateable or not, based on the syntax of query.- Hide quoted text -
>
> - Show quoted text -

Reminds me of some of my biggest frustration with triggers in existing products. Received on Thu Aug 27 2009 - 21:07:50 CEST