Re: Dreaming About Redesigning SQL

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

Actually, I'm confused here, since Id is in the other table, so it is certainly not a functional index, but looks more like a bitmapped join index. But we don't want to admit anything bitmapped (read non OLTP) in such a simple problem. So the question is how do we index this access path?

As far as file names stored in a table column, the fundamental difference is that files themselves are outside of the database. Anything referring to the outside of the database is a pointer. Person's name is a pointer. It's impossible to maintain referential integrity for such pointers. One can delete a file without letting the database to know, and we get a hanging pointer. A person can change his name without informing the database. The idea is that there is no benefits having pointers inside of the database referencing one table from the other. Received on Thu Oct 09 2003 - 03:47:26 CEST