Accounting Information Systems
CHAPTER 17
DATA MODELING AND DATABASE DESIGN
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
17.1 Why is it not necessary to model activities such as entering information about
customers or suppliers, mailing invoices to customers, and recording invoices
received from suppliers as events in an REA diagram?
The REA data model is used to develop databases that can meet both transaction
processing and management analysis needs. Only events that either (1) directly change
the quantities of resources, (2) represent commitments to future exchange events, or (3)
that provide new information about activities that management wants to plan, evaluate,
and control need to be included in such databases. None of the activities listed in the
question satisfy these requirements.
Data processing activities, such as preparing reports or transcribing data from a form, are
not explicitly modeled because they do not change information about any resources nor
are they fundamental activities which management wants to control. (Consider: How
often are managers concerned about how many reports a given employee prints in one
day?) Indeed, all the information contained in a vendor invoice is already in the
17-2
17.2 The basic REA template includes links between two events and links between events
and resources and between events and agents. Why do you think the basic REA
template does not include direct links between (a) two resources, (b) two agents, or
(c) between resources and agents?
a. The basic REA template was developed to assist in modeling an organization’s
economic transactions and, therefore, centers on events, the resources they affect, and
the agents who participate in them. Two events can be linked to reflect economic
duality (the give-to-get relationship) or causal sequence (orders precede sales).
b. Similarly, the basic REA template does not directly link agents to one another
because they often do not influence one another. As with resources, however, we will
see in chapter 19 that it is sometimes desirable to directly model links between
agents. One reason would be to represent supervisory relationships; another would be
to reflect the assignment of employees to service specific subsets of customers or
suppliers.
Accounting Information Systems
17.3 How can REA diagrams help an auditor understand a client’s business processes?
An REA diagram presents a model of the company’s database. Included in the model is a
representation of all the tables contained in the database as well as all of the relationships
that exist between the tables within the database. These tables and relationships reflect
17.4 Which parts of Figure 17-6 would accurately depict almost every organization’s
revenue cycle? Which parts would change?
The 1:N relationships between the events and customers depicted in Figure 17-6 would
apply to every organization, because it is always necessary to associate an order, sale, and
Ch. 17: Database Design using the REA Data Model
17-4
17.5 What is the relationship between the things that would be represented as resources
in an REA diagram and the different categories of assets found on an organization’s
balance sheet? (Hint: Are there any assets that would not be modeled as resources?
Are there any resources in an REA diagram that are not listed as assets on a balance
sheet?)
There are asset categories listed on a company balance sheet that would not be presented
as a resource on an REA diagram. The most noticeable is Accounts Receivable.
Accounts Receivable is merely the difference between amount that a company has sold to
17.6 How would accounts payable be reflected in an REA diagram? Why?
Accounts payable is not represented on an REA diagram. Accounts payable represents
purchases for which the organization has not yet paid the supplier. Thus, at any point in
Accounting Information Systems
17-5
17.7 What are the five stages of the database design process? In which stages should
accountants participate? Why?
The five stages of database design are: systems analysis, conceptual design, physical
design, implementation and conversion, and operation and maintenance. Accountants can
and should participate in every stage of the database design process, but their level of
17.8 What is the difference between an Entity-Relationship (E-R) diagram and an REA
diagram?
An entity-relationship (E-R) diagram is a graphical representation of a database that
depicts the entities of interest and the important relationships among those entities. The
entities are represented as rectangles; the relationships are represented as lines that
connect entities.
Ch. 17: Database Design using the REA Data Model
SUGGESTED ANSWERS TO THE PROBLEMS
17.1 Joe’s is a small ice–cream shop located near the local university’s baseball field.
Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream.
Customers can buy cones, sundaes, or shakes. When a customer pays for an
individual purchase, a sales transaction usually includes just one item. When a
customer pays for a family or group purchase, however, a single sales transaction
includes many different items. All sales must be paid for at the time the ice cream is
served. Joe’s maintains several banking accounts but deposits all sales receipts into
its main checking account.
Draw an REA Diagram, complete with cardinalities, for Joe’s revenue cycle,.
Accounting Information Systems
17-7
17.2 Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over
the years, he has developed good relationships with both vendors so that they allow
Joe to pay them biweekly for all purchases made during the preceding two-week
period. Joe calls in ice-cream orders on Mondays and Thursdays. The orders are
delivered the next day. Joe buys ice-cream toppings from one of several local stores
and pays for each such purchase at the time of sale with a check from the company’s
main checking account.
Draw an REA Diagram, complete with cardinalities, for Joe’s expenditure cycle.
VendorReceive Inventory
Ch. 17: Database Design using the REA Data Model
17.3 Sue’s Gallery sells original paintings by local artists. All sales occur in the store.
Sometimes customers purchase more than one painting. Individual customers must
Draw an REA Diagram for the gallery’s revenue cycle. Be sure to include
cardinalities.
Inventory Sales Employee
Accounting Information Systems
17-9
17.4 Sue’s Gallery only purchases finished paintings (it never commissions artists). It
pays each artist 50% of the agreed price at the time of purchase, and the remainder
after the painting is sold. All purchases are paid by check from Sue’s main checking
account.
Draw an REA Diagram, complete with cardinalities, of the gallery’s expenditure
cycle.
Employee
Disburse
Cash
Cash
Ch. 17: Database Design using the REA Data Model
17.5 Develop a data model of Fred’s Train Shop’s expenditure cycle activities related to
the acquisition of office equipment and other fixed assets. Fred sometimes orders
multiple pieces of equipment. Vendors usually ship the entire order, but sometimes
are out of stock of some items. In such cases, they immediately ship to Fred what
Draw an REA Diagram of your data model. Be sure to include cardinalities.
Office Equipment Order Office
Equipment
Employee
(Purchasing Clerk)
Supplier
Accounting Information Systems
17-11
17.6 Provide an example (in terms of companies with which you are familiar) for each of
the business situations described by the following relationship cardinalities:
a.
A company may receive multiple cash payments on a single sale or a company may
b.
c.
In this scenario, some inventory purchases can be paid for with multiple payments,
Ch. 17: Database Design using the REA Data Model
17-12
d.
In this scenario, inventory purchases are to be paid for with a single payment. For
e.
In this scenario, a single purchase of inventory is paid for with multiple payments.
f.
In this scenario, each sale must be preceded by one and only one order. The fact that
both the order and sales events are recorded implies that there is probably a time lag
g.