Introduction to Database Concepts and Microsoft Access 2003

1
Introduction to Database Concepts and Microsoft
Access 2003

• Health Science Center
• IT Center Training
• training_at_vpha.ufl.edu
• 352-273-5051

2
Database Concepts and Access 2003

• Introduction
• Database
• Microsoft Access
• Design and Creation
• Plan
• Tables
• Queries
• Forms
• Reports

3
Things to Do

• Contact your customer support
• Backup
• Backup
• Backup

4
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

5
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.

6
Basic Database Concepts

• Table
• A set of related records

• Record

Name Barry HarrisCollege MedicineTel 392-5555

• A collection of data about an individual item

• Field

Name Barry Harris

• A single item of data common to all records

7
An Example of a Table
Fields
Records
8
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
  youll make a mistake
• If you dont plan you will often have to start
  again
• Document what you are doing, will you remember
  what you did in three months time?

9
Questions To Ask Yourself

• What have I got?
• (Inputs)
• What do I want?
• (Outputs)
• What do I need to do to get there?
• (Process)
• How am I going to build it?
• (Application/Program)

10
Database Options

• Freeware/Shareware- 1,000s of records
• Microsoft Excel- Limit of 65,536 Rows
• Microsoft Access- 100,000s of records
• Oracle/SQL - 1,000,000,000s of records
  allows for Multiple users

11
Why Use Access?

• Familiar look and feel of Windows
• Easy to start building simple databases
• Can build sophisticated systems
• Its already on your computer
• True relational database

12
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 Fridays

13
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.
You can join fields together in queries, forms
and reports.
14
Basic Design Rules of Relational Databases

• Unique Field Names

Access wont 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.
Field names can be up to 64 characters long.
(see tables documentation for more details)
15
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.
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
16
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
todays date. However, since todays date is
always changing, this data very quickly becomes
stagnant.
Access will let you create calculations in
queries, forms and reports.
17
Basic Design Rules of Relational Databases

• Unique Records

If you dont have unique records, your database
cant tell which record you may be referring to.
18
Primary Keys
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?
19
Primary Keys
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.
20
Lets Get Started Planning
Patients
Appointments
21
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.

22
The Access Database Window

• Icons down the left hand side provide access to
  all database objects
• Select the object by clicking the icon

23
Data View/Design View
Datasheet View
Design View
24
Navigating Fields and Records

• To move through records and fields
• To move through records

• Tab
• ShiftTab
• Enter

• Home/End
• CtrlHome
• CtrlEnd

• Page Up
• Page Down
• The Arrow Keys

First Record
Last Record
Current Record
Previous Record
New Record
Next Record
25
Introducing Tables

• Database is a collection of TABLES
• Tables store all the data

26
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

27
Introducing Forms

• A friendlier view of the database
• Used for data input, menus, display and printing
• Can perform Calculations and Combine fields

28
Introducing Reports

• Output of information in a printed report
• Allows you to group and summarize data
• Can perform Calculations and Combine fields
• Cannot Edit Data
• Can Make Labels

29
Working Together
Tables
Employees
Customers
30
Lets Create Our Database