Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Page 1 of 12
CHAPTER 18
IMPLEMENTING AN REA MODEL IN A RELATIONAL DATABASE
Instructor’s Manual
Learning Objectives:
1. Integrate separate REA diagrams for individual transaction cycles
into a single comprehensive organization-wide REA diagram.
Questions to be addressed in this chapter:
1. First, he will integrate the separate REA diagrams he has
developed into a single, comprehensive enterprise-wide data
model.
Introduction
This chapter shows how to implement an REA diagram in a database.
Integrating REA Diagrams across Cycles
Figures 18-1, 18-2, and 18-3 present REA diagrams of Fred’s Train
Shop’s revenue, expenditure, and payroll cycles, respectively.
Learning Objective One
Integrate separate REA diagrams for individual
transaction cycles into a single comprehensive
organization-wide REA diagram.
Figures 18-1 and 18-2 were explained in Chapter 15, so we focus in
Chapter 18 on Figure 18-3 for the HR/payroll cycle activities.
Fred’s Train Shop uses an electronic time clock to record the hours
worked by each employee each day by marking the time each employee
began and ended working.
The relationship between the Time Worked and Disburse Cash events
reflects the use of an employee’s time and paying for it. The
relationship between these two events is 1:N. This is because each
Disburse Cash event is linked to many daily Time Worked events.
The Employee Time entity represents the fact that the resource being
acquired by the Time Worked event is the use of an employee’s skills
and knowledge for a particular period of time.
Each check or electronic funds transfer must be linked to at least one
and only one cash account. However the same cash account may be linked
to many disbursement events.
Rules for Combining REA Diagrams
Figures 18-1, 18-2, and 18-3 each contain some of the same entities.
For example, the inventory resource appears in both Figures 18-1
and 18-2.
Page 3 of 12
Figure 18-4 on page 528 which shows such a model for Fred’s Train
Shop.
Notice that the diagram merges copies of resource and event
entities, but retains multiple copies of agent entities.
Merging Redundant Resource Entities
Remember that REA diagrams for individual transaction cycles involve
give-to-get economic exchanges.
These problems are eliminated by merging redundant resource entities as
that shown in Figure 18-4.
Merging Redundant Event Entities
REA diagrams for individual transaction cycles may include some events
that also appear in the REA diagrams of another transaction cycle.
For example, Figures 18-2 and 18-3 both contain the Disburse Cash
event entity.
Figure 18-4 shows that the Disburse Cash event is linked to both
the Receive Inventory and the Time Worked events.
However, the cardinalities between the Disburse Cash event and the
other two events (Receive Inventory and Time Worked) in Figure 18-4 are
different from that in Figures 18-2 and 18-3.
The reason for this difference lies in the underlying semantics about
the nature of the relationship between the merged entity and other
entities.
Page 4 of 12
However, it is different when merging an event across transaction
cycles. The event that appears in both individual transaction
cycles can be linked to an event of one transaction cycle or to
an event in another transaction cycle. However, the event cannot
be linked to both events.
Validating the Accuracy of Integrated REA Diagrams
In Chapter 15 there were three basic principles for drawing REA
diagrams for individual transaction cycles. There are now five rules
for integrated REA diagrams:
2. Every event must be linked to two agents who participate in that
event.
3. Every event that involves the disposition of a resource must be
linked to an event that involves the acquisition of a resource.
(This reflects the economic duality underlying “give-to-get”
economic exchanges.)
Multiple Choice 1
The REA diagram for Fred’s Train Shop’s individual expenditure cycle
has _____ event entities.
a. 2
Multiple Choice 2
The REA diagram for Fred’s Train Shop’s individual revenue cycle has
_____ resource entities.
a. 2
b. 3
c. 4
d. 5
Multiple Choice 3
Merging redundant _____ does not affect any cardinalities, but merging
redundant _____ alters minimum cardinalities.
a. resources; agents
Multiple Choice 4
Whenever a merged event involves different agents in each of the
individual transaction cycles being merged, the minimum cardinalities
between that event and those agents changes from _____ to _____.
a. 1; M
Implementing an REA Diagram in a Relational Database
There are three steps to implementing an REA diagram in a relational
database:
1. Create a table for each distinct entity in the diagram and for
each many-to-many relationship.
Step 1: Create Tables for Each Distinct Entity and M:N
Relationship
Learning Objective Two
Build a set of tables to implement an REA model
of an AIS in a relational database.
Page 6 of 12
Seven tables will represent the event entities in the diagram:
Order Inventory, Receive Inventory, Disburse Cash, Time Worked,
Take Customer Orders, Sales, and Receive Cash.
In Figure 18-4 there are five M:N relationships.
Three are from the revenue cycle:
1. Take Customer Orders-Inventory
Two are from the expenditure cycle:
1. Inventory-Order Inventory
17 Tables will be needed to implement Figure 18-4 into a
relational database.
Step 2: Assign Attributes to Each Table
The next step is to determine which attributes should be included
in each table.
Identify Primary Keys
Remember from Chapter 4 that every table in a relational database
must have a primary key, consisting of an attribute, or
combination of attributes, that uniquely identifies each row in
that table.
Assign Other Attributes to Appropriate Tables
Page 7 of 12
These other attributes could include such items as customer
name and address.
A foreign key is an attribute in a table that is a primary
key in another table. Foreign keys are used to link tables.
Note that the employee number primary key in the Employees table
is the foreign key in seven other tables: 1) Order Inventory, 2)
Receive Inventory, 3) Disburse Cash, 4) Take Customer Orders, 5)
Sales, 6) Receive Cash, and 7) Record Time Worked.
Table 18-1 also includes nonkey attributes in some of the M:N
relationship tables.
Price and Cost Data
In Table 18-1, notice that information about prices and costs are
stored as an attribute in several different tables.
The Inventory table stores the suggested list price for the item.
Cumulative and Calculable Data
Notice that Table 18-1 does not contain cumulative data, such as
“quantity on hand” in the Inventory table, or calculate data, such as
“total amount of sale” in the Sales table.
Page 8 of 12
The total amount of sale equals quantity sold by the actual
sales price in the Sales-Inventory table.
Step 3: Use Foreign Keys to Implement 1:1 and 1:N Relationships
1:1 and 1:N relationships are more efficiently implemented by means of
foreign keys instead of being implemented as separate tables.
Using Foreign Keys to Implement 1:1 Relationships
Consider the case of a 1:1 relationship between sales and customer
payments (see Figure 17-7, panel A).
The minimum cardinality for the Receive Cash event is 0.
The minimum cardinality for the Sale event is 1.
Using Foreign Keys to Implement 1:N Relationships
1:N relationships can also be implemented in relational databases with
foreign keys.
Completeness Check
The list of attributes that users and management want included in the
database provides a means to check and validate the implementation
process.
Page 9 of 12
he did not have any table to cover the attribute “product discussed
during sales calls.”
Once all attributes have been assigned to tables, a final accuracy
check of relational databases is required:
1. Every table must have a primary key.
2. Other nonkey attributes in each table must be either a fact about
3. Every attribute in every table is single-valued.
Multiple Choice 5
In implementing an REA diagram in a relational database, the __________
are used to implement 1:1 and 1:M relationships.
a. primary keys
Multiple Choice 6
The following table(s) has/have concatenated keys.
a. Inventory
b. Sales-Inventory
c. Disburse Cash
d. Receive Inventory
Multiple Choice 7
Which of the following statements is false?
a. The second step in implementing an REA diagram in a relational
database is assigning attributes to appropriate tables.
Page 10 of 12
Using REA Diagrams to Retrieve Information from a Database
In this section we refer to Figure 18-4 and Table 18-1 to show how to
use completed REA diagrams to retrieve information to evaluate
performance.
Creating Journals and Ledgers
The traditional journals and ledgers can be created through the use of
queries.
Deriving Journals from Queries
In a relational database, event entities store information about
transactions.
However, this would produce a list of all sales transactions (cash and
credit sales). Sales journals only include credit sales.
To be able to determine the credit sales, the REA model would create a
new role “sale of merchandise to a customer” in the Sales table and
“receipt of payment from a customer” in the Receive Cash table.
Ledgers
In AISs, ledgers are master files that contain cumulative information
about specific accounts.
Page 11 of 12
Many financial statement accounts are represented as resources in the
REA model.
An important exception is claims: Figure 18-4 does not include
Accounts Receivable or Accounts Payable.
Generating Financial Statements
It is possible to use a completed REA diagram to guide the writing of
queries to produce the information that would be included in financial
statements.
Creating Managerial Reports
A major advantage of the REA data model is that it integrates
nonfinancial and financial data in the AIS and makes both types of data
easily accessible to management.
Multiple Choice 8
Sales and Sales-Inventory tables can be queried to produce a list of
all sales transactions However, to produce a sales journal would also
include queries of the both the __________ and __________ tables.
Page 12 of 12
Multiple Choice 9
A lot of the information about an organization’s assets that is posted
in ledgers is stored in _____ tables.
a. Resource
b. Event
c. Agent
d. Activity
Multiple Choice 10
Many financial statement accounts are represented as resources in the
REA model except for:
a. Cash
b. Accounts Receivable
c. Inventory
d. None of the above
