Montecino's CS 103 Course Page

Lecture 9 - Relational Databases (focus - Access 97)

This information on databases is only a broad overview.  It is not meant to give detailed explanations of all Access 97 functions and does not cover all material that may be on an exam.  Please consult your lab book for more detailed information on specific functions.

Database - a collection of data, such as an inventory of a store's goods, an inventory of your CD collection. Excel is a form of database, but not a relational database.

Structured database - information organized into fields, records and files.

Field - contains a single piece of information, for example, a customer's name

Record - contains fields of information about a particular entity, for example a customer's ID, address, phone number, item purchased, cost of item

File - a group of records, compiled in a Table 

Relational database - for ex. Microsoft Access - two or more files treated as a single unit. (For example, a video store keeps a relational database of all of its movies and maintains a record of who checked out what and when it is due to be returned.   Store personnel can call up a particular date and see which videos were returned and which were not.  Store personnel can also check to see if a particular movie is still in stock if a customer requests it.  If the information was not stored in a relational database, the information could not be cross indexed.

Relational database tables - basic building blocks of databases

Forms, reports, queries- all based on table data

Information in Tables is contained in: 
- FIELDS (in columns) The FIELD name (for ex. customers' last names,  address, item in inventory, sales price, etc.) can be up to 64 characters long and can include spaces, BUT spaces can cause problems in some applications.  The default data type is text. Names cannot contain a period (.), and exclamation point (!), brackets ( [] ) , or certain other symbols. (Most databases are case-sensitive, so when you create names you must use the same exact spelling and case when you perform searches.)

- Each Record is in a row (horizontal) in a table.
- Field names are listed in column headings.  Columns are vertical.
Create a new Database- open Access > File menu > NewGeneral (or Databases - to pick from several types) -> Create > type a name in "Filename:" textbox 

Create a table - Open up your database; select New; select "Design View"; select OK

Table Design Window:
- top pane contains the "design grid" (field names, data types, field descriptions)
- bottom pane displays the properties of the selected field. 

Field Data Types
- text (and/or numbers that don't require calculation)
- memo (lengthy text or a combination of text or numbers)
- number (can contain only numeric characters, commas, periods, hyphens)
- date/time
- currency (money values and numeric data used in calculations)
- AutoNumber (used as *primary keys - unique number for each record)
- Yes/No (contain only one of two  values: True or false boolean values)
- OLE objects (documents, graphics, spreadsheets linked or embedded in Access tables)
- Lookup Wizard - (creates a field that allows you to choose a value from another table or a list of values)

*Primary Key - a field or group of fields that uniquely identifies each record.

- Set in design view. (Every table in a relational database should have a primary key.) An AutoNumber field can be designated as the primary key.

Record manipulation tool - at bottom of computer screen. Displays current record as well as no. of records.  Allows you to move between records.

Selecting and deleting records - works like Excel. Use to move, delete, hide, or freeze columns.

Filter to exclude or select data - a way to temporarily hide or view specific data. 

Database Wizard - takes you through the steps of setting up a database

object buttons - Six  (6) Tabs on Database window:
1) Table - stores related data in rows (records) and columns (fields)
2) Query - a question used to data in records
3) Form - Data displayed in fields
4) Report -  printed information, which can include calculations
5) Macro - automates database tasks -often with a single command
6) Module - automates tasks using a built-in programming language

Database window command buttons: Open, Design, New.
Report has "Preview" command; Macro has "run" command.

Review Print, Print Preview, Magnifier, Page Setup, Print properties

Help, help!!!! - Use the Office Assistant/Help button:

Move a Column -Highlight Column at label level , then click and hold down the mouse button.  Drag the column or field to the new position.

Sort - ascending or descending order: A to Z, Z to A

Filter a Table - Open the Table,  Click Records (on the Menu bar) > Filter -> Advanced Filter/Sort >
Clear Grid Button (looks like a freehand "X") on the Toolbar > Double click item in the field list you want to filter; click "criteria" cell  for the field list item you want to filter; press Enter (quotation marks should appear around the item you just typed. 0; click the filter button (a funnel).

To remove the filter and return to all records, click the "funnel" button again .

Remove sort - Click Records > Remove Filter/Sort, or click "funnel."

Find a record in a table - Edit (on toolbar)  > Find.  Type in text or number (can use wildcards).

What is a Query? A query allows you to specifiy criteria to retrieve.  The query result displays only the fields you specified in setting up the query.  For example, if you kept a record of bike tour customers, you might want to create a query that tells you the types of tours and the age of the people who take the tours to get an idea of what type of tour appeals to which age group.

Create a simple Query (specifying criteria to retrieve specific records): Open your database.  Click the Query tab; click "New;" click "Design Wizard" or "Design View;" click OK. Select the Table/s from which you want to get your fields.  Select the fields from the available  fields.

Montecino's CS 103 Course Page