Re: Dreaming About Redesigning SQL

From: Bob Badour <bbadour_at_golden.net>
Date: 9 Oct 2003 05:38:20 -0700
Message-ID: <cd3b3cf.0310090438.137218e7_at_posting.google.com>

paul_geoffrey_brown_at_yahoo.com (Paul G. Brown) wrote in message news:<57da7b56.0310082211.79dca238_at_posting.google.com>...

> "Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:<Ze3hb.28$MU.214_at_news.oracle.com>...

> > "Paul G. Brown" <paul_geoffrey_brown_at_yahoo.com> wrote in message
> > news:57da7b56.0310081407.71d1bc8e_at_posting.google.com...
> > > mikharakiri_at_yahoo.com (Mikito Harakiri) wrote in message

>  news:<bdf69bdf.0310080845.4d71a1f0_at_posting.google.com>...

> > > > paul_geoffrey_brown_at_yahoo.com (Paul G. Brown) wrote in message

>  news:<57da7b56.0310080006.47b550a9_at_posting.google.com>...


> > > > >   RELATION Dept ( Id Dept_Id KEY, Name String );


> > > > >   RELATION Emp  ( Id Emp_Id KEY,  Dept REF(Dept),

> > > > >                     Name PersonName, Salary Money );

> > > > >

> > > > >   RETRIEVE E.Name, DEREF(E.Dept).Name FROM Emp E;

> > > > >

> > >

> > >   RETRIEVE E.Name FROM Emp E, Dept D

> > >      WHERE DEREF(E.Dept).Id = D.Id AND D.Name = 'shoe';

> >
> > Now, suppose that indexed nested loops(D, E) is the best access path to the
> > data. Then DBA or advanced ("self-managed";-) DBMS would have to create
> > functional index on DEREF(E.Dept).Id. Then, wouldn't it be just be cleaner
> > to store Id instead of reference in the table itself?

> 
>  [ snip ] 
> 
>    Well, the point of departure here was a discussion about modeling something 
>    at the logical level. Implementation is quite up to you. The simplest 
>    thing to do would be to take a bog-standard RDBMS approach and treat 
>    REF(Dept) as "this says to take the primary key of Dept and embed those 
>    columns invisibly within the Emp table and enforce the necessary RI 
>    constraint rules". At which point  a decision about how to process this 
>    query becomes a bog-standard query problem. 
> 
>    The biggest problems come about when you try to make REF() work in the 
>    absence of a primary key. At that point REF/DEREF goes really feral. In 
>    SQL-3 IIRC, there is this wierd thing where a column can ref to a key in 
>    more than one table but I'm not up to gospel on that. 
> 
>    Look: 'logical pointers' done this way isn't new, and it isn't my idea.
>    Carlo Zaniolo introduced the idea in a 1983 paper called GEM (or something 
>    like that).  Besides, once again, I don't like RED/DEREF because it 
>    introduces redundant syntax. 

It also violates the information principle. Received on Thu Oct 09 2003 - 14:38:20 CEST