Advanced Database Development

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

Имплементацијата вклучува тригер функција, тригер и поглед (VIEW).

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

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

Тригерот се активира пред промена на атрибутот status во релацијата naracki.

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

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

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

За тестирање беше употребена нарачка која се наоѓаше во статус 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 и овозможува полесно добивање информации за продажбата.

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

Тестирање на погледот

Погледот беше тестиран со:

SELECT *
FROM project.v_prodazba_po_kniga
ORDER BY vkupen_prihod DESC;

Тестирањето успешно ги прикажа книгите подредени според остварениот приход.

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

Заклучок

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

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

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