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

The Catalog relation lists the prices charged for parts by Suppliers.

Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate.

Explanation:

(a) Find the names of suppliers who supply some yellow part.

RA: sname(Suppliers on (color=0yellow0(Parts) on Catalog))

SQL:

SELECT S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’yellow’

(b) Find the sids of suppliers who supply some green part but not ared part.

RA: sid(Suppliers on (color=0green0(Parts) on Catalog))−

sid(Suppliers on (color=0red0(Parts) on Catalog))

SQL:

SELECT S.sid

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’green’

EXCEPT

SELECT T.sid

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’

(c) Find the sids and snames of suppliers who supply a’bolt’ whose price is under 100

dollars or whose color is red.

There is ambiguity here too. Some of you may interpret the questionto mean a bolt

whose price is under 100 dollars or a bolt whose color is red. Someof you may interpret

it to mean a bolt whose price is under 100 dollars or a red part. Ihave given points to

both these versions.

RA: sid,sname(Suppliers on((pname=0bolt0^cost<100)_(color=0red0)(Parts onCatalog)))

RA: sid,sname(Suppliers on((pname=0bolt0^(cost<100_color=0red0))(Parts onCatalog)))

SQL:

SELECT S.sid, S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND C.cost < 100

UNION

SELECT T.sid, T.sname

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’

SELECT S.sid, S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND C.cost < 100

UNION

SELECT T.sid, T.sname

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND P.color = ’red’

(d) Find the sids of suppliers who supply all parts.

RA: sid((sid,pid(Catalog))/(pid(Parts))

SQL:

SELECT C.sid

FROM Catalog C

WHERE NOT EXISTS (( SELECT P.pid

FROM Parts P )

EXCEPT

(SELECT C1.pid

FROM Catalog C1

WHERE C1.sid = C.sid ))

(e) Find pids of parts supplied by at least two differentsuppliers

RA: (CatPairs(1 !sid1, 2 !pid1, 3 !cost1, 4 !sid2, 5 !pid2, 6!cost2), Catalog×

Catalog)

pid1((pid1=pid2)^(sid16=sid2)CatPairs)

SQL:

SELECT P.pid

FROM Catalog P, Catalog C

WHERE P.pid = C.pid AND P.sid <> C.sid

(f) Find the names of suppliers who supply some brown part.

RA: sname(Suppliers on (color=0brown0(Parts) on Catalog))

SQL:

SELECT S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’brown’

(g) Find the sids of suppliers who supply some yellow part but nota red part.

RA: sid(Suppliers on (color=0yellow0(Parts) on Catalog))−

sid(Suppliers on (color=0red0(Parts) on Catalog))

SQL:

SELECT S.sid

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’yellow’

EXCEPT

SELECT T.sid

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’

(h) Find the sids and snames of suppliers who supply a’nut’ whose price is under 10 dollars

or whose color is pink.

There is ambiguity here too. Some of you interpret the question asa nut whose price is

under 10 dollars or a nut whose color is pink. Some of youinterpret it as a nut whose

price is under 10 dollars and a part whose color is pink. Bothversions are treated as

correct answers.

RA: sid,sname(Suppliers on((pname=0nut0^cost<10)_(color=0pink0)(Parts onCatalog)))

RA: sid,sname(Suppliers on((pname=0nut0^(cost<10_color=0pink0))(Parts onCatalog)))

SQL:

SELECT S.sid, S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND C.cost < 10

UNION

SELECT T.sid, T.sname

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’pink’

SELECT S.sid, S.sname

FROM Suppliers S, Parts P, Catalog C

WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND C.cost < 10

UNION

SELECT T.sid, T.sname

FROM Suppliers T, Parts P, Catalog C

WHERE T.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND P.color = ’pink

answer: 500 wph.

explanation:

answer: make you irritable