== ER Diagram ==
[[Image(ERModel_RouteMK_v1.png​)]]

== 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.