===== Relational Algebra 2 =====

**Union** Operator: Problem with × (cross product) and ⋈ (natural joins) is that they try to match tuples (rows) form the relations (tables).  Here, we combine the information vertically.

π<sub>cName</sub> College ∪ π<sub>sName</sub> Student gives:

Stanford
Susan
Cornell
Mary
John
...

**Difference** operator

IDs of students who didn't apply anywhere

π<sub>sID</sub> Student - π<sub>sID</sub> Apply

IDs **and names** of those students

π<sub>sName</sub> (( π<sub>sID</sub> Student - π<sub>sID</sub> Apply ) ⋈ Student )

**Intersection** operator

Names that are both a college name and a student name:

π<sub>cName</sub>College ∩ π<sub>sName</sub> Student

Intersection doesn't add expressive power.

E<sub>1</sub> ∩ E<sub>2</sub> ≣ E<sub>1</sub> - ( E<sub>1</sub> - E<sub>2</sub> )

And if the schema is the same between E<sub>1</sub> and E<sub>2</sub>, then

E<sub>1</sub> ∩ E<sub>2</sub> ≣ E<sub>1</sub> ⋈ E<sub>2</sub>

**Rename** Operator ("ρ" rho)

  - ρ<sub>R(A1,...,An)</sub>(E) <- General Form
  - ρ<sub>R</sub>(E)
  - ρ<sub>A1,...,An</sub>(E)

ρ<sub>c(name)</sub>(π<sub>cName</sub>College) ∪ ρ<sub>c(name)</sub>(π<sub>sName</sub> Student)

Rename is used for disambiguation in "self-joins"

**Pairs** of colleges in same state: Stanford, Berkeley; Berlekey, UCLA; ...

σ<sub>s1 = s2</sub> (ρ<sub>c1(n1,s1,e1)</sub>( College ) × ρ<sub>c2(n2,s2,e2)</sub>( College ) )

or

ρ<sub>c1(n1,s,e1)</sub>( College ) ⋈ ρ<sub>c2(n2,s,e2)</sub>( College )

This would give us Stanford, Stanford and Berkeley, Berkeley. So add...

σ<sub>s1 != s2</sub>( ρ<sub>c1(n1,s,e1)</sub>( College ) ⋈ ρ<sub>c2(n2,s,e2)</sub>( College ) )

We'll still get Stanford, Berkeley and Berkeley, Stanford.  So change...

σ<sub>s1 < s2</sub>( ρ<sub>c1(n1,s,e1)</sub>( College ) ⋈ ρ<sub>c2(n2,s,e2)</sub>( College ) )

Clever, eh?