Introduction

On Monday, we began talking about the relational algebra.
We said an algebra consists of expressions built out of constants or variables using a set of operators.
For arithmetic, our variables represent numbers, we might have the constants 0, 1, 2, and the operators might be +, *. An expression might look like ((2 + 3) * x).
In the relational algebra, our variables represent relations, our constants are fixed finite relations, for example, {("Bob", "SMITH", 21, 10), ("Sally", "Reynolds", 93, 15)}, and we have various operations on such relations to produce new relations.
On Monday, we gave an overview of the four classes of such operations: those based on sets, those that remove part of a relation, variations on Cartesian product, and renaming.
Today, we look at these in more detail.

Set Operations on Relations

The three most common operations on sets are union, intersection, and difference. For relations, we will use the following notations and definitions for their meaning:

`R \cup S := { t | t \in R vv t \in S }`. A tuple `t` is in `R \cup S` iff it belongs to either `R` or to `S`.
`R \cap S := { t | t \in R ^^ t \in S}`. A tuple `t` is in `R \cap S` iff it belongs to both `R` and `S`.
`R - S := { t | t \in R ^^ t !in S}`. A tuple `t` is in `R - S` iff it belongs in `R` and not in `S`.

In-Class Exercise

Suppose we have the relations, `R`:

{('Bobby Fisher', '123 Maple St, Hollywood', M, '9/9/99'), 
('Angelina Jolie', '456 Oak Rd., Brentwood' F, '8/8/88')}

and `S`:

{('Bobby Fisher', '123 Maple St, Hollywood', M, '9/9/99'), 
('Catherine DeNeuve', 'Somewhere in France' F, '8/8/60')}.

Compute `R cup S`, `R cap S`, and `R -S`.
Post your solutions to the Sep 12 In-Class Exercise Thread.

Projection

The projection operator is used to produce from a relation `R`, a new relation that has only some of `R`'s columns
The value of the expression `pi_{A_1, A_2, ..., A_n}(R)` is a relation that has only the columns for attributes `A_1, A_2, ..., A_n` of `R`.
The schema for the resulting relation is the set of attributes `{A_1, A_2, ..., A_n}`.

For example, consider the Movies(title, year, length, genre, studioName, producerC#) relation:

{('Star Wars', 1977, 124, 'sciFi', 'Fox' 12345),
('Galaxy Quest', 1999, 104, 'comedy', 'DreamWorks', 67890),
('Waynes World', 1992, 95, 'comedy', 'Paramount', 99999)}.

The result of `pi_{mbox(title),mbox(year),mbox(length)}(mbox(Movies))` is:

{('Star Wars', 1977, 124),
('Galaxy Quest', 1999, 104),
('Waynes World', 1992, 95)}.

The result of `pi_{mbox(genre)}(mbox(Movies))` is:

{('sciFi'),
('comedy')}. //notice its a set, so only one copy of comedy

Selection

The selection operator applied to a relation `R` produces a new relation with the subset of `R`'s tuples which match the selection condition.
If `C` denotes the condition, then this would be written as `sigma_C(R)`.
A condition can either be a comparison of an attribute of `R` to some value, for example, `mbox(length) ge 100`, or is built out of sub-conditions using AND, OR, and NOT, for example, `mbox(length) ge 100` AND `mbox(studioName)=\'mbox(Fox)\'`.
The relation output by the selection operation:
`sigma_{mbox(length) ge 100 mbox( AND ) mbox(studioName)=\'mbox(Fox)\'}(mbox(Movies))`
using the Movies table of the previous slide is:
```
{('Star Wars', 1977, 124, 'sciFi', 'Fox' 12345)}.
```

Cartesian Product

The Cartesian product of `R(A_1, ..., A_n)` and `S(B_1, ..., B_m)` is denoted `R times S(A_1,...,A_n,B_1,...,B_m)`.
It is defined as:
`R times S := {(a_1, ..., a_n, b_1, ..., b_m) | (a_1, ..., a_n) in R ^^ (b_1, ..., b_m) in S}.`
For example, suppose
`R(A_1, A_2) := {(1,1), (1,2), (1,3)}`
`S(B_1, B_2, B_3) := {(a,a,a), (a,b,c)}`
then `R times S(A_1, A_2, B_1, B_2, B_3)` will be:
{(1,1,a,a,a), (1,1,a,b,c), (1,2,a,a,a), (1,2,a,b,c), (1,3,a,a,a), (1,3,a,b,c)}.

Natural Joins

Usually, when we want to combine tuples from two relations, we want to combine them only if they match in some way.
On Hw1, we already considered the simplest kind of such match, the natural join of two relations `R(A_1,..., A_n, C_1,...C_t)` and `S(C_1,...,C_t, B_1,...,B_m)`, denoted $R \bowtie S(A_1,..., A_n, C_1,...C_t,B_1,...,B_m)$.
It is defined as:
$R \bowtie S(A_1,\ldots, A_n, C_1,\ldots,C_t,B_1,\ldots,B_m) :=\\ \quad\{(a_1,\ldots,a_n, c_1,\ldots,c_t, b_1,\ldots,b_m) | (a_1,\ldots,a_n, c_1,\ldots,c_t) \in R \wedge (c_1,\ldots,c_t, b_1,\ldots,b_m) \in S\}$
Note in the above the values `c_1,..., c_t` have to be the same in the tuples in `R` and `S`.
For example, the natural join of `R(A,B):= {(1,2),(3,4)}` and `S(B,C,D):={(2,5,6), (4,7,8), (9,10, 11)}` is `{(1,2,5,6), (3,4,7,8)}`.
It is possible for the natural join to return the empty relation, or for a tuple in `R` to join with more than one tuple in `S` and vice versa.
A tuple in either relation that fails to pair with any tuple in the other is called a dangling tuple.
If no attributes are shared by `R` and `S`, then the natural join is the same as a Cartesian product.

Theta Joins

The natural join forces us to pair tuples using one specific condition.
It is sometimes useful to combine tuples based on some other condition `C`.
A join using some other condition is called a theta-join. The name comes from the fact that, traditionally, rather than use `C` to indicate the condition, people used `theta`.
To compute the theta-join of `R` and `S` on condition `C`, $R \bowtie_C S$, we do the following:
1. Compute `P :=R times S`.
2. Compute `sigma_C(P)`.
We assume the attributes of `R` and `S` are disjoint. If not, say `R` and `S` share attribute `A`, then in the result we write `R.A` and `S.A` to distinguish them.
For example, if `U(A,B,C) := {(1,2,3),(6,7,8), (9,7,8)}` and `V(B,C,D) := {(2,3,4), (2,3,5), (7,8,10)}`. Then $U\bowtie_{A \lt D \mbox{ AND } U.B\neq V.B}V$ has attributes `A, U.B, U.C, V.B, V.C, D` and contains `{(1,2,3,7,8,10)}`.

Combining Operations to Form Queries

Given our base operators, we can compose them to answer complicated queries.
For example, consider the query, "What are the titles and years of movies made by Fox that are at least 100 minutes long?"
We can split the task of answering this query into subtasks:
1. Select those Movies tuples that have `mbox(length) >= 100`.
2. Select those Movies tuples that have `mbox(studioName) = 'mbox(Fox)'`.
3. Compute the intersction of (1) and (2).
4. Project from (3) onto the attributes `mbox(title)` and `mbox(year)`.
Writing the above using relational algebra, we get:
`pi_(mbox(title,year))(sigma_{mbox(length) >= 100}(mbox(Movies)) cap sigma_{mbox(studioName) = 'mbox(Fox)'}(mbox(Movies))).`
An alternative way we could have computed this is:
`pi_(mbox(title,year))(sigma_{mbox(length) >= 100 mbox(AND studioName) = 'mbox(Fox)'}(mbox(Movies)))`.
When optimizing a query, the DBMS query compiler might consider several alternative ways to compute the same query and try to estimate which is fastest.

Naming and Renaming

Sometimes, when we want to compute a query, we'd like to rename either a relation or some of its attributes.
For example, we might want to join Project and Employee but they both have an ID column and the ID for a Project doesn't mean the same as an Employee ID.
Another use case might be we want to compute a relation Grandparent(X,Y) by using some kind of join of Parent(X,Y) with itself.
The relational algebra supports the renaming operator `rho` for these purposes.
`rho_{S(A_1,...,A_n)}(R)` outputs a table named `S` with attributes `A_1, ..., A_n` where `A_j` is a new name for the `j`th attribute of `R` in the standard order. The tuples of `S` are those of `R`, but using this new naming scheme.

More Relational Alegbra

Outline