Функции, процедури и тригери

Процедури

Во овој дел се прикажани процедури кои овозможуваат централизирано извршување на операции над базата на податоци. Процедурите се користат за автоматизација на сложени процеси, вметнување, ажурирање и бришење на податоци.

prodecures.sql​

Процедура 1 : pr_sell_ticket

Оваа процедура автоматски продава билет во системот за возови. Прво проверува дали постојат патникот, патувањето и станиците, и дали седиштето е слободно. Потоа креира плаќање и билет и ако постои резервација ја означува како "Completed". Се користи за автоматизирана и сигурна продажба на билети без грешки и дупли резервации.

Процедура 2 : cancel_reservation

Оваа процедура ја поништува резервацијата и автоматски ги ажурира поврзаните податоци. Кога ќе се повика, статусот на резервацијата се менува во "Cancelled", а сите билети поврзани со таа резервација исто така се означуваат како "Cancelled" Се користи за правилно откажување на резервации и одржување конзистентност меѓу резервациите, плаќањата и билетите.

Процедура 3 : apply_seasonal_discount

Оваа процедура применува сезонски попуст на билетите за сите патувања што припаѓаат на одредена рута. Кога ќе се повика, ја намалува цената на билетите за даден процент и автоматски ги ажурира сите поврзани записи. Се користи за промоции и динамично формирање на цени по рути.

Процедура 4 : update_trip_delay

Оваа процедура го ажурира доцнењето и статусот на едно патување. Кога ќе се повика, го поставува бројот на минути на доцнење и автоматски го менува статусот во "Delayed" ако има доцнење или "On Time" ако нема. Се користи за брзо и точно ажурирање на состојбата на возовите во реално време.

Процедура 5 : reassign_passengers_to_new_trip

Оваа процедура ги префрла сите патници од едно откажано патување на ново патување. Кога ќе се повика, ги ажурира билетите да покажуваат нов "Train Trip", им го менува статусот во "Reassigned", а старото патување го означува како "Cancelled". Се користи за брзо пренасочување на патници при откажани или променети возни линии.

Процедура 6 : transfer_employee

Оваа процедура го префрла вработениот во друга компанија. Прво проверува дали новата компанија постои, а потоа го ажурира записот во Employee табелата. Се користи за управување со вработени и нивно преместување меѓу компании без грешки и неконзистентни податоци.

Процедура 7 : assign_platform_to_trip

Оваа процедура доделува платформа и станица на одредено патување. Кога ќе се повика, го ажурира Train Trip со нова платформа и станица, а ако патувањето не постои, фрла грешка. Се користи за прецизно распоредување на возовите на соодветни платформи во станиците.

Процедура 8 : change_trip_platform

Оваа процедура ја менува платформата на одредено патување. Кога ќе се повика, го ажурира записот во Train Trip и го поставува новиот Platformplatform_id за дадениот trip_id. Се користи за динамично менување на платформите според оперативните потреби на станицата.

Тригери

Во овој дел се прикажани скриптите потребни за дефинирање на тригерите кои овозможуваат автоматска обработка и контрола на податоците во базата. Тригерите се користат за одржување на интегритетот, автоматско ажурирање на вредности и евиденција на промените во системот.

triggers.sql​

Тригер 1 : auto_set_reservation_status

Овој trigger автоматски го поставува статусот на резервацијата како "Active" или "Expired" според датумот на истекување. Се користи за автоматско ажурирање на резервациите и одржување точни податоци во системот

Тригер 2 : prevent_duplicate_seat_booking

Овој trigger спречува дупло резервирање на исто седиште на исто патување со воз. Пред внесување нов билет, системот проверува дали седиштето е веќе резервирано и ако постои, прикажува грешка во спротивно. Се користи за да се избегнат конфликти и да се обезбедат точни резервации на билети.

Тригер 3 : update_trip_status_based_on_delay

Овој trigger автоматски го ажурира статусот на патувањето според доцнењето на возот. Ако бројот на минути за доцнење е поголем од 0, статусот се поставува на "Delayed", а во спротивно на "On Time". Се користи за автоматско следење и прикажување на точниот статус на возовите.

Тригер 4 : auto_set_transaction_date

Овој trigger автоматски ја поставува датата на трансакцијата ако не е внесена при креирање на плаќање. Ако transaction_date е NULL, системот ја става тековната дата. Се користи за да се осигураме дека секое плаќање има точен датум и за автоматско и конзистентно водење на финансиски записи.

Тригер 5 : prevent_employee_trip_overlap

Овој trigger спречува еден вработен да биде доделен на две патувања во исто време. Пред внесување нов запис во "Train Trip", системот проверува дали веќе постои ист вработен со исто време на поаѓање и ако да, фрла грешка. Се користи за да се избегнат конфликти во распоредот и за правилно распределување на вработените.

Тригер 6 : check_and_mark_overbooked

Овој trigger проверува дали бројот на продадени билети го надминува капацитетот на возот за дадено патување. Ако има повеќе билети од дозволени места, автоматски го менува статусот на патувањето во "Overbooked" и дава известување. Се користи за автоматско следење на пополнетост и спречување на преголем број резервации.

Тригер 7 : prevent_station_delete_active_trip

Овој trigger спречува бришење на станица ако таа е веќе користена во билети или патувања. Пред DELETE во табелата Station, системот проверува дали постојат поврзани записи и ако има, фрла грешка. Се користи за заштита на податоците и одржување на интегритет на базата.

Функции

Во овој дел се прикажани функциите кои се користат за извршување на специфични пресметки и обработка на податоците во базата. Функциите овозможуваат повторна употреба на логика и поедноставување на SQL наредбите.

functions.sql​

Функција 1 : free_seats_trip

Оваа функција пресметува колку слободни седишта има на одредено патување. Го зема капацитетот на возот и го одзема бројот на веќе продадени билети за тој trip. Се користи за проверка на достапност на места пред продажба или резервација на билети.

Функција 1 : free_seats_trip

Оваа функција пресметува колку слободни седишта има на одредено патување. Таа ја зема капацитетот на возот и го одзема бројот на веќе продадени билети за тој trip. Во проектот се користи за проверка на достапност на места пред продажба или резервација на билети.

Функција 2 : get_route_stations

Оваа функција ги враќа сите станици за одредена рута, подредени по нивниот редослед на движење. Се користи за прикажување на патната линија на возот (од почетна до крајна станица) и за подобро планирање и преглед на патувањата.

Функција 3 : get_next_departing_trains

Оваа функција ги враќа сите следни патувања со воз кои допрва треба да поаѓаат. Ги прикажува подредени по датум на поаѓање, заедно со нивниот статус. Cе користи за преглед на идни патувања и планирање на распоредот на возовите.

Функција 4 : get_passenger_total_spending

Оваа функција го пресметува вкупниот износ што еден патник го потрошил за билети. Ги собира сите износи од плаќањата поврзани со неговиот EMBG. Cе користи за анализа на потрошувачка и увид во финансиската активност на патниците.

Функција 5 : get_trip_revenue_efficiency

Оваа функција ја анализира ефикасноста на едно патување со воз врз основа на пополнетост и приход. Го пресметува процентот на искористеност на капацитетот и просечниот приход по седиште, па враќа оценка како „High Demand“, „Optimal“ или „Low Efficiency“ заедно со финансискиот резултат. Cе користи за анализа на профитабилноста и перформансите на завршените патувања.

Функција 6 : get_passenger_name_by_id

Оваа функција го враќа целосното име на патник според неговиот passenger_id. Ги спојува името и презимето од табелата Person. Ако патникот не постои, враќа порака „Passenger not found“. Cе користи за брзо добивање на информации за патници во извештаи и интерфејсот.

Функција 6 : get_most_profitable_route

Оваа функција ја наоѓа најпрофитабилната рута во системот. Го пресметува вкупниот приход од билети за секоја рута и ја враќа онаа со најголем приход, заедно со сумата. Во проектот се користи за анализа на бизнис перформанси и идентификација на најисплатливи линии.