== Relational Mapping - Relational Schema and DDL ==

=== Notation ===

  1.   Relations are denoted as: `CAPITALIZED LETTERS`(attr₁, attr₂, ..., attrₙ), for a relation with 𝑛 attributes.

  2.   Primary key attributes are **__underscored and bolded__**.

  3.   Foreign key attributes have an asterisk * prefixed before them.

    __Note: Some foreign key attributes have the table they are referencing right after them to clarify this.__

=== Relations - Relational Schema ===

`ACCOUNT`(__**account_id**__, account_email, name, surname, password)

`TRANSPORT_ORGANIZER`(__***account_id**__ (`ACCOUNT`), company_name, company_embg)

`ADMIN`(__***account_id**__ (`ACCOUNT`))

`STUDENT`(__***account_id**__ (`ACCOUNT`), university, index)

`DRIVER`(__***account_id**__ (`ACCOUNT`), years_experience, *works_for_id(`TRANSPORT_ORGANIZER`))

`VEHICLE`(__**vehicle_id**__, plate_num, model, brand, capacity, year_manuf)

`AUTOMOBILE`(__***vehicle_id**__ (`VEHICLE`))

`TRAIN`(__***vehicle_id**__ (`VEHICLE`))

`BUS`(__***vehicle_id**__ (`VEHICLE`))

`VAN`(__***vehicle_id**__ (`VEHICLE`))

`DRIVER_VEHICLE_OPERATION`(__***driver_id**__(`DRIVER`), __***vehicle_id**__(`VEHICLE`))

`TRIP`(__**trid_id**__, __***transport_organizer_id**__(`TRANSPORT_ORGANIZER`), __***route_id**__(`ROUTE`), free_seats, date, status)

`DRIVES_ON`(__***driver_id**__(`DRIVER`), __***trip_id**__(`TRIP`))

`ROUTE`(__**id**__, *transport_organizer_id(`TRANSPORT_ORGANIZER`), *from_loc (`LOCATION`), *to_loc (`LOCATION`))

`DAY_ACTIVE`(__***route_id**(`ROUTE`) __**day**__)

`LOCATION`(__**location_id**__, latitude, longitude, name)

`TRIP_STOPS`(__***trip_id**__(`TRIP`), __***location**__, time)

`TICKET`(__**id**__, *trip_id(`TRIP`), *gets_on_loc (`LOCATION`), *gets_off_loc (`LOCATION`), *account_id (`ACCOUNT`), date_purchased, time_purchased, price, seat)

`TICKET_RELATIONS`(*parent_ticket_id(`TICKET`), *child_ticket_id(`TICKET`))

`STUDENT_TICKET`(__***ticket_id**__ (`TICKET`), discount)

`CHILD_TICKET`(__***ticket_id**__ (`TICKET`), discount, embg, parent_embg)

`PAYMENT`(__**payment_id**__, __***account_id**__ (`ACCOUNT`), date, total_price, n_tickets)

`FAVORITE`(__***route_id**__(`ROUTE`), __***account_id**__ (`ACCOUNT`))

`REVIEW`(__**review_id**__, __***account_id**__ (`ACCOUNT`), *ticket_id, description, rating)

== DDL - Creation and Deletion of Tables ==
[attachment:routemk_create_and_delete.sql​ RouteMK_Create_and_Delete]
== Relational Schema Diagram ==
[[Image(RouteMK-Relational-Schema.jpg​​​)]]