REA analysis and E-R diagramming December 1, 2009 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. What are we hoping to achieve? What we want to do is follow a structured approach for designing databases. The basic element in a database is called an entity What do you think an entity might be relative to an ACCESS database? Some of the usual suspects
Entities from the DFD/flowchart world(people) Events Resources Locations 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 Resource-Event-Agent(Location) analysis 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) Perhaps a location
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. This sounds a lot like narratives, DFDs and flowcharts, huh? REA analysis Think back to the purchase order in the SUA that we looked at a few days ago Who What Where Entity-Relationship diagramming 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 Resource-Event-Agent modeling basic template Internal agent Resource These are all considered entities Event External Agent Location (if needed) (if needed) Internal agent Resource
Event Location (if needed) External Agent (if needed) Entity-Resource-Agent modeling Entity -Resource - such as merchandise or cash -Person (what we referred to as entities in DFDs) -Event Relationship Attribute -Describes how two entities relate -Provides specifics for an entity (the column information) Entity-Relationship modeling
Entity-Relationship modeling Date tblCashDisbursement Check No. PO No. tblPurchaseOrder PO No. PO No. Check No. tblInventoryReceipt Inv Rec No tblVendor Vendor No. Inv Receipt No. tblPO InventoryReceipt
tblMaterialsInventory PO No. + Inv Stck No. Inv. Stck No Inv Stock No. Vendor No. tblCashDisbursement InventoryReceipt Inv Rec No. + Chk No Vendor data Inventory data Entity-Relationship modeling Date tblCashDisbursement Check No. PO No. tblPurchaseOrder PO No.
PO No. Check No. tblInventoryReceipt Inv Rec No tblVendor Vendor No. Inv Receipt No. tblPO InventoryReceipt tblMaterialsInventory PO No. + Inv Stck No. Inv. Stck No Inv Stock No. Vendor No. tblCashDisbursement
InventoryReceipt Inv Rec No. + Chk No Vendor data Inventory data 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 Example:
Entity-Relationship modeling xample from last class Notice how each SSN is unique in the first AND the second t REGISTRATION know any ofLAST_NAME the information in the table, you know it all. SSN FIRST_NAME PHONE_NO LIC_PLATE_ST LIC_PLATE_NO you might want this way though... 123-34-5678 Curry to design Dorothy things (916)358-4448 CA 123 MCD 134-56-7783
Fong Person ID SSN 123-34-5678 134-56-7783 May LAST_NAME Curry Fong (916)563-7865 CA FIRST_NAME Dorothy May PHONE_NO (916)358-4448 (916)563-7865 Lets rewrite this ONE table as two separate (like we did last Plate tables
ID class) LIC_PLATE_ST SSN LIC_PLATE_NO 123 MCD 253 DAL CA CA 123-34-5678 134-56-7783 253 DAL Entity-Relationship modeling Designations 1-1 (one to one) Person ID SSN Plate ID
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. 1 CustNo. M tblInvoice InvoiceNo. 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 1 ItemNo ItemNo. M tblInvoiceLine M ItemNo InvoiceNo InvoiceNo. 1 tblInvoice
External Agent Get Resource A Economic Duality Resource B Outflow Give Resource B 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 (dont worry about these) Every exchange must have an internal and external agent
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 Identify the pair of economic exchange events Example - Revenue Cycle: Give Inventory Get Cash Identify resources and agents Resources for the sales (revenue) cycle:
Inventory Cash Agents for the sales cycle: Internal - Salesperson/Cashier External - Customer 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 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. Lets consider the Order event and the Purchase event. We will have place holders for the Cash Disbursement
event, but will not worry about it. Order Invty Vendor Inventory Employee Vendor Receive Invty Employee Vendor Cash Cash Disb Employee Order Invty Vendor
Inventory Employee Vendor Receive Invty Employee Vendor Cash Cash Disb Employee Order Invty 1 PO M Vendor 1
Inventory PO M Employee Vendor Here, there is only one employee the purchase manager that is called by the purchase order. Receive Invty Employee Vendor Cash Cash Disb Employee
POItemID M Order Invty 1 PO M Vendor 1 Inventory M PO M Employee Vendor 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. Cash Receive Invty Employee Vendor Cash Disb Employee M POM Line Item PO 1
Order Invty 1 PO M Vendor 1 ItemID 1 Inventory PO M Employee Vendor We create a NEW table with a composite primary
key to resolve the M-M dilemma. Receive Invty Employee Vendor Cash Cash Disb Employee M POM Line Item ItemID PO 1 Order Invty
1 1 1 PO M Vendor 1 Inventory PO PO M 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) Cash
M Employee Vendor Receive Invty Employee Vendor Cash Disb Employee M POM Line Item ItemID PO 1
Order Invty 1 1 1 PO M Vendor 1 Inventory M PO PO M Again, we have a Many to Many relationship that we must resolve. M
Employee Vendor M Receive Invty Employee Vendor Cash Cash Disb Employee M POM Line Item PO ItemID 1
Order Invty 1 1 1 PO M Vendor 1 Inventory PO 1 PO M Employee ItemID
M Rec. Rept. - M M Line Item RR Vendor 1 Receive Invty Employee Again, we create a NEW table with a composite primary key to resolve the M-M dilemma. Cash Vendor Cash Disb Employee M
POM Line Item PO ItemID 1 Order Invty 1 1 1 PO M Vendor 1 Inventory PO
1 PO M Employee ItemID M Rec. Rept. - M M Line Item RR M Vendor 1 Receive Invty The internal and external agents are handeled in the same way as the order process, but there is a different
The American Academy of Pediatrics has a web site called Healthy Children which provides also a lot of information for adolescents and even into young adulthood. Finally, the Young Men's and Young Women's Health web sites are extremely useful providing...
54) The difference in heating of the earth's surface produces the planet's seasons. 55) The Earth's tilt on its axis is the primary reason for the seasons. 56) The North Pole receives more radiation and sunlight during the summer than...
Patrimony-(-monia-condition)-condition or estate inherited from one's fathers Patron-one who acts like a father toward another Patronymic-(onyma[Gr]-name)-a father's name adapted and given to his children MATER/MATRIS mother Matriarch-(archos-chief)-a woman who rules the family Maternal-motherly Maternal grandparents-parents of one's ...
Raine et al (1997) Read. Raine. et al (1997) study on . brain abnormalities in murderers indicated by positron emission tomography (PET) and. write a summary of it. Use max 200 words, Include: the aim, method and results.
Nick Graham Steven Stokes We were to design a rollercoaster that moved an object from Point A to Point B in 15 seconds. The project cost roughly $35 to build, mostly due to the cost of the wood. Step One:...
Real Options in Capital Budgeting ... Call Option Valuation Option Valuation Parameters Option Valuation Re-evaluating the Lev-I The DCF valuation of the Lev-I was (205.63) The Lev-II option is worth 305.30 With the Lev-II option, the Lev-I is worth 99.67...
tourism. Revision of sectoral tools on safety and health: Opencast mines. Ports. Shipbuilding and ship repair industries * Subject to the approval of the Governing . Body. Development of sectoral tools2014-15*
Ready to download the document? Go ahead and hit continue!