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 them, 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. Most of the equipment is provided by the owners, but some is provided by SJSBC. The owner-provided equipment includes equipment that is attached to the boat, such as radios, compasses, depth indicators and other instrumentation, stoves, and refrigerators. Other owner-provided equipment, such as sails, lines, anchors, dinghies, life preservers, and equipment in the cabin (dishes, silverware, cooking utensils, bedding, and so on), is not physically attached to the boat. 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 customer responsible for all of the boat's equipment during the period of the 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 provide 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. Note that 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 data model of a proposed database to support an information system for SJSBC is shown in Figure. Note that, because the OWNER entity allows for owners to be companies are well as individuals, SJSBC can be included as an equipment owner (note that the cardinalities in the diagram allow SJSBC to own equipment while not owning any boats). Also note that
this model relates EQUIPMENT to CHARTER rather than BOAT even when the equipment is
physically attached to the boat. This is only one possible way to handle EQUIPMENT, but it is
satisfactory to the managers of SJSBC.
A. Convert this data model to a database design. Specify tables, primary keys, and foreign keys. Using Figure as a guide, specify column properties.
B. Describe how you have represented weak entities, if any exist.
C. Describe how you have represented supertype and subtype entities, if any exist.
D. Create a visual representation of your database design as a Crow's Foot E-R diagram similar to the one in Figure.
E. Document your minimum cardinality enforcement using referential integrity actions for required parents, if any, and the form in Figure 6-28(b) for required children, if any.