Is relational theory irrelevant? (was Re: Dreaming About Redesigning SQL)

andrewst <member14183_at_dbforums.com> wrote in message news:<3567017.1068124091_at_dbforums.com>...
> Originally posted by Mike Preece
> > Jonathan Leffler <jleffler_at_earthlink.net> wrote in message
> > news:<Jy0qb.549$Z25.266_at_newsread4.news.pas.earthlink.net>...
> > > Mike Preece wrote:
> > > > andrewst <member14183_at_dbforums.com> wrote:
> > > > >First of all, as Bob pointed out yesterday, the relational model does
> > > > >allow for relation-valued attributes, so that all the phone numbers for
> > > > >a person could be stored within the single person record. However, that
> > > > >would not be the "traditional" relational approach to 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,

Not really, to be honest. From what I've read I'm pretty sure we've had a much better way of doing this in Pick for more than 30 years - with multivalued attributes. I wonder where Date & Darwen got the idea.

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

I wonder how many more things they and others will come to accept in time to come. I guess it might just help someone to come up with the first "true" relational database. "It's relational Jim - just not as we know 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.

Thanks for that. I am learning and I appreciate it. I might come across as bitter occasionally - but bad boy Bob does get on my tits sometimes.

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

Agreed.

> Your false conclusion is drawn from your erroneous belief that the
> symmetric relational version is somehow "inefficient".

Yes.

> The fact that we
> may be less interested in the relatonship from the other end doesn't
> mean we MUST penalise that viewpoint.

Ideally.

> > > 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>;

OK. Btw - if you consider this syntax thing important (I don't), we could...
List Persons PhoneNumber with a0 = "Mike"

> > 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.
> So storing the data assymetrically has no impact on performance then,
> good or bad?

It's logical to store phone numbers against a person. It's also more efficient.

> > > And one of the beauties
> > > 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?

Ummm.. Gee, I don't think so. Maybe you're thinking back to when I said we could add a second phone number when we only ever had one before and add a brand new attribute (column) to an item (row) without having to change any other items. Just to clarify - we could do that while the file is open and in use. There *are* times when we would have to suspend the entire system. I think. I'll have to come back to you on that. I could tell you about my data abstraction layer and how it allows you to totally redesign the underlying database while it's in use - but that's not something you get out of the box with Pick (and I'm still perfecting it).

> > Let's look at the evidence:
> > 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.

I thought we agreed that when we were concentrating only on logical 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.

It just so happens that the physical implementation is a "natural" one in Pick. If you decide it's logical to record phone numbers against a person - then that's what you do in Pick and that's how it's stored physically. I'm sorry if you folks have a hard time accepting that that makes sense. Maybe it's because it's nigh on impossible to do in relational without using RVAs.

> > We agree that the relational model ignores that relevance.
> Yes. But we see that as a good thing whereas you believe it MUST be a
> bad thing. Hmm, let's see: who is best placed to judge relational on
> that score?

Well, I reckon it has to be someone with an open mind. Let's ask Bob. Maybe not. Let's just think about it. Maybe it's time for a new thread. This one has gone OT.

> > Someone says we can use RVAs.
> > Someone else says they don't actually exist - and that they would
> > complicate things too much.
> 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!)

And yet the theory is rock solid right? Well, I guess all of the people that have attempted to implement it must just have been downright incompetent.

> In fact, they were adding their "OO" extensions that are
> somewhat like RVAs probably before the theory was published.

That's no surprise here. People were using Pick before the rules were made up too. Now we get beaten over the head because Pick appears to break the rules. It doesn't matter though really. The theory will catch up with reality on day. Maybe.

> > 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?
> If all you want is a phone number you can use a notepad. I should
> bloody hope Pick does it simply! So does relational; how could it not?

It depends whose number I'm interested in. I have a notepad, sure. In it I have a single entry for a person's (or organisation's) name with all of their phone numbers (home, work, mobile, fax) listed against that single entry. If the number I want isn't in there I'll probably use a telephone directory. They also have a single entry for a name with multiple phone numbers. I also have Pick running on my computer which allows me to store things in exactly the same way. Maybe it's wrong for Pick to store things that way because it's both logical and physical and apparently that's wrong(?). Anyway, it does allow me to type in something like:

List Persons "Mike" Name PhoneNumber PhoneType PhoneUsage

and get the information I want just about as efficiently as possible. I just can't think how that could be improved upon - it couldn't be much simpler and I can't think how you could design a DBMS that gets this data more efficiently than a single direct read of a 2k frame from disk (irrespective of the size of the Phones file). Oh I get it - Bob's 8-fold improvement jiggery pokery trick? Ha. Haha. Ha. Hmmm... I guess my sense of humour must be different to his. Seriously though - what is the equivalent SQL statement to get the same information? Would the statement be identical whether the data is coming from the PersonsPhones table or from RVAs in the Persons file? I'm a bit confused now too - according to Bob perfection doesn't seem to be all that important after all. Bob is an authority you folks all look up to and respect isn't he? Received on Fri Nov 07 2003 - 12:32:51 CET