Relational Algebra

The running theme...

Replace [Thing A] with better, but equivalent [Thing B].

1. How can we tell if [Thing B] is equivalent to [Thing A]?
2. How can we tell if [Thing B] is better than [Thing A]?

Replace [Query A] with better, but equivalent [Query B].

1. How can we tell if [Query B] is equivalent to [Query A]?
2. How can we tell if [Query B] is better than [Query A]?

... but first a few definitions.

Relational Data

Relation (Table)

A collection of Tuples of Values

All tuples have the same set of attributes, or schema

Relational Data

Declarative Languages

Look at every T report 
For each week:
  Sum up the sprocket count
  Find that week's S report
  ... 

Declarative languages make it easier to explore equivalent computations to find the best one.

Declarative Queries

SQL

Readability: Focus on what and where from

Relational Algebra

Data Flow: Focus on transformations

Relational Calculus

Minimality: Focus on attribute relationships

SQL

                      SELECT R.A FROM R, S 
                      WHERE R.B = S.B AND S.C = 10
          

Relational Algebra

Relational Calculus

$$\exists B : R(A, B), S(B, 10)$$

Declarative Queries

SQL

Readability: Focus on what and where from

Relational Algebra

Data Flow: Focus on transformations

Relational Calculus

Minimality: Focus on attribute relationships

Preliminaries

We start with a database instance with a fixed schema

Queries are applied to Relations $$Q(\textbf{Trees}, \textbf{SpeciesInfo})$$

Queries are also Relations! $$Q_2(\textbf{SpeciesInfo}, Q_1(\textbf{Trees}))$$ (Relational Algebra is Closed)

Relational Algebra

Also: Intersection, Join, Division, Renaming

(Not essential, but can be useful)

First we focus on sets and bags.

Selection ($\sigma_{c}$)

Delete rows that fail the condition $c$.

Projection ($\pi_{A}$)

Delete attributes not in the projection list $A$.

Only 5 results... not 683788?

Set and Bag Projection are different

Reminder: Queries are Relations

What are these queries schemas?

Union ($\cup$)

Takes two relations that are union-compatible...

(Both relations have the same number of fields with the same types)

... and returns all tuples appearing in either relation

We use $\uplus$ if we explicitly mean bag union

Intersection ($\cap$)

Return all tuples appearing in both
of two union-compatible relations

Set Difference

Return all tuples appearing in the first, but not the second
of two union-compatible relations

Union, Intersection, Set Difference

What is the schema of the result of any of these operators?

Cross (Cartesian) Product ($\times$)

Create all pairs of tuples.

Cross (Cartesian) Product ($\times$)

What is the schema of the resulting relation?

The relation has a naming conflict
(two attributes with the same name)

Renaming ($\rho$)

What is the schema of the resulting relation?

When writing cross-products on the board,
I will use implicit renaming

Join ($\bowtie_c$)

Pair tuples according to a condition c.

Join Shorthands

Equi-joins are joins with only equality tests in the condition.

Join on attribute(s)

$R \bowtie_{A} S \equiv R \bowtie_{R.A = S.A} S$

Same values on the listed attributes

Natural Join

$R \bowtie S \equiv R \bowtie_{attrs(R) \cap attrs(S)} S$

Same values on all shared attributes

Which operators can create duplicates?

(Which operators behave differently in Set- and Bag-RA?)

Relational Algebra

A simple way to think about and work with
computations over collections.

… simple → easy to evaluate

… simple → easy to optimize

Next time, Optimizing RA