# 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

Last updated