Transcript Database Modeling

The Foundation of the Business Process
Model - Economic Exchanges



Economic Exchange – Two related economic
events where we acquire one resource in
exchange for another
Economic exchanges are important because they
involve the most fundamental events where value
is added in the business. Therefore, the first part
of this course will focus on these events only.
Examples: give cash, get inventory; give
inventory, get cash; get cash from investor, give
dividend to investor; borrow money from bank,
give money to bank; give cash, get building; give
cash, get services
Components of an Economic
Exchange




Two related events
Each event is associated with a resource – one event
increases resources (get), the other decreases or uses
resources (give); get and give may, and usually do,
involve different resources
Each event usually involves one internal agent and one
external agent; in an exchange, generally the same
external agent is associated with both events
Goal is to understand what the events, resources and
agents are, and the nature of the relationships between
them
An REA Model of an
Economic Exchange
William E. McCarthy*
Michigan State University
(These slides may be copied as long as original source is cited)
*http://www.msu.edu/user/mccarth4/
Cookie-Monster (the customer) and Elmo (the
entrepreneur) meet in the (real or virtual)
marketplace, thus setting the stage for an
Economic Exchange
Economic
Resource
Economic
Event
Economic
Agent
duality
Source:
W. E. McCarthy “The REA Accounting Model: A Generalized Framework for Accounting
Systems in a Shared Data Environment,” The Accounting Review, July 1982, pp 554-78.
W.E. McCarthy “The REA Modeling Approach to Teaching Accounting Information Systems,”
Issues in Accounting Education, November 2003, pp. 427-41. (source of following slides)
Cookie-Monster (the customer) and
Elmo (the entrepreneur) engage in a
SHIPMENT (transfer of Cookie Inventory)
Economic Resource
inside
participation
Economic Agent
Economic Event
stock-flow
Economic Agent
outside
participation
Give
Take
duality
outside
participation
Economic Agent
Economic Event
stock-flow
Economic Resource
inside
participation
Economic Agent
REA model of cookie sale from
entrepreneur’s (ELMO) perspective
Economic Resource
inside
participation
Economic Agent
Economic Event
stock-flow
Economic Agent
outside
participation
Give
Take
duality
outside
participation
Economic Agent
Economic Event
stock-flow
Economic Resource
inside
participation
Economic Agent
REA model of cookie sale from
entrepreneur’s (ELMO) perspective
Economic Resource
Cookies
inside
participation
Economic Agent
Salesperson
Economic Event
stock-flow
Sale
outside
participation
Give
Take
Economic Resource
Cash
Customer
duality
outside
participation
Economic Event
stock-flow
Economic Agent
Economic Agent
Customer
Cash Receipt
inside
participation
Economic Agent
Cashier
more general exchange model from the
entrepreneur’s (ELMO’s) internal perspective
COOKIES
COOKIES-stockflow-SALE
Product#
Description
Price
QOH
P-1
Chocolate
Chip
1.05
200
P-2
Chocolate
.95
P-3
Peanut
Butter
1.00
Pecan
1.10
P-4
Product#
Invoice#
Quantity
P-2
I-1
5
P-3
I-1
10
205
P-3
I-2
20
97
P-4
I-3
9
P-1
I-4
4
P-3
I-4
5
257
SALE-dualityCASH_RECEIPT
SALE
Invoice#
Dollar
Amount
Date
Salesperson
Employee#
Customer
#
Invoice
#
Receipt
Timestamp
Amount
Applied
I-1
14.75
1JUL
E-1234
C-987
I-1
2JUL0830
14.75
I-2
20.00
2JUL
E-1235
C-888
I-2
3JUL0800
2.00
I-3
9.90
3JUL
E-1236
C-999
I-2
5JUL0800
18.00
I-4
9.20
5JUL
E-1237
C-999
I-3
8JUL1145
9.90
I-4
8JUL1145
9.20
Partial Database for
Elmo’s Cookie Business
Why is this invoice amount $14.75 ??
How is customer paying for this ???
A business process is a set of activities that takes one or more
kinds of input and creates an output that is of greater value to the
customer (Hammer and Champy)
business process
business process
business process
labor
cash
Acquisition
Cycle
cookie
ingredients
cash
Conversion
Cycle
cookies
Revenue
Cycle
value chain
A value chain is a purposeful network of business processes
aimed at assembling the individual components of a final product
(i.e., its portfolio of attributes) of value to the customer (Porter
and Geerts/McCarthy)
Part of ELMO’s Value Chain for Providing Cookies
Semantic infrastructure of system
matches extended REA pattern
Enterprise
Systems
No Organizing
Rationale
Single Entry
Inwardly Oranized
A = L + OE
Outwardly
Organized
Enterprise
Value Chain
Hybrid
Single
Source
ERP
Transactions
& Obligations
Bookkeeping
MS Money
Quicken
Multidimensional
Accounting
Platinum
Solomon
Peachtree
Quickbooks
Modular
Integration:
ABC, MRP
Independent
Best of
Breed ERP
IntegratorEnabled
ERP
PeopleSoft
SAP
BPCS
Great Plains Dynamics
Trading
Partner
Constellar Hub
Vitria
StandardsEnabled
Supply
Chain
OMG
OAG
Customer
Focused
Siebel
Goldmine
i2
Ariba
ebXML
ISO Open -EDI
Enterprise Systems classification structure is from David, McCarthy & Sommer, Communications of the ACM, May 2003, pp. 65-9.
Different perspectives on REA modeling
needed for enterprise modeling (value chains)
and collaboration space (supply chains)



Enterprise modeling (as evidenced in normal ERP systems) is done
from the perspective of one company or entrepreneur. Business
processes are viewed as components of a single value chain. A single
exchange (like the sale of a product for money) would be modeled
twice, once in the enterprise system of each trading partner.
Collaboration space modeling (as evidenced in ebXML or ISO Openedi) is done from a perspective independent of each trading partner. A
single exchange is modeled once in independent terms that can be
then mapped into internal enterprise system components. Supply
chains are networks of business processes that alternate internal
transformations and external exchanges (definition due to Bob
Haugen).
REA modeling works in both cases and the independent to trading
partner mapping is absolutely straightforward and completely defined.
Illustration of Perspective: Trading Partner vs. Independent
Enterprise
Independent view of
Inter-enterprise events
Business
Process
Enterprise
Business
Process
Business
Process
Business
Process
Business
Process
Used for
collaboration space
modeling
Enterprise
Business
Process
Trading Partner view of
Inter-enterprise events
(upstream vendors and
downstream customers)
Business
Process
Blue arrows represent flow of goods, services,
and cash between different companies; green
arrows represent flows within companies
Business
Process
Business
Process
SOURCE: Adapted from ISO 15944-4, K. Morita
Economic Resource
Economic Agent
from
Economic Event
stock-flow
Economic Agent
to
initiating transfer
duality
responding transfer
Economic Agent
to
Economic Event
stock-flow
Economic Agent
from
Economic Resource
REA model of cookie sale from independent
(collaboration space) perspective
Identifying Economic Exchanges
The Merchant of Venice receives funding from outside
investors. His goal is to purchase silk in China and
sell it to the wealthy people in Italy. To accomplish this
goal, he first purchases a boat. Next he hires a deck
hand to captain the ship to China and back. While in
China, the manager negotiates with silk sellers and
purchases silk. When the return trip is completed, the
Merchant of Venice pays the deck hand, and sells the
silk. After retaining a portion of the proceeds for
himself, the remaining proceeds from the venture are
distributed among the investors according to the
amount of their original investment.
Merchant of Venice – Economic
Exchanges






Get cash from investor, give cash (principal plus
return) to investor
Give cash to silk merchant, get silk from
merchant
Give silk to wealthy people, get cash from
wealthy people
Give cash to ship builder, get ship from ship
builder
Give cash to deck hands, get service from deck
hands
Give cash to merchant, get services from
merchant
ER Diagrams


REA/Business Process – Show the events, resources and
agents in a business process and relationships between them
Data Model –Show the key entities to store data about (tables)
and relationships between them which must be captured
(primary/foreign key relationships and M:M tables)
Entity: events, resources or
agents
Describes the Relationship between
entities
ER DIAGRAMS ARE NOT FLOWCHARTS!!!!!!
Basic REA Template for an
Economic Exchange
Resource
get
Increasing
Event
by
from
for
to
Resource
give
Decreasing
Event
by
Internal
Agent
External
Agent
Internal
Agent
Basic REA Template for an
Economic Exchange -- Revenue
Inventory
give
Goods
Delivery
by
Shipping
Clerk
to
for
Customer
from
Cash
get
Cash
Receipt
by
Cash Rects
Clerk
Timing of Events


Generally, ERD’s are drawn with the
events listed from top to bottom in the
order they take place
For revenue, the two events making up
the exchange may occur



At the same time (a cash sale)
With the delivery first (a sale on account)
With the cash receipt first (customer prepays
or makes a deposit)
Basic REA Template for an Economic
Exchange – Expenditure
Inventory
get
Goods
Receipt
by
Receiving
Clerk
from
for
Vendor
to
Cash
give
Cash
Payment
by
Cash Pmts.
Clerk
REA Data Modeling



Step 1: Identify Events -- Store information about
events we want to plan, execute or evaluate; list
these down the center of the ER
Step 2: Identify Resources Influenced by Events - Resources are often assets of the business that
we wish to track information about; List these to
the left of the events on the ER.
Step 3: Identify Agents Involved in Events -Usually one internal agent and one external
agent. List these to the right of the events on the
ER.
REA Data Modeling, continued


Step 4: Identify Attributes of Events, Resources
and Agents -- Selection of attributes determines
what information we later have to make reports,
etc.
Step 5: Identify Relationships between Events,
Resources and Agents -- Words in the box are
unimportant, but you may want to use the basic
terminology used in the templates
Identifying Attributes



Eventually, we will become much more exacting
about the attributes stored when we create
database tables. For now, attributes help to
understand what is being modeled
For events, think of all of the things that would
be on the paper document used to capture
information about the event
For resources and agents, this is their master
file information
Basic REA Template for an
Economic Exchange – Revenue
Inventory
give
Goods
Delivery
by
Shipping
Clerk
to
for
Customer
from
Cash
get
Cash
Receipt
by
Cash Rects
Clerk
Attributes - Revenue






Goods Delivery – Date, Dock shipped from, customer,
items shipped, qty.
Cash Receipts – Date, Place received, customer,
amount received
Inventory – Item Number, description, qty on hand
Cash – Account/Drawer Number, Bank/Location,
Description, Balance
Customer – Customer Number, Name, Address,
Contact, Credit Limit, Balance
Shipping Clerks, Cash Receipts Clerks – Employee
Number, Name, Address, Department
More on Attributes – Inventory

Inventory items can be either



generic inventory items – ones that we always keep
in stock where we keep a catalog of the items we sell;
The data table for generic inventory items is basically
a listing of the items in our catalog and QOH
unique inventory items – custom made items that will
each have a unique identifier (think job order costing).
The data table for unique inventory items is a listing
of all of the individual items (or groups of identical
items called jobs) produced
YOU SHOULD ALWAYS ASSUME GENERIC
INVENTORY ITEMS, UNLESS IT IS
SPECIFICALLY STATED OTHERWISE
More On Attributes – Cash

The cash resource represents cash on
hand or in the bank. The data table storing
information about cash looks like one of
the following


For cash on hand – A listing of all of the cash
drawers in all of our stores and the amount of
cash that should be in each one
For cash in the bank – A listing of all of our
bank accounts, the bank and the balance
Basic REA Template for an Economic
Exchange – Expenditure
Inventory
get
Goods
Receipt
by
Receiving
Clerk
from
for
Vendor
to
Cash
give
Cash
Payment
by
Cash Pmts.
Clerk
Attributes - Expenditure






Goods Receipt – Date, Dock where received, vendor,
items received, qty.
Cash Payments – Date, Cash Account Number, Vendor,
amount paid
Inventory – Item Number, description, qty on hand
Cash – Account/Drawer Number, Bank/Location,
Description, Balance
Vendor – Vendor Number, Name, Address, Contact,
Credit Limit, Balance
Receiving Clerks, Cash Payments Clerks – Employee
Number, name, Address, Department
Basic REA Template for an Economic
Exchange – Payroll
Employee
Service
get
Get Employee
Service
Time Ticket
by
Production
Supervisor
from
for
Employee
to
Cash
give
Cash
Payment
by
Payroll
Clerk
REA Data Modeling, Continued


Step 6: Cardinality and Optionality -- For each entity
pair there are really TWO DIFFERENT relationships
Focus on a single example of one of the two entities



Cardinality -- How many instances the entity on the other
side of the relationship can be associated with that single
entity?
Optionality -- At ANY possible point in time, does a
relationship have to exist between the entity and the one
on the other side, or is it simply possible for a relationship
to exist?
Reevaluate, starting with a single example of the
other entity in the relationship
Cardinality and Optionality
Example
Cash
Payment


Only one (no crows feet)
Does payment always have to have a vendor?


Vendor
Single Payment
How many vendors associated with single payment?


sent to
Yes (vertical line)
Notice where this symbol goes
Cardinality and Optionality
Example
Cash
Payment


Can be many (crows feet)
Does Vendor always have to have a payment?


Vendor
Single Vendor
How many payments associated with single Vendor?


sent to
No – goods received, no payments yet (circle)
Notice where this symbol goes
Basic REA Template for an Economic
Exchange -- Conversion
Finished
Goods
Inventory
Manufacture Goods
(Production Order)
get
for
by
for
for
by
Raw Materials
Inventory
by
Machine
Time
give
Use Machines
(machine Log)
from
Production
Supervisor
give
Issue RM
(Materials
Requisition)
from
Production
Supervisor
Storeroom
Clerk
Production
Supervisor
Machine
Operator
Employee
Service
give
Use Employee
Service
(Time Ticket)
by
Production
Supervisor
from
Employee
Attributes - Conversion








Manufacture Goods/Production Order – Date, factory number,
supervisor, item to produce, qty.
Use Raw Materials/Materials Requisition -- Date, Storeroom
Clerk, Production Employee, Items Issues, Production Order,
Qty Issued
Use Employee Service/Time Ticket – Date Employee,
Production Order, Hours Worked, Job Code
Use Machine Time/Machine Log – Date, Machine Operator,
Machine, Hours used, Production Order
Employee Service – Job Code, Description, Capacity
Machine Time – Machine Number, Machine Use, Capacity
FG/RM Inventory – Item Number, description, qty on hand
Production Supervisor, Storeroom Clerk, Employees, Machine
Operators – Employee Number, name, Address, Department,
YTD earnings
Conversion Key Points





There is NO CASH INVOLVED, but Economic Exchange
Still Exists – Get Finished Goods, Give up employee
service, machine time, raw materials
The Economic Exchange is within the organization
Because all events are internal, there may not be two
agents for each event; Most often, the ones deleted
would be the production supervisor (authorizer) in the
‘use machine time’ and ‘use employee service’ events
Often, all of these events are recorded using a single
document (i.e. a record of a good being assembled;
called a job cost sheet); the process illustrated here
would be used in a large manufacturing operation
The Manufacture Goods event can take place over a
course of time (several hours, days or weeks). We may
record sub-events – i.e. start production, complete
production
Simultaneous Production and
Consumption





Definition – When a single resource is received or created at the same
time it is used or delivered.
Simultaneous production and consumption takes place with all services.
Unlike goods where there is one event where we get the good (goods
receipt) and a separate one where we give it (shipment), for services,
give and get occur simultaneously because services cannot be stored
Because of simultaneous production and consumption, the Give
Employee Service pattern for conversion is the same as the Get
Employee service template shown for payroll, except it says “GIVE”
rather than “GET”
These are really the same event because we simultaneously get
(produce) and give (consume) the employee’s service.
Despite the fact that they are the same event, sometimes, the event is
recorded two different times, once to pay payroll (the get) and separately
to track it for job costing purposes (the give); A better design is to record
the event only once, on the time ticket, and use the data for both payroll
and job costing (as we will assume)
REA Model (Geerts and
McCarthy)




Type images for basic objects allows
specification of policies and controls plus
abstract specification of negotiation
components
Commitment images for economic events
allows specification of contracts and
agreements
State machine model allows specification and
ordering of business events as collaboration
space messaging and/or internal workflow
Aggregation of binary collaborations allows
SOURCE: Geerts andcollaboration
McCarthy, The Ontological Foundations
of REAthird
Enterprise Information
Systems, 2003.
mediated
with
parties
Economic
Resource
Type
Economic
Contract
governs
Agreement
typifies
establish
specifies
involves
specifies
reciprocal
Economic
Commitment
specifies
reserves
Economic
Event
Type
qualifies
fulfills
typifies
Economic
Resource
stockflow
Business
Role
Economic
Event
from
to
Regulator
Economic
Agent
constrains
duality
Partner
Business
Transaction
Third
Party
requires
Bilateral
Collaboration
participates
Mediated
Collaboration
ISO Open-edi Ontology Collaboration Model
SOURCE: Adapted from ISO 15944-4, W.E. McCarthy