CPSC 408 Assignment 01

CPSC 408
Assignment 01, 65 Points
Consider a relational database with the following schema:
Suppliers (sid, sname, address)
Parts (pid, pname, color)
Catalog (sid, pid, cost)
The relation Suppliers stores supplier related information. Parts records information
about parts. Catalog stores which supplier supplies which part at which cost. Think of it as
a linking relation between Suppliers and Parts.
Write relational algebra expressions for the following queries.
1. Find the names of suppliers who supply some red part.
2. Find the IDs of suppliers who supply some red or green part.
3. Find the IDs of suppliers who supply some red part or are based at 21 George Street.
4. Find the names of suppliers who supply some red part or are based at 21 George
Street.
5. Find the IDs of suppliers who supply some red part and some green part.(Hint: use
intersection of relations or join the same relation several times)
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 cross product. This question is a challenge, too!)
The following queries are written in relational algebra. What do they mean?
1. πsname(σcolor="red"(Part)⋈σcost<100(Catalog)⋈Supplier)
2.πsname(πsid(σcolor="red"(Part)⋈σcost<100(Catalog))⋈Supplier)
3. πsname(σcolor="red"(Part)⋈σcost<100(Catalog)⋈Supplier) ∩
πsname(σcolor="green"(Part)⋈σcost<100(Catalog)⋈Supplier)
4. πsid(σcolor="red"(Part)⋈σcost<100(Catalog)⋈Supplier) ∩
πsid(σcolor="green"(Part)⋈σcost<100(Catalog)⋈Supplier)
5.πsname(πsid,sname(σcolor="red"(Part)⋈σcost<100(Catalog)⋈Supplier) ∩
πsid,sname(σcolor="green"(Part)⋈σcost<100(Catalog)⋈Supplier))
References:
Most of these exercises are taken from the book “Database Management Systems” by
Ramakrishnan/Gehrke, Chapter 4.
Deliverables:
Submit your answers in a single PDF document, alongside a README inside a compressed
folder with the name FirstNameInitialLastName_AssignmentNumber.zip (e.g.,
SClibourne_Assignment1.zip). The README should include your name, any special
instructions for running your code, and all references used for the assignment.
You can either write out your queries in Word and then convert to PDF or take screenshots
of your handwritten answers and use those in your PDF document.