Accounting Information Systems
CHAPTER 19
SPECIAL TOPICS IN REA MODELING
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
19.1 Often it takes several sales calls to obtain the first order from a new customer. Why
then does Figure 19-1 depict the relationship between the Call on Customer and
Take Customer Order events as being 1:1?
When a sales person visits a customer it is represented by the event Call on Customer.
Although single sales call may be followed by many orders from a customer over time, it
event is also 0.
19.2 How could an automobile dealer model the use of loaner cars, which it gives to
customers for free whenever they drop off a vehicle for maintenance that will take
longer than one day to complete?
The loaner car arrangement could be handled the same as rental car arrangement, except
19.3 In what situations would you expect to model a relationship between an agent and a
resource?
Relationships between agents and resources can be modeled for two reasons.
Ch. 19: Special Topics in REA Modeling
19-2
19.4 Why is depreciation not represented as an event in the REA data model?
Depreciation is not modeled as an event in the REA diagram because it is an accounting
19.5 How would you model the acquisition of a digital asset, such as the purchase of
software online (the software is downloaded and then installed on the purchaser’s
computer)?
There is very little difference in modeling an event where a physical resource is sold as
opposed to a digital resource. The digital asset must still be ordered, received, recorded,
19.6 How are the similarities and differences between the purchase of services, such as
telephone service, and the purchase of raw materials reflected in an REA data
model?
In terms of the REA model, the two types of transactions are handled much the same. In
both cases, the acquisition (receive) event would be linked to a Disburse Cash event and
Accounting Information Systems
19-3
19.7 How would you modify the expenditure cycle REA diagram in Figure 19-4 to
include the return of defective products to suppliers for credit?
Since the supplier, inventory, and employee entities already exist on the diagram, return
of defective good to the supplier would only need one additional event entity on the
diagram. An event labeled something like “Return Defective Goods” would be added. It
Ch. 19: Special Topics in REA Modeling
SUGGESTED ANSWERS TO PROBLEMS
19.1 We-Fix-Computers, Inc. provides spare parts and service for a wide variety of
computers. Customers may purchase parts to take home for do-it-yourself repairs, or they
may bring their systems in for repair, in which case they pay for both the parts and the
labor associated with the type of service required. Some services do not include any new
parts, just a labor charge for that service. Individual customers must pay for all parts
We-Fix-Computers, Inc. purchases its inventory of parts from more than a dozen different
vendors. Orders are usually delivered the next day; sometimes, however, suppliers ship
only partial orders. We-Fix-Computers pays for some of its purchases COD, but usually
pays by the 10th of the month for all purchases made the prior month. None of its suppliers
allows it to make installment payments.
Required
Draw an integrated REA diagram for We-Fix-Computers’ revenue and expenditure cycles.
Employees
Order Inventory
Inventory
Sales
Employee
Accounting Information Systems
19-5
19.2 The Mesa Veterinary Hospital is run by Dr. Brigitte Roosevelt. She has two
employees in the office and has asked you to develop a database to help better track her
data. Dr. Roosevelt currently uses her personal computer only for word processing, but she
is interested in also using it to maintain pet histories and accounting information. She is
excited about the transition and is counting on you to help her through the process. She
describes her daily activities as follows:
When new customers come to Mesa Veterinary Hospital, the “owners” of the pets are required
to complete an introductory form. This form includes the following:
• Owner name
They are also required to provide the following information about each pet, as some people
own many pets:
• Pet name
Dr. Roosevelt would like to enter this information once and then have the system retrieve it for
all subsequent visits.
Owners are charged $25 for each appointment and must pay additionally for any medications
prescribed for their pets. Dr. Roosevelt requires all pets to be brought back for another
examination prior to refilling any prescriptions. Customers must pay for services and
medication in full at the conclusion of their visits.
Ch. 19: Special Topics in REA Modeling
19-6
The prices charged by suppliers for a given product may change several times during the
year, so it is important to accurately store the cost of each item each time it is purchased.
Dr. Roosevelt concludes the interview by requesting that in addition to the facts mentioned,
she wants the system to store the following attributes:
• Number of pets owned by each customer
• Total charge for the appointment
Accounting Information Systems
19-7
REQUIRED
a. Given this brief overview, draw an integrated REA diagram for the Mesa Veterinary
Hospital and include cardinalities.
Drugs
Make Appointment
Employee
Employee
Employees
Order Drugs
Ch. 19: Special Topics in REA Modeling
19-8
b. As directed by your instructor, either draw the tables necessary to implement the
integrated REA diagram you developed for the Mesa Veterinary Hospital or build the
tables in a relational DBMS to which you have access. Be sure to include all attributes from
the narrative plus the additional ones explicitly listed by Dr. Roosevelt at the conclusion of
the interview. Create additional attributes only if necessary.
Table
Primary Key
Other Attributes (foreign keys in italics)
Services
Service number
Name, standard cost, list price, standard time to
perform
Drugs
Drug number
Drug name, standard cost, list price, beginning
quantity-on-hand
Cash
GLAccount number
Bank name, type of account, beginning balance
scheduled time, diagnosis, total charge,
receipt_number, weight, appointment number
Receive Cash
Receipt number
Date, amount, customer number, employee
number, GLAccount number
Pets
Pet number
Pet name, breed, color, birth date, customer
number
Customers
Customer number
Customer name, address, day phone, night phone,
number of pets owned
Employees
Employee number
Name, date hired, salary
Exam
Appointment number
Exam number
Drugs-Exams
Drug number
Exam number
Dosage, actual cost, actual price
Vendor
Vendor number
Name, address, account balance
Order Drugs
Purchase order
number
Receive Drugs
Receiving report
number
Vendor number, employee number, purchase order
number, date, vendor invoice
Disburse Cash
Check number
Vendor number, employee number, GL Account
number, amount, description, date
Order Drugs-
Drugs
Purchase order
number, Drug number
Quantity, unit cost
Receive Drugs-
Drugs
Receiving report
number, Drug number
Quantity received, condition
Receive Drugs-
Disburse Cash
Receiving report,
Check Number
Amount applied to invoice
Accounting Information Systems
19-9
19.3 Your university hires you to implement a database system for the library network.
You have interviewed several librarians, and the following summarizes these discussions:
• The library’s main goal is to provide students and professors with access to books and
other publications. The library, therefore, maintains an extensive collection of materials
that are available to anyone with a valid university identification card.
• The standard procedure for lending materials is that the student or faculty member
comes to one of the three campus libraries and locates the book or journal on the shelves.
• When students or faculty check out books, the system must be able to track the specific
copy that is being borrowed. Each book has a magnetic strip inserted in its spine, which is
used as a security measure. If someone tries to take a book without checking it out, an
alarm sounds.
• After borrowers check out a book, they are expected to return it by its due date. In
reality, everyone is allowed 30 days after the due date recorded on the checkout slip before
the book is officially overdue. At that point, the book must be returned, and the borrower
is assessed a $10 fine. If the book is permanently lost, then the borrower is fined $75 for the
book’s replacement. All fines must be paid in cash, in full. Students are not allowed to
enroll for subsequent semesters until all library fines are paid; they also do not receive a
Ch. 19: Special Topics in REA Modeling
19-10
diploma until all library fines are paid. Faculty must pay all outstanding fines by June 30
of each year.
• When a book is returned, the return must be entered into the system, and a unique return
number is used to log the transaction. At that time, the loan record is updated to show that
the book has been returned.
The following attributes have been identified as critical for the new system:
• University book ID
• Book publisher
• Due date
• Librarian college degree
• Actual return date
• Borrower ID
• Borrower name
• Book title
• Fine receipt number
• Amount received
• Dewey decimal number
• Borrower address
• Book copyright date
• Borrower e-mail address
Accounting Information Systems
19-11
REQUIRED
a. Draw an REA diagram for the library system. Remember to include cardinalities.
a. REA diagram solution.
Ch. 19: Special Topics in REA Modeling
19-12
Explanation of cardinalities:
1. One key to the problem is understanding that the Loan Book event represents the
checking out of a single book. As stated in the problem, if a borrower checks out 5 books,
the system adds five rows to the Loan Book table. This does not affect the borrower’s
checkout experience at all.
efficient to create a separate entity called book title, to store this constant information.
4. The cardinality from Receive Fines to Loan Book is (1,N) because a loan has to occur
prior to a fine being paid, but one cash receipt may pay for fines associated with a
Accounting Information Systems
19-13
b. As directed by your instructor, either create the tables on paper that would be
required to implement your REA diagram or actually build those tables in a
relational DBMS to which you have access. Only use the attributes listed, unless
others are absolutely necessary.
Problem 19-3, part b. Table solution
Table
Primary Key
Other Attributes (foreign keys in
italics)
Library
Library name *
Number of books
Books
University Book ID
Book status, ISBN#, default library
shelved at
Author
Author number
Name
Book Title-
Author
Author number
ISBN#
Loan Book
Loan number
Due date, University Book ID, Borrower
ID, loan status, library borrowed from,
librarian #, date checked out
Book Return
Book return number
librarian #
Cash
Cash account number
Beginning account balance
Receive Fine
Fine receipt#
Amount received, cash account#, library
name, librarian #, borrower ID
Employee
Librarian#
processed
Borrower
Borrower ID
Name, address, email, SSN, fine balance
owed, phone number
Fine-Loan
Fine receipt#
Loan number
Amount of fine
* Library name can be the primary key because it is created by the library system and, therefore,
guaranteed to be unique for each library.
Ch. 19: Special Topics in REA Modeling
19-14
19.4 The XYZ Company sells tools and parts to automotive repair shops. Shops call in
orders; all orders received by noon are delivered the same day. Between 12:00 and 1:00,
the system prints out schedules. From 1:00 to 5:00, drivers make deliveries according to the
printed schedules. Typically, each driver makes between 25 and 30 deliveries each day.
REQUIRED
a. Draw a partial REA diagram of the XYZ Company’s revenue cycle to model these
events: Taking Customer Orders, Deliveries, and the Use of Vehicles. Be sure to
include cardinalities.
Accounting Information Systems
b. Create a set of tables (either on paper or in a relational DBMS to which you have
access) to implement the REA model you developed for the XYZ Company.
Table Name
Primary Key
Other Fields(foreign keys in italics,
others in normal font)
Take Order
Order Number
Customer Number, Employee Number,
Amount
Delivery
Delivery Number
Truck Number, Employee Number,
Inventory
Item Number
Description, Quantity, List Price, Unit
Cost, Beginning Quantity On Hand,
Reorder Quantity, Reorder Point
Truck
Truck Number
Type, Description
Employee
Employee Number
Name, Address, Position, Pay Rate
Customer
Customer Number
Name, Address, Phone, Beginning
Account Balance, Credit Limit
Take Order-
Inventory
Order Number, Item Number
Quantity
Delivery-Inventory
Delivery Number, Item Number
Quantity
19.5 Assume that Stained Glass Artistry, a new shop that specializes in making stained
glass artwork, has hired you to design an integrated database that will provide the owners
with the accounting information they need to effectively manage the business. Stained
Glass Artistry makes a wide variety of stained glass windows for sale in its store.
A unique job order is assigned to each production run, which includes creating multiple
copies of the same basic design. When raw materials are issued to employees, the issuance
is documented on a prenumbered raw material issue form. The different kinds of glass
needed for the product, and other materials such as copper foil or lead, are issued at one
time, so that employees can efficiently produce the design.