Re: The Practical Benefits of the Relational Model

"D Guntermann" <guntermann_at_hotmail.com> wrote in message news:<H3H9AA.Jtp_at_news.boeing.com>...
> Union, Intersection, Difference, Product, Restrict, Project, Join, Divide
> operators are considered the first of a set of formal relational operators
> (Rename can be considered also) in that they are operators on relations.
 

> Why do you say that the Intersection is not a relational operator? Are you
> stating this because the intersection operator is not primitive in nature?

Chapter 4 of Alice&Riccardo&Sergio&Vittorio book describes SPC Algebra for conjunctive queries. (I apologize for guessing folklore name of the book; corrections are welcome). Selection, Projection, and Cartesian product are primitive operations, while few others - intersection, for example - could be expressed as a composition of primitive ones. On the other hand, union is a notable exception, so that it is introduced as an additional primitive operation to form SPCU Algebra. IMHO, the fact that union is so different from intersection is very disturbing. They are dual operation in the traditional set theory: unions and intersections in any tautology formula can be interchanged and you'll get another tautology. If they are so symmetric, why they are so different in the relational theory?

Some time ago I asked this question Vadim Tropashko from Oracle. He cooked an explanation involving a new(!) operation, that is dual to Cartesian Product.

When a relation is viewed as a matrix, it's easy to notice that "rows" and "columns" are dual entities. This ideal symmetry seems to be broken by the special treatment of the header "row". If we add a special Id column, the symmetry is somewhat (at least visually) restored.

EMAILS Id | Name Email
---+------------
 1 | Pete p_at_b.c
 2 | Kate k_at_b.c

PHONES Id | Name Phone
---+------------
 3 | Pete (650)
 4 | Jane (415)

Now let's build a cartesian product out of the above relations. To add some fun we consider headers as ordinary rows:

Cartesian Product (CP)

Id | Name | Email | Id | Name | Phone
---+------+-------+----+------+------
Id | Name | Email | 3 | Pete | (650)
Id | Name | Email | 4 | Jane | (415)
---+------+-------+----+------+------
 1 | Pete | p_at_b.c | Id | Name | Phone  2 | Kate | k_at_b.c | Id | Name | Phone ---+------+-------+----+------+------

 1 | Pete | p_at_b.c |  3 | Pete | (650)
 1 | Pete | p_at_b.c |  4 | Jane | (415)
 2 | Kate | k_at_b.c |  3 | Pete | (650)
 2 | Kate | k_at_b.c |  4 | Jane | (415)

Now we build a Naisetrac Product as follows:

Naisetrac Product (NP)

Id | Id | Id | Name | Name | Name | Email | Email | Email Id | Name | Phone | Id | Name | Phone | Id | Name | Phone ---+------+-------+------+------+-------+-------+-------+------

 1 |   1  |   1   | Pete | Pete | Pete  | p_at_b.c | p_at_b.c | p_at_b.c 
 2 |   2  |   2   | Kate | Kate | Kate  | k_at_b.c | k_at_b.c | k_at_b.c 
 3 | Pete | (650) |   3  | Pete | (650) |   3   | Pete  | (650)
 4 | Jane | (415) |   4  | Jane | (415) |   4   | Jane  | (415)

If we project the Naisetrac Product relation to {Name x Name, Email x Phone} columns, then we get a union:

Name | Email
Name | Phone
-----+------

Pete | p_at_b.c 
Kate | k_at_b.c 
Pete | (650)
Jane | (415)

Therefore, the pairs of dual relational operations are:

1. Intersection is dual to Cartesian Product.
2. Union is dual to Naisetrac Product.
3. Selection is dual to Projection.