Relational Database Introduction

Relational Operations

Given this simple and restricted data structure, it is possible to define some very powerful relational operators which, from the users' point of view, act in parallel' on all entries in a table simultaneously, although their implementation may require conventional processing.

Codd originally defined eight relational operators.

   1. SELECT originally called RESTRICT
   2. PROJECT
   3. JOIN
   4. PRODUCT
   5. UNION
   6. INTERSECT
   7. DIFFERENCE
   8. DIVIDE

The most important of these are (1), (2), (3) and (8), which, together with some other aggregate functions, are powerful enough to answer a wide range of queries. The eight operators will be described as general procedures - i.e. not in the syntax of SQL or any other relational language. The important point is that they define the result required rather than the detailed process of obtaining it - what but not how.
SELECT
RESTRICTS the rows chosen from a table to those entries with specified attribute values.

     SELECT item
     FROM stock_level
     WHERE quantity > 100

constructs a new, logical table - an unnamed relation - with one column per row (i.e. item) containing all rows from stock_level that satisfy the WHERE clause.
PROJECT
Selects rows made up of a sub-set of columns from a table.

     PROJECT stock_item
     OVER item AND description

produces a new logical table where each row contains only two columns - item and description. The new table will only contain distinct rows from stock_item; i.e. any duplicate rows so formed will be eliminated.
JOIN
Associates entries from two tables on the basis of matching column values.

     JOIN stock_item
     WITH stock_level
     OVER item

It is not necessary for there to be a one-to-one relationship between entries in two tables to be joined - entries which do not match anything will be eliminated from the result, and entries from one table which match several entries in the other will be duplicated the required number of times.

The above definition is actually that of a NATURAL or EQUI-JOIN - i.e. a join in which the values of the matching columns are equal. It has become normal to extend join to include other comparison operators such as less than, greater than, etc. It is important to be clear about one's intentions here to obtain meaningful results. Join is obviously a very general operation, and the principal source of processing power in relational systems, but it is also costly in time and space. Because no ordering can be guaranteed, a join may require a comparison of every entry in one table with every entry in the other, and create large intermediate results. That is why users of large-scale data bases, while acknowledging the power and flexibility of the relational approach, were slow to adopt it instead of methods based on more efficient file processing techniques.
PRODUCT
Builds a relation from two specified relations consisting of all possible combinations of rows, one from each of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A: a     B: d     =>   A product B: a   d
        b        e                       a   e
        c                                b   d
                                         b   e
                                         c   d
                                         c   e

UNION
Builds a relation consisting of all rows appearing in either or both of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A: a     B: a     =>     A union B: a
        b        e                       b
        c                                c
                                         e

INTERSECT
Builds a relation consisting of all rows appearing in both of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A: a     B: a     =>     A intersect B: a
        b        e
        c

DIFFERENCE
Builds a relation consisting of all rows appearing in the first and not in the second of the two relations.

For example, consider two relations, A and B, consisting of rows:

     A: a     B: a     =>  A - B: b   and   B - A: e
        b        e                c
        c

DIVIDE
Takes two relations, one binary and one unary, and builds a relation consisting of all values of one column of the binary relation that match, in the other column, all values in the unary relation.

     A: a  x     B: x     =>     A divide B: a
        a  y        y
        a  z
        b  x
        c  y

Of the relational operators 3.2.4. to 3.2.8.defined by Codd, the most important is DIVISION. For example, suppose table A contains a list of suppliers and commodities, table B a list of all commodities bought by a company. Dividing A by B produces a table listing suppliers who sell all commodities.
Back