**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**