Transcript HSC IT Center Training University of Florida

University of Florida
Introduction to Database Concepts
and Microsoft Access
HSC IT Center Training
University of Florida
Database Concepts and Access

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 filing cabinet, an address book, a
telephone directory, a timetable, etc.

In Access, your Database is your collection
of related tables
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
Database Options

Freeware/Shareware
- 1,000’s of records

Microsoft Excel
- Limit of 65,536 Rows

Microsoft Access
- 100,000’s of records

Oracle/SQL
- 1,000,000,000’s of records
& allows for Multiple users
HSC IT Center Training
University of Florida
Why Use Access?
 Familiar
look and feel of Windows
 Easy
to start building simple
databases
 Can
 It’s
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

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
You can join fields together in queries, forms and reports.
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

Unique Field Names
Access won’t let you use the same field name twice in one table
but it can become confusing to people doing data entry if you are
not clear. Try to keep a consistent naming convention.
Name
GatorLink Addr1
Addr2
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
Field names can be up to 64 characters long. (see tables documentation for more details)
HSC IT Center Training
University of Florida
Basic Design Rules of Relational Databases

Unique Field Names
You also want to be aware of the field names across tables. For
example several tables may use the Field FirstName. When you
use those fields in other parts of the database things can become
very confusing very quickly.
DOCTOR TABLE
Name
Day Off
PATIENT TABLE
Sallye Shapiro
Thursday
Name
Address
Samuel Smith
Monday
Annie Adams
6831 NW 4th Ave
Sidney Samueson Friday
April Appleton
PO Box 456
Arnold Arlington 234 SE 45th Road
Bobbie Brown
234 Peter Pan Terrace
Butch Bruce
3243 SE 4th Terrace
When these two Name fields are pulled
into the same Query/Form/Report they
will appear with the table name in front
of the field name: Patient Table.Name
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

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
Patients
Appointments
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
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
Data View/Design View
Datasheet View
Design View
HSC IT Center Training
University of Florida
Navigating Fields and Records

To move through records and fields




Tab
Shift+Tab
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


Database is a collection of TABLES
Tables store all the data
HSC IT Center Training
University of Florida
Introducing Queries



A means of asking questions (querying) of your data
Can look across a number of Tables and other Queries
Can perform Calculations and Combine fields
HSC IT Center Training
University of Florida
Introducing Forms



A friendlier view of the database
Used for data input, menus, display and printing
Can perform Calculations and Combine fields
HSC IT Center Training
University of Florida
Introducing Reports
 Output
of information in a printed report
 Allows
you to group and summarize data
 Can
perform Calculations and Combine fields
 Cannot
 Can
Edit Data
Make Labels
HSC IT Center Training
University of Florida
Working Together
Tables
Queries
Employees
Customers
Reports
Forms
Customer
Company Name
Address
City
Telephone
Contact Name
HSC IT Center Training
University of Florida
Let’s Create Our Database
HSC IT Center Training