Jump to navigation Jump to search

Southwind database

Graduated question


ER diagram for southwind databse: Image

tprod(code, dscr, pric) tcust (code, firm, addr) tpurc (cust, recv) tpurcd (cust, recv, prod, qnty) tship (cust,recv,addr,shpd) tshipd (cust, recv, shpd, prod, qnty) tsupl (code, addr) tdlvr (supl, recv) tdlvrd (supl, recv, prod, qnty) tretn (cust, recv, prod, qnty, expl) tstck (chck, prod, qnty)

The table tpurcd gives details of all products ordered. This table gives details of all purchase orders.

  FROM tcust
  JOIN tpurcd ON (tcust.code=tpurcd.cust)
  JOIN tprod  ON (

Some of the tables are joined by a composite foreign key. For example to link shipment details to a shipment record we must join on (cust,shpd).

SELECT addr, tship.shpd, prod
   FROM tship JOIN tshipd
     ON(tshipd.cust=tship.cust AND
 WHERE tship.addr IS NOT NULL