Rule for re-writing a 2-argument selection for correlated sub-query

Slideshow:

Review: correlated sub-query

Important observation to understand re-writing a 2-argument selection for uncorrelated sub-queries

Notice that:

Rule for re-writing a 2-argument selection for correlated sub-query

Re-writing a 2-argument selection for correlated sub-queries - Example 1

Query:

Solution:

Re-writing a 2-argument selection for correlated sub-queries - Example 1

Explaning the query using a concrete example:

The normal way to compute the average age of the stars = (age₁ + age₂ + ... age_n)/n

= ( (movieYear - birthYear₁) + (movieYear - birthYear₂) + ... + (movieYear - birthYear_n) )/n

Re-writing a 2-argument selection for correlated sub-queries - Example 1

Explaning the query using a concrete example:

Another way to compute the average age of the stars = (age₁ + age₂ + ... age_n)/n

= ( (movieYear - birthYear₁) + (movieYear - birthYear₂) + ... + (movieYear - birthYear_n) )/n

= ( (movieYear + movieYear + ... + movieYear)/n − (birthYear₁ + birthYear₂ + ... + birthYear_n)/n

Re-writing a 2-argument selection for correlated sub-queries - Example 1

Explaning the query using a concrete example:

Another way to compute the average age of the stars:

Re-writing a 2-argument selection for correlated sub-queries - Example 1

Explaning the query using a concrete example:

Re-writing a 2-argument selection for correlated sub-queries - Example 1

(1) Convert the inner query to a query plan:

Re-writing a 2-argument selection for correlated sub-queries - Example 1

(2) Convert the outer query using a 2-argument selection:

Re-writing a 2-argument selection for correlated sub-queries - Example 1

(3) Re-write the 2-argument selection:

Note: the inner σ operator has no condition and will always evaluate to true !!!

Further optimization - Example 1

We can replace the selection + cartesian product by a join operation:

Further optimization - Example 1

Because γ outputs unique groups, we can remove the duplicate elimiation δ operator:

Re-writing a 2-argument selection for correlated sub-queries - Example 2

Re-writing a 2-argument selection for correlated sub-queries - Example 2

(1) Convert the inner query to a query plan:

Re-writing a 2-argument selection for correlated sub-queries - Example 2

(2) Convert the outer query using a 2-argument selection:

Re-writing a 2-argument selection for correlated sub-queries - Example 2

(3) Re-write the 2-argument selection:

Further optimization - Example 2

We can replace the selection + cartesian product by a join (⋈) operation:

Further optimization - Example 2

Because γ outputs unique groups, we can remove the duplicate elimiation δ operator:

Review: correlated sub-query

Correlated sub-query:

Example:

SELECT DISTINCT m1.movieTitle, m1.movieYear FROM StarsIn m1 WHERE m1.movieYear - 40 ≤ (SELECT avg(s.birthdate) FROM StarsIn m2, MovieStar s WHERE m2.StarName = s.name AND m2.movieTitle = m1.movieTitle AND m2.movieYear = m1.movieYear)

The sub-query is correlated because we connot execute the sub-query without the outer query:

SELECT avg(s.birthdate) FROM StarsIn m2, MovieStar s WHERE m2.StarName = s.name AND m2.movieTitle = m1.movieTitle AND m2.movieYear = m1.movieYear

That is because the values of the attributes m1.movieTitle and m1.movieYear are undefined without the relation StarsIn (m1) in the outer query

Prelude to re-writing a 2-argument selection for correlated sub-query

Notice that:

Some attribute(s) of a relation in the inner query is matched against an attribute from the outer query:

SELECT DISTINCT m1.movieTitle, m1.movieYear FROM StarsIn m1 WHERE m1.movieYear - 40 ≤ (SELECT avg(s.birthdate) FROM StarsIn m2, MovieStar s WHERE m2.StarName = s.name AND m2.movieTitle = m1.movieTitle AND m2.movieYear = m1.movieYear)

m2.movieTitle and m2.movieYear are attributes of relation StarsIn in the inner query

These "match-making" attributes must be propagated out so they are available (= accessible) by the outer query !!!!

Rule for re-writing a 2-argument selection for correlated sub-query

Re-write rule for a correlated query:

Comments:

The duplicate elimination (δ) at location (1) is needed to suppress duplicate output values
A condition that use an attribute from the outer relation R (see location (2)) must be moved to the outer σ
(Because only then can the operation receive relation R as input !!!)
An attribute v that is used to compare with an attribute R.u from the outer relation R (see location (3)), must be propagated up the tree
(Because these attributes are needed further up in the query tree)

Example 1: re-writing a 2-argument selection for correlated sub-query

Relations:

MovieStar ( name, addr, gender, birthdate ) StarsIn ( movieTitle, movieYear, starName )

Query:

Find movies where the average age of the stars (in the movie) is ≤ 40 at the time the movie was made

Example to help you understand the query:

Movie1: made in 1970 Stars in movie: star 1 birthdate = 1940 --> age = 30 star 2 birthdate = 1930 --> age = 40 star 3 birthdate = 1920 --> age = 50 star 4 birthdate = 1910 --> age = 60 ============= avg age = 45

You can find the average age of the stars as follows:

Stars in movie: star 1 birthdate = 1940 star 2 birthdate = 1930 star 3 birthdate = 1920 star 4 birthdate = 1910 ============= avg birthdate = 1925 Avg age = 1970 - 1925 = 45
Therefore: Avg age of stars at the time the movie was made = YearInWhichMovieIsMade - avg( birthdates of the stars )

The select condition is therefore:

Avg age of stars at the time the movie was made ≤ 40 <=> YearInWhichMovieIsMade - avg( birthdate of actors ) ≤ 40 <=> YearInWhichMovieIsMade - 40 ≤ avg( birthdate of actors )

SQL query:

SELECT DISTINCT m1.movieTitle, m1.movieYear FROM StarsIn m1 WHERE m1.movieYear - 40 ≤ (SELECT avg(s.birthdate) FROM StarsIn m2, MovieStar s WHERE m2.StarName = s.name AND m2.movieTitle = m1.movieTitle AND m2.movieYear = m1.movieYear)

First, convert the inner query to a logical query plan (tree):

Note:

I made a optimization step by replacing:

σ_{starName=name}( StarsIn × MovieStar ) ⇒ StarsIn ⋈_{(starName=name)} MovieStar

Then, convert the outer query to a logical query plan using a 2-argument selection:

Re-write the 2-argument selection using the re-write rule:

Notice that:

After moving the clauses:

m2.movieTitle = m1.movieTitle m2.movieYear = m1.movieYear

the condition in σ is empty.

An empty condition evaluates to true :

Further optimization

We can use the Algebraic Laws (discussed later) to simplify/optimize the initial query plan:

Re-write the σ_{m2... = m1...} and the × as a join ⋈:
Because γ always produces unique groups, the δ (dupl. elim.) is useless:

Example 2: re-writing a 2-argument selection for correlated sub-query

Relations:

Employee: ssn fname lname dno salary --------- ------ -------- ----------- --------- 888665555 James Borg 1 55000.00 999887777 Alicia Zelaya 4 25000.00 987654321 Jennif Wallace 4 43000.00 987987987 Ahmad Jabbar 4 25000.00 123456789 John Smith 5 30000.00 333445555 Frankl Wong 5 40000.00 666884444 Ramesh Narayan 5 38000.00 453453453 Joyce English 5 25000.00

Query:

SQL query:

SELECT fname, lname FROM employee a WHERE salary = (SELECT max(salary) FROM employee b WHERE b.dno = a.dno)

The inner query is:
The logical query plan using a 2-argument selection is:
Re-write the 2-argument selection using the re-write rule:
Note:

Further optimization

We can use the Algebraic Laws to simplify/optimize the initial query plan:

Re-write the σ_{b.dno = a/dno} and the × as a join ⋈:
Because γ prodcues unique groups, the δ (dupl. elim.) is useless:

Postscript

The general procedure to convert an SQL query into a Relational Algebra query is more complicated

A detail treatise on this subject will require several more lectures
I will follow the text book and translate only the simplified SQL syntax as given earlier
(see: click here)

For a more throrough explanation on translation from SQL to Relational Algebra, see: click here