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.

SQL

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

Python

          with open_csv("R.csv") as R:
            with open_csv("S.csv") as S:
              for r_row in R:
                for s_row in S:
                  if r_row[1] == s_row[0] and s_row[1] == "10":
                    print(r_row[0])
    

Declarative Queries

SQL

Readability: Focus on what and where from

Relational Algebra

Data Flow: Focus on transformations

Relational Calculus

Minimality: Focus on attribute relationships

Relational Algebra

Relational Calculus

$$Q(A) := 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$.

          SELECT * FROM Trees [[ WHERE BORONAME = 'Brooklyn' ]]
      

Projection ($\pi_{A}$)

Delete attributes not in the projection list $A$.

Only 5 results... not 683788?

Set and Bag Projection are different

              SELECT [[ DISTINCT BORONAME ]] FROM Trees;
      

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?)

Division ($/$)

Not typically supported as a primitive operator,
but useful for expressing queries like:

Find species that appear in all boroughs

$$R / S \equiv \{\; \left<\vec t\right> \;|\; \forall \left<\vec s\right> \in S, \left< \vec t \vec s \right> \in R \;\}$$

Division ($/$)

Group Work

If time permits: Implement division using other operators.