Changes between Version 3 and Version 4 of Normalization


Ignore:
Timestamp:
01/14/26 02:54:59 (13 days ago)
Author:
221181
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v3 v4  
    1 = Формална анализа и доказ за нормализација на Stock Master =
     1= Нормализација =
    22
    3 == 1. Дефиниција на универзалната релација ==
    4 Нека '''R''' претставува универзалната шема на релацијата. Множеството на атрибути '''U''' во '''R''' е дефинирано како:
     3== Вовед ==
    54
    6  U = { user_id, username, password, full_name, email, role, is_active, created_at,
    7  customer_id, c_name, c_email, c_phone, c_address,
    8  category_id, cat_name, description,
    9  supplier_id, s_name, contact_person, s_phone, s_email, s_address,
    10  product_id, prod_name, prod_desc, sku, unit_price, reorder_level, is_active,
    11  warehouse_id, w_name, location, capacity,
    12  sale_id, date_time, total_amount,
    13  po_id, order_date, expected_date, actual_date, status,
    14  quantity, unit_cost, unit_price_at_sale, received_quantity, quantity_on_hand, last_updated }
    15 
    16 == 2. Функционални зависности (Functional Dependencies - F) ==
    17 Врз основа на SQL ограничувањата (CONSTRAINTS) и семантиката на податоците, дефинираме го множеството на функционални зависности '''F''' како канонски покривач:
    18 
    19 === Примарни детерминанти (Simple Determinants) ===
    20  1. user_id -> username, password, full_name, email, role, is_active, created_at
    21  2. customer_id -> c_name, c_email, c_phone, c_address, created_at
    22  3. category_id -> cat_name, description, created_at
    23  4. supplier_id -> s_name, contact_person, s_phone, s_email, s_address, created_at
    24  5. product_id -> prod_name, prod_desc, sku, unit_price, reorder_level, category_id, supplier_id, is_active, created_at
    25  6. warehouse_id -> w_name, location, capacity, created_at
    26  7. sale_id -> date_time, total_amount, user_id, customer_id, warehouse_id
    27  8. po_id -> order_date, expected_delivery_date, actual_delivery_date, status, supplier_id, warehouse_id, created_at
    28 
    29 === Композитни детерминанти (Composite Determinants) ===
    30  1. {sale_id, product_id} -> quantity, unit_price_at_sale
    31  2. {po_id, product_id} -> quantity, unit_cost, received_quantity
    32  3. {warehouse_id, product_id} -> quantity_on_hand, last_updated
    33 
    34 === Кандидат клучеви (Candidate Keys) ===
    35  1. username -> user_id (поради UNIQUE ограничување)
    36  2. email -> user_id (поради UNIQUE ограничување)
    37  3. sku -> product_id (поради UNIQUE ограничување)
    38 
    39 == 3. Класификација на атрибути ==
    40 Врз основа на '''F''', ги извршуваме следниве класификации:
    41 
    42 '''Лево (Детерминанти - Determinants):'''
    43  { 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) }
    44 
    45 '''Лево и десно (Примарни и зависни - Prime and Dependent):'''
    46 Во ''product'' табелата, category_id и supplier_id се зависни од product_id, но во нивните сопствени табели се детерминанти.
    47 
    48 '''Десно (Строго зависни - Strictly Dependent):'''
    49 Сите останати атрибути кои се исклучиво на десната страна на зависностите (на пр. password, full_name, quantity, total_amount).
    50 
    51 == 4. Покривачи на примарни клучеви (Closures of Primary Keys) ==
    52 Анализа на адитивното затворање (attribute closure) за секоја релација:
    53 
    54  1. '''Users:''' user_id+ = { user_id, username, password, full_name, email, role, is_active, created_at }
    55  2. '''Customer:''' customer_id+ = { customer_id, name, email, phone, address, created_at }
    56  3. '''Category:''' category_id+ = { category_id, name, description, created_at }
    57  4. '''Supplier:''' supplier_id+ = { supplier_id, name, contact_person, phone, email, address, created_at }
    58  5. '''Product:''' product_id+ = { product_id, name, description, sku, unit_price, reorder_level, category_id, supplier_id, is_active, created_at }
    59  6. '''Warehouse:''' warehouse_id+ = { warehouse_id, name, location, capacity, created_at }
    60  7. '''Sale:''' sale_id+ = { sale_id, date_time, total_amount, user_id, customer_id, warehouse_id }
    61  8. '''Purchase Order:''' po_id+ = { po_id, order_date, expected_delivery_date, actual_delivery_date, status, supplier_id, warehouse_id, created_at }
    62  9. '''Sale Item:''' {sale_id, product_id}+ = { sale_id, product_id, quantity, unit_price_at_sale }
    63  10. '''PO Item:''' {po_id, product_id}+ = { po_id, product_id, quantity, unit_cost, received_quantity }
    64  11. '''Warehouse Stock:''' {warehouse_id, product_id}+ = { warehouse_id, product_id, quantity_on_hand, last_updated }
    65 
    66 == 5. Доказ за нормални форми (Детален) ==
    67 
    68 === 5.1 1NF (Прва нормална форма) ===
    69 '''Услов:''' Релацијата е во 1NF ако и само ако сите вредности на атрибутите се атомски (atomic) и нема повторувачки групи.
    70 
    71  * '''Анализа на атомичност:'''
    72    * Сите колони во SQL шемата користат примитивни типови на податоци (INTEGER, VARCHAR, DECIMAL, TIMESTAMP, BOOLEAN).
    73    * Нема дефинирани релации во внатрешноста на атрибутите (nested relations) или полиски типови.
    74    * Иако полето ''address'' има семантички делови, во рамките на релационата тоа е дефинирано како едно поле ''TEXT'', што го задоволува условот за атомичност.
    75    * Нема повторувачки групи (на пр., нема колони ''product1'', ''product2''; наместо тоа, се користат табелите ''sale_item'' и ''purchase_order_item'' за повеќекратни вредности).
    76 
    77 '''Заклучок:''' Сите табели ги задоволуваат условите за 1NF.
    78 
    79 === 5.2 2NF (Втора нормална форма) ===
    80 '''Услов:''' Релацијата е во 2NF ако е во 1NF и нема парцијални зависности (не-клучните атрибути мора да зависат од целиот клуч, а не од негов дел).
    81 
    82  * '''Анализа по табели:'''
    83    1. '''Табели со едноставен клуч (Single Key):'''
    84      * Табели како ''users'' (PK: ''user_id''), ''customer'', ''product'' и др. тривијално го задоволуваат условот за 2NF. Бидејќи примарниот клуч се состои од еден атрибут, не може да постои "дел" од клучот што ќе детерминира нешто друго.
    85    2. '''Табели со композитен клуч (Composite Key):'''
    86      * '''Релацијата ''sale_item'' (PK: {sale_id, product_id}):'''
    87        * Атрибутите ''quantity'' и ''unit_price_at_sale'' зависат од продажбата И производот. Една продажба има многу ставки, а еден производ може да се продаде во многу продажби. Затоа, не можеме да го утврдиме количеството само со ''sale_id'' или само со ''product_id''. Тие зависат од '''целата''' комбинација на клучот.
    88        * Нема атрибут во оваа табела кој зависи само од еден дел од клучот.
    89      * Истата логика важи за ''purchase_order_item'', ''warehouse_stock''.
    90 
    91 '''Заклучок:''' Нема парцијални зависности во композитните клучеви. Сите табели се во 2NF.
    92 
    93 === 5.3 3NF (Трета нормална форма) ===
    94 '''Услов:''' Релацијата е во 3NF ако е во 2NF и нема транзитивни зависности (не-клучен атрибут не смее да зависи од друг не-клучен атрибут).
    95 
    96  * '''Анализа на транзитивни зависности:'''
    97    1. '''Релацијата ''product'':'''
    98      * Потенцијална транзитивна зависност би била: product_id -> category_id и category_id -> category_name. Ова би значело product_id -> category_name преку category_id.
    99      * Меѓутоа, во дадениот дизајн, category_name не се чува во ''product'' табелата. Тој е преместен во посебна табела ''category''. Оваа '''декомпозиција''' успешно ја отстранува транзитивната зависност.
    100    2. '''Релацијата ''sale'':'''
    101      * Слично, sale_id -> customer_id и customer_id -> customer_name. Полето ''customer_name'' не е во ''sale'', туку во ''customer''.
    102    3. '''Општ преглед:'''
    103      * Во сите табели, не-клучните атрибути зависат исклучиво и директно од примарниот клуч, а не преку други не-клучни атрибути.
    104 
    105 '''Заклучок:''' Нема транзитивни зависности. Сите табели се во 3NF.
    106 
    107 === 5.4 BCNF (Boyce-Codd нормална форма) ===
    108 '''Услов:''' Релацијата е во BCNF ако за секоја нетривијална функционална зависност X -> A, X е суперклуч на релацијата.
    109 
    110  * '''Критичен преглед на детерминантите:'''
    111    1. '''Табела ''users'':'''
    112      * Детерминанти: user_id (PK), username (UNIQUE), email (UNIQUE).
    113      * Сите тие (user_id, username, email) се суперклучеви (еднозначно идентификуваат ред). Дополнителните UNIQUE ограничувања не го кршат BCNF.
    114    2. '''Табела ''product'':'''
    115      * Детерминанти: product_id (PK), sku (UNIQUE).
    116      * sku е кандидат клуч (candidate key), па sku -> ... зависноста има суперклуч како детерминанта.
    117    3. '''Останатите табели:'''
    118      * Во табелите со композитни клучеви, нема детерминанта што е надвор од клучот или само дел од него (освен самиот PK).
    119 
    120 '''Заклучок:''' Нема зависности каде детерминантата не е суперклуч. Шемата е во '''BCNF'''.
    121 
    122 == 6. Својства на декомпозицијата ==
    123  1. '''Зачувување на зависности:''' Сите функционални зависности од '''F''' се локализирани во соодветните табели (напр. зависноста за sku е во ''product'' табелата).
    124  2. '''Декомпозиција без загуби:''' Foreign Key constraints обезбедуваат дека JOIN на табелите ќе резултира со оригиналната слика на податоците без изгубени или додадени редови.
    125 
    126 == 7. Специјални случаи и подобрувања ==
    127 
    128 === 7.1 ''total_amount'' како изведен атрибут (Денормализација) ===
    129 '''Статус:''' Складиран прерасчет (Stored Calculated Value).
    130 
    131 '''Анализа:''' Атрибутот ''sale.total_amount'' може да се пресмета како SUM(sale_item.quantity * sale_item.unit_price_at_sale). Неговото чување во ''sale'' табелата е денормализација наменета за перформанси.
    132 
    133 '''Техничко решение за конзистентност (Trigger):'''
    134 
    135 {{{
    136 #!sql
    137 CREATE OR REPLACE FUNCTION update_sale_total()
    138 RETURNS TRIGGER AS $$
    139 BEGIN
    140     UPDATE sale s
    141     SET total_amount = (
    142         SELECT COALESCE(SUM(si.quantity * si.unit_price_at_sale), 0)
    143         FROM sale_item si
    144         WHERE si.sale_id = COALESCE(NEW.sale_id, OLD.sale_id)
    145     )
    146     WHERE s.sale_id = COALESCE(NEW.sale_id, OLD.sale_id);
    147    
    148     RETURN COALESCE(NEW, OLD);
    149 END;
    150 $$ LANGUAGE plpgsql;
    151 
    152 CREATE TRIGGER trg_update_sale_total
    153 AFTER INSERT OR UPDATE OR DELETE ON sale_item
    154 FOR EACH ROW EXECUTE FUNCTION update_sale_total();
    155 }}}
    156 
    157 === 7.2 Атомичност на ''address'' ===
    158 '''Статус:''' Потенцијално кршење на 1NF.
    159 
    160 '''Анализа:''' Адресата е составена од повеќе семантички делови (улица, град, поштенски код, држава). Држењето на сите овие податоци во едно ''TEXT'' поле ја отежнува агрегацијата (на пр. пребарување на сите корисници во градот "Skopje").
    161 
    162 '''Предложена декомпозиција:'''
    163 
    164 {{{
    165 #!sql
    166 ALTER TABLE customer
    167 ADD COLUMN street_address VARCHAR(255),
    168 ADD COLUMN city VARCHAR(100),
    169 ADD COLUMN zip_code VARCHAR(20),
    170 ADD COLUMN country VARCHAR(100) DEFAULT 'Macedonia';
    171 
    172 ALTER TABLE supplier
    173 ADD COLUMN street_address VARCHAR(255),
    174 ADD COLUMN city VARCHAR(100),
    175 ADD COLUMN zip_code VARCHAR(20),
    176 ADD COLUMN country VARCHAR(100) DEFAULT 'Macedonia';
    177 }}}
    178 
    179 == 8. Краен Заклучок ==
    180 Врз основа на извршената формална анализа, заклучокот е следниов:
    181 
    182 '''Базата на податоци е до нормализирана до BCNF.'''
    183 
    184 Доказот поткрепува дека:
    185  1. '''1NF:''' Сите вредности се атомски.
    186  2. '''2NF:''' Нема делумни зависности во композитните клучеви.
    187  3. '''3NF:''' Нема транзитивни зависности поради правилното користење на Foreign Keys.
    188  4. '''BCNF:''' Сите детерминанти се суперклучеви (вклучувајќи ги UNIQUE ограничувањата).
    189 
    190 Предложените подобрувања за ''address'' и ''total_amount'' служат за зголемување на атомичноста (теоретска совршеност) и одржување на конзистентноста (практична примена).
     5Целта на оваа анализа е да се прикаже процесот на нормализација за предложениот модел на базата на податоци Stock Master, од идентификација на функционалните зависимости до постигнување на BCNF (Boyce-Codd нормална форма). Анализата е изведена врз основа на ентитетите и релациите од ER моделот разработен во Фаза P1.