================================================================
CSE 344 -- Spring 2011
Lecture 07: Relationa Algebra
================================================================
Reading assignment: Chapter 2.4, 5.1, 5.2

---------------
SQL = expresses WHAT we want
RA =  expresses HOW we get it

Data base optimizer/compiler: translates SQL --> RA

*** Question: why don't we use Java to epxress HOW we get it ?

---------------
What is an "algebra" ?  Give examples of algebras...

Objects in RA are finite relations (sets !)
Operations are....  [discuss each in class]

1. Union
2. Difference
3. Selection
4. Projection
5. Join

---------------
Joins [define/discuss in class]:

1.  cartesian product

2.  natural join

*** What is R(A,B,C) Join S(A,C,D,E) ?
*** What is R(A,B,C) Join S(D,E) ?
*** What is R(A,B) Join S(A,B) ?

3. eq-join

4. theta join

5. outer join.  it is an "extended" operator because it uses nulls;
   note that book uses different symbol for outerjoin

---------------
Summary: RA consists of 5 operations PLUS renaming (show in class)

*** Question: why do we leave out intersection ? 

---------------
Complex RA expressions; also called "query plans".

Purchase(pid, product, price, quantity)
Product(pname, manufacturer)
/* Purchase.product refers to Product.pname */

*** Question in class: write an RA expression that returns all
    purchases over $30 and their manufacturer

*** Question in class: write an RA expression that returns all
    manufacturer that sold at least 2 different products

*** Question in class: write an RA expression that returns all
    manufacturers that sell products only at a price > $20

---------------
Equivalent RA expressions (show in class)

*** Question: why is it important to search automatically for
    equivalent expressions ?

================================================================

Relational Algebra on Bags, a.k.a. Bag Algebra

*** Question: why do RBDMS use bags instead of sets ?

A set = {1,4,5,7}
A bag = {1,1,4,5,5,5,7,7}

What changes in each of the operators below ?

1. Union
2. Difference
3. Selection
4. Projection
5. Join

Algebraic laws on bag chage !

*** Question in class: give an example of an algebraic law that holds
    for sets but does not hold for bags.


New operators that make sense only on bags:

1. Duplicate elimination \delta
2. Group-by/aggregate \gamma
3. Sorting