Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Page 1 of 13
CHAPTER 17
DATABASE DESIGN USING THE REA DATA MODEL
Instructor’s Manual
Learning Objectives:
1. Discuss the steps for designing and implementing a database
system.
2. Use the REA data model to design an AIS database.
Questions to be addressed in this chapter include:
1. What steps are followed to design and implement a database
system?
2. How is the REA data model used to design an AIS database?
Introduction
This chapter introduces the topic of data modeling, one aspect of
database design that accountants should understand.
Database Design Process
Figure 17-1 on page 494 shows the five basic steps in database design.
Page 2 of 13
2. Conceptual Design
3. Physical Design
The first stage (systems analysis) consists of initial planning to
determine the need for and feasibility of developing a new system.
The second stage (conceptual design) includes developing the different
schemas for the new system, at the conceptual, external, and internal
levels.
The fifth and final stage is using and maintaining the new system.
Accountants can and should participate in every stage of the database
design process.
Accountants may provide the greatest value to their organizations by
taking responsibility for data modeling.
Entity-Relationship Diagrams
An entity-relationship (E-R) diagram is a graphical technique for
portraying a database schema.
An entity is anything about which the organization wants to collect and
store information.
In an E-R diagram, entities are depicted as rectangles.
Figure 17-2 on page 495 shows various E-R diagrams
Some data modelers use diamonds to depict relationships (panel
A), whereas others do not (panel B).
Page 3 of 13
E-R diagrams can be used to represent the contents of any kind of
database.
Multiple Choice 1
Which of the following statement(s) is true?
a. An entity is anything about which the organization wants to
collect and store information.
The REA Data Model
The REA data model was developed for use in designing AIS. The REA data
model focuses on the business semantics underlying an organization’s
value chain activities.
Three Basic Types of Entities
The REA data model is so named as it has three distinct categories:
1. R—resources the organization acquires and uses
Resources are those things that have economic value to the
organization.
Learning Objective Two
Use the REA data model to design an AIS database.
Page 4 of 13
There are two event entities in Figure 17-3 on page 496; Sales
and Receive Cash.
Agents are the people and organizations that participate in the events.
Figure 17-3 includes two types of agent entities: Employees and
Customers.
Structuring Relationships: The Basic REA Template
The essential features of the basic pattern are:
1. Each event is linked to at least one resource that it affects.
Rule 1—Every Event Entity Must Be Linked to at Least One Resource
Entity
Events must be linked to at least one resource that they affect.
The “Get Resource A” in Figure 17-4 on page 497 increases the quantity
of a resource.
For example, “Get” includes the receipt of goods from a supplier
which increases the amount of inventory, and the receipt of
payment from a customer increases the amount of cash.
Not every event changes the quantity of a resource. For example,
a customer’s commitment to purchase merchandise will not alter
the quantity of a resource until the sale is actually made. The
commitment of a company to purchase merchandise from a vendor
will not change the quantity of a resource until the actual
purchase is made and the items are received.
Page 5 of 13
Figure 17-5 on page 498 shows that each accounting cycle can be
described in terms of such give-to-get economic duality
relationships.
Rule 3—Every Event Entity Must Be Linked to at Least Two
Participating Agents
For accountability, organizations need to be able to track the
actions of employees.
Multiple Choice 2
In regards to REA data modeling, some researchers have proposed a
fourth type of entity, which they call _____.
a. accounts
b. locations
c. data
d. special agents
Multiple Choice 3
The essential features of relationship patterns include:
a. Each event is linked to at least two participating agents.
Multiple Choice 4
In order for a manufacturer to Get inventory, they must Give:
a. cash
Learning Objective Three
Draw an REA diagram of an AIS database.
Page 6 of 13
Developing an REA Diagram
Figure 17-6 on page 499 shows the REA diagram Paul developed for
the revenue cycle of Fred’s Train Shop.
Developing an REA diagram for a specific transaction cycle
consists of the following three steps:
1. Identify the events about which management wants to
collect information.
Step 1: Identify Relevant Events
The first step in developing an REA model of a transaction cycle is to
identify the events of interest to management.
From Chapter 10, the revenue cycle typically consists of four
sequential activities:
2. Fill customer orders.
The first activity of taking the customer’s order does not involve the
acquisition of resources (get) or provision of resources to an external
party (give).
Page 7 of 13
In Figure 17-6 on page 499, it shows that the basic business activities
in the revenue cycle indicate that the basic give-to-get economic
exchange consists of two events: 1) fill customer orders (referred to
as the Sales event) and collect payments from customers (referred to as
the Receive Cash event).
After identifying the economic exchange events, it is necessary to
determine which other business activities should be represented as
events in the REA model.
Paul notes that the Sales and Receive Cash reflects most in-store sales
transactions in which the customer selects the items to buy and then
pays for them.
However, there are some customers that call the store and ask Fred to
put items aside in which they will pick them up later during the week.
So now Paul needs to add the commitment event Take Customer Order to
the REA diagram before the Sales event.
What about accounts receivable? How can you possibly monitor the
accounts receivable balance sheet item? What information would the
billing event add that you don’t already have? Answer: “Nothing.” You
already have the information from the Sales event. Accounts receivable
equals all sales in which customers have not yet paid. So again, Paul
does not have to include the billing event.
Page 8 of 13
Thus, what gets modeled in the REA diagram is the business event (e.g.,
the sales transaction) and the facts that management wants to collect
about that event, not the entry of that data.
Step 2: Identify Resources and Agents
Next, the resources that are affected by those events need to be
identified. This involves answering three questions:
1. What economic resource is reduced by the “Give” event?
Again, a solid understanding of business processes makes it easy to
answer these questions.
To continue with our example, Paul observed that the Sales event
involves giving inventory to customers.
Finally, the Take Customer Order event involves the setting aside of
merchandise for customers.
In addition to specifying the resources affected by each event, it is
also necessary to identify the agents who participate in those events.
There will always be at least one internal agent (employee) and, in
most cases, an external agent (customer or vendor) who participate in
each event.
Step 3: Determine Cardinalities of Relationships
The final step in drawing an REA diagram for one transaction cycle is
to add information about relationship cardinalities.
No universal standard exists for presenting information about
cardinalities in REA diagrams. The text adopts the graphical “crow’s
feet” notation style for representing cardinality information.
Table 17-1 on page 502 explains the meaning of the symbols used to
represent cardinality information.
Minimum cardinality indicates whether a specific instance of the
entity next to the cardinality must be linked to at least one
instance of the entity on the opposite side of that relationship.
A minimum cardinality of zero means that an instance of the
entity on this side of the relationship need not be linked to any
specific instances of the other event.
For example, in Figure 17-7 the minimum cardinality of 0
A minimum cardinality of one means that each instance of that
entity must be linked to at least one instance of the other
entity participating in that relationship.
For example, in Figure 17-7 the minimum cardinality of 1
The maximum cardinality indicates whether one instance of that
entity can be linked to more than one instance of the other
entity participating in that relationship.
In Figure 17-7 the maximum cardinality next to Customer
Three Types of Relationships
Figure 17-7 portrays these three types of relationships:
1. A one-to-one (1:1) relationship exists when the maximum
cardinality for each entity in the relationship is 1.
Panel A: one-to-one (1:1) relationship
2. A one-to-many (1:N) relationship exists when the maximum
cardinality of one entity in the relationship is 1 and the
Page 10 of 13
maximum cardinality for the other entity in that relationship
is many.
Panel B: one-to-many (1:N) relationship
3. A many-to-many (M:N) relationship exists when the maximum
cardinality for both entities in the relationship is many.
Panel D: many-to-many (M:N) relationship
Figure 17-7 shows that any of these possibilities might describe the
relationship between the Sales and Receive Cash events.
If management is interested in tracking the frequency of different
payment methods, this fact might be recorded as an attribute of the
Sales event.
Panel B and C of Figure 17-7 depicts two ways that one-to-many (1:N)
relationships can occur.
Panel B indicates that the organization has a business policy
that allows customers to make installment payments to the selling
organization. However, this does not mean that every sales
transaction is paid for in installments.
Panel D of Figure 17-7 depicts a many-to-many relationship between the
Sale and Cash Receipt events. This type represents an organization that
has business policies that allow customers to make installment payments
Multiple Choice 5
The second step in developing an REA diagram is:
a. Determine the cardinalities of each relationship.
b. Identify the events about which management wants to collect
information.
Page 11 of 13
c. Identify the resources affected by each event and the agents who
participate in those events.
d. None of the above.
Multiple Choice 6
Which of the activities in a revenue cycle does not involve a Get or
Give event?
a. Bill customers.
Multiple Choice 7
Three types of relationships between entities include:
a. 1:M
b. N:M
c. 1:N
d. 0:1
Business Meaning of Cardinalities
The information that reflects facts about the organization and its
business practices is obtained during the systems analysis and
conceptual design stages of the database design process.
Let’s now examine Figure 17-6 to see what it reveals about Fred’s Train
Shop.
First, note that all of the agent-event relationships are 1:N. A
particular agent often participates in many events.
Figure 17-6 shows that the minimum cardinality on the event side
of the agent-event relationship is 0. The organization may wish
to store information about potential customers and alternate
suppliers.
Page 12 of 13
Most organizations track such inventory by an identifier such as
part number, item number, or stock-keeping unit (SKU) number and
do not attempt to track each physical instance of that product.
What if an organization sells unique, one-of-a-kind inventory, such as
original artwork? Such items can only be sold one time; consequently,
the maximum cardinality on the event side of the inventory-sales event
would be 1.
Now consider the relationship between the cash resource and the Receive
Cash event. Each cash receipt from a customer is deposited into one
cash account. Then the treasurer transfers money from that account to
other cash accounts. Each customer payment must be deposited into some
account; hence the minimum cardinality is 1 on the resource side of the
relationship. However, the minimum cardinality on the event side of the
relationship is 0.
Fred’s Train Shop extends credit to its business customers and mails
them monthly statements. The business customers send Fred one check to
cover all their purchases during a given time period. Therefore, one
Cash Receipts event could be linked to many different Sale events.
Uniqueness of REA Diagrams
Because each organization will have its own unique REA diagram, so will
relationship cardinalities differ. An REA diagram will need to be
Page 13 of 13
FOCUS 17-2 on page 578 highlights the importance of involving the
eventual users of the system in the data modeling process so that
terminology is consistent.
Multiple Choice 8
The information about the choice of cardinalities is obtained during
the __________ stage of the database design process.
a. physical design
Multiple Choice 9
Most organizations track an inventory by an identifier such as:
a. Part number
Multiple Choice 10
Because Fred’s Train Shop extends credit to its business customers in
Figure 17-6, the minimum cardinality on the Receive Cash side of the
relationship would be:
a. 0
b. 1
c. N
d. M
