REA Homework #2

Pegasus Paper Products

Pegasus Paper Products Inc.

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-workers 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 Annies 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 companys website. Customers may order products electronically from the website and customer information for all sale orders placed by via Pegasuss 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; because We Deliver is a union shop, PPP also has delivery agreements with two other shippers in case We Delivers employees decide to strike.

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. At the end of each day, Pegasus Paper Products determines which cash account has the lowest balance and make's that day's deposit into that account.

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	30. Quantity of a product ordered from the Vendor
2. Shipping address for a customer	31. Manufacturers suggested retail price
3. Stock number for a product	32. Date products were received from vendor
4. Vendor ID	33. Employee ID
5. Location of cash account	34. Date products were ordered from vendor
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	58.Shipper ID

Table Name	Primary Key	Attributes	Foreign Keys
VENDOR
PURCHASE ORDER
PURCHASE
CASH DISBURSEMENT
EMPLOYEE
INVENTORY
SALES RETURN
CASH
EMPLOYEE TYPE
SALES ORDER
SHIPPER
CASH RECEIPT
SALES
CUSTOMER

REA Homework #2 P EGASUS P APER P RODUCTS Pegasus Paper Products Inc. 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; because We Deliver is a union shop, PPP also has delivery agreements with two other shippers in case We Deliver's employees decide to strike. 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. At the end of each day, Pegasus Paper Products determines which cash account has the lowest balance and make's that day's deposit into that account. 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 7. Purchase Order number 8. Date of cash receipt 9. Customer ID 10. Product quantity on hand 11. Total dollar amount of a sale 12. Employee name 13. Description of a product 14. Cash receipt ID 15. Receiving report number 16. Type of cash account 17. Date of cash disbursement 18. Invoice number 19. Customer name 20. Quoted unit selling price for a product on a sales order 21. Standard product cost 22. Vendor telephone number 23. Employee address 24. Cash account number 25. Cash receipt amount applied to each sale 26. Job category # 27. Quantity of a product returned by a customer 28. Shipper name 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 35. Date products were ordered by a customer 36. Quoted unit cost of a product ordered from a vendor 37. Quantity of a product received from a vendor 38. Customer telephone number 39. Quantity of a product ordered by a customer 40. Total dollar amount of a cash disbursement 41. Actual quantity of a product sold in a sale 42. Accounts payable due vendor 43 Total dollar amount of a cash receipt 44 Vendor address 45 Total purchase order dollar amount 46 Cash disbursement voucher ID 47 Vendor email address 48 Actual unit cost of a product received from a vendor 49 Sales order number 50 Sales order total dollar amount 51 Total dollar amount of a purchase 52 Actual unit selling price of a product in a sale 53 Customer Billing Address 54 Job category description 55 Return authorization # 56 Date items returned by customer 57.Shipper phone number 58.Shipper ID Table Name VENDOR PURCHASE ORDER PURCHASE CASH DISBURSEMENT EMPLOYEE INVENTORY SALES RETURN CASH EMPLOYEE TYPE Primary Key Attributes Foreign Keys Table Name SALES ORDER SHIPPER CASH RECEIPT SALES CUSTOMER Primary Key Attributes Foreign Keys Table Name Primary Key Attributes Foreign Keys