November 9, 2015

Query: “Find the schedules and rooms of all courses taken by any Math major.”

Symbolically: schedule, room ( major=‘Math’ ((Student |x| Enroll) |x|

Class))

Relational algebra:

Student JOIN Enroll GIVING Temp1Temp1 JOIN Class GIVING Temp2SELECT Temp2 WHERE major=‘Math’ GIVING Temp3PROJECT Temp3 OVER schedule,

room

An internal node can be executed when its operands are available

Node is replaced by the result of the operation it represents

Process continues until root node is reached

Root node is executed last, and is replaced by the result of the entire query

Same SQL statement can be translated to different relational algebra statements

Performing SELECT early reduces size of intermediate nodes-See next slide

Symbolically: schedule, room ((( major=‘Math’ (Student)) |x| Enroll) |x|

Class)

Relational algebra:

SELECT Student WHERE major=‘Math’

GIVING T1T1 JOIN Enroll GIVING T2T2 JOIN Class GIVING T3PROJECT T3 OVER schedule, room

Query: “Find the schedules and rooms for all Math majors who received a grade of F.”

Symbolically: schedule, room (( major=‘Math’ & grade=‘F’ (Student |x| Enroll)) |x| Class))

Relational algebra:

Student JOIN Enroll GIVING T1SELECT T1 WHERE major=‘Math’ AND grade=‘F’

GIVING T2T2 JOIN Class GIVING T3PROJECT Temp3 OVER schedule, room

Apply both selections to their corresponding tables before the join reduces the size of the intermediate tables

Symbolically: schedule, room ((( major=‘Math’ (Student)) |x| ( grade=‘F’ (Enroll))) |x|

Class))

Relational algebra:

SELECT Student WHERE major=‘Math’ GIVING T1SELECT Enroll WHERE grade=‘F’ GIVING T2T1 JOIN T2 GIVING T3T3 JOIN Class GIVING T4PROJECT Temp4 OVER schedule, room

Performing projections early will reduce the number of columns in the intermediate table

SELECT Student WHERE major=‘Math’ GIVING T1

PROJECT T1 OVER stuId GIVING T2SELECT Enroll WHERE grade=‘F’ GIVING T3T2 JOIN T3 GIVING T4PROJECT T4 OVER classNumber GIVING T5T5 JOIN Class GIVING T6PROJECT T6 OVER schedule, room