1/51
Welcome to Week 7 Workshop
2/51
Housekeeping
The mark and feedback on Assignment 1 (SQL) is available on Wattle.
Refer to the sample solutions along with the common issues.
Test your queries on moviedb2021 instead of moviedb.
The specification of Assignment 2 (Database Theory) will be available on
Sep 28. The submission via Wattle is due 23:59 Oct 12 (Tuesday, Week 10)
Individual, no group work!
Do not post any idea/partial solution/result on Wattle.
2/51
Housekeeping
The mark and feedback on Assignment 1 (SQL) is available on Wattle.
Refer to the sample solutions along with the common issues.
Test your queries on moviedb2021 instead of moviedb.
The specification of Assignment 2 (Database Theory) will be available on
Sep 28. The submission via Wattle is due 23:59 Oct 12 (Tuesday, Week 10)
Individual, no group work!
Do not post any idea/partial solution/result on Wattle.
3/51
SQL⇒ Relational Algebra
Database users
SQL queries
SELECT …
FROM …
WHERE …
…
|
|
|
|
|
→|→
→|→
|
|
Database systems
RA queries
σ, π, ρ
∪, ∩, −
×, ./, …
3/51
SQL⇒ Relational Algebra
Database users
SQL queries
SELECT …
FROM …
WHERE …
…
|
|
|
|
|
→|→
→|→
|
|
Database systems
RA queries
σ, π, ρ
∪, ∩, −
×, ./, …
4/51
Why Relational Algebra?
Make SQL queries easy-to-use …
Declarative vs Procedural
Make me a cake
Mix 2 cup flour,
1/2 cup butter, and
2 eggs until well
blended. Divide the
dough into a 12×2-in.
log. Preheat oven to
350° and bake 30-35
minutes.
RA bridges the gap between the declarative nature of SQL and the
procedure nature of a computer system.
Expressive: Each SQL query can be represented by a RA query.
Procedural: Each RA query consists of step-by-step operations.
4/51
Why Relational Algebra?
Make SQL queries easy-to-use …
Declarative vs Procedural
Make me a cake
Mix 2 cup flour,
1/2 cup butter, and
2 eggs until well
blended. Divide the
dough into a 12×2-in.
log. Preheat oven to
350° and bake 30-35
minutes.
RA bridges the gap between the declarative nature of SQL and the
procedure nature of a computer system.
Expressive: Each SQL query can be represented by a RA query.
Procedural: Each RA query consists of step-by-step operations.
4/51
Why Relational Algebra?
Make SQL queries easy-to-use …
Declarative vs Procedural
Make me a cake
Mix 2 cup flour,
1/2 cup butter, and
2 eggs until well
blended. Divide the
dough into a 12×2-in.
log. Preheat oven to
350° and bake 30-35
minutes.
RA bridges the gap between the declarative nature of SQL and the
procedure nature of a computer system.
Expressive: Each SQL query can be represented by a RA query.
Procedural: Each RA query consists of step-by-step operations.
4/51
Why Relational Algebra?
Make SQL queries easy-to-use …
Declarative vs Procedural
Make me a cake
Mix 2 cup flour,
1/2 cup butter, and
2 eggs until well
blended. Divide the
dough into a 12×2-in.
log. Preheat oven to
350° and bake 30-35
minutes.
RA bridges the gap between the declarative nature of SQL and the
procedure nature of a computer system.
Expressive: Each SQL query can be represented by a RA query.
Procedural: Each RA query consists of step-by-step operations.
5/51
Why Relational Algebra?
Make SQL queries run fast …
RA enables many different ways to implement a SQL query.
SQL query
RA query 1
RA query 2
RA query n
……
Va
ri
ed
p
er
fo
rm
an
ce
Logically equivalent
5/51
Why Relational Algebra?
Make SQL queries run fast …
RA enables many different ways to implement a SQL query.
SQL query
RA query 1
RA query 2
RA query n
……
Va
ri
ed
p
er
fo
rm
an
ce
Logically equivalent
5/51
Why Relational Algebra?
Make SQL queries run fast …
RA enables many different ways to implement a SQL query.
SQL query
RA query 1
RA query 2
RA query n
……
Va
ri
ed
p
er
fo
rm
an
ce
Logically equivalent
6/51
Arithmetic v.s. Algebra
What is the difference between “2+8=8+2” and “a+b=b+a”?
Arithmetic: “2+8=8+2” is a specific fact.
Algebra: “a+b=b+a” is a general pattern.
6/51
Arithmetic v.s. Algebra
What is the difference between “2+8=8+2” and “a+b=b+a”?
Arithmetic: “2+8=8+2” is a specific fact.
Algebra: “a+b=b+a” is a general pattern.
6/51
Arithmetic v.s. Algebra
What is the difference between “2+8=8+2” and “a+b=b+a”?
Arithmetic: “2+8=8+2” is a specific fact.
Algebra: “a+b=b+a” is a general pattern.
7/51
What is an “Algebra”?
Mathematical system consisting of:
Operands — variables or values from which new values can be
constructed.
Operators — symbols denoting procedures that construct new values
from given values.
Elementary algebra consisting of:
Operands — variables X , Y , Z , etc.
Operators — +, -, ×, /
Relational algebra consisting of:
Operands — relations R1, R2, R3, etc.
Operators — {σ, π,∪,∩, ./, . . . }
7/51
What is an “Algebra”?
Mathematical system consisting of:
Operands — variables or values from which new values can be
constructed.
Operators — symbols denoting procedures that construct new values
from given values.
Elementary algebra consisting of:
Operands — variables X , Y , Z , etc.
Operators — +, -, ×, /
Relational algebra consisting of:
Operands — relations R1, R2, R3, etc.
Operators — {σ, π,∪,∩, ./, . . . }
7/51
What is an “Algebra”?
Mathematical system consisting of:
Operands — variables or values from which new values can be
constructed.
Operators — symbols denoting procedures that construct new values
from given values.
Elementary algebra consisting of:
Operands — variables X , Y , Z , etc.
Operators — +, -, ×, /
Relational algebra consisting of:
Operands — relations R1, R2, R3, etc.
Operators — {σ, π,∪,∩, ./, . . . }
8/51
Relational Operators 1
. . . . . .
. . . . . .
. . . . . .
1
http://merrigrove.blogspot.com.au/2011/12/another-introduction-to-algebraic-data.html (with some changes)
9/51
Summary of Relational Operators
Operator Notation Meaning
Selection σϕ(R) choose rows
Projection πA1,…,An(R) choose columns
Union R1 ∪ R2
set operationsIntersection R1 ∩ R2
Difference R1 − R2
Cartesian product R1 × R2
combine tablesJoin R1 ./ϕ R2
Natural-join R1 ./ R2
Renaming
ρR′ (A1,…,An)(R)
rename relation and attributesρR′ (R)
ρ(A1,…,An)(R)
10/51
Selection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the items with price less than 9 CHF?
σϕ(R), ϕ = Price < 9, R=SELL⇒ σPrice<9(SELL).
Shop Item Price
Coop Ham 8
Migros Cheese 8
10/51
Selection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the items with price less than 9 CHF?
σϕ(R), ϕ = Price < 9, R=SELL⇒ σPrice<9(SELL).
Shop Item Price
Coop Ham 8
Migros Cheese 8
10/51
Selection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the items with price less than 9 CHF?
σϕ(R), ϕ = Price < 9, R=SELL⇒ σPrice<9(SELL).
Shop Item Price
Coop Ham 8
Migros Cheese 8
11/51
Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items?
πA1,...,An (R), {A1, . . . ,An} = {Shop, Item}, R=SELL⇒ πShop,Item(SELL).
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
11/51
Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items?
πA1,...,An (R), {A1, . . . ,An} = {Shop, Item}, R=SELL⇒ πShop,Item(SELL).
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
11/51
Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items?
πA1,...,An (R), {A1, . . . ,An} = {Shop, Item}, R=SELL⇒ πShop,Item(SELL).
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
12/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
πShop,Item(σPrice<9(SELL))
Shop Item Price
Coop Ham 8
Migros Cheese 8
Shop Item
Coop Ham
Migros Cheese
12/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
πShop,Item(σPrice<9(SELL))
Shop Item Price
Coop Ham 8
Migros Cheese 8
Shop Item
Coop Ham
Migros Cheese
12/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
πShop,Item(σPrice<9(SELL))
Shop Item Price
Coop Ham 8
Migros Cheese 8
Shop Item
Coop Ham
Migros Cheese
13/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
What about σPrice<9(πShop,Item(SELL))?
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
Error!
No price attribute available.
13/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
What about σPrice<9(πShop,Item(SELL))?
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
Error!
No price attribute available.
13/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
What about σPrice<9(πShop,Item(SELL))?
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
Error!
No price attribute available.
13/51
Selection + Projection Example
Consider the relation SELL:
Shop Item Price
Coop Cheese 10
Migros Cabbage 10
Coop Ham 8
Migros Cheese 8
What if we only want to know all the available shops and items with the price
less than 9 CHF?
What about σPrice<9(πShop,Item(SELL))?
Shop Item
Coop Cheese
Migros Cabbage
Coop Ham
Migros Cheese
Error!
No price attribute available.
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R))
= σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns?
Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows?
No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
14/51
Selection and Projection – Properties
Selections are commutative
σϕ1(σϕ2(R)) = σϕ2(σϕ1(R)) = σϕ1∧ϕ2(R).
Projections are not commutative
πB1,...,Bm (πA1,...,An (R)) = πA1,...,An (πB1,...,Bm (R)) does not hold in general
Pairs of selection and projection are not commutative
πA1,...,An (σϕ(R)) = σϕ(πA1,...,An (R)) does not hold in general
Selections will always keep the same number of columns? Yes.
Projections will always keep the same number of rows? No (may introduce
duplicates and have to be eliminated).
15/51
Set Operations
Relations are sets (of tuples/rows), we have standard operations on sets.
Union, denoted as R1 ∪ R2, results in a relation that includes all tuples
either in R1 or in R2. Duplicate tuples are eliminated.
Intersection, denoted as R1 ∩ R2, results in a relation that includes all
tuples that are in both R1 and R2.
Difference, denoted as R1 − R2, results in a relation that includes all
tuples that are in R1 but not in R2.
Type compatibility: R1 and R2 must have the same type, i.e.,
the same number of attributes, and
the same domains for the attributes (the order is important).
15/51
Set Operations
Relations are sets (of tuples/rows), we have standard operations on sets.
Union, denoted as R1 ∪ R2, results in a relation that includes all tuples
either in R1 or in R2. Duplicate tuples are eliminated.
Intersection, denoted as R1 ∩ R2, results in a relation that includes all
tuples that are in both R1 and R2.
Difference, denoted as R1 − R2, results in a relation that includes all
tuples that are in R1 but not in R2.
Type compatibility: R1 and R2 must have the same type, i.e.,
the same number of attributes, and
the same domains for the attributes (the order is important).
16/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY)) ∩ πStudentID(σCourseNo=′ECON2102′ (STUDY))?
R1 = πStudentID(σCourseNo=′COMP2400′ (STUDY))
R2 = πStudentID(σCourseNo=′ECON2102′ (STUDY))
StudentID
111
222
INTERSECT
StudentID
111
16/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY)) ∩ πStudentID(σCourseNo=′ECON2102′ (STUDY))?
R1 = πStudentID(σCourseNo=′COMP2400′ (STUDY))
R2 = πStudentID(σCourseNo=′ECON2102′ (STUDY))
StudentID
111
222
INTERSECT
StudentID
111
17/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY)) ∩ πStudentID(σCourseNo=′ECON2102′ (STUDY))?
R1 = πStudentID(σCourseNo=′COMP2400′ (STUDY))
R1 ∩ R2
R2 = πStudentID(σCourseNo=′ECON2102′ (STUDY))
StudentID
111
18/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY) ∩ σCourseNo=′ECON2102′ (STUDY))?
R1 = σCourseNo=′COMP2400′ (STUDY)
πStudentID(R1 ∩ R2)
R2 = σCourseNo=′ECON2102′ (STUDY)
EMPTY!
18/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY) ∩ σCourseNo=′ECON2102′ (STUDY))?
R1 = σCourseNo=′COMP2400′ (STUDY)
πStudentID(R1 ∩ R2)
R2 = σCourseNo=′ECON2102′ (STUDY)
EMPTY!
18/51
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for
πStudentID(σCourseNo=′COMP2400′ (STUDY) ∩ σCourseNo=′ECON2102′ (STUDY))?
R1 = σCourseNo=′COMP2400′ (STUDY)
πStudentID(R1 ∩ R2)
R2 = σCourseNo=′ECON2102′ (STUDY)
EMPTY!
19/51
Cartesian Product, Join and Natural Join
Cartesian product R1 × R2 combines tuples from two relations in a
combinatorial fashion.
Join R1 ./ϕ R2 is introduced as the combination of Cartesian product
and selection. That is,
R1 ./ϕ R2 = σϕ(R1 × R2).
Natural Join R1 ./ R2
1 Implicitly apply the join condition on equality comparisons of
attributes that have the same name in both relations.
2 Project out one copy of the attributes that have the same name in both
relations.
19/51
Cartesian Product, Join and Natural Join
Cartesian product R1 × R2 combines tuples from two relations in a
combinatorial fashion.
Join R1 ./ϕ R2 is introduced as the combination of Cartesian product
and selection. That is,
R1 ./ϕ R2 = σϕ(R1 × R2).
Natural Join R1 ./ R2
1 Implicitly apply the join condition on equality comparisons of
attributes that have the same name in both relations.
2 Project out one copy of the attributes that have the same name in both
relations.
19/51
Cartesian Product, Join and Natural Join
Cartesian product R1 × R2 combines tuples from two relations in a
combinatorial fashion.
Join R1 ./ϕ R2 is introduced as the combination of Cartesian product
and selection. That is,
R1 ./ϕ R2 = σϕ(R1 × R2).
Natural Join R1 ./ R2
1 Implicitly apply the join condition on equality comparisons of
attributes that have the same name in both relations.
2 Project out one copy of the attributes that have the same name in both
relations.
20/51
Cartesian Product – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE × ENROL?
COURSE × ENROL will have 9 (=3×3) tuples and 7 (=3+4) attributes.
20/51
Cartesian Product – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE × ENROL?
COURSE × ENROL will have 9 (=3×3) tuples and 7 (=3+4) attributes.
21/51
Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./No=CourseNo ENROL?
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
21/51
Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./No=CourseNo ENROL?
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
22/51
Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for πNo,Cname(COURSE ./No=CourseNo ENROL)?
No Cname
COMP2400 Relational Databases
BUSN2011 Management Accounting
22/51
Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for πNo,Cname(COURSE ./No=CourseNo ENROL)?
No Cname
COMP2400 Relational Databases
BUSN2011 Management Accounting
23/51
Natural Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ ENROL?
If there are no matching attributes in two tables for NATURAL JOIN,
COURSE ./ ENROL will become COURSE × ENROL which outputs 9 (=3×3)
tuples and 7 (=3+4) attributes.
23/51
Natural Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ ENROL?
If there are no matching attributes in two tables for NATURAL JOIN,
COURSE ./ ENROL will become COURSE × ENROL which outputs 9 (=3×3)
tuples and 7 (=3+4) attributes.
24/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ ENROL?
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 222 2016 S1 active
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
24/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ ENROL?
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 222 2016 S1 active
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
25/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for σStudentID=111(COURSE ./ ENROL)?
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
25/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for σStudentID=111(COURSE ./ ENROL)?
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
26/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ COURSE?
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
26/51
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for COURSE ./ COURSE?
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL
Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL)
Incorrect!
27/51
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List the email of students who have enrolled in courses and the CourseNo
of these courses.
1 πEmail,CourseNo(σStudent.StudentID=Enrol.StudentID(STUDENT × ENROL))
2 πEmail,CourseNo(STUDENT ./Student.StudentID=Enrol.StudentID ENROL)
3 πEmail,CourseNo(STUDENT ./ ENROL)
4 (πEmail,CourseNo(STUDENT)) ./ ENROL Incorrect!
5 πEmail (STUDENT) ./ πCourseNo(ENROL) Incorrect!
28/51
Renaming
Renaming is used to rename either the relation name or the attribute
names, or both.
Renaming is denoted as
ρR′ (A1,...,An)
(R): renaming the relation name to R
′
and the attribute
names to A1, . . . ,An,
ρR′ (R): renaming the relation name to R
′
and keeping the attribute
names unchanged, or
ρ(A1,...,An)(R): renaming the attribute names to A1, . . . ,An and keeping
the relation name unchanged.
Renaming is useful for giving names to the relations that hold the
intermediate results.
28/51
Renaming
Renaming is used to rename either the relation name or the attribute
names, or both.
Renaming is denoted as
ρR′ (A1,...,An)
(R): renaming the relation name to R
′
and the attribute
names to A1, . . . ,An,
ρR′ (R): renaming the relation name to R
′
and keeping the attribute
names unchanged, or
ρ(A1,...,An)(R): renaming the attribute names to A1, . . . ,An and keeping
the relation name unchanged.
Renaming is useful for giving names to the relations that hold the
intermediate results.
28/51
Renaming
Renaming is used to rename either the relation name or the attribute
names, or both.
Renaming is denoted as
ρR′ (A1,...,An)
(R): renaming the relation name to R
′
and the attribute
names to A1, . . . ,An,
ρR′ (R): renaming the relation name to R
′
and keeping the attribute
names unchanged, or
ρ(A1,...,An)(R): renaming the attribute names to A1, . . . ,An and keeping
the relation name unchanged.
Renaming is useful for giving names to the relations that hold the
intermediate results.
28/51
Renaming
Renaming is used to rename either the relation name or the attribute
names, or both.
Renaming is denoted as
ρR′ (A1,...,An)
(R): renaming the relation name to R
′
and the attribute
names to A1, . . . ,An,
ρR′ (R): renaming the relation name to R
′
and keeping the attribute
names unchanged, or
ρ(A1,...,An)(R): renaming the attribute names to A1, . . . ,An and keeping
the relation name unchanged.
Renaming is useful for giving names to the relations that hold the
intermediate results.
28/51
Renaming
Renaming is used to rename either the relation name or the attribute
names, or both.
Renaming is denoted as
ρR′ (A1,...,An)
(R): renaming the relation name to R
′
and the attribute
names to A1, . . . ,An,
ρR′ (R): renaming the relation name to R
′
and keeping the attribute
names unchanged, or
ρ(A1,...,An)(R): renaming the attribute names to A1, . . . ,An and keeping
the relation name unchanged.
Renaming is useful for giving names to the relations that hold the
intermediate results.
29/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
1 πName,Name(σDoB=DoB(STUDENT × STUDENT))
2 πName,Name(STUDENT ./DoB=DoB STUDENT)
3 πName,Name(STUDENT ./ STUDENT)
29/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
1 πName,Name(σDoB=DoB(STUDENT × STUDENT))
2 πName,Name(STUDENT ./DoB=DoB STUDENT)
3 πName,Name(STUDENT ./ STUDENT)
30/51
Rename – Example
(1): πName,Name(σDoB=DoB(STUDENT × STUDENT)).
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT × STUDENT
StudentID Name DoB StudentID Name DoB
457 Lisa 18-Oct-1993 457 Lisa 18-Oct-1993
457 Lisa 18-Oct-1993 458 Mike 16-May-1990
457 Lisa 18-Oct-1993 458 Peter 18-Oct-1993
458 Mike 16-May-1990 457 Lisa 18-Oct-1993
458 Mike 16-May-1990 458 Mike 16-May-1990
458 Mike 16-May-1990 458 Peter 18-Oct-1993
458 Peter 18-Oct-1993 457 Lisa 18-Oct-1993
458 Peter 18-Oct-1993 458 Mike 16-May-1990
458 Peter 18-Oct-1993 458 Peter 18-Oct-1993
Incorrect!
30/51
Rename – Example
(1): πName,Name(σDoB=DoB(STUDENT × STUDENT)).
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT × STUDENT
StudentID Name DoB StudentID Name DoB
457 Lisa 18-Oct-1993 457 Lisa 18-Oct-1993
457 Lisa 18-Oct-1993 458 Mike 16-May-1990
457 Lisa 18-Oct-1993 458 Peter 18-Oct-1993
458 Mike 16-May-1990 457 Lisa 18-Oct-1993
458 Mike 16-May-1990 458 Mike 16-May-1990
458 Mike 16-May-1990 458 Peter 18-Oct-1993
458 Peter 18-Oct-1993 457 Lisa 18-Oct-1993
458 Peter 18-Oct-1993 458 Mike 16-May-1990
458 Peter 18-Oct-1993 458 Peter 18-Oct-1993
Incorrect!
30/51
Rename – Example
(1): πName,Name(σDoB=DoB(STUDENT × STUDENT)).
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT × STUDENT
StudentID Name DoB StudentID Name DoB
457 Lisa 18-Oct-1993 457 Lisa 18-Oct-1993
457 Lisa 18-Oct-1993 458 Mike 16-May-1990
457 Lisa 18-Oct-1993 458 Peter 18-Oct-1993
458 Mike 16-May-1990 457 Lisa 18-Oct-1993
458 Mike 16-May-1990 458 Mike 16-May-1990
458 Mike 16-May-1990 458 Peter 18-Oct-1993
458 Peter 18-Oct-1993 457 Lisa 18-Oct-1993
458 Peter 18-Oct-1993 458 Mike 16-May-1990
458 Peter 18-Oct-1993 458 Peter 18-Oct-1993
Incorrect!
31/51
Rename – Example
(2): πName,Name(STUDENT ./DoB=DoB STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT ./DoB=DoB STUDENT?
StudentID Name DoB StudentID Name DoB
Incorrect!
31/51
Rename – Example
(2): πName,Name(STUDENT ./DoB=DoB STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT ./DoB=DoB STUDENT?
StudentID Name DoB StudentID Name DoB
Incorrect!
31/51
Rename – Example
(2): πName,Name(STUDENT ./DoB=DoB STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
STUDENT ./DoB=DoB STUDENT?
StudentID Name DoB StudentID Name DoB
Incorrect!
32/51
Rename – Example
(3): πName,Name(STUDENT ./ STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
(STUDENT ./ STUDENT)
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
Incorrect!
32/51
Rename – Example
(3): πName,Name(STUDENT ./ STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
(STUDENT ./ STUDENT)
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
Incorrect!
32/51
Rename – Example
(3): πName,Name(STUDENT ./ STUDENT)
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
(STUDENT ./ STUDENT)
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
Incorrect!
33/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
πR1.Name,R2.Name(σR1.DoB=R2.DoB(ρR1(STUDENT)× ρR2(STUDENT)))
Almost correct!
πName,Name′(STUDENT ./ ρS(StudentID′,Name′,DoB)(STUDENT))
Almost correct!
33/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
πR1.Name,R2.Name(σR1.DoB=R2.DoB(ρR1(STUDENT)× ρR2(STUDENT)))
Almost correct!
πName,Name′(STUDENT ./ ρS(StudentID′,Name′,DoB)(STUDENT))
Almost correct!
33/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
πR1.Name,R2.Name(σR1.DoB=R2.DoB(ρR1(STUDENT)× ρR2(STUDENT)))
Almost correct!
πName,Name′(STUDENT ./ ρS(StudentID′,Name′,DoB)(STUDENT))
Almost correct!
33/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
πR1.Name,R2.Name(σR1.DoB=R2.DoB(ρR1(STUDENT)× ρR2(STUDENT)))
Almost correct!
πName,Name′(STUDENT ./ ρS(StudentID′,Name′,DoB)(STUDENT))
Almost correct!
33/51
Rename – Example
Given the following relation schema:
STUDENT={StudentID, Name, DoB}
Find pairs of students who have the same birthday. Show their names.
STUDENT
StudentID Name DoB
457 Lisa 18-Oct-1993
458 Mike 16-May-1990
459 Peter 18-Oct-1993
What about the following choices?
πR1.Name,R2.Name(σR1.DoB=R2.DoB(ρR1(STUDENT)× ρR2(STUDENT)))
Almost correct!
πName,Name′(STUDENT ./ ρS(StudentID′,Name′,DoB)(STUDENT))
Almost correct!
34/51
Rename – Example
Find pairs of students who have the same birthday. Show their names.
(1). πR1.Name,R2.Name(σR1.StudentID