Exercise 8.1

Supplier(ID, name, loginID, password)
    1 Acme acme joshua
    2 Ronco ronco ronco
    3 Popeil popi pp123
Part(ID, name, price, supplierID)
    1 birdSeed 5 1
    2 TNT 100 1
    3 leadShot 20 NULL
    4 pocketFisherman 19.99 3
 
  Job(ID, name, address)
    1 StudentUnion Calvin
    2 NULL Calvin
    3 ArtMuseum GrandRapids
  PJ(PID, JID, quantity, date)
    1 1 10 10-20-2018
    2 2 2 10-21-2018
    1 2 3 10-21-2018
    4 1 2 10-22-2018
Given this database, manually compute the results for these queries.
  1. Names of parts that are under $10 and are supplied by Acme
  2. Names of parts that are used on job #1
  3. Names of parts that are supplied by Popeil and used at Calvin

Exercise 8.2

Use this schema.

Supplier(ID, name, loginID, password, address)
Part(ID, name, price, supplierID)
Job(ID, name, address)
PartJob(partID, jobID, quantity, date)
PartPart(partID, subpartID, quantity)

Write queries to retrieve the following things.

  1. The names of suppliers from Grand Rapids, ordered alphabetically
  2. The names of parts $20 and over for which there is no supplierID
  3. The names of parts used in some Calvin job, with quantity & date
  4. The names of parts used to build the part “widget”
“Codd had a bunch of fairly complicated queries,” Chamberlin said. “And since I’d been studying CODASYL (the language used to query navigational databases), I could imagine how those queries would have been represented in CODASYL by programs that were five pages long that would navigate through this labyrinth of pointers and stuff. Codd would sort of write them down as one-liners. … (T)hey weren’t complicated at all. I said, ‘Wow.’ This was kind of a conversion experience for me. I understood what the relational thing was about after that.” — D. Chamberlin, E. F. Codd, 2003.