Chapter 8

1. Chapter 8 REA Modeling

2. Outline • Expected outcomes • Classes of AIS • REA modeling description • REA modeling illustration • Relational database design

3. Expected outcomes • Compare 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.

4. 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

5. REA modeling description • Systems documentation technique often used to describe event-driven 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

6. REA modeling description • Lecture break 8-1 What 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.

7. REA modeling illustration Inventory Customer Receive order. Notice the elements’ layout. Resources on the left, events in the middle, agents on the right. Order clerk

8. REA modeling illustration Every “receive order” event involves one to many inventory items. (0,*) (1,1) Inventory (1,*) (0,*) Customer Receive order. (0,*) (1,1) Every inventory item can be involved in zero to many “receive order” events. Order clerk

9. 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.

10. 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.

11. 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.

12. Relational database design • Needed tables based on previous REA model • Inventory table • Customer table • Order clerk table • Receive order table • Receive order / inventory table

13. 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.

14. 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.

15. 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.”

16. 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.

17. 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.

18. Classroom assessment • This 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.

19. 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.