Car Rental Reservation System

A term project for Relational Database Design and SQL (COMP 1630) at BCIT. Designed and implemented a full database solution for a car rental business — from ERD to stored procedures to business reporting queries.

Schema Design

Three core tables with enforced referential integrity:

• Car — vehicle inventory (make, model, year)
• AvailabilityCalendar — daily availability per vehicle
• ReservationDetails — customer bookings with date constraints

Reservation Stored Procedure

Handles atomic reservations with concurrency locking — checks availability, inserts the booking, and marks dates unavailable in a single transaction:

CREATE OR ALTER PROCEDURE dbo.sp_ReserveCar
  @CarId INT, @StartDate DATE, @EndDate DATE
AS
BEGIN
  BEGIN TRANSACTION;
  -- Lock and check availability
  IF EXISTS (
    SELECT 1 FROM AvailabilityCalendar WITH (UPDLOCK, HOLDLOCK)
    WHERE CarId = @CarId
      AND CalendarDate BETWEEN @StartDate AND @EndDate
      AND IsAvailable = 0
  )
  BEGIN ROLLBACK; RAISERROR('Car not available.', 16, 1); RETURN; END

  INSERT INTO ReservationDetails (CarId, StartDate, EndDate)
  VALUES (@CarId, @StartDate, @EndDate);

  UPDATE AvailabilityCalendar SET IsAvailable = 0
  WHERE CarId = @CarId AND CalendarDate BETWEEN @StartDate AND @EndDate;
  COMMIT;
END;

Business Analytics Queries

• Available cars on a given date
• All reservations for a specific vehicle
• Monthly utilization heatmap (pivoted by car)

Tools

SQL Server T-SQL Excel

Links

• GitHub Repository
• Full SQL Source