CS550 Assignment 3 Solution

Project Assignment

The project has two required parts—Part 1 and Part 2—and an optional Part 3.

Project Part 2 is also Homework Assignment 3 (HA3).

Part 1: Database Design
Consider the following Supply Chain information system description. The system should support a collaborative supply chain composed of suppliers, manufacturers, shippers and end-customers.

Items of different kinds are being moved in the supply chain.Manufacturers use Items of materials to manufacture Items of products for customers. Suppliers supply Items of materials to manufacturers; they also supply Items directly to Customers.

Shippers (e.g., UPS, FedEx, etc.) move items from one businessentity (supplier, manufacturer, customer, etc.) to another.

Items have a unique id and weight. Every business entity (suppliers, manufacturers, customers etc.) is identified by its id, and has a shipping location (to be used by Shippers for shippingorders), address, phone, web location, and contact information.

Every product item (e.g., a table) has a number of associated material/part items in certain quantities necessary to produce 1unit of the product item. For example, a table product item, requires 1 tabletop item, 4 leg items, and 8screw items. Suppliers supply Items, using price per unit, whichmay vary among different Suppliers for the same Item.

Suppliers have volume discount applied on the dollar amountcomputed based on price per unit.

Volume discount is described by a percentage of deduction for amount above a predetermined bound.

Manufacturers produce product Items; this production has an associated setUpCost and product cost per unit.


Shippers price shipping services per pairs of (source, destination)pairs, where sources and destinations are shipping locations of business entities.

The pricing of each shipper is based on the total weight of shipment from source to destination, using price per lb., and avolume discount applied on the total dollar amount.

Customers have demand quantity for certain Items.

The orders are recorded separately for shipping, manufacturing, and supply.

Shipping orders capture information about a shipper, sender, and recipient (who are business entities) and the Item being shipped, and record the quantity of the Item shipped.

Manufacturing orders capture information about a manufacturer, amanufactured Item and the ordered quantity; and

Supply orders capture information about a supplier, Item and thequantity supplied.

1. Create an ER diagram and specify all integrity constraints. If some information is missing, suggest additional assumptionsand briefly explain their rationale.
2. Translate the ER design into CREATE TABLE definitionsusing SQL, and add CHECK constraints as necessary
Important note: In Part 1, DO NOT try to reverse-engineer tables in Part2 - this won't give the correct answers!





Part 2: Database Implementation (NOTE: you need to submit the script file "queries.sql" ONLY for Project Part 2 = HA3. See (B) below.)

A. Consider the following tables (see file create_empty_tables.sql and sample_db.sql). 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 and manufactured must appear in this table.
2. busEntities(entity, shipLoc, address, phone, web, contact): every business entity has shipLoc (to beused 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 discountswill be expressed as fractions rather than percentage, e.g., 0.15 rather than 15 (%).
5. supplyUnitPricing(supplier, item, ppu): itemsupplied by sup has ppu (price per unit)
6. manufDiscounts(manuf, amt1, disc1): manufacturermanuf 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 prodItemby manuf, the manufacturer base cost is computedas 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 fromfromLoc to toLoc is computed as follows:
a. determine the total weight of all itemsshipped from fromLoc to toLoc (by all senders at fromLoc to all recipients attoLoc)
b. base cost: is computed based on totalweight 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 discountedprice
9. customerDemand(customer, item, qty): The demand by customer is qty units of item;
10. supplyOrders(item, supplier, qty): qty units of itemwere ordered from supplier
11. manufOrders(item, manuf, qty): qty units of itemwere 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 entitiessuch as suppliers, manufacturers and customers.


B. Implement the following SQL views by populating the template "queries.sql". This
SQL file must have the view definitions only and nothing else. For Part 2 of the project (=HA3) submit "queries.sql" ONLY to Blackboard. Make sure that "queries.sql" compiles/runs w/out errors.

2. totalManufItems: For every (product) item in manufOrders,compute the total qty of this item ordered from all manufacturers. The resulting schema should be (item, totalManufQty). Order the result by item.
3. matsUsedVsShipped: For every manuf in manufOrders, andmatItem used by this manuf (i.e., manuf is ordered a prodItem that requires a matItem according to billOfMaterials) compute:
- the total qty of this matItem required to produce all(product) items ordered from this manuf,
- the total qty of this matItem shipped by all shippers tothis manufacturer The resulting schema should be (manuf, matItem, requiredQty, shippedQty). Order the resultby manuf, matItem.
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 ordered from this manufacturer (in manufOrders). The resulting schema should be (item, manuf,shippedOutQty, orderedQty). Order the result by item, manuf.
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. The resulting schema should be (item,supplier, suppliedQty, shippedQty). Order the result byitem, supplier.
6. perSupplierCost: For each supplier in supplierDiscounts,compute the total cost of items supplied by this supplier (according to supplyOrders).
Clarification: to compute the perSupplierCost: (1) express the cost before discount (summation of ppu*qty over all itemsordered from that supplier); (2) applying discount. For example, assume that CostBeforeDiscount for supplier 17is $2500; and amt1=1000, disc1=0.1, amt2 = 2000, disc2
=0.2. The cost after discount will be (1000 + (2000- 1000)*(1-0.1) + (2500-2000)*(1-0.2) = 2300.
The resulting schema should be (supplier, cost). Order theresult by supplier.
7. perManufCost: For each manufacturer in manufDiscounts, compute the total manufacturing cost of all items produced bythis manufacturer (according to manufOrders).
The resulting schema should be (manuf, cost). Order theresult by manuf.
8. perShipperCost: For each shipper in shippingPricing, compute the total shipping cost of this shipper. The resultingschema should be (shipper, cost). Order the result by shipper.
9. totalCostBreakdown: Compute the total supply cost, manufacturing cost, and shipping cost. The resulting schemashould be (supplyCost, manufCost, shippingCost, totalCost).
10. customersWithUnsatisfiedDemand: Find customers, whose demand is NOT
satisfied, i.e., are not shipped allthe quantities of items. The resulting schema should be (customer). Order the result by customer.
11. suppliersWithUnsentOrders: Find suppliers, whose ordersare not fully shipped out. The resulting schema should be (supplier). Order the result by supplier.
12. manufsWoutEnoughMats: Find manufacturers who do NOT have enough materials to produce ordered product quantities, i.e., not enough materials were shipped to them. The resulting schema should be (manuf). Order the result bymanuf.
13. manufsWithUnsentOrders: Find manufacturers whose orders are not fully shipped out. The resulting schema shouldbe (manuf). Order the result by manuf.

C. How to test and debug your views (and know your scorewhen you're done)?
1. As described in "install_instructions.txt", install:
a. python3
b. cx_oracle Python module
c. cx_oracle client
d. recommended: ATOM studio (alternatively you can use any other IDE whichhas syntax binding for SQL and JSON.
2. Download and unpack the archive ha3_template.zip It has a number of files. If you use ATOM studio (which I recommend), under "File", choose "Add Project Folder" and select ha3_template. If you don't use ATOM studio, you can use any IDE that has syntax binding for SQL and JSON (Java Script Object Notation). Fill in the file "credentials.py" with your Oracle DBMS credentials.
3. Consider the input database data captured in the JSON file "testDBs/sampleDB.json". The mapping of the input to relational tables is self-explanatory. To test/debug your SQL script in "queries.sql" on an input DB associated with "sampleDB.json," do the following (from command line):
a. Duplicate file “queries_template.sql” to create "queries.sql" in the directory solution_sql.
b. In command line, make solution_sql your current directory.
c. Run in command line: >> python3 test_queries_main.py (note some systems use "python" or “py” etc. rather than "python3".)
d. "out.json" will contain the answers to all your views represented in JSON. The Python script "test_queries_main.py" generates tables, populate tables with data from "sampleDB.json". Then it runs your script "queries.sql" and extract output into "out.json" file.
e. You can compare your result "out.json" with the correct data results in the file "sampleDBanswers.json", which is provided in the HA4 folder.
f. You can play and modify "sampleDB.json", and see changes in the result.
a. In command line, make solution_sql your current directory.
b. Run in command line: >> python3 produce_answers_main_sql.py
c. This generated the file "answers.json" in the current directory, which containsthe
result of your views for test databases "db1.json", "db2.json", ... in the directory
./testDBs
d. Run in command line:
>> python3 report_unordered.py


Part 3: Bonus Option (do not attempt before all non-bonus parts are implemented):