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

RouteMK_create_delete​ RouteMK_dummy_data​

Relational Schema Diagram