Consider the following database schema:



Student(StudID,SName,SAddr)

Transcript(StudID,DeptCode,CrsNum,Semester,Grade)

(a) Write an SQL query that generates a table of the SNames of all students taking courses
in the department with DeptCode ?CS?.
(b) Give the ?naive? translation of your SQL query into the relational algebra (as given by
the general translation of SQL to relational algebra.)
(c) Assume that there is an index with search key DeptCode on the transcript table, and no
other indexes. Describe carefully and completely how you would evaluate the above
query in a most ecient way. Give a precise query plan and carefully describing each
operation.
(d) Give the relational algebra expression for this query that most closely corresponds to the
way you chose (in the previous subproblem) to eciently evaluate this query.

(a) Write an SQL query that generates a table of the SNames of all students taking courses

in the department with DeptCode ?CS?.

Solution:




SELECT S.SName


FROM Student S, Transcript T


WHERE S.StudID = T.StudID AND DeptCode = 'CS'

(b) Give the ?naive? translation of your SQL query into the relational algebra (as given by

the general translation of SQL to relational algebra.)

Solution:

(c) Assume that there is an index with search key DeptCode on the transcript table, and no

other indexes. Describe carefully and completely how you would evaluate the above

query in a most ecient way. Give a precise query plan and carefully describing each

operation.

Solution:

First select Transcript on DeptCode, then project out everything but StudId, and then

join the result with Student. Use sort-merge join or block-nested loops. Before the join,

get rid of Address in Student to reduce size. Finally, project out everything but SName.

(d) Give the relational algebra expression for this query that most closely corresponds to the

way you chose (in the previous subproblem) to efficiently evaluate this query.

Solution: