# 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:

![](https://1736932896-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M5-0RGXrGEYT2sPemdS%2F-M5-0Rgnjb23s9oFr3lT%2F-M5-0_NCCVD7rdNehqY3%2Faggregate.JPG?generation=1586990835935680\&alt=media)

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:

![](https://1736932896-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M5-0RGXrGEYT2sPemdS%2F-M5-0Rgnjb23s9oFr3lT%2F-M5-0_NEA9Vt1qb9vCd7%2Faggregation%20rename.JPG?generation=1586990835751236\&alt=media)

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