Objectives: WRITE the SQL statements according to these questions and show the function, triggers, or stored procedures and their results.

Please provide two functions with results, four procedures with results, and How many bookings we have at Christmas time.

Please briefly describe fortune car rental problems, functions, triggers, stored procedures and their results.

Definition of two functions with results.

Definition of 4 procedures with results.

Definition of 2 triggers with results.

Code of the procedures.

Outputs screenshots.

Design and implement a database with all the required procedures and functions.

Questions:

Fortune Car rentals are developing an automated system through which they wish to keep records of their vehicles, clients and daily reservations.

There are several rules they follow:

1. A client needs to register before they can make reservations. (every client will have client ID)

2. All the cars are registered in the vehicle table (each vehicle has a vehicle ID)

3. Reservation can be done from a start date to end date. An estimate budget is given to the client and the reservation is posted in the reservation table. Make sure end_date is not entered yet. The procedure returns the reservation_Id.

4. Reservation rules are:

A client cannot rent a car if she/he is under the age of 18.

5. When client is returning the car (can pass reservation ID or client ID):

The reservation ID is given.

If a reservation is for more than 10 days, a 10% discount is offered

If the reservation in having Christmas day in between, a 20% discount is offered.

Now calculate the cost and ask for the payment.

If client is not paying, or car is broken by the client, the client is moved to black list table with the reason

6. At the end of every year, the reservation table is emptied out (Leaving the records without end_date)

7. The data is moved to the archieved_reservation

8. Stats are generated and copied to the stats table.

9. The ultimate aim is to answer the following questions:

List all the active bookings for the cars.

List all the details of the clients having the cars rented today.

List all blacklisted clients

How many bookings do we have at Christmas time.

Generate the total revenue generated on a specific vehicle.

these already creat these tables:

CREATE TABLE clients (

client_id INTEGER PRIMARY KEY,

first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

age INTEGER NOT NULL

);

CREATE TABLE vehicles (

vehicle_id INTEGER PRIMARY KEY,

make TEXT NOT NULL,

model TEXT NOT NULL,

year INTEGER NOT NULL,

rental_rate DECIMAL(10, 2) NOT NULL

);

CREATE TABLE reservations (

reservation_id INTEGER PRIMARY KEY,

client_id INTEGER NOT NULL,

vehicle_id INTEGER NOT NULL,

start_date DATE NOT NULL,

end_date DATE,

estimate_budget DECIMAL(10, 2) NOT NULL,

actual_cost DECIMAL(10, 2),

payment_received BOOLEAN,

CONSTRAINT fk_clients FOREIGN KEY (client_id) REFERENCES clients(client_id),

CONSTRAINT fk_vehicles FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id)

);

CREATE TABLE blacklisted_clients (

client_id INTEGER PRIMARY KEY,

reason TEXT NOT NULL,

CONSTRAINT fk_clients FOREIGN KEY (client_id) REFERENCES clients(client_id)

);

CREATE TABLE archived_reservations (

reservation_id INTEGER PRIMARY KEY,

client_id INTEGER NOT NULL,

vehicle_id INTEGER NOT NULL,

start_date DATE NOT NULL,

end_date DATE NOT NULL,

estimate_budget DECIMAL(10, 2) NOT NULL,

actual_cost DECIMAL(10, 2),

payment_received BOOLEAN,

CONSTRAINT fk_clients FOREIGN KEY (client_id) REFERENCES clients(client_id),

CONSTRAINT fk_vehicles FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id)

);

CREATE TABLE stats (

year INTEGER PRIMARY KEY,

total_revenue DECIMAL(10, 2) NOT NULL

);

now, I do not know how to create 4 procedures and 2 functions with total_cost and how many bookings at Christmas time, 2 triggers to check the age under 18 and the client did not return the care reservation, how to copy the reservation data into stat table??? and end of the year, how reservation emptied out?

also, if the client breaks the car or does not return, how does the system move the client to the blacklist ??? show all outputs...

SQL !!!!!!

The tentative Scheme is given below: (feel tree to make changes) CLIENT \begin{tabular}{|l} C ID \\ F_NAME \\ L_NAME \\ DOB \\ CITY \\ GENDER \\ \hline \end{tabular} STATS YEAR C_ID AGE GENDER TOT_RESERVATIONS TOT_DAYS_OF_RESERVATIONS TOT COST OF RESERVATIONS