Transcript Order number

Chapter 8
REA Modeling
McGraw-Hill/Irwin
© 2013 The McGraw-Hill Companies, Inc., All Rights Reserved.
Outline
• Expected outcomes
• Classes of AIS
• REA modeling description
• REA modeling illustration
• Relational database design
8-2
Expected outcomes
• Compare and contrast view-driven and
event-driven AIS.
• Use REA modeling to represent an eventdriven AIS.
• Use a REA model to design a relational
database for an event-driven AIS.
8-3
Classes of AIS
• View-driven
– Traditional
– Collects limited data
designed to produce
general-purpose
financial statements
– IT may / may not be
present
– Common IT: general
ledger software
• Event-driven
– More sophisticated
– Collects broader range
of data for decision
making
– IT nearly always
present
– Common IT: relational
database software and
/ or ERP system
8-4
REA modeling description
• Systems
documentation
technique often used
to describe eventdriven AIS
• Three columns
– Resources
– Events
– Agents
• Focus on strategically
significant operating
events
• Elements linked via
cardinalities
• Cardinalities can be
used to create
normalized relational
database tables
8-5
REA modeling description
• Lecture break 8-1
What resources, events
and agents are
described in the short
A customer submits an
order online. An order
clerk verifies inventory
sequence of events on
availability from an
the right?
electronic database.
8-6
REA modeling illustration
Inventory
Notice the elements’ layout.
Receive
order.
Customer
Order clerk
Resources on the left, events
in the middle, agents on the
right.
8-7
REA modeling illustration
Every “receive order”
event involves one to
many inventory items.
Inventory
(1,*)
(0,*)
(0,*)
(1,1)
Receive
order.
Customer
(0,*)
Every inventory item
can be involved in
zero to many “receive
order” events.
(1,1)
Order clerk
8-8
REA modeling illustration
• Lecture break 8-2
The preceding REA model explains the
cardinalities between “inventory” and “receive
order.” Explain the remaining cardinalities in
the model using similar language.
8-9
Relational database design
• Cardinalities give a lot of information about
needed database tables.
• All tables should be in 3NF:
– Eliminate repeating groups AND
– Eliminate redundant data AND
– Eliminate columns not dependent on primary
key.
8-10
Relational database design
• Principles
– When the maximum cardinalities between two
elements are 1 and many, include the primary key
from the “1 side” in the table on the “many side.”
– When the maximum cardinalities between two
elements are many and many, create a junction table
in addition to the separate tables for the elements.
– Never store derivable data.
8-11
Relational database design
• Needed tables based on previous REA
model
– Inventory table
– Customer table
– Order clerk table
– Receive order table
– Receive order / inventory table
8-12
Relational database design
• Inventory table design specs
– Inventory ID
– Inventory item name
– Beginning quantity on hand
– Beginning quantity cost per unit
– Beginning quantity “as of” date
The underline
indicates the
table’s primary
key.
8-13
Relational database design
• Customer table design specs
– Customer ID
– Customer last name
– Customer first name
– Customer street address
– Customer city
– Customer state
Lecture break 8-3
•What additional fields
would you need in this
table?
•Create specs for the
“order clerk” table.
8-14
Relational database design
• Receive order table design specs
– Order number
– [Customer ID]
– [Order clerk ID]
– Order date
Brackets indicate foreign keys.
Remember the principle: When
the maximum cardinalities
between two elements are 1
and many, include the primary
key from the “1 side” in the
table on the “many side.”
8-15
Relational database design
• Receive order table design specs
– Order number
– [Customer ID]
– [Order clerk ID]
– Order date
Any individual order can include
“many” inventory items. So,
this table includes no data
about inventory items, as there
is no way to determine how
many fields would be needed
for them.
8-16
Relational database design
• Receive order / inventory table
– [Order number]
– [Inventory ID]
– Quantity ordered
– Price per unit
This junction table is necessary
because of the second design
principle: When the maximum
cardinalities between two
elements are many and many,
create a junction table in addition
to the separate tables for the
elements.
8-17
Classroom assessment
• This chapter has
focused on REA
models and their uses
in accounting
• Try your hand at
preparing a REA model
based on the short case
on the next slide.
• Then, work with a partner
to compare your work.
• Create database
specifications for two
tables indicated by your
REA model.
information systems.
8-18
Classroom assessment
Certified Fraud Examiners are required to complete 20 hours of continuing
professional education annually. At least ten of the hours must relate directly
to fraud detection / deterrence; two hours must focus on ethics. Each month,
the Association of Certified Fraud Examiners (www.acfe.com) searches its
member database to determine which members need to certify CPE
compliance. The Association mails a letter to those members, reminding them
to log on to the web site and certify their compliance. Members must do so by
the date specified in the letter. The ACFE may randomly select members to
provide detailed information about the CPE units they completed. If a member
is so selected and cannot provide required documentation, the ACFE may
extend the deadline or revoke the certification.
8-19
8-20