Accounting Information Systems: A Business Process Approach

Accounting Information Systems: A Business Process Approach Chapter Six: Understanding and Designing Queries and Reports

Exhibits: 6.2, 6.3 Tables: 6.1 - 6.10

Exhibit 6.2 Query Design Template Table Table 1 Table 2 Table 3 1. What attributes do users require in the query output? 2. What criteria will be used to generate the output? What attributes will be used in the criteria? 3. What foreign keys link the information in a table to the primary keys of other tables in the query?

Exhibit 6.3 Report Design Template: Content and Organization Title on Report Header Type of Report Source by Table Table 1 Table 2 Table 3 1. Attributes displayed on report 2a. Attributesnot displayed on report, but necessary for a calculation 2b. Criteria for selection of records in table 3. Foreign keys that link this table to the others used in the report (if any) Content PlacementAttribute Names* and Calculations** Used Report header Report details*** Order by Report footer For grouped detail reports: Group by Group header Group detail Order detail by roup footer Formulas for calculations: *Enter the attribute name in the row for each section where it is reported. Precede each attribute name with an abbreviation of the source table’s name. **Indicate the label for the calculation in the row for the section where it is to be displayed, and show the formula for the calculation on the bottom row of the template. ***For grouped detail reports, the report details sections consist of data about each group. For such reports, we specify what appears in the report details area by completing the information in the “For grouped detail reports” section of this template.

Table 6.1 Summary of ELERBE’s Database Design and Sample Data • Design of Tables • Primary • Table Name Key Foreign Key Other Attributes • Inventory ISBN Author, Title, Price, Quantity_On_Hand, • Quantity_Allocated • Customer Customer# Name, Address, Contact_Person, Phone • Order Order# Customer# (links to Order_Date • Customer Table) • Order_Detail Order#, Order# (links to Order Table) Quantity • ISBN* ISBN (links to Inventory Table) • *compound primary key • Inventory Table • Quantity_ Quantity_ • ISBN Author Title Price On_Hand Allocated • 0-256-12596-7 Barnes Introduction to Business $78.35 4,000 300 • 0-127-35124-8 Cromwell Building Database Applications $65.00 3,500 0 • 0-135-22456-7 Cromwell Management Information $68.00 5,000 50 • Systems • 0-146-18976-4 Johnson Principles of Accounting $70.00 8,000 260 • 0-145-21687-7 Platt Introduction to E-commerce $72.00 5,000 40 • 0-235-624-6 Rosenberg HTML and Javascript Primer $45.00 6,000 0

Table 6.1 Summary of ELERBE’s Database Design and Sample Data (Concluded) Customer Table Customer# Name Address Contact_Person Phone 3450 Brownsville C.C. Brownsville, TX Smith 956-555-0531 3451 Educate, Inc. Fairhaven, MA Costa 508-888-4531 3452 Bunker Hill C.C. Bunker Hill, MA LaFrank 617-888-8510 Order_Detail Table Order# ISBN Quantity 0100011 0-256-12596-7 200 0100011 0-146-18976-4 150 0100012 0-135-22456-7 50 0100012 0-146-18976-4 75 0100012 0-145-21687-7 40 0100013 0-146-18976-4 35 0100013 0-256-12596-7 100 Order Table Order# Order_Date Customer# 0100011 05/11/2003 3451 0100012 05/15/2003 3451 0100013 05/16/2003 3450

Table 6.2 Query Design for QUERY A QUERY A: List of all publications by Cromwell Inventory Table 1. What attributes do users require in the query ISBN, Author, Title output? 2. What criteria will be used to generate the output? Author=“Cromwell” What attributes will be used in the criteria?

Table 6.3 Results of Query A ISBN Title Author 0-127-35124-8 Building Database Applications Cromwell 0-135-22456-7 Management Information Systems Cromwell Note: For the full Inventory Table, see Table 6.1 on page 244.

Table 6.4 Query Design for QUERY B QUERY B: List of all orders on 05/15/2003 Order Table 1. What attributes do users require in the query Order#, Order_Date, output? Customer# 2. What criteria will be used to generate the output? Order_Date5#05/15/2003# What attributes will be used in the criteria?

Table 6.5 Panel A: Query Design for QUERY C The marketing manager wants a report that shows all orders for a book identified as ISBN 0-127-35124-8 during the year 2002. The information needed includes Name, Address, Contact_Person, Phone, and Quantity. Table Order Order_Detail Customer 1. What attributes do Quantity Name, Address, users require in the Contact_Person, query output? Phone 2. What criteria will be Order_Date.#12/31/2001# ISBN=0-127-35124-8 used to generate the and Order_Date,#01/01/2003# output? What attributes will be used in the criteria? 3. What foreign keys link Customer# (to identify Order# (to link to the the information in a appropriate customer) Order records) table to the primary keys of other tables in the query?

Table 6.5 (Continued) Panel B: Query Design for QUERY D The marketing manager wants a report that shows all orders for a book identified as ISBN 0-127-35124-8, regardless of year of order. The information needed includes Name, Address, Contact_Person, Phone, and Quantity. Table Order Order_Detail Customer 1. What attributes do Quantity Name, Address, users require in the Contact_Person, query output? Phone 2. What criteria will be ISBN=0-127-35124-8 used to generate the output? What attributes will be used in the criteria? 3. What foreign keys Customer# (to identify Order# (to link to link the information appropriate customer) the Order records) in a table to the primary keys of other tables in the query?

Table 6.5 (Concluded) Panel C: Query Design for QUERY E The marketing manager wants a report that shows all orders for a book with the title, “Building Database Applications,” during the year 2002. The manager does not know the ISBN for that book. The information needed is Name, Address, Contact_Person, Phone, and Quantity. Table Order Order_Detail Inventory Customer 1. What attributes Quantity Name, Address, do users require Contact_Person, in the query Phone output? 2. What criteria will Order_Date.#12/31/2001# Title = be used to gener- and “Building ate the output? Order_Date,#01/01/2003# Database What attributes Applications” will be used in the criteria? 3. What foreign keys Customer# (to Order# (to link to link the informa- identify appropriate the Order records) tion in a table to customer) ISBN (to link to the the primary keys Inventory record) of other tables in the query?

Table 6.6 Fairhaven Convenience Store: Tables with Sample Data Manager Table SSN Last_Name First_Name Address File_Status Exemptions 105-50-1234 Green Cindy Plainville, MI Single 1 154-08-8304 Ola Patrick Newport, MI Married 3 012-50-1237 Barley Thomas Wareham, MI Single 1 023-45-8921 Mello Jay Paris, MI Married 4 Inventory Table Product# Description Supplier Reorder_Point Begin_OH 101 Regular gas Shell 1000 10000 102 Engine oil Mobil 50 100 103 Antifreeze Dow 30 10 Sale Table Sale# SSN Date Sales_Tax 201 105-50-1234 12/15/03 $0.85 202 105-50-1234 12/15/03 $1.45 203 154-08-8304 12/15/03 $1.00 204 154-08-8304 12/16/03 $0.15

Table 6.6 Fairhaven Convenience Store: Tables with Sample Data (Concluded) Sale_Detail Table Sale# Product# Quantity_Sold Price 201 101 13 $2.00 201 103 1 $1.50 202 101 14 $1.50 202 102 2 $3.00 203 101 10 $2.00 204 102 1 $3.00 Deposit Table Deposit# Date Amount 801 12/15/03 $77.80 Note: In the examples in this chapter, we will assume that the quantity on hand is updated only periodically. Thus, the Quantity_On_Hand field in the Inventory Table has been replaced by the Begin_OH field. The Begin_OH represents the quantity on hand at the beginning of the period.

Table 6.7 Report Content for the Grouped Detailed Status Report in Figure 6.6A Title on Report Header Detailed Inventory Status Report Type of Report Grouped detail status report Source by Table Sale (S) Sale_Detail (SD) Inventory (I) 1. Attributes displayed Date Sale#, Product#, on report Quantity_Sold Description, Supplier, Begin_OH 2a. Attributesnot displayed on report, but necessary for a calculation 2b. Criteria for selection of N/A N/A N/A records in table 3. Foreign keys that link this Product#, table to the others used Sale# in the report (if any)

Table 6.8 Report Organization for Grouped Detailed Status Report in Figure 6.6A • Attribute Names • Content Placement and Calculations* Used • Report header No attributes or calculations • Details See group details • Order by I: Product# • Report footer N/A • For grouped detail reports: • Group by SD: Product# • Group header I: Product#, Description, Supplier, Begin_OH • Group detail SD: Sale#, Quantity_Sold; S: Date • Order detail by SD: Sale# • Group footer Current_Quantity_On_Hand* • *Formulas for calculations: • Current_Quantity_On_Hand = Begin_OH - Sum (Quantity_Sold) • I = Inventory Table; SD = Sales_Detail Table; S = Sales Table

Table 6.9 Sample Data for H & J Tax Preparation Service • Services Table • Year-to-Date • Service# Service_Description Fee Revenues • 1040 Federal Individual Income Tax Form 1040 (long form) $100 $120,000 • Sch-A 1040 Schedule A (itemized deductions) $50 $ 51,000 • Sch-B 1040 Schedule B (interest & dividend earnings) $50 $ 53,300 • Sch-C 1040 Schedule C (sole proprietorship) $110 $ 84,000 • State State Income Tax Return $80 $ 81,000 • Corp Corporate Income Tax $30 (per hr.) $103,000 • Client Table • Client# Client_Name Address Telephone Beg_Bal • 1001 Robert Barton 242 Greene St., St. Louis, MO 314-222-3333 $0 • 1002 Donna Brown 123 Walnut St., St. Louis, MO 314-541-3322 $0 • 1003 Sue Conrad 565 Lakeside, St. Louis, MO 314-541-6785 $0 • Service_Request Table • Request# Client# Accountant# Date • 104 1001 405-60-2234 02/12/03 • 105 1003 405-60-2234 02/15/03 • 106 1002 512-50-1236 02/16/03

Table 6.9 Sample Data for H & J Tax Preparation Service (Concluded) Accountant Table Accountant# Accountant_ Name 405-60-2234 Jane Smith 512-50-1236 Michael Speer Service_Request_Detail Table Request# Service# Fee 104 1040 $100 104 Sch-A $ 50 104 Sch-B $ 50 104 State $ 80 105 1040 $100 105 State $ 80 106 1040 $100 106 Sch-A $ 50 106 Sch-B $ 50 106 Sch-C $110 106 State $ 80 Invoice Table Invoice# Request# Invoice_Date Amount 305 104 02/13/03 $280 306 106 02/22/03 $390 307 105 02/23/03 $180

Table 6.10 Grouped Detail Status Report • Content and Organization • Title on Report Header Detailed Inventory Status Report • Type of Report Grouped detail status report • Source by Table Sale (S) Sale_Detail (SD) Inventory (I) • 1. Attributes displayed Date Sale#, Product#, Descrip- • on report Quantity_Sold tion, Supplier, • Begin_OH • 2a. Attributesnot displayed • on report, but necessary • for a calculation • 2b. Criteria for selection of • records in table • 3. Foreign keys that link this Product# • table to the others used Sale# • in the report (if any)

Table 6.10 Grouped Detail Status Report (Concluded) Content Placement Attribute Names and Calculations* Used Report header No attributes or calculations Report details See group details Order by Product# group Report footer N/A For grouped detail reports: Group by I: Product# Group header I: Product#, Description, Supplier, Begin_OH Group detail SD: Sale#, Quantity_Sold S: Date Order detail by SD: Sale# Group footer Current_Quantity_On_Hand* *Formulas for calculations: Current_Quantity_On_Hand = Begin_OH - Sum (Quantity_Sold) for a particular product

Accounting Information Systems: A Business Process Approach