Апликативен дизајн - Случаи на употреба и Сценарија за пристап до базата - SQL Погледи

Погледи

Поглед за наоѓање на најдобри клиенти

CREATE OR REPLACE VIEW public.najdobri_klienti
AS SELECT k.id_klient,
    k.korisnicko_ime,
    k.prezime,
    sum(n.vkupna_cena) AS vkupna_cena
   FROM klienti k
     JOIN naracka n ON k.id_klient = n.id_klient
  WHERE n.vkupna_cena IS NOT NULL
  GROUP BY k.id_klient, k.korisnicko_ime, k.prezime, n.vkupna_cena, n.datum_isporacana
 HAVING n.datum_isporacana < now()
  ORDER BY (sum(n.vkupna_cena)) DESC
 LIMIT 5;

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

Поглед за наоѓање на најдобри работници

-- public.najdobri_vraboteni_po_naracka source

CREATE OR REPLACE VIEW najdobri_vraboteni_po_naracka
AS SELECT n.id_naracka,
    n.naslov,
    v.ime,
    v.prezime,
    max(enid.br_na_zavrseni_delovi) AS max
   FROM naracka n
     JOIN evidencija_na_naracki enn ON n.id_naracka = enn.id_naracka
     JOIN evidencija_naracki_izraboteni_delovi enid ON enn.id_evidencija = enid.id_evidencija
     JOIN delovi d ON enid.id_del = d.id_del
     JOIN delovi_izraboteni_od_mashina diom ON d.id_del = diom.id_del
     JOIN mashini m ON diom.id_mashina = m.id_mashina
     JOIN vraboteni_vo_proizvodstvo_raboti_na_mashina vprrm ON m.id_mashina = vprrm.id_mashina
     JOIN vraboteni_vo_proizvodstvo vvp ON vprrm.id_vraboten = vvp.id_vraboten
     JOIN vraboteni v ON vvp.id_vraboten = v.id_vraboten
  GROUP BY n.id_naracka, n.naslov, v.ime, v.prezime;

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

Тригери и функции

Тригер за пресметување на цена на нарачка кога ќе се додаде нов продукт

create trigger trig_insert after
insert
    on
   naracka_se_sostoi_od_mebel for each row execute procedure naracka_vkupna_cena();

Процедура за соодветниот тригер

CREATE OR REPLACE FUNCTION naracka_vkupna_cena()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
        DECLARE
                total double precision;
                initial double precision;
        BEGIN
        select
        case when 
        n.vkupna_cena is null then 0 
        else n.vkupna_cena end into initial 
    from naracka as n 
    where n.id_naracka=new.id_naracka;
        select m.cena into total from mebel as m 
        where m.id_mebel = new.id_mebel;
        update naracka set vkupna_cena = initial + new.kolicina*total
        where naracka .id_naracka = new.id_naracka;
        return new;
        end;
$function$
;

Тригер за пресметување на цена на нарачка кога ќе се одземе продукт од нарачката

create trigger trig_delete after
delete
    on
   naracka_se_sostoi_od_mebel for each row execute procedure delete_vkupna_cena();

Процедура за соодветниот тригер

CREATE OR REPLACE FUNCTION public.delete_vkupna_cena()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
        DECLARE
                total double precision;
                initial double precision;
        BEGIN
        select
        case when 
        n.vkupna_cena is null then 0 
        else n.vkupna_cena end into initial 
    from naracka as n 
    where n.id_naracka=old.id_naracka;
        select m.cena into total from mebel as m 
        where m.id_mebel = old.id_mebel;
        update naracka set vkupna_cena = initial - old.kolicina*total
        where naracka .id_naracka = old.id_naracka;
        return new;
        end;
$function$
;

Тригер за пресметување на цена на нарачка кога ќе се додадат дополнителни делови за нарачка

create trigger trig_insert after
insert
    on
    dopolniteli_delovi_za_naracka for each row execute procedure naracka_vkupna_cena_delovi();

Функција која се повикува после активирање на тригерот

CREATE OR REPLACE FUNCTION public.naracka_vkupna_cena_delovi()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
        DECLARE
                total double precision;
                initial double precision;
                quantity integer;
        BEGIN
        select
        case when 
        n.vkupna_cena is null then 0 
        else n.vkupna_cena end into initial 
    from naracka as n 
    where n.id_naracka=new.id_naracka;
        select d.cena into total from delovi as d 
        where d.id_del = new.id_del;
        update naracka set vkupna_cena = initial + new.kolicina*total
        where naracka .id_naracka = new.id_naracka;
        return new;
        end;
$function$
;