Направени се неколку промени во претходните фази.

Направени промени во P1

Направена е промена во релацијата service_for_pets. Релацијата е променета од 1:N Pet_Cares:Pets во 1:N Pets:Pet_Cares. Промената ја направив бидејќи не е логично за едно животно да може да се побара само една услуга. За едно милениче ќе може да бидат побарувани повеќе услуги но секоја услуга истовремено ќе може да биде побарана од само едно милениче. Уште подобро би било ако се промени во many-to-many но засега останува вака.
Атрибутот quantity e ставен да биде атрибут на релацијата are_made. Бидејќи како што беше дефинирано секој производ во една нарачка ќе добиваше иста количина.

Корегиран дијаграм

Направени промени во P2

Измени во DDL скриптата за креирање на табели

ALTER TABLE pets 
DROP CONSTRAINT IF EXISTS petcares; 

ALTER TABLE pets 
DROP COLUMN IF EXISTS petcares;

== Додадени прашалници во P3
1. За секој ветеринарен центар да се прикажат вкупниот број на посети, вкупниот број на работни позиции за доктори и вкупниот број на посети изразени во проценти.
'''''Резултантна шема: (vet_center_name, total_visits, total_jobs, visit_percentage)''''' 
{{{
WITH VetCenterVisitData AS (
    SELECT 
        vc.id AS vet_center_id,
        vc.name AS vet_center_name,
        COUNT(pc.id) AS total_visits,
        COUNT(DISTINCT j.id) AS total_jobs
    FROM 
        vet_centers vc
    LEFT JOIN 
        pet_cares pc ON vc.id = pc.vetcentersID
    LEFT JOIN 
        jobs j ON vc.id = j.vetCentersID
    GROUP BY 
        vc.id
),
TotalVisitCount AS (
    SELECT 
        SUM(total_visits) AS total_visits_all_centers
    FROM 
        VetCenterVisitData
)
SELECT 
    vcd.vet_center_name,
    vcd.total_visits,
    vcd.total_jobs,
    ROUND((vcd.total_visits::decimal / tvc.total_visits_all_centers) * 100, 2) AS visit_percentage
FROM 
    VetCenterVisitData vcd,
    TotalVisitCount tvc
ORDER BY 
    visit_percentage DESC;
}}}


2. За секој корисник покрај неговото име и email, да се испечати email_Provider, вкупниот број на миленици кои што ги поседува корисникот, колку од нив се мачиња а колку кучиња и дали корисникот има посетено ветеринарен центар во Skopje.  
'''''Резултантна шема: (name, email, email_provider, total_pets, total_cats, total dogs, visited_vet_center_in_skopje)''''' 
{{{
SELECT 
    u.name,
    u.email,
    CASE 
        WHEN u.email LIKE '%@yahoo.com' THEN 'yahoo'
        WHEN u.email LIKE '%@gmail.com' THEN 'gmail'
        WHEN u.email LIKE '%@hotmail.com' THEN 'hotmail'
        ELSE 'other'
    END AS email_provider,
    COUNT(DISTINCT p.ID) AS total_pets,  -- Вкупно животни, избегнувајќи дуплирања
    SUM(CASE WHEN tp.kind_of_pet = 'Cat' THEN 1 ELSE 0 END) AS total_cats,  -- Вкупно мачки
    SUM(CASE WHEN tp.kind_of_pet = 'Dog' THEN 1 ELSE 0 END) AS total_dogs,  -- Вкупно кучиња
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM pets_visit_vet_centers pv 
            JOIN vet_centers vc ON pv.id_vet_centers = vc.ID
            JOIN cities c ON vc.citiesid = c.ID  -- Поврзување со табелата cities
            WHERE pv.id_pets IN (SELECT ID FROM pets WHERE usersID = u.ID)
            AND c.name = 'Skopje'  -- Проверка за Скопје
        ) THEN TRUE 
        ELSE FALSE 
    END AS visited_vet_center_in_skopje
FROM 
    users u
LEFT JOIN 
    pets p ON u.ID = p.usersID
LEFT JOIN 
    type_of_pets tp ON p.typeofpetsid = tp.ID  -- Поврзување со табелата type_of_pets
GROUP BY 
    u.ID, u.email;
}}}

ALTER TABLE pet_cares 
ADD COLUMN pet_id BIGINT;

ALTER TABLE pet_cares 
ADD CONSTRAINT fk_pet_id FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE CASCADE; 

ALTER TABLE orders
DROP COLUMN quantity;
 

ALTER TABLE product_are_made_orders
ADD COLUMN quantity int;

Измени во DDL скриптата за полнење на податоци

DELETE FROM project.product_are_made_orders;

INSERT INTO project.product_are_made_orders
(id_products, id_orders, quantity)
VALUES(1, 4, 1),
(2, 5, 1),
(3, 4, 1);

Измени во релациски дијаграм изведен од DBeaver
Релациски дијаграм

Обезбедување пристап

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

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

1. Kорисник кој што не е најавен.

• Koрисник кој што не е најавен (нема креирано профил) нема пристап до Pets, Pet Cares, Manage Customers, Blog Post.
• Во Products има можност да ги гледа достапните производи и да пребарува по име на проивод. Останатите функционалности како додавање/бришење и едитирање се оневозможени за овој тип на корисник.

2. Корисник менаџер.

• Во Manage Cities има можност да додава нови градови.
• Во полето Vet_Centers има можност да додава (креира) ветеринарни центри. Исто така може да ги брише и едитира. При креирање/едитирање на ветеринарни центри во полето city има можност да го избере градот од листа која што автоматски се променува со секоја промена во Manage Cities.
• Во полето Маnage Customers пристапува до профилите на сите корисници кои што се регистрирани на платформата.

3. Стандарден корисник.

• Стандарден корисник нема пристап до полето Manage Cities.
• Во полето Маnage Customers пристапува само до неговиот профил.
• Во полето Vet_Centers нема можност да додава, брише и изменува ветеринарни центри.
• Во полето Pets си ги гледа само сопствените миленици. Има можност да додава/брише/едитира милениици.
• Во полето Pet Cares ги гледа само сопствените побарани услуги. Има можност да додава/брише/едитира услуги. Кога сака да креира нова услуга во полето pet_name има можност да избере едно од своите миленичиња(му се прикажуваат само миленичињата кои што претходно ги има додадено во Pets).
• Во полето Blog Post ги гледа сите побарани совети. Можност за бришење/едитирање има само на сопствените побарани совети. Доколку кликне на Answers му се отвараат сите одговори за одредено прашање(совет). Можност за бришење/едитирање има само на сопствените дадени одговори. Во Details може да ги виде сите одговори на даден одговор. Со Create an Answer има можност да дава одговор на дадено прашање како и да дава одговор на даден одговор.
• Во Products има можност да ги гледа достапните производи и да пребарува по име на проивод. Останатите функционалности како додавање/бришење и едитирање се оневозможени за овој тип на корисник.

4. Корисник - Доктор

• Во полето Pets ги гледа сите миленици. Има можност да едитира/брише или додава нови миленици.
• Во полето Products нема можност да создава нови продукти.
• Во полето Vet Center нема можност да додава нови вет.центри, нема можност да ги едитира или брише постоечките вет.центри.
• Во полето Pet Cares ги гледа сите закажени услуги. Има можност да ги едитира или брише постоечките услуги.

Интегритет

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

ALTER TABLE reports
DROP CONSTRAINT fk_pets;

ALTER TABLE reports
ADD CONSTRAINT fk_pets FOREIGN KEY (petsID) REFERENCES pets(ID) ON DELETE CASCADE;

ALTER TABLE pet_cares
DROP CONSTRAINT fk_vetCenters;

ALTER TABLE pet_cares
ADD CONSTRAINT fk_vetCenters
FOREIGN KEY (vetcentersID) REFERENCES vet_centers(ID)
ON DELETE CASCADE;

ALTER TABLE pet_galery
DROP CONSTRAINT fk_pets;

ALTER TABLE pet_galery
ADD CONSTRAINT fk_pets
FOREIGN KEY (petsID) REFERENCES pets(ID)
ON DELETE CASCADE;

Тригери

Со употреба на тригер го форматирам мобилниот телефоснки број. Најпрво ја променив големината на полето бидејќи првично имавме назначено phoneNumber varchar(15), што е мала голема за телефонски број. При внес на телефоснки број во полето Vet Centers телефонскиот број автоматски се формитира со тоа што: доколку не започнува со +389 му се додава, доколку започнува со 0 се брише првата нула, и се бришат сите знаци освен знакот +.

ALTER TABLE vet_centers
ALTER COLUMN phoneNumber TYPE varchar(50);

CREATE OR REPLACE FUNCTION format_phone_number()
RETURNS TRIGGER AS $$
BEGIN
    -- Отстранување на сите не-нумерички знаци освен плусот на почетокот, ако постои
    IF LEFT(NEW.phoneNumber, 1) = '+' THEN
        NEW.phoneNumber := '+' || regexp_replace(SUBSTRING(NEW.phoneNumber FROM 2), '\D', '', 'g');
    ELSE
        NEW.phoneNumber := regexp_replace(NEW.phoneNumber, '\D', '', 'g');
    END IF;

    -- Ако бројот веќе започнува со '+389', не правиме промени
    IF LEFT(NEW.phoneNumber, 4) = '+389' THEN
        RETURN NEW;
    END IF;

    -- Ако бројот започнува со '389' и нема '+', додај само '+'
    IF LEFT(NEW.phoneNumber, 3) = '389' THEN
        NEW.phoneNumber := '+389' || SUBSTRING(NEW.phoneNumber FROM 4);
    ELSE
        -- Ако бројот не започнува со '389', додадете целосен префикс '+389'
        NEW.phoneNumber := '+389' || NEW.phoneNumber;
    END IF;

    -- Осигурување дека нема водечка '0' после '+389'
    IF LEFT(NEW.phoneNumber, 4) = '+389' AND SUBSTRING(NEW.phoneNumber FROM 5 FOR 1) = '0' THEN
        NEW.phoneNumber := '+389' || SUBSTRING(NEW.phoneNumber FROM 6);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



CREATE TRIGGER trigger_format_phone_number
BEFORE INSERT OR UPDATE ON vet_centers
FOR EACH ROW
EXECUTE FUNCTION format_phone_number();

Тригер кој што го искористив за да го пополнам полето date_askes во Blog Post Consultation со моменталниот датум.

CREATE OR REPLACE FUNCTION set_default_date_askes()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_askes IS NULL THEN
        NEW.date_askes := CURRENT_DATE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_blogpost
BEFORE INSERT ON blog_post_for_consultations
FOR EACH ROW
EXECUTE FUNCTION set_default_date_askes();

CREATE TRIGGER before_update_blogpost
BEFORE UPDATE ON blog_post_for_consultations
FOR EACH ROW
EXECUTE FUNCTION set_default_date_askes();

При внес на date_added во Products автоматски се пополнува денешниот датум.

CREATE OR REPLACE FUNCTION set_date_added()
RETURNS TRIGGER AS $$
BEGIN
    -- Поставување на dateAdded на тековниот датум
    NEW.dateAdded := CURRENT_DATE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_set_date_added
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION set_date_added();

При внес на name во полето City секоја прва буква од секој збор се претвара во голема буква.

CREATE OR REPLACE FUNCTION format_city_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Конвертирај го името на градот со првата буква на секој збор голема
    NEW.name := initcap(lower(NEW.name));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_format_city_name
BEFORE INSERT OR UPDATE ON cities
FOR EACH ROW
EXECUTE FUNCTION format_city_name();

При внес на pets_name во полето Pets секоја прва буква се претвара во голема буква.

CREATE OR REPLACE FUNCTION format_pets_name()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name := initcap(lower(NEW.name));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_format_pets_name
BEFORE INSERT OR UPDATE ON pets
FOR EACH ROW
EXECUTE FUNCTION format_pets_name();

При внес на dateending во Pet Cares се проверува дали е после startdate. Доколку не е полето dateending се пополнува со иста вредност како startdate.

CREATE OR REPLACE FUNCTION set_start_date()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.start_date IS NULL OR NEW.start_date <> NEW.dateending THEN
        NEW.start_date := NEW.dateending;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER set_start_date_trigger
BEFORE INSERT OR UPDATE ON pet_cares
FOR EACH ROW
EXECUTE FUNCTION set_start_date();

Погледи

Views имам искористено во Manage Customers и Blog Post.