Exercise 1: Car Rental

erDiagram

    client {
        string name
        string ssn "social security number (cnp)"
        string license_serial_num
        string phone
        string address
        int key PK "id_client"
    }

    car {
        string brand
        string model
        int year
        decimal price_per_day
        int stock
        int key PK "id_car"
    }

    booking_state {
        string name
        string label
        string uuid
        int key PK "id_booking_state"
    }

    booking {
        string number
        datetime date_start
        datetime date_end
        decimal duration
        decimal price_per_day
        decimal total
        int key PK "id_booking"
        int id_booking_state FK "id_booking_state"
        int id_car FK "id_car"
        int id_client FK "id_client"
    }

    rental {
        datetime date_start
        datetime date_end
        decimal duration
        decimal total
        string number
        int key PK "id_rental"
        int id_booking FK "id_booking"
        int id_car FK "id_car"
        int id_client FK "id_client"
    }

    client one--many rental : has
    car one--many rental : has
    booking one--many rental : has
    booking_state one--many booking : has
    client one--many booking : has
    car one--many booking : has

Description #

• Create an application for managing car rental services. The application should keep track of customers, available cars, bookings, and rentals.

• Customers have a name, social security number (CNP), license serial number, phone, and address. Cars are identified by brand, model, year of manufacture, rental price per day, and stock.

• A customer can reserve and rent multiple cars simultaneously depending on the stock, and a car brand can be reserved by multiple customers at the same time (since multiple cars are available).

• To register a booking, information about the booking date, duration (days), customer, car, price per day, and the application will need to calculate the total price for the selected period.

• The property “name” in the table “booking_state” can have the values: “Pending”, “Processed” and “Canceled”.

• In entity “booking_state”, name represents the state that will be displayed on the screen, label represents the value that programmer will use to filter the states, and the uuid represents a value to distinguish the states.

• Each booking can be finalized through a rental.

• OBS: The value of the field “uuid” is generated automatically.

Tasks #

• client/car/booking_state/booking/rental: Add, Edit, Delete.

  • When the booking is created, it will automatically become “Pending”.
  • The field “Booking state” from entity “booking” will be read-only in edit view.

• Create a catalog for entities: “booking_state”, “booking”, “car” and “client”.

• Create event listeners for tables that needs validations (e.g., adding a client without a name, car without model, etc., should not be allowed).

• Check if the booking interval for a car is available for a new booking. If it is not → validation message.

• In booking and rental, calculate the total duration and total price.

• Add a new entity action at the booking level “Cancel booking”:

  • It will change the booking status to “Canceled”;
  • A booking cannot be canceled if it is in the “Processed” state → validation message;
  • A booking cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the booking level “Generate rental”:

  • First check if a rental has already been generated from the same booking, if it was → validation message;
  • After creating the rental, the booking will have the “Processed” state;
  • A rental cannot be generated if the booking is “Canceled” → validation message.

• Car availability report: situation of car availability (total number of available cars, stock details).

• Customer report: situation of customers (total number of clients, contact details).

• Booking report: situation of bookings over a certain period (total number of bookings, booking details).

• Rental profit report: calculate profit from rentals (total profit, rental details).