PostgreSQL Database Sample ‘carrentaldb’
In this discussion, we will use an example database named carrentaldb, which is designed for managing a car rental service. The Entity-Relationship Diagram (ERD) provided serves as a visual representation of how various entities within the system interact with each other. This ERD will help us understand the structure of the database, including tables, relationships, and key attributes.
Overview of the carrentaldb Database
The rentalcardb database consists of several tables that represent key aspects of a car rental business. Below is a breakdown of the main entities and their relationships:
- Branches (branches)
- Stores information about rental locations, including branch name, location, and contact details.
- Cars (cars)
- Contains details about rental vehicles such as brand, model, year, color, license plate, rental price per day, and availability status.
- Each car is linked to a specific branch (
branch_id).
- Customers (customers)
- Stores customer details, including first name, last name, contact information, address, driving license number, and date of birth.
- Employees (employees)
- Holds data on employees working in different branches, including their names, email, phone number, position, and assigned branch (
branch_id).
- Holds data on employees working in different branches, including their names, email, phone number, position, and assigned branch (
- Reservations (reservations)
- Manages car rental bookings, linking a customer (
customer_id), a car (car_id), and an employee (employee_id) who processed the reservation. - Includes details such as pickup date, return date, total rental price, and rental status.
- Manages car rental bookings, linking a customer (
- Payments (payments)
- Tracks customer payments for rentals, storing details like reservation ID (
reservation_id), amount paid, payment date, method, and status.
- Tracks customer payments for rentals, storing details like reservation ID (
- Car Maintenance (carmaintenance)
- Records vehicle maintenance activities, including service date, details, cost, and next scheduled service.
- Each maintenance record is linked to a car (
car_id).
The tables list are as shown below:
1. branches
branch_id | branch_name | location | phone
-----------+---------------------+-----------------+--------------
1 | NY Rental Hub | New York, NY | 212-555-1234
2 | LA Car Center | Los Angeles, CA | 310-555-5678
3 | Chicago Auto Rent | Chicago, IL | 312-555-7890
4 | Houston Drive | Houston, TX | 713-555-2345
5 | Miami Cars | Miami, FL | 305-555-6789
6 | Seattle Car Rentals | Seattle, WA | 206-555-4321
7 | Denver Drive | Denver, CO | 303-555-8765
8 | Atlanta Autos | Atlanta, GA | 404-555-3456
9 | Boston Rent-a-Car | Boston, MA | 617-555-9876
10 | Las Vegas Rentals | Las Vegas, NV | 702-555-5432
(10 rows)
2. cars
car_id | brand | model | year | color | license_plate | rental_price_per_day | status | branch_id
--------+-----------+---------+------+--------+---------------+----------------------+-----------+-----------
1 | Toyota | Corolla | 2021 | Red | ABC123 | 40.00 | Available | 1
2 | Honda | Civic | 2020 | Blue | XYZ789 | 45.00 | Rented | 2
3 | Ford | Focus | 2019 | Black | LMN456 | 38.00 | Available | 3
4 | Chevrolet | Malibu | 2022 | White | DEF234 | 50.00 | Rented | 4
5 | Nissan | Altima | 2021 | Silver | GHI567 | 42.00 | Available | 5
6 | BMW | X5 | 2023 | Gray | JKL890 | 100.00 | Available | 6
7 | Mercedes | C-Class | 2022 | Black | MNO123 | 95.00 | Rented | 7
8 | Hyundai | Sonata | 2020 | Blue | PQR456 | 39.00 | Available | 8
9 | Tesla | Model 3 | 2023 | White | STU789 | 120.00 | Rented | 9
10 | Kia | Optima | 2019 | Red | VWX012 | 35.00 | Available | 10
(10 rows)
3. customers
customer_id | first_name | last_name | email | phone | address | driving_license | date_of_birth
-------------+------------+-----------+----------------------+------------+--------------------+-----------------+---------------
1 | John | Doe | john.doe@email.com | 1234567890 | 123 Main St, NY | DL123456 | 1985-06-15
2 | Jane | Smith | jane.smith@email.com | 0987654321 | 456 Elm St, CA | DL654321 | 1990-09-25
3 | Alice | Johnson | alice.j@email.com | 1112223333 | 789 Oak St, TX | DL789012 | 1995-02-10
4 | Robert | Brown | robert.b@email.com | 2223334444 | 321 Maple St, FL | DL345678 | 1980-11-20
5 | Emily | Davis | emily.d@email.com | 5556667777 | 654 Pine St, WA | DL567890 | 1992-08-30
6 | Michael | Wilson | michael.w@email.com | 8889990000 | 987 Birch St, IL | DL678901 | 1988-05-12
7 | Jessica | Taylor | jessica.t@email.com | 7778889999 | 246 Cedar St, OH | DL789123 | 1993-07-14
8 | David | Anderson | david.a@email.com | 6667778888 | 135 Spruce St, GA | DL890234 | 1987-04-05
9 | Sarah | Martinez | sarah.m@email.com | 4445556666 | 579 Walnut St, NV | DL901345 | 1996-10-22
10 | James | Harris | james.h@email.com | 3334445555 | 860 Redwood St, AZ | DL012456 | 1983-03-18
(10 rows)
4. employees
employee_id | first_name | last_name | email | phone | position | branch_id
-------------+------------+-----------+----------------------+------------+------------+-----------
1 | Michael | Brown | michael.b@email.com | 3334445555 | Manager | 1
2 | Sara | Davis | sara.d@email.com | 4445556666 | Agent | 2
3 | Daniel | Miller | daniel.m@email.com | 5556667777 | Supervisor | 3
4 | Sophia | Lopez | sophia.l@email.com | 6667778888 | Clerk | 4
5 | William | Gonzalez | william.g@email.com | 7778889999 | Technician | 5
6 | Olivia | Hernandez | olivia.h@email.com | 8889990000 | Manager | 6
7 | Ethan | Moore | ethan.m@email.com | 9990001111 | Agent | 7
8 | Isabella | Clark | isabella.c@email.com | 0001112222 | Supervisor | 8
9 | Mason | Rodriguez | mason.r@email.com | 1112223333 | Clerk | 9
10 | Ava | Lee | ava.l@email.com | 2223334444 | Technician | 10
(10 rows)
5. Reservations
reservation_id | customer_id | car_id | employee_id | pickup_date | return_date | total_rental_price | status
----------------+-------------+--------+-------------+-------------+-------------+--------------------+-----------
1 | 1 | 2 | 1 | 2025-02-01 | 2025-02-05 | 180.00 | Confirmed
2 | 2 | 1 | 2 | 2025-02-10 | 2025-02-12 | 80.00 | Pending
3 | 3 | 5 | 3 | 2025-03-15 | 2025-03-18 | 126.00 | Completed
4 | 4 | 3 | 4 | 2025-04-05 | 2025-04-10 | 190.00 | Canceled
5 | 5 | 4 | 5 | 2025-05-10 | 2025-05-14 | 200.00 | Confirmed
6 | 6 | 7 | 6 | 2025-06-01 | 2025-06-03 | 190.00 | Completed
7 | 7 | 8 | 7 | 2025-07-20 | 2025-07-22 | 78.00 | Pending
8 | 8 | 9 | 8 | 2025-08-15 | 2025-08-17 | 240.00 | Confirmed
9 | 9 | 6 | 9 | 2025-09-25 | 2025-09-30 | 500.00 | Completed
10 | 10 | 10 | 10 | 2025-10-10 | 2025-10-15 | 175.00 | Canceled
(10 rows)
6. payments
payment_id | reservation_id | amount_paid | payment_date | payment_method | status
------------+----------------+-------------+--------------+----------------+---------
1 | 1 | 180.00 | 2025-02-01 | Credit Card | Paid
2 | 2 | 80.00 | 2025-02-10 | Cash | Pending
3 | 3 | 126.00 | 2025-03-15 | PayPal | Paid
4 | 4 | 0.00 | 2025-04-05 | Credit Card | Failed
5 | 5 | 200.00 | 2025-05-10 | Cash | Paid
6 | 6 | 190.00 | 2025-06-01 | PayPal | Paid
7 | 7 | 78.00 | 2025-07-20 | Credit Card | Pending
8 | 8 | 240.00 | 2025-08-15 | Cash | Paid
9 | 9 | 500.00 | 2025-09-25 | Credit Card | Paid
10 | 10 | 0.00 | 2025-10-10 | PayPal | Failed
(10 rows)
7. carmaintenances
maintenance_id | car_id | service_date | service_details | cost | next_service_due
----------------+--------+--------------+---------------------------------+--------+------------------
1 | 1 | 2024-01-15 | Oil change and tire rotation | 120.50 | 2024-07-15
2 | 2 | 2024-02-10 | Brake pad replacement | 250.00 | 2024-08-10
3 | 3 | 2024-03-05 | Engine diagnostics and tuning | 180.75 | 2024-09-05
4 | 1 | 2024-04-20 | Battery replacement | 150.00 | 2024-10-20
5 | 4 | 2024-05-18 | Transmission fluid change | 300.00 | 2024-11-18
6 | 5 | 2024-06-22 | Wheel alignment and balancing | 130.00 | 2024-12-22
7 | 3 | 2024-07-07 | Suspension check and repair | 275.00 | 2025-01-07
8 | 2 | 2024-08-12 | Air conditioning system check | 90.00 | 2025-02-12
9 | 4 | 2024-09-01 | Exhaust system check and repair | 200.00 | 2025-03-01
10 | 5 | 2024-10-15 | Full vehicle inspection | 350.00 | 2025-04-15
The `carrentaldb` database is structured to efficiently manage a car rental business, ensuring smooth handling of vehicle reservations, customer information, employee roles, payments, and maintenance records. This ERD provides a clear visualization of the system, making it easier to understand the relationships between different entities.
