Premium Garden Products (PGP) supplies gardening enthusiasts and commercial garden centers throughout the northeast with a wide range of gardening equipment and products. Their wide range of inventory includes such items as tank sprayers, wheel barrows, fertilizers, plants, insecticides, etc. Sales to commercial customers (garden centers) are on credit whereas consumer sales are cash transactions (bank credit/debit cards). Sales orders are received via e-mail or phone at the Scranton PA home office. In the case of commercial customers sales the PGP sales clerk verifies customer credit worthiness by checking their available credit in the Customer table. The clerk then checks availability of the product from the Inventory table and creates a record in the Sales Invoice table to record the sale. The clerk then sends a stock release notice to the distribution center nearest to the customer’s location, from where the product is shipped. Shipments are made from two distribution centers: one in Scranton and one in Maryland. Commercial customers are given net 30 terms. They make payment by check, and the open Sales Invoice is then marked paid by placing the customers check number in the “payment” field of the invoice. Sales to consumer customers are processed similarly except no credit check is performed and the Sales Invoice record is marked paid at the time of sale by placing the last four digits of the customer’s credit card number in the payment field.
PGP obtains its inventories from over 100 nurseries and producers of garden products throughout the United States. When inventory levels fall to their predetermined reorder points the computer system automatically creates a record in the Purchase Order table and sends a hard copy PO to the supplier. When products arrive at the distribution centers the receiving clerks log onto the main office computer and create a record in the Receiving Report table. Suppliers send their invoices to the AP clerk who adds a record to the Supplier Invoice table. On the due date the cash disbursement clerk writes a check in payment of the AP, marks the supplier invoice paid by placing the check number in the “paid” field.

Required:
Design a partial relational database needed to support those aspects of PGP’s process that are outlined above. Label the database tables and assign meaningful attributes to each. Assign primary keys and foreign keys as needed. Assume that invoices, purchase orders and receiving reports may contain many items.