ER Diagram

Data Requirements

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:

• 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.
  • Candidate Key: Composite key of brand and model (not used since plate_num is a superkey).
  • Attributes:
    • model: The specific model of the vehicle.
    • brand: The manufacturer of the vehicle.
    • capacity: The seating capacity of the vehicle.

• 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).

• 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).

• Entity: Automobile
  • Description: A disjoint specialization of the Vehicle entity, identifying cars.
  • Primary Key: plate_num.
  • Attributes:

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

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

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

• Entity: Route
  • Description: Describes a scheduled/frequent route between two or more places.
  • Primary Key: id - An artificial ID for the route.
  • Attributes:
    • date: Scheduled date(s) for the route.
    • free_seats: Derived attribute indicating available seats.

• Entity: Child Ticket
  • Description: A disjoint specialization of the Ticket entity for passengers below a certain age.
  • Primary Key: ticket_id.
  • Attributes:
    • price
    • purchase_date
    • discount
    • SSN
    • parent_SSN

• Entity: One-Way Ticket
  • Description: A disjoint specialization of the Ticket entity for one-direction travel.
  • Primary Key: ticket_id.
  • Attributes:
    • price
    • purchase_date

• Entity: Two-Way Ticket
  • Description: A disjoint specialization of the Ticket entity for round-trip travel.
  • Primary Key: ticket_id.
  • Attributes:
    • expiration_period: Time the ticket is valid.
    • purchase_date
    • price
    • expiration_date: Derived attribute (purchase_date + expiration_period).

• Entity: Account
  • Description: Contains main information about application users.
  • Primary Key: email.
  • Attributes:
    • name
    • surname
    • password
    • university: Where the student studies.
    • student_index

• Entity: Transport Organizer
  • Description: A disjoint specialization of the Account entity for managing trips and schedules.
  • Primary Key: email.
  • Attributes:
    • name
    • surname
    • password
    • company_name
    • company_CIN

• Entity: Driver
  • Description: A disjoint specialization of the Account entity for individuals responsible for vehicle operation.
  • Primary Key: email.
  • Attributes:
    • name
    • surname
    • password

• Entity: Student
  • Description: A disjoint specialization of the Account entity for students enrolled at Macedonian universities.
  • Primary Key: email.
  • Attributes:
    • name
    • surname
    • password
    • university

• Entity: Favorite
  • Description: A weak entity representing a user's favorite routes.
  • Primary Key: Composite key (email, partial key for each favorite route).
  • Attributes:

• 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 local names.

• Entity: Payment
  • Description: A weak entity describing the process of purchasing tickets.
  • Partial Key: artificial ID payment_id.
  • Attributes:
    • date
    • num_tickets
    • total_price: Derived attribute.

Relationships

• makes: 1:N Relation between Account and Payment.
  • Description: Tells which payment was made by which account and is used as an identifying relation for a payment.

• leaves_a: 1:N Relation between Account and Review.
  • Description: Tells which review was left by which user. This relation identifies the entity Review.

• adds: 1:N Relation between Account and Favorite.
  • Description: Tells which account added which route to favorites. This relation identifies the entity Favorite.

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

• works_for: 1:N Relation between Transport Organizer and Driver.
  • Description: Tells which driver works for which transport organizer.

• on: 1:N Relation between Review and Trip.
  • Description: Tells which review was left on which trip. This relation identifies the entity Review.

• organizes: 1:N Relation between Transport Organizer and Route.
  • Description: Tells which transport organizer organized which route.

• is_a: 1:N Relation between Route and Favorite.
  • Description: Tells which route is favorited by which account. This relation identifies the entity Favorite.

• for: 1:N Relation between Route and Trip.
  • Description: Tells which route was made for which trip. This relation identifies the entity Trip.

• on: 1:N Relation between Route and One-Way Ticket.
  • Description: Tells which route was bought by which one-way tickets.

• back: 1:N Relation between Route and Two-Way Ticket.
  • Description: Tells which route was purchased as part of a two-way ticket for the return journey.

• forward: 1:N Relation between Route and Two-Way Ticket.
  • Description: Tells which route was purchased as part of a two-way ticket for the outbound journey.

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

• stops_at: N:M Relation between Route and Location.
  • Description: Tells which locations are included as stops on specific routes.

• operates: N:M Relation between Driver and Vehicle.
  • Description: Tells which vehicles are operated by which drivers.

• uses: N:M Relation between Route and Vehicle.
  • Description: Tells which vehicles are used on which routes.

• drives_on: N:M Relation between Route and Driver.
  • Description: Tells which drivers drive on which routes.

• lists: N:M Relation between Account and Trip.
  • Description: Tells which account has been on which trips.