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, *company (TRANSPORT_ORGANIZER))

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(*driver, *vehicle)

TRIP(trid_id, *transport_organizer, *route, free_seats, date, status)

DRIVES_ON(*driver, *trip)

ROUTE(id, *transport_organizer, *from_loc (LOCATION), *to_loc (LOCATION))

DAY_ACTIVE(*route, day)

LOCATION(latitude, longitude, time, name)

TRIP_STOPS(*trip, *location, at_time)

TICKET(id, *for_trip (TRIP), *linked_with (TICKET), *gets_on_loc (LOCATION), *gets_off_loc (LOCATION), *payment, *account, *review, 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, *by_account (ACCOUNT), date, total_price, n_tickets)

FAVORITE(*route, *account)

REVIEW(review_id, *account, *ticket, description, rating)

DDL - Creation and Deletion of Tables

Relational Schema Diagram