---

CS554, Homework 2

---

• The Supplier/Part/Project (SPJ) Database
  • The SPJ data model consists of 4 relations:

    supplier((snum, sname, status, city): stores information on suppliers part(pnum, pname, color, weight, city): stores information on parts proj(jnum, jname, city) stores information on projects spj(snum, pnum, jnum, qty): stores information on which supplier supplies what part to which project

    ---

  • The meaning of the attributes in each relation is as follows:

    supplier(snum, sname, status, city) snum = supplier number (key) sname = supplier name status = supplier status (how good he/she is) city = location of the supplier part(pnum, pname, color, weight, city) pnum = part number (key) pname = part name color = color of the part weight = weight of the part city = city where the part is made proj(jnum, jname, city) jnum = project number (key) jname = project name city = city where the project takes place spj(snum, pnum, jnum, qty): snum = supplier number (foreign key) pnum = part number (foreign key) jnum = project number (foreign key) qty = quantity The meaning of a tuple in the spj relation is: supplier `snum' supplies the part `pnum' to the project `jnum'.

  ---

  ---

  ---

  ---

• Formulate the following queries in SQL on the SPJ Database
  1. Find name of suppliers who supplies to some project in Altanta with a red part

     select distinct sname from supplier, spj, part, proj where supplier.snum = spj.snum and part.pnum = spj.pnum and proj.jnum = spj.jnum and proj.city = 'Atlanta' and part.color = 'Red'

  2. Find name of suppliers who supplies to at least 4 projects in Altanta with a red part

     select sname from supplier, spj, part, proj where supplier.snum = spj.snum and part.pnum = spj.pnum and proj.jnum = spj.jnum and proj.city = 'Atlanta' and part.color = 'Red' group by supplier.sname having count(proj.jnum) >= 4

  3. Find name of suppliers who do not supply to any project in Paris

     select sname from supplier where snum not in ( select distinct snum from spj, proj where proj.jnum = spj.jnum and proj.city = 'Paris' ) This query is wrong: select sname from supplier, spj, proj where supplier.snum = spj.snum and proj.jnum = spj.jnum and proj.city != 'Paris' Proof: spj: s1 p1 j1 100 s1 p1 j2 100 proj: j1 Paris j2 Rome Query will select s1 because s1 p1 j2 100 and j2's city (Rome) != Paris Supplier s1 cannot be part of the answer, because: s1 p1 j1 100 and j1's city = Paris

  4. For each supplier and part, list (1) supplier name, (2) part name and (3) the (total) quantity of of the parts being shipped.

     The answer has the following form:

     sname pname total shipped (it's OK to have a diff title) ---------- ---------- --------------------------------- Adams Bolt 300 Adams Cam 1000 Blake Screw 700 Bond Cam 100 ...

     select sname, pname, sum(qty) from spj, supplier, part where spj.snum = supplier.snum and spj.pnum = part.pnum group by sname, pname

  5. Find name of suppliers who has more shipments than the supplier 'Newton' (one tuple in relation spj represents one shipment)

     select sname from spj, supplier where spj.snum = supplier.snum group by supplier.sname having count(*) > (select count(*) from spj, supplier where spj.snum = supplier.snum and sname = 'Newton')