Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
