Kế toán doanh nghiệp - Chapter 8: Rea modeling

Chapter 8REA ModelingOutlineLearning objectivesPurpose and usesTerminologyCardinalitiesDatabase designLearning objectivesCompare and contrast view-driven and event-driven accounting information systems.Use REA modeling to represent an event-driven AIS.Use a REA model to design a relational database for an event-driven AIS.Purpose and usesPurposeTo describe an event-driven accounting information systemUsesIdentify the essential Resources, Events and Agents of the AISEstablish relationships between themDesign relational databases to implement an event-driven AISTerminologyEvents (center column)Three typesOperating: sell inventory to customersInformation: record inventory sales in the AISDecision / management: decide whether to drop a specific product lineOnly strategically significant operating events appear in a REA modelDetermining strategically significant operating events is a matter of judgment and practiceTerminologyResources (leftmost column)Items needed to carry out the eventsExamplesCashInventoryEquipmentEmployment applicationTerminologyAgents (rightmost column)People needed to carry out the eventsExamplesEmployeesVendorsCustomersStockholdersTerminologyInventoryBuy inventoryVendorEmployeeAll entities in a REA model are illustrated with rectangles. Resources on the left, events in the middle, agents on the right.CardinalitiesShow relationships between elements of the REA modelFacilitate creation of database tablesThree common symbols01*An employee might process no purchase orders. (“0”)Every order involves exactly one customer. (“1”)A single purchase requisition can include multiple inventory items. (“*”)CardinalitiesTo create cardinalities between two elements of a REA model, ask four questions.Set 1For each “x,” what is the minimum number of “y?”For each “x,” what is the maximum number of “y?”Set 2For each “y,” what is the minimum number of “x?”For each “y,” what is the maximum number of “x?”CardinalitiesInventoryBuy inventoryVendorEmployeeSuppose:X = inventoryY = buy inventoryCardinalitiesSet 1For each inventory item, what is the smallest number of “buy inventory” transactions?Hint: minimums are either 0 or 1Answer: 1For each inventory item, what is the greatest number of “buy inventory” transactions?Hint: maximums are either 1 or * (many)Answer: * (many)CardinalitiesInventoryBuy inventory(1,*)Every inventory item can be bought one to many times. Every inventory item participates in one to many “buy inventory” events.CardinalitiesSet 2For each “buy inventory” transaction, what is the smallest number of inventory items?Hint: minimums are either 0 or 1Answer: 1For each “buy inventory” transaction, what is the greatest number of inventory items?Hint: maximums are either 1 or * (many)Answer: * (many)CardinalitiesInventoryBuy inventory(1,*)Every “buy inventory” event includes one to many inventory items. CardinalitiesInventoryBuy inventory(1,*)(1,*)CardinalitiesBuy inventoryEmployee(0,*)(1,1)Every employee participates in zero to many “buy inventory” events.Every “buy inventory” event involves exactly one employee.Database designEvery “box” in the REA model needs at least one database tableWhen maximum cardinalities are 1 and *, include the key from the “one side” in the table on the “many side.”When maximum cardinalities are * and *, create a junction table to express the relationship.Follow the rules of normalization.Do not store calculated data.Database designInventoryBuy inventoryVendorEmployee(1,*)(1,*)(0,*)(1,1)(1,*)(1,1)Tables indicated by this REA model:VendorEmployeeInventoryBuy inventoryInventory / buy inventoryDatabase designVendor tableVendor IDVendor nameVendor addressVendor cityVendor stateVendor ZIP codeVendor web siteAnd many others!Employee tableEmployee IDEmployee last nameEmployee first nameEmployee departmentEmployee job titleAnd many others!Database designInventory tableInventory IDInventory item nameBeginning quantity on handBeginning inventory cost per unitBeginning inventory dateDatabase designBuy inventory tablePurchase order numberPurchase order date[Vendor ID][Employee ID]Buy inventoryVendor(1,*)(1,1)Remember: When the maximum cardinalities are “one” and “many,” put the primary key from the “one side” (vendor) into the table on the “many side” (buy inventory).Database designInventory / buy inventory table[Inventory ID][Purchase order number]Quantity purchasedCost per unit purchasedInventoryBuy inventory(1,*)(1,*)Remember: When the maximum cardinalities are “many” and “many,” create a junction table.