PURPOSE:

By completing this lab, you will gain hands-on experience working with a relational database created in Microsoft Access.

THE ASSIGNMENT:

Students with WebCT accounts should complete this lab by filling out the Quiz Form for this lab. If you do not have a WebCT account, you should notify your TA so we can get one for you!

If you are completing this lab with WebCT, all answers you record on your lab sheet will be stored on the WebCT server. These will be accessible to you via your WebCT userid and password, regardless of whether you are logging in from home or the computer lab.

If you are unable to access your WebCT account today, you should use a text editor (such as "WordPad" or "NotePad") to record your answers and save them on a diskette, so you may transfer them to your WebCT account later. If you don't have a diskette, you should be able to transfer them to your university web site for later access. If you are also unable to create your university website today, your teaching assistants should be able to save a copy of your work for later retrieval. Finally, if none of these options works, you should record information on a sheet of paper and enter the results into your WebCT account when it is available.

NOTE: Upon leaving the lab, be certain to remove all files containing any personal/account information, logout from any servers that have made use of your password, and close software connected to any of your accounts.

Contents:

Your teaching assistants will arrange you in groups. Your groups will sit together at the work tables in Korman 117 or in close proximity in other labs.

Introduce yourself to your fellow group members. One person should be chosen as this week's scribe, whose machine will be used to prepare a report on your group's activities. Your group will remain together throughout the term (subject to change). You should take turns acting as scribes from week to week.

Reminder: It is important that all group members participate in the lab activities. One student shouldn't hog the machine, or bully the others into doing things a certain way. Try to act professional when working with your partners - treat them with the respect of knowledgeable co-workers on a job.

If you are in Korman 117 (Friday labs):

The scribe should download the for recording your group's responses.

On your lab sheet, record the names of your group members (including yourself!!!) and indicate the name of the scribe.

Some members of your group may not have access to your group area. If this is the case, notify your teaching assistants. In order for your TA's to view files in your group area, it is necessary for you to upload an "index.html" file into your group area containing links to the files. To simplify this process, your instructors have created a standard "index.html" file to be used by each section. This file should not be edited! It displays information (concerning section number, group number, names of group members, and file names) that are read from an accompanying file, "groupWork.js". This file may be edited with a text editor, and contains comments explaining how to customize it for your use (along with some very simple JavaScript code.)

One member of your group (not necessarily the scribe!) should download the file now: , unzip the file "groupWork.zip", edit the config.js file as appropriate for your group by following the directions recorded in the file, then upload both the "index.html" and "config.js" files to your group area. Test config.js by examining your Group Work Area, and modify it as needed. (NOTE: the page you see should contain a banner with your Section and Group numbers, followed by names of team members and file names. The links to files cannot be updated yet because you do not yet have any files uploaded to your group work area.)

At the end of the period, be sure to remember to upload your work onto WebCT, along with your GroupExerciseAnswers.doc file and update your groupWork.js file to link to the files you uploaded.

On your lab sheet, summarize this activity.

Prior to the start of this lab session, you should have completed the Pre-Lab exercise. You should be familiar with the Microsoft Access database software, and the Fizgig example. You should also have brought with you the names of five movies, along with assorted other facts about the movies, for creating a database. Discuss the pre-Lab exercise with your fellow members. On your lab sheet, describe any difficulties you had with the pre-Lab and whether your group members had similar difficulties.

The first part of this lab is a Microsoft Access tutorial, based on the Fizgig database you examined in your Pre-Lab activity. For this part, each of you is to perform the steps of the tutorial individually on your own machine; however, you and your group are to stay synchronized and make sure each of you has completed a step before proceeding to the next step. This is important, because various aspects of this tutorial will be needed for the second part of the lab, which is a group work activity.

A has been created for you using Microsoft Access. Download the sample database by clicking the link.

This is the database you examined in your pre-lab activity. On your lab sheet, summarize the information in the database; that is, describe the tables and what is contained in them: what is this database used for? For now, close or minimize the tables containing the player data and the game data. Only the main window should be open (as seen in the first image above). Select the Players table (by single clicking) so that it is highlighted (but not necessarily open). Then chose the "Design" icon from the menu on this window. Two things should happen:

  1. A new window opens up (the design window).
  2. The icons on the tool bar change!

Opening the design window:
Notice that the tool bar has changed.

Let's experiment with some of the features of the design window. For our first experiment let's examine the "Data Type" column. If you click on one of the entries in that column, say the word "Number" in the first row, a down arrow appears on the right side of the column. Click on the arrow and record the options that appear on your data sheet. Select "Currency", then go back and view the table. To do this you will have to double click on the Player table in the main window. You will be asked to save your work: go ahead. On your lab sheet, describe the changes to the table. When you are done, return to the design window and "correct" your changes. You may wish to experiment by changing the "types" for the other data fields. Locate a "key" symbol on the left hand side of the design table. Right click on the "key" and record the options you see on your lab sheet. Choose the option that pertains to keys. What happens to the key? Right click on a different row and select the "key" option again. What happens? View the table with the player data. How has the table changed? Change the key back to the original field before going any further.

Wait until all group members have reached this point before proceeding. If group members need assistance, you should discuss this with them.

Whoever created this database didn't do a thorough job. It seems that all information regarding one of the players is missing. We need to enter the player's data, along with all the games she played into the database. Here is the data:

Name: Hiroko Trudeau
ID Number: 009-23-8841
Date of Birth: January 22, 1979
College: School of Cajun Transcendentalism
State: Louisiana
Games:
Defeats Farbstein 6 to 2 on July 1
Lost to Lowetwama 3 to 5 on July 2

The easiest way to add data to a table is simply to click on one of blank cells at the bottom on an existing table. After selecting that cell, fill in the relevant data. You can then move (horizontally) to other cells and fill in the remaining information. Try to fill in Hiroko's personal information on the Players table by typing it directly into the cells.

Typing in information for Hiroko Trudeau
Another way to do this is by use of a form. Microsoft Access automatically creates an easy-to-use dialogue form for entering data into the tables. Close the Players table, open the Games table, and click on the Form button: . When you do, a box comes up that facilitates data entry.

Using a form to enter Game information

When you are done, close all tables. Notice that the tool bar keeps changing depending upon what windows you are looking at. At this stage, only the main window should be open (as in the first picture above). We need to pick an option off the tool bar, so it's important that we have the correct window active in our screen. Scan the toolbar for the Relationships button: , and click on it. When you do, a new window appears.

Opening the relationships window:
The tool bar has changed again.

In this window, we can see the relationship between the data in the two tables. The data in the Player1 field of the Games table is the LastName of some player stored in the Players table. Recall that when we clicked on the "plus sign" in Section II above, we accessed game information for the player. That plus sign represents the relation shown here.

You might also recall that the relation was flawed. By clicking on the plus sign we only saw some of the games that the person played. Now we know why. The relation in the database is only between the player's last name and the Player1 field of the Game table. We also want a relationship between the player's last name and the Player2 field of the Game table. Double click on the relationship between the two tables to bring up the Edit Relationships box. You have to click on the actual line that joins the two boxes, not either of the fields! Our goal is to have both the Player1 and Player2 fields related to the LastName field in the Players table. To do this, simply click in the space underneath the existing relationship. When you do, a down arrow appears. By clicking the down arrow, you are given a list of fields to choose from. Chose LastName for the second entry in the left hand column, and Player2 for the right hand column.

Trying to fix our relationship.
Notice that the current relationship is "One-To-Many"

Record on your lab sheet any difficulties you have. When you are done, go back and hunt for the "boxed plus". If you can find it, report on the outcome of clicking. If you can't find it, hypothesize as to why this might be... Return to the main database window, and under Objects choose Queries. A query has already been designed for your use. By double clicking on the query (Query1) the database performs the query in question, and displays the results (try it!). Unfortunately, it's not clear from the results what this query is supposed to do! We have learned (perhaps the hard way) something about computing: always use descriptive names. Our first task is to rename this query to something more appropriate. That is accomplished in exactly the same way as renaming a file in Windows: simply right click on the query. When you do, a list of options appears. Choose to Rename the query. You can now type the new name of the query where the old one used to be. This query is supposed to search through all the games and find the ones played by Anatoly Fitzpatrick. You should name it something appropriate like "Anatoly's Games." On your lab sheet, write down the name you chose for the query. Now we need to fix the query so it does indeed find all of Anatoly's games. To do this, highlight the query (it's probably already highlighted from before) and click on the design button. A new window opens that allows us to create and edit queries.

Editing the query
The edit window shows us two things: the fields that are displayed, and the criteria for displaying the cells. In this case, the fields are all the fields from the Games table. These fields are listed across the top row of the query. You should experiment with those later on your own. For now, we want to fix the problem with this query. The query doesn't work properly because the criteria are not set correctly. According to the Criteria row of the query, data is displayed whenever Player1 is "Fitzpatrick." (Look at the Criteria listing in the Player1 column). Look carefully at the left hand side of the query. Underneath the word Criteria: is the word Or:. In this row, we can enter additional criteria that the query can use to determine the data that is displayed. In our case, we want to display the games when Player1 is Fitzpatrick, or Player2 is Fitzpatrick. Do you think you know what to do? The picture below should give you a big hint! Record your answer on your lab sheet.

Adding another criterion to the query
After you make your changes, execute your query (by double clicking on it in the main window, as above) to make sure it runs properly. On your lab sheet, record Anatoly's games. The last thing we want to do is examine the SQL code for our query. From the Design window for the query, chose the menu option View->SQL View. When you do, Access will show you the SQL code for this query. Record the code on your lab sheet, then try to answer the following questions:

Most people prefer not to look at the SQL code if they can possibly avoid it. However, Access allows for the inspection and manipulation of the SQL code. Simply put, the Design interface makes simple (common) queries easy, but lacks the power to handle all possible queries. For more difficult queries, it may be necessary to work directly with the SQL. Ponder this: is it possible that in the future no one will ever work directly with SQL, merely some other interface?

A database allows us to manage information quickly and effectively. In this lab we have examined:

In addition to covering some ad hoc material on databases, we have also encountered some other important concepts relating to the field of computer science:

  1. the practice of useful naming: the tables in our database were well name; that is we had a good idea what the table contained just by looking at the names. The query was poorly named. Not only could we not tell what it did by reading its name, but we might not have been able to tell what it was trying to do by watching it in action. A good name can save future users of our database/software/product hours of puzzling.
  2. a layer of abstraction: a layer of abstraction is an attempt to remove the user from the inner workings of the computer. In this case, we have encountered at least two layers of abstraction. The first is the Form that Access automatically creates that allows us to enter data in the table. The form is a layer between us and the actual implementation (in this case the table). We encountered another layer of abstraction between ourselves and the SQL code. Even though the code is there, the Design window for queries removed us from the implementation. Can you find other layers of abstraction? It should be relatively easy once you consider that the computer operates on a sequence of ONs (electricity through a component) and OFFs (no electricity through a component).
END OF TUTORIAL PORTION

Your group is to create a film archive, made up of data you collected in your pre-lab activity. You should each have the names of 5 films, along with the name of the director, year of release, and names of actors and actresses in the film. You should have at least two films with the same director, and at least two films that have the same actor or actress in them.

You are to complete as much of this exercise as is possible in the lab period. You are not required to continue the exercise after lab has ended, however recognize that your group's lab grade will be based on how much you complete.

Before entering any information into the database, you should work together in a design activity to decide on the appropriate form for the database. (If you are in Korman 117, you should use the whiteboard and markers for this; otherwise you should use paper!) Consider the following issues with your database:

A good rule of thumb is that information should not be duplicated in a database; that is, if there is information that appears more than once (for instance, an actor's name), it should not be entered as data into the database each time. Rather, it should be stored in a table and referenced each time it appears. The main reasons for this are that it is wasteful of space to re-enter the same information multiple times, and if a mistake is made in entering the data, it is possible that the multiple entries will not all contain the same information.

As a group, decide the following:

Draw a diagram showing the relationships between tables.Show your teaching assistant that you have completed this step.

Label each relationship as one-to-one, one-to-many, or many-to-many. The following explanation of these terms appears in the Microsoft Access online help:

In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table.

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table.

Create your database, build the tables, and establish the relationships you identified above. You may find it useful to consult the Microsoft Access online help. On completion, show your teaching assistant that you have completed this step and indicate this on your lab sheet.

Create a form for entering the data you collected into the database. On completion, show your teaching assistant that you have completed this step and indicate this on your lab sheet.

Create a report for displaying the data collected in the database. On completion, show your teaching assistant that you have completed this step and indicate this on your lab sheet.