Problems on The REA Approach to Database Modeling.

Problems

1.REA DIAGRAMS

Describe three differences between REA diagrams and ER diagrams.

2. REA ASSOCIATIONS

Bentley Restorations Company restores and sells top- end classic and antique automobiles. Most of its cus- tomers are private collectors, but some are investors who buy multiple cars and hold them for resale. Bentley extends no credit terms. All sales are for cash.

Required

Which of the relationships in the diagram for Problem 2 properly models the entity associations for Bentley Restorations? Explain your answer.

3. REA ASSOCIATIONS

Based on the data in Problem 2, which of the following relationships in the diagram for Problem 3 properly models the entity associations for Bentley Restorations? Explain your answer.

4. REA ASSOCIATIONS

Based on the data in Problem 2, which of the relation- ships in the diagram for Problem 4 properly models the entity associations for this situation? Explain your answer.

5. REA MODEL EXTRACT

Prepare an REA model depicting the issuance of raw materials into the manufacturing process. Identify the

resource, event, and agent entities. Show the cardinalities in the associations between entities.

6. REA MODEL—HORIZON BOOKS

Horizon Books is a bookstore in downtown Philadelphia. It carries an inventory of approximately 5,000 books. Customers come in and browse the shelves, select their books, and take them to the cashier. At the cashier’s desk, they can also discover whether a particular book is in stock, place orders for books not currently available in the bookstore, and collect and pay for books previously ordered. There are no credit sales.

  All customers pay for their purchases at the time of purchase.

Required

Prepare an REA model for Horizon’s database. Show all cardinalities in the associations.

7. REA MODEL—FEINMAN COMPUTERS

Feinman Computers sells desktop computer systems that it manufactures from parts and software that third- party vendors provide. Customers are both private consumers and small businesses. Consumers pay cash or by credit card, but business customers buy on credit. A credit check is made of all new business customers before approving a line of credit. Sales are made online or by a hard-copy order document that customers mail or fax to the company.

When a credit order is received, the sales clerk verifies inventory availability, prepares a sales order and sends the stock release copy to Willy, a warehouse employee who picks the goods and arranges shipment. Willy then prepares the bills of lading and shipping notices. Barb in the billing department receives the shipping notice from Willy and updates the inventory subsidiary ledger to account for the reduction in inventory. Barb files the stock release, prepares the invoice, and mails a copy of it to the customer. Barb then updates the sales journal and then sends the invoice, sales order, stock release, and shipping notice to the AR department.

Adam in the AR department files the documents that Barb sent him and updates the AR subsidiary ledger. Mickey in the mail room receives remittance advices and customer checks sent in payment of accounts. He sends the remittance advice to Adam for posting to the AR ledger and sends the checks to Carol, the cash receipts clerk. At the end of the day, she prepares a de- posit slip and deposits the checks into the company’s bank account, files the bank receipt, and updates the cash receipts journal.

Cash sales to consumer customers are handled in a manner similar to the process described here except that checks or credit card account numbers are submitted with the original order.

At the end of each week, John, an accounting clerk,

reconciles all transactions and posts them to the general ledger.

Required

a. Prepare the REA model of the sales/collection process.

b. Show the cardinalities for all associations.

c. List the tables, keys, and attributes needed to implement this model in a relational database.

8. REA MODEL—K CANNON, INC.

K Cannon, Inc., is a manufacturer of portable CD players. The company purchases raw materials such as plastics and computer chips for its conversion cycle. The following describes K Cannon’s purchases and payments procedures.

John, the purchasing department clerk, monitors raw materials inventory levels and prepares a purchase requisition when purchases are necessary. These are sent to the purchasing agent, who prepares six copies of a purchase order. Two purchase orders are sent directly to the vendor. One is placed in an open purchase order file in the purchasing department, and one is used to post to the purchases journal. Each week the purchasing department clerk prepares a journal voucher from the purchases journal and sends it to the general ledger department for posting. The AP and the receiving departments also each receive a copy of the purchase order, which they file temporarily.

Upon receiving the raw materials, the receiving department clerk creates five copies of the receiving report. One copy is sent to the raw materials warehouse and one copy is sent to the AP department. Two copies are sent to the purchasing department, where one is filed and one is used to update the inventory records. The final copy is filed in the receiving department with the purchase order and packing slip. Vendors send their invoices to the AP department, where they are used to update the AP subsidiary ledger.

In the cash disbursements department, Larry receives the information from the AP department, such as the purchase requisition, purchase order, receiving report, and invoice. He then prepares and signs the checks for the suppliers. After preparation of the checks, these sup- porting documents are sent back to the AP department. Each week, Larry prepares a journal voucher and sends it to the general ledger department for posting.

Required

a. Prepare the REA model of the purchase cash payments process.

b. Show the cardinalities for all associations.

c. List the tables, keys, and attributes needed to imple- ment this model in a relational database.

9. REA MODEL—PAYROLL SYSTEM

Every 2 weeks, employees for a manufacturing firm submit their time cards to the supervisor, who reviews and approves them. They are the submitted to the pay- roll department. At that time, the human resources clerk also submits a personnel action to the payroll clerk. The payroll clerk enters the information from these source documents into the employee records and then adds the employee hours to a payroll register reflecting employee pay rates, deductions, and job classification. One copy of the payroll register, along with the time cards, is filed in the payroll department, and one copy is sent to the AP department.

Next the payroll clerk sends the employee paychecks to the cash disbursements department. The checks are signed and distributed to the employees by the paymaster. The AP department prepares a cash disbursements voucher. A copy of the voucher and a copy of the payroll register are sent to the cash disbursements department and are then posted to the general ledger. The cash dis- bursements clerk writes a check for the entire payroll and deposits it in the payroll imprest cash account. A copy of the check, the disbursement voucher, and the payroll register are sent back to the AP department, where they are filed.

Required

a. Prepare the REA model of the payroll process.

b. Show the cardinalities for all associations.

c. List the tables, keys, and attributes needed to implement this model in a relational database.

10. REA MODEL—FIXED ASSET SYSTEM

Asset acquisition begins when the department manager recognizes the need to obtain or replace an existing fixed asset. The manager prepares two copies of a purchase requisition; one is filed in the department and one is sent to the purchasing department. The purchasing department uses the purchase requisition to prepare three copies of a purchase order. One copy of the purchase order is sent to the supplier, another copy is sent to the AP department, and the third copy is filed in the purchasing department. The receiving department receives the assets and packing slip from the vendor and prepares a receiving report. One copy of the receiving report is sent to AP, one is sent to the department man- ager, and one is sent to the inventory department clerk and used to update the inventory records.

The AP clerk receives the invoice, which she compares

to the purchase order and receiving report. The AP clerk inputs the information into the computer terminal, posts the liability, updates the purchase journal, and prints out hard copies of a journal voucher and cash disbursement voucher. The journal voucher is sent to the general ledger department, and the cash disbursements voucher and the supplier’s invoice are sent to the cash disbursements department. The purchase order and the receiving report are filed in AP. The cash disbursements clerk prepares and posts a check to the check register using the information from the supplier’s invoice and the cash disbursements voucher, and prints a hard copy of the check, which is sent to the vendor. The cash disbursements voucher is sent on to the general ledger department.

The department manager also handles asset maintenance and asset disposal. The manager adjusts the fixed asset inventory subsidiary account balances as the assets depreciate over time. When an asset has reached the end of its useful life, a disposal report is prepared. The department manager sends an asset status summary to the general ledger. The general ledger department clerk reconciles the cash disbursements voucher, the journal voucher, and the asset status summary and posts to the general ledger accounts.

Required

a. Prepare the REA model of the fixed asset procedures.

b. Show the cardinalities for all associations.

c. List the tables, keys, and attributes needed to imple- ment this model in a relational database.