Project 1: Intro
Relational Databases

All of the tables and other database objects are saved as a single file.

Example: Our example in this lesson is an Access database, LanguageArtsClasses.mdb, that I created when teaching the last 65 days of three 7th grade language arts classes.  The names have been changed to protect the innocent and the guilty! (Yes, it was a long 65 days!) The assignments and grades are real. So much work!

Examples will also be pulled from this database in later lessons. The whole database can be downloaded as part of the resource files.

Example: Relational database for Language Arts classes

Goal of the database: to track assignments and scores in order to calculate the six weeks averages in Reading, Spelling, and English and then the averages for the year for each student.

This database could definitely be improved! But it shows most of the important features of a relational database and what you can do with a database. Most of what I wanted from this database could not be done in a flat file database, or, at least, not without an unreasonable amount of effort.

Tables and Relationships

A relational database includes two or more tables that have a relationship. Two tables are related when they have a field in common.

The shared field is the primary key of one of the tables. A primary key must be a field that is guaranteed to have a unique value for each record in the table. The matching field in the other table is called a foreign key.

Most tables have an automatically assigned number as the primary key. Sometimes you have another choice, like a part number or a serial number.

MS Access shows these relationships visually with lines that connect the related fields. In the diagram of relationships, the primary keys are in bold.

Language Arts database, relationships between tables
Click each table above to see an image of its
datasheet view and its design view (in a new window)

Problem: Popup image is jagged and unclear: Your browser has automatically resized the image to fit the window.
Solution: Return the image to full size by moving your mouse over the image until the image size button appears, and then just clicking it.

The Language Arts Classes database actually has more tables than this, but these are most important ones.

Queries

A query can organize the data from one or more tables. It can sort the records into a particular order or filter to show only some of the record based on criteria. Calculated fields in a query can combine text fields or do math with fields.

Design View

Nowadays queries are often designed visually. You can drag the fields that you want to see in your results from the table to the columns in the Design View. Choose which columns to sort on and add restrictions as criteria. You can even create new fields based on the original ones.

The example query above is a simple one. It selects a few of the fields in the table Students. It creates a new field called Name by concatenating (putting two of more values together to form a single text string) the fields LastName and FirstName. The results are filtered to show only the students in class A. The results are sorted in ascending alphabetical order on the field Name. (Since the field Class is restricted to just class A, the Sort Ascending command for the field Class does not actually change anything.)

Datasheet View

When you run the query, the results show in a datasheet, neatly in order and filtered.

SQL View

Behind the Design View, shown above, is programming code in SQL (Structured Query Language). You can view and edit the actual query code, if you choose. Once you know how to write a query in SQL, it can be faster just to type simple queries (if your fingers will type what your brain meant to type).

Project 2: Access Basics will introduce you to MS Access in detail, so we will not try to work with an actual relational database at this point.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012