Query on relational schema


#1

Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

What does the following query accomplish on the above schema?

SELECT C.sid
FROM Catalog C
WHERE ( NOT EXISTS ( SELECT P.pid
FROM Parts P
WHERE P.color = ‘red’ AND
( NOT EXISTS ( SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid AND C1.pid = P.pid))))
OR ( NOT EXISTS ( SELECT P1.pid
FROM Parts P1
WHERE P1.color = ‘green’ AND
(NOT EXISTS (SELECT C2.sid
FROM Catalog C2
WHERE C2.sid = C.sid AND C2.pid = P1.pid))))

(A) Gives the sids of suppliers who supply some red part or supply some green part.
(B) Gives the sids of suppliers who supply some red part and supply some green part.
© Gives the sids of suppliers who supply every red part or supply every green part.
(D) Gives the sids of suppliers who supply every red part and supply every green part.