Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

From: Bob Badour <bbadour_at_golden.net>
Date: Wed, 19 Feb 2003 21:13:48 -0500
Message-ID: <YcX4a.103$CO5.10014787_at_mantis.golden.net>


"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:YcQ4a.7$3h1.94_at_news.oracle.com...
>
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:lrD4a.60$Dm4.6034464_at_mantis.golden.net...
> > "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> > news:D0z4a.21$h_3.106_at_news.oracle.com...
> > > select id, 'VOICE' type, voice phone
> > > from contact
> > > union
> > > select id, 'FAX' type, fax phone
> > > from contact
> > >
> > > we can easily see that both assumtions aren't correct:
> > >
> > > 2. Two tuples inserted together into a derived relation correspond to
a
> > > single tuple insertion in the base relation. The approach based upon
> > "single
> > > tuple" translation is flawed.
> >
> > The insert of two tuples one with a fax and one with a voice phone would
> > fail with an error because it would necessarily insert id twice, and
> > presumably id is a candidate key for contact.
>
> That is what I suspected, thanks for confirming it.
>
> > You might not like that result, but the answer to that is: "Use a more
> > sensible design."
>
> That example came from the industry. Database A has
>
> TABLE contacts (
> id NUMBER,
> voice VARCHAR2(10),
> fax VARCHAR2(10)
> );
>
> while database B has
>
> TABLE phones (
> id NUMBER,
> phonetype VARCHAR2(5),
> number VARCHAR2(10)
> );
>
> The "union" view defines a formal mapping between phones and contacts
> relations. Another, selfjoin view defines the reverse mapping between
them.
> Reverse mapping is a crux of formal approach to view updates.
>
> Databases A and B were created by different application vendors, and I
don't
> see any design deficiency here, other than vendor's reluctance to agree
upon
> a single consolidated schema (just kidding).
>
> Database A and B together can be viewed as a distributed database. There
is
> a redundancy here: the data in both tables should be maintained in sync.

Strictly speaking, it's not possible to keep them in sync because a user can insert a phone in phones without a fax. Contacts cannot represent this, and what you claimed was a reverse mapping is not a reverse mapping.

The design issues seem pretty obvious to me.

> That's one paragraph description of what "application integration" vendors
> are doing. (Or rather, should be doing, because, industry's notorious
> inability to distinguish between physical and logical levels drives
vendors
> along some wierd XML/SOAP/Messaging "solution" path:-(.

Hear, hear! Received on Thu Feb 20 2003 - 03:13:48 CET

Original text of this message