Where are we going?

1
Where are we going?

• Business process ? model ? data base ? report
• All accounting systems have these
• BP?document?chart of accounts?journal?ledger?repor
  ts
• BP?data model?trigger?data entry?report

2
Traditional approach separate systems for each
cycle
Financing
Reporting G/L
Fixed Asset
Revenue
Acquisition
Cycle
Expenditure Cycles
Inventory
Human Resources
Acquisition
3
Problems with multiple systems

• Coordination difficult because of lack of shared
  information
• Sales?inventory?production,
• Scheduling?budgeting?operations,
• Multiple versions of the truth
• What is salesper accounting, marketing,
  production??
• Redundancy ? higher costs, inefficient,
  inconsistency

4
ERP An Integrated Solution
Customer Order Processing
Shipping
Human Resources
Ware- house
Inventory Management
Manufacturing
Equip.
Receiving
Procurement
Data about ALL of the various business processes
Financial System
5
How do ERPs provide integration?
A/R
Materials Management
HR
Production
Common Database
Sales
A/P
Quality Control
Cash Mgt
6
ERP and the value chain
Just part of the picture
SCM
Customers
Suppliers
ERP
Product Design
CRM
Others
Adapted from Numetrix Users Group meeting, 1999
7
ERPs

• SAP, Oracle/PeopleSoft/JD Edwards, Lawson, Great
  Plains (?)
• what does it mean to be an ERP
• shouldnt everyone be one
• Configure, conform, construct
• Integrated v. Best of class
• Be sure to read ABCs of ERPs

8
Reporting out of the ERP

• Standardexport to Excel perhaps
• convert to ASCII for SEC
• convert to HTML for web
• convert to pdf for distibution
• ..
• XBRLexport to Excel perhaps
• convert to ASCII for SEC
• automatically encode to XBRL
• make available to SEC, web, pdf

9
Classifying Accounting Systems types (small to
big)

• Single Entry
• Money, Quicken
• Bookkeeping Systems (organized around AL OE)
• Peachtree, DacEasy, Quickbooks
• Multidimensional Accounting
• GEACs SmartEnterprise, Solomons Solomon IV
• Modular Integration
• JBA Softwares System 21, Lawsons Insight II
  Enterprise Suite
• Single Source ERP
• SAPs R/3, Oracle Financials, JD Edwards One
  World, PeopleSoft, Baans BaanERP

10
Enterprise-Wide Systems

• An ERP can create a flexible, more democratic
  organization
• An ERP can create a hierarchical, uniform
  organization
• An ERP can strengthen an organizations ability
  to execute effective business processes
• An ERP can threaten an organizations ability to
  execute an effective business strategy

11
Foundational units

• Relational databases
• Data modeling
• Normalization
• Query languages

12
Relational database rules

• Every table must have a unique primary keycannot
  be null
• Foreign keys must be null or have a value
  corresponding to the value of a primary key in
  another table
• Each attribute must describe a characteristic of
  the object identified by the primary keyall
  attributes must relate to the primary entire key
• Each column in a row must be single-valuedno
  repeated attributes
• No calculated fields
• All related tables must be connected with foreign
  keys

13
Relational Data Bases
Consider the transaction captured by this source
document
14
Relational Data Bases
What entities are involved?
EVENT
AGENT
RESOURCE
Inventory
Customer
Sale
15
Relational Data Bases
What tables are needed?
EVENT
AGENT
RESOURCE
Inventory
Customer
Sale
16
Relational Data Bases
Is all information accounted for?
17
Relational Data Bases
What database rule(s) are violated?
18
Relational Data Bases

• We correct these problems by adding another table
  to create a normalized data base
• Data duplication is minimized
• Note the concatenated primary key in the
  Sales-Inventory table

19
Normalization rules

• Remove all fields with multiple valuesthe
  multiple-valued objects should be their own
  table
• Remove all fields that do not depend upon the
  entire primary keythe fields belong to one of
  the connected tables
• Remove all fields whose values can be
  unambiguously predicted by looking at the values
  of a non-primary key fieldthe fields should be
  in their own table
• depend upon value can be predicted by
• Do recording normalization exercise
• What business rules have you determined with
  these tables

20
Relational Data Bases
We have created an efficient relational database
thatuses foreign keys to link the tables
21
Data modeling

• Identify objects of interest
• Identify relationships
• Identify attributes of the objects
• We will use ER diagrams to build an REA model to
  give a well-structured database
• Semantic modelingstart with understanding of
  business and how processes work to begin model
• Data bucketstart with data attributes and sort
  into normalized tables

22
Activity to Information

• Business activity to business information
• Real business activity occurs?
• Identify relevant objects?
• Identify relationships between objects?
• Identify relevant attributes?
• Build data base
• Entitiestables Relationshipstable links
  Attributesfields
• Capture data from business activity, populate
  database
• Query database?business information
• No ledgers, journals, debits/credits, chart of
  accounts

23
Future of accounting??

• Could lead to the abandonment of double entry
  accounting - redundancy is no longer required to
  ensure accuracy of the AIS
• External reporting may become a matter of
  database access by usersSee Focus 4-1 (p. 127)
• Provides users with powerful ways to access data
  without aggregating and valuing by accountants.
  Multiple views, without predefinition by the
  accountants, are now possible
• Accountants must be active participants in
  designing systems to see that adequate controls
  are included to safeguard the data and
  reliability of the information

24
Faculty Evaluation

• The material is hard to envision due to the fact
  that most of it is hopeful thinking as to future
  developments. Why do accountants need this
  really?

25
REA Data Modeling

• Building accurate databases requires a great
  deal of careful planning and design before you
  even sit down at a computer.
• The REA data model provides a method for
  designing a database that is well-structured (it
  creates a normalized relational database)
• The REA data model consists, in general, of three
  basic elements and a pattern
• INSTEAD of starting with a mess of data and
  applying normalization rules to develop a set
  of tables the REA data model lets us BEGIN with
  business knowledge to create a set of normalized
  tables

26
Basic Business Processes
A set of Give-Get exchanges
27
Capital Acquisitions
What was theexchange?
Who wasinvolved?
What wasexchanged?
28
Expenditure
What was theexchange?
Who wasinvolved?
What wasexchanged?
29
Revenue
What was theexchange?
Who wasinvolved?
What wasexchanged?
30
REA Business Process Model
EVENTS
AGENTS
RESOURCES
31
Steps in REA data modeling

• Identify the basic exchange (give get)
• Identify the resources affected by each event and
  the agents who participate in each event
• Combine both events into the basic exchange
  template
• Add information about cardinalities
• Identify magnitude of relationships
• Implement Model in Relational Database
• Table for each entity
• Table for each MN relationship
• Use foreign keys for 11 and 1N relationships

32
Model for one event in the exchange
33
Model for the other event in exchange
Resource
Event
Agent
34
The Basic REA Exchange Template
On the diagram, it helps to model different
internal agents separately, even though will all
appear in just one table called EMPLOYEES
35
Create the basic Exchange template
Resource
Event
Agent
36
Some special cases

• Commitment eventan agreement to engage in an
  economic exchange in the future
• Purchase order
• Sales order
• Observation eventactivity that is relevant for
  planning, evaluation or control but is not
  directly related to an economic exchange
• Marketing call
• Computer support call

37
Step 2 Cardinalities

• Cardinalities explain how many instances of the
  entity on one side of the relationship can be
  linked to one instance of the entity on the other
  side of the relationship
• In a relational database, each instance of an
  entity a row in a table
• To understand this, first really need to
  understand what each entity represents
• Cash
• Inventory
• Customer
• Etc.

38
Now we can model cardinalities

• Definition Cardinalities indicate how many
  instances of one entity can be related to a
  single instance of the another entity
• Cardinalities come as pair of numbers (minimum,
  maximum)
• Minimum can be 0 or 1
• Maximum can be 1 or N (N many)

39
Cardinality Identification

• Consider two related objects, Receive Cash and
  Customer
• For a single event of Receive Cash, what is the
  minimum number of agent Customers, zero or 1?
• For a single event of Receive Cash, what is the
  maximum number of agent Customers, 1 or many?
• For a single agent Customer, what is the minimum
  number of event Receive Cash, zero or 1?
• For a single agent Customer, what is the maximum
  number of event Receive Cash, 1 or many?
• The answers reflect the business rules of the
  organization.

40
Generalized Statement

• For a single occurrence of OBJECT A, what is the
  minimum number of related OBJECT B occurrences,
  zero or 1?
• For a single occurrence of OBJECT A, what is the
  maximum number of related OBJECT B occurrences, 1
  or many?
• This gives the minimum and maximum cardinality
  from A to B
• This is shown, in the ER format as

41
Example of cardinality displayed graphically
42
Another example
43
Summary - Cardinalities

• Cardinalities tell how many instances in one
  entity can be linked to one instance in the other
  entity
• Cardinalities expressed as pairs of numbers
  (minimum, maximum)
• Minimums can be 0 or 1
• Maximums can be 1 or N
• There are four possible cardinality pairs (0,1)
  (0,N) (1,1) (1,N)

44
Relationship Specification

• Maximum cardinality of each entity
• 3 possible relationship combinations
• 11 (maximum both sides 1)
• 1N (maximum one side 1, other side N)
• MN (maximum both sides N)
• Important for relating data tables

45
Implementing an REA model in a relational database

• Once an REA diagram has been developed, it can be
  used to design a well-structured relational
  database.
• The three steps to implementing an REA diagram in
  a relational database are
• Create a table for
• Each distinct entity in the diagram
• Each many-to-many relationship
• Assign attributes to appropriate tables
• Use foreign keys to implement one-to-one and
  one-to-many relationships.
• RememberREA diagrams will differ across
  organizations because of differences in business
  policies.

46
Employees (Salesperson)
Call on Customer
Suppliers
Customer
Take Cust. Order
Inventory
Order Inventory
Employees (Purchase Agent)
Employees (Salesperson)
Receive Inventory
Customer
Suppliers
Sales
Employees (Cashier)
Receive Cash
Employees (Cashier)
Disburse Cash
Cash
47
Identify the entities to be represented

• Total entities to be represented in separate
  tables

Events
7


Resources
2


Agents
3


12


48
Look for NM relationships

• Total number of tables in database

Events
7


Resources
2


Agents
3


12


Plus Many-to-Many Relationships
6


18


49
Create tables

• Table names for these 18 tables correspond to the
  names of the entities in the REA diagram.
• The tables for MN relationships are hyphenated
  concatenations of the entities involved in the
  relationship.
• Makes it easier
• To verify that all necessary tables have been
  created.
• To use the REA diagram as a guide when querying
  the database.

50
Sample names

• Table names for our integrated diagram

• Call on Customer
• Take Customer Order
• Give Inventory
• Receive Cash
• Order Inventory
• Receive Inventory
• Disburse Cash
• Inventory
• Cash

• Customer
• Supplier
• Employee
• Take Order-Inventory
• Give Inventory-Inventory
• Give Inventory-Receive Cash
• Order Inventory-Inventory
• Receive Inventory-Inventory
• Receive Inventory-Disburse Cash

51
Identify table attributes

• Step 2 Assign Attributes to Each Table
• The next step is to determine which attributes
  should be included in each table.
• The designer needs to interview users and
  management to identify which facts need to be
  included in the database.
• Should use the REA diagram and business rules to
  determine in which tables those facts should be
  placed.

52
Table attributesprimary keys and others

• Identify Primary Keys
• Every table in a relational database must have a
  primary key.
• The primary key is usually a single attribute.
• However for MN relationship tables, it consists
  of two attributes that represent the primary key
  of each linked entity-concatenated keys
• Assign Other Attributes to Appropriate Tables
• Attributes other than the primary key are also
  included in tables
• To provide for accurate transaction processing
  and the production of financial statements or
• To facilitate effective management of the
  entitys resources, events, and agents.
• Any attribute in a table must be a fact about the
  object represented by the primary key.

53
Non-key attributes in NM tables

• Some non-key attributes even need to be stored in
  MN tables.
• Example The inventory-sales table may include a
  quantity sold attribute.
• The quantity sold cant be placed in the
  inventory table, because there can be many sales
  of any particular inventory item, and each sale
  produces a different quantity ordered.
• The quantity sold cant be placed in the sales
  table, because an individual sale can include
  several inventory items.
• The quantity sold is placed in the
  sales-inventory table so that you can determine
  how much of EACH inventory item was ordered with
  EACH sale.
• Normalization

54
Special problems with time-dependent attributes

• General ruleivory tower
• Time-independent data (such as birth dates or
  item descriptions) should be stored as an
  attribute of a resource or agent.
• Data that vary across time (such as list prices
  or addresses) should be stored in special tables
• Price change table
• General rulepractical/feasible
• Often time-dependent attributes are kept with
  event entities or in MN relationships that
  involve at least one event.
• Extended prices (Quantity X Price)

55
Computations and accumulations

• Accumulations
• Attributes like quantity on hand or account
  balance are cumulative data.
• Quantity on hand is calculated as
• Sum of quantities purchased from the table
  linking inventory to the receive inventory
  event.
• LESS Sum of quantity sold from the
  sales-inventory table.
• Customer balance
• Sum of all sales to the customer.
• LESS Sum of all cash receipts from customer.

56
Joining tablesForeign keys

• Step 3 Use foreign keys to implement 11 and
  1N relationships.
• Using Foreign Keys to Implement One-to-One
  Relationships
• Minimum cardinalities may suggest which choice is
  more efficient.
• When there are two sequential events, the primary
  key of the event that occurs first is usually the
  foreign key in the event that occurs second.
• Provides better control, as the employee who
  updates the table for the second event does not
  have to access the table for the event that
  occurred first.
• Using foreign keys to implement 1N relationships
• Place the primary key of the entity that can
  occur only once as a foreign key in the entity
  that can occur many times

57
Summary Relationship specifications

• Relationship specifications indicate the maximum
  cardinality for each entity participating in that
  relationship
• Relationship specifications are two numbers,
  separated by a colon there are three possible
  specifications
• 11 you implement this with foreign keys
• 1N you implement this with foreign keys
• MN you create a separate (linking) table for
  this relationship

58
Example
Customer
Inventory
Give Inventory
Employee
Customer
Receive Cash
Cash
59
Steps

• Identify needed tables
• Each event, resource, agent
• Each MN relationship
• Assign attributes, including primary key
  attributes to each table
• Use foreign keys to link the tables

60
Table solution
Table Name
Primary Key
Foreign Key
Other Attributes
Sale
Sale No.
Customer No., Employee No.
Date of Sale, Time of Sale,
Receive Cash
Cash Rect. No.
Employee No., Customer No.,
Receipt Date, Receipt Time,
Account No.
Total Amount of Receipt
Sale No.,
Inventory
Item No.
Description, List Price
Cash
Account No.
Bank, Type of Account
Customer
Customer No.
Customer Name, Customer
Address, Customer Phone
Employee
Employee No.
Employee Name, Employee
Address, Employee Phone,
Job Title
Sales-Inventory
Sale No.-Item
Quantity Sold
Sale No, Item No.
No.
61
Attribute check

• Completeness Check
• The list of attributes that users and management
  want included in the database provide a means to
  check and validate the implementation process.
• Each of those attributes should appear in at
  least one table as a primary key or an other
  attribute.
• Checking this list may reveal that a particular
  attribute has not been assigned or may even
  indicate the need to modify the REA diagram
  itself.

62
Review the tablesREA model, together

• The need to modify the REA diagram as a result of
  this completeness check is not unusual.
• In fact, it is often helpful to create tables and
  assign attributes before completion of the REA
  diagramhelps clarify what each entity
  representsThink about this for project 4
• When all attributes have been assigned, the basic
  requirements for a well-structured relational
  database can be used as a final accuracy check
• Every table has a primary key.
• Other attributes in the table are either a fact
  that describes the entity or a foreign key used
  to link tables.
• Every attribute in every table is single-valued.

63
Homework Assignment

• Work the Tigard Vet problem