copyright © 2012 pearson education, inc. publishing as prentice hall 18-1

Copyright © 2012 Pearson Education, Inc. publishing as Prentice Hall

Chapter 18

Implementing an REA Model in a Relational Database

18-1

Learning Objectives Integrate separate REA diagrams for individual

business cycles into a single, comprehensive organization-wide REA diagram.

Build a set of tables to implement an REA model of an AIS in a relational database.

Explain how to write queries to retrieve information from an AIS relational database built according to the REA data model.

Copyright © 2012 Pearson Education, Inc. publishing as Prentice Hall

18-2

Copyright 2012 © Pearson Education, Inc. publishing as Prentice Hall

REA Diagram – Revenue Cycle

18-3

Redundant

Redundant

Copyright 2012 Pearson Education, Inc. publishing as Prentice Hall

REA Diagram – Expenditure Cycle

18-4

Redundant

Redundant

Redundan t


REA Diagram – Payroll Cycle

First, REA is in mirror form, Agent, Event, Resource

Get/Give Time Worked/Disburse

Cash

Give/Get Time Used/Bill Client

Each Event should be related to one Resource But Time Worked is

usually NOT Time can NOT be

increased/decreased Attributes stored in

Time Event Hours Worked Time Used

18-5

Redundant

Redundant


REA Redundancies Separate REA for an organization will have redundant Entities

Redundant Resource Entities Eliminate Redundant Resources (no duplicates):

Link Resources to event entities in one business cycle and to event entities in the other cycle One Event that Increases the Resource and, One Event that Decreases the Resource No effect on Cardinality

Redundant Event Entities Eliminate (Merge) the Redundant Event Entities alters the minimum cardinalities associated with the other events that are

related to the merged event may be linked to either an event that is part of one business cycle or to an event

that is part of another cycle but cannot be linked to both events. the minimum cardinality associated with the other events must be 0 in the

integrated REA diagram May alter minimum cardinality between Event and Agent

Minimum becomes 0 (optional) if agents are different in different transaction cycles

18-6


REA Redundancies

Agents If Duplicate Agents exist across transaction

cycles Do NOT eliminate them, keep them for

legibility of the model

18-7


Integrated REA Diagram

18-8

Reven

ue C

ycle

Expendit

ure

Cycl

ePa

yro

ll C

ycl

e


Cardinality Effect of Merging Resources

18-9

Cardinalities Between Resource and Entities Remain the Same.


Cardinality Effect of Merging Events

18-10

The Cardinality between Disburse Cash and Supplier & Employee (as payee) is now 0 to 1, that is a disbursement can be made to the supplier OR the employee but not BOTH!

The Cardinality Between Disburse Cash and Receive Inventory and Disburse Cash and Time Worked is now 0


Rules for Creating Integrated REA Diagram

1. Every event must be linked to at least one resource.

2. Every event must be linked to two agents who participate in that event.

3. Every event that involves the disposition of a resource must be linked to an event that involves the acquisition of a resource.

4. Every resource must be linked to at least one event that increments that resource and to at least one event that decrements that resource.

5. If event A can be linked to more than one other event, but cannot be linked simultaneously to all of those other events, then the REA diagram should show that event A is linked to a minimum of 0 of each of those other events.

6. If event A can be linked to more than one other agent, but cannot be linked simultaneously to all of those other agents, then the REA diagram should show that event A is linked to a minimum of 0 of each of those other agents.

18-11


Using REA Diagram to Create Relational Database

Advantage: Ensures the elimination of Anomalies:

Update Insert Delete

18-12


REA to Database Steps1. Create a table for each distinct entity in the diagram and for each many-to-

many relationship.

2. Assign attributes to appropriate tables. Identify Primary Keys

Attribute(s) that uniquely identifies each record For M:N relationships the primary key consists of two attributes that represent the

primary keys of each entity linked in that relationship. Identify remaining attributes for Table.

3. Use foreign keys to implement one-to-one and one-to-many relationships. an attribute of one entity that is itself the primary key of another entity. 1:1

If events are sequential, the Primary Key of the 1st event should be used as the Foreign Key of the 2nd event

If not sequential, either entities primary key can become the foreign key of the other table

1:N Primary Key of the Entity Linked to the Many Entity MUST be used as the Foreign Key

in the Many Entity 18-13


Step 1

•Create Tables for each Entity

•Create Table for each M:N Maximum Cardinality

18-14

12 tables from Entities

5 tables from M:M cardinalities

Event Tables

Resource Tables


Step 3

•1:1•Primary key of 1st chronological event should be foreign key of next event.

•Order# FK in Sales

•1:N•Primary key of entity linked to many event, becomes foreign key in many event

•Supplier# FK in Order Inventory

18-15


Retrieving Information from REA Database

Journals Information contained in Event tables

Ledgers Information contained in Resource tables

Financial Statements Information contained in Resources and Information on imbalances

Accounts receivable sales transactions for which customer payments have not yet

been received

Accounts payable purchases from suppliers that have not yet been paid for

18-16

copyright © 2012 pearson education, inc. publishing as prentice hall 18-1

Documents