Changes between Initial Version and Version 1 of Normalization

Timestamp:

01/07/26 01:01:04 (3 days ago)

Author:

221181

Comment:

--

Normalization

@@ +1 @@
+= Формална анализа и доказ за нормализација на Stock Master =
+
+== 1. Дефиниција на универзалната релација ==
+Нека '''R''' претставува универзалната шема на релацијата. Множеството на атрибути '''U''' во '''R''' е дефинирано како:
+
+ U = { user_id, username, password, full_name, email, role, is_active, created_at, 
+ customer_id, c_name, c_email, c_phone, c_address, 
+ category_id, cat_name, description, 
+ supplier_id, s_name, contact_person, s_phone, s_email, s_address, 
+ product_id, prod_name, prod_desc, sku, unit_price, reorder_level, is_active, 
+ warehouse_id, w_name, location, capacity, 
+ sale_id, date_time, total_amount, 
+ po_id, order_date, expected_date, actual_date, status, 
+ quantity, unit_cost, unit_price_at_sale, received_quantity, quantity_on_hand, last_updated }
+
+== 2. Функционални зависности (Functional Dependencies - F) ==
+Врз основа на SQL ограничувањата (CONSTRAINTS) и семантиката на податоците, дефинираме го множеството на функционални зависности '''F''' како канонски покривач:
+
+=== Примарни детерминанти (Simple Determinants) ===
+ 1. user_id -> username, password, full_name, email, role, is_active, created_at
+ 2. customer_id -> c_name, c_email, c_phone, c_address, created_at
+ 3. category_id -> cat_name, description, created_at
+ 4. supplier_id -> s_name, contact_person, s_phone, s_email, s_address, created_at
+ 5. product_id -> prod_name, prod_desc, sku, unit_price, reorder_level, category_id, supplier_id, is_active, created_at
+ 6. warehouse_id -> w_name, location, capacity, created_at
+ 7. sale_id -> date_time, total_amount, user_id, customer_id, warehouse_id
+ 8. po_id -> order_date, expected_delivery_date, actual_delivery_date, status, supplier_id, warehouse_id, created_at
+
+=== Композитни детерминанти (Composite Determinants) ===
+ 1. {sale_id, product_id} -> quantity, unit_price_at_sale
+ 2. {po_id, product_id} -> quantity, unit_cost, received_quantity
+ 3. {warehouse_id, product_id} -> quantity_on_hand, last_updated
+
+=== Кандидат клучеви (Candidate Keys) ===
+ 1. username -> user_id (поради UNIQUE ограничување)
+ 2. email -> user_id (поради UNIQUE ограничување)
+ 3. sku -> product_id (поради UNIQUE ограничување)
+
+== 3. Класификација на атрибути ==
+Врз основа на '''F''', ги извршуваме следниве класификации:
+
+'''Лево (Детерминанти - Determinants):'''
+ { user_id, username, email, customer_id, category_id, supplier_id, product_id, sku, warehouse_id, sale_id, po_id, (sale_id, product_id), (po_id, product_id), (warehouse_id, product_id) }
+
+'''Лево и десно (Примарни и зависни - Prime and Dependent):'''
+Во ''product'' табелата, category_id и supplier_id се зависни од product_id, но во нивните сопствени табели се детерминанти.
+
+'''Десно (Строго зависни - Strictly Dependent):'''
+Сите останати атрибути кои се исклучиво на десната страна на зависностите (на пр. password, full_name, quantity, total_amount).
+
+== 4. Покривачи на примарни клучеви (Closures of Primary Keys) ==
+Анализа на адитивното затворање (attribute closure) за секоја релација:
+
+ 1. '''Users:''' user_id+ = { user_id, username, password, full_name, email, role, is_active, created_at }
+ 2. '''Customer:''' customer_id+ = { customer_id, name, email, phone, address, created_at }
+ 3. '''Category:''' category_id+ = { category_id, name, description, created_at }
+ 4. '''Supplier:''' supplier_id+ = { supplier_id, name, contact_person, phone, email, address, created_at }
+ 5. '''Product:''' product_id+ = { product_id, name, description, sku, unit_price, reorder_level, category_id, supplier_id, is_active, created_at }
+ 6. '''Warehouse:''' warehouse_id+ = { warehouse_id, name, location, capacity, created_at }
+ 7. '''Sale:''' sale_id+ = { sale_id, date_time, total_amount, user_id, customer_id, warehouse_id }
+ 8. '''Purchase Order:''' po_id+ = { po_id, order_date, expected_delivery_date, actual_delivery_date, status, supplier_id, warehouse_id, created_at }
+ 9. '''Sale Item:''' {sale_id, product_id}+ = { sale_id, product_id, quantity, unit_price_at_sale }
+ 10. '''PO Item:''' {po_id, product_id}+ = { po_id, product_id, quantity, unit_cost, received_quantity }
+ 11. '''Warehouse Stock:''' {warehouse_id, product_id}+ = { warehouse_id, product_id, quantity_on_hand, last_updated }
+
+== 5. Доказ за нормални форми (Детален) ==
+
+=== 5.1 1NF (Прва нормална форма) ===
+'''Услов:''' Релацијата е во 1NF ако и само ако сите вредности на атрибутите се атомски (atomic) и нема повторувачки групи.
+
+ * '''Анализа на атомичност:'''
+   * Сите колони во SQL шемата користат примитивни типови на податоци (INTEGER, VARCHAR, DECIMAL, TIMESTAMP, BOOLEAN).
+   * Нема дефинирани релации во внатрешноста на атрибутите (nested relations) или полиски типови.
+   * Иако полето ''address'' има семантички делови, во рамките на релационата тоа е дефинирано како едно поле ''TEXT'', што го задоволува условот за атомичност.
+   * Нема повторувачки групи (на пр., нема колони ''product1'', ''product2''; наместо тоа, се користат табелите ''sale_item'' и ''purchase_order_item'' за повеќекратни вредности).
+
+'''Заклучок:''' Сите табели ги задоволуваат условите за 1NF.
+
+=== 5.2 2NF (Втора нормална форма) ===
+'''Услов:''' Релацијата е во 2NF ако е во 1NF и нема парцијални зависности (не-клучните атрибути мора да зависат од целиот клуч, а не од негов дел).
+
+ * '''Анализа по табели:'''
+   1. '''Табели со едноставен клуч (Single Key):'''
+     * Табели како ''users'' (PK: ''user_id''), ''customer'', ''product'' и др. тривијално го задоволуваат условот за 2NF. Бидејќи примарниот клуч се состои од еден атрибут, не може да постои "дел" од клучот што ќе детерминира нешто друго.
+   2. '''Табели со композитен клуч (Composite Key):'''
+     * '''Релацијата ''sale_item'' (PK: {sale_id, product_id}):'''
+       * Атрибутите ''quantity'' и ''unit_price_at_sale'' зависат од продажбата И производот. Една продажба има многу ставки, а еден производ може да се продаде во многу продажби. Затоа, не можеме да го утврдиме количеството само со ''sale_id'' или само со ''product_id''. Тие зависат од '''целата''' комбинација на клучот.
+       * Нема атрибут во оваа табела кој зависи само од еден дел од клучот.
+     * Истата логика важи за ''purchase_order_item'', ''warehouse_stock''.
+
+'''Заклучок:''' Нема парцијални зависности во композитните клучеви. Сите табели се во 2NF.
+
+=== 5.3 3NF (Трета нормална форма) ===
+'''Услов:''' Релацијата е во 3NF ако е во 2NF и нема транзитивни зависности (не-клучен атрибут не смее да зависи од друг не-клучен атрибут).
+
+ * '''Анализа на транзитивни зависности:'''
+   1. '''Релацијата ''product'':'''
+     * Потенцијална транзитивна зависност би била: product_id -> category_id и category_id -> category_name. Ова би значело product_id -> category_name преку category_id.
+     * Меѓутоа, во дадениот дизајн, category_name не се чува во ''product'' табелата. Тој е преместен во посебна табела ''category''. Оваа '''декомпозиција''' успешно ја отстранува транзитивната зависност.
+   2. '''Релацијата ''sale'':'''
+     * Слично, sale_id -> customer_id и customer_id -> customer_name. Полето ''customer_name'' не е во ''sale'', туку во ''customer''.
+   3. '''Општ преглед:'''
+     * Во сите табели, не-клучните атрибути зависат исклучиво и директно од примарниот клуч, а не преку други не-клучни атрибути.
+
+'''Заклучок:''' Нема транзитивни зависности. Сите табели се во 3NF.
+
+=== 5.4 BCNF (Boyce-Codd нормална форма) ===
+'''Услов:''' Релацијата е во BCNF ако за секоја нетривијална функционална зависност X -> A, X е суперклуч на релацијата.
+
+ * '''Критичен преглед на детерминантите:'''
+   1. '''Табела ''users'':'''
+     * Детерминанти: user_id (PK), username (UNIQUE), email (UNIQUE).
+     * Сите тие (user_id, username, email) се суперклучеви (еднозначно идентификуваат ред). Дополнителните UNIQUE ограничувања не го кршат BCNF.
+   2. '''Табела ''product'':'''
+     * Детерминанти: product_id (PK), sku (UNIQUE).
+     * sku е алтернативен клуч (candidate key), па sku -> ... зависноста има суперклуч како детерминанта.
+   3. '''Останатите табели:'''
+     * Во табелите со композитни клучеви, нема детерминанта што е надвор од клучот или само дел од него (освен самиот PK).
+
+'''Заклучок:''' Нема зависности каде детерминантата не е суперклуч. Шемата е во '''BCNF'''.
+
+== 6. Својства на декомпозицијата ==
+ 1. '''Зачувување на зависности:''' Сите функционални зависности од '''F''' се локализирани во соодветните табели (напр. зависноста за sku е во ''product'' табелата).
+ 2. '''Декомпозиција без загуби:''' Foreign Key constraints обезбедуваат дека JOIN на табелите ќе резултира со оригиналната слика на податоците без изгубени или додадени редови.
+
+== 7. Специјални случаи и подобрувања ==
+
+=== 7.1 ''total_amount'' како изведен атрибут (Денормализација) ===
+'''Статус:''' Складиран прерасчет (Stored Calculated Value).
+
+'''Анализа:''' Атрибутот ''sale.total_amount'' може да се пресмета како SUM(sale_item.quantity * sale_item.unit_price_at_sale). Неговото чување во ''sale'' табелата е денормализација наменета за перформанси.
+
+'''Техничко решение за конзистентност (Trigger):'''
+
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION update_sale_total()
+RETURNS TRIGGER AS $$
+BEGIN
+    UPDATE sale s
+    SET total_amount = (
+        SELECT COALESCE(SUM(si.quantity * si.unit_price_at_sale), 0)
+        FROM sale_item si
+        WHERE si.sale_id = COALESCE(NEW.sale_id, OLD.sale_id)
+    )
+    WHERE s.sale_id = COALESCE(NEW.sale_id, OLD.sale_id);
+    
+    RETURN COALESCE(NEW, OLD);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_update_sale_total
+AFTER INSERT OR UPDATE OR DELETE ON sale_item
+FOR EACH ROW EXECUTE FUNCTION update_sale_total();
+}}}
+
+=== 7.2 Атомичност на ''address'' ===
+'''Статус:''' Потенцијално кршење на 1NF.
+
+'''Анализа:''' Адресата е составена од повеќе семантички делови (улица, град, поштенски код, држава). Држењето на сите овие податоци во едно ''TEXT'' поле ја отежнува агрегацијата (на пр. пребарување на сите корисници во градот "Skopje").
+
+'''Предложена декомпозиција:'''
+
+{{{
+#!sql
+ALTER TABLE customer 
+ADD COLUMN street_address VARCHAR(255),
+ADD COLUMN city VARCHAR(100),
+ADD COLUMN zip_code VARCHAR(20),
+ADD COLUMN country VARCHAR(100) DEFAULT 'Macedonia';
+
+ALTER TABLE supplier 
+ADD COLUMN street_address VARCHAR(255),
+ADD COLUMN city VARCHAR(100),
+ADD COLUMN zip_code VARCHAR(20),
+ADD COLUMN country VARCHAR(100) DEFAULT 'Macedonia';
+}}}
+
+== 8. Краен Заклучок ==
+Врз основа на извршената формална анализа, вердиктот е следен:
+
+'''Базата на податоци е до нормализирана до BCNF.'''
+
+Доказот поткрепува дека:
+ 1. '''1NF:''' Сите вредности се атомски.
+ 2. '''2NF:''' Нема делумни зависности во композитните клучеви.
+ 3. '''3NF:''' Нема транзитивни зависности поради правилното користење на Foreign Keys.
+ 4. '''BCNF:''' Сите детерминанти се суперклучеви (вклучувајќи ги UNIQUE ограничувањата).
+
+Предложените подобрувања за ''address'' и ''total_amount'' служат за зголемување на атомичноста (теоретска совршеност) и одржување на конзистентноста (практична примена).