Relational Algebra

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

Extended Relational Algebra Operations

There are mainly two kinds of extended relational algebra operations:

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.

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

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.

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

Last updated 4 years ago