CPSC 203Introduction to ComputersT97By Jie (Jeff) Gao

4 October 2005 CPSC 203 Tutorial by Jie Gao 2

Introduction to Database• What is a database?

– A database is a collection of information related to a particular subject or purpose.

• Why should we use databases?– Keep consistent information altogether– Easy to manage and find useful information– Easy to manage related information

4 October 2005 CPSC 203 Tutorial by Jie Gao 3

An ExampleDept Course Year Semester Students

CPSC 203 05 Winter 571

CPSC 231 04 Fall 187CPSC 331 05 Fall 94

MATH 249 05 Winter 76

MATH 353 05 Winter 25

PHYS 225 04 Winter 129

4 October 2005 CPSC 203 Tutorial by Jie Gao 4

Terminology (1)Table

Dept Course Year Semester StudentsCPSC 203 05 Winter 571CPSC 231 04 Fall 187CPSC 331 05 Fall 94MATH 249 05 Winter 76MATH 353 05 Winter 25PHYS 225 04 Winter 129

Field

Record

4 October 2005 CPSC 203 Tutorial by Jie Gao 5

Terminology (2)• DBMS

– Database Management System• Database Objects in Access

– Table– Query– Form– Report– Pages……

4 October 2005 CPSC 203 Tutorial by Jie Gao 6

Create a Database• In Access, all tables and objects are

stored in a single database file• You need to specify a (new) database file

when starting Access– All data is saved automatically in the

database file• Be careful when you work with database

files on the WebDisk (see details on my website)

4 October 2005 CPSC 203 Tutorial by Jie Gao 7

Creating A Table• Work in Table Design View• Decide what fields to add (columns of the

table)– Field name should tell you something about

the field content– Select appropriate data type for fields– Optionally:

• Required?• Set input mask for fields

• Set primary key• Give the table a name

4 October 2005 CPSC 203 Tutorial by Jie Gao 8

Data Types• Define what is expected in a field• Inputs will be checked against the data

type• Available types:

– Text, Memo– Number– Date/time– Currency– Yes/No– …

4 October 2005 CPSC 203 Tutorial by Jie Gao 9

Primary Key• The primary key is a field (or combination

of fields) in a table• Values in the primary key should be

present and unique in all rows• Primary key is used to index and identify

each row quickly• Use the “key” tool button to specify

primary key

4 October 2005 CPSC 203 Tutorial by Jie Gao 10

Alter A Table• Change in design view

– Everything could be changed• If delete a field, the data in that field will be

deleted

4 October 2005 CPSC 203 Tutorial by Jie Gao 11

Edit Data• Input data in datasheet view• Move your cursor to different records

using the navigator at the bottom of the datasheet view

• Use Tab or Shift+Tab keys to move your cursor to the next or previous field

4 October 2005 CPSC 203 Tutorial by Jie Gao 12

A Little More Work• Sort

– By a field– Ascending or descending

• Data in a table is automatically saved; the layout of a table’s data view should be saved manually

4 October 2005 CPSC 203 Tutorial by Jie Gao 13

Forms in Access• A form in Access is used to provide a

friendly interface to the user– With labels and input boxes to input and/or

view data• The user can input data into a table

through a form created on that table• The user can also navigate data by using

the form

4 October 2005 CPSC 203 Tutorial by Jie Gao 14

Create a Form• Two methods: Wizard or Design View• Form Wizard is easy to use. You can

create a form for a table with simple steps• Design View can provide you more power.

You can implement advanced features!

4 October 2005 CPSC 203 Tutorial by Jie Gao 15

Form Wizard• Select

– Which fields will appear in the formYou can select fields from multiple tables

– Form type (layout)– Style– Name

• You can click “Finish” when you accept the defaults for the following steps.

4 October 2005 CPSC 203 Tutorial by Jie Gao 16

Form Design View• You can

– Add controls to the form– Drag fields into the form– Adjust the form layout– Add header or footer– Even do something like programming

4 October 2005 CPSC 203 Tutorial by Jie Gao 17

Assignment 3• Start with a blank database• Create a table• Create a form on that table• Input data• Create reports

– Create queries when necessary• Refer to the information on my web site

4 October 2005 CPSC 203 Tutorial by Jie Gao 18

Queries in Access• A query in Access is used to filter

records and fields• Only the fields and records which

match the query are returned• The data in a query’s view is from

some table

4 October 2005 CPSC 203 Tutorial by Jie Gao 19

Create a Query (Simple)• Two ways: Wizard or Design View• Query Wizard can only create simple

queries such as select part of the fields to display

• Query Design View provides you the powerful interface to create various queries

• Simple examples

4 October 2005 CPSC 203 Tutorial by Jie Gao 20

What’s in the Next Week• Complex queries• Reports• Detailed assignment explanations• During this week you can work on:

– Exercise with the projects on the Office 2000 book

– Create your table and form– Input data