Overview
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
(data)
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
to:
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
organisations
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
data-gathering
Different parts of a database
Fields
Records
Queries
Reports
Fields
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
suggestions
Records
A simple table showing fields (columns) and records(rows):
And as part of an MS Access database table:
Queries
Queries are the information retrieval
requests you make to the database
Your queries are all about the
information you are trying to gather
Reports
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
on?
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
conditions
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
tool
A powerful tool for managing
information
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:
•Oracle
•mySQL
•SQL Server (Microsoft)
•DB2 (IBM)
•Informix
Definitions
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
another
one-to-many relationships
many-to-many relationships
Examples
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.
Ex.
MSCI_ByrneGuestLecture.mdb
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
toggle
between Right-Click + Add to add
design, sheet table(s)
and SQL
views.
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]
format.
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:
Females
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
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)
etc.
Query – Grouping Data the 1 Button
Clicking
- Totals
Enables Grouping, Counting
and Statistical Options
Running this
Query
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
Sum
Avg
Min
Max
Count
StDev
Var
Query – Export Data
Create and Save
1)
Query
Use OfficeLinks (Excel Toggle
2)
Option) to “Analyze it with Excel”
Data Automatically
3)
Exported to Excel
Microsoft Access – Module 4
Creating / Working with
Forms/Reports
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
reports
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
application.
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
application.
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:
http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html