Ch. 19: Special Topics in REA Modeling
19-16
The owners also have provided you with the following list of facts that they want
stored in the database. (Note: You must create appropriate primary keys for each table;
this is the list of other attributes.) Attributes in Standard Glass Artistry AIS:
• Date hired
• Time started task
• Time completed task
• Style of glass (name or description)
• Quantity on hand
• Color of glass
Accounting Information Systems
19-17
REQUIRED
a. Draw an integrated REA diagram for Stained Glass Artistry. Include both minimum
and maximum cardinalities.
Raw Materials
Inventory
Bill of Materials
Explanation of cardinalities:
a) Each row in the Bill of Materials table represents the standards for using one specific
raw material to produce one specific finished good design. Therefore, every row in
the Bill of Materials table is linked to one and only one row in the finished goods
table. A finished good, however, may consist of numerous raw materials and,
Ch. 19: Special Topics in REA Modeling
may be produced many different times and, therefore, can be linked to multiple rows
in the Work in Process table.
d) All raw materials are issued at one time; thus, the relationship between Raw Materials
Inventory and Issue Raw Materials is M:N.
b. Create the set of relational tables required to implement your REA diagram for Stained
Glass Artistry in a relational database.
Table Name
Primary Key(s)
Other Attributes (foreign keys in italics,
others in normal font)
Raw Materials
Raw Material
number
Style of glass, beginning quantity on hand,
color of glass, standard cost of glass
Employee
Employee number
Name, date hired, wage rate, date of birth
Employee Services
Category number
Issue Raw Materials
Raw Materials Issue
number
W.I.P. number, issuing employee number,
Work in Process
W.I.P. number
Design number, Quantity to be produced,
date design produced, actual cost of
design
Perform Job
Operation
Job Operation
number
Employee number, category number,
labor standard number, W.I.P. number,
Time started task, time completed task
Inventory
standard cost of design
Issue Raw Materials
number
Raw Materials Issue
number
Finished Goods
Design number
Design name, beginning quantity on hand,
Accounting Information Systems
19-19
19.6 Bernie’s Pet Store sells pet food, toys, and supplies. Bernie, the owner, is the only
person who places orders with suppliers. He is also the only person who writes checks.
Suppliers ship each order individually; if they are out of an item, they back order it and
ship it separately as soon as it arrives. Bernie pays each supplier monthly for all purchases
made the previous month. Suppliers do not allow him to make installment payments.
When employees are not working the cash register or checking in merchandise, they
restock shelves and clean up the premises. Bernie does not want to track each individual
restock or clean-up event, but does want to know how much time each employee spends
each day doing those tasks. He also wants to track how much time each employee spends
each day receiving inventory and how much time they spend working at the cash register.
He wants to be able to write queries that would show time spent by job task (restocking,
cleaning, receiving, or sales) for each employee. It is not practical, however, to try to
Ch. 19: Special Topics in REA Modeling
19-20
Employees
(Bernie)
Vendors
Employees
Order Inventory Inventory Sales Employee
Receive Inventory
Disburse
Cash
Cash
Receive
Cash
Customer
Employee
Explanation of cardinalities in Bernie’s pet store:
a) Checks may be written to either suppliers or to employees. One table can be used for
both types of checks. The primary key of that table would be a concatenated key
consisting of two attributes: check number and account number. (The latter attribute
distinguishes operating checks from payroll checks). Since a check may go to either a
vendor or an employee, the minimum cardinalities from the disburse cash event to those
agents are zero.
Accounting Information Systems
19-21
d) The Use Employee time event is used to track how employees spend their time. A row
would be created for each block of time an employee spent performing a particular type
of task. An attribute in this table would be a text field describing what an employee did
during that block of time. For example, if the employee restocked shelves from 8:00 am
to 11:00 am, there would be one row in the Use Employee Time table for that block of
e) The Employee Time resource is shown in dashed lines because it is not likely to be
implemented in a table.
Ch. 19: Special Topics in REA Modeling
19.7 At Big Time University (BTU) students are allowed to purchase two basketball tickets
for each home game. Each ticket contains the date of the game, and the seat information,
such as section, row, and individual seat number. Students pay for each game individually;
that is, student sporting event passes are not used at BTU. BTU deposits the proceeds from
each game into its bank.
REQUIRED
a. Prepare an REA diagram with cardinalities for the revenue cycle for BTU’s basketball
b. Implement your model in a set of relational tables. Be sure to specify primary keys,
foreign keys, and identify at least one other attribute that should be included in each table.
Primary Key(s)
Other Attributes (foreign keys in
italics, others in normal font)
Invoice Number
Student Number, Employee Number,
Date, Total Amount
Remittance Number
Invoice Number, Employee Number,
Student Number, GLAccount Number,
Date, Total Amount
Ticket Number
Row, Seat
Student Number
Name, Address, Phone
Employee Number
Name, Address, Phone, Position
Accounting Information Systems
19-23
19.8 Small contractors often rent special equipment for specific jobs. They need to track
the equipment that is rented, when it is returned, and payments made to the rental
company.
REQUIRED
a. Draw a partial REA diagram for the acquisition, payment, and return of rental
equipment. Be sure to include cardinalities and state any assumptions you made
when specifying those cardinalities.
Return
Rented
Equipment
Employee
Rented
Equipment
This solution is based on the following assumptions:
1. Each Rent event is independent of every other Rent event. For example, each time the
contractor rents equipment, they must sign a rental agreement or contract for all the
equipment they rent at that particular time.
Ch. 19: Special Topics in REA Modeling
19-24
allows the contractor to rent multiple items of the same type. For example, the contractor
may rent 5 jackhammers and 5 air compressors in a single Rent event. Thus, many rental
item types may appear on a single Rent event.
Note: An alternative solution would be to model the rental and return of each individual piece of
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.
Accounting Information Systems
19-25
SUGGESTED ANSWERS TO THE CASES
Case 19.1 This case involves creating a database from an integrated REA diagram and
then using the REA diagram to guide the writing of queries to prepare financial
statements.
Required
a. Create the tables necessary to implement Figure 19-9 in a relational database. Be sure to
include primary keys and other relevant attributes in each table.
Table Name
Primary Key(s)
Other Attributes (foreign keys in
italics, others in normal font)
Customer
Customer number
account balance, credit limit
Employee
Employee number
Name, date hired, wage rate, date of
birth, position
Take Customer Order
Sales Order Number
date, total amount
sales order number, Item Number,
Name, address, phone, beginning
date
Receive Cash
Remittance Number
Customer number, employee number,
GLAccount number,Invoice Number
date, amount
Finished Goods
Inventory
Item Number
Name, Description, beginning quantity
on hand, standard cost, list price
Work in Process
W.I.P. Number
produced, date design produced
Started, Time Finished, Date
Equipment Operations
List
Machine Operation List
Number
Job Operations List
Job Operations List Number
Standard time, Item number
Bill of Materials
Bill of Materials Number
Raw materials number, item number,
Standard quantity used in this design
Raw Materials
Raw Material Number
hand, standard cost
Description, beginning quantity on
Ch. 19: Special Topics in REA Modeling
19-26
Raw Material Number
Suppliers
Supplier Number
Name, Address, Phone, Beginning
balance
Equipment
Equipment ID Number
Description, Cost, Depreciation
Method, Useful Life, Salvage Value,
Year Acquired, Purchase Order
number, Receiving report number
Acquire Services
Service Acquisition number
Description, Cost, Check number,
GL Account number
General and
Administrative Services
GLAccount Number
Description, length of contract, budget
Cash
GLAccount Number
Description, beginning balance
Time Worked
TimeCard Number
Employee Number, Supervisor
Number, Time in, Time Out, Date,
Check number
Order Raw Materials
and Equipment
Purchase Order Number
Date, employee number, supplier
number, equipment ID number
Receive Raw Materials
and Equipment
Receiving Report Number
number
Issue Stock
Issuance Number
Date, Transfer Agent Number,
Employee (Treasurer) number,
Shares, par value
Issue Debt
Loan Number
Amount, Date, Interest rate, term,
Transfer Agent number, employee
(Treasurer) number, GLAccount
number
Transfer Agent
Transfer Agent Number
Name, Address, Phone
Customer Order –
Finished Goods
Order Number, Item
Number
Quantity ordered, unit sales price
Sale – Receive Cash
Invoice Number, Receipt
Number
Amount applied
Order Raw Materials
and Equipment – Raw
Materials
Purchase Order Number,
Raw Materials Number
Quantity ordered, unit cost
Receive Raw Materials
and Equipment – Raw
Materials
Receipt Number, Raw
Materials Number
Quantity received, condition
Accounting Information Systems
19-27
and Equipment – Cash
Disbursements
Check number
Order Raw Materials
and Equipment –
Receive Raw Materials
and Equipment
Purchase Order number,
Receiving Report number
Issue Raw Materials –
Raw Materials
Raw materials number,
Issue Raw Materials
Number
Quantity issued
Sales – Finished Goods
Inventory
Invoice Number, Item
Number
Quantity sold
b. Write the query, or set of queries, necessary to generate as many elements of
financial statements as possible. For example, write the query or set of queries that
would be used to calculate the amount of cash on hand, the total of accounts
receivable, the total value of raw materials, inventory on hand, etc.
The actual syntax will depend on the software used. The following logic describes the
queries that can be used to provide most of the information needed to construct a simple
income statement and balance sheet:
1. To derive total sales you need to query three tables: Take Customer Order, Take
Customer Order – Finished Goods Inventory, and Sales. First, find the set of
2. To derive total actual Cost of Goods Sold requires assumptions about inventory
costing method (LIFO, FIFO, etc.). However, it is straightforward to calculate the
standard cost of goods sold as follows: query the Finished Goods Inventory, the
3. Only three expenses can be calculated from the model: wages, general
Ch. 19: Special Topics in REA Modeling
19-28
worked for each employee by that employee’s payrate (found in the
4. To calculate cash balance first retrieve the sum of the beginning balance attribute
from the Cash table. Second, sum the amount column in the Receive Cash, Issue
5. To calculate accounts receivable begin by computing the sum of the beginning
6. It is straightforward to calculate the standard cost of ending inventory using the
7. The equipment table has the cost of all equipment. Cumulative depreciation can
8. Accounts payable can be calculated as follows.
a. Begin by retrieving the sum of the beginning balance attribute from the
suppliers table.
b. Then calculate the total of all purchases.
i. For equipment, this involves summing the cost attribute for all
rows in the equipment table linked to a Receive Raw Materials and
Equipment event this fiscal period.
19-29
9. Long term debt can be calculated by summing the amount column in the Issue
Debt table.