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;
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:
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 ;
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:
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;
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 ;
DELIMITER $$
CREATE PROCEDURE FindDamageCost (IN RentalID INT, OUT DamCost DECIMAL)
BEGIN
DECLARE Damage VARCHAR(1);
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 ;
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 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 ;
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download