San Juan Sailboat Charters (SJSBC) is an agency that leases (charters) sailboats

Question

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 6-45. 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 6-43 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 6-39.

E. Document your minimum cardinality enforcement using referential integrity actions for required parents, if any, and the form in Figure 6-29(b) for required children, if any.

BOAT SCHEDULED MAINTENANCE OWNER OwnerlD CoastGuardRegNumber BoatName BoatMake BoatModel MaintenancelD CompanyName LastName FirstName Address City State ZIP Phone Email BankName BankAccountNumber Maintenanceltem RequiredDate ScheduledDate CompletedDate Cost o BoatType Length Beam NumberOfBerths CHARTER O CUSTOMER EQUIPMENT CharterID CustomeriD ItemIDTabNumber ItemNumber ItemSerialNumber ItemMake ItemModel NumberOfltems ItemCost DepartureDate ReturnDate NumberlnParty BoatCost EquipmentCost TotalCost LastName FirstName Address City State ZIP Phone Email CreditCardNumber LOG CharterID EntryNumber EntryDate EntryTime EntryLocation Weather DepartingFrom SailingTo FIGURE 6-45 Data Model for San Juarn Sailboat Charters

Explanation / Answer

Part A:

CoastGuardRegNumber

ItemIDTabNumber

OwnerID

MaintenanceID

CoastGuardRegNumber

CustomerID

IntemIDTabNumber

Part B:

In "Log" table attribute "EntryNumber" isWekEntity combined with foreign key CharterID

Part C:

There is no Subtype and Super type entity exists because there is no parent child relationship.

Part D:

figure 6-39 is not available so not able to solve the problem correctly

Part E

figure 6-29(b) is not available so not able to solve the problem correctly

Table name Primary Key Foreign Key Owner OwnerID

CoastGuardRegNumber

ItemIDTabNumber

Boat CoastGuardRegNumber

OwnerID

MaintenanceID

Scheduled_Maintenance MaintenanceID CoastGuardRegNumber Equipment IntemIDTabNumber OwnerID Charter CharterID

CoastGuardRegNumber

CustomerID

IntemIDTabNumber

Customer CustomerID CharterID Log CharterID + EntryNumber CharterID

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.