Database Management What we will do today

Database Management What we will do today

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

InvoiceNo SALES_ORDER line item INVENTORY SO_Number Item_Number Qty_Ordered 1010 2010-0050 2 1010 1000-1 5 1011 1002-1 5 1011 1001-1 10 1012 1003-1 5 1012 1001-1 10 1013 1001-1 50 1014 1003-1 25 1015 1003-1 25 1016 3961-1041 5 1016 3965-1050 50 1016 1003-1 5

1016 1000-1 4 INVENTORY_ITEMS Item_Number Item_Name 1000-1 20 in. Bicycle 1001-1 26 in. Bicycle 1002-1 24 in. Bicycle 1003-1 20 in. Bicycle 1003-1 20 in. Bicycle 2010-0050 Formed Handlebar 3961-1041 Tire Tube, 26 in. 3965-1050 Spoke Reflector SALES_ORDERS SO_Number Cust_Code 1010 WHEEL 1011 ETC 1012 WHEEL

1013 IBS 1014 ETC 1015 WHEEL 1016 ETC CUSTOMERS Cust_Code ETC IBS WHEEL Cust_Name Bikes Et Cetera Inter. Bicycle Sales Wheelaway Cycle Center Entity-Relationship diagrams M CustomerID tblINVOICE InvoiceID 1 tblINVITEM InventoryID InvoiceID

M InvoiceID M 1 tblCUSTOMER CustomerID 1 InventoryID tblITEM InventoryID Entity-Relationship diagrams ID_TAG PACKID TAGNUM INSTDATE AC01 32808 9/13/95 DB32 32808 12/13/95 DB32 37691 6/15/95

DB33 57772 5/27/95 WP08 37691 6/15/95 WP08 57772 5/27/95 WP09 59836 10/30/95 WP09 77740 5/27/95 SOFTCOST 754.95 380.00 380.00 412.77 227.50 170.24 35.00 35.00 TAGNO TAGNUM COMPID EMPNUM LOCATION 32808 M579

611 Accounting 37691 B121 124 Sales 57772 C007 567 Info Systems 59836 B221 124 Home 77740 M579 567 Home EMPLOYEE EMPNUM 124 567 611 EMPNAME Alvarez, Ramon Feinstein, Betty Dinh, Melissa Entity-Relationship diagrams (1,M) tblEMPLOYEE

EMPNUM TAGNUM 1 EMPNUM M tblTAGNO TAGNUM 1 M tblIDTAG TAGNUM PACKID Entity-Relationship diagrams REA data model REA is specifically for Accounting Information Systems 3 types of entities Resources

Events Agents Basic Template Basic Template Resource A Inflow Participant Internal Agent Participant External Agent Participant Internal Agent Participant

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

employee. Cash 1 RR 1 RR M Employee Vendor Cash Disb Employee

Recently Viewed Presentations

  • HIV and Adolescents - ACT for Youth

    HIV and Adolescents - ACT for Youth

    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...
  • Seasons

    Seasons

    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...
  • light - Mrs. Landon--Advanced Language Arts

    light - Mrs. Landon--Advanced Language Arts

    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 ...
  • 2017 HbA1c - edinburghlabmed.co.uk

    2017 HbA1c - edinburghlabmed.co.uk

    New diabetes diagnosis pathwayPLIG, 19th Jan 2017. Jonathan Malo (StR, Clinical Biochemistry) Nicola Zammit (Consultant - Diabetes & Endocrinology, Chair of Diabetes MCN)
  • Ethical considerations - Ms Lindstrom's Blog

    Ethical considerations - Ms Lindstrom's Blog

    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.
  • By: Sara Carlisle Ty Coleman Nick Graham Steven

    By: Sara Carlisle Ty Coleman Nick Graham Steven

    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 - York University

    Real Options - York University

    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...
  • Advisory Bodies 25 - 28 September 2012

    Advisory Bodies 25 - 28 September 2012

    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*