Transcript CGS 2060/2100 Recitation - Florida State University

Databases
& Access 2000
Today’s Topic: Databases
 Define: Database Software
 Examine the different uses of database
software
 Define the four components of database
software:




Tables
Queries
Forms
Reports
Database Software
Software designed to store, sort, sift
through, manipulate, and display
large amounts of data.
Database Software
 A Database table looks very similar to a
spreadsheet, it has rows and columns, and
data is entered into cells, but databases have
a different purpose.
Database Software
Spreadsheet software is
used to store and run
calculations on numeric
data
Jane Strickland's
Income
Jan
Wages
$
699.00
Loans
$
260.00
Misc.
$
Monthly Totals
$
Yearly Total
959.00
$11,678.00
Database software is used
for storing and
manipulating large amounts
of data of any kind
Postal Code
Country
Phone
EC1 4SD
UK
(171) 555-2222
70117
USA
(100) 555-4822
48104
USA
(313) 555-5735
100
Japan
(03) 3555-5011
33007
Spain
(98) 598 76 54
545
Japan
(06) 431-7877
3058
Australia
(03) 444-2343
M14 GSD
UK
(161) 555-4448
S-345 67
Sweden
031-987 65 43
Consider This….
 What software would logically be used to
store the following data:
FSU’s Schedule of Classes
Spreadsheet
 Database
Financial Aid Reports
 Spreadsheet
Database
Amazon.com’s product
catalogue
Spreadsheet
Student Transcripts & GPA’s
 Spreadsheet
 Database
 Database
Solution Not Always Apparent
 Often times users will transfer data between a
spreadsheet and database to accommodate different
needs.
The registrar might
use a spreadsheet to
calculate a student’s
GPA
Then transfer the
results to the
student’s database
record.
Database Components
Tables: Store the data.
For example, the
Customers table will
store customer-related
data for all customers
MS Access
Object Bar
Database Components
Queries: Allow you to
sift through the data
stored in Tables to find
useful information. For
example, you might
query your Customer
Table to find your 10
best customers.
MS Access
Object Bar
Database Components
Forms: allow users to
easily and securely
enter new records into
a Table.
MS Access
Object Bar
Database Components
Reports: provide tools
to create attractive
output from Tables and
Queries.
MS Access
Object Bar
Database Components: Tables
Database Components: Tables
 A collection of data organized to meet user’s
needs.
File or Table
Records
(Entities)
Fields
(Attributes)
Database Components: Tables
 Database Tables are created
and Fields defined using
Design View.
 Database Tables are viewed
and used using Datasheet
View.
Database Components: Tables
Creating a
Table
1. Type field names in
this column.
3. Set one or more
fields to act as the
primary key……
2. Set each field’s data
type by selecting it
from the list.
Database Components: Tables
Primary Keys
 The Primary Key field must
contain unique data for
each record. Data used to
identify that record.
 Example: SSN’s are used
as primary keys in IRS
records
Consider This….
 What data field could be used as primary key
for the following database tables?
Table
A credit card company’s
customer records.
FSU student records
A personal address book
Primary Key
Account number
Social Security Number or
FSUCard Number
Combined first name and last
name
Relationships
 Databases typically
consist of several related
tables. Fields are
selected to define how
the tables are related.
Relationships
 In this example the
CardNumber field is used
to relate these tables so
that the identity of the
customer can be
established.
Relationships
 This is called a one to
many relationship since
the primary key
guarantees that their will
be only one customer with
any specified cardnumber.
Relationships
 …and each customer can
make many –even and
infinite amount of,
purchases.
Database Components: Queries
Database Components: Queries
Select the
table(s) you
wish to query
Creating a Query using
Design View
Database Components: Queries
Creating a Query using
Design View
Select the fields that you
wish to include in the
query along with the
table to which they
belong
Database Components: Queries
Creating a Query using
Design View
Specify the Criteria used
to filter the data. In this
query, only records with
the Course Number
CGS2060 will be
displayed.
Database Components: Queries
Creating a Query using
Design View
Define which field(s)
will be used to sort the
data
Database Components: Queries
Creating a Query using
Design View
Check the fields that you
would like to display in
the query
Database Components: Queries
Viewing the results of a
query with Datasheet
View
Only records for CGS2060 are
displayed.
Database Components: Forms
Database Components: Forms
Forms
provide an
attractive and
secure
interface for
entering data
into a table.
Database Components: Reports
Database Components: Reports
Create a report
to present data
from a table or
query in an
attractive and
meaningful
manner.
Summary
 Tables store the data.
 Queries sort and filter data in one or more
tables
 Forms are used to input data into a table.
 Reports are used to output the data.
 Tables and Queries are best created using
Design View
 Forms and Reports are easiest created using
Wizards
Time to Practice