AN JUAN SAILBOAT CHARTERS CASE QUESTIONS San Juan Sailboat Charters (SJSBC) is a

Question

AN JUAN SAILBOAT CHARTERS CASE QUESTIONS San Juan Sailboat Charters (SJSBC) is an agency that leases (charters) sailboats not own the boats. Instead, SJSBC leases boats on behalf of boat owne income from their boats when they are not using them, and SJSBC charges the for this service. SJSBC specializes in boats that can be used for multiday ant owners a fee weekly charters eklychahe The smallest sailboat available ?s28feetín length and t elargestis5ifeeri equipment is vided at the time of the charter. Most of the equipment is provided by the oun some is provided by SJSBC. The owner-provided equipment includes equipment attached to the boat, such as radios, compasses, depth indicators and other instruha tion, stoves, and refrigerators. Other owner provided equipment, such as sails, chors, dinghies, life preservers, and equipment in the cabin (dishes, silverware, cokm utensils, bedding, and so on), is not physically attached to the boat. SJSBC provides sumable supplies, such as charts, navigation books, tide and current tables, soap, dish pto. Each sailboat is fully equipped at the time it is leased. Most of the owners ln els, toilet paper, and similar items. The consumable supplies are treated as equipment SJSBC for tracking and accounting purposes. Keeping track of equipment is an important part of SJSBC's responsibilities. Much df the equipment is expensive, and those items not physically attached to the boat can be ily damaged, lost, or stolen. SJSBC holds the customer responsible for all of the boar' equipment during the period of the charter. SJSBC likes to keep accurate records of its customers and charters, and customers required to keep a log during each charter. Some itineraries and weather conditions more dangerous than others, and the data from these logs provide information about the customer experience. This information is useful for marketing purposes as uating a customer's ability to handle a particular boat and itinerary are Sailboats need maintenance. Note that two definitions of boat are (1) "break out other thousand" and (2) "a hole in the water into which one pours money." SJSBC is quired by its contracts with the boat owners to keep accurate records of all maintenane ctivities and costs

Explanation / Answer

A.

Convert this data model to a database design. Specify tables, primary keys, and foreign keys.

// Creating OWNER table

CREATE TABLE OWNER
(
OwnerID int(25) NOT NULL UNIQUE,

LastName varchar(25),
FirstName varchar(25) NOT NULL,
Address varchar(255),
City varchar(255),
State char(2),

ZIP int (10),

Phone int (12),

Email varchar (100),

BankName char(50),

BankAccoountNumber int(25)

);

Desc OWNER;

Note: The table will be shown all the columns.

//For BOAT table

CREATE TABLE BOAT
(
CoastGuardRegNumber int (25) NOT NULL UNIQUE,

BoatName varchar(255) NOT NULL,
BoatMAKE varchar(255),
BoatModel string(255),
BoatType string(255),
Length int(255),

Beam string (25),

NumberOfBerths int (25),

);

//For creation of Scheduled maintenance table

CREATE TABLE ScheduledMaintenance
(
MaintenanceID int (25) NOT NULL PRIMARY KEY,

Maintenance varchar(255) NOT NULL,
requiredDate date yyyy-mm-dd,
ScheduleDate date yyyy-mm-dd,
CompletedDate date yyyy-mm-dd,
);

//For creation of Equipment table

CREATE TABLE EQUIPMENT
(
ItemIDTabNumber int (25) NOT NULL PRIMARY KEY,

ItemNumber int(255) NOT NULL UNIQUE,

ItemSerialNUmber int (25) UNIQUE,

ItemModel string (25),

NumberOfItems int (25),

Item Cost (25)

);

//For creation of Charter table

CREATE TABLE CHARTER
(
CharterId int (25) NOT NULL PRIMARY KEY,

DepartureDate date yyyy-mm-dd,

ReturnDate date yyyy-mm-dd,

NumberInParty int (5),

BoatCost int (15),

EquipmentCost int (25),

TotalCost int (22),

);

//For creation of Customer table

CREATE TABLE COSTUMER
(
CostomerID int (10) NOT NULL PRIMARY KEY,

LastName varchar(255),
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
State varchar(255),

ZIP int (25),

Phone int (25) NOT NULL,

Email string (25),

BankName varchar(255),

CreditCardNumber int(25) UNIQUE

);

//For creation of Log table

CREATE TABLE LOG

CharterID int (10) NOT NULL UNIQUE,

EntryNumber int (10) NOT NULL,

EntryDate date yyyy-mm—dd,

EntryTime time hh:mm:ss

EntryLocation varchar(255)

Weather varchar (22),

DepartingFrom varchar(50)

SailingTo varchar (50)

);

Note: This is the creation of all tables of database design. You can also insert data into these tables like this.

INSERT INTO Customers (CustomerId, LastName, FirstName, Address, City, State, ZIP, Phone, Email, CreditCardNumber)
VALUES ('121','B’. ‘Tom','Skagen 21','miami','WashingDC','2562001',919837148547, skstz007@xxxx.com, 0000000000);

This will show the entry of your ‘CUSTOMER’ table created. When you will run this command.

Select * from CUSTOMER;

B. Describe how you have represented weak entities, if any exist.

Weak entities are those which are dependent of a particular entity. They are kept NOT NULL, UNIQUE and PRIMARY KEY as required in any particular table.

C. Describe how you have represented supertype and subtype entities, if any exist.

Super-type and subtype entities are kept NOT NULL, UNIQUE and PRIMARY KEY as required in any particular table. For example in customer table ‘LastName’, ‘FirstName’ and ‘CustomerID’ and ‘phone’ are the subtypes of ‘Customer’. ‘CustomerID’ is kept NOT NULL and UNIQUE i.e. entering these with a unique value is a must. The first name and phone number of customer is NOT NULL. Entering these values is a must, but this can duplicate.

E. Document referential integrity constraint enforcement, using Figure 5-29 as a guide.

Parent

Child

Refferential Integrity

CHARTER

LOG

Parent

Child

Refferential Integrity

CHARTER

LOG

• CharterID in CHARTER table must be exist in CharterID in LOG table.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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