DEFINING ENTITIES AND DATA MODELING—PURCHASES PROCEDURES The business rules that const...

DEFINING ENTITIES AND DATA MODELING—PURCHASES PROCEDURES

The business rules that constitute the purchases system for the Safe Buy Grocery Stores chain are similar at all the store locations. The purchase manager at each location is responsible for selecting his or her local suppliers. If the manager needs a product, he or she chooses a supplier. Each store follows the steps described here.

1. The purchasing function begins with sales representatives from suppliers periodically observing the shelves and displays at each location and recognizing the need to restock inventory. Inventory declines by direct sales to the customers or by spoilage of perishable goods. In addition, the supplier’s sales representatives review obsolescence reports that the purchase manager prepares. These reports identify slow moving and dated products that are deemed unsalable at a particular location. These products are returned to the supplier and replaced with more successful products. The sales representatives prepare a hard-copy purchase requisition and meet with the purchase managers of the individual store locations. Together, the sales representative and the purchase manager create a purchase order defining the products, the quantity, and the delivery date.

2. At the intended delivery date, Safe Buy Grocery Stores receive the goods from the suppliers. Goods received are unloaded from the delivery trucks and stocked on the shelves and displays by part-time employees.

3. The unloading personnel create a receiving report. Each day, a receiving report summary is prepared and sent to the purchase managers for review.

4. The supplier subsequently submits an invoice to the AP department clerk, who creates an invoice record. The clerk reconciles the invoice against the receiving report and purchase order and then creates a payment obligation to be paid at a future date, depending on the terms of trade.

5. On the due date, a check is automatically prepared and sent to the supplier, and the payment is recorded in the check register. At the end of each day, a payment summary is sent to the purchase managers for review

Required

Assume that the manual system described is to be automated using a relational database system. Create a data model for the system including primary keys, foreign keys, and data attributes that will support the tasks and user views. You may need to make assumptions about how certain automated activities will be performed. Normalize the model.