L8: Relational Algebra Flashcards
5 Basic Relational Algebra Operators
- Selection
- σ (sigma)
- Projection
- Π (capital pi)
- Cross Product
- X
- Union
- ∪
- Difference
- -
Why is Relational Algebra useful?
Relational Algebra allows us to translate
declarative (SQL) queries
into
precise and optimizable expressions
Considerations in
Relational Algebra Formalism
- Where RDBMS consider multisets, Relational Algebra considers sets
- We consider the Named Perspective
- Every attribute must have a unique name
Relational Algebra Symbols:
σc ( R )
σc ( R )
Selection
Return all tuples in relation R
which match condition C
Relational Algebra Symbols:
ΠA1, … , An ( R )
ΠA1, … , An ( R )
Projection
From relation R,
Return tuples only with the attributes
specified by A1, …, An
Relational Algebra Symbols:
R1 X R2
R1 X R2
Cross Product of R1 and R2
Each tuple in R1 with each tuple from R2
Rare, mainly used to express joins
Relational Algebra Symbols:
R ∪ S
R ∪ S
Union of Relations R and S
Returns a relation instance with all tuples from R and S
R and S must be union-compatible,
- Same number of fields
- Fields have same domains
Relational Algebra Symbols:
R - S
R - S
Difference of R and S
Returns all tuples that are in R, but not in S
Relational Algebra Symbols:
R ∩ S
R ∩ S
Intersection of R and S
- Tuples that are in both R and S
- It is a derived operation:
- R ∩ S = R - (R - S)
- R and S must be union compatible
Relational Algebra:
Derived/Auxiliary Operators
- Intersection
- ∩
- Joins
- ⋈
- Types:
- Condition
- Natural
- equi-join
- Renaming
- ρ
When are Renaming Operations useful?
- Resolving Conflicts
- Allows you to explicitly give names to some fields
- example:
- S1 X R1, there are two fields with the same name
- Helps break up large expressions
- When an expression gets long and nested, it can be confusing
Renaming Operation Syntax
S( R(F), E )
where:
E : a relational algebra expression (input)
R: The name of the new relation for E (output)
F: renaming list, such as
oldname → newname or
position → newname or
Field names in R are the same as in E,
if they are not renamed in F
Join Operation
⋈
Join ⋈ is one of the most useful operations.
Used most commonly to combine information from two or more relations.
Derived Operation:
Join = Cross Product → Selection & Projection
Join Operation:
Benefits of using Join Operation
instead of Cross Product
- Joins used much more than cross-product
- Result of cross product is much larger than join
Condition Join:
R ⋈c S
R ⋈c S
Condition c can refer to attributes of both R and S
Resulting schema is the same as that of a cross product
