Question

Consider the Schema diagram for a university database

student takes ID course id sec id semester 1er 4112 dept_name tot cred grade advisor section course_id sec id semester year building TOOM_10 time_slof_id course course id title dept name credits department dept name building budget time slot time slot id day start time end time instructor classroom prerea course id prereg id ID name building NOOM O Capacity feaches dept_name salary ID course id serid semester Express each of the following queries using relational algebra. (10 points each) Note: Your queries should use as few relations as arguments as possible (i.e. do not include relations that are not needed to produce the results).

a. Find the IDs of all students who have taken all of the CMPSC courses. Use the division operation.
b. Do the same operation as in problem a, using only fundamental operations.

Answer 1

a.

SELECT DISTINCT(ID) FROM takes as t
WHERE NOT EXISTS (
(SELECT c.course_id FROM course as c )
EXCEPT
(SELECT tc.course_id FROM  takes as tc  WHERE tc.course_id = t.course_id and title = 'CPMSC') );

b.
SELECT DISTINCT(ID)
FROM takes,course
WHERE takes.course_id = course.course_id and course.title = 'CMPSC';

--We are retrieving distinct studentID from takes table,For the course_id in takes table retrieve the corresponding course_id
--from course table and choose the course whose title is CPMSC

this can also be written as:
SELECT student.ID
FROM takes,student,section,course
WHERE student.ID = takes.ID and takes.course_id = section.course_id and section.course_id = course.course_id and course.title = 'CPMSC';