Objectives: WRITE the SQL statements according these questions and

show the function, triggers, or stored procedures and their results.

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.

\begin{tabular}{|l|l|l|} \hline VEHICLE & RESERVATION & CLIENT \\ V ID & RID & C ID \\ \hline V_MODEL & START_DATE & F_NAME \\ V_MAKE & END_DATE & L_NAME \\ COST_PER_DAY & C_ID & DOB \\ C_ID__OOT & CITY \\ TOTAL_COST & GENDER \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline ARCHIVED_RESERVATION & \\ RID & BLACK_LIST \\ START_DATE & C_ID \\ END_DATE & F_NAME \\ C_ID & L_NAME \\ V_ID & DOB_COST & DOB \\ TOTAL_COSY & CITY \\ ADDED_BY & GENDER \\ ADDED_DATE & REASON \\ & READEAY \\ & ADDED_BY \\ ADDED_DATE \\ \hline \end{tabular} STATS YEAR C_ID AGE GENDER TOT_RESERVATIONS TOT_DAYS_OF_RESERVATIONS TOT COST OF RESERVATIONS