MNA Vehicle Rental Management System

PostgreSQL Queries & Database Design

A robust PostgreSQL database design for a vehicle rental platform. This project includes schema definitions, entity relationships, and advanced SQL queries to manage users, a fleet of vehicles, and rental bookings.

📌 Project Overview

The system is designed to handle the core operations of a rental business, ensuring data integrity through relational constraints (Foreign Keys), Check constraints, and specific data types.

---

📊 Database Design (ERD)

The database follows a relational structure to ensure zero data redundancy and clear associations.

Relationships:

1. One-to-Many (User → Bookings): One customer can make multiple bookings over time.
2. Many-to-One (Bookings → Vehicle): Many separate booking records can refer to a single vehicle (booked on different dates).
3. Logical Integrity: Every single booking entry must link exactly one user to exactly one vehicle.

---

PostgreSQL Queries

Query 1: JOIN (INNER JOIN)

• Retrieve booking information along with: Customer name, Vehicle name.

SELECT u.name AS "Customer Name",  v.name AS "Vehicle Name", start_date, end_date, b.status FROM bookings AS b
INNER JOIN users AS u ON b.user_id = u.user_id
INNER JOIN vehicles AS v ON b.vehicle_id = v.vehicle_id;

Query 2: EXISTS (NOT EXISTS)

• Find all vehicles that have never been booked.

SELECT * FROM vehicles AS v
WHERE NOT EXISTS (SELECT 1 FROM bookings AS b WHERE b.vehicle_id = v.vehicle_id);

Query 3: WHERE (SELECT, WHERE)

• Retrieve all available vehicles of a specific type (e.g. cars).

SELECT * FROM vehicles
WHERE type='car' AND status = 'available';

Query 4: GROUP BY and HAVING (GROUP BY, HAVING, COUNT)

• Find the total number of bookings for each vehicle and display only those vehicles that have more than 2 bookings.

Total number of bookings for each vehicle

SELECT  v.name AS vehicle_name, COUNT(b.vehicle_id) AS total_booking FROM bookings AS b
INNER JOIN vehicles AS v ON b.vehicle_id = v.vehicle_id
GROUP BY v.name;

Main Query

SELECT  v.name AS vehicle_name, COUNT(b.vehicle_id) AS total_booking FROM bookings AS b
INNER JOIN vehicles AS v ON b.vehicle_id = v.vehicle_id
GROUP BY v.name HAVING COUNT(b.vehicle_id) > 2;