Consider the following schema $($primary key fields are underlined$)$:

Customers $($cid$,$ cname, city$)$

$-$cid is the unique identifier for the customer

$-$cname is the name of the customer

$-$city is the city where customer resides

$-$cid is the primary key.

Suppliers $($sid$,$ sname, city$)$

$-$sid is the unique identifier for the supplier

$-$sname is supplier$$s name

$-$city is the city supplier is located

$-$sid is the primary key

Parts $($pid$,$ pname, color$)$

$-$pid is the unique identifier for the part

$-$pname is the part$$s name

$-$color is part$$s color

$-$pid is the primary key.

Catalog $($pid$,$ sid,price$)$ : tuples in the Catalog table correspond to a catalogentries: i$.$e$.,$ parts and their suppliers.

$-$pid is the id of the part in the catalog entry

$-$sid is id of the supplier that supplies the part in the catalog

$-$price is the cost of the part in the catalog

$-($sid$,$pid$)$ is the primary key

$-$sid is a foreign key referencing Suppliers$($sid$)$

$-$pid is a foreign key referencing Parts$($pid$)$

Orders $($cid$,$pid, sid, qty$)$ : each tuple in Orders corresponds to an order. Assume each customer orders a particular part from a supplier once.

$-$cid is the customer that buys the part

$-$pid is the part sold

$-$sid is the supplier that supplies the part

$-$qty is the number of parts sold in a particular order

$-($cid$,$sid, pid$)$ is the primary key.

$-($sid$,$pid$)$ is a foreign key referencing Catalog $($sid$,$pid$)$

Based on the above database schema, express the following queries in relational algebra. Assume set semantics for all operations.

$1.$ Find all distinct parts supplied by Pullman stores. Return $$pid$$s of those parts.

$2.$ Find the suppliers in Pullman who supply a yellow part for less than $$11.$ Return $$sid$$s and names for those suppliers.

$3.$ Find all parts which are provided by some supplier $($i$.$e$.,$ they appear in the catalog$)$ but they were never ordered by a customer. Return the $$pid$$s and names of those parts.

$4.$ Find all customers who ordered one of the products that Andy ordered. Return names and cities of those customers and the $$pid$$s of the products they ordered.

$5.$ Find the suppliers who have received orders from customers who live in the city where that supplier is located. Return $$sid$$s$,$ names, and cities of those suppliers.

$6.$ Find customers who ordered more than $2$ items from a single supplier $($i$.$e$.,$ sum of the quantities of all parts customer purchased from the supplier is at least $3).$ Return names of the customers,$$sid$$s of the suppliers, and number of items they ordered.

$7.$ Find the customer $$cid$$s who paid more than $$20$ on $$green$$ parts.

$8.$ Find the parts which are supplied by at least $3$ different suppliers. Return the $$pid$$s$,$ names, and colors of those parts.

$9.$ For each part in the catalog find the supplier that offers the lowest price; give the part name, supplier $$sid$$ and the price supplier sells the part for.

$10.$ Find the number of suppliers in each city.

The same data is also available in appendix A$.$ In the tool, you can try the queries and check the output. The expected output for each relational algebra expression is provide in appendix B$.$

APPENDIX$-$A

Customers:

cid$$cname$$city

c$1$John$$Pullman

c$2$Alli$$Pullman

c$3$Kelly$$Moscow

c$4$Tom$$Spokane

c$5$Sam$$Spokane

c$6$Andy$$Pullman

Suppliers:

sid$$sname$$city

s$1$Best Tools$$Pullman

s$2$Pullman Tools$$Pullman

s$3$Acme Tools$$Moscow

s$4$Parts & More$$Pullman

s$5$Supply Store$$Spokane

s$6$Spokane HW Store$$Spokane

Parts:

pid$$pname$$color

p$1$Part$1$red

p$2$Part$2$red

p$3$Part$3$green

p$4$Part$4$green

p$5$Part$5$yellow

p$6$Part$6$red

P$7$Part$7$red

Catalog:

pid$$sid$$price

p$1$s$2$$$48$

p$1$s$3$$$51$

p$1$s$4$$$52$

p$1$s$6$$$49$

p$2$s$1$$$21$

p$2$s$2$$$20$

p$3$s$4$$$15$

p$5$s$1$$$10$

p$5$s$2$$$12$

p$5$s$3$$$9$

p$5$s$4$$$10$

p$5$s$6$$$11$

p$7$s$3$$$33$

p$7$s$4$$$35$

Orders:

cid$$pid$$sid$$qty

c$1$p$1$s$23$

c$1$p$3$s$41$

c$3$p$2$s$21$

c$3$p$3$s$42$

c$3$p$5$s$31$

c$3$p$5$s$22$

c$4$p$1$s$33$

c$4$p$2$s$24$

c$4$p$3$s$41$

c$6$p$3$s$41$

c$6$p$5$s$32$

c$6$p$5$s$63$

c$6$p$2$s$23$

APPENDIX$-$B

$1.$

Catalog.pid

p$1$

p$2$

p$3$

p$5$

p$7$

$2.$

Catalog.sid$$ Suppliers.sname

s$1$ BestTools

s$4$ Parts and More

$3.$

Parts.pid Parts.pname

p$7$ Part$7$

$4.$

Customers.cname$$ Customers.city$$ Orders.pid

John$$ Pullman$$ p$3$

Kelly$$ Moscow$$ p$2$

Kelly$$ Moscow$$ p$3$

Kelly$$ Moscow$$ p$5$

Tom$$ Spokane$$ p$2$

Tom$$ Spokane$$ p$3$

Andy$$ Pullman$$ p$3$

Andy$$ Pullman$$ p$5$

Andy$$ Pullman$$ p$2$

$5.$

Orders.sid$$ Suppliers.sname$$ Customers.city

s$2$ Pullman Tools$$ Pullman

s$4$ Parts and More$$ Pullman

s$3$ Acme Tools$$ Moscow

$6.$

Customers.cname$$ Orders.sid$$ numItems

John$$ s$23$

Kelly$$ s$23$

Tom$$ s$33$

Tom$$ s$24$

Andy$$ s$63$

Andy$$ s$23$

$7.$

Orders.cid

c$3$

$8.$

Parts.pid; Parts.pname; Parts.color

p$1$; Part$1$; red

p$5$; Part$5$; yellow

$9.$

Parts.pname; Catalog.sid$2$; Catalog.price$2$

Part$1$; s$2$; $48$

Part$2$; s$2$; $20$

Part$3$; s$4$; $15$

Part$5$; s$3$; $9$

Part$7$; s$3$; $33$

$10.$

Suppliers.city; numSupplier

Pullman; $3$

Moscow; $1$

Spokane; $2$