Skip to main content.

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

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

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

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:

 Key Terms
  • composite primary key
  • data type
  • foreign key {FK}
  • primary key {PK}
  • Universal Modeling Language (UML)
  • 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

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.

 Tuple or Row?

Although the database management field often uses the terms "tuple" instead of "row" and "relation" instead of "table," in this project description we use the terms "row" and "table" because that is what you see used by Access.

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: Create a New 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. Close any open database (select File > Close).
  3. Create a new database (select File > New . . .
    Access provides various methods for creating a new database. For this project, you should select "New Blank database . . ."
  4. A "File New Database" window appears. Select a directory location ("Save in:") and name for your database ("File name:"). For "Save as type:" select Microsoft Office Access Database.
  5. Click on the "Create" button. A new window appears with your file name at the top. See figure 3.23.

    Figure 3.23—Your Database Window

    Screendump of main database window

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

  1. If you have not already done so, download the database file from the course calendar. Save it to your desktop. Two of the three tables have already been created. Create the Sailor table as described below.
  2. Start Access and open your database file.
  3. Select View > Database Objects . . .  > Tables. Your screen will show a window similar to that in figure 3.24.

    Figure 3.24— Viewing Database Tables

    screendump of database table window
  4. Select "Create table in Design view" as shown in figure 3.25.

    Figure 3.25 — Create Table in Design View

    screendump of Create table in Design view window
  5. Double click on "Create table in Design view." 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.
  6. Click on the first row under "Field Name." Enter the attribute name in the cell, such as "sid."
  7. 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.
  8. Repeat steps 5–7 for each of the attributes in the sailor table.
  9. All tables should have a primary key. To add a primary key to this table, select the attribute "sid" then right click your mouse and select primary key. This makes the attribute sid the primary key. Note the key symbol that now appears to the left of the attribute. See figure 3.26.

    Figure 3.26—Completed Sailor Table Dialogue Box

    screendump of completed sailor table dialog box
  10. 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."
  11. To leave the "Design" view, close the window by selecting the X at the top-right of the window (the close button). The "Sailor" table is now listed in the "Table" view (see figure 3.27). To return to Design view, right click the table "Sailor" and select "Design" view.

    Figure 3.27—Table View with "Sailor" Listed

    Table View with "Sailor" Listed

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.

pencilNotes

Note 1—Be sure to select "Text" as the data type for a sailor's name.

Note 2—Select "Currency" as the data type for the "age" attribute and change its "Format" under Field Properties to "General Number." This prevents values that use decimal points from rounding up or down as they do with the "Number" data type.

Note 3—Unfortunately, Access does not allow a composite primary key, that is, a primary key that consists of multiple attributes. This is a problem for the Reservation table. To address this limitation, please create another attribute, such as "rid" for reservation identification, to uniquely identify each attribute in the table.

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

To insert data:

  1. Start Access and open a 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.

    Figure 3.28—Sailor: Table Form

    screendump of Sailor: Table Form
  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 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."