Transcript Extended Relational Algebra for SQL Slides

SQL, RA, Sets, Bags
• Fundamental difference between theoretical RA
and practical application of it in DBMSs and SQL
– RA  uses sets
– SQL  uses bags (multisets)
• There are good performance reasons for using
bags:
– Queries involve 2+ join, union, etc., which would
require an extra pass through the relation being built
– There are times we WANT every instance, particularly
for aggregate functions (e.g. taking an average)
• Downside:
– Extra memory
• Section 5.1 Topics include:
– Union, Difference, Intersection and how they are
affected by operation over bags
– Projection operator over bags
– Selection operator over bags
– Product and join over bags
• All the above follow what you would expect
• Other topics in 5.1:
– Algebraic laws of set operators applied to bags
Examples: set operators over bags
• {1,2,1} ∪ {1,1,2,3,1} =
– {1,1,1,1,1,2,2,3}
• {1,2,1,1} ∩ {1,2,1,3} =
– {1, 1, 2}
• {1,2,1,1,1} – {1,1,2,3} =
– {1,1}
Exercise 5.1.3a
Exercise 5.1.3b
• πbore(Ships |><| Classes)
More relational algebra
δ – Duplicate elimination
• δ(R)
– Eliminate duplicates from relation R
– (i.e. converts a relation from a bag to set
representation)
• R2 := δ(R1)
– R2 consists of one copy of each tuple that appears
in R2 one or more times
• DISTINCT modifier in SELECT stmt
δ - Example
R= ( A
1
3
1
δ(R) =
B)
2
4
2
A
1
3
B
2
4
τ – Sorting
• R2 := τL(R1)
– L – list of some attributes of R1
– L specifies the order of sorting
• Increasing order
– Tuples with identical components in L specify no order
• Benefit:
– Obvious – ordered output
– Not so obvious – stored sorted relations can have
substantial query benefit
• Recall running time for binary search
• O(log n) is far superior than O(n)
Aggregation Operators
• Use to summarize something about the values
in attribute of a relation
– Produces a single value as a result
•
•
•
•
•
SUM(attr)
AVG(attr)
MIN(attr)
MAX(attr)
COUNT(attr)
Example: Aggregation
R= ( A
1
3
3
B )
3
4
2
SUM(A) = 7
COUNT(A) = 3
MAX(B) = 4
AVG(B) = 3
SUM(A),
COUNT(A),
MAX(B), AVG(B)
=?
Grouping Operator
• R2 := γL(R1)
• L is a list of elements that are:
– Individual attributes of R1
• Called grouping attributes
– Aggregated attribute of R1
• Use an arrow and a new name to rename the
component
– R2 projects only what is in L
How does γL(R) work?
1. Form one group for each distinct list of values
for those attributes in R
2. Within each group, compute AGG(A) for each
aggregation on L
3. Result has one tuple for each group
– The grouping attributes' values for the group
– The aggregations over all tuples of the group (for
the aggregated attributes)
Example: Grouping / Aggregation
R=
γ
( A
1
4
1
1
B
2
5
2
3
C)
3
6
5
5
(R) = ??
First, partition R by A and B :
A
B
C
1
2
3
1
2
5
4
5
6
1
3
5
A,B,AVG(C)->X
Then, average C
within groups:
A
1
4
1
B
2
5
3
X
4
6
5
Note about aggregation
• If R is a relation, and R has attributes A1…An,
then
– δ(R) == γA1,A2,…,An(R)
– Grouping on ALL attributes in R eliminates
duplicates
– i.e. δ is not really necessary
• Also, if relation R is also a set, then
– πA1,A2,…,An(R) = γA1,A2,…,An(R)
Extended Projection
• Recall R2 := πL(R1)
– R2 contains only L attributes from R1
• L can be extended to allow arbitrary
expressions:
– Renaming (e.g., A -> B)
– Arithmetic expressions (e.g., A + B -> SUM)
– Duplicate attributes (i.e., include in L multiple
times)
Example: Extended Projection
R= ( A
1
3
B)
2
4
πA+B->C,A,A (R) =
C
3
7
A1
1
3
A2
1
3
Outer joins
• Recall that the standard natural join occurs only if
there is a match from both relations
• A tuple of R that has NO tuple of S with which it
can join is said to be dangling
– Vice versa applies
• Outer join: preserves dangling tuples in join
– Missing components set to NULL
• R |>◦<|C S.
– This is a bad approximation of the symbol – see text
– NO C? Natural outer join
Example: Outer Join
R=
(A
1
4
B)
2
5
S=
(B
2
6
C)
3
7
(1,2) joins with (2,3), but the other two tuples
are dangling.
R |>◦<| S =
A
B
1
4
NULL
C
2
5
6
3
NULL
7
Types of outer joins
• R |>◦<| S
– No condition, requires matching attributes
– Pads dangling tuples from both side
• R |>◦<| L S
– Pad dangling tupes of R only
• R |>◦<| R S
– Pad dangling tuples of S only
• SQL:
– R NATURAL {LEFT | RIGHT} JOIN S
– R {LEFT | RIGHT} JOIN S
– NOTE MySQL does not allow a FULL OUTER JOIN! Only LEFT or
RIGHT
– Just UNION a left outer join and a right outer join… mostly
A+B
1
5
1
6
7
A2
0
4
0
4
9
B2
1
9
1
16
16
B+1
1
3
3
4
1
4
C-1
0
3
4
3
1
3
A
0
2
2
3
B
1
3
4
4
A
0
2
3
SUM(B)
2
7
4
SELECT A,SUM(B)
FROM R
GROUP BY A
A
0
2
3
SELECT A FROM R GROUP BY A;
SELECT DISTINCT A FROM R;
A
2
MAX(C)
4
SELECT A,MAX(C)
FROM R NATURAL JOIN S
GROUP BY A;
What if MAX(C)
was SUM(C)?
A
2
2
0
0
2
3
B
3
3
1
1
4
4
C
4
4
┴
┴
┴
┴
SELECT * FROM R NATURAL LEFT JOIN S;
A
2
2
┴
┴
┴
┴
B
3
3
0
2
2
0
C
4
4
1
4
5
2
SELECT * FROM R NATURAL RIGHT JOIN S;
A
2
2
0
0
2
3
┴
┴
┴
┴
B
3
3
1
1
4
4
0
2
2
0
C
4
4
┴
┴
┴
┴
1
4
5
2
SELECT *
FROM R
NATURAL LEFT JOIN S
UNION
SELECT *
FROM R
NATURAL RIGHT JOIN S;
Right?
• SELECT *
FROM R
NATURAL LEFT JOIN S
UNION ALL
SELECT *
FROM R
NATURAL RIGHT JOIN S
WHERE A IS NULL;
A
0
0
0
0
0
0
0
0
2
2
3
┴
┴
R.B
1
1
1
1
1
1
1
1
3
4
4
┴
┴
S.B
2
2
3
3
2
2
3
3
┴
┴
┴
0
0
C
4
5
4
4
4
5
4
4
┴
┴
┴
1
2
Back to SQL
Aggregations
• SUM, AVG, COUNT, MIN, and MAX can be
applied to a column in a SELECT clause
– Produces an aggregation on the attribute
• COUNT(*) count the number of tuples
• Use DISTINCT inside of an aggregation to
eliminate duplicates in the function
Example:
• Sells(bar, beer, price)
• Find the average price of Guinness
– SELECT AVG(price)
– FROM Sells
– WHERE beer = 'Guinness';
• Find the number of different prices charged for
Guinness
– SELECT COUNT(DISTINCT price)
AS "# Prices"
– FROM Sells
– WHERE beer = 'Guinness';
Grouping
• SELECT
attr(s)
FROM
tbls
WHERE
cond_expr
GROUP BY attr(s)
• The resulting SELECT-FROM-WHERE relation
determined FIRST, then grouped according to
GROUP BY clause
– MySQL will also sort the relations according to
attributes listed in GROUP BY clause
• Therefore, allows optional ASC or DESC (just like ORDER BY)
• Aggregations are applied only within each group
Grouping and NULLS
Note on NULL and Aggregation
• NULL values in a tuple:
– never contribute to a sum, average or count
– can never be a min or max of an attribute
• If all values for an attribute are NULL, then
the result of an aggregation is NULL
– Exception: COUNT of an empty set is 0
• NULL values are treated as ordinary values
when forming groups
Example: Grouping
• Sells(bar, beer, price)
Frequents(drinker, bar)
• Find the average price for each beer
– SELECT beer, AVG(price)
– FROM Sells
– GROUP BY beer;
• Find for each drinker the average price of Guinness at
the bars they frequent
–
–
–
–
–
SELECT drinker, AVG(price)
FROM Frequents
NATURAL JOIN Sells
WHERE beer = 'Guinness'
GROUP BY drinker;
Restrictions
• Example:
– Find the bar that sells Guinness the cheapest
– SELECT bar, MIN(price)
FROM Sells
WHERE beer = 'Guinness';
– Is this correct?
• Book states that this is illegal SQL
– if an aggregation used, then each SELECT element
should be aggregated or be an attribute in GROUP BY
– MySQL allows the above, but such queries will give
meaningless results
Example of confusing aggregation
• Find the country of the ship with bore of 15
with the smallest displacement
• SELECT country,
MIN(displacement)
FROM Classes
WHERE bore = 15;
Not quite the correct answer!
Be sure to follow the rules for
aggregation.
HAVING Clause
• HAVING cond
– Follows a GROUP BY clause
– Condition applies to each possible group
– Groups not satisfying condition are eliminated
• Rules for conditions in HAVING clause:
– Aggregated attributes:
• Any attribute in relation in FROM clause can be aggregated
• Only applies to the group being tested
– Unaggregated attributes
• Only attributes in GROUP BY list
• mySQL is more lenient with this, though they result in
meaningless information
Example: HAVING
• Sells(bar, beer, price)
• Find the average price of those beers that are
served in at least three bars
• SELECT beer, AVG(price)
FROM Sells
GROUP BY beer
HAVING COUNT(*) >= 3;
Example: HAVING
• Sells(bar, beer, price)
Beers(name, manf)
• Find the average price of beers that are either served in at
least three bars or are manufactured by Sam Adams
• SELECT beer, AVG(price)
• FROM Sells
• GROUP BY beer
• HAVING COUNT(*) >= 3 OR
•
beer IN
•
(SELECT name
FROM Beers
WHERE manf = 'Sam Adams');
• Find the average displacement of ships from
each country having at least two classes
•
•
•
•
SELECT country, AVG(displacement)
FROM Classes
GROUP BY country
HAVING count(*) >= 2;
Summary so far
•
•
•
•
•
•
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
–
–
–
–
S
R1,…,Rn
C1
a1,…,ak
C2
b1,…,bk;
S attributes from R1,…,Rn or aggregates
C1 are conditions on R1,…,Rn
a1,…,ak are attributes from R1,…,Rn
C2 are conditions based on any attribute, or on any
aggregation in GROUP BY clause
– b1,…,bk are attributes on R1,…,Rn
Exercises
Exercise 6.2.3f
SELECT battle
FROM Outcomes
INNER JOIN Ships
ON Outcomes.ship = Ships.name
NATURAL JOIN Classes
GROUP BY country, battle
HAVING COUNT(ship) >= 3;
Exercise 6.4.7a
• SELECT COUNT(type)
FROM
Classes
WHERE type = 'bb';
Exercise 6.4.7b
• SELECT AVG(numGuns) AS 'Avg
Guns'
FROM
Classes
WHERE type = 'bb';
Exercise 6.4.7c
• SELECT AVG(numGuns) AS 'Avg
Guns'
FROM
Classes
NATURAL JOIN Ships
WHERE type = 'bb';
Exercise 6.4.7d
• SELECT class,
MIN(launched) AS First_Launched
FROM
Classes
NATURAL JOIN Ships
GROUP BY class;
Exercise 6.4.7e
•
•
•
•
•
•
•
SELECT C.class, COUNT(O.ship) AS '# sunk'
FROM
Classes AS C
NATURAL JOIN Ships AS S
INNER JOIN Outcomes AS O
ON S.name = O.ship
WHERE O.result = 'sunk'
GROUP BY C.class;