Supplier (sid: integer, sname: string, address: string ) Part (pid: integer, pna

Question

Supplier (sid: integer, sname: string, address: string )

Part (pid: integer, pname: string, colour: string )

Catalog (sid: integer, pid: integer, cost: real )

The relation Supplier stores suppliers and the key of that relation is sid. The relation Part stores parts, and pid is the key of that relation. Finally, Catalog stores which supplier supplies which part at which cost. The key is the combination of the two attributes sid and pid.

4. Find the names of suppliers who supply some red part or are based at 21 George Street.

6.Find pairs of IDs such that the supplier with the first ID charges more for some part than the supplier with the second ID. (Hint: you may want to create temporary relations to get two copies of Catalog)

7. Find the IDs of suppliers who supply only red parts. (Hint: A supplier supplies only red parts if it is not the case that the supplier offers a part that is not red. This question is a challenge!)

8. Find the IDs of suppliers who supply every part. (Hint: A supplier supplies every part if it is not the case that there is some part which they do not supply. Use set difference and cartesian product.

Explanation / Answer

/*4. Find the names of suppliers who supply some red part or are based at 21 George Street. */
SELECT S.sname
FROM Supplier S
WHERE S.address = '21 George Street'
OR S.sid IN ( SELECT C.sid
FROM Part P INNER JOIN Catalog C ON P.pid = C.pid
WHERE P.colour='red')

/*6.Find pairs of IDs such that the supplier with the first ID charges more for some part than the supplier with the second ID.
(Hint: you may want to create temporary relations to get two copies of Catalog)*/
SELECT C1.sid, C2.sid
FROM Catalog C1 INNER JOIN Catalog C2 ON C1.pid = C2.pid AND C1.sid = C2.sid
WHERE C1.cost > C2.cost

/*7. Find the IDs of suppliers who supply only red parts. (Hint: A supplier supplies only red parts
if it is not the case that the supplier offers a part that is not red. This question is a challenge!)*/
SELECT s.sid FROM Supplier s INNER JOIN Catalog c ON s.sid=c.sid
INNER JOIN Part p ON c.pid=p.pid WHERE p.colour='RED';
/*8. Find the IDs of suppliers who supply every part. (Hint: A supplier supplies every part
if it is not the case that there is some part which they do not supply. Use set difference and cartesian product. */
SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
FROM Part P
WHERE NOT EXISTS (SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid))

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.