Question

Consider the following schema: 

Suppliers (sid, sname, address) 

Parts (pid, pname, colour) 

Catalog(sid, pid, cost) 

The key for Suppliers is sid, for Parts is pid, and for Catalog is sid and pid The Catalog relation associates prices charged for parts by suppliers. 

Write the following queries using relational algebra. For items (a) through (e), use the "sequences of assignments" form. For items (f) and (g), use the "expression tree" form. List all assumptions. (Some marks will be given for the quality of your answers.) 

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

(b) Find the ids of suppliers who supply some blue or magenta part.

 (c) Find the ids of suppliers who supply every magenta part. 

(d) Find the ids of suppliers who supply every part. 

(e) Find the ids of parts supplied by at least two different suppliers 

(f) Find the ids of the most expensive parts supplied by company name named "Screw-2-You Limited". 

(g) Find the ids of parts supplied by every supplier at less than or equal to $50. (If any supplier either does not supply the part or charges more than $50 for it, then that part should not be in the final relation.)

Answer 1

-- 1 supplier supplies only blue part

πsname(πsid((πpidσcolor=_’blue’_Parts) Catalog) Suppliers)

--2 supplier supplies either of colour

πsid(πpid(σcolor=’blue’∨ color=’magenta’ Parts) catalog)

--3 supplier supplies every magenta part

(πsid,pid Catalog)/(πpid σcolor=’magenta’ Parts)

--4 Below query to get supplier supplies every part

(πsid,pid Catalog)/(πpid Parts)

As per HOMEWORKLIB RULES we csn only solve 1st 4 sub questions.