== 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`(__**transport_organizer_id**__, *accound_id(`ACCOUNT`), company_name, company_embg)

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

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

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

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

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

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

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

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

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

`TRIP`(__**trip_id**__, *transport_organizer_id(`TRANSPORT_ORGANIZER`), *route_id(`ROUTE`), *account_id(`DRIVER`), *vehicle_id(`VEHICLE`), free_seats, date, status)

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

`ROUTE`(__**route_id**__, *transport_organizer_id(`TRANSPORT_ORGANIZER`), *from_loc_id (`LOCATION`), *to_loc_id (`LOCATION`))

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

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

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

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

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

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

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

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

=== DDL - Creation, Deletion and Insertion with dummy data of Tables
[attachment:RouteMK.2.sql​ RouteMK_create_delete]
[attachment:data.sql​ RouteMK_dummy_data]

== Relational Schema Diagram ==
[[Image(RouteMK_relational_schema.png​, 100%​​​)]]