wiki:Normalization

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

Содржина

  1. Определување функционални зависности
    1. Функционални зависности
    2. Класификација на атрибути
      1. Лево - атрибути кои одредуваат други
      2. Лево и десно - атрибути кои одредуваат други и се одредени од други
      3. Десно - атрибути кои се одредени од други
    3. Покривачи на примарните клучеви
    4. Анализа според покривачи
  2. Анализа на нормални форми
    1. 1 НФ
    2. 2 НФ
    3. 3 НФ
      1. Прекршоци на 3 НФ
      2. Подобрувања на дизајнот за да се задоволи 3 НФ
    4. BCNF
      1. Прекршоци на BCNF
      2. Подобрувања на дизајнот за да се задоволи BCNF

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

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

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

Order Status

o_status_id+={o_status_id, o_status_name, o_status_desc}

Delivery Status

d_status_id+={d_status_id, d_status_name, d_status_desc}

Pro Forma Status

pf_status_id+={pf_status_id, pf_status_name, pf_status_desc}

City

city_id+={city_id, city_name, region_id, region_name}

Region

region_id+={region_id, region_name}

Weekday

day_id+={day_id, day_name}

Warehouse

wh_id+={wh_id, wh_adr}

Category

ctg_id+={ctg_id, ctg_name}

Manufacturer

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

User

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

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

Атрибутите на User и дополнително следните атрибути:

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

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

Атрибутите на User и дополнително следните атрибути:

user_id+ = {wh_id, wh_adr}

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

Атрибутите на User и дополнително следните атрибути:

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}

Vehicle

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

Article

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

Price

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

Pro-forma

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

Delivery

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

Order

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

Article Unit

unit_id+ = {unit_id, unit_serial_no, unit_batch_no, unit_manufacture_date, unit_expiration_date, unit_cost_price, wh_id, wh_adr, price_id, price, price_eff_date, art_id, art_image, art_name, art_weight, ctg_id, ctg_name, man_id, man_name, man_email, man_mobile, man_adr, ord_id, ord_sum, ord_comment, ord_date, ord_fulfillment_date, o_status_id, o_status_desc, o_status_name, user_id, user_name, user_surname, user_mobile, user_role, user_email, user_active, user_pass, user_salt, clazz_, user_image, cust_EDB, cust_company_name, cust_adr, cust_representative_img, city_id, city_name, region_id, region_name, del_id, del_date_created, del_start_time, del_end_km, del_start_km, del_end_time, del_date, d_status_id, d_status_desc, d_status_name, veh_id, veh_reg_date, veh_plate, veh_kilometers, veh_carry_weight, veh_vin, veh_service_interval, veh_last_service, veh_last_service_km}

Token

t_id+={t_id, t_date, t_value, t_type, t_validated_at, t_expiry, user_pass, user_active, user_name, user_email, user_surname, user_mobile, user_role, user_salt, clazz_, user_image, city_id, city_name, region_id, region_name}

Image Store

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

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

{unit_id, price_id}+={unit_id, price_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, 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}

Customer Weekday

cust_day_id+={cust_day_id, cust_id, user_name, user_surname, 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, cust_loc_latitude, cust_loc_longitude, day_id, day_name, start_time, end_time}

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

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

  • Транзитивни зависности во податоците за возила: 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
  • Креирање на нова табела за сервисна историја 
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 
create table vehicle_service {
ALTER TABLE vehicle DROP COLUMN veh_last_service;
ALTER TABLE vehicle DROP COLUMN veh_last_service_km;
  • Креирање на поглед за полесен пристап до информации за последен сервис 
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
  • Креирање на нова табела за историја на чинење за единка артикл 
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 
ALTER TABLE article_unit DROP COLUMN unit_cost_price;
  • Креирање на поглед за полесен пристап до моменталната цена на единка 
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 item 
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)
);
  • Додавање и промена на атрибути 
ALTER TABLE article_unit DROP COLUMN ord_id;
ALTER TABLE article_unit ADD COLUMN order_item_id BIGINT;
ALTER TABLE article_unit ADD CONSTRAINT fk_article_unit_order_item 
    FOREIGN KEY (order_item_id) REFERENCES order_item (item_id);
  • Креирање тригер и функција за да се прави пресметката на сума 
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 да добие ограничување да е уникатен. 
alter table users add constraint uq_user_email unique(user_email) ;
Customer

  • Атрибутот EDB во ентитетот Customer да добие ограничување да е уникатен. 
alter table customer add constraint uq_cust_edb unique(cust_EDB);
Image store
  • Ограничување на типот на валидни ентитети 
alter table image_store
add constraint check_img_entity_type
check (img_ent_type in ('user', 'article', 'customer', 'vehicle'));
  • Додавање на индекс за подобри перформанси при пребарување. 
create index idx_img_entity on image_store(img_ent_type, img_ent_id);
  • Збогатување на ентитетот со атрибути. 
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);
Last modified 6 weeks ago Last modified on 09/16/25 01:27:24
Note: See TracWiki for help on using the wiki.