Re: Dreaming About Redesigning SQL

"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.

     KR

                Pb