Question

ACCOUNTING INFORMATION SYSTEMS; DATABASE DESIGN

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, wheelbarrows, fertilizers, plants, and insecticides. 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, Pennsylvania, 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 the 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 per- formed 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 pre-determined 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 and 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 in the problem. 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.

Answer 1

Table

Order Inventory

Receive Inventory

Disburse Cash

Cash   Inventory Employee Vendor Order Inventory- Inventory Receive Inventory-Inventory Receive Inventory-Cash Disbursement Primary key Purchase Order# Receiving Report# Check# GLAccount# Item# Employee # Vendor # Purchase Order #, Item # Receiving Report #, Item # Receiving Report #, Check # Foreign Keys Employee#, Vendor#, Receiving Report# Vendor#, Employee# Vendor#, Employee#, GLAccount# Other attributes Date Date, vendor invoice # Date, amount, description Balance, beginning balance Description, beginning quantity on hand, reorder quantity, reorder point Name, hire date, position, payrate Name, address, contact, beginning account balance, performance rating Quantity, unit cost Quantity, condition Amount applied.