HW 3 data

SJSBC List

San Juan Sailboat Case Study/SAN JUAN SAILBOAT CHARTERS CASE QUESTIONS1.docx

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 sample list of owners and boats. Your list will be similar in structure to that in Figure 1-35 in text, 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.

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 boat and 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)

D. Create a sample list of owners, boats, and charters. Your list will be similar to that in Figure 1-36 in text. 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.

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)