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 address book, a telephone directory, a
  timetable, etc.

5
Database Structure
Store
Sort
DATA
Retrieve
6
Database Options

• 1,000s of records
• Freeware/Shareware
• 0 - 200
• 100,000s of records
• MS Access
• 239 - 479
• 1,000,000,000s of records
• Oracle/SQL
• 105 - 40,000

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

8
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

9
An Example of a Table
Fields
Records
10
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?

11
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)

12
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

13
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

14
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.
15
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.
16
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.
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
21
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
22
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.

23
Introducing Access Tools
Tables
Employees
Customers
24
The Access Database Window

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

25
Navigating Fields and Records

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

• Tab
• ShiftTab
• Enter
• ShiftEnter

• Home
• End
• CtrlHome
• CtrlEnd

• Page Up
• Page Down
• The Arrow Keys

First Record
Last Record
Current Record
Previous Record
New Record
Next Record
26
Introducing Tables
Datasheet View
Design View
27
Table Design View
Fieldname
Data Type
Description
Properties
28
Introducing Queries

• A means of asking questions of your database
• Can look across a number of Tables

29
Introducing Forms

• A friendlier view of the database
• Used for data input, menus, display and printing

30
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

31
Lets Create Our Database