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:
- Each Record is in a row (horizontal)
in a table.
Create a table - Open up your database; select New; select "Design View"; select OK
Table Design Window:
Field Data Types -
*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)
on Database window:
Database window command buttons:
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 >
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.