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, Deletion and Insertion with dummy data of Tables ==

RouteMK_Create_Delete_Insert​

Relational Schema Diagram