Csc 417/517 Assignment 6

Due Wednesday Nov. 7

For the following SQL queries,
  1. Parse them
  2. Convert to a Logical query plan in relational algebra (Drawing as a tree would be helpful)
  3. Optimize this tree by pushing operations down the tree, and
  4. Group the associative - commutative operators, into the "Logical Query Plan"
  5. [Create the best Physical query plan by choosing a join order]
The schema is, you can use the short forms:

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

S (i, n, m )
C(c, t, p)
E(i, c, g)

SELECT n, c
FROM S natural join E
WHERE n='Lin' AND c like 'csc%'

SELECT n, t
FROM (S natural join C) natural join E
WHERE p='Jensen' AND m='PHY'

SELECT n, t, p
FROM  S, C
WHERE i = 'mmouse'
   AND i,c IN
       (SELECT i, c FROM E)      (you can assume distinctness for this subquery)