database introduction

Document Sample
database introduction Powered By Docstoc
   What a database is
   How it fits into the broader information
    management picture
   What the different parts of a database are
   How to think about information in a database-
    shaped way
   The priorities involved in the database
    planning process
What is a database?

  A database is a storage space
  for content / information
But what is data? And
where is it now?
 Data is factual information about objects and
 concepts, such as:
 •   measurements
 •   statistics
     You can find it in:
    filing cabinets
    spreadsheets
    folders
    ledgers
    lists
    colleagues’ memories
What does “managing
information” mean?
   Making information work for us

   Making information useful

   Avoiding "accidental disorganisation”

   Making information easily accessible and
    integrated with the rest of our work
Managing as re-organising
  We often need to access and re-sort data for
  various uses. These may include:
     Creating mailing lists
     Writing management reports
     Generating lists of selected news stories
     Identifying various client needs

  Can you add to the list?
Managing as re-processing
The processing power of a database allows it
   Sort
   Match
   Link
   Aggregate
   Skip fields
   Calculate
   Arrange
Databases everywhere!
Because of the versatility of databases, we
find them powering all sorts of projects:
   A web site that is capturing registered users
   A client tracking application for social service
   A medical record system for a health care facility
   Your personal address book in your e-mail client
   A collection of word processed documents
   A system that issues airline reservations
Exercise 1: Understanding data and
Different parts of a database
   Fields
   Records
   Queries
   Reports
    Database storage units
    Generic elements of content
Exercise 2: Breaking down
content into fields
Use the table below to break each content type into
fields. Refer to the list of example fields for
A simple table showing fields (columns) and records(rows):

And as part of an MS Access database table:
   Queries are the information retrieval
    requests you make to the database
   Your queries are all about the
    information you are trying to gather
   If the query is a question...
...then the report is its answer
 Reports can be tailored to the needs of
    the data-user, making the information
    they extract much more useful
The database planning process
 What kind of database are you working
    Information and referral database

    Contact/client database

    Donation database (donorbase)

    Other
Information and referral databases
   Store information about service providers
   Track and match requests for referral
   Use a simple design
   Need to be maintained with up-to-date and
    complete information
Contact/client databases
   Store contact information for client base
   Sortable by criteria like:
       Client interests
       Services provided
       Subscription expiry dates
       Last contacted date
Donation databases (donorbases)
   Generate reports of donations
   Order or sort donors according to different
   Record the various donation details of each donor
   Create and sort lists of potential and current donors
   Print letters, labels and reports
Exercise 3: Database planning
   Work through the questions on the worksheet
   Be aware of the sequence (order) in which
    questions arise
   Repeat the exercise with your colleagues in
    your organisation
Conclusion: Database - the information
   A powerful tool for managing
   Information first - database second
   Take your co-workers with you
         Include them at all stages of the project
          planning process
         Train them properly in the use of the tool
         Develop their understanding of the capacity of
          the tool
What is Microsoft Access?
   Microsoft Access is a relational database management
   system (DBMS or RDBMS). At the very core, it is a
   software “engine” that provides an interface between
   physical data and user application queries.

   Other examples of DBMS applications include:

   •SQL Server (Microsoft)
   •DB2 (IBM)
   MS Access - software used for creating databases
       Quickly
       Accurately
       Using wizards and templates developed to maintain the
        integrity of your data
   Data are just information
   Database
       Collection of data
       Related to a particular topic or project
Data Integrity
   Data must be accurate.
   Data are RELATED to other data in your
    database (e.g., library patron is related to the
    book(s) that s/he has checked out).
   Maintaining the INTEGRITY of the
    relationship between different pieces of data
    is very important.
       Example of compromised integrity: sending an
        overdue notice to the wrong library patron
MS Access vs. MS Excel
   MS Excel
       spreadsheet
       flat database
       all information has a one-to-one relationship
   MS Access
       like multiple spreadsheets that are connected to one
           one-to-many relationships
           many-to-many relationships
   Printed Phone Directory (White Pages)
       Flat database: One-to-one relationships
   Library Catalog
       Relational database: Many-to-many relationships
           Library patrons check out many books.
           Books are checked out by many patrons.
   University Class Schedule
       Students have many professors.
       Professors have many students.
       Classes can be held in many classrooms.
MS Access vs. MS Excel
   The choice is simple:
       IF you have only one-to-one relationships,
        you need to use MS Excel.
       IF you have one-to-many or many-to-many
        relationships, you need to use MS Access.
What is in an MS-Access file
                     Unless advanced
                     techniques are employed,
                     all entities are stored in
                     one *.mdb file. When
                     running, a locking file
                     (*.ldb) is also visible.
                     Only the mdb file needs
                     to be copied to transfer
                     the database to another
                     computer or location.

Query Overview - 1
    An MS-Access query is a set of stored SQL
     instructions that manipulate and/or select data
     from one or more tables.

    Select Query – Data grouping and/or filtering
    Make-Table Query – Select + creates/populates
     new table.
    Update Query – Updates fields from specified
     table data
    Append Query – Runs query on one table,
     appends results to a table
    Delete Query – Delete selected records from table
                                            PA Harris, Vanderbilt University
Query Overview - 2
 SQL (Structured Query Language) is a very
  widely used database language designed
  specifically for communicating with databases
 SQL is not proprietary – almost every DBMS
  supports SQL (including MS-Access).
 SQL is relatively easy to learn, but extremely
  powerful – one of the easiest ways to learn is to
  use MS-Access Query by Example methods, then
  look at the generated SQL command
 Remember that a query is nothing more than the
  database engine running the stored SQL command
  (it looks and sometimes acts like a table, but really
  adds little mass to the database file)
One Table Query Example - Live
  Use this
  button to
  between                       Right-Click + Add to add
  design, sheet                 table(s)
  and SQL

  Custom sort
  by one or
  more fields.
                  Drag and Drop Fields
2-Table Query Example - Live
                  Right-Click + Add to add table(s)
                  Note that relationship often automatic.

                              Calculated Field
Drag and Drop Fields
                            BMI: [Weight]/([Height]/100)^2

Right-Clicking gray area
above field enables
property changes.
Query – Calculating Fields

     Name the calculated field, then type a colon, then
     type the equation using brackets ( [ ] ) around table
     fields. If there is ambiguity in the field names
     between tables, you may need to type table.[field]

     Ex: BMI: [Weight]/([Height]/100)^2
Query – Sorting Data

       Choose Ascending or Descending in the Sort Row
       This query would sort by Gender THEN by Race.
                                                             You need not “show” the data field to use as a filter.
Query – Filtering Data

   This query will return all records in the database for:
          who are not white
          whose height are greater than 150 cm
          and who weigh between 60 and 70 kg
Query – Filter Operators
     =             equals
     >             greater than
     >=            greater than or equal
     <             less than
     <=            less than or equal
     <>            not equal to
     Between       between two values
     Is Null       field is empty
     is not null   field is not empty
     Like          Matches a pattern (Like John*)
     OR            Logical OR (one or other is true)
     AND           Logical AND (both are true)
Query – Grouping Data the 1 Button
                          - Totals
                                   Enables Grouping, Counting
                                   and Statistical Options

                                                    Running this
                                                    indicates there
                                                    are 203
                                                    Females and
 Notice new “Total” row.                            261 Males in
 Each field (column) can be set.                    the database.
Query – Grouping Data -2
                    Totals Options Include:
                       Group By
Query – Export Data
      Create and Save

                                 Use OfficeLinks (Excel Toggle
                                 Option) to “Analyze it with Excel”

       Data Automatically
       Exported to Excel
Microsoft Access – Module 4

          Creating / Working with
Graphical User Interface (GUI)

   Although it is possible to enter data directly into a
   table, you can enhance data quality by forcing data
   entry through forms.

   Depending upon your users, you may wish to set
   things up so they never even see the database
   window. In other words, you can design your
   application so they only touch the data through
   programmed forms.
Graphical User Interface (GUI)

   Continuing with the glucose database we
   formulated earlier, we’ll now attempt to build a
   graphical user interface to:

   1) Collect Data
   2) Periodically report data through pre-formatted
   3) Quit the program
GUI – Forms/Report Live

          Out of Program
MS Access – Module 4 Summary
Use forms and reports together to build a data software

Design to the lowest common denominator (Murphy will
use your program early and often)

Always look for and design carrots to win over the true
data entry personnel. If it saves them time or offers
something they couldn’t do before, they might use the

Look for champions – bright, energetic individuals who
will try something new, etc.
MS Access – Resources
I cannot recommend the BEST MS-Access book.
However, I can recommend the following series of books
that I usually turn to when learning new technology:

•Visual Quickstart Series – beginner/intermediate level
•O’Reilly Series – intermediate/advanced level

There is also an excellent tutorial on the web:

Shared By: