Project 3B Overview | Sailor's Age | Query in Query | Structure Modifications | Export XML

next page

Modifications to the table structure

(14 Catalyst questions on this page)

Imagine that you had developed this database six months ago. Then an accident occurred: 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!

The Boat Club decided the database needed to keep track of what skill level is required to use each boat.

Since the database was already keeping track of the skill level of each sailor, this change would let the BoatClub verify that a sailor who wants to reserve a given boat is sufficiently skilled.

After much discussion, the Boat Club decided that the level of skill is determined by the type of boat and its size. They created categories of boats (which they call boat models) and assigned skill levels to each model.

You put that information in a new table called tableBoatModel

1. Open tableBoatModel. Sort it by boatLength. Hint: Use the sort button!
   
   
2. Go to the Catalyst quiz to answer questions 5 and 6.

We also added a new field, modelID to tableBoats to indicate the appropriate model for each boat. Here is what the relationships display now looks like for this database (you'll see a second new table, called tableLocation, which will be explained later):

3. Go to Catalyst and answer questions 7-11 based on this diagram.
   
   
4. Create queries to answer the following questions. These first 3 require only 1 table (no joins). Between means it doesn't include either 4 or 6.
   
   
   1. Each sailor has a rating based upon his/her skill level. How many sailors have a skill rating between 4 and 6?
      
      
      1. Create a new query and save as query3.
         
         
      2. Go to Catalyst and answer question 12.
         
         
         
   2. What colors are the boats whose names start with the letter "s"?
      
      
      1. Create a new query and save as query4.
         
         
      2. Go to Catalyst and answer questions 13 and 14.
         
         
         
   3. How many boats have names that contain the letter "e" and are pink?
      
      
      1. Create a new query and save as query5.
         
         
      2. Go to Catalyst and answer questions 15 and 16.
         
         
         
5. Create these queries using one or more tables:
   
   
   1. How many 15-foot canoes are there?
      
      
      1. Create a new query and save as query6.
         
         
      2. Answer Catalyst question 17.
         
         
         
   2. How many reservations have been made for boats which have a rating greater than 6?
      
      
      1. Create a new query and save as query7.
         
         
      2. Answer Catalyst question 18.
         

Hint: The query for question 19 may require a query within a query, like we did for sailorAge.

6. Now figure out how to answer the following questions. You can write some queries and/or just look at tables to figure out the answers. It might help to use a query within a query for these queries.
   
   
   1. How many sailors can sail the boat named "Island Time"?
      
      
      1. Create a new query and save it as query8.
         
         
      2. Answer Catalyst Question 19.
         
         
   2. How many boats is the sailor named "David de Berry" rated to use?
      
      
      1. Create a new query and save it as query9.
         
         
      2. Answer Catalyst Question 20.

Next, you'll learn about the new tableLocations...