ER Diagram

Entities

• Entity: Admin
  • Description: An entity that disjointly specializes from the Account entity and uniquely identifies administrators of the system.
  • Primary Key: email - Entered when creating the account.
  • Attributes: None additional to those inherited from Account.

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

• Entity: Account
  • Description: Contains main information about application users.
  • Primary Key: email.
  • 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: plate_num - The registration/plate number of the vehicle.
  • 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: plate_num.

• Entity: Bus
  • Description: A disjoint specialization of the Vehicle entity for buses.
  • Primary Key: plate_num.

• Entity: Train
  • Description: A disjoint specialization of the Vehicle entity for trains.
  • Primary Key: plate_num.

• Entity: Van
  • Description: A disjoint specialization of the Vehicle entity for vans.
  • Primary Key: plate_num.

• 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: id - An artificial 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: email.
  • 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: Composite key (latitude, longitude).
  • Attributes:
    • time: Estimated arrival time.
    • name: Descriptive name of the location.

• Entity: Favorite
  • Description: A weak entity representing a user's favorite routes.
  • Primary Key: Composite key (email, favorite_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 its specialized types (e.g., Student Ticket, Child Ticket).
  • Description: Indicates the specialization of a ticket for specific passenger categories.

• lists: 1:N Relation between Account and Trip.
  • Description: Indicates which trips are attended by a specific account.

• drives_on: N:M Relation between Driver and Route.
  • Description: Indicates which routes are driven by a specific driver.

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

• uses: N:M Relation between Route and Vehicle.
  • Description: Indicates which vehicles are used for a specific route.

• stops_at: N:M Relation between Route 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.

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

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

• back: N:M Relation between Two-Way Ticket and Route.
  • Description: Indicates the return route associated with a two-way ticket.

• forward: N:M Relation between Two-Way Ticket and Route.
  • Description: Indicates the outbound route associated with a two-way ticket.