transportation booking system

erdiagram

erDiagram
    Providers {
        int provider_id PK
        varchar name
        varchar contact
    }
     Vehicles {
        int vehicle_id PK
        int provider_id FK
        varchar vehicle_type
        text vehicle_specific_attributes
    }

    Routes {
        int route_id PK
        varchar vehicle_type
        varchar origin_location
        varchar destination_location
    }

    Journeys {
        int journey_id PK
        int route_id FK
        int vehicle_id FK
        datetime departure
        datetime arrival
        decimal price
    }

    Students {
        int student_id PK
        varchar first_name
        varchar last_name
    }

    Bookings {
        int booking_id PK
        int journey_id FK
        int student_id FK
        int user_id FK
        varchar seat_number
        varchar booking_status
    }

    SeatAvailability {
        int journey_id FK
        varchar seat_number PK
        boolean available
    }

    CBAdmins {
        int user_id PK
        varchar full_name
    }

    Vehicles ||--o{ Journeys : uses
    Providers }o--|| Vehicles : has
    Routes ||--o{ Journeys : defines
    Journeys ||--o{ Bookings : contains
    CBAdmins ||--o{ Bookings : "makes booking"
    Journeys ||--o{ SeatAvailability : tracks
    Bookings }|--|| Students : has