Rental Cars Reservation System

Year 2022, you are hired as a software engineer in a car rental company. Your company is planning for nationwide expansion of its business. You are required to develop a database to support storage and manipulation of information about the cars, customers, and reservations.

Each car is uniquely identified with its license plate. It also has manufacturer (Toyota, Jeep, etc), model, year of made and color information. Cars are rented on hourly, daily, and monthly rates. To ensure the availability of the car, its status is updated from available to rented (if rented), or under maintenance (if under maintenance).

The company wants to identify each customer with a unique customer id., name, address, contact number, driving license number, and payment information (credit, cash) for each customer is also stored.

Record of each car reservation is maintained with unique identification number and identification of the car and customer. Date and time of the reservation request is also stored along with the date and time of pick up and return of the car. Status of each reservation is also updated form confirmed (if confirmed), to picked up (when the car is picked up), and to returned (when the car is returned). The total amount for the reservation is also stored for future reference.

You can specify the assumptions (if any) for designing the required database.

You are required to submit:

1. An Entity Relationship (ER) diagram with appropriate relationships and cardinalities.

2. Relational model mapped from the ER diagram using any relational DBMS.

3. Populate each relation with at least 5 records. Submit the snapshot of each relation with records.

You are required to submit the SQL queries and the snapshot of the results for the followings:

2.1 Show full information of the car available for rent today.

2.2 List the name and phone number of all the customers supposed to return the rented cars today.

2.3 Show the customer id and price paid for each completed reservation (car is returned)

2.4 Show the manufacturer, model, and color of each car under maintenance.

2.5 Find the manufacturer and model of the top rented car.