User Tools

Site Tools


db-class-excerpt
no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


Previous revision
Last revision
db-class-excerpt [2011/10/29 00:03] dblume
Line 1: Line 1:
 +===== 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?
db-class-excerpt.txt · Last modified: 2023/04/12 20:44 by 127.0.0.1