Your co-worker, Athletic Annie, was working on the database design project for Pegasus Paper Products (PPP) when she suffered a slight concussion while playing racquetball.A survey of all ACG personnel revealed that you are the only one on your team not currently assigned to a project and thus your supervisor has instructed you to complete the project and provided you with your co-worker's notes, a list of the attributes PPP wishes to track, as well as a partially completed REA diagram.You are to:

Complete the REA diagram and

Convert your business process level model into a set of minimal relational database tables using the following format:

Table Primary Key Attributes Foreign Key

Athletic Annie's Notes

The products carried by Pegasus Paper Products are mass-produced, and therefore each instance of a product is not specifically identified (e.g. with a unique serial number) but instead is tracked with a product stock number that is the same for all instances of that product.

Sales to some customers are made in person, at the store's South Jersey location.For those sales, Pegasus Paper Products has a policy of accepting cash only.Its "in-store" customers tend to be small volume customers and the marketing department has decided it has no need to track which specific in-store customers are purchasing which products.

However, Pegasus Paper Products also has customers nationwide who use the company's website.Customers may order products electronically from the website and customer information for all sale orders placed by via Pegasus's internet website must be tracked especially the customer's billing and shipping addresses.

A recent sales analysis revealed that in-store sales transactions were approximately 15% of the revenue and 10% of the total sales transactions; internet sales comprised approximately 85% of the revenue and 90% of the sales transactions.

Customers have the option of having Pegasus Paper Products ship the products as they become available or waiting until the order is complete (the former requires them to pay extra shipping costs).

Multiple customer orders are never combined into the same shipment and for internal control purposes, each shipment will be assigned to exactly one shipper.

Pegasus Paper Products uses We Deliver for all deliveries however, PPP has delivery agreements with two other shippers.

Payment terms offered by Pegasus Paper Products are n/15 and checks are the only acceptable form of payment (debit and credit card payments are not accepted). Sometimes customers will pay for multiple sales with a single check, and Pegasus Paper Products needs the information system to be able to accept partial payments even though those are very rare (less than 5%).

The internal controls are very tight, so of course, every return is related to exactly one employee for accountability purposes.

Pegasus Products does not allow any sales returns on in-store sales; that is, all in-store sales are final.However, sometimes the incorrect products are delivered to Pegasus Paper Products' internet customers and these customers have 30 days to process a return.

Only about 10% of products sold are returned; any return could consist of multiple products however it would only be related to one sales shipment, although occasionally (0.1% of the time) Pegasus Products' receives multiple returns for the same shipment.When that happens, the employee involved with that shipment has to provide a written explanation for the multiple discrepancies.

On-site sales and cash receipts are processed by cashiers, who enter product information into an electronic cash register, put the merchandise into bags, and accept the customer's payment (providing change to the customer if necessary).

Store clerks, access the internet-order information and fill all off-site orders in-between helping on-location customers, by packing the merchandise and the related invoice into boxes, labeling the boxes with the customer shipping address, mailing the boxes, and recording the shipment information.Of course for internal control purposes each shipment is processed by exactly one store clerk.

When cash receipts are received from off-site customers, they are also processed by one of the cashiers. They record the cash receipt and put the checks into the cash register drawer, which serves as an "on-hand cash account".

Pegasus Paper Products also has cash accounts at a local bank, where the cash register drawer contents as well as checks received from occasional non-sale sources (such as loans) are deposited.Each account has a different purpose (e.g. revenues, payroll, supplier disbursements, etc.)

Pegasus Paper Products also has a group of purchasing agents who order products from various vendors,of course each purchase order is related to exactly one agent.

All purchases must be preceded by an order; purchase agents are never allowed to just go out and buy something without going through the formal ordering process.

Because the manager approves ALL purchase orders, her participation is not tracked in the database.Exactly one purchase agent is responsible for any one order and orders are accepted by the vendor in their entirety or not at all.

Vendors accept nearly all (99%) of Pegasus Paper Products' orders, and only completed orders are shipped. Multiple orders accepted by the same vendor are sometimes combined into a single shipment to Pegasus Paper Products.

One of Pegasus Paper Products' inventory clerks receives the products, unpacks and counts them, enters the appropriate information into the information system, and places them on the store display shelves.

Pegasus Paper Products pays for all purchases made from a vendor during a given month on the last day of that month with one check, and intends never to make partial payments for purchases of inventory.

Each and every check (including checks written for other things such as loan payments, wages, etc.) is written by one of Pegasus Paper Products' accounts payable clerks and is drawn on one of its cash accounts.Approximately 90% of all checks written are for purchases.

Pegasus Paper Products wants a well-designed database and plans to enter information about its employees, cash accounts, products, customers, and vendors before entering information about any events for which those things need to be tracked.

Regarding employees, all employee information is categorized by job category type (e.g. management, salaried non-management, hourly etc.)

Purchases and purchase orders of different types of things (for example, fixed assets or services) will NOT be included in the same entities with purchases and purchase orders of product inventory; rather they would be considered as separate entities (and you are not modeling those).

Pegasus Paper Products does not sell anything other than product inventory. Below, Pegasus Paper Products has identified attributes that need to be included in its information system (don't add or subtract any attributes).

For simplification purposes the zip code table is not included in this exercise.

1. Date of sale

2. Shipping address for a customer

3. Stock number for a product

4. Vendor ID

5. Location of cash account

6. Vendor name

35. Date products were ordered by a customer

7. Purchase Order number

36. Quoted unit cost of a product ordered from a vendor

8. Date of cash receipt

37. Quantity of a product received from a vendor

9. Customer ID

38. Customer telephone number

10. Product quantity on hand

39. Quantity of a product ordered by a customer

11. Total dollar amount of a sale

40. Total dollar amount of a cash disbursement

12. Employee name

41. Actual quantity of a product sold in a sale

13. Description of a product

42. Accounts payable due vendor

14. Cash receipt ID

43 Total dollar amount of a cash receipt

15. Receiving report number

44 Vendor address

16. Type of cash account

45 Total purchase order dollar amount

17. Date of cash disbursement

46 Cash disbursement voucher ID

18. Invoice number

47 Vendor email address

19. Customer name

48 Actual unit cost of a product received from a vendor

20. Quoted unit selling price for a product on a sales order

49 Sales order number

21. Standard product cost

50 Sales order total dollar amount

22. Vendor telephone number

51 Total dollar amount of a purchase

23. Employee address

52 Actual unit selling price of a product in a sale

24. Cash account number

53 Customer Billing Address

25. Cash receipt amount applied to each sale

54 Job category description

26. Job category #

55 Return authorization #

27. Quantity of a product returned by a customer

56 Date items returned by customer

28. Shipper name

57.Shipper phone number

29. Shipper address

30. Quantity of a product ordered from the Vendor

31. Manufacturers' suggested retail price

32. Date products were received from vendor

33. Employee ID

34. Date products were ordered from vendor

58.Shipper ID