Project 3: The Boat Club Database

This project utilizes information you learn in Unit 3 and is worth 150 points.

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

Project 3A: 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. Start Access.
2. Click on the Office Icon
   
   
3. Click on New.
   
   
   
4. Depending on your version of Access, you may be asked whether you want to open a blank database.
   1. Browse to the folder where you wan to store your database. An Explorer window will pop up.
   2. Click the down arrow to change type to Microsoft Office Access Databases (2002-2003)(*.mdb).
   3. For File Name, enter Project3.mdb
   4. Click OK.
      
   5. Click Create.
      

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

1. Notice that the Home tab is selected. Change to the Create tab.
   
2. Choose Table.
   
   
   
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. You should now create tables for "Boat" and "Reservation."

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 the "Sailor" table.

   Table 3.26—Sample Data for "Sailor"

   SID	name	rating	age
   1	Dustin	7	45.0
   2	Brutus	1	33.0
   3	Laura	8	55.5
   4	Andy	8	25.5
   5	Rusty	10	35.5
   6	Horatio	7	35.0
   7	Zelda	10	16.0
   8	Horatio	9	35.0
   9	Amy	3	25.5
   10	Bob	3	63.5

   
   
5. Enter the sample data from table 3.27 into the "Boat" table.

   

   Table 3.27—Sample Data for "Boat"

   BID	name	color
   1	Interlake	blue
   2	Interlake	red
   3	Clipper	green
   4	Marine	red

   Table 3.28— Sample Data for " Reservation"

   RID	SID	BID	date	duration
   1	1	1	10/10/07	60
   2	1	2	10/10/07	60
   3	1	3	10/08/07	60
   4	1	4	10/07/07	90
   5	3	2	11/10/07	90
   6	3	3	11/06/07	60
   7	3	4	11/12/07	90
   8	6	1	9/05/07	60
   9	6	2	9/08/07	60
   10	7	3	9/08/07	60

Project 3B Part 1: 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?
2. How many sailors are named "Horatio"?
3. What is the average age of a sailor?
4. List reservations sorted by duration.
5. How many sailors are between 25 and 35 years old and have a rating 3 or higher?
6. How many times has the boat named Clipper been reserved?
7. List all reservations involving red boats and sailors who are over 30 years old.
8. What days, if any, has Andy reserved the Clipper?
9. Li 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.

Project 3B Part 2: 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 (#2). 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 (left), and the BoatClub was sued. Just two weeks later, another unqualified sailor ran a large boat aground (right). You and the Boat Club want to make sure that never happens again!

During this project part, you will write a Word document and also change the database. Be sure to put your name, student ID number, and lab section on the Word document.

Word Document		Access
1	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.	Make the correction in Access.	10
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 sailors with a high enough rating are able to reserve a particular model of boat. Given this information, do the following:
2A	Propose an extension to the ER model that allows you to represent boat models. Explain how your solution minimizes data redundancy. Discuss any significant benefits or limitations of your solution.	Implement your solution in Access.	25
2B	Write two new questions that you will ask the database about boat models.	Create and implement two queries about boat models.	25
Total			60
2C	For extra credit: Write two new questions that you will ask the database about boat models. The queries must include at least two tables.	Create and implement two queries about boat models.	25
Total with optional extra credit			85

Rubrics

Project 3A Deliverables	Points Possible
Create new database, Project3.mdb	10
Create and modify three tables	15
Data in the three tables	15
Total	40

 

Project 3B Deliverables	Points Possible
In Access:
Create 10 SQL queries.	50
Correct the wrong attribute in Access.	5
Implement your extension in Access.	15
Create two queries about Boat Models.	20
Extra credit: two queries about Boat Models that use at least two tables.	20
In Word or NotePad:
Identify the problematic attribute and explain how you would correct the problem.	5
Propose an extension to the ER model that allows you to represent boat models. Explain how your solution minimizes data redundancy. Discuss any significant benefits or limitations of your solution.	10
Write two new questions that you will ask the database about boat models.	5
Extra Credit: Write two new questions that you will ask the database about boat models.	5
Total	110
Total with Extra Credit	135

Turn-in procedure

You will be submitting your Project 3 files through a Catalyst Collect-It turn-in. This project is not a Web page, so it will not be visible on the Web. For each project part, please turn in the Access database file. For Project 3B turn in the Access database file and the Word document.

Project 3A Online Due Date: Friday, March 7, before 11:00 pm.

• To submit, use the Catalyst Collect-It Turn-in Area on the Course Calendar and select the link for "Project 3A."Turn in your Access database file, including the three original tables from Project 3A Part 1.

Project 3B Online Due Date: Monday, March 17, before 11:00 pm.

• To submit, use the Catalyst Collect-It Turn-in Area on the Course Calendar and select the link for "Project 3B." Turn in your Access database file including
  1. Three tables that you created in Project 3A
  2. Your extension to the database
  3. Twelve queries, two of which are based on the extension to the database
  4. Your Word or .txt file with your answers to Project 3B Part 2, Items 1 and 2