SQL Triggers And Procedures For Rental Database

Trigger 1: Check and Update Customer’s Member Date

1) When new rows in the corresponding tables are entered the following data needs to be checked to make sure that the date is correct (ie either before or after the current date) Customer’s MemberDate: must be before or equal to the current date 

Trigger: 

DROP TRIGGER IF EXISTS Trigg_CustMemDate;

DELIMITER $$

CREATE TRIGGER Trigg_CustMemDate BEFORE INSERT ON customer

FOR EACH ROW

BEGIN

                IF NEW.MemberDate > CURDATE() THEN             

                                UPDATE customer SET NEW.MemberDate=CURDATE() WHERE CusomerID=NEW.CustomerID;               

                END IF;

END$$

DELIMITER ;

Note:

                If Member date is after the current date, it automatically put the current date

Vehicle’s PurchaseDate: must be before or equal to the current date  Trigger: 

DROP TRIGGER IF EXISTS Trigg_VehPurDate;

DELIMITER $$

CREATE TRIGGER Trigg_VehPurDate BEFORE INSERT ON vehicle

FOR EACH ROW

BEGIN

                IF NEW.PurchaseDate> CURDATE() THEN             

                                UPDATE vehicle SET NEW.PurchaseDate=CURDATE() WHERE Rego=NEW.Rego;  

                END IF;

END$$

DELIMITER ;

Note:

                If Purchase date is after the current date, it automatically put the current date 

Rental’s CheckoutDate: must be after or equal to the current date  Trigger: 

DROP TRIGGER IF EXISTS Trigg_RenChkOutDate;

DELIMITER $$

CREATE TRIGGER Trigg_RenChkOutDate BEFORE INSERT ON rental

FOR EACH ROW

BEGIN

                IF NEW. Checkout< CURDATE() THEN     

                                UPDATE rental SET NEW. Checkout=CURDATE() WHERE RentalID=NEW.RentalID;

                END IF;

END$$

DELIMITER ;

Note:

                If Checkout date is before the current date, it automatically put the current date 

2) When a new car is entered into the database, the value of the NextUpdate column should automatically be set to one month from the date the vehicle was added to the table.

Trigger: 

DROP TRIGGER IF EXISTS Car_NextUpdate;

DELIMITER $$

CREATE TRIGGER Car_NextUpdate BEFORE INSERT ON vehicle

FOR EACH ROW

BEGIN

                UPDATE vehicle SET NEW.NextUpdate=DATE_ADD(NEW.PurchaseDate, INTERVAL 30 DAY) WHERE Rego=NEW.Rego;

END$$

DELIMITER ; 

3) The value of a vehicle will depreciate monthly. When the date of the NextUpdate for each vehicle arrives, the value of the vehicle should depreciate by 2.5% monthly. When the value has been updated, the date of the NextUpdate column of the Vehicle table should be set to one month from the date it was updated. (Don’t worry about situations where the date has been missed – assume that the system will be turned on every day, without fail) 

DROP PROCEDURE IF EXISTS UpdateVehicleValue;

DELIMITER $$

CREATE PROCEDURE UpdateVehicleValue ()

BEGIN  

   DECLARE rego VARCHAR(6);

   declare  val INT(6);

   declare nupdate Date;

   DECLARE vehicle_cur CURSOR FOR  SELECT Rego,VALUE,NextUpdate FROM vehicle;

Trigger 2: Set Next Update Date for New Cars

   OPEN vehicle_cur;  

                loop_vehicle: LOOP

                                FETCH vehicle_cur INTO rego,val,nupdate;

                                IF done THEN

                                                LEAVE loop_vehicle;

                                END IF;

                                IF nupdate=CURDATE() THEN

                                                UPDATE vehicle SET VALUE = VALUE – VALUE * 2.5/100 WHERE REGO=rego;                                        

                                                UPDATE vehicle

SET

    NextUpdate = DATE_ADD(NextUpdate, INTERVAL 30 DAY)

WHERE

    REGO = rego;   

                                END IF;

                END LOOP loop_vehicle;  

                CLOSE vehicle_cur;  

END$$

DELIMITER ; 

4) When a vehicle is rented (ie when a row is added to the rental table), the database should do the following:

1. a) Automatically calculate the duedate depending on how many days the vehicle is rented out for.
2. b) Automatically calculate the rent to be charged and input it into the rentalcharge table.

The rental of a car is calculated as follows:

(car type charge + 0.1% of the value of the car) per day + any insurance 

DROP TRIGGER IF EXISTS Trigg_RentVehicle;

DELIMITER $$

CREATE TRIGGER Trigg_RentVehicle BEFORE INSERT ON rental

FOR EACH ROW

BEGIN

                DECLARE carTypeCharge DECIMAL(6,2);

                DECLARE insCharge DECIMAL(6,2);

                DECLARE valueVehicle DECIMAL(6,2);

                DECLARE rentamt DECIMAL(6,2);               

                SET NEW.Duedate=DATE_ADD(NEW.Checkin, INTERVAL RentDays DAY);

                SELECT Charge INTO carTypeCharge FROM VehicleType WHERE VehicleTypeID = (SELECT VehicleTypeID FROM Rental WHERE RentalID=NEW.RentalID);

                SELECT Price INTO insCharge FROM Insurance WHERE InsuranceID = (SELECT InsuranceID FROM Rental WHERE RentalID=NEW.RentalID);

                SELECT VALUE INTO valueVehicle FROM Vehicle WHERE Rego = (SELECT Rego FROM Rental WHERE RentalID=NEW.RentalID);               

                SET rentamt= (carTypeCharge+ 0.1*valueVehicle)/NEW.RentDays + insCharge;               

                INSERT INTO RentalCharge (RentalID,Rent) VALUE (NEW.RentalID,rentamt);               

END$$

DELIMITER ; 

1. c) If any accessories are rented (ie new rows added to the rentaccessory table), the quantity of that accessory (in the accessory table) should be reduced by the quantity taken. Also, the total charge of the accessories rented should be added to the rental charge previously calculated in (b) above. 

DROP TRIGGER IF EXISTS Trigg_AccessRental;

DELIMITER $$

CREATE TRIGGER Trigg_AccessRental BEFORE INSERT ON RentAccessory

FOR EACH ROW

BEGIN

                DECLARE acctotcost DECIMAL(6,2);

                UPDATE Accessory SET Qty=Qty-NEW.Qty WHERE AccessoryID=NEW.AccessoryID;

                SELECT Cost * NEW.Qty INTO acctotcost FROM Accessory;           

                UPDATE Rental SET RentalCharge=RentalCharge+acctotcost WHERE RentalID=NEW.RentalID;      

END$$

DELIMITER ; 

5) When the vehicle is returned (ie when the checkin date – which was originally NULL – is updated for that rental), the following should be done:

1. a) Values for the fields OdometerIn, FuelLevel, Damage must be entered and therefore cannot be NULL when the car is returned.
2. b) The number entered for the OdometerIn in the rental table should automatically be updated for that vehicle in the vehicle table.
3. c) If the fuel tank is not full (ie, the level is not 1 but: 0.75, 0.5, 0.25, 0) the customer is charged for a tank fill up. (The cost that is charged is the (1-fuellevel that is entered) * fueltank (available in the vehicle table) * the currentcost (found in the fueltype table)). Once calculated this charge should be entered in the rentalcharge table.
4. d) If the vehicle is not returned by the duedate a penalty is incurred (which is calculated as the rent charge per day plus a penalty. The penalty will differ depending on the number of days overdue). 

DROP TRIGGER IF EXISTS Trigg_VehicleRental;

DELIMITER $$

CREATE TRIGGER Trigg_VehicleRental BEFORE UPDATE ON Rental

FOR EACH ROW

BEGIN  

                DECLARE fuelcost DECIMAL(6,2);

                DECLARE ftank DECIMAL(6,2);   

                DECLARE totcost DECIMAL(6,2);

                DECLARE penamt DECIMAL(6,2);

                DECLARE rcharge DECIMAL(6,2);

                DECLARE odue DECIMAL(6,2);

                DECLARE insID VARCHAR(1);               

                IF NEW.Checkin IS NOT NULL THEN

                                IF NEW.OdometerIn IS NOT NULL AND NEW.FuelLevel IS NOT NULL AND NEW.Damage IS NOT NULL THEN                                        

                                                UPDATE Vehicle SET Odometer=NEW.OdometerIn WHERE Rego=NEW.Rego;

                                                IF (NEW.FuelLevel<1) THEN

                                                                SELECT CurrentCost INTO fuelcost FROM FuelType WHERE FuelTypeID = (SELECT FuelTypeID FROM vehicle WHERE Rego = NEW.Rego);

                                                                SELECT FuelTank INTO ftank FROM vehicle WHERE Rego = NEW.Rego;                                                                                               

Procedure: Update Vehicle Value and Next Update Date

                                                                SET totcost=(1-FuelLevel) * ftank * fuelcost ;                                                                                       

                                                                SET rcharge=rcharge+totcost;   

                UPDATE rentalcharge SET Rent=rcharge WHERE RentalID=NEW.RentalID;

                                                END IF;                                               

                                                IF Checkin > Duedate THEN

                                                                SELECT Rent, Overdue INTO rcharge, odue FROM rentalcharge WHERE RentalID = NEW.RentalID;

                                                                SET penamt=RentDays * Rent+odue * (Checkin – Duedate)  ;

                                                                INSERT INTO Penalty (PenaltyType, MinDay, MaxDay, Cost) VALUES (‘Penalty8’,1,RentDays,penamt);              

                                                END IF;

                                END IF;

                END IF;

END$$

DELIMITER ; 

1. e) If there are damages to the car, and the customer didn’t take the full insurance option, the damages are paid by the customer. If Partial Insurance is taken, the customer pays for half of the damages. The damages is calculated when a value is entered in the damage field of the rentalcharge table. 

DELIMITER $$

CREATE PROCEDURE FindDamageCost (IN RentalID INT, OUT DamCost DECIMAL)

BEGIN   

    DECLARE Damage VARCHAR(1);

                DECLARE DamageAmt DECIMAL;

                DECLARE InsuranceID VARCHAR(1);

                DECLARE rental_cur CURSOR FOR SELECT * FROM Rental WHERE [email protected];   

    OPEN rental_cur;

    SET DamageAmt=0;

                loop_rental: LOOP

                                FETCH rental_cur INTO Damage, InsuranceID ;

                                IF done THEN

            LEAVE loop_rental;

        END IF;

        SELECT Damage INTO DamageAmt FROM RentalCharge WHERE RentalID=RentalID;

                                IF Damage = ‘Y’ THEN                                                                                     

                                                IF InsuranceID=’F’ THEN

                                                                UPDATE RentalCharge SET DamageAmt=DamageAmt WHERE RentalID=RentalID;                                                        

                                                ELSE

                                                                UPDATE RentalCharge SET DamageAmt=DamageAmt/2 WHERE RentalID=RentalID;

                                                END IF;

                                END IF;

                END LOOP loop_rental;

                CLOSE rental_cur;

END$$

DELIMITER ; 

1. f) If any accessories were rented, the quantity for that accessory should be increased by 1. 

DROP TRIGGER IF EXISTS Trigg_VehicleAccess;

DELIMITER $$

CREATE TRIGGER Trigg_VehicleAccess BEFORE INSERT ON RentAccessory

FOR EACH ROW

BEGIN  

                UPDATE Accessory SET Qty=Qty+1 WHERE AccessoryID=NEW.AccessoryID;

END$$

DELIMITER ; 

7) Old rentals – 6 months from the checkin date should be deleted from the rental table. 

DELIMITER $$

CREATE PROCEDURE DeleteOldRental ()

BEGIN

   DELETE FROM Rental WHERE TIMESTAMPDIFF(MONTH,Checkin,CURDATE())>=6;

END$$

DELIMITER ; 

8) 12 month old vehicles (from date of purchase) will no longer be available for rent, and will be put up for sale. The database should automatically remove the old vehicle from the vehicle table and add it to the sale table. The data that is to be entered is similar to what is in the vehicle table, except for a slight variation: the FuelType, EngineCapacity and VehicleType will be added to the description field. (The FuelType and VehicleType should be the actual type not the number and the EngineCapacity should have the word ‘litre’ after the value) 

DELIMITER $$

CREATE PROCEDURE TransferOldVehicle ()

BEGIN   

    DECLARE descrip VARCHAR(150);

    DECLARE fueltype VARCHAR(50);

    DECLARE vehtype VARCHAR(50);

    DECLARE engcap VARCHAR(50);               

    DECLARE Rego VARCHAR(6);

                DECLARE Description VARCHAR(60);

                DECLARE Make VARCHAR(15);

                DECLARE Model VARCHAR(15);

                DECLARE Year INT(4);

                DECLARE VALUE INT(6);

                DECLARE EngineCapacity DECIMAL(2,1);

                DECLARE FuelTank INT(3);

                DECLARE odometer INT(6);

    DECLARE vehicle_cur CURSOR FOR SELECT Rego,Description,Make,Model,Year,VALUE,EngineCapacity,FuelTank,odometer FROM vehicle;  

         OPEN vehicle_cur; 

                loop_vehicle: LOOP

                                FETCH vehicle_cur INTO Rego,Description,Make,Model,Year,VALUE,EngineCapacity,FuelTank,odometer ;

                                IF done THEN

            LEAVE loop_vehicle;

        END IF;

                                IF checkin IS NOT NULL THEN

                                                IF TIMESTAMPDIFF(MONTH,PurchaseDate,CURDATE())>=12 THEN

                                                                SELECT FuelType INTO fueltype FROM FuelType WHERE FuelTypeID = (SELECT FuelTypeID FROM Vehicle WHERE Rego=Rego);

                                                                SELECT VehicleType INTO vehtype FROM VehicleType WHERE VehicleTypeID = (SELECT VehicleTypeID FROM Vehicle WHERE Rego=Rego);                                                              

                                                                SET descrip=CONCAT(descrip,’,’,fueltype);

                                                                SET descrip=CONCAT(descrip,’,’,vehtype);

                                                                SET descrip=CONCAT(descrip,’,’,EngineCapacity,’,litre’);                                                               

                                                                INSERT INTO Sale (Rego, Description, Make, Model, Year, Odometer, Price) VALUES (Rego, descrip,Make,Model,Year,odometer,VALUE);                                                               

                                                                DELETE FROM vehicle WHERE Rego=Rego;

                                                END IF;

                                END IF;

                END LOOP loop_vehicle;

                CLOSE vehicle_cur;

END$$

DELIMITER ;