Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
Date: Mon, 24 Feb 2003 11:44:55 -0800
Message-ID: <rIu6a.8$i82.99_at_news.oracle.com>
"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
news:b32jok$19em$1_at_sp15at20.hursley.ibm.com...
> Then the 'mapping view' from A -> B, is
>
> select id, 'VOICE' as phonetype, voice as number
> from contact
> union
> select id, 'FAX' as phonetype, fax as number
> from contact
>
<snip>
> the 'mapping view' from B-> A, is
>
> select v.id, v.number as voice, f.number as fax
> from phones V, phones F
> where V.i = F.i
> and V.phonetype = 'VOICE'
> and F.phonetype = 'FAX'
>
> Now a RDBMS that supported all that would be top.
> Especially if it supported:
> Deriving the 2 mapping views solely from the 2 tables.
My kneejerk reaction to that is "Not possible". Imagine a view A->B inverting phone numbers like this 650-123-4567 -> 4567-123-650. There is no way to know this solely from tables+constrains.
> Deriving table B and mapping B -> A from table A and the A->B
mapping.
> If A, B and A-> B are declared by a user, the DBMS should be able to
check
> the correctness of the mapping.
I can't derive one view from another, yet. I can check if one view is indeed the inverse of the other. Here is a composition of (B->A)*(A->B):
select v.id, v.number as voice, f.number as fax
from (
select id, 'VOICE' as phonetype, voice as number
from contact
union
select id, 'FAX' as phonetype, fax as number
from contact
) V, (
select id, 'VOICE' as phonetype, voice as number
from contact
union
select id, 'FAX' as phonetype, fax as number
from contact
) F
where V.i = F.i
and V.phonetype = 'VOICE'
and F.phonetype = 'FAX'
Step #1. Push "where V.phonetype = 'VOICE'" predicate into the inner view V. The result:
select v.id, v.number as voice, f.number as fax
from (
select id, 'VOICE' as phonetype, voice as number
from contact
) V, (
select id, 'VOICE' as phonetype, voice as number
from contact
union
select id, 'FAX' as phonetype, fax as number
from contact
) F
where V.i = F.i
and F.phonetype = 'FAX'
2. Push "where F.phonetype = 'FAX'" predicate into the inner view F. The result:
select v.id, v.number as voice, f.number as fax
from (
select id, 'VOICE' as phonetype, voice as number
from contact
) V, (
select id, 'FAX' as phonetype, fax as number
from contact
) F
where V.i = F.i
3. Project away the phonetype columns that are not used in the outer view:
select v.id, v.number as voice, f.number as fax
from (
select id, voice as number from contact
) V, (
select id, fax as number from contact
) F
where V.i = F.i
4. Straighten up intermediate aliasing, where "voice->number->voice" would become just "voice":
select v.id, v.voice, f.fax as fax
from (
select id, voice from contact
) V, (
select id, fax from contact
) F
where V.i = F.i
5. Use the PK to remove unnecessary join:
select id, voice, fax from contact
We just proved:
(B->A)*(A->B) = 1
Likewise:
(A->B)*(B->A) = 1
Note, that what we just did is very similar to proving
X->Y: 5x+3 = y
is inverse to
Y->X: (y-3)/5 = x
Indeed ((5x+3)-3)/5 = x is easily reduced to identity.
Once again, view updates is about solving equations with relational algebra operators.
> FYI In DB2 we can insert into UNION views, but it's strictly a one tuple
in
> the view to one tuple in one table affair. I could use INSTEAD OF
triggers,
> but to map the two inserts into one could still be difficult.
Or, is it even possible?
Overall, you made a good point. I had a wrong idea that INSTEAD-OF triggers [which is admittedly a rather low-tech approach] resolves any view update problem. Not everything can be done on per row basis! Received on Mon Feb 24 2003 - 20:44:55 CET