Assignment-ACCESS DataBase

Chapter One –Getting Started Page 12 of 21 ANSWERS TO SAN JUAN SAILBOAT CHARTERS CASE QUESTIONS San Juan Sailboat Charters (SJSBC) is an agency that leases (charters) sailboats.

SJSBC does not own the boats. Instead, SJSBC leases boats on behalf of boat owners who want to earn income from their boats when they are not using the boats themselves, and SJSBC charges the owners a fee for this service. SJSBC specializes in boats that can be used for multiday or weekly charters. The smallest sailboat available is 28 feet in length, and the largest is 51 feet in length.

Each sailboat is fully equipped at the time it is leased. Most of the equipment is provided at the time of the charter. The majority of the equipment is provided by the owners, but some is provided by SJSBC. Some of the owner-provided equipment is attached to the boat, such as radios, compasses, depth indicators and other instrumentation, stoves, and refrigerators. Other owner-provided equipment is not physically attached to the boat, such as sails, lines, anchors, dinghies, life preservers, and equipment in the cabin (dishes, silverware, cooking utensils, bedding, and so on). SJSBC provides consumable supplies such as charts, navigation books, tide and current tables, soap, dish towels, toilet paper, and similar items. The consumable supplies are treated as equipment by SJSBC for tracking and accounting purposes.

Keeping track of equipment is an important part of SJSBC’s responsibilities. Much of the equipment is expensive, and those items not physically attached to the boat can be easily damaged, lost or stolen. SJSBC holds the customers responsible for all of the boat’s equipment during the period of their charter.

SJSBC likes to keep accurate records of its customers and charters, and customers are required to keep a log during each charter. Some itineraries and weather conditions are more dangerous than others, and the data from these logs provides information about the customer experience. This information is useful for marketing purposes, as well as for evaluating a customer’s ability to handle a particular boat and itinerary.

Sailboats need maintenance (two definitions of boat are: (1) “break out another thousand” and (2) “a hole in the water into which one pours money”). SJSBC is required by its contracts with the boat owners to keep accurate records of all maintenance activities and costs.

A. Create a sample list of owners and boats. Your list will be similar in structure to that in Figure 1-30, but it will concern owners and boats rather than owners and pets. Your list should include, at the minimum, owner name, phone, and billing address, as well as boat name, make, model, and length. Chapter One –Getting Started Page 13 of 21 B. Describe modification problems that are likely to occur if SJSBC attempts to maintain the list in a spreadsheet.

Note that owners may own more than one boat. For example, Bill Tulsa owns both Ebb Tide and Seafarer V. If the owner’s phone number changes, this will require changing multiple rows. If the change is made incorrectly, one row can disagree with another. Phone numbers could be entered inconsistently. There is no place to record the owner data if you have no boat owned by him or her.

C. Split the list into tables such that each has only one theme. Create appropriate ID columns. Use a linking column to represent the relationship between a boatand an owner. Demonstrate that the modification problems you identified in part B have been eliminated.

BOAT (BoatID , BoatName, Make, Model, Length, OwnerID) OWNER (OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, Address, City, State, ZIP) BOAT: OWNER:

D. Create a sample list of owners, boats, and charters. Your list will be similar to that in Figure 1-31. Your list should include the data items from part A as well as the charter date, charter customer and the amount charged for each charter. Chapter One –Getting Started Page 14 of 21 E. Illustrate modification problems that are likely to occur if SJSBC attempts to maintain the list from part D in a spreadsheet.

Same as the answer for part B, except it is even worse because it can involve charter items as well.

F. Split the list from part D into tables such that each has only one theme. Create appropriate ID columns. Use linking columns to represent relationships. Demonstrate that the modification problems you identified in part E have been eliminated.

BOAT (BoatID , BoatName, Make, Model, Length, OwnerID) OWNER (OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, Address, City, State, ZIP) CUSTOMER (CustomerID , CustomerName) CHARTER (CharterID , CharterDate, BoatID, CustomerID, Amount) BOAT: OWNER:

CUSTOMER: Chapter One –Getting Started Page 15 of 21 CHARTER: ANSWERS TO GARDEN GLORY PROJECT QUESTIONS Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations. Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance.

Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services.

A. Create a sample list of owners and properties. Your list will be similar in structure to that in Figure 1-30, but it will concern owners and properties rather than owners and pets.

Your list should include, at the minimum, owner name, phone, and billing address, as well as property name, type, and address. PropertyName PropertyType Address OwnerName OwnerPhone BillingAddress Jones Home Residence Elm St E.J. Jones 223-1111 2nd Street Eastlake Office Office Elm St E.J. Jones 223-1111 2nd Street Samish Park East Park City 222-0030 City Hall Westview Park West Park City 222-0030 City Hall See Figure 2-29 for additional data for some of the columns.

B. Describe modification problems that are likely to occur if Garden Glory attempts to maintain the list in a spreadsheet.

If the owner’s phone number changes, this will require changing multiple rows. If the change is made incorrectly one row can disagree with another. Phone numbers could be entered inconsistently (see phone for E.J. Jones, above). There is no place to record the owner if you have no property for him or her