18.7 Develop a set of tables to implement the integrated REA diagram you developed in
Problem 17.6 for the Computer Warehouse in a relational database. Specify a
primary key for each table, and suggest at least one other attribute that should be
included in each table.
Table
Primary key
Other attributes (foreign keys in italics)
Vendor
Vendor#
Name, address, phone, contact
Order Inventory
Purchase Order#
Date, vendor#, employee#
reorder quantity, reorder point
Cash
GLaccount#
Account name, beginning balance
Sales
Invoice#
Date, employee#, customer#, remittance#
Customers
Customer#
Name, address, beginning balance
Inventory
Item#
Sales-Inventory
Invoice#, Item#
Quantity, unit price
Inventory
Item#
18.8 Explain how to calculate the total amount of Accounts Payable:
Total Accounts Payable can be calculated in five steps, as follows:
Step 1: Normally, only orders actually received are considered purchases for purposes of
calculating accounts payable. Therefore, begin with a query of the Receive
Query 4.
18-14
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 3: Retrieve the total beginning balance of Accounts Payable by querying the
Suppliers table and summing the beginning balance column, restricting the sum
operation to only those suppliers located in Arizona.
Step 4: Query the Disburse Cash and Supplier tables to calculate the total amount paid to
suppliers located in Arizona this fiscal period by summing the amount column for
Accounting Information Systems
18-15
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:
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
the Receive Inventory event and the Inventory table for only those rows with a
particular value in the item# column.
Step 2: Write a query to sum the quantity sold in the M:N Sales-Inventory relationship
Ch. 18: Implementing an REA Model in a Relational Database
18-16
g. The salesperson who made the largest amount of sales in October
Step 1: Write a query to calculate total sales by summing the product of quantity sold
times unit price in the Sales-Inventory M:N relationship table.
Accounting Information Systems
18-17
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
Disburse Cash
Check#
Vendor#,
Date, amount, description
Inventory
Item#
Description, beginning quantity on
Vendor
Vendor#
Name, address, contact, beginning
Receive
Receiving
Quantity, condition
Receive
Receiving
Amount applied
Purchase
Employee#,
Date
18-18
1. Calculate total Accounts Receivable.
a. Calculate beginning Accounts Receivable by summing the beginning balance
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
attribute in the Customer table for the customer of interest.
Accounting Information Systems
18-19
3. Create a sales invoice form that references the appropriate tables and inputs
data about attributes into the proper 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
Ch. 18: Implementing an REA Model in a Relational Database
18-20
Balance Sheet items derivable from Figure 18.4:
Only Cash, Accounts Receivable, Accounts Payable, and Wages Payable can be
definitively calculated. Inventory valuation requires an assumption about inventory
Accounting Information Systems
• Accounts Payable
Step 1: Normally, only orders actually received are considered purchases for
purposes of calculating accounts payable. Therefore, begin with a
18-22
5. Design appropriate input controls for the sales invoice form created in step 3.
1. Auto-number the sales invoice to prevent creating duplicate or null primary keys.
3. Sign check on quantity sold and price fields.
4. Completeness checks on customer information (billing and shipping address) –
5. Completeness checks on inventory information (description, list price, etc.) – this