Transcript Introduction to Database Concepts and Access 2000

University of Florida
Creating a Simple Database with
Microsoft Access 2007
Health Science Center
IT Center – Training
[email protected]
352-273-5051
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
Basic Design Rules

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.
PT ID #
Home Address
987
123 West Main Street, Gainesville, FL 32601
654
456 South 3rd Road, Apt 12, Newberry, FL 32684
321
846 West Newberry Road, Gainesville, FL 32609
You can join fields together in queries, forms and reports.
HSC IT Center Training
University of Florida
Basic Design Rules

Unique Field Names
You also want to be aware of the field names across tables. For
example several tables may use the Field LastName. When you
use those fields in other parts of the database things can become
very confusing very quickly.
DOCTOR TABLE
First Name
Last Name
PATIENT TABLE
Sallye
Shapiro
First Name
Last Name
Samuel
Smith
Annie
Adams
Sidney
Samueson
April
Appleton
Arnold
Arlington
Bobbie
Brown
Butch
Bruce
When these two Last 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.Last Name
HSC IT Center Training
University of Florida
Basic Design Rules

No Calculated or Derived Fields
Calculations - If we give an employee a raise, we will need to
change both the Hourly Rate and the Standard Pay, and we
need to make sure our calculations are correct.
Derivations - Since we have the Dept ID, there is no need to
include the Dept Name, it can be pulled from the Dept Table.
Listing it in both places leads to data entry errors.
Employee
ID Num
Hourly
Rate
Standard
Hours
Standard
Pay
Dept ID
Num
Dept Name
123
$10.00
40
$390.00
12
Editing
789
$10.00
20
$200.00
12
Edit
Access will let you create calculations in queries, forms and reports.
HSC IT Center Training
University of Florida
Basic Design Rules

Unique Records
If you don’t
have unique
records, your
database
can’t tell
which record
you may be
referring to.
LastName
GatorLink Phone
College
Smith
rsmith
3-5051
Pharmacy
Smith
rsmith
273-5051
COP
Smith
rsmith
273-5051
Pharmacy
Thomas
bthomas
392-5555
Medicine
Van Winkle
sleepyguy
846-5656
PHHP
LastName
EmergencyContact
Smith
Mary Anne Smith
HSC IT Center Training
University of Florida
Primary Keys
LastName
GatorLink
Phone
College
Smith
rsmith
273-5051
Pharmacy
Thomas
bthomas
392-5555
Medicine
Van Winkle
sleepyguy
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
LastName
GL ID
Phone
College
Smith
rsmith
273-5051
Pharmacy
Thomas
Bthomas
392-5555
Medicine
Van Winkle
sleepyguy
846-5656
PHHP
While each column in this particular data set has unique data, the
field that will work best for us is GL ID (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
Basic Design Rules

Unique Records
We use the
unique primary
key as our link
between our
tables, this helps
ensure we
connect to the
correct record.
ID
LastName
GatorLink Phone
College
1
Smith
rsmith
3-5051
Pharmacy
2
Smith
rsmith
273-5051
COP
3
Smith
rsmith
273-5051
Pharmacy
4
Thomas
bthomas
392-5555
Medicine
5
Van Winkle sleepyguy
846-5656
PHHP
Emp ID EmergencyContact
2
Mary Anne Smith
HSC IT Center Training
University of Florida
Planning our Patient Table
What have I got?
Name
Date of Last Visit
Address
DOB
Phone
Medications
Age
SSN
Medical Record #
Insurance
Emergency Contact Allergies
Height
Gender
Weight
Age at First Visit
…
HSC IT Center Training
University of Florida
Planning our Patient Table
Does the data belong in this table?
Name
Date of Last Visit
Address
DOB
Phone
Medications
Age
SSN
Medical Record #
Insurance
Emergency Contact Allergies
Height
Gender
Weight
Age at First Visit
HSC IT Center Training
University of Florida
Planning our Patient Table
Smallest Logical Parts
Name First/Last
Date of Last Visit
Address/City/ST/Zip DOB
Phone
SSN
Age
Gender
Medical Record #
Age at First Visit
HSC IT Center Training
University of Florida
Planning our Patient Table
Unique Field Names
Pt First Name
Pt Date of Last Visit
Pt Last Name
Pt DOB Birth Date
Pt Address
Pt SSN Social Sec #
Pt City
Pt Gender
Pt ST State
Pt Age at First Visit
Pt Zip
Pt Phone
Pt Age
Pt Medical Record #
HSC IT Center Training
University of Florida
Planning our Patient Table
No Calculated or Derived Fields
Pt First Name
Pt Date of Last Visit
Pt Last Name
Pt Birth Date
Pt Address
Pt Social Sec #
Pt City
Pt Gender
Pt State
Pt Age at First Visit
Pt Zip
Pt Phone
Pt Age
Pt Medical Record #
HSC IT Center Training
University of Florida
Planning our Patient Table
Primary Key
Pt First Name
Pt Birth Date
Pt Last Name
Pt Social Sec #
Pt Address
Pt Gender
Pt City
(AutoNumber)
Pt State
Pt Zip
Pt Phone
Pt Medical Record #
HSC IT Center Training
University of Florida
Planning our Patient Table
Final Plan
Pt Med Rec #
Pt First Name
Pt Last Name
Pt Gender
Pt Birth Date
Pt Phone
HSC IT Center Training
University of Florida
Let’s Build our Database!
HSC IT Center Training