db-class-excerpt

## 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.

πcName College ∪ πsName Student gives:

Stanford
Susan
Cornell
Mary
John

Difference operator

IDs of students who didn't apply anywhere

πsID Student - πsID Apply

IDs and names of those students

πsName (( πsID Student - πsID Apply ) ⋈ Student )

Intersection operator

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

πcNameCollege ∩ πsName Student

E1 ∩ E2 ≣ E1 - ( E1 - E2 )

And if the schema is the same between E1 and E2, then

E1 ∩ E2 ≣ E1 ⋈ E2

Rename Operator (“ρ” rho)

1. ρR(A1,…,An)(E) ← General Form
2. ρR(E)
3. ρA1,…,An(E)

ρc(name)cNameCollege) ∪ ρc(name)sName Student)

Rename is used for disambiguation in “self-joins”

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

σs1 = s2c1(n1,s1,e1)( College ) × ρc2(n2,s2,e2)( College ) )

or

ρc1(n1,s,e1)( College ) ⋈ ρc2(n2,s,e2)( College )

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

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

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

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

Clever, eh?