Changes between Version 65 and Version 66 of DatabaseProgramming


Ignore:
Timestamp:
05/20/26 22:37:25 (6 days ago)
Author:
231109
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v65 v66  
    1010
    1111{{{
    12 #!sql
    1312CREATE OR REPLACE PROCEDURE pr_sell_ticket(
    1413    IN p_passenger_embg CHAR(13),
    … …  
    148147
    149148{{{
    150 #!sql
    151149CREATE OR REPLACE PROCEDURE cancel_reservation(p_reservation_id INT)
    152150AS $$
    … …  
    181179
    182180{{{
    183 #!sql
    184181CREATE OR REPLACE PROCEDURE apply_seasonal_discount(p_route_id INT, p_discount_pct NUMERIC)
    185182AS $$
    … …  
    210207
    211208{{{
    212 #!sql
    213209CREATE OR REPLACE PROCEDURE update_trip_delay(p_trip_id INT, p_delay_mins INT)
    214210AS $$
    … …  
    234230
    235231{{{
    236 #!sql
    237232CREATE OR REPLACE PROCEDURE reassign_passengers_to_new_trip(p_cancelled_trip_id INT, p_new_trip_id INT)
    238233AS $$
    … …  
    266261
    267262{{{
    268 #!sql
    269263CREATE OR REPLACE PROCEDURE transfer_employee(
    270264    p_embg CHAR(13),
    … …  
    294288
    295289{{{
    296 #!sql
    297290CREATE OR REPLACE PROCEDURE assign_platform_to_trip(
    298291    p_trip_id INT,
    … …  
    322315
    323316{{{
    324 #!sql
    325317CREATE OR REPLACE PROCEDURE change_trip_platform(
    326318    p_trip_id INT,
    … …  
    353345
    354346{{{
    355 #!sql
    356347CREATE FUNCTION auto_set_reservation_status()
    357348RETURNS TRIGGER AS $$
    … …  
    377368
    378369{{{
    379 #!sql
    380370CREATE  FUNCTION prevent_duplicate_seat_booking()
    381371RETURNS TRIGGER AS $$
    … …  
    408398
    409399{{{
    410 #!sql
    411400CREATE FUNCTION update_trip_status_based_on_delay()
    412401RETURNS TRIGGER AS $$
    … …  
    432421
    433422{{{
    434 #!sql
    435423CREATE FUNCTION auto_set_transaction_date()
    436424RETURNS TRIGGER AS $$
    … …  
    454442
    455443{{{
    456 #!sql
    457444CREATE FUNCTION prevent_employee_trip_overlap()
    458445RETURNS TRIGGER AS $$
    … …  
    486473
    487474{{{
    488 #!sql
    489475CREATE OR REPLACE FUNCTION check_and_mark_overbooked()
    490476RETURNS TRIGGER
    … …  
    525511
    526512{{{
    527 #!sql
    528513CREATE FUNCTION prevent_station_delete_active_trip()
    529514RETURNS TRIGGER AS $$
    … …  
    561546=== Функција 1 : free_seats_trip
    562547
    563 [[Image("Screenshot 2026-05-20 165507.png", 300px)]]
    564 
    565 Оваа функција пресметува колку слободни седишта има на одредено патување. Го зема капацитетот на возот и го одзема бројот на веќе продадени билети за тој trip. Се користи за проверка на достапност на места пред продажба или резервација на билети.
    566 
    567 
    568 === Функција 1 : free_seats_trip
    569 
    570 {{{
    571 #!sql
     548{{{
    572549CREATE OR REPLACE FUNCTION free_seats_trip(
    573550    p_trip_id INT
    … …  
    597574SELECT free_seats_trip(155);
    598575}}}
    599 Оваа функција пресметува колку слободни седишта има на одредено патување. Таа ја зема капацитетот на возот и го одзема бројот на веќе продадени билети за тој trip. Во проектот се користи за проверка на достапност на места пред продажба или резервација на билети.
     576Оваа функција пресметува колку слободни седишта има на одредено патување. Го зема капацитетот на возот и го одзема бројот на веќе продадени билети за тој trip. Се користи за проверка на достапност на места пред продажба или резервација на билети.
    600577
    601578=== Функција 2 : get_route_stations
    602579
    603580{{{
    604 #!sql
    605581CREATE FUNCTION get_route_stations(
    606582    p_route_id INT
    … …  
    636612
    637613{{{
    638 #!sql
    639614CREATE FUNCTION get_next_departing_trains()
    640615RETURNS TABLE (
    … …  
    696671=== Функција 5 : get_trip_revenue_efficiency
    697672
    698 [[Image("Screenshot 2026-05-20 171652.png", 300px)]]
    699 
    700 [[Image("Screenshot 2026-05-20 171658.png", 300px)]]
    701 
     673{{{
     674CREATE OR REPLACE FUNCTION get_trip_revenue_efficiency(p_trip_id INT)
     675RETURNS TEXT AS $$
     676DECLARE
     677    v_capacity INT;
     678    v_sold_count INT;
     679    v_total_revenue NUMERIC;
     680    v_occupancy_pct NUMERIC;
     681    v_rev_per_seat NUMERIC;
     682    v_result TEXT;
     683BEGIN
     684    SELECT t.capacity INTO v_capacity
     685    FROM "Train Trip" tt
     686    JOIN Train t ON tt.Traintrain_id = t.train_id
     687    WHERE tt.trip_id = p_trip_id;
     688
     689    SELECT COUNT(*), COALESCE(SUM(price), 0)
     690    INTO v_sold_count, v_total_revenue
     691    FROM Ticket
     692    WHERE "Train Triptrip_id" = p_trip_id;
     693
     694    IF v_capacity > 0 THEN
     695        v_occupancy_pct := (v_sold_count::NUMERIC / v_capacity) * 100;
     696        v_rev_per_seat := v_total_revenue / v_capacity;
     697    ELSE
     698        RETURN 'Error: Train has no capacity';
     699    END IF;
     700
     701    IF v_occupancy_pct >= 85 THEN
     702        v_result := 'High Demand (' || ROUND(v_occupancy_pct, 1) || '%)';
     703    ELSIF v_occupancy_pct >= 50 THEN
     704        v_result := 'Optimal (' || ROUND(v_occupancy_pct, 1) || '%)';
     705    ELSE
     706        v_result := 'Low Efficiency (' || ROUND(v_occupancy_pct, 1) || '%)';
     707    END IF;
     708
     709    RETURN v_result || ' | Rev/Seat: ' || ROUND(v_rev_per_seat, 2);
     710END;
     711$$ LANGUAGE plpgsql;
     712
     713SELECT
     714    tt.trip_id,
     715    r.route_name,
     716    tt.departure_time,
     717    get_trip_revenue_efficiency(tt.trip_id) AS performance_report
     718FROM "Train Trip" tt
     719JOIN Route r ON tt.Routeroute_id = r.route_id
     720WHERE tt.trip_status = 'Completed'
     721ORDER BY tt.departure_time DESC
     722LIMIT 150;
     723}}}
    702724Оваа функција ја анализира ефикасноста на едно патување со воз врз основа на пополнетост и приход. Го пресметува процентот на искористеност на капацитетот и просечниот приход по седиште, па враќа оценка како „High Demand“, „Optimal“ или „Low Efficiency“ заедно со финансискиот резултат. Cе користи за анализа на профитабилноста и перформансите на завршените патувања.
    703725
    704726=== Функција 6 : get_passenger_name_by_id
    705727
    706 [[Image("Screenshot 2026-05-20 172105.png", 300px)]]
    707 
     728{{{
     729CREATE OR REPLACE FUNCTION get_passenger_name_by_id(p_id INT)
     730RETURNS VARCHAR AS $$
     731DECLARE
     732    v_full_name VARCHAR;
     733BEGIN
     734    SELECT p.first_name || ' ' || p.last_name INTO v_full_name
     735    FROM Passenger pass
     736    JOIN Person p ON pass.PersonEMBG = p.EMBG
     737    WHERE pass.passenger_id = p_id;
     738
     739    RETURN COALESCE(v_full_name, 'Passenger not found');
     740END;
     741$$ LANGUAGE plpgsql;
     742
     743SELECT get_passenger_name_by_id(500);
     744}}}
    708745Оваа функција го враќа целосното име на патник според неговиот passenger_id. Ги спојува името и презимето од табелата Person. Ако патникот не постои, враќа порака „Passenger not found“. Cе користи за брзо добивање на информации за патници во извештаи и интерфејсот.
    709746
    710747=== Функција 6 : get_most_profitable_route
    711748
    712 [[Image("Screenshot 2026-05-20 172105.png", 300px)]]
    713 
    714 Оваа функција ја наоѓа најпрофитабилната рута во системот. Го пресметува вкупниот приход од билети за секоја рута и ја враќа онаа со најголем приход, заедно со сумата. Во проектот се користи за анализа на бизнис перформанси и идентификација на најисплатливи линии.
    715 
    716 
    717 
    718 
    719 
    720 
    721 
     749{{{
     750CREATE OR REPLACE FUNCTION get_most_profitable_route()
     751RETURNS TEXT AS $$
     752DECLARE
     753    v_route_name VARCHAR;
     754    v_total_revenue NUMERIC;
     755BEGIN
     756    SELECT
     757        r.route_name,
     758        COALESCE(SUM(t.price), 0)
     759    INTO
     760        v_route_name,
     761        v_total_revenue
     762    FROM Route r
     763    JOIN "Train Trip" tt
     764        ON r.route_id = tt.Routeroute_id
     765    JOIN Ticket t
     766        ON tt.trip_id = t."Train Triptrip_id"
     767    GROUP BY r.route_id, r.route_name
     768    ORDER BY SUM(t.price) DESC
     769    LIMIT 1;
     770
     771    RETURN
     772        'Most Profitable Route: ' || v_route_name ||
     773        ' | Total Revenue: ' || ROUND(v_total_revenue, 2);
     774END;
     775$$ LANGUAGE plpgsql;
     776
     777SELECT get_most_profitable_route();
     778}}}
     779Оваа функција ја наоѓа најпрофитабилната рута во системот. Го пресметува вкупниот приход од билети за секоја рута и ја враќа онаа со најголем приход, заедно со сумата. Cе користи за анализа на бизнис перформанси и идентификација на најисплатливи линии.
     780
     781=== Функција 7 : check_frequent_traveler
     782
     783{{{
     784CREATE OR REPLACE FUNCTION check_frequent_traveler(p_passenger_id INT)
     785RETURNS TEXT AS $$
     786DECLARE
     787    v_ticket_count INT;
     788    v_full_name VARCHAR;
     789BEGIN
     790
     791    SELECT p.first_name || ' ' || p.last_name
     792    INTO v_full_name
     793    FROM Passenger pass
     794    JOIN Person p ON pass.PersonEMBG = p.EMBG
     795    WHERE pass.passenger_id = p_passenger_id
     796    LIMIT 1;
     797
     798    SELECT COUNT(t.ticket_id)
     799    INTO v_ticket_count
     800    FROM Ticket t
     801    JOIN Payment pay ON t.Paymentpayment_id = pay.payment_id
     802    JOIN Reservation r ON pay.Reservationreservation_id = r.reservation_id
     803    WHERE r.Passengerpassenger_id = p_passenger_id;
     804
     805    IF v_ticket_count >= 20 THEN
     806        RETURN v_full_name || ' is a VIP Frequent Traveler with ' || v_ticket_count || ' trips.';
     807    ELSIF v_ticket_count >= 10 THEN
     808        RETURN v_full_name || ' is a Frequent Traveler with ' || v_ticket_count || ' trips.';
     809    ELSIF v_ticket_count > 0 THEN
     810        RETURN v_full_name || ' is an Occasional Traveler with ' || v_ticket_count || ' trips.';
     811    ELSE
     812        RETURN v_full_name || ' has no trips.';
     813    END IF;
     814END;
     815$$ LANGUAGE plpgsql;
     816
     817SELECT check_frequent_traveler(800);
     818}}}
     819Оваа функција проверува колку патувања има направено одреден патник во системот. Го пресметува вкупниот број на купени билети поврзани со неговите резервации и врз основа на тоа го класифицира како VIP Frequent Traveler, Frequent Traveler, Occasional Traveler или патник без патувања. Cе користи за анализа на активноста и лојалноста на патниците, како и за идентификација на најактивните корисници.
     820
     821=== Функција 8 : get_revenue_by_period
     822
     823{{{
     824CREATE OR REPLACE FUNCTION get_revenue_by_period(
     825    p_start_date DATE,
     826    p_end_date DATE
     827)
     828RETURNS TEXT AS $$
     829DECLARE
     830    v_total_tickets INT;
     831    v_total_revenue NUMERIC;
     832BEGIN
     833    SELECT
     834        COUNT(*),
     835        COALESCE(SUM(t.price), 0)
     836    INTO
     837        v_total_tickets,
     838        v_total_revenue
     839    FROM Ticket t
     840    JOIN "Train Trip" tt
     841        ON t."Train Triptrip_id" = tt.trip_id
     842    WHERE tt.departure_time::DATE
     843          BETWEEN p_start_date AND p_end_date;
     844
     845    RETURN
     846        'Period: ' || p_start_date || ' to ' || p_end_date ||
     847        ' | Tickets Sold: ' || v_total_tickets ||
     848        ' | Total Revenue: ' || ROUND(v_total_revenue, 2);
     849END;
     850$$ LANGUAGE plpgsql;
     851
     852SELECT get_revenue_by_period('2025-01-01', '2026-03-31');
     853}}}
     854Оваа функција ги пресметува вкупниот број на продадени билети и вкупниот приход остварен во одреден временски период. Ги анализира билетите поврзани со патувања чие време на поаѓање се наоѓа помеѓу внесените датуми и враќа информација за бројот на продадени билети и остварениот приход. Cе користи за финансиска анализа, следење на продажбата и проценка на приходите во одреден период.
     855
     856
     857
     858
     859
     860
     861
     862
     863