Database Basics

1 / 26
About This Presentation
Title:

Database Basics

Description:

Hospital surgery room environment. Operations are performed on patients. ... E-R modeling is a method of drawing pictures. REA Diagrams ... – PowerPoint PPT presentation

Number of Views:28
Avg rating:3.0/5.0
Slides: 27
Provided by: Smit99

less

Transcript and Presenter's Notes

Title: Database Basics


1
Database Basics
  • Overview of Databases

2
Arrivederci Pacioli
  • Five primary weaknesses of traditional accounting
    system (debits and credits)
  • Focus on subset of business activities
  • Delay in data capture and processing
  • Captures limited transaction information
  • Duplicate data
  • Expensive and defensive controls

3
Why is understanding relational databases
important?
  • All modern accounting software / ERP software is
    supported by relational databases.
  • To understand how these systems operate, you need
    to understand relational databases.
  • Custom reports are created using query writing
    languages to pull information from the database.
    If you do not understand the databases, it is
    very difficult to create reports.

4
Motivation forREA Component
  • Modern ISs run on top of databases, making it
    important to understand how databases store and
    process information.
  • E-R is the standard for modeling RDBMS
  • REA models provide an intermediate view of
    business processes.
  • Value chain, value system, and strategy are high
    level analyses
  • REA is intermediate level
  • Flowcharts are low level

5
Consider the transaction captured by this source
document
6
What entities are involved?
7
What tables are needed?
8
Is all information accounted for?
9
Relational Data Bases
  • We correct these problems by adding another table
    to create a normalized data base
  • Data duplication is minimized
  • Note the linked primary key in the
    Sales-Inventory table

10
Normalization exampletables
  • Vendor
  • Vendor ID
  • Vendor Name
  • Vendor Address
  • Discount Percentage
  • Discount Time
  • Item
  • Item number
  • Item description
  • Item price

Purchase PO number Purchasing agent ID Date
ordered Shipping terms Shipping date Vendor
ID Purchase-Item Item number PO
number Quantity ordered
11
Entity-Relationship (E-R) Modeling
  • E-R Modeling is one of many ways to draw pictures
    of businesses.
  • Other modeling choices include
  • Data flow diagrams (DFD)
  • Flowchart
  • E-R modeling is an ARTmay be a bit of a
    stretch...it is not a science.

12
Entity-Relationship(E-R) Modeling
  • Entities represent
  • People
  • Places
  • Things
  • Occurrences in time (like transactions)
  • Relationship
  • Link related entities

13
E-R Modeling Example
  • Hospital surgery room environment
  • Operations are performed on patients. One doctor
    performs the operation while being assisted by
    several nurses.

14
E-R Example
Doctor
Operating Room
Operation
Nurse
Patient
15
REA vs. E-R
  • The REA framework says that the world consists
    solely of Resources, Events, and Agents
    (Locations?)
  • E-R modeling is a method of drawing pictures.

16
REA Diagrams
  • McCarthy (1979, 1982) determined that business
    systems consist solely of
  • Resources
  • Events
  • Agents
  • McCarthy (1979, 1982) also identified common
    patterns of Resources, Events, and Agents

Inside Agent
Resource
Event
Outside Agent
17
REA Diagrams
  • Resource - anything under the firms control,
    that provides value and of which there is limited
    quantity
  • Event - an occurrence that changes the quantity
    of a resource
  • Agent - someone responsible for the event. One
    is from the organization (internal) the other is
    outside (external)
  • Location - where the event occurredsometimes
    included

18
REA Exchange Pattern
  • Decrease events are always linked to increase
    events - Duality
  • No free lunch - firms do not give up resources
    without receiving something

Inside Agent
Resource
Decrease Event
Outside Agent
Inside Agent
Resource
Increase Event
Outside Agent
19
Integrated REA Diagrams
  • Exchange REA Diagrams are combined to create an
    integrated REA Diagram.

Inside Agent
Inside Agent
Resource
Decrease Event
Increase Event
Outside Agent
Outside Agent
Inside Agent
Inside Agent
Resource
Increase Event
Decrease Event
Outside Agent
Outside Agent
20
Integrated REA Diagrams
  • Complete Integrated REA Diagrams include an
    increase event and a decrease event for each
    resource.
  • Resources must come from somewhere
  • Resources must go somewhere
  • May choose not to model complete system.
  • This is a new model, new field, developing
    field...but the concepts are well-accepted in the
    database field
  • UN/CEFACT (Center for Trade Facilitation)
  • ISO

21
REA Data Modeling Process
  • REA framework - provides conceptual model
  • Tables - Identify required tables and fields
  • Database - Build tables, establish relationships,
    and input data into relational database

22
Cardinalities
  • Definition the number of times each individual
    entity can participate in the relationship
  • Relationships between entities determine how
    tables must be related...entities lead to tables

23
Cardinalities
  • Maximums
  • Business process
  • Rules for relationships in database
  • Minimums
  • Business process
  • Information for controls in database

24
Emergency RoomE-R Example
  • Doctor - Operation
  • A doctor can have many operations
  • An operation can have one responsible doctor
  • Nurse - Operation
  • A nurse can have many operations
  • An operation can have many nurses

25
Steps in REA data modeling
  • Identify the business processes
  • Identify the resources affected by each event and
    the agents who participate in each event
  • Combine events into the basic exchange template
  • Add information about cardinalities
  • Identify magnitude of relationships
  • Implement Model in Relational Database
  • Table for each entity
  • Table for each MN relationship
  • Use foreign keys for 11 and 1N relationships
  • Develop a set of normalized tables

26
Queriesputting it back together
  • Some query basics
  • Database languages...data definition, data
    manipulation, data query
  • Data dictionary...to see what the data look like
  • Structured query language (SQL)...standardized
    query language with many specializations
Write a Comment
User Comments (0)