### 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') ```