CS 457/557 Assignment 7 - Query plans

Due Friday, 16 March 2018

A. For the following SQL queries,
  1. Convert to a Logical query plan in relational algebra (Drawing as a tree would be helpful)
  2. Optimize this tree by pushing operations down the tree
  3. Recognize joins, and
  4. Group the associative - commutative operators, into the "Logical Query Plan"
Here is the schema  ---  you can use the short forms below:

Students(id, name, major)
Courses (ccode, title, professor)
Enrollment(id, ccode, grade)


SELECT name, ccode
FROM Students S natural join Enrollment E
WHERE major='CSC'

SELECT name, title, grade
FROM  Students S, Courses C,  Enrollment E
WHERE C.ccode like 'CS%'
   AND S.id =E.id
   AND C.ccode = E.ccode

SELECT name, title, professor
FROM  Students S, Courses C
WHERE id = 42
   AND id, ccode IN
       (SELECT id, ccode FROM Enrollment E)            (you can assume distinctness for this subquery)

SELECT name, ccode, title, professor
FROM  Students S, Courses C, Enrollment E
WHERE S.id = 42
   AND S.id= E.id
   AND C.ccode = E.ccode