CSCI-300 Database Management Design Project: Contract-Supplies System Turner Construction company was founded in New York in 1902 and its first project was a reinforced concrete cooper shop for the manufacturer J. B King & Company. The company negotiates contracts with several suppliers for the supply of various amounts of selected item kinds at a price that forms part of the contract. Orders are placed against any of the negotiated contracts for the supply of items at the price quoted in the contract. The price of an item quoted in a contract is called CONTRACT-PRICE. An order can consist of any amount of those items that are in that contract. The quantity of an item in an order is called ORDER-QTY. Any number of orders can be made against a contract. However, the sum of any given item kind in all orders against one contract cannot exceed the amount of that item kind quoted in that contract. This is called CONTRACT-AMOUNT in the database. An inquiry would be made to establish if a sufficient quantity of an item is available before an order for that item is placed. All the items in an order must be supplied as part of the same contract. Each order is placed only against one contract and is made on behalf of one project. An order is made for one or more item kinds in that contract. DATE-REQUIRED specifies the date of an order which is placed and DATE-COMPLETED specifies the date of an order which is filled The access requirements for this system are given as follows: 1. 2. 3. 4. Enter a new SUPPLIER-NO with SUPPLIER-ADDRESS and SUPPLIER-NAME. (infrequent) Enter a new ITEM-NO with ITEM-DESCRIPTION. (infrequent) Enter a new PROJECT-NO with PROJECT-DATA. (infrequent) Enter a new CONTRACT-NO with DATE-OF-CONTRACT together with the ITEM-NO, CONTRACT-PRICE, and CONTRACT-AMOUNT for all items in the contract. (infrequent) Enter a new order (100/day) 5. Enter ORDER-NO, DATE-REQUIRED, PROJECT-NO, CONTRACT-NO For each ordered item Begin Enter ITEM-NO, ORDER-NO, ORDER-QTY End 6. 7. Find the items in an order (30/day) Find the price of an item in an order. The price of the item is the price negotiated for the order's contract (5/day) Find the orders in which a particular item appears (20/day) 8. 9. Find the price for a given item in a contract (150/day) 10. Find a particular contract together with its supplier (20/day) Find the quantity of a given item still available under a given contract (100/day) Detailed pseudocode of the access requirement is: the CONTRACT-AMOUNT for the ITEM-NO in the CONTRACT-NO Amount-available CONTRACT-AMOUNT For all orders in the contract Begin If the order contains the given ITEM-NO Then amount-available amount-available ORDER-QTY End Output amount-available Summarize the purchases by CONTRACT-NO of all items ordered in ITEM-NO order Produce the summary with ITEM-NO listed within CONTRACT-NO (weekly) For each CONTRACT-NO 1. For each ORDER-NO in CONTRACT For each ITEM-NO in ORDER Create temporary record Sort in sequence Sum all ORDER-QTY for same CONTRACT-NO and ITEM-NO output the summary The volume data are given as follows: 1. Number of contract 2. Average no. of items/contract 100 3. Max no. of items/contract 4. Average no. of orders/contract 1200 5. Max no. of orders/contract 6. Average no. of contracts/supplier 3 7. Max no. contracts/supplier 8. Average no. of items/orders 9. Max no. of items/order 10. Average no. of orders/month 11. Number of items 12. Number of projects/month 50 500 6000 10 10 100 5000 2000 50 The data item sizes are given as follows: 1. SUPPLIER-NO 2. SUPPLIER-ADDRESS 30 20 3. SUPPLIER-NAME 4. CONTRACT-NO 5. DATE-OF-CONTRACT 6. ITEM-NO 7. ITEM-DESCRIPTION 8. CONTRACT-PRICE 9. CONTRACT-AMOUNT 10. ORDER-NO 11. DATE-REQUIRED 12. DATE-COMPLETED 13. ORDER-QTY 14. PROJECT-DATA 20 20 15. PROJECT-NO