978-0134474021 Chapter 18 Solutions Manual Part 2

18.9 Refer to Figure 18.4 and Table 18-1 to write the query logic needed to answer the

following questions. (Optional: If requested by your instructor, write your queries in

SQL or a Query-By-Example graphical interface.) Some answers may require more

than one query—try to write the most efficient queries possible.

a. Accounts payable for all suppliers in Arizona

Step 1: Normally, only orders actually received are considered purchases for purposes of

Step 2: Query the Order Inventory – Inventory table to determine the total amount

Step 3: Retrieve the total beginning balance of Accounts Payable by querying the

Step 4: Query the Disburse Cash and Supplier tables to calculate the total amount paid to

Step 5: Total Accounts Payable to Arizona Suppliers = Answer to Query 2 + Answer to

Query 3 – Answer to Query 4.

b. Total amount of sales to a customer named Smith

Step 1: Identify all sales to Smith by writing a query that joins the Sales and Customer

Step 2: Sum the product of quantity sold times unit price in the M:N Sales-Inventory

18-1

Accounting Information Systems

c. Total wage expense

Pay rates are likely to differ across employees. Therefore, calculate total wage expense by

summing the wage expense for each employ, as follows:

Step 1: Query the Time Worked table to sum the total hours worked, grouped by

Step 2: Query the table produced in step 1 and the Employee table to calculate the total

d. Total wages payable

Total wages payable equals wages earned but not yet paid. Thus, use the same procedure

e. Net increase (decrease) in quantity-on-hand for a particular inventory item

Step 1: Write a query to sum the quantity received in the M:N relationship table linking

f. The proportion of sales made to walk-in customers (i.e., no order)

Step 1: Write a query to calculate total sales by summing the product of quantity sold

g. The salesperson who made the largest amount of sales in October

18-2

Accounting Information Systems

Step 1: Write a query to identify all rows in the Sales table that occurred in October.

Step 2: Write a query to calculate total sales by summing the product of quantity sold

.

h. The salesperson who made the most sales in October

Step 1: Write a query to identify all rows in the Sales table that occurred in October.

i. The most popular item, in terms of total units sold

Step 1: Write a query against the M:N Sales-Inventory relationship table that sums the

18-3

Accounting Information Systems

18.10 Refer to Problem 17.10 and develop a set of tables to implement the REA diagram

you developed for Stan’s Southern Barbeque Supply Store. Identify the primary and

foreign keys for each table, and don’t forget to address any M:N relationships.

Table Primary key Foreign Keys Other attributes

Order Inventory Purchase

Order#

Employee#,

Vendor#,

Receiving Report#

Date

ory

Receive

Inventory-Cash

Disbursement

Receiving

Report#,

Check#

Amount applied

18-4

Accounting Information Systems

18.11 Answer the following multiple-choice problems.

1. Which of the following types of relationships in an REA diagram must be

implemented as a separate table in a relational database?

a. 1:1

b. 1:N

c. N:1

d. M:N

2. When implementing a revenue cycle REA diagram in a relational database, the

relationship between customers and sales would be implemented by ____.

a. placing the customer number attribute as a foreign key in the sales table

b. placing the sales invoice number as a foreign key in the customer table

c. either approach is equally acceptable

3. When merging REA diagrams from two different cycles, it is not necessary to

change the minimum cardinality for _____ that appear in both of the separate REA

diagrams.

a. Resources

b. Agents

c. Events

18-5

Accounting Information Systems

4. ABC Company sells original manuscripts of Broadway plays. ABC pays for all its

purchases in full with one check. It allows customers to make installment payments

on sales that exceed $33,000. However, a customer cannot make another purchase

until all prior purchases have been paid for. The REA diagram for ABC company

depicts seven Employee entities, each labeled for the role played by that type of

employee (e.g., cashier, salesperson, shipping clerk, accountant, etc.). The REA

diagram also contains two resource entities (Inventory and Cash) and the following

events: Purchases, Disburse Cash, Sales, and Receive Cash. The REA diagram also

contains two instances of the Customer entity and three instances of the Supplier

entity. Implementing the integrated REA diagram for the revenue and expenditure

cycles of the ABC company in a relational database requires how many tables?

a. 5

b. 7

c. 9

d. 15

e. 18

5. The XYZ Company sells sports equipment. The actual sales price of a given item

varies throughout the year due to sales events. The actual sales price should be

stored as an attribute in the _________ table.

a. Sales

b. Inventory

c. Sales-Inventory

6. In an integrated REA diagram for both the revenue and expenditure cycles, the

quantity on hand during the middle of the year for an inventory item would be

stored as an attribute in the ____ table.

a. Inventory.

b. Sales-Inventory

c. Purchases-Inventory

d. None of the three tables

18-6

Accounting Information Systems

SUGGESTED ANSWERS TO THE CASES

18.1 The specific solution will vary depending upon the DBMS and REA data model used.

Therefore, we present only the query logic here for the model depicted in Figure 18.4.

1. Calculate total Accounts Receivable.

2. Calculate Accounts Receivable for a specific customer.

This requires a similar set of queries as used to calculate total Accounts Receivable:

a. Calculate beginning Accounts Receivable by summing the beginning balance

b. Select only those rows in the Sales table that represent sales to the customer of

c. Calculate total new Sales this fiscal period by summing the product of quantity

d. Calculate total cash received from customers by summing amount received

e. Total Accounts Receivable = Query A + Query C – Query D.

18-7

Accounting Information Systems

3. Create a sales invoice form that references the appropriate tables and inputs

data about attributes into the proper tables.

The syntax for doing this will vary depending upon the DBMS used. Solutions should

be tested to ensure that the form adds new rows to the following tables:

4. Calculate as many financial statement items as possible from the data model you

implement.

Income Statement items derivable from Figure 18.4:

Only Sales and Wage Expense can be definitively calculated; cost of goods sold

Sales

Wage expense

18-8

Accounting Information Systems

Balance Sheet items derivable from Figure 18.4:

Cash

Step 1: Calculate the beginning balance of cash by summing the amount

column in the Cash table.

Accounts Receivable

a. Calculate beginning Accounts Receivable by summing the beginning

18-9

Accounting Information Systems

Accounts Payable

Step 1: Normally, only orders actually received are considered purchases for

Step 2: Query the Order Inventory – Inventory table to determine the total

Step 3: Retrieve the total beginning balance of Accounts Payable by querying

Step 4: Query the Disburse Cash table to calculate the total amount paid to

Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 –

Wages Payable

Step 1: Query the Time Worked table to sum the total hours worked, grouped

Step 2: Query the table produced in step 1 and the Employee table to calculate

Step 3: Sum the total amounts column in the table produced in step 2.

18-10

Accounting Information Systems

5. Design appropriate input controls for the sales invoice form created in step 3.

The syntax required will vary depending upon the DBMS used. Students should be

encouraged to review material from chapter 10 to identify appropriate input controls.

Solutions should include the following:

1. Auto-number the sales invoice to prevent creating duplicate or null primary keys.

6. Validity check on sales date (check against the current system date).

18-11