Database Basics

1
Database Basics

• By
• Bob Larson

2
Agenda

• What is a database?
• The difference between data and information
• What is a database system?
• How to select a database system
• Database definitions and fundamental building
  blocks
• Database development the first steps

3
What is a Database

• Any organized collection of data
• A collection of similar data
• Examples of databases
• Telephone book white pages
• T.V. Guide
• Airline reservation system
• Motor vehicle registration records
• Papers in your filing cabinet
• Files on your computer hard drive

4
Data Information Knowledge

• Data
• Can be defined in many ways
• IS defines data as unprocessed information
• Information
• Data that have been organized and communicated in
  a coherent and meaningful manner
• Knowledge
• Knowledge information evaluated and organized so
  that it can be used purposefully

Data is converted into information, and
information is converted into knowledge
5
Data vs. Information

• We collect data
• Information is harvested from data
• Many companies are good at collecting data
• Fewer are good at harvesting information

6
Ultimate Purpose of a Database Management System
(DBMS)
To transform
Data
Information
Knowledge
Action
7
Why do we need a database?

• Keep records of our
• Clients
• Staff
• Volunteers
• To keep a record of activities and interventions
• Keep sales records
• Develop reports
• Perform research
• Longitudinal tracking (Say what?)

8
Database Terminology
9
Database Management System (DBMS)

• Software data tools to
• Store (tables)
• Organize (sort)
• Add, modify or delete
• Ask questions (queries)
• Produce forms and reports
• Summarizing
• Displaying details
• Toolbox is a good analogy

10
DBMS Examples

• Microsoft Access
• Structured Query Language (SQL)
• Microsoft SQL Server
• Oracle
• MySQL
• FileMaker Pro
• Lotus Notes

11
Operational vs. Analytical Databases

• Operational databases
• Used to track and assist in the daily business
  activities
• Data typically changes frequently reflecting
  activities occurring over time
• Examples
• H/R systems
• Mailing lists
• Inventory management programs
• Accounting systems (including programs like
  Quicken and QuickBooks)
• Point of sale systems (cash registers)
• Analytical databases
• Tend to be more static including larger amounts
  of historical data
• Data is then analyzed (processed) looking for
  patterns or trends
• Often support the strategic activities of an
  organization
• Goal may be
• Predict the future
• Summarize historical data
• Prove historical assumptions
• Did a pattern of discrimination occur
• What is the price elasticity for a product line

12
Flat-File vs. Relational Database

• Flat-File Database
• All relevant data in a single table, or series of
  unrelated tables
• Work best for small quantities of data where
  viewing and sorting the data in a single list
  does not create a time-consuming task
• Typically a persons first databases
• Example Excel spreadsheet or Word data list file
• Relational Database
• Provide a solution to data entry redundancy
  problems
• Linked through common fields (columns) with
  exactly the same data
• Tables linked together can be queried as if one
  table

13
Flat-File Example

• Weaknesses common to flat-file systems
• Note the duplicate information in the table
• Inconsistencies in the way Supervisor Names are
  entered

14
Relational Database Example
15
Database Tables
16
Query from Two Tables
17
Forms
18
Reports
19
Selecting a DBMS

• Can be divided into two categories
• Desktop databases
• Oriented toward single-user applications
• Reside on standard personal computers
• Client / Server databases
• Contain mechanisms to
• Ensure the reliability and consistency of data
• Data security
• Oriented toward multi-user applications

20
Needs Analysis

• Will vary with the organization
• At a minimum, should answer the following
  questions
• How many records will be stored and for how long?
• Who will be using the database?
• What tasks will they perform? 
• How often will the data be modified? 
• Who will make these modifications?
• Who will be providing IT support for the
  database?
• What hardware is available? 
• Is there a budget for purchasing additional
  hardware?
• Who will be responsible for maintaining the data?
• Will data access be offered over the Internet? 
• If so, what level of access should be supported?
• What level of security is needed and how will it
  be enforced?

21
Where do we start?

• Explore the paper system
• Client intake forms
• Job application form
• Funders reports
• Database modeling
• Define required fields from forms or required
  reports
• Avoid repetition
• Keep it simple
• Identify a unique identifier or primary key

22
Fin