Re: Dreaming About Redesigning SQL
Date: Thu, 06 Nov 2003 08:08:11 -0500
Message-ID: <3567017.1068124091_at_dbforums.com>
Originally posted by Mike Preece
> Jonathan Leffler <jleffler_at_earthlink.net> wrote in message
> > Mike Preece wrote:
> > > andrewst <member14183_at_dbforums.com> wrote:
> > > >First of all, as Bob pointed out yesterday, the relational
> > > >allow for relation-valued attributes, so that all the phone
> > > >a person could be stored within the single person record.
> > > >would not be the "traditional" relational approach to
> news:<Jy0qb.549$Z25.266_at_newsread4.news.pas.earthlink.net>...
> model does
> numbers for
> However, that
> this.
> > > Why not? Is there something in relational theory that says
> that would
> > > be wrong in some way?
> > There are two reasons why RVA (relation-valued attributes) are
> not
> > used in the traditional approach.
> > One is the horribly pragmatic point that neither SQL DBMS nor
> any
> > RDBMS (possibly excepting Alphora) actually implement support
> for
> > RVAs, so it is difficult to use in practice what only exists in
> theory
> > (that sounds familiar - c.d.p should be happy with that). The
> main
> > reason that RVAs have not been implemented is that they were
> only
> > recognized as valid in the last 10 years or so, and SQL has been
>
> > standardized rather longer than that (17 years or so).
>
> Why were RVAs intoduced (or accepted) into relational theory in the
> firs..., I mean, 10 years ago?
>
If you really want to understand this, read The Third Manifesto by C J Date and H Darwen. I haven't got it with me and to be honest, I can't remember why. I think it is more a case of accepting it as not subverting the relational model rather than advocating doing it.
> Why haven't any of the enormous SQL-relational DBMS
> companies
> implemented support for them? I would have thought 10 years would have
> been long enough and it's not as though their R&D is under-resouced
> exactly.
>
They have, kind of. But being market driven they have done it as part of making their DBMSs "object oriented". For example, Oracle lets you do this:
CREATE TYPE phone_info
( phone_no VARCHAR2(20)
, phone_type VARCHAR2(1)
, phone_preference VARCHAR2(1)
);
CREATE TYPE phone_list AS TABLE OF phone_info;
CREATE TABLE person
( person_id INTEGER
, person_name VARCHAR2(35)
, phones phone_list
);
INSERT INTO person VALUES
( 123
, 'Andrews'
, phone_list( phone_info('123-123 1234', 'H', 'E'), phone_info('123-456 7890', 'W', 'D') )
);
I may not have the syntax quite right, I never actually do this, but it is right in principle.
> > The other reason, which might apply even if RVAs
> were available for
> > use, is that it introduces an asymmetry between the tables
>
> Yes. That's what we've just been discussing isn't it? I thought we had
> agreed that there *is* an asymmetry in the Persons:Phones relationship
> - "so that all the phone numbers for a person could be stored within
> the single person record".
>
We have agreed that you may consider the relationship to be asymmetric. That isn't the same as saying that is HAS to be stored assymetrically. Your false conclusion is drawn from your erroneous belief that the symmetric relational version is somehow "inefficient". The fact that we may be less interested in the relatonship from the other end doesn't mean we MUST penalise that viewpoint.
>
> > The traditional
> > relational solution treats those queries substantially the
> same.
>
> Substantially? Why is that word in there?
>
Well, obviously the 2 queries are not THE same query...
>
> > Using an RVA, the code for one query is considerably different
> from
> > the other.
>
> Interestingly enough, in Pick they would be:
>
> List Persons Name with PhoneNumber "12345"
> and
> List Persons "Mike" PhoneNumber
>
i.e. syntactically different:
List <table> <attribute1> with <attribute2> <value>
and
List <table> <Value> <attribute2>
In SQL:
select person_name from person_phones where phone_number='12345';
and
select phone_number from person_phones where person_name=Mike';
i.e. in both cases:
select <attribute1> from <table> where <attribute2> = <vaue>;
>
> and would run extremely efficiently regardless of whether Mike's got
> one or a dozen phones or how many of his numbers are shared by other
> people.
>
> > of the relational model (which PICK might also claim - I'm not
> sure)
> > is that if you need to change your mind (so that RVAs are not a
> good
> > idea after all), then you can probably change the database too
> without
> > suspending the entire system.
>
> Well gee it sure doesn't look beautiful to me - but then I'm not
> beholding.
>
Are you saying that you LIKE suspending the entire system?
>
>
> We agree that the person is more relevant in the persons to phones
> relationship.
>
But we disagree over whether that is necessarily relevant to logical database design. We agree that it may be relevant to physical database design, but you don't seem to understand or accept that logical and physical design are different issues.
>
> We agree that the relational model ignores that relevance.
>
>
> Someone says we can use RVAs.
> Someone else says they don't actually exist - and that they would
>
The theory exists. Most well-known SQL products do something like them, but probably not in a way that meets the full requirements of the theory (as usual!) In fact, they were adding their "OO" extensions that are somewhat like RVAs probably before the theory was published.
>
> What? We're trying to get someone's phone number here guys!
>
> It's simple. Pick does it simply. Someone said earlier in this thread
> that "perfection" is important to relational database people. Is it
> really?
-- Posted via http://dbforums.comReceived on Thu Nov 06 2003 - 14:08:11 CET