ER Diagram

Entities

• Entity: Admin
  • Description: An entity that disjointly specializes from the Account entity and uniquely identifies administrators of the system.
  • Primary Key: account_id - Artificially generated identifier.
  • Attributes: None additional to those inherited from Account.

• Entity: Student
  • Description: A specialization of the Account entity for students enrolled at universities.
  • Primary Key: account_id - Artificially generated identifier.
  • Attributes:
    • university: The university the student attends.
    • index: Unique student identifier.

• Entity: Account
  • Description: Contains main information about application users.
  • Primary Key: account_id - Artificially generated identifier.
  • Attributes:
    • name: User's first name.
    • surname: User's last name.
    • password: Encrypted user password.

• Entity: Vehicle
  • Description: Generalization entity for all means of transport available on the system.
  • Primary Key: vehicle_id - Artificially generated identifier.
  • Candidate Key: plate_num - The registration/plate number of the vehicle. This is not used as a primary key due to its augmentative nature.
  • Attributes:
    • model: The specific model of the vehicle.
    • brand: The manufacturer of the vehicle.
    • capacity: The seating capacity of the vehicle.
    • year_manufactured: Year the vehicle was manufactured.

• Entity: Automobile
  • Description: A disjoint specialization of the Vehicle entity for cars.
  • Primary Key: vehice_id - Artificially generated identifier.

• Entity: Bus
  • Description: A disjoint specialization of the Vehicle entity for buses.
  • Primary Key: vehicle_id - Artificially generated identifier.

• Entity: Train
  • Description: A disjoint specialization of the Vehicle entity for trains.
  • Primary Key: vehicle_id - Artificially generated identifier.

• Entity: Van
  • Description: A disjoint specialization of the Vehicle entity for vans.
  • Primary Key: vehicle_id - Artificially generated identifier.

• Entity: Trip
  • Description: A weak entity representing an instance of a route, used to check information about tickets purchased for a route.
  • Partial Key: trip_id - An artificial ID of a trip.
  • Attributes:
    • status: The state of the trip (e.g., completed, cancelled, in progress).
    • date: Scheduled date for the trip.
    • free_seats: Derived attribute indicating available seats.

• Entity: Review
  • Description: A weak entity describing user ratings for a trip.
  • Partial Key: review_id - An artificial ID for the review.
  • Attributes:
    • description: User-provided context about the trip.
    • rating: An integer rating (out of 5).

• Entity: Ticket
  • Description: Allows a user (Account) to reserve a place on a route.
  • Primary Key: ticket_id - An artificial ID for the ticket.
  • Attributes:
    • price: The base price for the ticket.
    • date_purchased: The date the ticket was purchased.
    • seat: Derived attribute (calculated as capacity - free_seats).
    • type_person_ticket: Discriminator for Student or Child Tickets.

• Entity: Child Ticket
  • Description: A disjoint specialization of the Ticket entity for passengers below a certain age.
  • Primary Key: ticket_id.
  • Attributes:
    • discount: Discount applied to the ticket.
    • embg: Personal ID of the child.
    • parent_embg: Personal ID of the parent.

• Entity: Student Ticket
  • Description: A disjoint specialization of the Ticket entity for student passengers.
  • Primary Key: ticket_id.
  • Attributes:
    • discount: Discount applied to the ticket.

• Entity: Route
  • Description: Describes a scheduled/frequent route between two or more places.
  • Primary Key: route_id - An artificially generated ID for the route.
  • Attributes:
    • free_seats: Derived attribute indicating available seats.

• Entity: Transport Organizer
  • Description: A disjoint specialization of the Account entity for managing trips and schedules.
  • Primary Key: email.
  • Attributes:
    • company_name: Name of the organization.
    • company_embg: Unique company identifier.

• Entity: Driver
  • Description: A disjoint specialization of the Account entity for individuals responsible for vehicle operation.
  • Primary Key: account_id - Artificially generated identifier.
  • Attributes:
    • years_exp: Number of years the driver has been active.

• Entity: Location
  • Description: Contains geographical points for route stops (along the way or final destinations).
  • Primary Key: location_id - Artificially generated identifier.
  • Candidate Key: The composite key (latitude, longitude). Not used due to its unnecessary complexity for implementation and referencing.
  • Attributes:

• name: Descriptive name of the location.

• Entity: Favorite
  • Description: A weak entity representing a user's favorite routes.
  • Primary Key: Composite key (account_email, route_id).

• Entity: Payment
  • Description: A weak entity describing the process of purchasing tickets.
  • Partial Key: payment_id - Artificial ID for the payment.
  • Attributes:
    • date: Payment date.
    • num_tickets: Number of tickets purchased.
    • total_price: Derived attribute.

Relationships

• leaves_a: 1:N Relation between Account and Review.
  • Description: Indicates which account left a review for a trip.

• makes: 1:N Relation between Account and Payment.
  • Description: Indicates which account made a specific payment.

• for_ticket: 1:N Relation between Payment and Ticket.
  • Description: Indicates which payment was made for which ticket.

• for_trip: 1:N Relation between Ticket and Trip.
  • Description: Indicates which trip corresponds to a specific ticket.

• on: 1:N Relation between Review and Trip.
  • Description: Indicates which review was written for which trip.

• organizes: 1:N Relation between Transport Organizer and Route.
  • Description: Indicates which transport organizer is responsible for managing a specific route.

• owns: 1:N Relation between Transport Organizer and Vehicle.
  • Description: Indicates which transport organizer owns a specific vehicle.

• works_for: 1:N Relation between Driver and Transport Organizer.
  • Description: Indicates which transport organizer employs a specific driver.

• stops: 1:N Relation between Route and Location.
  • Description: Indicates which locations are part of a specific route.

• adds: 1:N Relation between Account and Favorite.
  • Description: Indicates which favorite route is added by a specific account.

• linked: 1:N Relation between Ticket and Ticket.
  • Description: Indicates if ticket is multi-way.

• lists: 1:N Relation between Account and Ticket.
  • Description: Indicates which tickets are bought by a specific account.

• from: 1:N Relation between Route and Location.
  • Description: Indicates on which location the route is starting.

• to: 1:N Relation between Route and Location.
  • Description: Indicates on which location the route is ends.

• is_a: 1:N Relation between Route and Favorite.
  • Description: Indicates which routes are marked as favorites by specific accounts.

• for: 1:N Relation between Route and Trip.
  • Description: Indicates which trips belong to a specific route.

• drives_on: N:M Relation between Driver and Trip.
  • Description: Indicates which trips are driven by which drivers.

• operates: N:M Relation between Driver and Vehicle.
  • Description: Indicates which vehicles are operated by a specific driver.

• uses: N:M Relation between Trip and Vehicle.
  • Description: Indicates which vehicles are used for which trips.

• stops: N:M Relation between Trip and Location.
  • Description: Indicates which locations are included as stops for a specific route.

• gets_on: N:M Relation between Ticket and Location.
  • Description: Indicates which location corresponds to a boarding point for a specific ticket.