Fundamentals of Database Design

1
Fundamentals of Database Design

• John Villamil-Casanova
• Executive Vice President CIO
• The Aspira Association
• 202.835.3600 ext. 123
• jvillamil_at_aspira.org

2
Agenda

• Introduction and participants needs
• We will review what is a database
• Understand the difference between data and
  information
• What is the purpose of a database system
• How to select a database system
• Database definitions and fundamental building
  blocks

3
Agenda (2)

• Database development the first steps
• Quality control issues
• Data entry considerations

4
What is a database

• A database is any organized collection of data.
  Some examples of databases you may encounter in
  your daily life are
• a telephone book
• T.V. Guide
• airline reservation system
• motor vehicle registration records
• papers in your filing cabinet
• files on your computer hard drive. 

5
Data vs. informationWhat is the difference?

• What is data?
• Data can be defined in many ways. Information
  science defines data as unprocessed information.

• What is information?
• Information is data that have been organized and
  communicated in a coherent and meaningful manner.
  
• Data is converted into information, and
  information is converted into knowledge.
• Knowledge information evaluated and organized so
  that it can be used purposefully.

6
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

7
What is the ultimate purpose of a database
management system?
Is to transform
Data
Information
Knowledge
Action
8
More about database definition

• What is a database?
• Quite simply, its an organized collection of
  data. A database management system (DBMS) such
  as Access, FileMaker, Lotus Notes, Oracle or SQL
  Server which provides you with the software tools
  you need to organize that data in a flexible
  manner. It includes tools to add, modify or
  delete data from the database, ask questions (or
  queries) about the data stored in the database
  and produce reports summarizing selected
  contents.

9
Lets explore some examples

• Outlook contacts
• Aspira Association MIS
• KidTrax
• GIS-GPS systems

10
Types of Databases

• Non-relational databases
• Non-relational databases place information in
  field categories that we create so that
  information is available for sorting and
  disseminating the way we need it. The data in a
  non-relational database, however, is limited to
  that program and cannot be extracted and applied
  to a number of other software programs, or other
  database files within a school or administrative
  system. The data can only be "copied and pasted.
  Example a spread sheet
• Relational databases
• In relational databases, fields can be used in a
  number of ways (and can be of variable length),
  provided that they are linked in tables. It is
  developed based on a database model that provides
  for logical connections among files (known as
  tables) by including identifying data from one
  table in another table

11
Selecting a Database Management System

• Database management systems (or DBMSs) can be
  divided into two categories -- desktop databases
  and server databases.  
• Generally speaking, desktop databases are
  oriented toward single-user applications and
  reside on standard personal computers (hence the
  term desktop). 
• Server databases contain mechanisms to ensure the
  reliability and consistency of data and are
  geared toward multi-user applications.

12
Selecting a database system Need Analysis

• The needs analysis process will be specific to
  your organization but, at a minimum, should
  answer the following questions
• How many records we will warehouse and for how
  long?
• Who will be using the database and 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?

13
Some Definitions

• A File A group or collection of similar records,
  like INST6031 Fall Student File, American History
  1850-1866 file, Basic Food Group Nutrition File
• A record book a "rolodex" of data records, like
  address lists, inventory lists, classes or
  thematic units, or groupings of other unique
  records that are combined into one list (found in
  AppleWorks, FileMaker Pro software).
• A field one category of information, i.e., Name,
  Address, Semester Grade, Academic topic
• A record one piece of data, i.e., one student's
  information, a recipe, a test question
• A layout a design for a database that contains
  field names and possibly graphics.
• Database glossary

14
Fundamental building blocks

• Tables comprise the fundamental building blocks
  of any database.  If you're familiar with
  spreadsheets, you'll find database tables
  extremely similar.  Take a look at this example
  of a table sample database
• The table above contains the employee
  information for our organization --
  characteristics like name, date of birth and
  title.  Examine the construction of the table and
  you'll find that each column of the table
  corresponds to a specific employee characteristic
  (or attribute in database terms).  Each row
  corresponds to one particular employee and
  contains his or her information.  That's all
  there is to it!  If it helps, think of each one
  of these tables as a spreadsheet-style listing of
  information.

15
Where do we start?

• Lets explore your 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

16
Some Quality Control Considerations

• Remember garbage in garbage out. Some
  examples and how to prevent this.
• Quality management encompasses three distinct
  processes quality planning, quality control, and
  quality improvement
• Quality Planning in relation to database systems
  design
• Who will perform data entry?
• Training? On-line help?
• How data entry will be performed?

17
Data entry considerations

• Define must enter fields no record is
  complete unless such and such is entered
• Make data entry fool proof. Example Grade level
  can be entered as a number (8 or 8th or eight).
  By using a pull-down menu with the correct data
  format these mistakes can be avoided.

18
Data Entry additional considerations

• Barcode scanners
• USB or
• Wireless attached to a Palm or Pocket PC
• Pocket PC
• WiFi 802.11g, Bluetooth
• Wireless networks (real-time on demand systems)