Transcript Introduction to Database Concepts and Access 2000

University of Florida
Introduction to Database Concepts
and Microsoft Access 2003
Health Science Center
IT Center – Training
[email protected]
352-273-5051
HSC IT Center Training
University of Florida
Database Concepts and Access 2003

Introduction
– Database
– Microsoft Access

Design and Creation
– Plan
–
–
–
–
Tables
Queries
Forms
Reports
HSC IT Center Training
University of Florida
Things to Do

Contact your customer support

Backup

Backup

Backup
HSC IT Center Training
University of Florida
What is a Database?

A structured collection of related data

An address book, a telephone directory, a
timetable, etc.
HSC IT Center Training
University of Florida
Database Structure
Store
Sort
DATA
Retrieve
HSC IT Center Training
University of Florida
Database Options

1,000’s of records
– Freeware/Shareware


100,000’s of records
– MS Access


0$ - $200
$239 - $479
1,000,000,000’s of records
– Oracle/SQL

$105 - $40,000
HSC IT Center Training
University of Florida
Data vs. Information


Data – a collection of facts made up of text, numbers
and dates:
Murray
35000
7/18/86
Information - the meaning given to data in the way it is
interpreted:
Mr. Murray is a sales person whose annual salary is
$35,000 and whose hire date is July 18, 1986.
HSC IT Center Training
University of Florida
Basic Database Concepts

Table
– A set of related records

Record
– A collection of data
about an individual item

Name: Barry Harris
College: Medicine
Tel: 392-5555
Name: Barry Harris
College: Medicine
Tel: 392-5555
Field
– A single item of data
common to all records
Name: Barry Harris
HSC IT Center Training
University of Florida
An Example of a Table
Fields
Records
Name
GatorLink
Phone
College
Graff
rgraff
392-3900
Pharmacy
Harris
bharris
392-5555
Medicine
Ipswich
zipswich
846-5656
PHHP
HSC IT Center Training
University of Florida
Design and Document Your Database

A designers best tools are a pencil and paper
– It is important to plan what you are going to do

The sooner you touch the computer the sooner you’ll
make a mistake
– If you don’t plan you will often have to
start again
– Document what you are doing, will
you remember what you did in three
months time?
HSC IT Center Training
University of Florida
Questions To Ask Yourself

What have I got?
–

What do I want?
–

(Outputs)
What do I need to do to get there?
–

(Inputs)
(Process)
How am I going to build it?
–
(Application/Program)
HSC IT Center Training
University of Florida
Why Use Access?
 Familiar
 Easy
 Can
 It’s
look and feel of Windows
to start building simple databases
build sophisticated systems
already on your computer
 True
relational database
HSC IT Center Training
University of Florida
What is a Relational Database?

A relational database is a collection of tables
from which data can be accessed in many
different ways without having to reorganize
the database tables.
–
That is, once relationships are created, tables can
“talk” to each other. We can link (relate) the
tables to find:



Which doctors are seeing a patient
Which students are in which class
Which item is selling the most on Friday’s
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

Unique Field Names
Here we see an Address 1 & 2. One of them should be for
Campus Addresses, and one for Home Addresses, but because of
the vague field names, some data has been entered incorrectly.
Name
GatorLink Address1
Address2
Graff
rgraff
PO Box 100012
Gainesville, FL 32610
123 West Main Street,
Gainesville, FL 32601
Harris
Bharris
PO Box 100234
Gainesville, FL 32610
456 South 3rd Road
Newberry, FL 32684
846 West 52nd Ave
Gainesville, FL 32609
PO Box 100256
Gainesville, FL 32610
Ipswich zipswich
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

No Calculated or Derived Fields
If we wanted to see how long an employee had been working
with us, we can calculate their Length Employed by subtracting
their hire date from today’s date. However, since today’s date is
always changing, this data very quickly becomes stagnant.
Name
GatorLink Date Hired Length Employed
Graff
rgraff
05/15/2000
4 yrs, 2 mo
Harris
bharris
02/08/1986
18 yrs, 6 mo
12/15/1999
4 yrs, 7 mo
Ipswich zipswich
Access will let you create calculations in queries, forms and reports.
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

Data is broken down into Smallest Logical Parts
Putting all of the home address in one field may make for
convenient data entry, but it makes it very difficult to work with
the data. For example, what if I needed to sort by City or Zip
Code? Pulling fields together is fairly simple, pulling them apart
is very difficult.
Name
GatorLink Home Address
Graff
rgraff
123 West Main Street, Gainesville, FL 32601
Harris
Bharris
456 South 3rd Road, Newberry, FL 32684
Ipswich zipswich
846 West 52nd Ave, Gainesville, FL 32609
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

Unique Records
If you don’t
have unique
records, your
database
can’t tell
which record
you may be
referring to.
Name
GatorLink
Phone
College
Graff
rgraff
2-3900
Pharmacy
Graff
rgraff
392-3900
COP
Graff
rgraff
392-3900
Pharmacy
Harris
bharris
392-5555
Medicine
Ipswich
zipswich
846-5656
PHHP
HSC IT Center Training
University of Florida
Primary Keys
Name
GatorLink
Phone
College
Graff
rgraff
392-3900
Pharmacy
Harris
bharris
392-5555
Medicine
Ipswich
zipswich
846-5656
PHHP
To ensure that each record is unique in each table, we
can set one field to be a Primary Key field.
A Primary Key is a field that that will contain no
duplicates and no blank values.
Looking at the table above, what would be the best
Primary Key?
HSC IT Center Training
University of Florida
Primary Keys
Name
GL
Phone
College
Graff
rgraff
392-3900
Pharmacy
Harris
bharris
392-5555
Medicine
Ipswich
zipswich
846-5656
PHHP
While each column in this particular data set has unique data, the
field that will work best for us is GL (GatorLink). Many
employees will work for the same college, have the same last
name and possibly even share telephone numbers, but each
employee should have a unique GatorLink ID.
When there is not a unique field in your data set, you can use an
AutoNumber. Access can create incremented or random
AutoNumbers for your primary key.
HSC IT Center Training
University of Florida
Let’s Get Started Planning
HSC IT Center Training
University of Florida
Starting Microsoft Access

Click on “Microsoft Access”
in the Start menu
Or if you have set up a shortcut on
your Desktop, click on the Access
shortcut icon
HSC IT Center Training
University of Florida
Opening a Database

To open a database when you
start Access
– From the bottom of the left panel,
choose the database you wish to
open, or choose More… to browse
for another database

To Create a database, click on the
Create a new file… option in the
bottom of the left frame.

You can also use the file menu to
open existing databases or create new
ones.
HSC IT Center Training
University of Florida
Introducing Access Tools
Tables
Queries
Employees
Customers
Reports
Forms
Customer
Company Name
Address
City
Telephone
Contact Name
HSC IT Center Training
University of Florida
The Access Database Window

Icons down the left hand side provide access to
all database objects
– Select the object by clicking the icon
HSC IT Center Training
University of Florida
Navigating Fields and Records

To move through records and fields





Tab
Shift+Tab
Enter
Shift+Enter




Home
End
Ctrl+Home
Ctrl+End



Page Up
Page Down
The Arrow Keys
To move through records
First Record
Current Record
Previous Record
Last Record
Next Record
New Record
HSC IT Center Training
University of Florida
Introducing Tables
Datasheet View
Design View
HSC IT Center Training
University of Florida
Table Design View
Data Type
Description
Properties
Fieldname
HSC IT Center Training
University of Florida
Introducing Queries


A means of asking questions of your database
Can look across a number of Tables
HSC IT Center Training
University of Florida
Introducing Forms


A friendlier view of the database
Used for data input, menus, display and printing
HSC IT Center Training
University of Florida
Introducing Reports
– Output of information
from your database in
the form of a printed
report
– Allows you to group
and summarize
information
– Can be previewed to
the screen prior to
printing
HSC IT Center Training
University of Florida
Let’s Create Our Database
HSC IT Center Training