B6A3 - DBMS

Vehicle Rental System - Database Design & SQL Queries

---

create 'b6a3' database

create database b6a3

---

creating tables

1. create 'users' table

user_role

create type user_role as enum('admin', 'customer')

create table users(
  id uuid primary key,
  role user_role not null default 'customer',
  name varchar(100) not null,
  email varchar(200) not null unique,
  password varchar(200) not null,
  phone varchar(15)
)

---

2. create 'vehicles' table

vehicle_type

create type vehicle_type as enum('car', 'bike', 'truck')

status type

create type vehicle_status as enum('available', 'rented', 'maintenance')

create table vehicles(
  id uuid primary key,
  name varchar(300) not null,
  type vehicle_type not null,
  model int not null,
  registration_number int unique not null,
  rental_price int check(rental_price > 0),
  status vehicle_status not null default 'available'
)

---

3. create 'bookings' table

booking_status

create type booking_status as enum('pending', 'confirmed', 'completed', 'cancelled')

create table bookings(
  id uuid primary key,
  user_id uuid references users(id) not null,
  vehicle_id uuid references vehicles(id) not null,
  start_date date default now(),
  end_date date not null,
  status booking_status default 'pending',
  total_cost int check(total_cost > 0)
)

---

queries

Query 1: JOIN

Retrieve booking information along with:

• Customer name
• Vehicle name
• Concepts used: INNER JOIN

select
bookings.id as "booking_id",
users.name as "customer_name",
vehicles.name as "vehicle_name",
bookings.start_date,
bookings.end_date,
bookings.status
from bookings
inner join users on bookings.user_id = users.id
inner join vehicles on bookings.vehicle_id = vehicles.id;

---

Query 2: EXISTS

Find all vehicles that have never been booked. Concepts used: NOT EXISTS

select * from vehicles
where not exists
(select * from bookings where bookings.vehicle_id = vehicles.id)

---

Query 3: WHERE

Retrieve all available vehicles of a specific type (e.g. cars). Concepts used: SELECT, WHERE

select * from vehicles where vehicles.type = 'car' and vehicles.status = 'available'

---

Query 4: GROUP BY and HAVING

Find the total number of bookings for each vehicle and display only those vehicles that have more than 2 bookings. Concepts used: GROUP BY, HAVING, COUNT

select vehicles.name, count(*) as "Total Booking" from bookings
inner join vehicles on vehicles.id = bookings.vehicle_id
group by vehicles.name
having count(*) > 2;

---

✅ Assignment Completed — B6A2 by Arafat Hossain