Project 3 (Revised): The Boat Club Database for Access 2007

This project utilizes information you learn in Unit 3 and is worth 150 points. It consists of three parts.

In the three parts of this project, you will

1. implement a database model in Microsoft Access (40 points).
2. write a series of SQL queries for the database (50 points).
3. modify and extend the Entity-Relationship model (60 points).

Objectives

When you successfully complete Project 3 you should be able to

• translate a simple Entity-Relationship model into an Access database;
• insert data into the database;
• query the database using SQL; and
• extend a simple Entity-Relationship model.

Background for the Project

The goal of the "BoatClub" database is to enable members of a boat club to reserve boats for trips lasting several hours. The two major entities are:

• Sailors—members of the boat club who reserve boats; and
• Boats—boats in the club's inventory.

How are these two entities related? In this problem we need to know what boats are reserved by what sailors. Thus, "reservation" is obviously an important relationship in this simple problem. We can model the two entities "Sailors" and "Boats" and the relationship "Reservation" with the Entity-Relationship model shown in figure 3.22.

Figure 3.22—Entity-Relationship Model of BoatClub

The model is expressed in Universal Modeling Language (UML). While other notations could be used, we use UML because of its current popularity. Let's look at this diagram, piece by piece. On the left of figure 3.22 is the "Sailor" entity. It consists of the four attributes shown in table 3.23.

Table 3.23—Attributes of the Sailor Entity

Attribute	Description
SID	A sailor—each sailor is assigned a unique ID
name	The sailor's name
rating	The sailor's rating, ranging from 1 (low) to 10 (high)
age	The sailor's age

On the right of figure 3.22 is the "Boat" entity. Its attributes are shown in table 3.24.

Table 3.24—Attributes of the Boat Entity

Attribute	Description
BID	A boat ID—each boat is assigned a unique ID (painted on the bow)
name	The name of the boat (also painted on the bow)
color	The color of the boat

The notation {PK} is used to denote primary keys. You can see that sid and bid are both primary keys, meaning that values of these attributes can be used to uniquely identify each row of their respective tables.

The "Reservation" table is a little more complex. It consists of the attributes in table 3.25.

Table 3.25—Attributes of the Reservation Table

Attribute	Description
SID	Sailor ID
BID	Boat ID
date	The date and time of the reservation
duration	The expected duration of the reservation in minutes

Notice that the "Reservation" table is a bridge table because it allows data from two other tables to be combined. Recall that these tables, "Sailor" and "Boat," are known as base tables.

The attributes SID and BID are called foreign keys. The notation {FK} is used to indicate this fact. In combination, the attributes "sid," "bid," and "date" make up the primary key for the table. The notation {PK} is used to indicate this. Therefore, "sid" and "bid" are individually foreign keys, plus they are part of the primary key.

Look again at figure 3.22. The label "Makes" tells us how to read the relationship between "Sailor" and "Reservation." Thus, we see that a sailor "makes" a reservation. We can also see that a boat is "allocated" to a reservation.

The * and 1 indicate the multiplicity of the relationships. In short, the model states that a sailor can make many reservations (*) but a reservation involves only a single sailor. Similarly, a boat can be allocated to many reservations, but only one boat is allocated to a particular reservation. In other words, these are one-to-many relationships.

Project Description

Given this Entity-Relationship model, your objectives are to implement a database in Access, insert some data into the database, query the database with SQL, and extend the BoatClub ER model to satisfy a new relationship. Details for completing these objectives are outlined below.

Part A: Implement the BoatClub Model in Access (40 points)

Follow these steps to create the BoatClub database.

1. Create a new database in Access named "BoatClub."
2. Create and modify three tables.
3. Insert data into each of the tables.

Step 1: Open the Database in Access (10 points)

Note that steps may vary slightly depending on what version of MS Access you are using. They should be essentially the same, however. E-mail your instructor or TA if you encounter difficulties.

1. Download the database. Save to Documents.
   
   
2. Start Access.
3. Click on the Office Icon
   
   
4. Click on Open
   
   
   
5. Double-click on the Project3.mdb that you downloaded in Step 1.
   
   

Step 2: Create and Modify Tables (5 points per table for 15 points total)

1. Notice that the Home tab is selected. Click on the Create tab.
   
2. 
   
   
   
3. Click on Table Design.
   
4. Design view provides a table dialog box (refer to figure 3.26 below) for entering information about the attributes (or columns) that make up the table. The two key pieces of information are the names of the attributes, "Field Name," and their "Data Types." The data type states the kind of value for the attribute. For example, the data type for a sailor's name must be text whereas her rating must be a number.
   
   
5. Click on the first row under "Field Name." Enter the attribute name in the cell, such as "sid."
6. Next, click on the cell under "Data Type" and click on the arrow box to see a list of options. Select "Number" from the pull-down menu.
7. Repeat steps 5–7 for each of the attributes in the sailor table.
8. All tables should have a primary key. To add a primary key to this table, select the attribute "sid" then click on primary key. This makes the attribute sid the primary key. Note the key symbol that now appears to the left of the attribute.
   
9. Once you are finished adding attributes, you need to save the table by selecting File > Save. The first time you save a table, Access will ask you to name the table. In this case, name the table "Sailor."
10. Note that after you save the table, you are returned to the Home tab and Datasheet view that allows you to enter data into the table.

This is all you need to know about creating and modifying tables for this project. Tables for "Boat" and "Reservation" have already been created for you.

Step 3: Insert Data (5 points per table for 15 points total)

To insert data:

1. Start Access and open the Project3.mdb database.
2. Select View > Database Objects . . . > Tables.
3. Double click on the table name and the "Sailor: Table" window appears with a tabular form for entering data. See figure 3.28. You can type the values of the attributes into these cells.

   

4. Enter the sample data from table 3.26 into "Sailor". The data has already been entered for you into the "Boat" and "Reservation" tables.

   Table 3.26—Sample Data for "Sailor"

   SID	name	rating	age
   22	Dustin	7	45.0
   29	Brutus	1	33.0
   31	Laura	8	55.5
   32	Andy	8	25.5
   58	Rusty	10	35.5
   64	Horatio	7	35.0
   71	Zelda	10	16.0
   74	Horatio	9	35.0
   85	Amy	3	25.5
   95	Bob	3	63.5

   Table 3.27—Sample Data for "Boat"

   BID	name	color
   101	Interlake	blue
   102	Interlake	red
   103	Clipper	green
   104	Marine	red

   Table 3.28— Sample Data for " Reservation"

   RID	SID	BID	date	duration
   1	22	101	10/10/07	60
   2	22	102	10/10/07	60
   3	22	103	10/08/07	60
   4	22	104	10/07/07	90
   5	31	102	11/10/07	90
   6	31	103	11/06/07	60
   7	31	104	11/12/07	90
   8	64	101	9/05/07	60
   9	64	102	9/08/07	60
   10	74	103	9/08/07	60

Part B: Write SQL Queries (50 points)

Given the database you created, write the SQL queries that answer the following questions. Name your queries according to the question number. (5 points per query)

1. How many reservations are in the "Reservation" table? (Already done for you.)
2. How many sailors are named " Horatio ?"
3. What is the average age of a sailor?
4. Show a listing of reservations sorted by duration.
5. How many sailors are between 25 and 35 years old and have a rating 3 or greater?
6. How many times has the boat named Clipper been reserved?
7. List all the reservations involving red boats and sailors who are over 30 years old.
8. What days, if any, has Andy reserved the Clipper?
9. Show a listing of the average age of sailors by rating level.
10. List the sailors' names and the number of reservations made by each sailor but do not show the cases where only one reservation was made.

Part C: Extend the BoatClub Entity-Relationship Model (60 points)

Imagine that you developed this database six months ago, and have wanted to make one change ever since (see #1 below). You will include it with the additional work the BoatClub has asked you to do. They have asked you to extend the database. The BoatClub rented a boat to a sailor who was unqualified to sail that type of boat. He rammed another boat, and the BoatClub was sued. They want to make sure that never happens again!

1. The first task is something you have been wanting to do since the project finished. One of the attributes in the "Sailor" relation is not specified in a suitable manner because the value would need to be updated for everyone yearly. In short, a better type exists for this attribute. Please identify the problematic attribute and explain how you would correct the problem. (10 points)
2. Boats come in different models and knowing the model of the boat can be important. The BoatClub has asked you to make sure that only those sailors with a particular rating are able to reserve a particular model of boat. Given this information, do the following:
   1. Propose an extension to the ER model that allows you to represent boat models. (5 points)
   2. Implement your solution in Access. (15 points)
   3. Explain how your solution minimizes data redundancy. (5 points)
   4. Create and implement two queries in Access that make use of your extension. (10 points each)
   5. Discuss any significant benefits or limitations of your solution. (5 points)
3. For extra-credit, propose two additional queries that make use of your extension and implement them in your database. The queries need not be related to sailor ratings. The queries can be about anything as long as they make use of the new tables you implemented. (10 points each)

Submit Your Project

To get credit for this project, submit your materials to the Collect-It turn-in area. Your submission should include:

1. an Access database file, including
   1. the three original tables from Part A and the ten queries,
   2. your proposed extension and two new queries, all implemented in the database.
2. a Word file with your answers to Part C, Items 1 and 2.

Deadline

Project 3 is due before 10pm on December 11, 2007.

To submit, use the Assignment Turn-in Area on the Course Syllabus and select the link for "Project 3."

• Click Here to Turn In files for Dave's lab sections AA & AB
• Click Here to Turn In files for Keith's lab sections AC & AD
• Click Here to Turn In files for Sam's lab sections AE & AF