# Re: The Practical Benefits of the Relational Model

From: John Jacob <jingleheimerschmitt_at_hotmail.com>
Date: 3 Oct 2002 18:35:51 -0700

Paul,

> Out of interest, how does D4 insert into this view?
>
> (A UNION B) MINUS (A INTERSECT B)
>
> It seems to me to be entirely arbitrary to insert into A rather than B, or
> vis-versa, and only slightly less arbitrary to not allow any inserts at
> all.

Here is the D4 code I used to recreate your sample against the 1.1 version of the DAE:

create table A { ID : Integer, key { ID } }; create table B { ID : Integer, key { ID } };

insert table { row { 1 ID }, row { 2 ID } } into A; insert table { row { 2 ID }, row { 3 ID }, row { 4 ID }, row { 5 ID } } into B;

select (A union B) minus (A intersect B);

The result of this select is, of course:

ID

```--
1
3
4
5

I would like to address this by considering each operator in turn,
then the whole view. I apologize in advance if I sound patronizing,
that is not my intention, I merely wish to spell out exactly what the
view update mechanism is doing.

Consider A union B. The result of this is obviously anything that is
in A or in B, possibly both. So if we insert into this view, then we
are saying I want to add something that could be in A, or B, or both.
If this is true, then why is it arbitrary to say it goes into A or B,
or even both. That is precisely what the union operator is doing. If
the row can be inserted into A, it is. If the row can be inserted into
B, it is. If it happened to be inserted into both, that's precisely
what we asked it to do. The result is that the row now appears in the
union, so we have indeed inserted it.

Now consider A intersect B. The result of this is only rows that are
both in A and in B. Following the same logic as above, if we insert
into this view, then we are saying I want to add something that must
be in A and in B. Therefore if the row cannot be inserted into both
sides of the intersection, it is rejected.

Now consider A minus B. The result of this is rows that are in A, but
not in B. The english equivalent of insertion is I want to add
something that must be in A, but not in B. Therefore if the row can be
inserted into B, it is rejected, otherwise, it is inserted into A.

Analogous remarks apply to the delete and update cases for each of
these operators.

Now all we have to do is apply the rules for each operator down
through the expression:

insert table { row { 6 ID } }
into (A union B) minus (A intersect B);

Since the row { 6 ID } can be inserted into the intersection of A with
B, i.e. it satisfies both predicates, the insertion is rejected on the
grounds that it violates the difference predicate.

Here is the result from the DAE:

New row violates difference predicate.

insert table { row { 2 ID } }
into (A union B) minus (A intersect B);

The behavior here is slightly different, although this is an anomoly
with our insert operator:

Violation of insert constraint "insert.key{ID}".

The row { 2 ID } cannot be inserted into the intersection of A with B,
because it results in a key violation, so it is considered acceptable
for insertion into the other side of the view, A union B, but this
also causes a key violation. The resulting behavior is correct (namely
that the insert is rejected), but for the wrong reason. The insertion
of the row { 2 ID } into the intersection of A with B should succeed
because the row is already in the view. We do not check if the entire
row is the same before raising the key violation error, which is
different than the behavior that would occur if the statement were
executed long hand, i.e. A := A union table { row { 2 ID } }. We will
fix this problem as soon as possible, although I must note that the
situation is not likely to arise in practice.

In either case, all inserts into this view will be rejected because
there is no way to distinguish between a row which belongs in A, and a
row which belongs in B. If there is a way to distinguish between the
two, then the insert will succeed, in general.