Changes between Version 4 and Version 5 of Normalization

Timestamp:

09/02/25 18:43:39 (8 weeks ago)

Author:

185022

Comment:

--

Normalization

@@ -9 +9 @@
 === Функционални зависности
 
-==== Users
+'''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
+'''Customer го проширува User'''
 
 `user_id → cust_EDB, cust_company_name, cust_adr, cust_representative_img  (каде што clazz_ = 'customer')`
 
-==== Manager го проширува User
+'''Manager го проширува User'''
 
 `user_id → wh_id  (каде што clazz_ = 'manager')`
 
-==== Driver го проширува User
+'''Driver го проширува User'''
 
 `user_id → veh_id  (каде што clazz_ = 'driver')`
 
-==== City
+'''City'''
 
 `city_id → city_name, region_id`
 
-==== Region
+'''Region'''
 
 `region_id → region_name`
 
-==== Warehouse
+'''Warehouse'''
 
 `wh_id → wh_adr, city_id`
 
-==== Vehicle
+'''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
+'''Categories'''
 
 `ctg_id → ctg_name`
 
-==== Manufacturer
+'''Manufacturer'''
 
 `man_id → man_name, man_adr, man_mobile, man_email`
 
-==== Article
+'''Article'''
 
 `art_id → art_name, art_image, art_weight, ctg_id, man_id`
 
-==== Price
+'''Price'''
 
 `price_id → price, price_eff_date, art_id`
 
-==== Pro forma
+'''Pro forma'''
 
 `pf_id → pf_deadline, pf_date_created, pf_status_id`
 
-==== Pro Forma Status
+'''Pro Forma Status'''
 
 `pf_status_id → pf_status_name, pf_status_desc`
 
-==== Delivery
+'''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
+'''Delivery Status'''
 
 `d_status_id → d_status_name, d_status_desc`
 
-==== Order
+'''Order'''
 
 `ord_id → ord_date, ord_sum, ord_fulfillment_date, ord_comment, o_status_id, cust_id, del_id, pf_id`
 
-==== Delivery Status
+'''Delivery Status'''
 
 `o_status_id → o_status_name, o_status_desc`
 
-==== Article unit
+'''Article unit'''
 
 `unit_id → unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date, unit_cost_price, wh_id, ord_id`
 
-==== Weekday
+'''Weekday'''
 
 `day_id → day_name`
 
-==== Customer weekday
+'''Customer weekday'''
 
 `cust_day_id → cust_id, day_id, start_time, end_time`
 
-==== Token
+'''Token'''
 
 `t_id → t_value, t_date, t_type, t_expiry, t_validated_at, t_user`
 
-==== Image store
+'''Image store'''
 
 `img_id → img_path, img_ent_type, img_ent_id`
@@ -117 +117 @@
 === Покривачи на примарните клучеви
 
-==== Vehicle
+'''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'''
 
 `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}`
@@ -137 +137 @@
 `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
+'''Manufacturer'''
 
 `man_id+ = {man_id, man_name, man_adr, man_mobile, man_email}`
 
-==== Article
+'''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'''
 
 `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
+'''Pro-forma'''
 
 `pf_id+ = {pf_id, pf_deadline, pf_date_created, pf_status_id, pf_status_name, pf_status_desc}`
 
-==== Delivery
+'''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
+'''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
+'''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}`
@@ -172 +172 @@
 `}`
 
-==== Композитен клуч - Article Unit и Price
+'''Композитен клуч - Article Unit и Price'''
 
 `(unit_id, price_id)+ = {unit_id, price_id,` 
@@ -188 +188 @@
 `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
+'''Weekday'''
 
 `cust_day_id+ = {cust_day_id, cust_id, day_id, start_time, end_time,`
@@ -204 +204 @@
 `city_name, region_id, region_name}`
 
-==== Token
+'''Token'''
 
 `t_id+ = {t_id, t_value, t_date, t_type, t_expiry, t_validated_at, t_user,`
@@ -216 +216 @@
 `city_name, region_id, region_name}`
 
-==== Image Store
+'''Image Store'''
 
 `img_id+ = {img_id, img_path, img_ent_type, img_ent_id}`
@@ -229 +229 @@
 * Поврзување на слики: `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->(треба да зависи на временски период)`
+ * Проблем: Не може да се следат промени на цени.
+
+===== Подобрувања на дизајнот за да се задоволи 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;
+}}}
+
+=== 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);
+}}}
+