Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

09/19/26 02:00:05 (12 days ago)

Author:

183164

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Напредно програмирање на базата на податоци =
+
+Сите објекти се во скриптата [attachment:advanced_db.sql]. Редослед на извршување: schema_creation.sql, advanced_db.sql, data_load.sql. Скриптата може да се извршува повеќепати. Скриптата data_load.sql привремено ги исклучува тригерите, бидејќи историските податоци се внесуваат директно во нивната конечна состојба, а по внесот повторно ги вклучува.
+
+Сите правила се тестирани: секое недозволено дејство базата го одбива со порака на македонски јазик, а прототипот од фазата P4 работи без измени со вклучени тригери.
+
+== Животен циклус на пријавата и конзистентност на статусот ==
+
+Пријавата мора да минува низ точно определени статуси: поднесена → примена → во тек → решена, а од секој отворен статус може да биде одбиена. Решена или одбиена пријава е затворена и статусот не може повеќе да се менува. Првиот запис во историјата секогаш е „поднесена“ и го креира системот. Секоја следна промена ја прави работник, и не може да биде со време пред претходната промена.
+
+Статусот се чува на две места: во reports (моменталниот статус, за брзо филтрирање) и во status_logs (целата историја). Во фазата P5 беше утврдено дека ова е намерна редундантност. Затоа базата мора да гарантира дека моменталниот статус секогаш е еднаков на последниот запис во историјата, без оглед на тоа дали апликацијата прво го менува статусот или прво внесува запис во историјата.
+
+=== Имплементација ===
+
+'''Домен:'''
+{{{#!sql
+CREATE DOMAIN report_status AS VARCHAR(20)
+    CHECK (VALUE IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected'));
+}}}
+
+'''Функција за дозволените премини:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION status_transition_allowed(p_from TEXT, p_to TEXT)
+RETURNS BOOLEAN LANGUAGE sql IMMUTABLE AS $$
+    SELECT (p_from, p_to) IN (('submitted',   'received'),
+                              ('submitted',   'rejected'),
+                              ('received',    'in_progress'),
+                              ('received',    'rejected'),
+                              ('in_progress', 'resolved'),
+                              ('in_progress', 'rejected'));
+$$;
+}}}
+
+'''Тригери:'''
+ * reports_before_insert - новата пријава мора да има статус submitted.
+ * reports_before_status_update - директна промена на статусот во reports мора да е дозволен премин.
+ * status_logs_before_insert - првиот запис мора да биде submitted без работник; секој следен мора да е дозволен премин, да не е постар од претходниот и да го направи доделен работник.
+ * status_logs_after_insert - по секој нов запис во историјата, статусот во reports автоматски се усогласува.
+ * reports_status_consistency - одложен (DEFERRABLE INITIALLY DEFERRED) тригер-ограничување кој на крајот на трансакцијата проверува дали статусот во reports е еднаков на последниот запис во историјата. Бидејќи е одложен, апликацијата може во иста трансакција прво да го смени статусот, а потоа да внесе запис, или обратно.
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION trg_status_logs_before_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_last status_logs%ROWTYPE;
+BEGIN
+    SELECT * INTO v_last
+    FROM status_logs
+    WHERE report_id = NEW.report_id
+    ORDER BY changed_at DESC, log_id DESC
+    LIMIT 1;
+
+    IF NOT FOUND THEN
+        IF NEW.status <> 'submitted' OR NEW.worker_id IS NOT NULL THEN
+            RAISE EXCEPTION 'Првиот запис за пријавата % мора да биде submitted, без работник',
+                            NEW.report_id;
+        END IF;
+        RETURN NEW;
+    END IF;
+
+    IF NOT status_transition_allowed(v_last.status, NEW.status) THEN
+        RAISE EXCEPTION 'Недозволена промена на статусот на пријавата % од % во %',
+                        NEW.report_id, v_last.status, NEW.status;
+    END IF;
+    IF NEW.changed_at < v_last.changed_at THEN
+        RAISE EXCEPTION 'Промената не може да биде постара од претходната (%)', v_last.changed_at;
+    END IF;
+    IF NEW.worker_id IS NULL THEN
+        RAISE EXCEPTION 'Промената на статусот мора да ја направи работник';
+    END IF;
+    IF NOT EXISTS (SELECT 1 FROM assignments a
+                   WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
+        RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
+    END IF;
+    RETURN NEW;
+END $$;
+
+CREATE TRIGGER status_logs_before_insert
+    BEFORE INSERT ON status_logs
+    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_before_insert();
+
+CREATE OR REPLACE FUNCTION trg_status_logs_after_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+BEGIN
+    UPDATE reports
+    SET status = NEW.status
+    WHERE report_id = NEW.report_id
+      AND status IS DISTINCT FROM NEW.status;
+    RETURN NULL;
+END $$;
+
+CREATE TRIGGER status_logs_after_insert
+    AFTER INSERT ON status_logs
+    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_after_insert();
+
+CREATE OR REPLACE FUNCTION trg_reports_status_consistency()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_current TEXT;
+    v_logged  TEXT;
+BEGIN
+    SELECT status INTO v_current FROM reports WHERE report_id = NEW.report_id;
+    IF NOT FOUND THEN
+        RETURN NULL;
+    END IF;
+    SELECT status INTO v_logged
+    FROM status_logs
+    WHERE report_id = NEW.report_id
+    ORDER BY changed_at DESC, log_id DESC
+    LIMIT 1;
+    IF v_logged IS DISTINCT FROM v_current THEN
+        RAISE EXCEPTION 'Статусот на пријавата % (%) не одговара на историјата (%)',
+                        NEW.report_id, v_current, COALESCE(v_logged, 'нема запис');
+    END IF;
+    RETURN NULL;
+END $$;
+
+CREATE CONSTRAINT TRIGGER reports_status_consistency
+    AFTER INSERT OR UPDATE OF status ON reports
+    DEFERRABLE INITIALLY DEFERRED
+    FOR EACH ROW EXECUTE FUNCTION trg_reports_status_consistency();
+}}}
+
+'''Складирана процедура:''' промената на статусот од апликацијата се сведува на еден повик, а тригерите ги проверуваат правилата и го усогласуваат статусот:
+{{{#!sql
+CREATE OR REPLACE PROCEDURE change_report_status(p_report_id INTEGER,
+                                                 p_worker_id INTEGER,
+                                                 p_new_status TEXT,
+                                                 p_note TEXT DEFAULT NULL)
+LANGUAGE plpgsql AS $$
+BEGIN
+    INSERT INTO status_logs (status, note, report_id, worker_id)
+    VALUES (p_new_status, p_note, p_report_id, p_worker_id);
+END $$;
+
+CALL change_report_status(4, 4, 'resolved', 'Дефектот е отстранет.');
+}}}
+
+'''Пример:''' CALL change_report_status(10, 4, 'resolved', 'test'); за пријава со статус submitted базата ја одбива со порака „Недозволена промена на статусот на пријавата 10 од submitted во resolved“. Промена само на reports.status без запис во историјата се одбива при потврдување на трансакцијата.
+
+== Непроменлива историја на статусите ==
+
+Историјата на статусите е доказ за транспарентноста и одговорноста на општината кон граѓаните. Затоа веќе внесените записи не смеат да се менуваат ниту бришат, ниту од апликацијата ниту директно во базата. Единствен исклучок е бришење на целата пријава, кога нејзината историја се брише каскадно.
+
+=== Имплементација ===
+
+'''Тригер:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION trg_status_logs_immutable()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+BEGIN
+    IF TG_OP = 'DELETE'
+       AND NOT EXISTS (SELECT 1 FROM reports WHERE report_id = OLD.report_id) THEN
+        RETURN OLD;    -- каскадно бришење: пријавата веќе е избришана
+    END IF;
+    RAISE EXCEPTION 'Историјата на статусите не може да се менува ни брише';
+END $$;
+
+CREATE TRIGGER status_logs_immutable
+    BEFORE UPDATE OR DELETE ON status_logs
+    FOR EACH ROW EXECUTE FUNCTION trg_status_logs_immutable();
+}}}
+
+== Овластувања за дејствијата на работниците и администраторите ==
+
+Работник може да го менува статусот и да пишува коментари само за пријави на кои е доделен. Затворена пријава (решена или одбиена) не прима нови коментари и не може да се доделува. Доделувањето не може да има време пред поднесувањето на пријавата. Овие правила зависат од податоци во повеќе табели, па не можат да се изразат со обични CHECK ограничувања или надворешни клучеви.
+
+=== Имплементација ===
+
+'''Тригери:''' проверката за доделен работник при промена на статусот е во status_logs_before_insert (погоре). Дополнително:
+{{{#!sql
+CREATE OR REPLACE FUNCTION trg_assignments_before_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_report reports%ROWTYPE;
+BEGIN
+    SELECT * INTO v_report FROM reports WHERE report_id = NEW.report_id;
+    IF v_report.status IN ('resolved', 'rejected') THEN
+        RAISE EXCEPTION 'Пријавата % е затворена (%) и не може да се доделува',
+                        NEW.report_id, v_report.status;
+    END IF;
+    IF NEW.assigned_at < v_report.created_at THEN
+        RAISE EXCEPTION 'Доделувањето не може да биде пред поднесувањето на пријавата';
+    END IF;
+    RETURN NEW;
+END $$;
+
+CREATE TRIGGER assignments_before_insert
+    BEFORE INSERT ON assignments
+    FOR EACH ROW EXECUTE FUNCTION trg_assignments_before_insert();
+
+CREATE OR REPLACE FUNCTION trg_comments_before_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_status TEXT;
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM assignments a
+                   WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
+        RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
+    END IF;
+    SELECT status INTO v_status FROM reports WHERE report_id = NEW.report_id;
+    IF v_status IN ('resolved', 'rejected') THEN
+        RAISE EXCEPTION 'Пријавата % е затворена и не прима нови коментари', NEW.report_id;
+    END IF;
+    RETURN NEW;
+END $$;
+
+CREATE TRIGGER comments_before_insert
+    BEFORE INSERT ON comments
+    FOR EACH ROW EXECUTE FUNCTION trg_comments_before_insert();
+}}}
+
+== Откривање на слични пријави и автоматски приоритет ==
+
+Кога повеќе граѓани независно пријавуваат ист проблем на иста локација, тоа е знак дека проблемот е сериозен. Ако при поднесување на нова пријава во радиус од 150 метри веќе постојат барем 2 активни пријави од истата категорија, приоритетот на новата пријава автоматски се крева на најмалку „висок“. Истата функција за пребарување ја користи и апликацијата, за да му ги прикаже на граѓанинот сличните пријави пред поднесување. Растојанието се пресметува точно во метри, со формулата на Haversine. Поднесувањето на пријава со фотографии и првиот запис во историјата е изведено во една складирана функција.
+
+=== Имплементација ===
+
+'''Функции:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION distance_m(lat1 NUMERIC, lon1 NUMERIC, lat2 NUMERIC, lon2 NUMERIC)
+RETURNS DOUBLE PRECISION LANGUAGE sql IMMUTABLE AS $$
+    SELECT 2 * 6371000 * asin(sqrt(
+               power(sin(radians(lat2 - lat1) / 2), 2)
+             + cos(radians(lat1)) * cos(radians(lat2))
+             * power(sin(radians(lon2 - lon1) / 2), 2)));
+$$;
+
+CREATE OR REPLACE FUNCTION find_similar_reports(p_category_id INTEGER,
+                                                p_latitude NUMERIC,
+                                                p_longitude NUMERIC,
+                                                p_radius_m DOUBLE PRECISION DEFAULT 150,
+                                                p_exclude_report_id INTEGER DEFAULT NULL)
+RETURNS TABLE (report_id INTEGER, description TEXT, status TEXT,
+               created_at TIMESTAMP, distance_m DOUBLE PRECISION)
+LANGUAGE sql STABLE AS $$
+    SELECT r.report_id, r.description, r.status::TEXT, r.created_at,
+           ROUND(distance_m(p_latitude, p_longitude, r.latitude, r.longitude)::NUMERIC, 1)
+    FROM project.reports r
+    WHERE r.category_id = p_category_id
+      AND r.status NOT IN ('resolved', 'rejected')
+      AND r.latitude IS NOT NULL
+      AND r.report_id IS DISTINCT FROM p_exclude_report_id
+      AND distance_m(p_latitude, p_longitude, r.latitude, r.longitude) <= p_radius_m
+    ORDER BY 5;
+$$;
+
+CREATE OR REPLACE FUNCTION priority_rank(p TEXT)
+RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
+    SELECT CASE p WHEN 'low' THEN 1 WHEN 'medium' THEN 2
+                  WHEN 'high' THEN 3 WHEN 'urgent' THEN 4 END;
+$$;
+}}}
+
+'''Тригер:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION trg_reports_before_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_similar INTEGER;
+BEGIN
+    IF NEW.status IS DISTINCT FROM 'submitted' THEN
+        RAISE EXCEPTION 'Новата пријава мора да има статус submitted (добиено: %)', NEW.status;
+    END IF;
+    IF NEW.latitude IS NOT NULL THEN
+        SELECT COUNT(*) INTO v_similar
+        FROM find_similar_reports(NEW.category_id, NEW.latitude, NEW.longitude, 150);
+        IF v_similar >= 2 AND priority_rank(NEW.priority) < priority_rank('high') THEN
+            NEW.priority := 'high';
+        END IF;
+    END IF;
+    RETURN NEW;
+END $$;
+
+CREATE TRIGGER reports_before_insert
+    BEFORE INSERT ON reports
+    FOR EACH ROW EXECUTE FUNCTION trg_reports_before_insert();
+}}}
+
+'''Складирана функција за поднесување:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION submit_report(p_citizen_id INTEGER,
+                                         p_category_id INTEGER,
+                                         p_description TEXT,
+                                         p_location_text TEXT,
+                                         p_latitude NUMERIC,
+                                         p_longitude NUMERIC,
+                                         p_photos TEXT[] DEFAULT '{}')
+RETURNS INTEGER LANGUAGE plpgsql AS $$
+DECLARE
+    v_report_id  INTEGER;
+    v_created_at TIMESTAMP;
+BEGIN
+    INSERT INTO reports (description, location_text, latitude, longitude, category_id, citizen_id)
+    VALUES (p_description, p_location_text, p_latitude, p_longitude, p_category_id, p_citizen_id)
+    RETURNING report_id, created_at INTO v_report_id, v_created_at;
+
+    INSERT INTO status_logs (status, changed_at, report_id, worker_id)
+    VALUES ('submitted', v_created_at, v_report_id, NULL);
+
+    INSERT INTO photos (image_url, report_id)
+    SELECT '/uploads/reports/' || v_report_id || '/' || f, v_report_id
+    FROM unnest(p_photos) AS f;
+
+    RETURN v_report_id;
+END $$;
+}}}
+
+'''Пример:''' при тестирањето, три пријави за дупка на коловоз во радиус од 25 метри на ул. Македонија се поднесени со submit_report. Првите две добија приоритет „низок“, а третата автоматски доби „висок“, бидејќи во близина веќе имаше две активни пријави.
+
+== Рокови за реакција и позадинска ескалација на приоритетот ==
+
+Секој приоритет има рок за реакција (SLA): итен 1 ден, висок 3 дена, среден 7 дена, низок 14 дена. Пријава која не е затворена и чиј статус не се променил подолго од рокот се смета за задоцнета. Покрај тоа, пријавите кои долго чекаат не смеат да останат со низок приоритет: секој ден автоматски се подига приоритетот според деновите од последната промена на статусот (3 или повеќе дена - најмалку среден, 7 или повеќе - најмалку висок, 14 или повеќе - итен). Приоритетот никогаш не се намалува автоматски, па повторното извршување не менува ништо.
+
+=== Имплементација ===
+
+'''Функции:'''
+{{{#!sql
+CREATE OR REPLACE FUNCTION sla_days(p TEXT)
+RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
+    SELECT CASE p WHEN 'urgent' THEN 1 WHEN 'high' THEN 3
+                  WHEN 'medium' THEN 7 ELSE 14 END;
+$$;
+
+CREATE OR REPLACE FUNCTION priority_from_rank(r INTEGER)
+RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$
+    SELECT (ARRAY['low', 'medium', 'high', 'urgent'])[GREATEST(1, LEAST(4, r))];
+$$;
+
+CREATE OR REPLACE FUNCTION escalate_overdue_reports()
+RETURNS TABLE (report_id INTEGER, old_priority TEXT, new_priority TEXT, days_waiting INTEGER)
+LANGUAGE plpgsql AS $$
+BEGIN
+    RETURN QUERY
+    WITH waiting AS (
+        SELECT r.report_id, r.priority::TEXT AS priority,
+               EXTRACT(DAY FROM now()::TIMESTAMP - MAX(l.changed_at))::INTEGER AS days_waiting
+        FROM project.reports r
+        JOIN project.status_logs l ON l.report_id = r.report_id
+        WHERE r.status NOT IN ('resolved', 'rejected')
+        GROUP BY r.report_id
+    ),
+    target AS (
+        SELECT w.report_id, w.priority, w.days_waiting,
+               priority_from_rank(GREATEST(
+                   priority_rank(w.priority),
+                   CASE WHEN w.days_waiting >= 14 THEN 4
+                        WHEN w.days_waiting >= 7  THEN 3
+                        WHEN w.days_waiting >= 3  THEN 2
+                        ELSE 1 END)) AS new_priority
+        FROM waiting w
+    ),
+    updated AS (
+        UPDATE project.reports r
+        SET priority = t.new_priority
+        FROM target t
+        WHERE r.report_id = t.report_id
+          AND t.new_priority <> t.priority
+        RETURNING r.report_id, t.priority, t.new_priority, t.days_waiting
+    )
+    SELECT * FROM updated ORDER BY 1;
+END $$;
+}}}
+
+'''Позадинска задача:''' секој ден во 06:00. Скриптата ја закажува задачата со екстензијата pg_cron, ако е инсталирана на серверот. Ако не е, задачата се закажува однадвор (на пример со cron на серверот) со командата psql -c "SELECT * FROM project.escalate_overdue_reports();".
+{{{#!sql
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
+        PERFORM cron.schedule('cityfix-escalate-overdue', '0 6 * * *',
+                              'SELECT * FROM project.escalate_overdue_reports()');
+    ELSE
+        RAISE NOTICE 'pg_cron не е достапен: escalate_overdue_reports() закажете ја однадвор.';
+    END IF;
+END $$;
+}}}
+
+'''Поглед за администраторите''' со доделените работници, деновите од последната промена и ознака за задоцнетост:
+{{{#!sql
+CREATE VIEW v_report_overview AS
+WITH last_change AS (
+    SELECT report_id, MAX(changed_at) AS last_changed_at
+    FROM status_logs
+    GROUP BY report_id
+)
+SELECT r.report_id, c.name AS category, r.description, r.status, r.priority,
+       ci.full_name AS citizen, r.created_at, lc.last_changed_at,
+       (SELECT string_agg(w.full_name, ', ' ORDER BY w.full_name)
+        FROM assignments a JOIN workers w ON w.worker_id = a.worker_id
+        WHERE a.report_id = r.report_id) AS assigned_workers,
+       EXTRACT(DAY FROM now()::TIMESTAMP - lc.last_changed_at)::INTEGER AS days_since_change,
+       sla_days(r.priority) AS sla_days,
+       (r.status NOT IN ('resolved', 'rejected')
+        AND now()::TIMESTAMP - lc.last_changed_at > sla_days(r.priority) * INTERVAL '1 day') AS is_overdue
+FROM reports r
+JOIN categories c   ON c.category_id = r.category_id
+JOIN citizens ci    ON ci.citizen_id = r.citizen_id
+JOIN last_change lc ON lc.report_id = r.report_id;
+}}}
+
+'''Пример:''' при тестирањето функцијата го подигна приоритетот на три пријави (пријава која чека 14 дена од низок на итен, пријава која чека 8 дена од среден на висок, и пријава која чека 3 дена од низок на среден). Второто извршување веднаш потоа не промени ништо.
+
+== Приватност на граѓаните и прегледи за работата ==
+
+Граѓаните ја гледаат интерактивната мапа со активните пријави, но не смеат да ги гледаат личните податоци на другите граѓани (име, е-пошта, телефон). Затоа мапата не чита директно од табелите, туку од поглед кој ги содржи само јавните податоци: активните пријави и пријавите решени во последните 30 дена. За распределба на работата, администраторите имаат поглед со оптовареноста и учинокот на секој работник.
+
+=== Имплементација ===
+
+'''Погледи:'''
+{{{#!sql
+CREATE VIEW v_public_reports AS
+SELECT r.report_id, c.name AS category, r.description, r.location_text,
+       r.latitude, r.longitude, r.status, r.priority, r.created_at,
+       (SELECT COUNT(*) FROM photos p WHERE p.report_id = r.report_id) AS photo_count
+FROM reports r
+JOIN categories c ON c.category_id = r.category_id
+WHERE r.status NOT IN ('resolved', 'rejected')
+   OR r.report_id IN (SELECT l.report_id FROM status_logs l
+                      WHERE l.status = 'resolved'
+                        AND l.changed_at >= now() - INTERVAL '30 days');
+
+CREATE VIEW v_worker_workload AS
+WITH resolved AS (
+    SELECT l.worker_id, l.report_id, l.changed_at AS resolved_at, r.created_at
+    FROM status_logs l
+    JOIN reports r ON r.report_id = l.report_id
+    WHERE l.status = 'resolved'
+)
+SELECT w.worker_id, w.full_name,
+       COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected'))
+           AS active_reports,
+       COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected')
+                                            AND r.priority IN ('high', 'urgent'))
+           AS active_high_priority,
+       (SELECT COUNT(*) FROM resolved rs
+        WHERE rs.worker_id = w.worker_id
+          AND rs.resolved_at >= now() - INTERVAL '90 days') AS resolved_last_90_days,
+       (SELECT ROUND(AVG(EXTRACT(EPOCH FROM rs.resolved_at - rs.created_at) / 86400)::NUMERIC, 1)
+        FROM resolved rs WHERE rs.worker_id = w.worker_id) AS avg_resolution_days
+FROM workers w
+LEFT JOIN assignments a ON a.worker_id = w.worker_id
+LEFT JOIN reports r     ON r.report_id = a.report_id
+GROUP BY w.worker_id, w.full_name;
+}}}
+
+== Сопствени домени за валидација ==
+
+Е-поштата, телефонот, статусот и приоритетот се користат во повеќе табели. Наместо истото CHECK ограничување да се повторува во секоја табела (како во фазата P2), правилата се дефинирани еднаш, како домени, и сите колони ги користат. Промена на правилото се прави на едно место.
+
+=== Имплементација ===
+
+'''Домени:'''
+{{{#!sql
+CREATE DOMAIN email_address AS VARCHAR(150)
+    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
+
+CREATE DOMAIN phone_number AS VARCHAR(20)
+    CHECK (VALUE ~ '^\+?[0-9]{6,19}$');
+
+CREATE DOMAIN priority_level AS VARCHAR(10)
+    CHECK (VALUE IN ('low', 'medium', 'high', 'urgent'));
+
+ALTER TABLE admins      ALTER COLUMN email    TYPE email_address;
+ALTER TABLE workers     ALTER COLUMN email    TYPE email_address;
+ALTER TABLE citizens    ALTER COLUMN email    TYPE email_address;
+ALTER TABLE citizens    ALTER COLUMN phone    TYPE phone_number;
+ALTER TABLE reports     ALTER COLUMN status   TYPE report_status;
+ALTER TABLE reports     ALTER COLUMN priority TYPE priority_level;
+ALTER TABLE status_logs ALTER COLUMN status   TYPE report_status;
+}}}
+
+Во скриптата, домените се креираат само ако не постојат, а старите CHECK ограничувања од P2 се бришат, за скриптата да може да се извршува повеќепати.
+
+== Користење на вештачка интелигенција ==
+
+[wiki:AdvancedDatabaseDevelopmentAIUsage Користење на вештачка интелигенција за напредното програмирање на базата]