Consider the following tables(see create_empty_tables.sql file in the solution_sql\config folder and the JSON DB files in the testDBs folder). For foreign key constraints definition, note that suppliers, manufacturers, shippers, customers, senders, and recipients are business entities. Note also that product items, and material items are items.

1.items(item, unitWeight): item has unitWeight. Assume that all items, including supplied, manufactured and transported must appear in this table.

2.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. Assume that all business entities, including suppliers, manufacturers and customers must appear in this table.

3.billOfMaterials(prodItem, matItem, QtyMatPerItem): to produce 1 unit of prodItem, manufacturers need QtyPerItem units of matItems (e.g., if a table (prodItem) needs 4 Legs (matItems), QtyPerItem = 4)

4.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 are expressed as fractions rather than percentage, e.g., 0.15 rather than 15 (%).

5.supplyUnitPricing(supplier, item, ppu): item supplied by sup has ppu (price per unit)

6.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.

7.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

8.shippingPricing(shipper, fromLoc, toLoc, minPackagePrice, pricePerLb, amt1, disc1, amt2, disc2): The shipping cost for a shipper from fromLoc to toLoc is computed as follows:

a. determine the total weight of all items shipped from fromLoc to toLoc (by all senders at fromLoc to all recipients at toLoc)

b. base cost: is computed based on total weight of shipment and pricePerLb

c. discounted cost: then for amount between amt1 and amt 2, disc 1 is applied; and to the amount above amt2, disc2 is applied

d. total cost: the maximum of minPackagePrice and the discounted price

9.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.

10.supplyOrders(item, supplier, qty): qty units of item were ordered from supplier

11.manufOrders(item, manuf, qty): qty units of item were ordered to be produced by manuf

12.shipOrders(item, shipper, sender, recipient, qty): qty units of item were requested to be shipped by shipper from sender to recipient. Note senders and recipients are business entities such as suppliers, manufacturers, and customers.

Question -

Make a SQL view "query3" that computes a table over the schema (item, qty, amount, ppu4), where o qty is the total quantity of this item ordered from all suppliers (according to supplyOrders); o amount is the total dollar amount that would be paid to all suppliers for this item before discounts (according to supplyUnitPricing) o ppu4 is the average price paid (before discounts) per one unit of this item (if qty above is positive) and NULL otherwise. The view should produce a tuple for every item in the supplyUnitPricing table. Note that qty and amount should be 0 (zero) for an item in supplyUnitPricing table that does not appear in supplyOrders table. Order the result by item and make sure there are no duplicates.