Assiqnme t2-ProceduresFunciions Tripge Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Design and implement PL/SQL function, trigger, and stored procedures Tables are given below 1.Vehicle table : y id char(5) not null primary kcy,y modcl varchar(20), y make varchar(20), cost per day decimal(5,2); 2. reservation table: r id char(5) not null primary key, start date date, end date date, c id char(5),y id char(5), total cost decimal(5, 3. table client: c id char(5) not null primary key, f name varchar(20), I name varchar(20), dob date, city varchar(20), gender char(1); 4. table archivedreservation r id char(5) not null primary key, start date date, end date date, c id char(5),v id char(5) total cost decimal(5, 2),added by varchar(20),added date date; 5.table stats: year date, c id char(5), age int, gender char1), tot reservations int, tot davs of reservations int, tot cost of reservations decimal(5, 2): 6.table black list: c id char(5), I name varchar(20), I name varchar(20), dob date, city varchar(20), gender char(1), reason varchar(20),added by varchar(20),added date date; Please note that additional text file has sal commands for the basic structure. You may need to fill in more date run queries. Paqe 4 ct 5 291 ct 1772 wordt !ngli'h (l.nr:ed get.4) Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Procedures Problem 1: details of members The vehicle reservation system should display the name of the client in proper om as David John if the clien-ld is passed to the procedure with the details of his date of birth and city he lives in. Problem 2:Displaylng the details of reservation for a vehicle If the V id is passed, then the procedure should be able to display the complete details of the vehicles for which reservations are made. exec list reservations(1): Transaction Id: s for Vehicle no: V1003 i.e. Mercedez Benz year 2011 117.5 was booked from 12-JAN-19 to 15-JAN-19 costing the total of $352.5 Problem 3 : Displaying the details of all reservatious with its details Change the above procedure to be able to display the complete details of the vehicles for which reservations are made. ing soa se Problem 4 Black list (Stored Procedure) Create a store procedure to add clients to the "Black list". The input parameters are CID and a string of characters for the reason. Insert all client information, the reason, and the UUSER NAME (ST....) and date time. The procedure should also delete the client from the clients table the eSTI. Problem 5: Archiving data (Stored Procedure) Create a stored procedure to archive the reservation data. The archived procedure is executed by the DBA on the first day of a new year. This procedure has no input parameters and displays message "total # reservations archived." The archiving procedure adds the old (previous year) reservations to the ARCHIVED RESERVATION table and deletes the old rescrvations from the RESERVATION table. Assiqnme t2-ProceduresFunciions Tripge Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Cop Section-I unctions roblem 1 : Calculating total for the reservation (Function) The total cost for the rental is calculated based on the number of days and the cost of the vehicle. An additional tax of 12% is added to the total. If the rental is longer than 10 days a discount of 15% is subtracted from the total cost. Create a function to calculate TOTAL COST for a specified reservation id (R ID). Problem 2: Offering a X-Mas discount for the reservation (Function) Create a function to calculate TOTAL COST for a new reservation in the month of December. A discount of 15% is offered on the total deal fore tax) if onc of the days of booking is X-Mas. Problem 3: Minimum Age and maximum Age (TRIGGER) The vehicle reservation system should check the age of the person renting the car. The minimum age is 21 years and maximum age is 70 years. Your function should return False if the is not yet 21 or above 70 years. The age is calculated as of today (SYSDATE on the server) when the c id is passed to the function. Problem 4 : Preventing the client from the black list to rent a car (TRIGGER) Write a function that returns false if the client is one of the blacklisted clients. The c id is passed to the function. Paq, 1cFE Z25 of 1772 wardt !ngli'h (l.nr:.dS.at.4) Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Part-2 Triggers Problem 1 Minimum Age and maximum Age (TRIGGER) The vehicle reservation system should check the age of the person renting the car. The minimum age is 21 years and maximum age is 70 years. Create a TRIGGER to prevent an addition of a reservation for a client who is not yet 21 or above 70 years. The age is calculated as of today (SYSDATE on the server). Problem 2 Character-based Primary Key including a sequence number (TRIGGER) Client ids C ID are defined as a string of 10 characters. The first character is a letter C" followed by 9 characters, which are created from a sequence left padded by zeros. Create a trigger to add automatically a new primary key for an Similar trigger must run on vehicle table to define vehicle Id as well as on the transaction table to have transaction id. Vehicle has the character V and transaction table do not have any code. INSERT on the Client table. Problem 3 Preventing the client from the black list to rent a car (TRIGGER) Write a trigger to stop an INSERT or UPDATE operation on transaction table for the blacklisted clients. Psq+4ct7 194 et 1772 wordt !ngli'h (l.nr:.dSete4) Assiqnme t2-ProceduresFunciions Tripge Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Design and implement PL/SQL function, trigger, and stored procedures Tables are given below 1.Vehicle table : y id char(5) not null primary kcy,y modcl varchar(20), y make varchar(20), cost per day decimal(5,2); 2. reservation table: r id char(5) not null primary key, start date date, end date date, c id char(5),y id char(5), total cost decimal(5, 3. table client: c id char(5) not null primary key, f name varchar(20), I name varchar(20), dob date, city varchar(20), gender char(1); 4. table archivedreservation r id char(5) not null primary key, start date date, end date date, c id char(5),v id char(5) total cost decimal(5, 2),added by varchar(20),added date date; 5.table stats: year date, c id char(5), age int, gender char1), tot reservations int, tot davs of reservations int, tot cost of reservations decimal(5, 2): 6.table black list: c id char(5), I name varchar(20), I name varchar(20), dob date, city varchar(20), gender char(1), reason varchar(20),added by varchar(20),added date date; Please note that additional text file has sal commands for the basic structure. You may need to fill in more date run queries. Paqe 4 ct 5 291 ct 1772 wordt !ngli'h (l.nr:ed get.4) Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Procedures Problem 1: details of members The vehicle reservation system should display the name of the client in proper om as David John if the clien-ld is passed to the procedure with the details of his date of birth and city he lives in. Problem 2:Displaylng the details of reservation for a vehicle If the V id is passed, then the procedure should be able to display the complete details of the vehicles for which reservations are made. exec list reservations(1): Transaction Id: s for Vehicle no: V1003 i.e. Mercedez Benz year 2011 117.5 was booked from 12-JAN-19 to 15-JAN-19 costing the total of $352.5 Problem 3 : Displaying the details of all reservatious with its details Change the above procedure to be able to display the complete details of the vehicles for which reservations are made. ing soa se Problem 4 Black list (Stored Procedure) Create a store procedure to add clients to the "Black list". The input parameters are CID and a string of characters for the reason. Insert all client information, the reason, and the UUSER NAME (ST....) and date time. The procedure should also delete the client from the clients table the eSTI. Problem 5: Archiving data (Stored Procedure) Create a stored procedure to archive the reservation data. The archived procedure is executed by the DBA on the first day of a new year. This procedure has no input parameters and displays message "total # reservations archived." The archiving procedure adds the old (previous year) reservations to the ARCHIVED RESERVATION table and deletes the old rescrvations from the RESERVATION table. Assiqnme t2-ProceduresFunciions Tripge Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Cop Section-I unctions roblem 1 : Calculating total for the reservation (Function) The total cost for the rental is calculated based on the number of days and the cost of the vehicle. An additional tax of 12% is added to the total. If the rental is longer than 10 days a discount of 15% is subtracted from the total cost. Create a function to calculate TOTAL COST for a specified reservation id (R ID). Problem 2: Offering a X-Mas discount for the reservation (Function) Create a function to calculate TOTAL COST for a new reservation in the month of December. A discount of 15% is offered on the total deal fore tax) if onc of the days of booking is X-Mas. Problem 3: Minimum Age and maximum Age (TRIGGER) The vehicle reservation system should check the age of the person renting the car. The minimum age is 21 years and maximum age is 70 years. Your function should return False if the is not yet 21 or above 70 years. The age is calculated as of today (SYSDATE on the server) when the c id is passed to the function. Problem 4 : Preventing the client from the black list to rent a car (TRIGGER) Write a function that returns false if the client is one of the blacklisted clients. The c id is passed to the function. Paq, 1cFE Z25 of 1772 wardt !ngli'h (l.nr:.dS.at.4) Fil Home nrt Deeign Layu nccs Msilngs Review vie Heb PDFArchitest 5 Crstor FDF rdhitest6Crestoll me what you want te da Share Comments Part-2 Triggers Problem 1 Minimum Age and maximum Age (TRIGGER) The vehicle reservation system should check the age of the person renting the car. The minimum age is 21 years and maximum age is 70 years. Create a TRIGGER to prevent an addition of a reservation for a client who is not yet 21 or above 70 years. The age is calculated as of today (SYSDATE on the server). Problem 2 Character-based Primary Key including a sequence number (TRIGGER) Client ids C ID are defined as a string of 10 characters. The first character is a letter C" followed by 9 characters, which are created from a sequence left padded by zeros. Create a trigger to add automatically a new primary key for an Similar trigger must run on vehicle table to define vehicle Id as well as on the transaction table to have transaction id. Vehicle has the character V and transaction table do not have any code. INSERT on the Client table. Problem 3 Preventing the client from the black list to rent a car (TRIGGER) Write a trigger to stop an INSERT or UPDATE operation on transaction table for the blacklisted clients. Psq+4ct7 194 et 1772 wordt !ngli'h (l.nr:.dSete4)