Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
Date: Wed, 19 Feb 2003 10:35:08 -0800
Message-ID: <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.