write my assignment 31240
i have created blow a database for a boat rental company, the problem is that i am having issues with my triggers , 1,2 and 3
CREATE TABLE OWNER
(
OWN_ID INT PRIMARY KEY NOT NULL,
OWN_F_NAME VARCHAR(15) NOT NULL,
OWN_L_NAME VARCHAR(15) NOT NULL,
OWN_PHONE INT NOT NULL,
OWN_CITY VARCHAR(15) NOT NULL
);
CREATE TABLE CUSTOMER
(
CUS_ID VARCHAR(5) CONSTRAINT CUS_ID_PK PRIMARY KEY,
CUS_L_NAME VARCHAR(15) NOT NULL,
CUS_F_NAME VARCHAR(15) NOT NULL,
CUS_BALANCE INT NOT NULL,
CUS_PHONE INT NOT NULL,
CUS_CITY VARCHAR(15) NOT NULL
);
CREATE TABLE CREW
(
CREW_ID VARCHAR(5) CONSTRAINT CREW_PK PRIMARY KEY,
CREW_F_NAME VARCHAR(15) NOT NULL,
CREW_L_NAME VARCHAR(15) NOT NULL,
CREW_RATE INT NOT NULL,
CREW_PHONE INT NOT NULL
);
CREATE TABLE EQUIPMENT
(
EQUIP_ID VARCHAR(5) CONSTRAINT EQUIP_PK PRIMARY KEY,
EQUIP_NAME VARCHAR(15),
EQUIP_DESC VARCHAR(50),
EQUIP_QUANTITY INT NOT NULL,
EQUIP_RATE INT NOT NULL
);
CREATE TABLE ROUTE
(
ROUTE_ID VARCHAR(5) CONSTRAINT ROUTE_PD PRIMARY KEY,
ROUTE_NAME VARCHAR(20) NOT NULL,
ROUTE_BEGIN VARCHAR(20) NOT NULL,
ROUTE_END VARCHAR(20) NOT NULL,
ROUTE_RATE INT NOT NULL
);
CREATE TABLE MAINT_FACILITY
(
FACI_ID VARCHAR(5) CONSTRAINT FACI_PK PRIMARY KEY,
FACI_NAME VARCHAR(20) NOT NULL,
FACI_LOCATION VARCHAR(20) NOT NULL,
FACI_PHONE INT NOT NULL,
FACI_RATING VARCHAR(15) CONSTRAINT FACI_RATING_5BEST CHECK(Faci_Rating In (‘1′,’2′,’3′,’4′,’5’))
);
CREATE TABLE BOAT
(
BOAT_ID varchar(5) CONSTRAINT BOAT_PK PRIMARY KEY,
OWN_ID INT CONSTRAINT BOAT_OWN_FK REFERENCES OWNER(OWN_ID),
BOAT_SIZE VARCHAR(15) CONSTRAINT SIZES CHECK (boat_size In (‘LARGE’,’MEDIUM’,’SMALL’)),
BOAT_RATE INT NOT NULL,
boat_availability varchar(3)
);
CREATE TABLE LEASE
(
LEASE_ID VARCHAR(5) CONSTRAINT LEASE_PK PRIMARY KEY,
BOAT_ID VARCHAR(5) CONSTRAINT LEASE_BOAT_FK REFERENCES BOAT(BOAT_ID),
CUS_ID VARCHAR(5) CONSTRAINT LEASE_CUSTOMER_FK REFERENCES CUSTOMER(CUS_ID),
CREW_ID VARCHAR(5) CONSTRAINT LEASE_CREW_FK REFERENCES CREW(CREW_ID),
ROUTE_ID VARCHAR(5) CONSTRAINT LEASE_ROUTE_FK REFERENCES ROUTE(ROUTE_ID),
LEASE_PRICE INT NOT NULL,
LEASE_START_DATE DATE,
LEASE_END_DATE DATE,
RETURN_DATE DATE
);
CREATE TABLE EXPERIENCE_HISTORY
(
EXPERIENCE VARCHAR(15) CONSTRAINT CUSTOMER_EXPERIENCE CHECK (experience IN (‘WORST’, ‘BAD’, ‘FAIR’, ‘GOOD’, ‘EXCELLENT’)),
CUS_ID VARCHAR(5) CONSTRAINT EXPERIENCE_CUS_FD REFERENCES CUSTOMER(CUS_ID),
BOAT_ID VARCHAR(5) CONSTRAINT EXPERIENCE_BOAT_FK REFERENCES BOAT(BOAT_ID),
LEASE_ID VARCHAR(5) CONSTRAINT EXPERIENCE_LEASE_FK REFERENCES LEASE(LEASE_ID),
ROUTE_ID VARCHAR(5) CONSTRAINT EXPERIENCE_ROUTE_fK REFERENCES ROUTE(ROUTE_id),
EXPERIENCE_DESC VARCHAR(40) NOT NULL,
CONSTRAINT EXPERIENCE_PK PRIMARY KEY (CUS_ID,BOAT_ID)
);
CREATE TABLE MAINTENANCE_LOG
(
MAINT_ID VARCHAR(5) CONSTRAINT MAINT_LOG PRIMARY KEY,
BOAT_ID VARCHAR(5) CONSTRAINT MAINT_BOAT_FK REFERENCES BOAT(BOAT_ID),
FACI_ID VARCHAR(5) CONSTRAINT MAINT_FACI_FK REFERENCES MAINT_FACILITY(FACI_ID),
MAINT_DATE DATE,
MAINT_PRICE INT NOT NULL,
MAINT_DESCRIP VARCHAR(50)
);
CREATE TABLE EQUIPMENT_LOG
(
EQUIP_ID VARCHAR(5) CONSTRAINT EQUIP_FK REFERENCES EQUIPMENT(EQUIP_ID),
LEASE_ID VARCHAR(5) CONSTRAINT EQUIP_LEASE_FK REFERENCES LEASE(LEASE_ID),
CONSTRAINT EQUIP_LOG_PK PRIMARY KEY(EQUIP_ID, LEASE_ID)
);
—INSERTING RECORDS INTO OWNER TABLE
INSERT INTO OWNER values (1001,’Farah’,’Ali’,320319549,’saint cloud’);
INSERT INTO OWNER values (1002,’Conor’,’Ronald’,763339568,’sauk Center’);
INSERT INTO OWNER values (1003,’Michael’,’Smith’,911555666,’las vegas’);
INSERT INTO OWNER values (1004,’Tosh’,’bkosh’,455888777,’los angeles’);
INSERT INTO OWNER values (1005,’Olson’,’Bzdok’,811861818,’san francisco’);
–INSERTING RECORDS IN CUSTOMER TABLE
INSERT INTO CUSTOMER VALUES (‘1′,’Aneno’,’Musah’,’50’,’1115656511′,’Denver’);
INSERT INTO CUSTOMER VALUES (‘2′,’Dave’,’East’,’101′,’22555322′,’Minneapolis’);
INSERT INTO CUSTOMER VALUES (‘3′,’Big’,’Abdi’,’200′,’33233331′,’Dallas’);
INSERT INTO CUSTOMER VALUES (‘4′,’Shawn’,’Mendez’,’300′,’44114423′,’Boston’);
INSERT INTO CUSTOMER VALUES (‘5′,’Dale’,’Faai’,’350′,’552645526′,’New york’);
–INSERTING RECORDS INTO CREW TABLE
INSERT INTO CREW VALUES (’11’,’Yaya’,’Ponike’,’20’,’1004441001′);
INSERT INTO CREW VALUES (’22’,’Denis’,’guster’,’30’,’2003322001′);
INSERT INTO CREW VALUES (’33’,’Rahi’,’Abraha’,’30’,’300355445′);
INSERT INTO CREW VALUES (’44’,’Guster’,’Gump’,’40’,’400463502′);
INSERT INTO CREW VALUES (’55’,’Kevin’,’Garnet’,’50’,’500454502′);
–INSERTING RECORDS INTO EQUIPMENT TABLE
INSERT INTO EQUIPMENT VALUES (‘5001′,’SAILS’,’SMALL SIZE’,’100′,’10’);
INSERT INTO EQUIPMENT VALUES (‘5002′,’LINES’,’LONG ONES’,’200′,’20’);
INSERT INTO EQUIPMENT VALUES (‘5003′,’ANCHORS’,’EXPENSIVE’,’542′,’5′);
INSERT INTO EQUIPMENT VALUES (‘5004′,’DINGHIES’,’STRONG’,’45’,’90’);
INSERT INTO EQUIPMENT VALUES (‘5005′,’COMPASS’,’WATER RESISTANT’,’86’,’63’);
–INSERTING RECORDS INTO ROUTE TABLE
INSERT INTO ROUTE VALUES (‘6001′,’palm valley’,’granite rd’,’palm st’,’30’);
INSERT INTO ROUTE VALUES (‘6002′,’greenway village’,’yellow st’,’greenway lane’,’15’);
INSERT INTO ROUTE VALUES (‘6003′,’Lake George’,’division st’,’George lane’,’20’);
INSERT INTO ROUTE VALUES (‘6004′,’mendota heights’,’carson ave’,’mendota ave’,’50’);
INSERT INTO ROUTE VALUES (‘6005′,’pleasant Park’,’parker st’,’pleasant rd’,’60’);
–inserting records into MAINTENANCE FACILITY TABLE —
INSERT INTO MAINT_FACILITY VALUES(‘700′,’Nelsons Shop’,’Mankato’,’566566564′,’2′);
INSERT INTO MAINT_FACILITY VALUES(‘701′,’Ready Shop’,’Minneapolis’,’665665654′,’5′);
INSERT INTO MAINT_FACILITY VALUES(‘703′,’Highway Shop’,’St. Paul’,’775775758′,’4′);
INSERT INTO MAINT_FACILITY VALUES(‘704′,’Stop n Go Shop’,’St. Cloud’,’887887879′,’3′);
INSERT INTO MAINT_FACILITY VALUES(‘705′,’Belevue Shop’,’Brainerd’,’998999987′,’4′);
–INSERTING RECORDS INTO BOAT TABLE
INSERT INTO BOAT VALUES(‘800′,’1001′,’LARGE’,’30’,’no’);
INSERT INTO BOAT VALUES(‘801′,’1002′,’MEDIUM’,’10’,’yes’);
INSERT INTO BOAT VALUES(‘802′,’1003′,’SMALL’,’40’,’no’);
INSERT INTO BOAT VALUES(‘803′,’1004′,’LARGE’,’50’,’yes’);
INSERT INTO BOAT VALUES(‘804′,’1005′,’MEDIUM’,’60’,’no’);
–INSERTING RECORS INTO LEASE TABLE
INSERT INTO LEASE VALUES (‘900′,’800′,’1′,’11’,’6001′,’150′,’10-OCT-2013′,’12-NOV-2013′,’12-NOV-2013′);
INSERT INTO LEASE VALUES (‘901′,’801′,’2′,’22’,’6002′,’150′,’08-OCT-2013′,’22-NOV-2013′,”);
INSERT INTO LEASE VALUES (‘902′,’802′,’3′,’33’,’6003′,’150′,’01-JAN-2013′,’01-FEB-2013′,’25-JAN-2013′);
INSERT INTO LEASE VALUES (‘903′,’803′,’4′,’44’,’6004′,’150′,’01-NOV-2013′,’12-NOV-2013′,”);
INSERT INTO LEASE VALUES (‘904′,’804′,’5′,’55’,’6005′,’150′,’10-OCT-2013′,’20-OCT-2013′,’30-OCT-2013′);
–INSERTING RECORDS INTO EXPEREINCE_HISTORY TABLE
INSERT INTO EXPERIENCE_HISTORY VALUES(‘WORST’,’1′,’800′,’900′,’6001′,’STORMY DAY’);
INSERT INTO EXPERIENCE_HISTORY VALUES(‘BAD’,’2′,’801′,’901′,’6002′,’VERY NICE WEATHER’);
INSERT INTO EXPERIENCE_HISTORY VALUES(‘FAIR’,’3′,’802′,’902′,’6003′,’VERY FRIENDLY CREW MEMBERS’);
INSERT INTO EXPERIENCE_HISTORY VALUES(‘GOOD’,’4′,’803′,’903′,’6004′,’FAIRLY TAKEN CARE OF BOAT’);
INSERT INTO EXPERIENCE_HISTORY VALUES(‘EXCELLENT’,’5′,’804′,’904′,’6005′,’PERFECT CREW’);
–INSERTING RECORS INTO MAINTENANCE_LOG TABLE
INSERT INTO MAINTENANCE_LOG VALUES(‘2000′,’800′,’700′,’22-oct-2013′,’100′,’Painting’);
INSERT INTO MAINTENANCE_LOG VALUES(‘2001′,’801′,’701′,’10-nov-2013′,’50’,’cleaning’);
INSERT INTO MAINTENANCE_LOG VALUES(‘2003′,’802′,’703′,’20-jan-2013′,’60’,’new planks introduced’);
INSERT INTO MAINTENANCE_LOG VALUES(‘2004′,’803′,’704′,’15-aug-2013′,’70’,’new maps ‘);
INSERT INTO MAINTENANCE_LOG VALUES(‘2005′,’804′,’705′,’01-dec-2013′,’80’,’radio fixed’);
–INSERTING RECORDS INTO EQUIPMENT_LOG TABLE
INSERT INTO EQUIPMENT_LOG VALUES(‘5001′,’900’);
INSERT INTO EQUIPMENT_LOG VALUES(‘5002′,’901’);
INSERT INTO EQUIPMENT_LOG VALUES(‘5003′,’902’);
INSERT INTO EQUIPMENT_LOG VALUES(‘5004′,’903’);
INSERT INTO EQUIPMENT_LOG VALUES(‘5005′,’904’);
-trigger 1 , to avoid booking a boat that is already booked and gone
Create Or Replace Trigger Avoid_over_booking
before INSERT ON LEASE
for each row
DECLARE
boat_status VARCHAR2(3);
Begin
Select BOAT_AVAILABILITY
INTO boat_status
FROM BOAT
Where boat_id = :new.boat_id;
DBMS_OUTPUT.PUT_LINE(boat_status);
If boat_status =’no’
Then
Raise_application_error(-20010,’sorry!!! The boat you selected is already booked, try an available boat’);
ELSIF boat_status =’yes’
THEN
UPDATE BOAT
SET BOAT_AVAILABILITY = ‘no’
WHERE BOAT_ID = :new.boat_id;
End if;
End;
/
Show error;
——-trigger 2, calculating final balance after return of the boat
CREATE OR REPLACE TRIGGER Final_Balance
AFTER UPDATE ON LEASE
FOR EACH ROW
DECLARE
old_balance NUMBER(10,3);
additional_balance NUMBER(10,3);
days NUMBER(10,0);
expected_end_date DATE;
actual_end_date DATE;
BEGIN
SELECT CUS_BALANCE
INTO old_balance
FROM CUSTOMER
WHERE CUS_ID=:NEW.CUS_ID;
expected_end_date:= :OLD.LEASE_END_DATE;
actual_end_date:= :new.RETURN_DATE;
days:= actual_end_date-expected_end_date;
IF days > 0 THEN
additional_balance := DAYS * 75;
UPDATE CUSTOMER
SET CUS_BALANCE = :NEW.LEASE_PRICE + additional_balance + old_balance
WHERE CUS_ID = :NEW.CUS_ID;
ELSIF
DAYS < 0 THEN
additional_balance := DAYS * 20;
–since days are negative , additinal balance is negative too
UPDATE CUSTOMER
SET CUS_BALANCE = :NEW.LEASE_PRICE + old_balance + additional_balance
WHERE CUS_ID = :NEW.CUS_ID;
END IF;
UPDATE BOAT
SET BOAT_AVAILABILITY = ‘yes’
WHERE BOAT_ID = :NEW.BOAT_ID;
END;
/
SHOW ERROR;
–trigger 3 , Booking is not allowed if Balance is over $400
CREATE OR REPLACE TRIGGER excess_balance
BEFORE INSERT on LEASE
FOR EACH ROW
DECLARE
Balance_due Number(10,3);
BEGIN
SELECT CUS_BALANCE
INTO Balance_due
FROM CUSTOMER
WHERE CUS_ID = :new.cus_id;
DBMS_OUTPUT.PUT_LINE(Balance_due);
IF Balance_due > 400
THEN
RAISE_APPLICATION_ERROR(-20040,’Sorry!Booking is not allowed for customers with balance due over $400! Pay Your old balance! Thanks!!!’);
END IF;
END;
/
SHOW ERROR;
"Not answered?"
