Vehicle Rental System - Database Design & SQL Queries

📋 Project Overview

The Vehicle Rental System is a database-oriented project designed to demonstrate fundamental concepts of relational database design, Entity Relationship Diagrams (ERD), and SQL querying.
The system models a simplified real-world scenario where users can rent vehicles for a specified duration.

This project focuses on designing a structured database schema and writing SQL queries to retrieve meaningful information from the database.

🚀 Vehicle Rental System ERD

📖 Table of Contents

• 🎯 Objectives
• 📊 System Description
• 🗂️ Database Design & Business Rules
  • 1. Users Table
  • 2. Vehicles Table
  • 3. Bookings Table
• 📚 Entity Relationship Diagram (ERD)
• 🗃️ Database Schema
  • Tables Design
  • Trigger Function
• 📝 SQL Queries
  • Query 1: INNER JOIN
  • Query 2: NOT EXISTS
  • Query 3: WHERE Clause
  • Query 4: GROUP BY and HAVING
• 🎯 SQL Theory Questions & Answers
  • Question 1: Foreign Key
  • Question 2: WHERE vs HAVING
  • Question 3: Primary Key
  • Question 4: INNER JOIN vs LEFT JOIN
• 🔗 Project Links

🎯 Objectives

The objectives of this project are to:

• Design an ERD with One-to-One, One-to-Many, and Many-to-One relationships
• Understand and implement Primary Keys and Foreign Keys
• Write SQL queries using:
  • INNER JOIN
  • NOT EXISTS
  • WHERE
  • GROUP BY and HAVING

📊 System Description

The Vehicle Rental System manages the following entities:

• Users
• Vehicles
• Bookings

Each entity is designed to represent real-world business logic and maintain data integrity.

---

🗂️ Database Design & Business Rules

1. Users Table

The Users table stores information about system users.

Attributes:

• User ID (Primary Key)
• Role (Admin / Customer)
• Name, Email (Unique)
• Password, Phone Number

Business Rules:

• Each user must have a unique email address.
• Users can create multiple bookings.
• Role-based access control (Admin/Customer).

2. Vehicles Table

The Vehicles table stores information about rentable vehicles.

Attributes:

• Vehicle ID (Primary Key), Vehicle Name
• Vehicle Type (car / bike / truck), Model
• Registration Number (Unique)
• Rental Price per Day
• Availability Status (Available / Rented / Maintenance)

Business Rules:

• Each vehicle must have a unique registration number.
• Vehicle availability determines booking eligibility.
• Rental price must be positive.

3. Bookings Table

The Bookings table stores rental transaction details.

Attributes:

• Booking ID (Primary Key)
• User ID (Foreign Key)
• Vehicle ID (Foreign Key)
• Start Date, End Date
• Booking Status (Pending / Confirmed / Completed / Cancelled)
• Total Cost

Business Rules:

• Each booking is associated with exactly one user and one vehicle.
• A user can make multiple bookings.
• A vehicle can have multiple bookings over time.
• Each booking is associated with exactly one user and one vehicle
• End date must be after or equal to start date

---

📚 Entity Relationship Diagram (ERD)

The ERD represents the relationships between Users, Vehicles, and Bookings.

Relationship:

• One-to-Many: Users → Bookings
  • One user can make multiple bookings
  • Each booking belongs to exactly one user
• Many-to-One: Bookings → Vehicles
  • One vehicle can have multiple bookings (over different time periods)
  • Each booking is for exactly one vehicle
• One-to-One (Logical): Each active booking connects exactly one user to one vehicle for a specific time period

Key Components:

• Primary Keys: user_id, vehicle_id, booking_id
• Foreign Keys: user_id in Bookings references Users, vehicle_id in Bookings references Vehicles
• Cardinality: Clearly defined relationship types
• Status Fields: booking_status, availability_status

---

🗃️ Database Schema

Tables Design

1. Users Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    role VARCHAR(20) NOT NULL DEFAULT 'Customer' CHECK (role IN ('Admin', 'Customer')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Vehicles Table

CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('car', 'bike', 'truck')),
    model VARCHAR(50) NOT NULL,
    registration_number VARCHAR(50) UNIQUE NOT NULL,
    rental_price DECIMAL(10,2) NOT NULL CHECK (rental_price > 0),
    status VARCHAR(20) DEFAULT 'available' 
        CHECK (status IN ('available', 'rented', 'maintenance'))
);

3. Bookings Table

CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'confirmed', 'completed', 'cancelled')),
    total_cost DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (total_cost >= 0),

    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE,
    CHECK (end_date >= start_date)
);

• Create trigger function for Automatic Cost Calculation

CREATE FUNCTION calculate_total_costs()
RETURNS TRIGGER AS 
$$
DECLARE
    rental_days int;
    daily_rate DECIMAL(10,2);
BEGIN
    rental_days := NEW.end_date - NEW.start_date;
    IF rental_days < 1 THEN 
        rental_days := 1; 
    END IF;
    
    -- Get rental price per day
    SELECT rental_price INTO daily_rate
    FROM vehicles 
    WHERE vehicle_id = NEW.vehicle_id;
    
    -- Calculate total cost
    NEW.total_cost := rental_days * daily_rate;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER trg_calculate_total_costs
BEFORE INSERT OR UPDATE ON bookings
FOR EACH ROW
EXECUTE FUNCTION calculate_total_costs();

📝 SQL Queries

All required SQL queries are included in the queries.sql file.

Query 1: INNER JOIN

Retrieve booking information along with customer name and vehicle name.

SELECT 
    b.booking_id,
    u.name AS customer_name,
    v.name,
    b.start_date,
    b.end_date,
    b.status
FROM bookings b
INNER JOIN users u ON b.user_id = u.user_id
INNER JOIN vehicles v ON b.vehicle_id = v.vehicle_id
ORDER BY b.booking_id;

Query 2: NOT EXISTS

Find all vehicles that have never been booked.

SELECT 
    v.vehicle_id,
    v.name,
    v.type,
    v.model,
    v.registration_number,
    v.rental_price,
    v.status
FROM vehicles v
WHERE NOT EXISTS (
    SELECT 1 
    FROM bookings b 
    WHERE b.vehicle_id = v.vehicle_id
)
ORDER BY v.vehicle_id;

Query 3: WHERE Clause

Retrieve all available vehicles of a specific type (ex: car)

SELECT 
    vehicle_id,
    name,
    type,
    model,
    registration_number,
    rental_price,
    status
FROM vehicles
WHERE type = 'car' AND status = 'available';

Query 4: GROUP BY and HAVING

Retrieve vehicles with more than two bookings.

SELECT 
    v.name,
    COUNT(b.booking_id) AS total_bookings
FROM vehicles v
LEFT JOIN bookings b ON v.vehicle_id = b.vehicle_id
GROUP BY v.name, v.type
HAVING COUNT(b.booking_id) > 2;

🎯 SQL Theory Questions & Answers

Question 1: What is a foreign key and why is it important in relational databases?

Answer:
A foreign key is a column (or set of columns) in a table that creates a link between data in two tables. It references the primary key of another table, establishing a relationship that ensures referential integrity.

Importance:

• Prevents invalid data entry – Only values existing in the referenced table are allowed
• Maintains consistency – Ensures data relationships remain valid across tables
• Enables meaningful joins – Allows combining related data from multiple tables
• Automatic restrictions – Can prevent deletion/updates of parent records with existing child records
• Defines clear relationships – Documents how tables are connected (one-to-many, many-to-one)

Example:
Students table has student_id as primary key.
Orders table has order_id as primary key and student_id as foreign key referencing Students.student_id.

This ensures every order is linked to a valid student.

---

Question 2: What is the difference between WHERE and HAVING clauses in SQL?

Answer:

• WHERE clause filters rows before grouping (GROUP BY). It cannot use aggregate functions.
• HAVING clause filters groups after grouping (GROUP BY). It is used with aggregate functions.

Key Differences:

Aspect	WHERE Clause	HAVING Clause
Execution Time	Before GROUP BY	After GROUP BY
Operates On	Individual rows	Grouped rows
Aggregate Functions	Cannot use SUM, COUNT, AVG	Can use SUM, COUNT, AVG
Position in Query	Comes before GROUP BY	Comes after GROUP BY

Example:

SELECT department, COUNT(*) 
FROM employees 
WHERE salary > 30000          -- Filters rows before grouping
GROUP BY department 
HAVING COUNT(*) > 5;          -- Filters groups after grouping

Proper use ensures accurate data filtering at correct stages of query execution.

Question 3: What is a primary key and what are its characteristics?

Answer:
A primary key is a column (or set of columns) that uniquely identifies each row in a table.

Characteristics:

1. Unique – No two rows can have the same primary key value
2. Not NULL – Cannot contain NULL values
3. Immutable – Should not change over time (best practice)
4. Single per table – Only one primary key per table (though it can be composite)
5. Indexed automatically – Creates a clustered index by default (in most DBMS)

Example:
In Employees table, employee_id can be primary key as each employee has a unique ID.

Provides a reliable way to identify, reference, and link records across tables.

---

Question 4: What is the difference between INNER JOIN and LEFT JOIN in SQL?

Answer:

• INNER JOIN returns only matching rows from both tables (intersection).
• LEFT JOIN returns all rows from the left table, and matching rows from the right table (non-matching rows get NULLs).

Key Differences:

Aspect	INNER JOIN	LEFT JOIN
Matching	Only matching rows	All from left + matching from right
Result Size	Result ≤ both tables	Result ≥ left table
NULL Handling	No NULLs in join columns	NULLs for non-matching right columns
Data Loss	May lose unmatched rows	Preserves all left table rows

Example:

-- INNER JOIN: Only customers with orders
SELECT * FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LEFT JOIN: All customers, with or without orders
SELECT * FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Choosing the correct join type ensures you get the exact dataset needed for reporting or analysis.

🔗 Project Links

🌍 ERD Diagram

• ERD URL: Vehicle Rental System ERD

• Tool: DrawSQL

• Features: Interactive diagram with table relationships and attributes

📁 GitHub Repository: GitHub Link