Supplies and Shipments SQL

INFO

  • items (item, unitWeight) item has unitWeight
  • busEntities(entity, shipLoc, address, phone, web, contact) every business entity has shipLoc (to be used by shipping companies), address, phone, web link, and contact info
  • billOfMaterials(prodItem, matItem, QtyMatPerItem) to produce 1 unit of prodItem, manufacturers need QtyPerItem units of matItems
  • supplierDiscounts(supplier, amt1, disc1, amt2, disc2) supplier gives discount disc1 for purchase amount between amt1 and amt2, and disc2 for purchase amount above amt2. Note that discounts will be expressed as fractions rather then percentage, e.g., 0.15 rather than 15 (%).
  • supplyUnitPricing(supplier, item, ppu) item supplied by sup has ppu (price per unit)
  • manufDiscounts(manuf, amt1, disc1) manufacturer manuf gives discount disc1 for manufacturing cost in excess of amt1 of the base cost, which is computed according to manufUnitPricing table - see below.
  • manufUnitPricing(manuf, prodItem, setUpCost, prodCostPerUnit) For manufacturing of prodItem by manuf, the manufacturer base cost is computed as setUpCost plus the prodPricePerUnit times the qty of the produced prodItem
  • shippingPricing(shipper, fromLoc, toLoc, minPackagePrice, pricePerLb, amt1, disc1, amt2, disc2) The shipping cost for a shipper from fromLoc to toLoc is computed as follows: determine the total weight of all items shipped from fromLoc to toLoc (by all senders at fromLoc to all recipients at toLoc) base cost: is computed based on total weight of shipment and pricePerLb discounted cost: then for amount between amt1 and amt 2, disc 1 is applied; and to the amount above amt2, disc2 is applied total cost: the maximum of inPackagePrice and the discounted price
  • customerDemand(customer, item, qty) The demand by customer is qty units of item; note that items may come from any combination of manufacturers and/or suppliers.
  • supplyOrders(item, supplier, qty) qty units of item were ordered from supplier
  • manufOrders(item, manuf, qty) qty units of item were ordered to be produced by manuf
  • shipOrders(item, shipper, sender, recipient, qty) qty units of item were requested to be shipped by shipper from sender to recipient

Implement the following SQL views (use the given view names):

1. shippedVsCustDemand: For every (customer, item) in customerDemand, compute the total qty of this item shipped to this customer, along with the demand qty. Note that the items may come from manufacturers and/or suppliers

2.totalManufItems: For every item in manufOrders, compute the total qty of this (product) item produced

3.matsUsedVsShipped: For every manuf in manufOrders, and matItem used by this manuf (i.e., manuf ordered a prodItem that requires a matItem according to billOfMaterials) compute:

  • 1.the total qty of this matItem necessary to produce all the ordered (product) items by this manuf,
  • 2.the total qty of this matItem shipped (by all shippers) to this manufacturer

4.producedVsShipped: For every (item, manuf) in manufOrders compute the total qty of this item shipped out from this manuf (by all shippers to any recipient), along with the total qty of this item produced by this manufacturer (in manufOrders).

5.suppliedVsShipped: For every (item, supplier) in supplyOrders compute the total qty of this item shipped from this supplier (by all shippers to any recipient), along with the ordered qty of this item.

6.perSupplierCost: For each supplier in supplierDiscounts, compute the total cost of items supplied by this supplier (according to supplyOrders).

7.perManufCost: For each manufacturer in manufDiscounts, compute the total manufacturing cost of all items produced by this manufacturer (according to manufOrders).

8.perShipperCost: For each shipper in shippingPricing, compute the total shipping cost of this shipper.

9.totalCostBreakdown: Compute the total supply cost, manufacturing cost, shipping cost, and the overall cost.

Using the defined SQL views, implement the following SQL queries:

1.Find customers, whose demand is NOT satisfied, i.e., are not shipped all the quantities of items

2.Find suppliers, whose orders (in supplyOrders table) are not fully shipped out.

3.Find manufacturers who do NOT have enough materials to produce ordered product quantities, i.e., not enough materials were shipped to them

4.Find manufacturers whose orders are not fully shipped out

Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.