Context Navigation

Advanced Database Development

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

Имплементацијата вклучува:

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

Автоматска проверка и намалување на залихата при завршување на нарачка

Data requirements description

При завршување на нарачка потребно е автоматски да се провери дали има доволно примероци на залиха за сите книги што се наоѓаат во нарачката.

Нарачката не смее да премине во статус ZAVRSENA доколку за некоја од книгите нема доволно примероци на залиха.

Доколку има доволно залиха за сите ставки, количината на залиха на секоја книга автоматски се намалува за количината што е нарачана.

Ова правило е имплементирано на ниво на базата на податоци со тригер, со што проверката важи независно од тоа од каде е направена промената на статусот на нарачката.

Trigger function

Креирана е функцијата project.proveri_i_namali_zaliha().

CREATE OR REPLACE FUNCTION project.proveri_i_namali_zaliha()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    stavka RECORD;
BEGIN
    IF OLD.status <> 'ZAVRSENA'
       AND NEW.status = 'ZAVRSENA' THEN

        FOR stavka IN
            SELECT
                s.kniga_id,
                s.kolicina,
                k.naslov,
                k.kolicina_na_zaliha
            FROM project.sodrzi s
            JOIN project.knigi k
                ON s.kniga_id = k.kniga_id
            WHERE s.naracka_id = NEW.naracka_id
        LOOP
            IF stavka.kolicina_na_zaliha < stavka.kolicina THEN
                RAISE EXCEPTION
                    'Нема доволно залиха за книгата "%". Достапно: %, потребно: %',
                    stavka.naslov,
                    stavka.kolicina_na_zaliha,
                    stavka.kolicina;
            END IF;
        END LOOP;

        UPDATE project.knigi k
        SET kolicina_na_zaliha =
            k.kolicina_na_zaliha - s.kolicina
        FROM project.sodrzi s
        WHERE s.naracka_id = NEW.naracka_id
          AND k.kniga_id = s.kniga_id;

    END IF;

    RETURN NEW;
END;
$$;

Функцијата прво ги проверува сите ставки од нарачката. Доколку барем една книга нема доволно залиха, се генерира исклучок и промената на статусот не се извршува.

Само доколку сите ставки ја поминат проверката, се извршува автоматско намалување на залихата.

Trigger

CREATE TRIGGER trg_proveri_i_namali_zaliha
BEFORE UPDATE OF status
ON project.naracki
FOR EACH ROW
EXECUTE FUNCTION project.proveri_i_namali_zaliha();

Тригерот се активира при промена на атрибутот status на нарачката. Функцијата ја извршува проверката само кога статусот преминува од статус различен од ZAVRSENA во ZAVRSENA.

Тестирање со доволна залиха

За тестирање беше употребена нарачка со идентификатор 4 која се наоѓаше во статус VO_OBRABOTKA.

UPDATE project.naracki
SET status = 'ZAVRSENA'
WHERE naracka_id = 4;

Нарачката содржеше еден примерок од книгата Think and Grow Rich. Пред завршувањето на нарачката книгата имаше 11 примероци на залиха.

По успешно завршување на нарачката залихата автоматски беше намалена на 10 примероци.

Со ова е потврдено дека тригерот правилно ја намалува залихата при успешно завршување на нарачката.

Тестирање со недоволна залиха

За проверка на ограничувањето беше поставена залиха 0 за книгата Atomic Habits:

UPDATE project.knigi
SET kolicina_na_zaliha = 0
WHERE kniga_id = 1;

Потоа беше направен обид нарачката со идентификатор 5, која содржи еден примерок од оваа книга, да се постави во статус ZAVRSENA:

UPDATE project.naracki
SET status = 'ZAVRSENA'
WHERE naracka_id = 5;

Базата на податоци ја одби операцијата и ја прикажа пораката:

Нема доволно залиха за книгата "Atomic Habits". Достапно: 0, потребно: 1.

Со ова е потврдено дека нарачка не може да биде означена како завршена доколку нема доволно залиха.

По тестирањето залихата беше вратена:

UPDATE project.knigi
SET kolicina_na_zaliha = 14
WHERE kniga_id = 1;

Аналитички поглед за продажба по книга

Data requirements description

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

Потребни се информации за моменталната залиха, бројот на продадени примероци, остварениот приход и бројот на реализирани нарачки.

За таа цел е креиран погледот project.v_prodazba_po_kniga.

View

CREATE OR REPLACE VIEW project.v_prodazba_po_kniga AS
SELECT
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha,
    COALESCE(SUM(s.kolicina), 0) AS vkupno_prodadeni,
    COALESCE(
        SUM(s.kolicina * s.edinechna_cena),
        0
    ) AS vkupen_prihod,
    COUNT(DISTINCT n.naracka_id) AS broj_naracki
FROM project.knigi k
LEFT JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
LEFT JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
    AND n.status = 'ZAVRSENA'
WHERE n.naracka_id IS NOT NULL
   OR s.naracka_id IS NULL
GROUP BY
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha;

Погледот ги обединува податоците од релациите knigi, sodrzi и naracki и обезбедува повторно употреблив извор на агрегирани информации за продажбата.

Тестирање

SELECT *
FROM project.v_prodazba_po_kniga
ORDER BY vkupen_prihod DESC;

Погледот успешно ги прикажува податоците за продажбата на книгите подредени според вкупниот приход.

Следење на залихата на книгите

Data requirements description

За администраторот е важно навремено да може да ги забележи книгите со мала или целосно потрошена залиха.

За таа цел е креиран поглед кој покрај моменталната количина на залиха го определува и статусот на залихата.

Книгите се класифицираат во три групи:

NEMA_ZALIHA – кога количината е 0;
NISKA_ZALIHA – кога количината е од 1 до 5;
DOVOLNA_ZALIHA – кога количината е поголема од 5.

View

CREATE OR REPLACE VIEW project.v_niska_zaliha AS
SELECT
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha,
    COALESCE(SUM(s.kolicina), 0) AS vkupno_prodadeni,
    CASE
        WHEN k.kolicina_na_zaliha = 0 THEN 'NEMA_ZALIHA'
        WHEN k.kolicina_na_zaliha <= 5 THEN 'NISKA_ZALIHA'
        ELSE 'DOVOLNA_ZALIHA'
    END AS status_zaliha
FROM project.knigi k
LEFT JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
LEFT JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
    AND n.status = 'ZAVRSENA'
GROUP BY
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha;

Тестирање

SELECT *
FROM project.v_niska_zaliha
ORDER BY kolicina_na_zaliha ASC;

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

Овој поглед може да му помогне на администраторот полесно да идентификува книги за кои е потребно дополнување на залихата.

Препорачување книги според расположение

Data requirements description

Една од главните функционалности на BiblioPremium е препорачување книги според моменталното расположение или потреба на корисникот.

Бидејќи книгите се поврзани со расположенија преку релацијата povrzana_so, креирана е функција која за избрано расположение ги враќа соодветните книги кои моментално се достапни на залиха.

Stored function

CREATE OR REPLACE FUNCTION project.preporacaj_knigi_po_raspolozenie(
    p_raspolozenie_id INTEGER
)
RETURNS TABLE(
    kniga_id BIGINT,
    naslov VARCHAR,
    cena NUMERIC,
    kolicina_na_zaliha INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT
            k.kniga_id,
            k.naslov,
            k.cena,
            k.kolicina_na_zaliha
        FROM project.knigi k
        JOIN project.povrzana_so ps
            ON k.kniga_id = ps.kniga_id
        WHERE ps.raspolozenie_id = p_raspolozenie_id
          AND k.kolicina_na_zaliha > 0
        ORDER BY k.naslov;
END;
$$;

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

Дополнително, условот k.kolicina_na_zaliha > 0 спречува да се препорачуваат книги кои моментално не се достапни.

Тестирање

Функцијата беше тестирана за расположението со идентификатор 1:

SELECT *
FROM project.preporacaj_knigi_po_raspolozenie(1);

Како резултат беа добиени книгите:

Atomic Habits
Mindset
Think and Grow Rich

Со ова е потврдено дека функцијата правилно ги користи врските помеѓу книгите и расположенијата и ги враќа само книгите кои имаат достапна залиха.

Заклучок

Во оваа фаза се имплементирани повеќе механизми на ниво на базата на податоци кои се директно поврзани со деловната логика на BiblioPremium.

Тригерот обезбедува автоматска проверка и намалување на залихата при завршување на нарачка и спречува неконзистентна состојба кога нема доволно примероци.

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

Функцијата preporacaj_knigi_po_raspolozenie() ја поддржува една од главните функционалности на системот – препорачување достапни книги според избраното расположение.

Со овие механизми се воведува дополнителна логика директно во базата на податоци над основните ограничувања и референцијалниот интегритет дефинирани во претходните фази.

Advanced Database Development AI Usage

Name of AI service/solution that was used

ChatGPT

URL: https://chatgpt.com/

Type of service/subscription: AI assistant

Final result

AI алатката беше користена како дополнителна помош при разработување на идеи за напредните механизми во базата на податоци и при проверка на SQL/PLpgSQL решенијата.

Финалната имплементација е приспособена на структурата и деловната логика на BiblioPremium и е практично тестирана над проектната база на податоци.

Како дел од фазата беа имплементирани и тестирани:

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

Entire AI usage log

AI беше користен за дискусија на можни решенија, проверка и подобрување на SQL/PLpgSQL кодот и структурата на документацијата за оваа фаза.

Last modified 5 days ago Last modified on 09/25/26 04:49:21

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text