Where are we going l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 63

Where are we going? PowerPoint PPT Presentation


  • 100 Views
  • Uploaded on
  • Presentation posted in: General

Where are we going?. Business process  model  data base  report All accounting systems have these… BPdocumentchart of accountsjournalledgerreports BPdata modeltriggerdata entryreport. Traditional approach: separate systems for each cycle. Financing. Reporting & G/L.

Download Presentation

Where are we going?

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Where are we going l.jpg

Where are we going?

  • Business process  model  data base  report

  • All accounting systems have these…

    • BPdocumentchart of accountsjournalledgerreports

    • BPdata modeltriggerdata entryreport


Traditional approach separate systems for each cycle l.jpg

Traditional approach: separate systems for each cycle

Financing

Reporting &

G/L

Fixed Asset

Revenue

Acquisition

Cycle

Expenditure

Cycles

Inventory

Human Resources

Acquisition


Problems with multiple systems l.jpg

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 sales—per accounting, marketing, production??

  • Redundancy  higher costs, inefficient, inconsistency


Erp an integrated solution l.jpg

ERP: An Integrated Solution

Shipping

Customer Order

Processing

Human Resources

Ware-

house

Inventory

Management

Manufacturing

Equip.

Receiving

Procurement

Data about ALL of the various business processes

Financial System


How do erp s provide integration l.jpg

How do ERP’s provide integration?

A/R

Materials

Management

HR

Production

Common

Database

Sales

A/P

Quality

Control

Cash

Mgt


Erp and the value chain l.jpg

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


Slide7 l.jpg

ERPs

  • SAP, Oracle/PeopleSoft/JD Edwards, Lawson, Great Plains (?)

    • what does it mean to be an ERP

    • shouldn’t everyone be one

  • Configure, conform, construct

  • Integrated v. Best of class

  • Be sure to read ABCs of ERPs


Reporting out of the erp l.jpg

Reporting out of the ERP

  • Standard—export to *Excel* perhaps

    • convert to ASCII for SEC

    • convert to HTML for web

    • convert to pdf for distibution

    • …..

  • XBRL—export to *Excel* perhaps

    • convert to ASCII for SEC

    • automatically encode to XBRL

    • make available to SEC, web, pdf…


Classifying accounting systems types small to big l.jpg

Classifying Accounting Systems – types (small to big)

  • Single Entry

    • Money, Quicken

  • Bookkeeping Systems (organized around A=L + OE)

    • Peachtree, DacEasy, Quickbooks

  • Multidimensional Accounting

    • GEAC’s SmartEnterprise, Solomon’s Solomon IV

  • Modular Integration

    • JBA Software’s System 21, Lawson’s Insight II Enterprise Suite

  • Single Source ERP

    • SAP’s R/3, Oracle Financials, JD Edwards’ One World, PeopleSoft, Baan’s BaanERP


Enterprise wide systems l.jpg

An ERP can create a flexible, more democratic organization …

An ERP can create a hierarchical, uniform organization…

An ERP can strengthen an organization’s ability to execute effective business processes…

An ERP can threaten an organization’s ability to execute an effective business strategy…

Enterprise-Wide Systems


Foundational units l.jpg

Foundational units

  • Relational databases

  • Data modeling

  • Normalization

  • Query languages


Relational database rules l.jpg

Relational database rules

  • Every table must have a unique primary key—cannot 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 key—all attributes must relate to the primary entire key

  • Each column in a row must be single-valued—no repeated attributes

  • No calculated fields

  • All related tables must be connected with foreign keys


Relational data bases l.jpg

Relational Data Bases

Consider the transaction captured by this source document:


Relational data bases14 l.jpg

Relational Data Bases

What entities are involved?

RESOURCE

EVENT

AGENT

Inventory

Sale

Customer


Relational data bases15 l.jpg

Relational Data Bases

What tables are needed?

RESOURCE

EVENT

AGENT

Inventory

Sale

Customer


Relational data bases16 l.jpg

Relational Data Bases

Is all information accounted for?


Relational data bases17 l.jpg

Relational Data Bases

What database rule(s) are violated?


Relational data bases18 l.jpg

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


Normalization rules l.jpg

Normalization rules

  • Remove all fields with multiple values—the multiple-valued objects should be their own table

  • Remove all fields that do not depend upon the entire primary key—the 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 field—the 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


Relational data bases20 l.jpg

Relational Data Bases

We have created an efficient relational database thatuses foreign keys to link the tables:


Data modeling l.jpg

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 modeling—start with understanding of business and how processes work to begin model

  • Data bucket—start with data attributes and sort into normalized tables


Activity to information l.jpg

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

    • Entities=tables; Relationships=table links; Attributes=fields

  • Capture data from business activity, populate database

  • Query databasebusiness information

  • No ledgers, journals, debits/credits, chart of accounts…


Future of accounting l.jpg

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 users—See 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


Faculty evaluation l.jpg

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?”


Rea data modeling l.jpg

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


Basic business processes l.jpg

Basic Business Processes

A set of

Give-Get exchanges


Capital acquisitions l.jpg

Merchant

GiveCash

Cash

Ship

GetShip

Shipbuilder

Capital Acquisitions

What wasexchanged?

What was theexchange?

Who wasinvolved?


Expenditure l.jpg

Merchant

GiveCash

Cash

Inventory

GetSilk

Vendor

Expenditure

What wasexchanged?

What was theexchange?

Who wasinvolved?


Revenue l.jpg

Merchant

GiveSilk

Inventory

Cash

GetCash

Customer

Revenue

What wasexchanged?

What was theexchange?

Who wasinvolved?


Rea business process model l.jpg

Internal

GiveSomething

ResourceDecreased

ResourceIncreased

GetSomething

External

REA Business Process Model

RESOURCES

EVENTS

AGENTS


Steps in rea data modeling l.jpg

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 M:N relationship

    • Use foreign keys for 1:1 and 1:N relationships


Model for one event in the exchange l.jpg

Resource

Event

Agent

Ship

Builder

Get Ship

Ship

Merchant

of Venice

Model for one event in the exchange


Model for the other event in exchange l.jpg

Ship

Builder

Give Cash

Cash

Merchant

of Venice

Model for the other event in exchange

Resource

Event

Agent


The basic rea exchange template l.jpg

Internal

Agent

Resource

Give Event

External

Agent

Resource

Get Event

Internal

Agent

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


Create the basic exchange template l.jpg

Merchant

of Venice

“Captain”

Get Ship

Ship

Ship

Builder

Give Cash

Cash

Merchant

of Venice

“Cashier”

Create the basic Exchange template

Resource

Event

Agent


Some special cases l.jpg

Some special cases

  • Commitment event—an agreement to engage in an economic exchange in the future

    • Purchase order

    • Sales order

  • Observation event—activity that is relevant for planning, evaluation or control but is not directly related to an economic exchange

    • Marketing call

    • Computer support call


Step 2 cardinalities l.jpg

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, eachinstance of an entity = a row in a table

  • To understand this, first really need to understand what each entity represents

    • Cash

    • Inventory

    • Customer

    • Etc….


Now we can model cardinalities l.jpg

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)


Cardinality identification l.jpg

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.


Generalized statement l.jpg

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:


  • Example of cardinality displayed graphically l.jpg

    Example of cardinality displayed graphically


    Another example l.jpg

    Another example


    Summary cardinalities l.jpg

    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)


    Relationship specification l.jpg

    Relationship Specification

    • Maximum cardinality of each entity

    • 3 possible relationship combinations:

      • 1:1 (maximum both sides = 1)

      • 1:N (maximum one side =1, other side = N)

      • M:N (maximum both sides = N)

    • Important for relating data tables


    Implementing an rea model in a relational database l.jpg

    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.

    • Remember…REA diagrams will differ across organizations because of differences in business policies.


    Slide46 l.jpg

    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)

    Employees (Cashier)

    Receive Cash

    Disburse Cash

    Cash


    Identify the entities to be represented l.jpg

    Identify the entities to be represented

    • Total entities to be represented in separate tables:

    Events

    7

    Resources

    2

    Agents

    3

    12


    Look for n m relationships l.jpg

    Look for N:M relationships

    • Total number of tables in database:

    Events

    7

    Resources

    2

    Agents

    3

    12

    Plus: Many-to-Many Relationships

    6

    18


    Create tables l.jpg

    Create tables

    • Table names for these 18 tables correspond to the names of the entities in the REA diagram.

      • The tables for M:N 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.


    Sample names l.jpg

    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


    Identify table attributes l.jpg

    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.


    Table attributes primary keys and others l.jpg

    Table attributes—primary 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 M:N 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 entity’s resources, events, and agents.

      • Any attribute in a table must be a fact about the object represented by the primary key.


    Non key attributes in n m tables l.jpg

    Non-key attributes in N:M tables

    • Some non-key attributes even need to be stored in M:N tables.

    • Example: The inventory-sales table may include a “quantity sold” attribute.

      • The quantity sold can’t 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 can’t 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…


    Special problems with time dependent attributes l.jpg

    Special problems with time-dependent attributes

    • General rule—ivory 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 rule—practical/feasible:

      • Often time-dependent attributes are kept with event entities or in M:N relationships that involve at least one event.

        • Extended prices (Quantity X Price)


    Computations and accumulations l.jpg

    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.


    Joining tables foreign keys l.jpg

    Joining tables—Foreign keys

    • Step 3: Use foreign keys to implement 1:1 and 1:N 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 1:N 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


    Summary relationship specifications l.jpg

    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:

      • 1:1 – you implement this with foreign keys

      • 1:N – you implement this with foreign keys

      • M:N – you create a separate (linking) table for this relationship


    Example l.jpg

    Example

    Customer

    Inventory

    Give

    Inventory

    Employee

    Customer

    Cash

    Receive

    Cash


    Steps l.jpg

    Steps…

    • Identify needed tables

      • Each event, resource, agent

      • Each M:N relationship

    • Assign attributes, including primary key attributes to each table

    • Use foreign keys to link the tables


    Table solution l.jpg

    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.


    Attribute check l.jpg

    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.


    Review the tables rea model together l.jpg

    Review the tables…REA 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 diagram—helps clarify what each entity represents—Think 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.


    Homework assignment l.jpg

    Homework Assignment

    • Work the Tigard Vet problem


  • Login