Project 1: Intro
Relational Databases

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

A relational database uses several linked tables to hold data rather than a single table. Proper design of the database enables you to enter just once any information that would be tied to multiple records.

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

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.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro Arrow: subtopic open
    Text databases
    Spreadsheet databases
    Flat file databases
    Relational databases
    ExercisesTo subtopics

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports


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.

Assignments Scores Classes/Subjects Students Relationship diagram for tables in LanguageArts database

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

Icon: Trouble 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 Button: Restore image to original size (Internet Explorer) appears, and then just clicking it.

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


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.

Query Design View

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.

Query Datasheet View

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).

Query SQL View

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.