Transcript PPT Presentation

Introduction to Access
and Databases
By:
Dr. K.V.Vishwanatha
Professor, Dept. of CSE,
R.V.C.E, Bangalore
Topics


What is a relational database?
Components of a database:





Tables
Queries
Forms
Reports
When to use databases / spreadsheets
Relational databases
Access is a program for creating and
managing relational databases.


Database: store of information
Relational: relationships between
pieces of data are stored
A non-relational database
STAFF
Manager
Phone Location Sex
Name
DOB
Role
Alan Spencer
5/2/55
Administrator Colin Morton 4242
G23
M
Sarah Supton
3/12/70 Administrator Alan Spencer 2487
G23
F
Leanne Thurr
3/1/71
Teaching
Assistant
Colin Morton 9210
111
F
Colin Morton
1/1/40
Dean
-
4297
323
M
A relational version
RoleID
1
2
3
ROLES
Title
Administrator
Teaching Assistant
Dean
STAFF
AnnualLeave
1
3
90
LOCATIONS
LocationID LocationName
01
G23
02
111
03
323
ID
Firstname Lastname
Role Manager DOB
Phone Location Sex
1
2
3
4
Alan
Sarah
Leanne
Colin
1
1
2
3
4242
2487
9210
4297
Spencer
Supton
Thurr
Morton
4
1
4
4
5/2/55
3/12/70
3/1/71
1/1/40
01
01
02
03
M
F
F
M
What tables do you need?

Split data into “subject areas”, eg:
Rooms, Staff, Students, Events, Committees

Each subject is a potential table

Identify how the facts (data) you want to store relate
to the subjects (tables)
If the data cannot be thought of as a characteristic of that table it
may need to be assigned to a new table.
The Access environment


A lot like other Microsoft Office software
(menus, toolbars, etc)
“Database window” – the central point for
your database
Databases are on disk!
Document
in memory
CREATE/
EDIT
Word or
Excel
program

SAVE
Hard
disk
Access
program
CREATE/
EDIT/ SAVE
Must choose a file location before
working on your database
Hard
disk
Tables


Tables hold all the information in Access.
Similar to a “list” in Excel – a grid of
information.
A field (the name of this field is
“PRODUCT DESCRIPTION”)
A record (this
record describes
black ring
binders)
Creating a table
Either:
1. Import from Excel or other source
(File > Get External Data > Import)
2. Create new within Access
(Insert > Table, or choose the “New” option on the database
window)
Keys



One field in a table is normally chosen
to be the key
Key must be unique to each record – for example,
your payroll number or a student’s UCAS number
Access uses the key to perform some of its
operations, such as...
Table relationships
“Students” table:
StudentID LastName
FirstName Title
TutorID
16012
31526
89430
48612
Praveen
Alan
Peter
John
0002
0003
0003
0001
Johnson
Hemming
Pike
Turbot
“Staff” table:
StaffID
LastName
FirstName Title
0001
Smith
Alice
Ms
0002
0003
Kerzby
Prentice
Honor
John
Ms
Mr
Ms
Mr
Mr
Mr
Table relationships

The “Relationships Window”
(Tools > Relationships)
Queries



Queries look like tables
They are used to get data from tables
according to specific needs, eg: address lists;
end-of-year marks
“Dynamic”: when the table is updated, the
query is updated
Forms




Used for entering information into tables
Not “necessary” – but useful
Flexible formatting
Can help ensure that you’re entering the right
data
Forms
UCAS form
Form in admissions database
with the same layout
Reports



Reports make the information “presentable”:
printed reports,
labels, charts…
Can present information from Tables
and/or Queries
Dynamic (like Queries) – report will update
whenever the underlying data updates
Exporting information
You can:
 Export to Excel
(File > Save As/Export)
N.B. This will export a Table or Query – not the
whole database!

Save As HTML for the web
(File > Save as HTML)
Starting a new database



Plan the information that’s going to be in it,
and the uses of it (pencil & paper) – include
everything you can think of!
Create database
Enter information
When to use databases
Use a database:
 To store complex information
 When the information will need to be
used/presented in a variety of ways
Remember:
 Databases can be complex and difficult to set up
properly
 Avoid re-inventing the wheel...
When to use spreadsheets
Use a spreadsheet:
 To maintain a small, simple set of information
 To perform numerical analyses (formulae, functions)
 To create graphs & charts quickly