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(email, name, surname, password)

TRANSPORT_ORGANIZER(*account_email (ACCOUNT), company_name, company_embg)

ADMIN(*account_email (ACCOUNT))

STUDENT(*account_email (ACCOUNT), university, index)

DRIVER(*account_email (ACCOUNT), years_experience)

VEHICLE(plate_num, model, brand, capacity, year_manuf)

AUTOMOBILE(*plate_num (VEHICLE))

TRAIN(*plate_num (VEHICLE))

BUS(*plate_num (VEHICLE))

VAN(*plate_num (VEHICLE))

VEHICLE_OPERATION(*account_email(ACCOUNT), *plate_num(VEHICLE))

TRIP(trid_id, *transport_organizer_email(ACCOUNT), *route_id(ROUTE), free_seats, date, status)

DRIVES_ON(*driver_email(DRIVER), *trip_id(TRIP))

ROUTE(id, *transport_organizer_email(ACCOUNT), *from_loc (LOCATION), *to_loc (LOCATION))

DAY_ACTIVE(*route_id(ROUTE) day)

LOCATION(latitude, longitude, time, name)

TRIP_STOPS(*trip, *location, at_time)

TICKET(id, *trip_id(TRIP), *ticket_id(TICKET), *gets_on_loc (LOCATION), *gets_off_loc (LOCATION), *payment_id, *account_email, *review_id, date_purchased, time_purchased, price, seat, two_way)

STUDENT_TICKET(*ticket_id (TICKET), discount)

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

PAYMENT(payment_id, *account_email (ACCOUNT), date, total_price, n_tickets)

FAVORITE(*route_id(ROUTE), *account_email (ACCOUNT))

REVIEW(review_id, *account_email (ACCOUNT), *ticket_id, description, rating)

DDL - Creation and Deletion of Tables

Relational Schema Diagram