Kế toán, kiểm toán - Chapter 8: Rea modeling

Chapter 8REA ModelingOutlineExpected outcomesClasses of AISREA modeling descriptionREA modeling illustrationRelational database designExpected outcomesCompare and contrast view-driven and event-driven AIS.Use REA modeling to represent an event-driven AIS.Use a REA model to design a relational database for an event-driven AIS.Classes of AISView-drivenTraditionalCollects limited data designed to produce general-purpose financial statementsIT may / may not be presentCommon IT: general ledger softwareEvent-drivenMore sophisticatedCollects broader range of data for decision makingIT nearly always presentCommon IT: relational database software and / or ERP systemREA modeling descriptionSystems documentation technique often used to describe event-driven AISThree columnsResourcesEventsAgentsFocus on strategically significant operating eventsElements linked via cardinalitiesCardinalities can be used to create normalized relational database tablesREA modeling descriptionLecture break 8-1What resources, events and agents are described in the short sequence of events on the right?A customer submits an order online. An order clerk verifies inventory availability from an electronic database.REA modeling illustrationReceive order.CustomerOrder clerkInventoryNotice the elements’ layout. Resources on the left, events in the middle, agents on the right.REA modeling illustrationReceive order.CustomerOrder clerkInventory(1,*)Every “receive order” event involves one to many inventory items.(1,1)(1,1)(0,*)Every inventory item can be involved in zero to many “receive order” events.(0,*)(0,*)REA modeling illustrationLecture break 8-2The preceding REA model explains the cardinalities between “inventory” and “receive order.” Explain the remaining cardinalities in the model using similar language.Relational database designCardinalities give a lot of information about needed database tables.All tables should be in 3NF:Eliminate repeating groups ANDEliminate redundant data ANDEliminate columns not dependent on primary key.Relational database designPrinciplesWhen 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.Relational database designNeeded tables based on previous REA modelInventory tableCustomer tableOrder clerk tableReceive order tableReceive order / inventory tableRelational database designInventory table design specsInventory IDInventory item nameBeginning quantity on handBeginning quantity cost per unitBeginning quantity “as of” dateThe underline indicates the table’s primary key.Relational database designCustomer table design specsCustomer IDCustomer last nameCustomer first nameCustomer street addressCustomer cityCustomer stateLecture break 8-3What additional fields would you need in this table?Create specs for the “order clerk” table.Relational database designReceive order table design specsOrder number[Customer ID][Order clerk ID]Order dateBrackets 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.”Relational database designReceive order table design specsOrder number[Customer ID][Order clerk ID]Order dateAny 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.Relational database designReceive order / inventory table[Order number][Inventory ID]Quantity orderedPrice per unitThis 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.Classroom assessmentThis chapter has focused on REA models and their uses in accounting information systems.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.Classroom assessmentCertified 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.