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

[[PageOutline(2-4, Содржина, inline)]]

== Определување функционални зависности

`R = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_id, wh_adr, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin,veh_reg_date, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email, art_id, art_name, art_image, art_weight, price_id, price, price_eff_date, pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc, del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, day_id, day_name, cust_day_id, start_time, end_time, t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_id, img_path, img_ent_type, img_ent_id}`

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

'''Users'''

`user_id → user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id`

'''Customer го проширува User'''

`user_id → cust_EDB, cust_company_name, cust_adr, cust_representative_img  (каде што clazz_ = 'customer')`

'''Manager го проширува User'''

`user_id → wh_id  (каде што clazz_ = 'manager')`

'''Driver го проширува User'''

`user_id → veh_id  (каде што clazz_ = 'driver')`

'''City'''

`city_id → city_name, region_id`

'''Region'''

`region_id → region_name`

'''Warehouse'''

`wh_id → wh_adr, city_id`

'''Vehicle'''

`veh_id → veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id`

'''Categories'''

`ctg_id → ctg_name`

'''Manufacturer'''

`man_id → man_name, man_adr, man_mobile, man_email`

'''Article'''

`art_id → art_name, art_image, art_weight, ctg_id, man_id`

'''Price'''

`price_id → price, price_eff_date, art_id`

'''Pro forma'''

`pf_id → pf_deadline, pf_date_created, pf_status_id`

'''Pro Forma Status'''

`pf_status_id → pf_status_name, pf_status_desc`

'''Delivery'''

`del_id → del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id`

'''Delivery Status'''

`d_status_id → d_status_name, d_status_desc`

'''Order'''

`ord_id → ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, cust_id, del_id, pf_id`

'''Delivery Status'''

`o_status_id → o_status_name, o_status_desc`

'''Article unit'''

`unit_id → unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id`

'''Weekday'''

`day_id → day_name`

'''Customer weekday'''

`cust_day_id → cust_id, day_id, start_time, end_time`

'''Token'''

`t_id → t_value, t_date, t_type, t_expiry, t_validated_at, t_user`

'''Image store'''

`img_id → img_path, img_ent_type, img_ent_id`

=== Класификација на атрибути

==== Лево - атрибути кои одредуваат други

`cust_day_id, price_id, pf_id, del_id, ord_id, unit_id, t_id, img_id`

==== Лево и десно - атрибути кои одредуваат други и се одредени од други

`user_id, city_id, region_id, wh_id, veh_id, ctg_id, man_id, art_id, d_status_id, o_status_id, pf_status_id, day_id`

==== Десно - атрибути кои се одредени од други

`user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_name, region_name, cust_EDB, cust_company_name, cust_adr, cust_representative_img, wh_adr, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, ctg_name, man_name, man_adr, man_mobile, man_email, art_name, art_image, art_weight, price, price_eff_date, pf_deadline, pf_date_created, pf_status_name, pf_status_desc, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_name, d_status_desc, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_name, o_status_desc, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, day_name, start_time, end_time, t_value, t_date, t_type, t_expiry, t_validated_at, t_user, img_path, img_ent_type, img_ent_id`

=== Покривачи на примарните клучеви

'''Vehicle'''

`veh_id+ = {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}`

'''User'''

`user_id+ = {user_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, city_name, region_id, region_name}`

* Ако тип на корисник е 'customer':

`user_id+ += {cust_EDB, cust_company_name, cust_adr, cust_representative_img}`

* Ако тип на корисник е 'manager':

`user_id+ += {wh_id, wh_adr}`

* Ако тип на корисник е 'driver':

`user_id+ += {veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr}`

'''Manufacturer'''

`man_id+ = {man_id, man_name, man_adr, man_mobile, man_email}`

'''Article'''

`art_id+ = {art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}`

'''Price'''

`price_id+ = {price_id, price, price_eff_date, art_id, art_name, art_image, art_weight, ctg_id, ctg_name, man_id, man_name, man_adr, man_mobile, man_email}`

'''Pro-forma'''

`pf_id+ = {pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}`

'''Delivery'''

`del_id+ = {del_id, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, d_status_name, d_status_desc, veh_id, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr, city_id, city_name, region_id, region_name}`

'''Order'''

`ord_id+ = {ord_id, ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id, user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img, city_name, region_id, region_name, del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, d_status_id, veh_id, d_status_name, d_status_desc, veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km, veh_plate, veh_vin, veh_reg_date, wh_id, wh_adr,pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}`

'''Article Unit'''

`unit_id+ = {unit_id, unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, wh_adr, city_id, city_name, region_id, region_name, ord_id}`

* Ако ord_id не е null, исто така ги вклучува и:
`unit_id+ += {ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, o_status_name, o_status_desc, cust_id, del_id, pf_id,` 

''и сите атрибути одредени од ord_id''

`}`

'''Композитен клуч - Article Unit и Price'''

`(unit_id, price_id)+ = {unit_id, price_id,` 

''Сите атрибути определни од unit_id''

`unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id,` 

''Сите атрибути определни од price_id''

`price, price_eff_date, art_id,`

''Сите транзитивни атрибути''

`wh_adr, city_id, city_name, region_id, region_name, art_name, art_image, art_weight, ctg_id, man_id, ctg_name, man_name, man_adr, man_mobile, man_email}`

'''Weekday'''

`cust_day_id+ = {cust_day_id, cust_id, day_id, start_time, end_time,`

''Од day_id:''

`day_name,`

''Од cust_id:''

`user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id, cust_EDB, cust_company_name, cust_adr, cust_representative_img,`

''Од city_id:''

`city_name, region_id, region_name}`

'''Token'''

`t_id+ = {t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user,`

''Од t_user (кое е user_id):''

`user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_active, user_image, clazz_, user_role, city_id,`

''Од city_id:''

`city_name, region_id, region_name}`

'''Image Store'''

`img_id+ = {img_id, img_path, img_ent_type, img_ent_id}`

=== Анализа според покривачи

Од анализирање на покривачите може да ги забележиме следните проблеми во нормализацијата на базата:

* Транзитивни зависности во податоците за возила: `veh_id → veh_last_service, veh_last_service_km`
* Недостига зависноста `(ord_id, art_id) → quantity, price`
* Историја на цени и вредност - `unit_id → unit_cost_price`
* Поврзување на слики: `img_id → img_ent_type, img_ent_id`

== Анализа на нормални форми

=== 1 НФ

Овој дизајн ја задоволува првата нормативна форма со тоа што:

* Сите атрибути се атомични.
* Секоја табела има примарен клуч.
* Нема табела со колона која содржи повеќе вредности.

=== 2 НФ

Овој дизајн ја задоволува втората нормативна форма со тоа што:

* Ја задоволува 1НФ.
* Сите обични атрибути се целосно зависни од 1 примарен клуч, секоја табела има 1 примарен клуч.
* Ниту една табела со композитен примарен клуч нема атрибути кои зависат само од еден дел на тој клуч - нема парцијални зависности.

=== 3 НФ

Барања:
* Мора да ја задоволува 2 НФ.
* Да нема транзитивни зависности.

==== Прекршоци на 3 НФ
* Сервисна историја на возила во ентитетот `Vehicle`.
 * Деталите за сервис, конкретно атрибутите - `veh_last_service` и `veh_last_service_km` - зависат од настанот - сервис, а не директно од возилото.
 * '''Функционална зависност''': `veh_id->veh_last_service->(service details)`.
 * Проблем: Не може да се следат повеќе сервисни настани, се зачувува само последната.
* Article Unit Cost Price
 * Цената на чинење на еден артикл зависи од временскиот период, не само од единката артикл.
 * '''Функционална зависност''': `unit_id->unit_cost_price->(треба да зависи на временски период)`
 * Проблем: Не може да се следат промени на цени.
* Релацијата `Order-Article`
 * Нема начин да се асоцираат повеќе артикли со количина со една нарачка.

==== Подобрувања на дизајнот за да се задоволи 3 НФ

===== Vehicle

* Креирање на нова табела за сервисна историја

{{{#!sql
create table vehicle_service {
    service_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    veh_id INT NOT NULL,
    service_date DATE NOT NULL,
    service_km INT NOT NULL,
    service_type VARCHAR(255) NOT NULL,
    service_notes TEXT,
    service_cost DECIMAL(10,2),
    service_next_date DATE,
    service_next_km INT,
    FOREIGN KEY (veh_id) REFERENCES vehicle (veh_id)
}
}}}

* Бришење на атрибутите од `vehicle`

{{{#!sql
create table vehicle_service {
ALTER TABLE vehicle DROP COLUMN veh_last_service;
ALTER TABLE vehicle DROP COLUMN veh_last_service_km;
}}}

* Креирање на поглед за полесен пристап до информации за последен сервис


{{{#!sql
CREATE VIEW vehicle_latest_service AS
SELECT v.veh_id, 
       vs.service_date AS last_service_date,
       vs.service_km AS last_service_km,
       vs.service_next_date,
       vs.service_next_km
FROM vehicle v
LEFT JOIN (
    SELECT veh_id, 
           service_date, 
           service_km,
           service_next_date,
           service_next_km,
           ROW_NUMBER() OVER (PARTITION BY veh_id ORDER BY service_date DESC) as rn
    FROM vehicle_service
) vs ON v.veh_id = vs.veh_id AND vs.rn = 1;
}}}

===== Article Unit

* Креирање на нова табела за историја на чинење за единка артикл


{{{#!sql
CREATE TABLE unit_cost_history (
    history_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    unit_id BIGINT NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL,
    effective_date DATE NOT NULL DEFAULT CURRENT_DATE,
    recorded_by BIGINT,
    FOREIGN KEY (unit_id) REFERENCES article_unit (unit_id),
    FOREIGN KEY (recorded_by) REFERENCES users (user_id)
);
}}}

* Отстранување на на атрибутот `unit_cost_price`


{{{#!sql
ALTER TABLE article_unit DROP COLUMN unit_cost_price;
}}}

* Креирање на поглед за полесен пристап до моменталната цена на единка


{{{#!sql
CREATE VIEW article_unit_current_cost AS
SELECT au.unit_id, uch.cost_price
FROM article_unit au
LEFT JOIN (
    SELECT unit_id, 
           cost_price, 
           ROW_NUMBER() OVER (PARTITION BY unit_id ORDER BY effective_date DESC) as rn
    FROM unit_cost_history
) uch ON au.unit_id = uch.unit_id AND uch.rn = 1;
}}}

===== Order-Article

* Креирање на нова табела за `order items`

{{{#!sql
CREATE TABLE order_item (
    item_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ord_id BIGINT NOT NULL,
    art_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (ord_id) REFERENCES orders (ord_id),
    FOREIGN KEY (art_id) REFERENCES article (art_id),
    UNIQUE (ord_id, art_id)
);
}}}

* Креирање тригер и функција за да се прави пресметката на сума

{{{#!sql
CREATE OR REPLACE FUNCTION update_order_sum()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET ord_sum = (SELECT COALESCE(SUM(total_price), 0) 
        FROM order_item WHERE ord_id = NEW.ord_id)
        WHERE ord_id = NEW.ord_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_item_after_change
AFTER INSERT OR UPDATE OR DELETE ON order_item
FOR EACH ROW EXECUTE FUNCTION update_order_sum();
}}}



=== BCNF

Барања:
* Мора да ја задоволува 3 НФ.
* За секоја нетривијална функционална зависност `X->Y`, мора да има супер-клуч (кандидат клуч)

==== Прекршоци на BCNF

* Атрибутот `email` во ентитетот `Users` не е уникатен.
* Атрибутот `EDB` во ентитетот `Customer` не е уникатен.
* Референцирање на ентитети во `image_store`
 * Моментално нема начин да се осигураме дека `img_ent_id` всушност референцира валиден ентитет.

==== Подобрувања на дизајнот за да се задоволи BCNF

===== Users

* Атрибутот `email` во ентитетот `Users` да добие ограничување да е уникатен.

{{{#!sql
alter table users add constraint uq_user_email unique(user_email) ;
}}}

===== Customer
 
* Атрибутот `EDB` во ентитетот `Customer` да добие ограничување да е уникатен.

{{{#!sql
alter table customer add constraint uq_cust_edb unique(cust_EDB);
}}}

===== Image store

* Ограничување на типот на валидни ентитети

{{{#!sql
alter table image_store
add constraint check_img_entity_type
check (img_ent_type in ('user', 'article', 'customer', 'vehicle'));
}}}

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

{{{#!sql
create index idx_img_entity on image_store(img_ent_type, img_ent_id);
}}}

* Збогатување на ентитетот со атрибути.

{{{#!sql
alter table image_store
add column img_title varchar(255),
add column img_upload_date timestamp default current_timestamp,
add column img_uploaded_by bigint,
add constraint fk_img_uploader foreign key (img_uploaded_by) references users(user_id);
}}}