---

CS554, Homework 1

---

• 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 Relational Algebra on the SPJ Database
  1. Find name of suppliers who supplies to some project in Altanta with a red part

     R1 = σcity = 'Atl' ( project ) R2 = σcolor = 'red' ( part ) R3 = R1 ⋈ spj ⋈ R2 // R3 = suppliers who supplies a red part to a proj in Atl Ans = πsname ( R3 ⋈ supplier )

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

     R1 = σcity = 'Atl' ( project ) R2 = σcolor = 'red' ( part ) R3 = R1 ⋈ spj ⋈ R2 // R3 = suppliers who supplies a red part to a proj in Atl R4 = snum F count(jnum) ( R3 ) R5 = σcount >= 4 ( R4 ) Ans = πsname ( R5 ⋈ supplier )

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

     R1 = σcity = 'Atl' ( project ) R2 = R1 ⋈ spj // R2 = suppliers who supplies to a proj in Atl R3 = πsnum ( supplier ) − πsnum ( R2 ) Ans = πsname ( R4 ⋈ supplier )

  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 ---------- ---------- --------------------------------- Adams Bolt 300 Adams Cam 1000 Blake Screw 700 Bond Cam 100 ...

     R1 = snum,pnum F sum(qty) ( spj ) Ans = πsname, pname ( supplier ⋈ R1 ⋈ part )

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

     R1 = snum F count(jnum,pnum,qyt) ( spj ) R2 = F count(jnum,pnum,qyt) ( spj ⋈ σsname='Newton' (supplier) ) R3 = R1 ⋈ count > count R2 Ans = π sname R3 ⋈ supplier Also correct if you have: R1 = snum F count(jnum,pnum) ( spj ) R2 = F count(jnum,pnum) ( spj ⋈ σsname='Newton' (supplier) ) R3 = R1 ⋈ count > count R2 Ans = π sname R3 ⋈ supplier