REA analysis and E-R diagramming

1. REA analysisand E-R diagramming 4/27/2011

2. What are we hoping to achieve? • Tool for designing a database system to meet the needs of the organization • REA modeling is a method of analyzing and thinking about the system • E-R diagramming is a means of diagramming what the database should look like based upon the analysis above.

3. E-R diagrams • Database design involves the determination of what the tables in the database look like (their structure) and how they relate to one another. • Normalization did this • A way of representing, or communicating, these structures is with E-R diagrams. • E-R diagrams show what the tables are and how they are related - much like our normalization exercises, but with a slightly different diagrammatic format.

4. Entity-Resource-Agent modeling • Resource - such as merchandise or cash • Person (what we referred to as entities in DFDs) • Event Entity Relationship • Describes how two entities relate Attribute • Provides specifics for an entity (the column information)

5. An example ItemID Description Bin_No Cost QOH ROP Customers 1 CustID M Inventory CustomerID CustName CustAddr CustPhone Contact Invoice 1 InvoiceID 1 M Invoice-Item ItemID InvoiceID CustomerID InvoiceDate N InvoiceID ItemID QuantOrd

6. Entity-Relationship diagrams

7. Entity-Relationship modeling • Cardinality • Within the context of ER modeling, we can characterize the cardinality of a relationship. • Cardinality has to do with the number of possible outcomes that we are combining together • Designations • 1-1 (one to one) • This is when two tables are related and for every occurrence of the primary key in the first table, there is one and only one occurrence of the foreign key in the second table. Third normal form does not require any 1 - 1 relations

8. Entity-Relationship modeling • Designations • 1-M (one to many) • This is the most common relationship • The primary key of the first table is unique in the second table • Consider a customer table and an invoice table • Each customer may have MANY invoices • Each invoice relates to ONLY ONE customer tblCustomer CustNo. tblInvoiceInvoiceNo. CustNo. 1 M

9. Entity-Relationship modeling • Designations • M-M (many to many) • This is frequent in accounting contexts. • You have two tables • In each table, there are multiple occurrences of the primary key of the other table • Example is Invoices and Inventory Items • Each invoice may have several items in inventory • Each item in inventory may appear on several invoices • The solution is to create a table that has a COMPOSITE PRIMARY KEY and build TWO relations tblInventory ItemNo tblInvoiceLine ItemNo InvoiceNo tblInvoiceInvoiceNo 1 ItemNo. M M InvoiceNo. 1

11. Entity-Relationship diagrams Tbl_SOSO_NUM Tbl_SO_ITEMItem_NumberSO_NUM Tbl_SO_ITEMItem_NumberSO_NUM Cust_Code InvoiceID M M 1 M 1 Tbl_CustomersCust_Code Tbl_InventoryItem_Number Item_Number 1

13. Entity-Relationship diagrams (1,M) tblEMPLOYEEEMPNUM tblIDTAGTAGNUM PACKID TAGNUM M 1 tblTAGNOTAGNUM tblTAGNOTAGNUM EMPNUM 1 M

14. Entity-Relationship diagrams

15. What are we hoping to achieve with REA modeling? • What we want to do is follow a structured approach for designing databases. • The basic element in a database is called an entity - a table

16. Resource-Event-Agent modeling • REA modeling is a hot topic in systems circles • Some books combine REA and E-R diagramming and some make no distinction

17. Resource-Event-Agentanalysis and modeling • We focus on events, which are things that get recorded in our system • For each event we will possibly have • The event itself • Resources consumed or obtained • Internal agents (entities) • External agents (entities) • The reason that the word entities is in parentheses is that with this type of modeling, all five of these things are referred to as “entities”

18. Event External Agent Resources Internal Agents Who What

19. Entity-Relationship diagramming • Recall that it uses three symbols • A rectangle • An entity (but not the same as in DFDs and flowcharts • A diamond • A relationship • An oval • An attribute • A specific form of E-R model is called REA (Resource-Event-Agent) modeling • REA is specifically for Accounting Information Systems

20. Basic Template Participant Participant Internal Agent Internal Agent Resource A Resource B Inflow Outflow Get Resource A Give Resource B Participant Participant External Agent External Agent Economic Duality

21. Basic Template • This is a slightly more restrictive view than we previously took. • Exchange event is two sided (balance sheet equation) • Commitment events (inquiry events?) LEAD TO exchange events (don’t worry about these) • Every exchange must have an internal and external agent

22. Four steps to developing an REA Diagram • Identify the pair of economic exchange events • Identify resources (balance sheet accounts) and agents • There will always be at least one internal and one external agent for economic exchange events. • Examine whether it should be broken down to include “commitment-type” events • Determine cardinalities of relationships

23. Identify the pair of economic exchange events Example - Revenue Cycle: Give Inventory Get Cash

24. Identify resources and agents • Resources for the sales (revenue) cycle: • Inventory • Cash • Agents for the sales cycle: • Internal - Salesperson/Cashier • External -Customer

25. USING the REA diagram • Create a table for each entity and one for each M:N relationship • You need a table for each M:N relationship to concatenate the primary keys for the two tables • Put the appropriate attributes (columns) in the tables • Implement relationships

26. Example WE-FIX-COMPUTERS operates a repair shop for PCs. This describes their purchase system for parts. They order parts from more than a dozen vendors. Sometimes vendors ship partial orders. We-Fix pays for purchases by the 10th of the next month. It always pays each invoice in full (no installment payments). There is a single purchase manager through which all purchases are made. Let’s consider the Order event and the Purchase event. We will have “place holders” for the Cash Disbursement event, but will not worry about it.

27. Order Invty Vendor Inventory Employee Vendor Receive Invty Employee Vendor Cash Cash Disb Employee

28. Order Invty Vendor Inventory Employee Vendor Receive Invty Employee Vendor Cash Cash Disb Employee

29. 1 M Order Invty PO Vendor 1 Inventory PO M Employee Vendor Receive Invty Here, there is only one employee… the purchase manager… that is called by the purchase order. Employee Vendor Cash Cash Disb Employee

30. 1 M PO- ItemID M Order Invty PO Vendor 1 Inventory M PO M Employee Vendor Receive Invty Here, we have a Many to Many relationship because each item in inventory can appear on several purchase orders and each purchase order has possibly several inventory items. See next slide for solution. Employee Vendor Cash Cash Disb Employee

31. PO- Line Item PO Order Invty PO Vendor 1 1 M M M ItemID 1 1 Inventory PO M Employee Vendor Receive Invty We create a NEW table with a composite primary key to resolve the M-M dilemma. Employee Vendor Cash Cash Disb Employee

32. PO- Line Item PO Order Invty PO Vendor 1 1 M M M ItemID 1 1 1 Inventory PO PO M Employee Vendor M Receive Invty We have a 1-M relation between orders and receipts ONLY because vendors might make partial shipments (so more than one shipment is received for a given PO) Employee Vendor Cash Cash Disb Employee

33. PO- Line Item PO Order Invty PO Vendor 1 1 M M M ItemID 1 1 1 Inventory PO M PO M Employee Vendor M M Receive Invty Again, we have a Many to Many relationship that we must resolve. Employee Vendor Cash Cash Disb Employee

34. PO- Line Item PO Order Invty PO Vendor 1 1 M M M ItemID 1 1 1 Inventory PO PO M Employee 1 ItemID Vendor M Rec. Rept. - Line Item M RR Receive Invty 1 M Employee Again, we create a NEW table with a composite primary key to resolve the M-M dilemma. Vendor Cash Cash Disb Employee

35. PO- Line Item PO Order Invty PO Vendor 1 1 M M M ItemID 1 1 1 Inventory PO PO M Employee 1 ItemID M RR Vendor M 1 Rec. Rept. - Line Item M RR Receive Invty 1 M 1 RR Employee M The internal and external agents are handeled in the same way as the order process, but there is a different employee. Vendor Cash Cash Disb Employee

36. Entity-Relationship modeling

37. Entity-Relationship modeling tblCashDisbursementCheck No. tblCashDisbursementInventoryReceipt Inv Rec No. + Chk No Check No. Date tblInventoryReceiptInv Rec No Inv Receipt No. PONo. tblPurchaseOrderPO No. tblPOInventoryReceiptPO No. + Inv Stck No. tblMaterialsInventoryInv. Stck No PONo. VendorNo. Inv Stock No. Inventory data tblVendorVendor No. Vendor data

38. Entity-Relationship modeling tblCashDisbursementCheck No. tblCashDisbursementInventoryReceipt Inv Rec No. + Chk No Check No. Date tblInventoryReceiptInv Rec No Inv Receipt No. PONo. tblPurchaseOrderPO No. tblPOInventoryReceiptPO No. + Inv Stck No. tblMaterialsInventoryInv. Stck No PONo. VendorNo. Inv Stock No. Inventory data tblVendorVendor No. Vendor data