CSCI A114 / INFO I111
Lecture Notes One

First semester 2001-2002

Introduction and welcome. Getting started.

This will be a course about databases.

In the simplest sense, a database is a collection of records and files that are organized for a particular purpose. The purpose, in most cases, is data manipulation (storage included), as well as that of generating new data (or information) out of the data that you have been collecting.

One could achieve the same purposes with less sophisticated means than a database management system (DBMS) like Access. Flat files, word processors, directory structures, spreadsheets are actual examples. In some cases the use of these tools is justified. A DBMS is a complicated software system. But here are situations when you need to switch to such a powerful system and accept the overhead of having to learn how it works:

  1. When you have too many separate files or too much data in individual files. This can make the management of data very difficult. Or, when the data exceeds the limits of the software (word processor, spreadsheet, etc) or the capacity of the system memory.

    Data management is the middle name (well, literally almost) of any DBMS.

  2. When you have multiple uses for the data: detailing transactions (invoices, for example), summary analysis (such as quarterly sales summaries), and "what if" scenarios. These are situations when you need to be able to look at the data in many different ways, but you find it difficult to create multiple "views" of the data.

    A DBMS will always provide you with a query language that can help you extract information from the database.

  3. When you need to share the data. For example, when numerous people are entering and updating data and analyzing it. Only one person at a time can update a spreadsheet or a word processing document, but many people can simultaneously share and and update a database table. Also, databases ensure that people reading the data see only committed updates.

    A DBMS will always ensure adequate concurrency control.

  4. When you must control the data because different users access the data, because the data is used to run your business, and because the data is related (such as data for customers and orders). This means you must secure access to data and control data values, and you must ensure data consistency.

    Data security and consistency are natural functions of any DBMS.

Here's a more complete list of benefits of using a DBMS:
  1. concurrency control
  2. recovery (in case of crashes, and data corruption)
  3. storage optimization (thus saving disk space)
  4. caching (for faster access)
  5. data consistency (design methods help eliminate redundancy)
  6. security
  7. data independence (we don't need to know the internals)
  8. higher usability (data can be queried to obtain multiple views)
The presentation in your text and in class will be based on a problem-solving approach. The methodology that we will be using is usually known by the acronym STAIR, which stands for the following steps in the problem-solving process:
    Refinement (step back and evaluate: does it look good, can it be improved?)
   Implementation (use the tools to make the algorithm actually work for you)
  Approach, or algoritm (determine how you will proceed)
 Tools available (identify them)
State the problem (clearly)
Life/business will provide you with problems. The next step is to know your tools. Your lab manual goes through a sequence of 9 (nine) projects, and 2 (two) extra features, each providing you with an example problem and helping you get acquainted with your tools along the way.

The main tool we will be using in the lab will be Microsoft Access. You will also need to develop a different set of tools, more theoretical in nature, that will help you design the solution, but we first need to get used to the tool that will be used to actual implement the solution.

Access is a relational database management system (RDBMS). The relational data model is not the only one in use today, but is the dominant one. We will be touching on other models as well, but in this class the goal will be to get a very good understanding on the relational data model.

Here's some relational database terminology just to get us started:

Relation
Information about a single subject such as customers, orders, golfers, golfer scores, or entertainment groups. A relation is usually stored as a table (with columns) in a relational database management system.

Attribute
A specific piece of information about a subject, such as the address for a customer or the dollar amount of a contract. An attribute is normally stored as a data column, or field, in a table (which implements a relation).

Relationship
The way information in one relation is related to information in another relation. For example, customers have a one-to-many relationship with orders because one customer can place many orders, but any order belongs to only one customer (basically). Entertainment groups might have a many-to-many relationship wight night clubs because each group is interested in working for multiple clubs, and each club will book several groups over time.

Join
The process of linking tables or queries on tables via their related data values. For example, customers might be joined to orders by by matching customer ID in a customer table and an orders table. The purpose of a join is to put together the data (for subsequent querying, in general). This is a more advanced concept that will become clearer later.

I believe that whatever we learn we learn by doing. For this reason the lab is central to this class.

We start with what you will be working on during the lab tomorrow. We duplicate some of the info in the notes for the lab, and from the lab manual. The purpose is to make sure everybody starts at the same time. Subsequent lecture notes will start building the mathematical formalism behind the relational data model. We need that, and you need it too, but for now we return to our first tutorial.

The case perspective follows. The lab notes have detailed steps, and the homework assignment asks you to solve a very similar problem, but entirely on your own. The lecture minute papers will give you a chance to test your understanding of the various concepts and techniques that you encounter in the process. Later on we will encrease the level of complexity both from an implementation point of view as well as from a theoretical point of view.

This tutorial will give you a chance to familiarize yourself with the main components of Access:

Plus, of course, the rich user interface of Access.

And now, let's get started.

CASE PERSPECTIVE

With the popularity of water sports increasing, the number of recreational boaters has risen dramatically! Boats come in all shapes and sizes and are often stored at a marina.

Larger, full-service marinas typically have a service department. The department performs the requested work, such as engine repair, and bills the owner's account. Smaller marinas usually cannot provide on-site service departments, but can offer the same convenience to boat owners by contracting with Bavant Marine Services. Bavant sends a technician to perform the required labor and bills the marina.

To ensure operations run smoothly, Bavant Marine Services needs to maintain data on its technicians and their assigned marinas. Bavant wants to organize the data in a database, managed by a database management system such as Access. In this way, Bavant can keep its data current and accurate while management can analyze the data for trends and produce a variety of useful reports. Your task is to help Bavant Marine Services in creating and using their database.

1. Bavant Marine Services Database

The term database describes a collection of data organized in a manner that allows access, retrieval, and use of that data. A database management system, such as Access, allows you to use a computer to create a database; add, change, and delete data in the database; sort the data in the database; retrieve data in the database; and create forms and reports using the data in the database.

In Access, a database is a collection of tables. Here is a sample database for Bavant Marine Services. It consists of two tables. The Marina table contains information about marinas that Bavant Marine Services provides services for. Each marina is assigned to a specific technician. The Technician table contains information about the technicians to whom these marinas are assigned.

Marina table
Marina Number Name Address City State Zip Code Warranty Non-warranty Tech Number
AD57 Alan's Docks 314 Central Burton MI 49611 $1,248.00 $597.75 23
AN75 Afton's Marina 21 West 8th Glenview MI 48121 $1,906.50 $831.25 36
BL72 Brite's Landing 281 Robin Burton MI 49611 $217.00 $0.00 36
EL25 Elend Marina 462 River Torino MI 48268 $413.50 $678.75 49
FB96 Fenton's Boats 36 Bayview Cavela MI 47926 $923.20 $657.50 23
FM22 Fedder Marina 283 Waterfront Burton MI 49611 $432.00 $0.00 36
JB92 JT Boat Club 28 Causeway Torino MI 48268 $0.00 $0.00 36
NW72 Nelson's Wharf 27 Lake Masondale MI 49832 $608.50 $520.00 23
SM72 Solton's Marine 867 Bay Ridge Glenview MI 48121 $462.50 $295.00 49
TR72 The Reef 92 East Bay Woodview MI 47212 $219.00 $0.00 36

The rows in the tables are called records. A record contains information about a given person, product or event.

A row in the Marina table, for example, contains information about a specific marina. The columns in the table are called fields.
A field contains a specific piece of information within a record. In the Marina table, for example, the fourth field, City, contains the city where the marina is located.
The first field in the Marina table is the Marina Number. This is a code assigned by Bavant Marine Services to each marina.
Like many organizations, Bavant Marine Services calls it a number although it actually contains letters. The marina numbers have a special format. They consist of two uppercase letters followed by a two-digit number.
These numbers are unique; that is, no two marinas will be assigned the same number. Such a field can be used as a unique identifier. This simply means that a given marina number will appear only in a single record in the table.
Only one record exists, for example, in which the marina number is BL72 (although there may be more than one marina called Brite's Landing just as there are three Papa John's stores in town.) A unique identifier is also called a primary key.
Thus, the Marina Number field is the primary key for the Marina table. Indeed it is. What's the Warranty field used for?

The Warranty field contains the amount billed to the Marina that should be covered by the boat owner's warranty. The Non-warranty field contains the amount that is not covered by warranty. For example, marina AD57 is Alan's Docks. It is located at 314 Central in Burton, Michigan. The zip code is 49611. The marina has been billed $1,248.00 that should be covered by warranty and $597.75 that will not be covered by warranty.

Each marina is assigned to a single technician. The last field in the Marina table, Tech Number gives the number of the marina's technician. The first field in the Technician table, also called Tech Number is the number assigned by Bavant Marine Services to the technician. These numbers are unique, so Tech Number is the primary key of the Technician table.

Technician table
Tech Number Last Name First Name Address City State Zip Code Hourly Rate YTD Earnings
23 Anderson Trista 283 Belton Port Anton MI 47989 $24.00 $17,862.00
36 Nichols Ashton 978 Richmond Hewitt MI 47618 $21.00 $19,560.00
49 Gomez Teresa 2855 Parry Ashley MI 47711 $22.00 $21,211.50

The other fields in the Technician table are self-explanatory with the exception, perhaps, of YTD Earnings; this field contains the total amount that has been paid to the technician for services, so far, this year. For example, technician 23 is Trista Anderson. She lives at 283 Belton in Port Anton, Michigan. Her zip code is 47989. Her hourly billing rate is $24.00 and her YTD earnings are $17,862.00.

The tech number displays in both the Marina table and the Technician table. It is used to relate marinas and technicians. For example, in the Marina table you see that the tech number for marina AD57 is 23. To find the name of this technician look for the row in the Techician table that contains 23 in the Tech Number field. Once you have found it, you know the marina is assigned to Trista Anderson.

To find all the marinas assigned to Trista Anderson look through the Marina table for all the marinas that contain 23 in the Tech Number field. Her marinas are:
  • AD57 (Alan's Docks)
  • FB96 (Fenton's Boats), and
  • NW72 (Nelson's Wharf)
Together with the management of Bavant Marine Services, you have determined the data that must be maintained in the database is that shown above. You first must create the database and the tables it contains.
In the process you must define the fields included in the two tables, as well as the type of data each field will contain. You then must add the appropriate records to the tables.
You also must print the contents of the tables. Finally you must create a report with the Marina Number, Name, Warranty, and Non-warranty fields for each marina served by Bavant Marine Services.
Other reports and requirements for the database at Bavant Marine Services will be addressed with the Bavant Marine Services management in the future. Very good. Let's get started on what's needed thus far.

2. Starting Access and Creating a New Database

In Access, all the tables, reports, forms, and queries that you create are stored in a single file called a database. Thus before creating any of these objects, you must first start Access and create the database that will hold them. Step by step instructions on how to create this are given in the lab notes.

3. Creating a Table

An Access database consists of a collection of tables. Once you have created the database, you must create each of the tables within it. In this project you must create both the Marina and Technician tables.

To create a table, you describe the structure of the table to Access by describing the fields within the table.

For each field you indicate the following:

  1. Field name - Each field in the table must have a unique name.

  2. Data type - Indicates to Access the type of data the field will contain.
    The data types we will use in this project are:

    • Text - The field can contain any characters
    • Number - The field can contain only numbers
    • Currency - The field can contain only dollar amounts

  3. Description - Access allows you to enter a detailed description of the field.

You also must indicate which field or fields make up the primary key; that is, the unique identifier, for the table.

In our example they are Marina Number (for Marina) and Tech Number (for Technician).

The field names, data types, field widths, primary key information, and descriptions for the Marina table are shown below.

Structure of Marina table
Field Name Data Type Field Size Primary Key? Description
Marina Number Text 4 Yes Marina Number (Primary Key)
Name Text 20 Marina Name
Address Text 15 Street Address
City Text 15 City
State Text 2 State (Two-Character Abbreviation)
Zip Code Text 5 Zip Code (Five-Character Version)
Warranty Currency Current Warranty Amount
Non-warranty Currency Current Non-warranty Amount
Tech Number Text 2 Number of Marina's Technician

With this information you are ready to begin creating the table.

Again, detailed instructions appear in the lab notes. In this project you create a database using the design and datasheet views. To this end you start Access, create a new table (Marina), define its structure, and enter the data into the table. You then define a second table (Technician), define its structure, and enter the data. You will then preview and print the contents of the table(s), use a form to view data, and will create a report. In the process you should be keeping an eye on the homework assignment and minute paper quizzes to make sure they will be easy to solve (as they are supposed to reinforce everything that you learn in the project).

Here's the structure of the Technician table:

Structure of Technician table
Field Name Data Type Field Size Primary Key? Description
Tech Number Text 2 Yes Technician Number (Primary Key)
Last Name Text 10 Last Name of Technician
First Name Text 8 First Name of Technician
Address Text 15 Street Address
City Text 15 City
State Text 2 State (Two-Character Abbreviation)
Zip Code Text 5 Zip Code (Five-Character Version)
Hourly Rate Currency Hourly Rate of Technician
YTD Earnings Currency YTD Earnings of Technician

See you in lab.

Last updated: August 28, 2001 by Adrian German for A114/I111