CASE 18-1 Practical Database Design

CASE 18-1 Practical Database Design

Hands-on practice in database design is important. Use a relational DBMS to implement the integrated REA data model presented in this chapter, or one of the integrated data models from the homework problems, or a model provided by your instructor. Then, perform the following tasks:

· 1. Write a query to calculate total accounts receivable.

· 2. Write a query to calculate accounts receivable for a specific customer.

· 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables.

· 4. Write queries to calculate as many financial statement items as possible from the data model you implement.

· 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3.

18.1.

Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe’s ice-cream shop in order to draw an integrated REA diagram of both cycles.

PROBLEMS

17.1.

Joe’s is a small ice-cream shop located near the local university’s baseball field. Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account.

REQUIRED

Draw an REA diagram, complete with cardinalities, for Joe’s revenue cycle.

17.2.

Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in ice-cream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company’s main checking account.

REQUIRED

Draw an REA diagram, complete with cardinalities, for Joe’s expenditure cycle.

PROBLEMS

17.1.

Joe’s is a small ice-cream shop located near the local university’s baseball field. Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account.

REQUIRED

Draw an REA diagram, complete with cardinalities, for Joe’s revenue cycle.

17.2.

Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in ice-cream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company’s main checking account.

REQUIRED

Draw an REA diagram, complete with cardinalities, for Joe’s expenditure cycle.