Online Car Rental Management System (OCRMS)

Consider the following set of requirements for an Online Car Rental Management System Database.

The OCRMS keeps the fundamental information of each customer such as name, email, password, national ID number, driver license number, expiry date of driver license number, customer ID number, nationality, detailed address, mobile number, date of birth and sex. National id number, customer ID number and driver license number are unique for each customer.

The OCRMS keeps all necessary information about the employees.

Each Car is described by type (e.g. sedan, pickup, truck etc.), brand name, class, model, color, fuel type, current odometer, rental fee per day, location, plate number, vehicle identification number VID and expiry date of vehicle registration. Both VID and plate number are unique for each car.

The customers can register, view available cars, their profiles and prices and book from them by identifying the location, pick-up and return date and time.

An invoice must be issued for each booking plus any additional days or odometers after returning the car.

Different types of payments can be accepted such as cash, cheque and credit cards.

Payment for credit cards should include the cardholder name, cardholder surname, card number, card type (visa, master card, etc.), card expiry date, card security code, total price paid after deducting the cash and cheque value.

The Cheque must have a Cheque-number, expiry date, and amount.

The customer can evaluate the car (out of 5 stars) and write a feedback after returning it.

You should design the system that contain entities and attributes to store data that makes the system able to report the requested information by executing the following queries.

SQL Queries:

List the name and email for all customers who are currently renting cars along with the details of

cars they have rented and renting date.

List the name and email for all customers who supposed to return the cars but they did not.

List all bookings for a particular customer.

List the name of all customers who have rented cars via the online system in the past month.

Find the name of all customers who made a booking with a total above 500$.

Find the cars that have the highest average evaluation