Journal of Information Systems Vol No Spring pp Developing

Journal of Information Systems Vol. 15, No. 1 Spring 2001 pp. 35–56 Developing Database Query Proficiency: Assuring Compliance for Responses to Web Site Referrals A. Faye Borthick Donald R. Jones Georgia State University Ryan Kim Colonial Pipeline Company ABSTRACT: This case illustrates how database queries can be used to provide continuous assurance in a situation where two trading partners want assurance of the other’s compliance with their agreements. In this two-sided assurance situation, a car maker wants assurance that its dealers make timely responses to web site customers and the dealers want assurance that the car maker is sending them all the designated customer referrals. The queries, developed in Microsoft Access®, illustrate the kind of queries that accountants could prepare to perform continuous monitoring of business activities. In this situation, referrals not in compliance with the agreements might be sent automatically to car maker and dealer managements. Query-based approaches to continuous assurance are likely to become more common as trading partners devise new business relationships and want assurance that the other party is abiding by their mutual agreements. Keywords: continuous assurance; database querying; query strategy; queries. I. THE CASE The Business Situation hen trading partners devise new ways of working with each other, they may not know what to expect. For example, when a car maker creates a web site for potential customers to inquire about new vehicles and refers these inquiries to dealers, the car maker will be concerned about the timeliness of the dealers’ responses. The dealers, on the other hand, want their fair share of the referrals. The car maker and the dealers can promise to abide by an agreement on response time and the way referrals are allocated, but neither the car maker nor their dealers seem ready to trust the other one completely given the history of their relationships and the potential for technology-induced changes in car sales.1 One approach to helping a car maker and its dealers work together to promote sales of vehicles is to employ a mechanism for providing assurance to each party that the other party is abiding by their agreement. This kind of two-sided assurance could be achieved through the use of database queries that could W 1 A potential source of future conflict between car makers and their dealers is likely to be car makers’ attempts to enter retail markets directly through Internet sales (Wallace 1999b; Rich and Lundegaard 1999; Warner 1999). Even though dealers object to car makers selling over the Internet (Wallace 1999a), car makers are being pressed to sell over the Internet by customers’ ability to car shop on the Internet (Harris 1999). The authors are indebted to Penny Lyman and anonymous reviewers for helpful comments about this work. 36 Journal of Information Systems, Spring 2001 run periodically or continuously to identify noncompliant responses soon enough for corrective action to be effective. Your assignment, explained below, is to develop database queries that give the car maker assurance about dealer compliance and give dealers assurance about car maker compliance. The Database To make it easier for people to buy its cars, a car maker established a web site that lets potential buyers indicate which model(s) and features interest them.2 After receiving the information from a potential buyer, the car maker records it in database tables like the following: Customer: Primary key = CustomerID Customer ID 000342512 000342525 000342539 000342546 First Name Ryan Daniel Terrel Cathy Last Name Hong Lowell Thomas Allen Street 710 London Rd 225 Burbank Dr 2985 Peachtree St 1827 McPherson Rd City Atlanta Atlanta Atlanta Atlanta Post Code 30344 30314 30360 30303 Phone Work 404-876-4875 404-567-2245 770-975-6521 770-988-6521 Phone Home 404-548-6625 404-514-8898 770-548-9658 770-985-3542 Email Address ryan0980@aol.com low008@aol.com Gold076@hotmail.com Allen23@mci.com CustomerInquiry: No primary key CustomerID 000342512 000342512 000342525 000342525 000342539 000342546 000342546 FirstName Ryan Ryan Daniel Daniel Terrel Cathy Cathy LastName Hong Hong Lowell Lowell Thomas Allen Allen ModelInInquiry Concorde LHS, Sebring Coup Concorde Sebring Convertible 300M, LHS Concorde Town & Country InqDateTime 11/10/99 11/12/99 11/12/99 11/15/99 11/15/99 11/25/99 12/11/99 The car maker emails each inquiry to the dealer that is closest to the potential buyer, where “closest” is operationalized as the dealer whose 5-digit postal code minimizes the difference between the buyer’s postal code and the dealer’s postal code.3 Information about the inquiries that dealers receive is kept in the ReferralToDealer table. The date/timestamp in the table is the time a referral was sent to the dealer and is automatically generated by the referral system. ReferralToDealer: Primary key = ReferralID ReferralID 000010345 000010352 000010363 000010379 000010382 000010394 000010407 CustomerID 000342512 000342525 000342539 000342546 000342512 000342525 000342546 DealerID 0024145 0016287 0037269 0405718 0024145 0016287 0405718 Ref DateTime 11/10/99 10:00:00 AM 11/12/99 1:15:00 PM 11/15/99 10:00:00 AM 12/11/99 11:35:00 AM 11/12/99 11:00:00 AM 11/15/99 2:20:00 PM 11/25/99 10:00:00 AM 2 3 For example, see Waltner (2000). The postal code scheme is used simply to illustrate the kinds of computations that could be performed in the queries. For another allocation scheme, see Wallace (1998). Borthick, Jones, and Kim—Developing Database Query Proficiency 37 Information about dealers is kept in the following table: Dealer: Primary key = DealerID DealerID 0016287 0023718 0024145 0035284 0037269 0405718 Name Buckhead Auto Neal Pope Motorcar Paul Light Afford Auto Bob Motoring Town Touring Street 3126 Piedment Rd 4420 Buford Hwy 4125 Piedmont Rd 3350 Cumberland Rd 330 Forrest Rd 141 Piedmont Ave City Atlanta Atlanta Atlanta Atlanta Atlanta Atlanta PostCode 30305 30341 30342 30339 30349 30303 Phone 404 770 404 404 404 404 261-1851 216-9700 261-1851 303-1400 361-3832 659-3673 After they contact customers, dealer staff enter information about the contacts, including the date and time, into a web form. Dealers then click the Submit button on the web form, which sends this selfreported information to a web site provided by the car maker and initiates script processing that writes the information in the following table: DealerResponseToReferral: Primary key = ReferralID ReferralID 000010345 000010352 000010363 000010379 000010382 000010394 000010407 DealerID 0024145 0016287 0037269 0405718 0024145 0016287 0405718 ResDateTime 11/12/99 11:30:00 AM 11/12/99 4:00:00 PM 11/15/99 4:30:00 PM 12/11/99 4:00:00 PM 11/15/99 12:20:00 PM 11/16/99 9:00:00 AM 11/25/99 1:00:00 PM PhoneResponse Yes No Yes No No Yes No EmailResponse Yes Yes No Yes Yes Yes Yes Another table in the database, EmailResponseToReferral, contains information about the dealer’s emailed responses to potential buyers. Each dealer copies the car maker on its email messages to customers, and the car maker gets the dates/timestamps from the email header information that the car maker’s email system generates automatically. EmailResponseToReferral: Primary key = ReferralID ReferralID 000010345 000010352 000010379 000010382 000010394 000010407 DealerID 0024145 0016287 0405718 0024145 0016287 0405718 ResDateTime 11/12/99 11:30:00 AM 11/15/99 1:36:00 PM 12/11/99 5:00:00 PM 11/15/99 12:20:00 PM 11/15/99 5:00:00 PM 11/25/99 1:00:00 PM EmailAddress ryan0983@aol.com low008@aol.com Allen23@mci.com ryan0983@aol.com low008@aol.com Allen23@mci.com Because each referral represents the potential sale of a car, dealers should be motivated to respond quickly to all the referrals they receive. Still, some potential buyers complain that a week passes before a dealer contacts them. (That interval is long enough to change one’s mind about buying the car.) Being exasperated with the situation,4 the car maker informed its dealers that it would cease making referrals to dealers that take more than 48 hours to contact the customers referred to them. Having created this policy, the car maker now has to implement it, which means identifying any dealers not in compliance. 4 See Wilson (2000). 38 Journal of Information Systems, Spring 2001 Required Part 1: Assurance for the Car Maker about Dealer Compliance Develop the following sequence of database queries and use the query results to explain the extent to which car dealers are contacting customers referred to them within 48 hours: 1. Determine response times for dealer-reported responses. 2. Find the average response time by dealer for self-reported times. 3. Determine response times for dealers’ emailed responses. 4. Find the average response time for dealers’ emailed responses. 5. Compare dealers’ self-reported response times to dealers’ emailed response times. 6. Find the average time difference between dealers’ self-reported and emailed responses. Part 2: Assurance for Dealers about Car Maker Compliance A. Develop the following sequence of database queries and use the query results to explain the extent to which the car maker is referring customers to the closest dealer, where “closest” is defined as the dealer that minimizes the absolute value of the difference between the customer’s and the dealer’s postal codes: 1. For each referral, determine the difference between the postal codes of the customer and the dealer receiving the referral. 2. For each referral, determine the minimum postal code difference over all dealers. 3. For each referral, determine whether the referred dealer corresponds to the minimum postal code difference. B. Design a criterion for “closest” dealer that would be better than minimizing the absolute value of the difference between the customer’s and the dealer’s postal codes for the geographic area in Figure 1. Part 3: Objective Questions Select the best response to each of the following questions. A. Making the DealerResponseToReferral and EmailResponseToReferral tables available to a new query will result in which of the following join results: 1. No join occurs until the user connects the attributes to be joined. 2. No join occurs until the user highlights the attributes to be joined and clicks the join icon. 3. A join occurs automatically because the tables have identical primary keys. 4. A join occurs automatically because the tables have some identical attributes. B. Suppose the database contains the following queries: 1. qDealerResponse, which has attributes DealerID, ReferralID, DealerResponseToReferral.ResDateTime, RefDateTime, and DealerResponse where DealerResponse = ([DealerResponseToReferral.ResDateTime] – [RefDataTime])*24 2. qEmailResponse, which has attributes DealerID, ReferralID, EmailResponseToReferral.ResDateTime, RefDateTime, and DealerResponse where EmailResponse = ([EmailResponseToReferral.ResDateTime] – [RefDataTime])*24 Making the qDealerResponse and qEmailResponse queries available to a new query will result in which of the following join results: 1. No join occurs until the user connects the attributes to be joined. 2. No join occurs until the user highlights the attributes to be joined and clicks the join icon. 3. A join occurs automatically because the tables have identical primary keys. 4. A join occurs automatically because the tables have some identical attributes. C. From the perspective of web-savvy customers, the best measure of response time to their inquiries would be the elapsed time based on the date/time attributes in the tables: 1. CustomerInquiry and DealerResponseToReferral 2. ReferralToDealer and DealerResponseToReferral Borthick, Jones, and Kim—Developing Database Query Proficiency 39 D. E. F. G. H. I. 3. ReferralToDealer and EmailResponseToReferral 4. CustomerInquiry and EmailResponseToReferral Consider a query for which the DealerResponseToReferral table and the EmailResponseToReferral table have been made available. In the calculated expression: [DealerResponseToReferral.ResDateTime] – [EmailResponseToReferral.ResDateTime] the term “[DealerResponseToReferral]!”: 1. Is necessary 2. Is unnecessary 3. Causes a syntax error 4. Causes multiple joins Suppose the CustomerInquiry table, the Dealer table, and the ReferralToDealer table are made available to a new query and the user does not change the way the query manager joined the tables. After CustomerID is entered in the first field, the result for the query will have how many rows? 1. The number of rows in the CustomerInquiry table 2. The number of rows in the ReferralToDealerTable 3. The number of rows in the CustomerInquiry table times the number of rows in the ReferralToDealer table 4. The number of rows in the CustomerInquiry table times the number of rows in the Dealer table Suppose the Customer table, the Dealer table, and the ReferralToDealer table are made available to a new query and the user does not change the way the query manager joined the tables. After CustomerID is entered in the first field, the result for the query will have how many rows? 1. The number of rows in the Customer table 2. The number of rows in the ReferralToDealer table 3. The number of rows in the Customer table times the number of rows in the ReferralToDealer table 4. The number of rows in the Customer table times the number of rows in the Dealer table Suppose the car maker has complied with the agreement to refer customers to the closest dealer, where “closest” is defined as the dealer for which the absolute difference between the dealer’s postal code and the customer’s postal code is minimized (see the postal code map in Figure 1.) Also suppose that customers complain about the distances to the dealers to which they were referred. A better approach to allocating customers to dealers that minimizes the need for future program or data table changes would be to: 1. Preassign postal codes to dealers and use a table lookup to make the assignments 2. Preassign telephone prefixes to dealers and use a table lookup to make the assignments 3. Assign customers to dealers to minimize driving distance based on automatic lookup of street addresses in a geographic database 4. Assign customers to dealers such that the distance based on latitude and longitude coordinates is less than x miles and dealers get referrals in proportion to their sales The potential enhancement to the car maker’s web site that would be the most attractive to customers and the least likely to prompt resistance from dealers is to: 1. Give customers the ability to scan dealers’ new car inventories 2. Give customers access to the prices that the car maker charges dealers 3. Let customers purchase vehicles directly from the car maker 4. Let customers see pricing for vehicles from other car makers without leaving the site The best composition of a primary key for the Customer Inquiry table is: 1. CustomerID 2. InqDateTime 3. CustomerID and ModelInquiry 4. CustomerID and InqDateTime 40 Journal of Information Systems, Spring 2001 J. Consider a query for which the Dealer, ReferralToDealer, and Customer tables have been made available. In this query, the calculated expression: Abs([Dealer.PostCode] – [Customer.PostCode]) would be equivalent to: 1. Abs([Dealer.PostCode] ) – Abs( [Customer.PostCode]) 2. [Customer.[PostCode] – [Dealer.[PostCode] 3. Abs([Customer.PostCode] – [Dealer.PostCode]) 4. – ([Customer.PostCode] – [Dealer.PostCode]) Borthick, Jones, and Kim—Developing Database Query Proficiency 41 II. TEACHING NOTES Learning Objectives This case comprises a learning experience that helps students learn to: (1) decide what information would be relevant to solving a business problem; (2) extract the needed information; (3) put the information in a form conducive to solving the problem; and (4) analyze the information to solve the problem. The case operationalizes the learning objectives in the information-use category (Borthick 1996). It characterizes a business situation for which management wants information of the kind that accountants might be asked to produce by querying a database (Borthick 1992). The context for the case, assuring car dealer and car maker compliance with their agreements, was chosen because it is situated in current business practice. The context illustrates the problems that companies may have as they attempt to transform their interactions with each other and customers to take advantage of the new ways of communicating and processing information that information technology has enabled. The assurance need is posed as two-sided (i.e., each trading partner wants assurance about the other’s behavior) because that illustrates the growing mutual dependence that trading partners have on each other. The case scenario was designed with the least number of tables and attributes necessary to make the situations plausible, but the database still contains sufficient distractor tables and attributes to require learners to think through the implications of their querying. For example, the CustomerInquiry table, a view table with no primary key, duplicates attributes contained in the Customer table. Learners can be engaged in a discussion of whether this table ought to be included in a well-designed database. Because business problems often come with irrelevancies intermingled with essential information, so does this case. The database tables contain very few rows, allowing learners to verify their query results easily by inspecting table rows. A Microsoft Access® file (Office 2000) containing the database is available from the first author on request. The case has no requirement for assessing the reliability of the database before conducting the querying. At the instructor’s discretion, learners could be engaged in a discussion of how this would be done. In this situation, the most likely assurors would be the car maker’s internal audit staff, who would likely do no more to assure database reliability than the following: 1. Verify that the database tables are complete. 2. Verify that the email system was functioning during the time the referrals were made. 3. Verify that script processing for web form capture of car dealer responses about their contacts with customers was functioning as designed. In Part 1 (assurance for the car maker about car dealer compliance), learners (1) use default table linking to make the information they need accessible, (2) create mathematical expressions to manipulate attribute values in rows, (3) format and sort result columns, (4) apply functions to columns, and (5) combine query results to permit performance comparisons. Query Strategies 3 and 4 essentially duplicate Query Strategies 1 and 2 but with one of the source tables being different. This repetition yields a longer solution narrative, but many learners seem to benefit from the repetition. That is, many learners do not seem to be able to internalize the query strategy and the querying steps with just one encounter. The overall querying outcome identifies procedural lapses at specific car dealers. In Part 2 (assurance for car dealers about car maker compliance), learners (1) use default and idiosyncratic table linking, (2) create mathematical expressions to manipulate attribute values in rows, (3) apply functions to columns, and (4) combine query results to permit performance comparisons. The overall querying outcome identifies a design flaw in the way referrals are allocated to dealers. 42 Journal of Information Systems, Spring 2001 In Part 3 (multiple-choice questions), the learning objectives are to: Query Focus: Problem Strategy or Querying Syntax Syntax Syntax Strategy Syntax Strategy Syntax Syntax Learning Objective Use linking relationships to make the desired data (and only the desired data) accessible Join tables without primary keys Adopt a customer perspective on performance Qualify attributes to ensure uniqueness of reference Improve system design given constraints Designate a primary key for a view table Specify precedence relations in expressions Question A, E, F B C D G, H I J Case Use: Courses and Prior Learner Proficiencies The case has been used in courses in accounting information systems (AIS) and information systems assurance. It could be used in other accounting and business courses, e.g., introductory accounting, business processes, or assurance, in which a learning objective is to have students experience database querying to answer business questions whose answers do not fit a pre-structured response format. Instead, students must create a response that meets the information need. This kind of learning experience is more likely to prepare students “for the ambiguous business world they will encounter upon graduation” (Albrecht and Sack 2000, 43). The case is usable by learners with varying degrees of proficiency in formulating query strategy and in using a database query interface. An instructor can accommodate varying levels of learner proficiency by supplying different portions of the solution text. For example, if they are somewhat skilled in strategy formulation and in using the query interface, learners could be given the business situation, the database, and the general statements of the two questions to be answered with the suggested query sequences. (This is the way the case is posed above.) If learners are very skilled at formulating query strategy, then the query sequence could be withheld for the two questions. If learners need help formulating query strategy, then some or all of the following strategy sections (identified with “Query Strategy” at the beginning of the heading) could be supplied too. If they need help developing the database queries, then learners could be given some or all of the following query development sections (identified with the heading “Query Creation”). If learners have no knowledge of database systems, then it is helpful for them to have access to a database text, e.g., Perry and Schneider (2001). Part 1 Solution: Assurance for the Car Maker about Car Dealer Compliance Query Strategy 1: Determine Dealer-Reported Response Times Join tables with referral time and dealer-reported response time. The first step in providing assurance to the car maker is deciding what data are required. Because the question is about dealer performance in responding to customer referrals, the required tables are ReferralToDealers and DealerResponseToReferrals. Because the field of interest is the elapsed time between when the car maker sent the referral to the dealer (field RefDateTime in table ReferralToDealer) and when the dealer responded to it (field ResDateTime in table DealerResponseToReferral), the two tables need to be joined on the field ReferralID. Joining the tables makes the two date/time values for each referral accessible in the same row so that the elapsed time (the response time) can be calculated. Query Creation Load the database in Microsoft Access® (Office 2000). Select Queries from the Objects menu and double-click Create query in Design view. In the Show Table screen, highlight the ReferralToDealer table and select Add, which makes the table appear in the Select Query screen. (Another way to make the table appear in the Select Query screen is to double-click it in the Show Table screen.) Similarly, make the DealerResponseToReferral table appear in the Select Query screen and then Close the Show Table screen. Borthick, Jones, and Kim—Developing Database Query Proficiency 43 The tables join themselves automatically on ReferralID because ReferralID is the identical primary key in both tables. A line connecting the two ReferralID fields signifies the join. (For online information about creating or deleting relationships between tables, go to the help menu, click Microsoft Access® Help, click the Index tab, type “relationship”, select Search, and select “Create or modify relationships” and “Work with relationships”.) Linking (joining) the tables selects only those rows with matching values in the ReferralID field in both tables. Determine dealer-reported response times. To create a table with dealer-reported response times, select the needed fields from the ReferralToDealer and DealerResponseToReferral tables and create a calculated field for the response time, the difference between the ResDateTime field and the RefDateTime field. Mentally calculate the response time for a sample row to verify the results of the subtraction and the units (e.g., hours or days) in which it is presented. A result that is given in days can be converted to hours by including the appropriate multiplier in the calculation. Query Creation To create a table with dealer-reported response times, select the fields ReferralID, DealerID, and RefDateTime from the ReferralToDealer table (by double-clicking them one at a time) and the ResDateTime field from the DealerResponseToReferral table. Selecting the fields makes their values available in the result table. Because they also appear in the DealerResponseToReferral table, the ReferralID and DealerID fields could be selected from the DealerResponseToReferral table instead of the ReferralToDealer table. Although it is not finished, the query needs a name, and naming it makes the field names more accessible during query building. To name it, select the quit icon (X) in the upper right-hand corner of the Select Query screen, select Yes in response to the “Do you want to save changes…?” prompt, enter a meaningful name for the query, e.g., DealerResponse, and select OK. To see what the query result is thus far, double-click the DealerResponse query. Return to Design view (the Select Query screen) by selecting the design icon, the protractor symbol at the left end of the toolbar. To finish the query, left click in the blank field to the right of the ResDateTime field in the Select Query screen. Select the build icon (magic stick symbol) from the tool bar, which opens the Expression Builder screen. The expression builder is the tool for specifying mathematical operations on the values of fields. (For online information about expressions, on the help menu, click Microsoft Access® Help, click the Index tab, type “expression builder” and select Search.) To create the expression to subtract the time of the referral from the time of the dealer’s reported response, double-click the ResDateTime field in the middle column of the Expression Builder screen, select the subtraction symbol in the expression builder (or type a minus sign), and doubleclick the RefDateTime field. The text of the expression looks like: [ResDateTime] – [RefDateTime] Select OK in the Expression Builder screen, select the quit icon (X) in the upper right-hand corner of the Select Query screen, and select Yes in response to the “Do you want to save changes…?” prompt. Open the query by double-clicking its name, which gives the following result: Query: DealerResponse ReferralID 000010345 000010352 000010363 000010379 000010382 000010394 000010407 DealerID 0024145 0016287 0037269 0405718 0024145 0016287 0405718 RefDateTime 11/10/99 10:00:00 AM 11/12/99 1:15:00 PM 11/12/99 1:15:00 PM 11/12/99 1:15:00 PM 11/12/99 11:00:00 AM 11/15/99 2:20:00 PM 11/25/99 10:00:00 AM ResDateTime 11/12/99 11:30:00 AM 11/12/99 4:00:00 PM 11/15/99 4:30:00 PM 12/11/99 4:00:00 PM 11/15/99 12:20:00 PM 11/16/99 9:00:00 AM 11/25/99 1:00:00 PM Expr1 2.0625 0.114583333328483 0.270833333335759 0.184027777773736 3.05555555555475 0.777777777781012 0.125 To verify that the result is what was intended and to figure out how time is represented, mentally subtract RefDateTime from ResDateTime for one of the rows in the result table. The result of 44 Journal of Information Systems, Spring 2001 the subtraction is represented in days. To make the result appear as hours, the expression has to convert days to hours. To make the result appear in hours, switch to Design view (by selecting the design icon), click the field with the expression, open the expression builder, add left and right parentheses around the expression [ResDateTime]–[RefDateTime], position the cursor after the right parenthesis, select the multiplication symbol (*) (or type it), and enter the digits 24. Replace “Expr1” (the default name of the expression) with a meaningful name such as ResponseInHours. Now the expression looks like: ResponseInHours: ([ResDateTime] – [RefDateTime])*24 Select OK to close the expression builder. To format the ResponseInHours column to make it show the values with two decimal digits, left click in the ResponseInHours field, right click, select Properties, select Format, select Fixed from the pulldown menu, and close the Field Properties window. To make the result table display the result in order with the row with the largest ResponseInHours value appearing first, click in the Sort row of ResponseInHours. From the pulldown menu, select Descending. (For online information about sorting data, on the help menu, click Microsoft Access® Help, click the Index tab, type “sort”, and select “Sort records in a table, query, or form” from the topic list.) Run the query (by selecting !) and verify that it gives the intended result. Once the query gives the right result, save it by selecting the quit icon (X) in the upper right-hand corner of the Select Query screen and selecting Yes in response to the “Do you want to save changes…?” prompt. With the time units in hours, the DealerResponse query gives the following result: Query: DealerResponse ReferralID CustomerID 000010382 000010345 000010394 000010363 000010379 000010407 000010352 000342512 000342512 000342525 000342539 000342546 000342546 000342525 DealerID 0024145 0024145 0016287 0037269 0405718 0405718 0016287 RefDateTime 11/12/99 11:00:00 AM 11/10/99 10:00:00 AM 11/15/99 2:20:00 PM 11/15/99 10:00:00 AM 12/11/99 11:35:00 AM 11/25/99 10:00:00 AM 11/12/99 1:15:00 PM ResDateTime 11/15/99 12:20:00 PM 11/12/99 11:30:00 AM 11/16/99 9:00:00 AM 11/15/99 4:30:00 PM 12/11/99 4:00:00 PM 11/25/99 1:00:00 PM 11/12/99 4:00:00 PM Response InHours 73.33 49.50 18.67 6.50 4.42 3.00 2.75 Query Strategy 2: Find the Average Response Time by Dealer for Self-Reported Times Examining the average response time for each dealer (referred to as “by dealer”) is another way to get a sense of the behavior of each dealer in responding to referrals. To find the average of self-reported response times for each dealer, create a new query based on the DealerResponse query, indicate the average to be calculated, and group the query results with the largest average first (descending). Query Creation To create a query to find the average response time by dealer, select Queries in the Objects menu, double-click Create query in Design view, select the Queries tab, select the DealerResponse query, select Add, and select Close. (Another way to make the query appear in the Select Query screen is to double-click it.) Select DealerID and ResponseInHours by double-clicking them one at a time. Select the ∑ icon from the tool bar to produce a total row in the Design view screen. The Total row lists Group By as the default option for each field. This option defines the groups that will enter calculations. In this query, to show average by each dealer, leave Group By in the Total row for DealerID. Click the Total row in the ResponseInHours column and select Avg from the pulldown menu in the field. (For online information about using Group By, on the help menu, click Microsoft Access® Borthick, Jones, and Kim—Developing Database Query Proficiency 45 Help, click the Index tab, type “query group”, select Search, and select “Perform calculations in a query” from the topic list.) Run the query to see the result thus far, which shows the average response in hours for the four dealers with referrals: DealerID AvgOfResponseInHours 0016287 10.7083333333139 0024145 61.416666666657 0037269 6.50000000005821 0405718 3.70833333328483 To see the whole heading AvgOfResponseInHours, left-click on the right border of the heading cell and drag it to the right to widen the column. Sorting the values in descending order will arrange the rows so that the longest average response time appears first, which will make the results easier to analyze. To sort the rows in descending order, return to Design view, left-click the sort row in the ResponseInHours column, and choose Descending from the pulldown menu. To make the values show two decimal digits, right-click in the ResponseInHours field, select Properties, select Format, select Fixed from the pulldown menu, and close the Field Properties window. Save the query with a meaningful name, e.g., AvgResponseInHours, by selecting the quit icon (X) in the upper right-hand corner of the Select Query screen, selecting Yes in response to the “Do you want to save changes…?” prompt, and entering the name. The query result is: Query: AvgResponseInHours DealerID AvgOfResponseInHours 0024145 61.42 0016287 10.71 0037269 6.50 0405718 3.71 From this result, based on dealers’ self-reported response times, three dealers contacted customers referred to them within 48 hours on average and one did not. The self-reported response times may not, however, be reliable. Some factors affecting reliability of the self-reported response times are (1) times may not be recorded soon enough for dealer staff to remember when they contacted customers; (2) dealers may have wanted to make their responses look better than they really were; or (3) there may be some systematic flaws in the recording process. Because the process requires manual intervention, it is not clear that the results are reliable, either to the advantage or the disadvantage of the dealers. These threats to data reliability might be manageable if there were some way to assess them, which is the objective of the next section. Query Strategy 3: Determine Response Times for Dealers’ Emailed Responses Because the timestamps on email messages were recorded automatically by the email system when the email messages were sent, their recorded times are likely to be more reliable than the dealers’ selfreported response times. If we repeat the analysis performed above for dealers’ self-reported times on the timestamps of the email messages, we can see if compliance differs depending on which response mode is used in the analysis. Here are the steps: using the ReferralToDealers and EmailResponseToReferral tables, create a query that calculates the elapsed time (response time) in hours between when the car maker sent the referral to the dealer (field RefDateTime in table ReferralToDealer) and when the dealer responded to it (field ResDateTime in table EmailResponseToReferral). Query Creation Calculate elapsed time. Select Queries from the Objects menu and double-click Create query in Design view. In the Show Table screen, make the ReferralToDealer and EmailResponseToReferral tables appear in the Select Query screen and Close the Show Table screen. A line connecting the two ReferralID fields signifies that Access joined the two tables. 46 Journal of Information Systems, Spring 2001 To create a table with email response times, select the fields DealerID, ReferralID, and RefDateTime from the ReferralToDealer table (by double-clicking them one at a time) and the ResDateTime field from the EmailResponseToReferral table. Name the query by selecting the quit icon (X) in the upper right-hand corner of the Select Query screen, select Yes in response to the “Do you want to save changes…?” prompt, enter the name of the query, e.g., EmailResponse, and select OK. To see the results thus far for the EmailResponse query, double-click it. To finish the query, switch to Design view and left-click in the blank field to the right of the ResDateTime field in the Select Query screen. Select the build icon from the tool bar to open the expression builder. To create the expression to subtract the time of the referral from the time of the dealer’s email response, double-click the ResDateTime field in the middle column of the Expression Builder screen, select the subtraction symbol in the expression builder, and double-click the RefDateTime field. The text of the expression looks like: [ResDateTime] – [RefDateTime] To make the result appear in hours, add left and right parentheses around the expression “[ResDateTime]–[RefDateTime]”, position the cursor after the right parenthesis, select the multiplication symbol, and enter the digits 24. To give the calculated field a name (other than the default one), move the cursor to the left of the expression, enter a meaningful name, e.g., EmailResponse, and follow the name with “:”. Now the expression looks like: EmailResponse: ([ResDateTime] – [RefDateTime])*24 Select OK to close the expression builder. Format the EmailResponse field for two decimal digits by right-clicking the EmailResponse field, selecting Properties, selecting Fixed, and closing the Field Properties screen. Save and run the query. The query results are: Query: EmailResponse DealerID 0024145 0016287 0405718 0024145 0016287 0405718 ReferralID 000010345 000010352 000010379 000010382 000010394 000010407 RefDateTime 11/10/99 10:00:00 AM 11/12/99 1:15:00 PM 12/11/99 11:35:00 AM 11/12/99 11:00:00 AM 11/15/99 2:20:00 PM 11/25/99 10:00:00 AM ResDateTime 11/12/99 11:30:00 AM 11/15/99 1:36:00 PM 12/11/99 5:00:00 PM 11/15/99 12:20:00 PM 11/16/99 5:00:00 PM 11/25/99 1:00:00 PM EmailResponse 49.50 72.35 5.42 73.33 26.67 3.00 Query Strategy 4: Find the Average Response Time for Dealers’ Emailed Responses Using the query just developed that determines response times for dealers’ emailed responses, calculate the average response by dealer based on email responses. Query Creation To create a query to find the average response times by dealer, select Queries in the Objects menu, double-click Create query in Design view, select the Queries tab, double-click the EmailResponse query and select Close. Select DealerID and EmailResponse by double-clicking them one at a time. Select the ∑ icon from the tool bar to insert a total row in the Design view screen. The Total row lists Group By as the default option for each field. This option defines the groups that will enter calculations. To show average by each dealer, leave Group By in the Total row for DealerID. Left-click the Total row of EmailResponse and select Avg from the pulldown menu in the field. (For online information about using Group By, on the help menu, click Microsoft Access® Help, select the Borthick, Jones, and Kim—Developing Database Query Proficiency 47 Index tab, type “query group” select Search, and select “Perform calculations in a query” from the topic list.) To sort the rows of the EmailAverage query in descending order, click the sort row in the EmailResponse column and choose Descending from the pulldown menu in the field. (For online information about sorting data, on the help menu, click Microsoft Access Help, select the Index tab, type “sort”, and select “Sort records in a table, query, or form” from the topic list.) Format the field for two decimal digits by right-clicking the AvgOfEmailResponse field, selecting Properties, selecting Fixed, and closing the Field Properties screen. Save the query with a meaningful name, e.g., EmailAverage, and run it. The query results, with a row for each dealer that responded with email, are: Query: EmailAverage DealerID 0024145 0016287 0405718 AvgOfEmailResponse 61.42 49.51 4.21 Query Strategy 5: Compare Dealers’ Self-Reported Response Times to Dealers’ Emailed Response Times How can the car maker know whether the response times are sufficiently reliable? For the selfreported times in the DealerResponseToReferral table, there is no way to know. For dealers’ emailed responses, the automatically provided email timestamps are as reliable as the email system that produced them. The email date/timestamps could be compared with the self-reported times. Some indication of the reliability of dealers’ reports on their response times can be obtained by examining the difference between ResDateTime fields in the EmailResponseToReferral and DealerResponseToReferrals tables for the referrals for which email responses were created. To compare matching self-report and email response times, create a query using the DealerResponseToReferrals and EmailResponseToReferral tables joined on ReferralID that includes DealerID and an expression for calculating the difference between self-reported and email response times. (The query should not be based on the DealerAverage and EmailAverage queries because the averages in these queries are for different sets of referrals. The query could be based on the DealerResponse and EmailResponse queries, which would require joining the queries on ReferralID by clicking on the ReferralID field in one of the queries and dragging and dropping the cursor to the ReferralID in the other query.) Query Creation Select Queries in the Objects menu and double-click Create query in Design view. On the Show Table screen, double-click the DealerResponseToReferral and EmailResponseToReferral tables to make them appear in the Select Query screen and Close the Show Table screen. Double-click ReferralID, DealerID, and ResDateTime in the DealerResponseToReferral table and ResDateTime in the EmailResponseToReferral table. Select the quit icon (X), select Yes in response to the save prompt, enter a meaningful name for the query, e.g., ResponseDifference, and select OK. Open the query in Design view and left-click in the blank field to the right of the ResDateTime field in the Select Query screen. Select the build icon to begin an expression to calculate the difference between the two response times. In the center column of the expression builder, double-click ResDateTime from the EmailResponseToReferral table. The center column is not wide enough for all of EmailResponseToReferral.ResDateTime to be visible, but the right choice can be confirmed by examining the expression after the term has been entered. Put a minus sign (–) after EmailResponseToReferral.ResDateTime and then double-click ResDateTime from the DealerResponseToReferral table. Modify the expression to convert it to hours (from days) and give the expression a meaningful name, e.g., ResponseDifference. The expression should look like: 48 Journal of Information Systems, Spring 2001 ResponseDifference: ([EmailResponseToReferral.ResDateTime] – [DealerResponseToReferral.ResDateTime])*24 Select OK to close the expression builder. Format the ResponseDifference column for two decimal places and sort it descending. Save the query before executing it. The result from the query is: Query: ResponseDifference Referral 000010352 000010394 000010379 000010407 000010382 000010345 Dealer 0016287 0016287 0405718 0405718 0024145 0024145 Dealer Response Referrals.ResDateTime 11/12/99 4:00:00 PM 11/16/99 9:00:00 AM 12/11/99 4:00:00 PM 11/25/99 1:00:00 PM 11/15/99 12:20:00 PM 11/12/99 11:30:00 AM Email Response Referral.ResDateTime 11/15/99 1:36:00 PM 11/16/99 5:00:00 PM 12/11/99 5:00:00 PM 11/25/99 1:00:00 PM 11/15/99 12:20:00 PM 11/12/99 11:30:00 AM Response Difference 69.60 8.00 1.00 0.00 0.00 0.00 To see the whole heading for a ResDateTime column, left-click on the right border of a heading cell and drag it to the right to widen the column. (Access does not permit users to manipulate column headings other than to change the width. The double-row presentation of the result table here was achieved outside Access.) Query Strategy 6: Find the Average Time Difference between Dealers’ Self-Reported Times and Email Responses To obtain the average difference in self-reported response times and email response times by dealer, find the average ResponseDifference by DealerID, sorted with the largest average first. Query Creation To create a query to find the average response differences by dealer, select Queries in the Objects menu and double-click Create query in Design view. Select the Queries tab, double-click the ResponseDifference query, and select Close. Select DealerID and ResponseDifference by doubleclicking them. Select the ∑ icon from the tool bar to produce a total row. The total row lists Group By as its default option for each field, which defines the groups that will enter calculations. In this query, to show average by each dealer, leave Group By in the Total row for DealerID. Left-click the Total row for the ResponseDifference field, and select Avg from the pulldown menu in the field. Left-click the Sort field and select Descending from the pulldown menu. (For online information about using Group By, on the help menu, click Microsoft Access® Help, click the Index tab, type “query group”, select Search, and select “Perform calculations in a query” from the topic list.) Format the field for two decimal places. Give the query a meaningful name, e.g., AverageResponseDifference, and save it. The query gives the following results: Query: AverageResponseDifference DealerID AvgOfResponseDifference 0016287 38.80 0405718 0.50 0024145 0.00 This query result indicates that of the three dealers with email responses one dealer recorded its response times very accurately, one dealer recorded its responses within a half-hour of the actual times (or made phone calls to customers before sending them email), and one dealer recorded its responses with a 39 hour discrepancy with email timestamps. One of the four dealers with referrals did not respond to customers through email. If there were more referrals and dealers in the database, there would probably be more different kinds of recording behavior to detect. Borthick, Jones, and Kim—Developing Database Query Proficiency 49 Assessment of Dealer Compliance Based on dealers’ self-reported response times, the result in the DealerAverage query indicates that one of the four dealers exceeded an average 48-hour response time. This result is consistent with customers complaining about tardy responses. Based on timestamps from emailed dealer responses, the results in the EmailAverage query indicate that two of the three dealers making email responses exceeded an average 48-hour response time. One of these dealers appeared to be responding timely according to self-reported response times. Comparing the self-reported response times with the automatically timestamped email responses (in the AverageResponseDifference query) reveals discrepancies for two dealers. For one dealer, the discrepancy is large; for the other one, small. These results illustrate the importance of being able to assess the reliability of data used for analyzing performance. Without this kind of detailed analysis, it is easy to mischaracterize performance and the behavior associated with it. As this example demonstrates, performance may look different depending on which data are analyzed, e.g., self-reported versus automatically recorded timestamps. Another data reliability issue concerns access to the queries. Because it has exclusive control over the web site, the car maker has control over the recording of queries and how they are made available to the dealers. Thus, dealers are able to view only the customer inquiries that the car maker sends them through email. Statements in the case that make this apparent are: (1) “the car maker records it [the customer inquiry] in database tables” and (2) “The car maker emails each inquiry to the dealer that is closest to the potential buyer.” Part 2 Solution: Assurance for Dealers about Car Maker Compliance A. Database Queries Over time, several dealers wondered whether they were getting all the referrals that were due them. Their doubts increased as they heard potential customers say they had been referred to dealers that were farther away from them. The queries developed in this section are designed to give dealers assurance that the car maker is allocating referrals as they agreed. Query Strategy 1: For Each Referral, Determine the Difference between the Customer’s and Dealer’s Postal Codes Make postal code fields accessible. The first step is to decide what data are required. Because the question is about whether each customer is referred to the nearest dealer (the definition of which involves customers’ and dealers’ postal codes), the appropriate tables are Dealer, ReferralToDealer and Customer. The Dealer and Customer tables contain postal codes, and the ReferralToDealer table designates the dealers to which customers were referred. The postal codes in the Customer and Dealer tables can be brought together by joining the Customer and ReferralToDealer tables on CustomerID and joining the ReferralToDealer and Dealer tables on DealerID. The absolute value of the difference between customer and dealer postal codes can be obtained after joining the three tables. Query Creation Load the database in Access. Select Queries from the Objects menu and double-click Create query in Design view. In the Show Table screen, double-click the Dealer, ReferralToDealer, and Customer tables to make them appear in the Select Query screen. Close the Show Table screen. Determine postal code differences for referrals. The differences between customers’ postal codes and the postal codes of the dealers to which customers were referred can be determined by comparing postal code values. For each referral, this means calculating the absolute value of the difference between the customer’s postal code and the postal code of the dealer receiving the referral. Specifying the absolute value in the calculation (with the “Abs” function) causes the result to be expressed as a positive number regardless of which sign (plus or minus) the result actually had. Query Creation To start the query, double-click the following fields to make them appear in the query: ReferralID from the ReferralToDealer table; CustomerID and PostCode from the Customer table; and DealerID 50 Journal of Information Systems, Spring 2001 and PostCode from the Dealer table. (The CustomerID and DealerID fields could also be selected from the ReferralToDealer table.) Save the query with a meaningful name e.g., PostDiffActual. Open the query in Design view. To finish the query, create an expression that calculates the absolute value of the difference between the customer postal code and the dealer postal code. To do this, left-click in the first blank field in the Select Query screen. Select the build icon (magic stick symbol) from the tool bar to open the Expression Builder screen. (For online information about expressions, on the help menu, Microsoft Access® Help, select the Index tab, type “expression builder”, and select Search.) In the middle column of the expression builder, double-click Customer.PostCode, select the minus sign (–), and double-click Dealer.PostCode. To make the result of the calculation an absolute value, put parentheses around the whole expression and type “Abs” in front of the left parenthesis. Give the field a name by moving the cursor to the left of “Abs” and entering “AbsDiffActual: ”. Now the expression looks like: AbsDiffActual: Abs([Customer.PostCode] – [Dealer.PostCode]) Select OK and save the query. Running the query gives the following result: Query: PostDiffActual ReferralID 000010345 000010352 000010363 000010379 000010382 000010394 000010407 CustomerID 000342512 000342525 000342539 000342546 000342512 000342525 000342546 Customer .PostCode 30344 30314 30360 30303 30344 30314 30303 DealerID 0024145 0016287 0037269 0405718 0024145 0016287 0405718 Dealer .PostCode 30342 30305 30349 30303 30342 30305 30303 Abs DiffActual 2 9 11 0 2 9 0 Query Strategy 2: For Each Referral, Determine the Minimum Postal Code Difference over All Dealers To find the minimum difference for postal code (closest dealer) for each referral, it is necessary to compare the postal code of each customer for each referral with the postal codes of all dealers. (Although minimizing the absolute value of postal code differences was the approach given in this assignment, many other allocation schemes are possible.) Comparing all pairs of postal codes requires (1) joining the ReferralToDealer and Customer tables so that customers’ postal codes are linked to referrals and (2) not joining the tables on DealerID so that the query produces every combination of rows from the joined ReferralToDealer and Customer tables and the DealerID table. This arrangement is necessary in order to put the two postal code values in the same row for each referral so that an expression can be created to calculate the difference between them. Query Creation To create the comparison table, select Queries from the Objects menu and double-click Create query in Design view. On the Show Table screen, double-click the Dealer, ReferralToDealer, and Customer tables and Close the Show Table screen. The query manager joins the three tables (Customer and ReferralToDealer on CustomerID; Dealer and ReferraltoDealer on DealerID). The default link between ReferralToDealer and Dealer tables is not, however, needed for this query. To pair every referral with every dealer in order to be able to find the difference in each pair of postal codes, highlight the link between the ReferralToDealer and Dealer tables and delete it by pressing [Delete]. (For online information about creating or deleting relationships between tables, on the help menu, click Microsoft Access® Help, click the Index tab, type “relationship”, select Search, and select “Create or modify relationships” and “Work with relationships”.) Borthick, Jones, and Kim—Developing Database Query Proficiency 51 Double-click the following fields to make them appear in the query: ReferralID from ReferralToDealer; CustomerID and PostCode from Customer; and DealerID and PostCode from Dealer. Give the query a meaningful name, e.g., AllCombinations, and save it. Run the query and examine the result to verify that every referral has been paired with every dealer. Open the query in Design view. To calculate the difference between the pairs of postcodes, leftclick in the first blank field (to the right of the PostCode field from the Dealer table). Select the build icon (magic stick symbol) from the tool bar to open the Expression Builder screen. (For online information about expressions, on the help menu, select Microsoft Access® Help, select the Index tab, type “expression builder”, and select Search). Double-click Customer.PostCode from the middle column of the Expression Builder, select the minus sign (–), and Dealer.PostCode. To express the result of the calculation as an absolute value, enclose the expression in parentheses and type “Abs” in front of the left parenthesis. Give the expression a meaningful name by typing “AbsDifference:” in front of “Abs”. The complete expression is: AbsDifference: Abs([Customer.PostCode] – [Dealer.PostCode]) Select OK. Save the query and run it. The query result is: Query: AllCombinations ReferralID 000010345 000010345 000010345 000010345 000010345 000010345 000010382 000010382 000010382 000010382 000010382 000010382 000010352 000010352 000010352 000010352 000010352 000010352 000010394 000010394 000010394 000010394 000010394 000010394 000010363 000010363 000010363 000010363 000010363 000010363 000010379 000010379 CustomerID 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342512 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342525 000342539 000342539 000342539 000342539 000342539 000342539 000342546 000342546 Customer. PostCode 30344 30344 30344 30344 30344 30344 30344 30344 30344 30344 30344 30344 30314 30314 30314 30314 30314 30314 30314 30314 30314 30314 30314 30314 30360 30360 30360 30360 30360 30360 30303 30303 DealerID 0016287 0023718 0024145 0035284 0037269 0405718 0016287 0023718 0024145 0035284 0037269 0405718 0016287 0023718 0024145 0035284 0037269 0405718 0016287 0023718 0024145 0035284 0037269 0405718 0016287 0023718 0024145 0035284 0037269 0405718 0016287 0023718 Dealer. PostCode 30305 30341 30342 30339 30349 30303 30305 30341 30342 30339 30349 30303 30305 30341 30342 30339 30349 30303 30305 30341 30342 30339 30349 30303 30305 30341 30342 30339 30349 30303 30305 30341 AbsDifference 39 3 2 5 5 41 39 3 2 5 5 41 9 27 28 25 35 11 9 27 28 25 35 11 55 19 18 21 11 57 2 38 52 Customer. PostCode 30303 30303 30303 30303 30303 30303 30303 30303 30303 30303 Journal of Information Systems, Spring 2001 Dealer. PostCode 30342 30339 30349 30303 30305 30341 30342 30339 30349 30303 ReferralID 000010379 000010379 000010379 000010379 000010407 000010407 000010407 000010407 000010407 000010407 CustomerID 000342546 000342546 000342546 000342546 000342546 000342546 000342546 000342546 000342546 000342546 DealerID 0024145 0035284 0037269 0405718 0016287 0023718 0024145 0035284 0037269 0405718 AbsDifference 39 36 46 0 2 38 39 36 46 0 Query Strategy 3: For Each Referral, Determine Whether the Referred Dealer Corresponds to the Minimum Postal Code Difference Find the minimum postal code difference for each referral. Based on the query created above, another query needs to be created to find the minimum postal code difference for each ReferralID. This minimum value identifies the “closest” dealer to the customer for each ReferralID. Query Creation To create a query to find the minimum postal code difference for each ReferralID, Select Queries from the Objects menu and double-click Create query in Design view. Select the Queries tab, double-click the AllCombinations query, and Close the Show Table screen. Double-click ReferralID and AbsDifference from the AllCombinations query to add them to the query. Select the ∑ icon from the tool bar to produce a total row. The total row lists Group By as the default option for each field. This option defines the groups for calculations. Leave Group By in the ReferralID column. Left-click in the Total row of the AbsDifference column and choose Min from the pulldown menu to show the minimum value of AbsDifference for each ReferralID. (For online information about calculations in queries, on the help menu, Microsoft Access Help, select the Index tab, type “calculate”, select Search, and select “Perform calculations in a query” in the topic list.) Save the query with a meaningful name, e.g., MinDifferences. Access supplies a default name for the expression, i.e., MinOfAbsDifferences. The result shows the minimum value of the postal code difference for each ReferralID. Query: MinDifferences ReferralID MinOfAbsDifference 000010345 2 000010352 9 000010363 11 000010379 0 000010382 2 000010394 9 000010407 0 Determine whether the referred dealer corresponds to the minimum postal code difference. A dealer is defined to be “closest” if the absolute value of the difference between its postal code and the customer’s postal code is no greater than the difference for any other dealer. Thus, comparing the MinOfAbsDifference field in the MinDifferences query with the DiffAsReferred field in the PostDiffActual query identifies referrals for which the minimum was not attained. Such referrals would not comply with the car maker and dealers’ agreement about how to allocate referrals.. Query Creation Select Queries from the Objects menu and double-click Create query in Design view. Select the Queries tab and double-click the queries PostDiffActual and MinDifferences to make them appear Borthick, Jones, and Kim—Developing Database Query Proficiency FIGURE 1 Postal Code Map 53 Legend: D = Dealer location Scale: 1.75 inches = 5 miles 30349 54 Journal of Information Systems, Spring 2001 in the Show Table screen. Close the Show Table screen. Join the queries by clicking on ReferralID in one query and dragging to ReferralID in the other query. Select the needed fields: ReferralID, CustomerID, Customer.PostCode, DealerID, Dealer.PostCode, and AbsDiffActual from the PostDiffActual query and MinOfAbsDifferences from the MinDifferences query. Save the table with a meaningful name, e.g., PostcodeDiscrepancies. Open the query in Design mode and left-click in the blank field to the right of the MinOfAbsDifference field. Select the build icon (magic stick symbol) from the tool bar to open the Expression Builder screen. In the middle column, double-click AbsDiffActual, enter a minus sign (–), and double-click MinOfAbsDifferences. Take the absolute value of the expression by enclosing the expression in parentheses and typing “Abs” to the left of the left parenthesis. Give the expression a name by typing “Discrepancy:” to the left of “Abs”. The expression is: Discrepancy: Abs([AbsDiffActual] – [MinOfAbsDifferences]) Select OK and save the query. The following result of the query shows the postal codes of the actual dealer and the closest dealer for the postal code of each customer. The Discrepancy field of the table shows whether postal codes of the referred and closest dealers are identical. The number 0 indicates that they are identical. In other words, all customers were referred to a “closest” dealer in terms of the minimum postal code difference. This analysis does not identify referrals for which there might be more than one “closest” dealer; it only determines whether customers were referred to a dealer that was as “close” as any other dealer. Query: PostcodeDiscrepancies Customer .PostCode 30344 30314 30360 30303 30344 30314 30303 Dealer .PostCode 30342 30305 30349 30303 30342 30305 30303 AbsDiff Actual 2 9 11 0 2 9 0 MinOfAbs Difference 2 9 11 0 2 9 0 ReferralID 000010345 000010352 000010363 000010379 000010382 000010394 000010407 CustomerID 000342512 000342525 000342539 000342546 000342512 000342525 000342546 DealerID 0024145 0016287 0037269 0405718 0024145 0016287 0405718 Discrepancy 0 0 0 0 0 0 0 Assessment of Car Maker Compliance The zero values in the Discrepancy column mean that the car maker referred all customers to dealers that were as “close” to the customers as any other dealers. Thus, the car maker complied with the agreement it had with the dealers to refer customers to the “closest” dealer where closest is defined as the dealer such that the absolute value of the difference between the dealer’s postal code and the customer’s postal code is minimized. So why would customers complain? Figure 1 illustrates why: postal codes that are adjacent to each other on the map may have larger absolute numerical differences than postal codes that are not adjacent to each other on the map. For example, the referral scheme refers a customer in postal code 30360 to a dealer in postal code 30349, which minimizes the postal code difference between the customer and the dealer, but dealers in postal codes 30305 and 30342 are physically closer to the customer. The reason that customers complain is not because the car maker did not implement the agreement correctly but because the design of the system, i.e., using this approach to allocating referrals, is flawed. This design flaw is relatively easy to detect with just a little thought about the process and what following it implies for customers. The nature of the flaw is apparent from the postal code map. The lesson in this example is not that postal code differences are a poor basis for allocating referrals but that information systems may have design flaws. Even systems created by the most well-intentioned developers exhibit flaws. Systems that begin life with no flaws may exhibit some as the world whose realities they represent changes and people use them in unanticipated ways. Some of the flaws are easy to detect with a little thought, but others are hard to detect even with a great deal of thought. To make Borthick, Jones, and Kim—Developing Database Query Proficiency 55 matters worse, developers often introduce new flaws during attempts to correct existing ones. Furthermore, it may be difficult to distinguish between system deficiencies due to lack of compliance with system procedures (e.g., car dealers not complying with the agreement to contact customers within 48 hours) and system deficiencies that are a function of a flawed design (e.g., allocating referrals on a minimize postal code difference basis). B. New Design for “Closest” Dealer Criterion Although it has the advantage of being easy to implement because all the required data (postal codes) are available in the tables, the “minimize postal code difference” approach to referring customers to dealers is sure to lead to unhappy customers because the allocation to dealers is unrelated to customers’ physical proximity to dealers. While appealing because the data are available in the tables, allocating referrals based on telephone number area codes or prefixes would suffer from the same deficiency because area codes and prefixes no longer correspond to compact locales in high-growth areas. A better design for the “closest” dealer criterion might be to minimize the physical distance between the customer and the referred dealer but to do so in a way that minimizes the processing effort. For example, if a geographic database were available, the computer program that refers customers could look up customers’ and dealers’ addresses in units that could be manipulated to give physical distances expressed in miles or driving time between the customer’s and dealer’s postal codes. Part 3 Solution: Objective Questions A. Solution: 3. When tables have identical primary keys, the query manager joins them automatically on their primary keys. B. Solution: 1. Queries do not inherit the primary key designations from their source tables so the user has to join the tables by connecting the attributes to be joined. C. Solution: 4. Customers are likely to think of response time as beginning when they inquired rather than when the car maker sent the referrals to dealers, which eliminates b and c. Web-savvy customers are more likely to be interested in the elapsed time to the dealer’s email responses, which eliminates a. D. Solution: 1. The term “[DealerResponseToReferral]!” is necessary in order to distinguish which ResDateTime attribute is being referred to in the calculation. E. Solution: 3. Because the CustomerInquiry table has no primary key and the Dealer table and the ReferralToDealer table joined themselves on DealerID, the query manager matches every row in the CustomerInquiry table with every row in the join of Dealer and ReferralToDealer (which has a row for every row in the ReferralToDealer table), the result has the number of rows equal to the number of rows in the CustomerInquiry table times the number of rows in the ReferralToDealer table. F. Solution: 2. The Customer table and the ReferralToDealer table joined themselves on CustomerID, and the Dealer table and the ReferralToDealer table joined themselves on DealerID. This gives one row in the results for each row in the ReferralToDealer table provided the Customer and Dealer tables contained rows corresponding to the customers and dealers appearing in the ReferralToDealer table. G. Solution: 3. As dealer locations and postal code areas change, changes in the lookup table would be required, which eliminates choice 1. In high-growth areas, telephone prefixes (xxx in yyy xxxdddd) do not represent compact areas, and they may not be unique in areas with multiple telephone area codes, which eliminates choice 2. Choice 3 remedies the defect of the postal code scheme and is least likely to require future modifications. All dealers except the one with the most sales are sure to insist that they receive more referrals, which would necessitate changes, which eliminates choice 4. H. Solution: 1. Choice 1 would be the most attractive one because it would help customers find the dealer that has the specific configuration the customer wants in stock. Dealers are likely to resist choice 2, even though the information is already available on the web, because they prefer to negotiate by starting from the higher sticker price, which eliminates choice 2. Dealers would resist choice 3 56 Journal of Information Systems, Spring 2001 I. J. because it would likely decrease their sales, which eliminates choice 3. In the short run, many states have laws prohibiting car makers from selling vehicles directly. Customers can get the information in choice 4 from web sites independent of the car maker, which customers might deem to be more reliable, which eliminates choice 4. Solution: 4. Primary keys must uniquely identify the row in the table. Choice 1 does not ensure uniqueness because there may be more than one inquiry per customer. Choice 2 does not ensure uniqueness because there may be more than one inquiry with the same timestamp across many inquiries from many persons. Choice 3 does not ensure uniqueness because someone may inquire about more than one configuration per model. Choice 4 is the one most likely to be unique. Although someone may inquire about a model in more than one configuration, he or she is unlikely to be able to do so at exactly the same time when the timestamp includes seconds. Solution: 3. In choice 1, subtracting an absolute value from an absolute value may give a result with a minus sign. In choice 2, the expression will give a result with a minus sign when Dealer.PostCode is greater than Customer.PostCode. Choice 3 is equivalent to the calculated expression because rearranging the order of the subtraction within the absolute value function does not change the result. In choice 4, the expression will give a result with a minus sign when Customer.PostCode is greater than Dealer.Postcode. III. SUMMARY This case illustrates database querying as a way to provide continuous assurance. It develops database queries for giving a car maker assurance that dealers respond to web site customers on a timely basis and for giving car dealers assurance that the car maker sends them all the appropriate customer referrals. The queries are developed in Microsoft Access, but any database system supporting QBE or SQL could be used. The execution of the queries could be automated so that they run continuously (or on a periodic basis), and referrals not in compliance with the agreements might be referred automatically to car maker and dealer managements. As trading partners evolve their ways of relating to each other to take advantage of new communication capabilities (especially web-based features) and to respond to new entrants (especially web-based ones), this approach to continuous assurance is likely to become more common. Accountants should be able to satisfy this need by developing continuous assurance applications based on database queries. REFERENCES Albrecht, W. S., and R. J. Sack. 2000. Accounting Education: Charting the Course through a Perilous Future. Accounting Education Series, Volume 16. Sarasota, FL: AAA. Available at: http://aaahq.org/pubs/AESv16/ toc.htm. Borthick, A. F. 1992. Helping users get the information they want, when they want it, in the form they want it: Integrating the choice and use of information. Journal of Information Systems 6 (2): v–ix. ———. 1996. Helping accountants learn to get the information managers want: The role of the accounting information systems course. Journal of Information Systems 10 (2): 75–85. Harris, E. 1999. Web car-shopping puts buyers in driver’s seat. Wall Street Journal (April 15): B10. Perry, J. T., and G. P. Schneider. 2001. Building Accounting Systems Using Access2000. Cincinnati, OH: SouthWestern. Rich, M., and K. Lundegaard. 1999. Car dealers fight forays by makers. Wall Street Journal (April 28): S1. Wallace, B. 1998. Car dealers yield sales to Internet. Computerworld (August 31): 1, 17. Available at: http:// www.computerworld.com/cwi/story/0,1199,NAV47_STO43338,00.html. ———. 1999a. Car-buying site could help put GM on the road again. Computerworld (March 8): 43. Available at: http://www.computerworld.com/cwi/story/0,1199,NAV47_STO34814,00.html. ———. 1999b. Carmakers resist selling direct over the Internet. Computerworld (May 31): 6. Available at: http:// www.computerworld.com/cwi/story/0,1199,NAV47_STO35857,00.html. Waltner, C. 2000. Auto dealers come to terms with the Web. Informationweek (April 24): 104–110. Available at: http://www.informationweek.com/783/dealer.htm. Warner, F. 1999. Auto makers and dealers wage battle. Wall Street Journal (June 16): A2. Wilson, T. 2000. Auto dealers take web offensive. Internetweek (February 7): 1, 51. Available at: http:// www.internetwk.com/lead/lead020300.htm.