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

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

[attachment: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: Сите вредности се атомски.[[br]]
2NF: Сите атрибути зависат од примарниот клуч.[[br]]
3NF: Нема транзитивни зависности.

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

**paketi**(**paketid**, **ime**, **cena**)

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

**paketid** → **ime**, **cena**

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

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

**tip_vremetranje**(**vremetraenjeid**, **vremetraenjedenovi**, **ime**)

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

**vremetraenjeid** → **vremetraenjedenovi**, **ime**

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

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

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

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

**clenstvoid** → **clenid**, **paketid**, **vremetraenjeid**, **start_datum**

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

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

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

**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: сите атрибути се атомски.[[br]]
2NF: нема составен клуч; сите зависат од PK.[[br]]
3NF: нема транзитивни зависности (телефон/адреса не зависат од ime, туку директно од PK).[[br]]

**poseti**(**posetaid**, **clenid**, **lokacijaid**, **datum**)

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

**posetaid**→ **clenid**, **lokacijaid**, **datum**

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

**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, [[br]]
    **vremetraenjedenovi** INT NOT NULL,  [[br]]
    **ime** VARCHAR(50) NOT NULL
);


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

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

--за lookup на член и сортирање/филтер по датум[[br]]
CREATE INDEX IF NOT EXISTS ix_clenstvo_clenid_start[[br]]
  ON clenstvo (clenid, start_datum DESC);

Филтрирање само по датум[[br]]
CREATE INDEX IF NOT EXISTS ix_clenstvo_start[[br]]
  ON clenstvo (start_datum);

Најново членство по член[[br]]
CREATE INDEX IF NOT EXISTS ix_clenstvo_clenid_start_inc[[br]]
 ON clenstvo (clenid, start_datum DESC) INCLUDE (paketid);

Последни посети на член[[br]]
CREATE INDEX IF NOT EXISTS ix_poseti_clenid_datum[[br]]
  ON poseti (clenid, datum DESC);

Посети по локација[[br]]
CREATE INDEX IF NOT EXISTS ix_poseti_lokacija_datum[[br]]
 ON poseti (lokacijaid, datum DESC);

=== **Тригери** ===

Автоматско броење посети кај член[[br]]
CREATE OR REPLACE FUNCTION trg_poseti_inc_broj_poseti()[[br]]
RETURNS trigger LANGUAGE plpgsql AS $$[[br]]
BEGIN[[br]]
  UPDATE clenovi[[br]]
     SET broj_poseti = COALESCE(broj_poseti, 0) + 1[[br]]
   WHERE clenid = NEW.clenid;
  RETURN NEW;[[br]]
END; $$;[[br]]

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

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

CREATE OR REPLACE FUNCTION trg_clenstvo_no_future_start()[[br]]
RETURNS trigger LANGUAGE plpgsql AS $$[[br]]
BEGIN[[br]]
  IF NEW.start_datum > current_date THEN[[br]]
    RAISE EXCEPTION 'start_datum (%) не смее да биде во иднина', NEW.start_datum;[[br]]
  END IF;[[br]]
  RETURN NEW;[[br]]
END; $$;[[br]]

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

Автоматско пополнување start_datum ако е NULL[[br]]
CREATE OR REPLACE FUNCTION trg_clenstvo_default_start()[[br]]
RETURNS trigger LANGUAGE plpgsql AS $$[[br]]
BEGIN[[br]]
  IF NEW.start_datum IS NULL THEN[[br]]
    NEW.start_datum := current_date;[[br]]
  END IF;[[br]]
  RETURN NEW;[[br]]
END; $$;[[br]]

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


=== **Трансакции** ===

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

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

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

COMMIT;

=== **Cложени извештаи** ===
[attachment:]