Нормализација и подобрувања на дизајнот

Во оваа секција е прикажана нормализацијата на целокупниот ER модел до трета нормална форма (3NF), за да се отстранат зависностите, се избегне дуплирање на податоци и да се подобри интегритетот.

normlizacija_ddl_schema.3.sql​

Објаснување на нормализацијата

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

Клучни подобрувања:

-Централизација на времетраењето на пакетите преку табелата tip_vremetranje. -Подобрување на интегритетот на податоците со користење на foreign keys помеѓу clenstvo, clenovi, paketi и tip_vremetranje. -Елиминирање на можни транзитивни зависности (пример: clenovi → clenstvo → paketi). -Воведување на поврзувачки табели за многу-на-многу релации (trenira, raboti).

Со оваа нормализација до 3NF, се подобрува конзистентноста, се намалува дуплирањето на податоци и се поедноставува одржувањето на базата.

Анализа по табела

clenovi(clenid, ime, prezime, email, broj_poseti)

Функционални зависности:

clenid → ime, prezime, email, broj_poseti

email → clenid (уникатност)

1NF: Сите вредности се атомски.
2NF: Сите атрибути зависат од примарниот клуч.
3NF: Нема транзитивни зависности.

Декомпозиција: / (нема потреба)

paketi(paketid, ime, cena)

Функционални зависности:

paketid → ime, cena

1NF: Атомски вредности.
2NF: Сите зависат од примарниот клуч.
3NF: Нема транзитивни зависности.

Декомпозиција: / (нема потреба)

tip_vremetranje(vremetraenjeid, vremetraenjedenovi, ime)

Функционални зависности:

vremetraenjeid → vremetraenjedenovi, ime

1NF: Атомски вредности.
2NF: Сите зависности се од примарниот клуч.
3NF: Нема транзитивни зависности.

Декомпозиција: / (нема потреба)

clenstvo(clenstvoid, clenid, paketid, vremetraenjeid, start_datum)

Функционални зависности:

clenstvoid → clenid, paketid, vremetraenjeid, start_datum

clenid → ime, prezime, email (транзитивна зависност ако би биле во иста табела)

1NF: Атомски вредности.
2NF: Сите зависности се од примарниот клуч.
3NF: Со референтни клучеви се елиминира транзитивноста.

Декомпозиција:

clenstvo(clenstvoid, clenid, paketid, vremetraenjeid, start_datum)

clenovi(clenid, ime, prezime, email, broj_poseti)

paketi(paketid, ime, cena)

tip_vremetranje(vremetraenjeid, vremetraenjedenovi,ime)

lokacii(lokacijaid, ime, adresa, telefon)

Функционални зависности: lokacijaid → ime, adresa, telefon

1NF: сите атрибути се атомски.
2NF: нема составен клуч; сите зависат од PK.
3NF: нема транзитивни зависности (телефон/адреса не зависат од ime, туку директно од PK).

poseti(posetaid, clenid, lokacijaid, datum)

Функционални зависности:

posetaid→ clenid, lokacijaid, datum

1NF: Атомски вредности.
2NF: Сите зависности од клучот.
3NF: Нема транзитивни зависности.

vraboteni(vrabotenid, ime, pozicija)

Функционални зависности:

vrabotenid → ime, pozicija

1NF/2NF/3NF: Веќе е нормализирана.

trenira(clenid, vrabotenid)

Функционални зависности:

(clenid, vrabotenid) → { } (само примарен клуч)

1NF/2NF/3NF: Релација за многу-на-многу.

raboti(vrabotenid, lokacijaid)

Функционални зависности:

(vrabotenid, lokacijaid) → { }

1NF/2NF/3NF: Веќе е нормализирана.

Нови релации по нормализација

Во однос на првичниот дизајн, не беше потребно да се воведат дополнителни табели освен оние што веќе постојат (tip_vremetranje за времетраење, trenira и raboti за многу-на-многу врски).

CREATE TABLE tip_vremetranje (

vremetraenjeid SERIAL PRIMARY KEY,
vremetraenjedenovi INT NOT NULL,
ime VARCHAR(50) NOT NULL

);

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

Индекси за подобрување на перформанси

--за lookup на член и сортирање/филтер по датум
CREATE INDEX IF NOT EXISTS ix_clenstvo_clenid_start

ON clenstvo (clenid, start_datum DESC);

Филтрирање само по датум
CREATE INDEX IF NOT EXISTS ix_clenstvo_start

ON clenstvo (start_datum);

Најново членство по член
CREATE INDEX IF NOT EXISTS ix_clenstvo_clenid_start_inc

ON clenstvo (clenid, start_datum DESC) INCLUDE (paketid);

Последни посети на член
CREATE INDEX IF NOT EXISTS ix_poseti_clenid_datum

ON poseti (clenid, datum DESC);

Посети по локација
CREATE INDEX IF NOT EXISTS ix_poseti_lokacija_datum

ON poseti (lokacijaid, datum DESC);

Тригери

Автоматско броење посети кај член
CREATE OR REPLACE FUNCTION trg_poseti_inc_broj_poseti()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN

UPDATE clenovi

SET broj_poseti = COALESCE(broj_poseti, 0) + 1

WHERE clenid = NEW.clenid;

RETURN NEW;

END; $$;

DROP TRIGGER IF EXISTS poseti_inc_broj_poseti ON poseti;
CREATE TRIGGER poseti_inc_broj_poseti
AFTER INSERT ON poseti
FOR EACH ROW
EXECUTE FUNCTION trg_poseti_inc_broj_poseti();

Забрана за членство во иднина

CREATE OR REPLACE FUNCTION trg_clenstvo_no_future_start()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN

IF NEW.start_datum > current_date THEN

RAISE EXCEPTION 'start_datum (%) не смее да биде во иднина', NEW.start_datum;

END IF;
RETURN NEW;

END; $$;

DROP TRIGGER IF EXISTS clenstvo_no_future_start ON clenstvo;
CREATE TRIGGER clenstvo_no_future_start
BEFORE INSERT OR UPDATE ON clenstvo
FOR EACH ROW
EXECUTE FUNCTION trg_clenstvo_no_future_start();

Автоматско пополнување start_datum ако е NULL
CREATE OR REPLACE FUNCTION trg_clenstvo_default_start()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN

IF NEW.start_datum IS NULL THEN

NEW.start_datum := current_date;

END IF;
RETURN NEW;

END; $$;

DROP TRIGGER IF EXISTS clenstvo_default_start ON clenstvo;
CREATE TRIGGER clenstvo_default_start
BEFORE INSERT ON clenstvo
FOR EACH ROW
EXECUTE FUNCTION trg_clenstvo_default_start();

Трансакции

Трансакции за внес во повеќе табали
BEGIN;
--членство INSERT INTO clenstvo (clenid, paketid, start_datum)
VALUES (:clenid, :paketid, current_date)
RETURNING clenstvoid;

--посета INSERT INTO poseti (clenid, lokacijaid, datum)
VALUES (:clenid, :lokacijaid, current_date);

-- врзувањље со тренер -- INSERT INTO trenira (clenid, vrabotenid) VALUES (:clenid, :vrabotenid);

COMMIT;

Cложени извештаи

queries.sql​