Relational Algebra - University of...
22
Relational Algebra Dr Paolo Guagliardo University of Edinburgh Fall 2016
Transcript of Relational Algebra - University of...
Relational Algebra
Dr Paolo Guagliardo
University of Edinburgh
Fall 2016
Relational algebra
Procedural query language
A relational algebra expression
I takes as input one or more relations
I applies a sequence of operations
I returns a relation as output
Operations:
Projection (π)
Selection (σ)
Product (×)
Renaming (ρ)
Union (∪)
Intersection (∩)
Difference (−)
The application of each operation results in a new relationthat can be used as input to other operations
1 / 17
Projection
I Vertical operation: choose some of the columns
I Syntax: πset of attributes(relation)
I πA1,...,An(R) takes only the values of attributes A1, . . . , Anfor each tuple in R
Customer
CustID Name City Address
cust1 Renton Edinburgh 2 Wellington Plcust2 Watson London 221B Baker Stcust3 Holmes London 221B Baker St
πName,City(Customer)
Name City
Renton EdinburghWatson LondonHolmes London
2 / 17
Selection
I Horizontal operation: choose rows satisfying some condition
I Syntax: σcondition(relation)
I σθ(R) takes only the tuples in R for which θ is satisfied
term := attribute | constant
θ := term op term with op ∈ {=, 6=, >,<,>,6}| θ ∧ θ | θ ∨ θ | ¬θ
3 / 17
Example of selection
Customer
CustID Name City Age
cust1 Renton Edinburgh 24cust2 Watson London 32cust3 Holmes London 35
σCity 6=‘Edinburgh’∧Age<33(Customer)
CustID Name City Age
cust2 Watson London 32
4 / 17
Efficiency (1)
Consecutive selections can be combined into a single one:
σθ1(σθ2(R)
)= σθ1 ∧ θ2(R)
Example
Q1 = σCity6=‘Edinburgh’
(σAge<33(Customer)
)Q2 = σCity6=‘Edinburgh’∧Age<33(Customer)
Q1 = Q2 but Q2 faster than Q1 in general
5 / 17
Efficiency (2)
Projection can be pushed inside selection
πα(σθ(R)
)= σθ
(πα(R)
)only if all attributes mentioned in θ appear in α
Example
Q1 = πName,City,Age
(σCity 6=‘Edinburgh’∧Age<33(Customer)
)Q2 = σCity6=‘Edinburgh’∧Age<33
(πName,City,Age(Customer)
)Question: Which one is more efficient?
6 / 17
Efficiency (2)
Projection can be pushed inside selection
πα(σθ(R)
)= σθ
(πα(R)
)only if all attributes mentioned in θ appear in α
Example
Q1 = πName,City,Age
(σCity 6=‘Edinburgh’∧Age<33(Customer)
)Q2 = σCity6=‘Edinburgh’∧Age<33
(πName,City,Age(Customer)
)Question: Which one is more efficient?
6 / 17
Cartesian product
R×S concatenates each tuple of R with all the tuples of S
Example
R A B1 23 4
× S C D1 a2 b3 c
= R×S A B C D1 2 1 a1 2 2 b1 2 3 c3 4 1 a3 4 2 b3 4 3 c
Expensive operation:
I card(R×S) = card(R)× card(S)
I arity(R×S) = arity(R) + arity(S)
7 / 17
Joining relations
Combining Cartesian product and selection
Customer: ID, Name, City, AddressAccount: Number, Branch, CustID, Balance
We can join customers with the accounts they own as follows
σID=CustID(Customer×Account)
8 / 17
Renaming
Gives a new name to some of the attributes of a relation
Syntax: ρreplacements(relation),where a replacement has the form A→ B
ρA→A′, C→D
A B Ca b c1 2 3
=
A′ B Da b c1 2 3
Example
Customer: CustID, Name, City, AddressAccount: Number, Branch, CustID, Balance
σCustID=CustID′(Customer× ρCustID→CustID′(Account)
)
9 / 17
Renaming
Gives a new name to some of the attributes of a relation
Syntax: ρreplacements(relation),where a replacement has the form A→ B
ρA→A′, C→D
A B Ca b c1 2 3
=
A′ B Da b c1 2 3
Example
Customer: CustID, Name, City, AddressAccount: Number, Branch, CustID, Balance
σCustID=CustID′(Customer× ρCustID→CustID′(Account)
)9 / 17
Natural join
Joins two tables on their common attributes
Example
Customer: CustID, Name, City, Address
Account: Number, Branch, CustID, Balance
Customer ./ Account =
πX∪Y(σCustID=CustID′
(Customer× ρCustID→CustID′(Account)
))where X = { all attributes of Customer }
Y = { all attributes of Account }
10 / 17
From SQL to relational algebra
SELECT 7→ projection π
FROM 7→ Cartesian product ×WHERE 7→ selection σ
SELECT A1, . . . , AmFROM T1, . . . , TnWHERE 〈condition〉
7→ πA1,...,Am
(σ〈condition〉(T1× · · ·×Tn)
)
Common attributes in T1, . . . , Tn must be renamed
11 / 17
Set operations
Union
R A Ba1 b1a2 b2
∪ S A Ba1 b1a3 a3
= R ∪ S A Ba1 b1a2 b2a3 b3
Intersection
R A Ba1 b1a2 b2
∩ S A Ba1 b1a3 a3
= R ∩ S A Ba1 b1
Difference
R A Ba1 b1a2 b2
− S A Ba1 b1a3 a3
= R − S A Ba2 b2
The relations must have the same set of attributes12 / 17
Union and renaming
R Father ChildGeorge ElizabethPhilip CharlesCharles William
S Mother ChildElizabeth CharlesElizabeth Andrew
We want to find the relation parent-child
ρFather→Parent(R) ∪ ρMother→Parent(S) = Parent ChildGeorge ElizabethPhilip CharlesCharles WilliamElizabeth CharlesElizabeth Andrew
13 / 17
Union and renaming
R Father ChildGeorge ElizabethPhilip CharlesCharles William
S Mother ChildElizabeth CharlesElizabeth Andrew
We want to find the relation parent-child
ρFather→Parent(R) ∪ ρMother→Parent(S) = Parent ChildGeorge ElizabethPhilip CharlesCharles WilliamElizabeth CharlesElizabeth Andrew
13 / 17
Full relational algebra
Primitive operations: π , σ , × , ρ , ∪ , −Removing any of these results in a loss of expressive power
Derived operations
./ can be expressed in terms of π , σ , × , ρ
∩ can be expressed in terms difference:
R ∩ S = R− (R− S)
14 / 17
Other derived operations
Theta-join R ./θ S = σθ(R×S)
Equijoin ./θ where θ is a conjunction of equalities
Semijoin Rnθ S = πX(R ./θ S
)where X is the set of attributes of R
Antijoin Rnθ S = R− (Rnθ S)
Why use these operations?
I to write things more succintly
I they can be optimized independently
15 / 17
Other derived operations
Theta-join R ./θ S = σθ(R×S)
Equijoin ./θ where θ is a conjunction of equalities
Semijoin Rnθ S = πX(R ./θ S
)where X is the set of attributes of R
Antijoin Rnθ S = R− (Rnθ S)
Why use these operations?
I to write things more succintly
I they can be optimized independently
15 / 17
Division
R over set of attributes X
S over set of attributes Y ⊂ XLet Z = X − Y
R÷ S ={r̄ ∈ πZ(R) | ∀s̄ ∈ S . r̄s̄ ∈ R
}={r̄ ∈ πZ(R) | {r̄}×S ⊆ R
}= πZ(R)− πZ
(πZ(R)× S −R
)
16 / 17
Division: Example
Exams
Student Course
John DatabasesJohn NetworksMary ProgrammingMary MathMary Databases
DPT
Course
DatabasesProgramming
Exams ÷ DPT =Student
Mary
= πStudent(Exams)− πStudent(πStudent(Exams)×DPT− Exams
)17 / 17
