15.00 - Logical Operations on Relations - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Logical Operations on Relations

Relations are formally decomposed and constructed using logical relational operators and the relational algebra. The relational algebra is a set of procedural constructs for manipulating relations, while the relational calculus is a set of nonprocedural constructs for performing the same operations (SQL is a somewhat uneasy combination of both the algebra and the calculus). Codd, with his so‑called reduction formula, sometimes referred to as Codd’s theorem, proved the equivalence of the relational algebra and the relational calculus. Codd was apparently not aware of the distantly related work on relation algebras and relation calculi that Chin and Tarski had developed 20 years earlier, nor that Schröder had developed the first relation algebra nearly 80 years earlier! This does not detract from the later development by Codd of the relational algebra and calculus, because his work is a very distant relative of the various relation algebras and calculi developed in various fields of pure mathematics.

The first four operators in the following table derive directly from set theory, while the last four were developed by Codd specifically to suit the needs of relational databases.

Although algebras of relations have existed in one form or another since the time that set theory became a discipline of mathematics, it was only with the work of the great logician Alfred Tarski that relation algebra began to be studied seriously. Codd was also apparently not aware that several workers had begun similar work earlier in the same decade. Subsequent to the relational algebra developed by Codd, the relational algebra has continued to develop, particularly through the work of C.J. Date and his colleagues. These workers have extended the relational algebra to allow it to perform computations, among other things, a capability the operators introduced by Codd did not have.

Set Theory Operators

 

Logical Operator

Description

DIFFERENCE

X - Y

The set of all attributes contained in relation X but not in relation Y.

INTERSECTION

X Y

The set of all attributes contained in both relation X and relation Y.

PRODUCT

X Y

The set of all multiples of all attributes contained in relations X and Y.

UNION

X Y

The set of all attributes contained in either relation X or relation Y or both.

Special Relational Database Operators

 

Logical Operator

Description

DIVIDE

The division of relation R of degree m + n by relation S of degree n produces a quotient relation of degree m.

JOIN

The join of relation R on attribute X with relation S on attribute Y is the set of all tuples t such that the concatenation of a tuple r, belonging to R, and a tuple s, belonging to S, and the predicate r.R equality_operator s.S evaluates to TRUE.

In SQL, this is expressed in an ON clause in a DML join statement.

PROJECT

The projection of relation R on attributes X, Y, …, n is the set of all tuples (x, y, …, n) such that a tuple n appears in R with X value x, Y value y, and so on.

Less formally, a projection on relation R is any subset of the attributes of R.

In SQL, this is expressed as a column list in a DML statement.

RESTRICT/
SELECT

The restriction of relation R is the set of all tuples t such that the comparison t.X operator t.Y evaluates to TRUE.

Less formally, a restriction (or selection) on relation R is any subset of the tuples of R satisfying the condition X equality_operator Y.

In SQL, this is expressed in a WHERE clause in a DML statement.