Script 1: Creating and Inserting Data into Tables
use CurRENT_CarRENTals;
— Ques 1
— drop already created trigger on customer table
DROP TRIGGER IF EXISTS customers_before_insert;
— Create trigger to check customer’s Memberdate
DELIMITER $$
CREATE TRIGGER `customers_before_insert` BEFORE INSERT ON `Customer`
FOR EACH ROW
BEGIN
IF LENGTH(NEW.MemberDate) > Now() Then
CALL `’MEMBER DATE IS NOT CORRECT!’`;
END IF;
END$$
DELIMITER ;
— drop already created trigger on Vehicle table
DROP TRIGGER IF EXISTS vehicles_before_insert;
— Create trigger to check vehicle’s purchase date
DELIMITER $$
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `Vehicle`
FOR EACH ROW
BEGIN
IF LENGTH(NEW.PurchaseDate) > Now() Then
CALL `’PURCHASE DATE IS NOT CORRECT!’`;
END IF;
END$$
DELIMITER ;
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_insert;
— Create trigger to check rental’s checkout date
DELIMITER $$
CREATE TRIGGER `rental_before_insert` BEFORE INSERT ON `Rental`
FOR EACH ROW
BEGIN
IF LENGTH(NEW.Checkout) < Now() Then
CALL `’CHECKOUT DATE IS NOT CORRECT!’`;
END IF;
END$$
DELIMITER ;
— Ques 2
— drop already created trigger on vehicle table
DROP TRIGGER IF EXISTS vehicles_before_insert;
— Create trigger to update NextUpdate value
DELIMITER $$
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `Vehicle`
FOR EACH ROW
BEGIN
set @nextUpdate :=DATE_ADD(NEW.PurchaseDate, INTERVAL 1 MONTH);
set new.NextUpdate= @nextUpdate;
END$$
DELIMITER ;
— Ques 3
— drop already created trigger on vehicle table
DROP TRIGGER IF EXISTS vehicles_before_update;
— Create trigger to update value of the vehicle
DELIMITER $$
CREATE TRIGGER `vehicles_before_update` BEFORE UPDATE ON `Vehicle`
FOR EACH ROW
BEGIN
set @nextUpdateValue :=DATE_ADD(NEW.PurchaseDate, INTERVAL 1 MONTH);
set new.NextUpdate= @nextUpdateValue;
set @value :=NEW.`Value` – (NEW.`Value` * 2.5/100);
set NEW.`Value`= @value;
END$$
DELIMITER ;
— Ques 4 A)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_insert;
— Create trigger to calculate due date
DELIMITER $$
CREATE TRIGGER `rental_before_insert` BEFORE INSERT ON `Rental`
FOR EACH ROW
BEGIN
set @dueDate := DATE_ADD(NEW.Checkout, INTERVAL NEW.RentDays DAY);
set new.dueDate= @dueDate;
END$$
DELIMITER ;
— Ques 4 B)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_insert;
— Create trigger to calculate total rent
DELIMITER $$
CREATE TRIGGER `rental_before_insert` BEFORE INSERT ON `Rental`
FOR EACH ROW
BEGIN
set @charge := (select VehicleType.Charge from Rental inner join Vehicle on Rental.Rego=Vehicle.Rego
inner join VehicleType on VehicleType.VehicleTypeID=Vehicle.VehicleTypeID where Rental.RentalID=new.RentalID);
set @insurance := (select insurance.price from insurance inner join rental on insurance.InsuranceID=Rental.InsuranceID
where Rental.RentalID=new.RentalID);
set @Rentvalue := (select Vehicle.value from Vehicle inner join Rental on Rental.Rego=Vehicle.Rego
where Rental.RentalID=new.RentalID);
set @rent := (@charge + (@Rentvalue * 0.1/100))* new.RentDays + @insurance;
insert into RentalCharge(RentalID, Rent) values (@rent, NEW.RentalID);
END$$
DELIMITER ;
— Ques 4 C)
— drop already created trigger on accessory table
DROP TRIGGER IF EXISTS accessory_before_insert;
— Create trigger to update accessory quantity
DELIMITER $$
CREATE TRIGGER `accessory_before_insert` BEFORE INSERT ON `RentAccessory`FOR EACH ROW
BEGIN
update Accessory set Qty= Qty-new.Qty where Accessory.AccessoryID=new.AccessoryID;
set @accessoryCost := (select Accessory.cost from RentAccessory inner join Rental on Rental.RentalID=RentAccessory.RentalID
inner join Accessory on Accessory.AccessoryID=RentAccessory.AccessoryID
where Rental.RentalID=new.RentalID);
update RentalCharge set Rent=Rent+(@accessoryCost*new.qty) where RentalCharge.RentalID=new.RentalID;
END$$
DELIMITER ;
— Ques 5 A)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_update;
— create trigger to check odometer, fuel level and damage
DELIMITER $$
CREATE TRIGGER `rental_before_update` BEFORE UPDATE ON `Rental`
FOR EACH ROW
BEGIN
IF (NEW.Checkin != null) Then
IF (NEW.OdometerIn = null) THEN
CALL `’ODOMETER CANNOT BE NULL!’`;
END IF;
IF (NEW.FuelLevel = null) THEN
CALL `’FUEL LEVEL CANNOT BE NULL!’`;
END IF;
IF (NEW.Damage = null) THEN
CALL `’DAMAGE CANNOT BE NULL!’`;
END IF;
END IF;
END$$
DELIMITER ;
— Ques 5 B)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_update;
— create trigger to update odometer in the Vehicle table
DELIMITER $$
CREATE TRIGGER `rental_before_update` BEFORE UPDATE ON `Rental`
FOR EACH ROW
BEGIN
IF (NEW.Checkin != null) Then
Update Vehicle set odometer=odometer+new.OdometerIn where Vehicle.Rego=new.Rego;
END IF;
END$$
DELIMITER ;
— Ques 5 C)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_update;
— create trigger to update the rental charge
DELIMITER $$
CREATE TRIGGER `rental_before_update` BEFORE UPDATE ON `Rental`
FOR EACH ROW
BEGIN
IF (NEW.FuelLevel != ‘1’) Then
set @fuelTank := (select Vehicle.FuelTank from Vehicle inner join Rental on Rental.Rego=Vehicle.Rego
where Rental.RentalID=new.RentalID);
set @currentCost := (select CurrentCost from FuelType inner join Vehicle on FuelType.FuelTypeID=Vehicle.FuelTypeID
inner join Rental on Rental.Rego=Vehicle.Rego
where Rental.RentalID=new.RentalID);
set @charge := (1-new.FuelLevel) * @fuelTank * @currentCost;
insert into RentalCharge(RentalID, Rental) values (new.RentalID, @charge);
END IF;
END$$
DELIMITER ;
— Ques 5 D)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS rental_before_update;
— create trigger to update the rental charge if penelty is there
DELIMITER $$
CREATE TRIGGER `rental_before_update` BEFORE UPDATE ON `Rental`
FOR EACH ROW
BEGIN
set @dueDate :=DATE_ADD(NEW.Checkout, INTERVAL RentDays DAY);
IF NEW.DueDate = NULL AND @dueDate < NOW() Then
IF DATEDIFF(NOW(), @dueDate) <=10 THEN
SET @penalty := 25;
END IF;
IF DATEDIFF(NOW(), @dueDate) >10 AND DATEDIFF(NOW(), @dueDate) <= 20 THEN
SET @penalty := 55;
END IF;
IF DATEDIFF(NOW(), @dueDate) >20 AND DATEDIFF(NOW(), @dueDate) <= 30 THEN
SET @penalty := 70;
END IF;
IF DATEDIFF(NOW(), @dueDate) >30 AND DATEDIFF(NOW(), @dueDate) <= 99 THEN
SET @penalty := 110;
END IF;
update RentalCharge set Rent=Rent + @penalty where RentalCharge.RentalID=new.RentalID;
END IF;
END$$
DELIMITER ;
— Ques 5 E)
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS RentalCharge_before_insert;
— create trigger to update the damage cost
DELIMITER $$
CREATE TRIGGER `RentalCharge_before_insert` BEFORE INSERT ON `RentalCharge`
FOR EACH ROW
BEGIN
Set @damage := (Select damage from Rental where Rental.RentalID=New.RentalID);
Set @InsuranceID := (Select InsuranceID from Rental where Rental.RentalID=New.RentalID);
IF @damage = ‘Y’ AND @InsuranceID != ‘F’ THENset new.damage :=0;END IF;
IF @damage = ‘Y’ AND @InsuranceID != ‘P’ THEN
set new.damage := new.damage-(new.damage/2);
END IF;
END$$
DELIMITER ;
— drop already created trigger on rental table
DROP TRIGGER IF EXISTS
RentalCharge_before_update;
— create trigger to update the damage cost
DELIMITER $$
CREATE TRIGGER `RentalCharge_before_update` BEFORE UPDATE ON `RentalCharge`
FOR EACH ROW
BEGIN
Set @damage := (Select damage from Rental where Rental.RentalID=New.RentalID);
Set @InsuranceID := (Select InsuranceID from Rental where Rental.RentalID=New.RentalID);
IF @damage = ‘Y’ AND @InsuranceID != ‘F’ THEN
set new.damage :=0;
END IF;
IF @damage = ‘Y’ AND @InsuranceID != ‘P’ THEN
set new.damage := new.damage-(new.damage/2);
END IF;
END$$
DELIMITER ;
— Ques 5 F)
— drop already created trigger on Accessory table
DROP TRIGGER IF EXISTS accessory_before_insert;
— Create trigger to update accessory quantity
DELIMITER $$
CREATE TRIGGER `accessory_before_insert` BEFORE INSERT ON `RentAccessory`FOR EACH ROW
BEGIN
update Accessory set Qty= Qty-new.Qty where Accessory.AccessoryID=new.AccessoryID;
END$$
DELIMITER ;
— Ques 7
SET SQL_SAFE_UPDATES=0;
delete from rental where TIMESTAMPDIFF(MONTH, now(), checkin) > 6;
— Ques 8
— drop already created trigger on Vehicle table
DROP TRIGGER IF EXISTS vehicles_before_insert;
— Create trigger to check old vehicles
DELIMITER $$
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `Vehicle`
FOR EACH ROW
BEGIN
set @checkinDate := (select Rental.Checkin from Rental inner join Vehicle on Rental.Rego=Vehicle.Rego
where Vehicle.Rego=new.Rego);
IF TIMESTAMPDIFF(MONTH, now(), NEW.PurchaseDate) >= 12 THEN
IF @checkinDate != null then
DELETE FROM VEHICLE WHERE VEHICLE.REGO= NEW.REGO;
insert into Sale values (new.Rego, new.Description, new.Make, new.Model, new.`Year`, new.Odometer, new.`value`);
END IF;
END IF;
END$$
DELIMITER ;
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
