Query

CREATE TABLE SCRIPT

CREATE TABLE tbl_customers
(
custssn VARCHAR2(11) CONSTRAINT customers_custssn_nn NOT NULL,
lname VARCHAR2(14) CONSTRAINT customers_lname_nn NOT NULL,
fname VARCHAR2(14) CONSTRAINT customers_fname_nn NOT NULL,
street VARCHAR2 (30) CONSTRAINT customers_street_nn NOT NULL,
city VARCHAR2(14) CONSTRAINT customers_city_nn NOT NULL,
state VARCHAR2(2) CONSTRAINT customers_state_nn NOT NULL,
zip VARCHAR2(5) CONSTRAINT customers_zip_nn NOT NULL,
phone CHAR(12) CONSTRAINT customers_phone_nn NOT NULL,
CONSTRAINT customers_custssn_pk PRIMARY KEY (custssn)
);

CREATE TABLE SailBoats
(
BoatName VARCHAR2(25),
BoatLength NUMBER (10,2),
CONSTRAINT SailBoats_Boatname_pk PRIMARY KEY (BoatName)
);

CREATE TABLE Charters
(
BoatName VARCHAR2(25) ,
PlannedDepart DATE,
CustSSN VARCHAR2(12) ,
ActualDepart DATE,
PlannedReturn DATE,
AcutalReturn DATE,
RentalFee NUMBER(7,2),
LateFee NUMBER(10,2) ,
CONSTRAINT Charters_BoatName_pk PRIMARY KEY (BoatName, PlannedDepart),
CONSTRAINT Charters_BoatName_fk FOREIGN KEY (BoatName) REFERENCES Sailboats(BoatName) ,
CONSTRAINT Charters_CustSSN_fk FOREIGN KEY (CustSSN) REFERENCES Customers(CustSSN),
CONSTRAINT Charters_Date_cc CHECK ((PlannedReturn >= PlannedDepart) AND (ActualDepart >= PlannedDepart) AND (AcutalReturn >= PlannedReturn)),
CONSTRAINT Charters_MinFee_cc CHECK (RentalFee >= 500)
);

CREATE TABLE Crew
(
CrewSSN VARCHAR2(11),
LName VARCHAR2(14),
FName VARCHAR2 (14),
Street VARCHAR2 (30),
City VARCHAR2(14),
State VARCHAR2(2),
Zip VARCHAR2(5),
CONSTRAINT Charters_CrewSSN_pk PRIMARY KEY (CrewSSN)
);

CREATE TABLE Role
(Role_Desc VARCHAR2(20) ,
MinCommision NUMBER (10,2),
MaxCommision NUMBER (10,2),
CONSTRAINT Role_Role_pk PRIMARY KEY (Role_Desc)
);

CREATE TABLE CrewAssignment
(
BoatName VARCHAR2(25) ,
PlannedDepart DATE,
CrewSSN VARCHAR2(11) ,
Role_Desc VARCHAR2(20),
Commission NUMBER(10,2),
CONSTRAINT CrewAssignment_BoatName_pk PRIMARY KEY (BoatName, PlannedDepart, CrewSSN),
CONSTRAINT CrewAssignment_BoatName_fk FOREIGN KEY (BoatName, PlannedDepart) REFERENCES Charters(BoatName, PlannedDepart) ,
CONSTRAINT Charters_CrewSSN_fk FOREIGN KEY (CrewSSN) REFERENCES Crew(CrewSSN),
CONSTRAINT Charters_Role_fk FOREIGN KEY (Role_Desc) REFERENCES
Role(Role_Desc)
);




INSERT DATA SCRIPT


INSERT INTO TBL_CUSTOMERS ( CUSTSSN, LNAME, FNAME, STREET, CITY, STATE, ZIP,
PHONE ) VALUES (
'778-989-654', 'VEN', 'SARA', 'LAKE VIEW STREET', 'CALICORNIA', 'CA', '1111', '92581898988 ');
INSERT INTO TBL_CUSTOMERS ( CUSTSSN, LNAME, FNAME, STREET, CITY, STATE, ZIP,
PHONE ) VALUES (
'987-987-089', 'TIMOTHY', 'BARAK', 'W.H WASHINGTON', 'WASHINGTON', 'DC', '9098', '090977686 ');
commit;

INSERT INTO TBL_SAILBOATS ( BOATNAME, BOATLENGTH ) VALUES (
'FANTASTIC', 5.77);
INSERT INTO TBL_SAILBOATS ( BOATNAME, BOATLENGTH ) VALUES (
'SUPER SPEED', 6);
INSERT INTO TBL_SAILBOATS ( BOATNAME, BOATLENGTH ) VALUES (
'FLY TALKER', 7.9);
commit;

INSERT INTO TBL_CHARTERS ( BOATNAME, PLANNEDDEPART, CUSTSSN, ACTUALDEPART, PLANNEDRETURN,
ACUTALRETURN, RENTALFEE, LATEFEE ) VALUES (
'FANTASTIC', TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '778-989-654'
, TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 500, NULL);
INSERT INTO CHARTERS ( BOATNAME, PLANNEDDEPART, CUSTSSN, ACTUALDEPART, PLANNEDRETURN,
ACUTALRETURN, RENTALFEE, LATEFEE ) VALUES (
'Super Speed', TO_Date( '03/09/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '123-456-7890'
, TO_Date( '03/09/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '03/10/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '03/10/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 1000, NULL);
commit;

INSERT INTO CREW ( CREWSSN, LNAME, FNAME, STREET, CITY, STATE,
ZIP ) VALUES (
'111-565-9983', 'Halsteeder', 'Dylan', '45 East Franklin', 'San Diego', 'CA', '11111');
INSERT INTO CREW ( CREWSSN, LNAME, FNAME, STREET, CITY, STATE,
ZIP ) VALUES (
'990-000-8893', 'Headley', 'Michael', 'XYZ STREET', 'WASHINGTON', 'DC', '67665');
commit;

INSERT INTO ROLE ( ROLE_DESC, MINCOMMISION, MAXCOMMISION ) VALUES (
'CAPTAIN', 350, 500);
INSERT INTO ROLE ( ROLE_DESC, MINCOMMISION, MAXCOMMISION ) VALUES (
'FIRST MATE', 200, 350);
INSERT INTO ROLE ( ROLE_DESC, MINCOMMISION, MAXCOMMISION ) VALUES (
'SEAMAN', 100, 200);
commit;


INSERT INTO CrewAssignment ( BoatName, PlannedDepart, CrewSSN, Role_Desc,
Commission ) VALUES (
'Captains Choice', TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '990-000-8893'
, 'Captain', 415);
INSERT INTO TBL_CREWASSIGNMENT ( BOATNAME, PLANNEDDEPART, CREWSSN, ROLE_DESC,
COMMISSION ) VALUES (
'SUPER SPEED', TO_Date( '03/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '111-565-998'
, 'FIRST MATE', 150);
commit;



SELECT DATA SCRIPT

SELECT * FROM TBL_CUSTOMERS;
SELECT * FROM TBL_SAILBOATS;
SELECT * FROM TBL_CHARTERS;
SELECT * FROM TBL_CREW;
SELECT * FROM TBL_ROLE;
SELECT * FROM TBL_CREWASSIGNMENT;



Refer back to the description and ERD for homework 7. Create two triggers as follows:

A trigger that restricts the commission field in the CrewAssignments table to appropriate values as specified in the roles table. Your trigger should fire on both “insert” and “update” events on the CrewAssignments table.

A trigger that automatically generates a late fee when the ActualReturn date field is updated in the Charters table. The fee should be computed as $100 times the number of days late a boat is returned.