ITEC 340

Homework 5 & 6

 

San Juan Sailboat charters rents sailboats to customers.  The Rental fee for the boat is negotiated between San Juan and the customer.  A late fee of twice the daily rental rate is charged if the boat is returned late.  Assume for example that a boat is rented for seven days for a total rental fee of $1,400.   If the boat is returned two days late the late fee would be $800.  Customers can choose to sail without a crew or can hire a crew.  The daily commission for crew members is in addition to any rental fee on the boat.

 

Below is a sample schema for San Juan. Here is some sample data for an implementation of the ROLES entity:

 

Role

MinCommission

MaxCommission

Captain

350

500

First Mate

200

350

Seaman

100

200

 

The amounts indicate the minimum and maximum daily commission that a crew member would earn if they are hired onto a charter.

 

Required for Homework 5:

 

Write a single script named h5.sql which creates, constrains, and populates a set of tables that implements the schema below.  Your columns should be of an appropriate type (numeric, date, varchar2)  Your constraints should include all PKs and FKs indicated.   You should also use NN and CC constraints to enforce the following business rules:

 

  1. no customer information can be left blank
  2. the planned return date must be on or after the planned departure date
  3. the actual return date must be on or after the actual departure date
  4. the actual departure date must be on or after the planned departure date (customers pay the full charter price whether they leave on time or not)
  5. The minimum rental fee for any charter is $500

 

Use the data above for your ROLES table. Insert sufficient sample data into the other tables you implement to fully articulate the relationships shown in the ERD. 

 

Place your h5.sql script in an RU05 folder in your ITEC340 dropbox submission folder by 11:30 p.m. on Thursday, October 27th.

 

Required for Homework 6:

 

Implement two triggers.  The first trigger should be named LateFee and should automatically compute and update the Late Fee when an update of the ActualReturn field occurs.  The second trigger, named ComCheck, should fire when a CrewAssignment row is inserted or when the Role or Commission fields of the CrewAssignment table are updated.  The trigger should enforce the rule that a crew members daily commission is between the limits in the ROLES table at the time of the insert of update event.

 

Create two scripts named Trigger1.sql and Trigger2.sql that create these two triggers.  Place these two scripts in an RU06 folder in your ITEC340 dropbox submission folder by 11:30 p.m. on Tuesday, November 1st.