Implementing an rea model in a relational database suggested answers to discussion questions



Download 125.61 Kb.
Page5/5
Date03.04.2021
Size125.61 Kb.
1   2   3   4   5

Primary key


Foreign Keys

Other attributes

Order Inventory

Purchase Order#

Employee#, Vendor#, Receiving Report#

Date

Receive Inventory

Receiving Report#

Vendor#, Employee#

Date, vendor invoice #

Disburse Cash

Check#

Vendor#, Employee#, GLAccount#

Date, amount, description

Cash

GLAccount#




Balance, beginning balance


Inventory

Item#




Description, beginning quantity on hand, reorder quantity, reorder point

Employee

Employee#




Name, hire date, position, payrate

Vendor

Vendor#




Name, address, contact, beginning account balance, performance rating

Order Inventory- Inventory

Purchase Order#, Item#




Quantity, unit cost

Receive Inventory-Inventory

Receiving Report#, Item#




Quantity, condition

Receive Inventory-Cash Disbursement

Receiving Report#, Check#




Amount applied

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.




  1. Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table.




  1. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales – Inventory M:N relationship table.




  1. Calculate total cash received from customers by summing amount received column in the Receive Cash table.




  1. Total Accounts Receivable = Query A + Query B – Query C



  1. Calculate Accounts Receivable for a specific customer.

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




  1. Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table for the customer of interest.




  1. Select only those rows in the Sales table that represent sales to the customer of interest (i.e., those rows in the Sales table which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest).




  1. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales – Inventory M:N relationship table for only those rows which have an invoice number in the set of invoice numbers from query b.




  1. Calculate total cash received from customers by summing amount received column in the Receive Cash table for only those rows which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest.




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




  1. 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:




  • Sales




  • Sales-Inventory

It will also need to reference the Customer and Inventory tables to identify relevant information (shipping and billing addresses, item description, etc.).





  1. 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 requires making assumptions about inventory cost/valuation method (FIFO, LIFO, weighted average, specific identification).


  • Sales

Sum the product of quantity sold times unit price for all rows in the M:N Sales-Inventory relationship table




  • Wage expense

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


Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee’s pay rate).
Step 3: Sum the total amounts in the table produced in step 2.

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 method (FIFO, LIFO, weighted average, specific identification).



  • Cash

Step 1: Calculate the beginning balance of cash by summing the amount column in the Cash table.


Step 2: Calculate total cash receipts by summing the amount column in the Receive Cash table.
Step 3: Calculate total cash disbursements by summing the amount column in the Disburse Cash table.
Step 4: Ending Cash Balance = Answer to Query 1 + Answer to Query 2 – Answer to Query 3.



  • Accounts Receivable




  1. Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table.




  1. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales – Inventory M:N relationship table.




  1. Calculate total cash received from customers by summing amount received column in the Receive Cash table.




  1. Total Accounts Receivable = Query A + Query B – Query C




  • Accounts Payable

Step 1: Normally, only orders actually received are considered purchases for purposes of calculating accounts payable. Therefore, begin with a query of the Receive Inventory table (or M:N relationship table linking the Order Inventory and Receive Inventory events) to determine which orders have been received this fiscal period.


Step 2: Query the Order Inventory – Inventory table to determine the total amount purchased this fiscal period by summing the product of quantity ordered by its unit cost for those purchase orders for which there is a corresponding receipt of inventory (from step 1).
Step 3: Retrieve the total beginning balance of Accounts Payable by querying the Suppliers table and summing the beginning balance column.
Step 4: Query the Disburse Cash table to calculate the total amount paid to suppliers this fiscal period by summing the amount column for every row in which the supplier number is not null. It is important to exclude rows where the supplier number is null, as those represent other types of payments (e.g., payroll).
Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 – Answer to Query 4.



  • Wages Payable

Step 1: Query the Time Worked table to sum the total hours worked, grouped by employee number, restricted to only those rows for which the check number column is null.


Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee’s pay rate).
Step 3: Sum the total amounts column in the table produced in step 2.


  1. 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.




  1. Validity check on item numbers.




  1. Sign check on quantity sold and price fields.




  1. Completeness checks on customer information (billing and shipping address) – this information should ideally be automatically populated upon entering the customer number.




  1. Completeness checks on inventory information (description, list price, etc.) – this information should ideally be automatically populated upon entering the item number.




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




18-

© 2011 Pearson Education, Inc. Publishing as Prentice Hall






Share with your friends:
1   2   3   4   5




The database is protected by copyright ©essaydocs.org 2020
send message

    Main page