Relational Algebra

Relational Algebra is a procedural language based on the previously discussed relational operators, mainly:

• selection $\sigma$

• projection $\Pi$

• union $\cup$

• set difference -

• cartesian product $\times$

• rename $\rho$

Extended Relational Algebra Operations

There are mainly two kinds of extended relational algebra operations:

• generalized projection

• aggregate functions

Generalized Projection

This is an extension of the projection operation.

It basically allows us to use arithmetic functions in the projection list.

$\Pi _{F-1, F_2, ..., F_n} (E)$

where $F_1, F_2, ..., F_n$ are arithmetic operations involving constants and the attributes in the schema of E.

Ex. $\Pi _{ID, name, department, salary/12} (instructor)$

Note the "/12" in the projection list.

Aggregate Functions

Aggregation functions take a collection of values and return a single value as the result.

• avg: average value

• min: minimum value

• max: maximum value

• sum: sum of values

• count: number of values

are some aggregate functions.

The aggregate operation in relational algebra is denoted as follows:

where the Gs are a list of attributes on which to group (can be empty), the Fs are aggregate functions and the As are attribute names.

The result of the aggregation operation doesn't have a name. We can name the result using the rename operator or as follows:

The above will output the average salary of instructors grouped by department.

Multiset Relational Algebra

Pure relational algebra removes all duplicates, however, multiset relational algebra retains duplicates.

The following explains how the operations differ in multiset relational algebra:

• selection: the result has as many duplicates of a tuple as in the input, if the tuple satisfies the selection

• projection: the result will contain one tuple per input tuple, even if it is a duplicate

• cross product: if there are m copies of t1 in r, and n copies of t2 in s, there will be m x n copies of t1.t2 in r $\times$ s
• union: will have m + n copies

• intersection: will have min(m, n) copies

• difference: will have min(0, m – n) copies