Changes between Version 9 and Version 10 of Normalization


Ignore:
Timestamp:
01/15/26 05:58:28 (12 days ago)
Author:
221181
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v9 v10  
    22
    33== 1. Дефиниција на универзалната релација ==
     4
     5Тргнуваме од една единствена универзална релација која ги содржи сите атрибути од доменот. Заборавме на ентитети и релации - имаме само една релација со сите податоци:
     6
    47Нека '''R''' претставува универзалната шема на релацијата. Множеството на атрибути '''U''' во '''R''' е дефинирано како:
    58
    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'' служат за зголемување на атомичноста (теоретска совршеност) и одржување на конзистентноста (практична примена).
     9{{{
     10U(userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active,
     11  customerId, customer_name, customer_email, customer_phone, customer_address,
     12  productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level,
     13  categoryId, category_name, category_description,
     14  supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address,
     15  warehouseId, warehouse_name, warehouse_location, warehouse_capacity,
     16  saleId, sale_date_time, sale_total_amount,
     17  poId, po_order_date, po_expected_delivery_date, po_status,
     18  saleitem_quantity, saleitem_unit_price_at_sale,
     19  poitem_quantity, poitem_unit_cost,
     20  stock_quantity_on_hand, stock_last_updated)
     21}}}
     22
     23----
     24
     25== 2. Функционални зависности ==
     26
     27Ги идентификуваме сите функционални зависности што важат во универзалната релација:
     28
     29'''F = {'''
     30 * '''FD1:''' userId → user_username, user_password, user_full_name, user_email, user_role, user_is_active
     31 * '''FD2:''' user_username → userId, user_password, user_full_name, user_email, user_role, user_is_active
     32 * '''FD3:''' user_email → userId, user_username, user_password, user_full_name, user_role, user_is_active
     33 * '''FD4:''' customerId → customer_name, customer_email, customer_phone, customer_address
     34 * '''FD5:''' productId → product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId
     35 * '''FD6:''' product_sku → productId, product_name, product_description, product_unit_price, product_reorder_level, categoryId, supplierId
     36 * '''FD7:''' categoryId → category_name, category_description
     37 * '''FD8:''' supplierId → supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address
     38 * '''FD9:''' warehouseId → warehouse_name, warehouse_location, warehouse_capacity
     39 * '''FD10:''' saleId → sale_date_time, sale_total_amount, userId, customerId, warehouseId
     40 * '''FD11:''' poId → po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId
     41 * '''FD12:''' {saleId, productId} → saleitem_quantity, saleitem_unit_price_at_sale
     42 * '''FD13:''' {poId, productId} → poitem_quantity, poitem_unit_cost
     43 * '''FD14:''' {warehouseId, productId} → stock_quantity_on_hand, stock_last_updated
     44'''}'''
     45
     46----
     47
     48== 3. Кандидат клучеви и примарен клуч ==
     49
     50=== 3.1 Определување на кандидат клучеви ===
     51
     52За да најдеме кандидат клуч, треба да најдеме минимално множество атрибути чиј затворач ги содржи сите атрибути на релацијата.
     53
     54=== 3.2 Класификација на атрибути (Лева/Десна страна) ===
     55
     56||= Атрибут =||= Лева страна =||= Десна страна =||= Класификација =||
     57|| userId || ✓ (FD1,FD3) || ✓ (FD2,FD3,FD10) || Обете ||
     58|| user_username || ✓ (FD2) || ✓ (FD1,FD3) || Обете ||
     59|| user_email || ✓ (FD3) || ✓ (FD1,FD2) || Обете ||
     60|| user_password || ✗ || ✓ || Само десно ||
     61|| user_full_name || ✗ || ✓ || Само десно ||
     62|| user_role || ✗ || ✓ || Само десно ||
     63|| user_is_active || ✗ || ✓ || Само десно ||
     64|| customerId || ✓ (FD4) || ✓ (FD10) || Обете ||
     65|| customer_name || ✗ || ✓ || Само десно ||
     66|| customer_email || ✗ || ✓ || Само десно ||
     67|| customer_phone || ✗ || ✓ || Само десно ||
     68|| customer_address || ✗ || ✓ || Само десно ||
     69|| productId || ✓ (FD5,FD12,FD13,FD14) || ✓ (FD6) || Обете ||
     70|| product_sku || ✓ (FD6) || ✓ (FD5) || Обете ||
     71|| product_name || ✗ || ✓ || Само десно ||
     72|| product_description || ✗ || ✓ || Само десно ||
     73|| product_unit_price || ✗ || ✓ || Само десно ||
     74|| product_reorder_level || ✗ || ✓ || Само десно ||
     75|| categoryId || ✓ (FD7) || ✓ (FD5,FD6) || Обете ||
     76|| category_name || ✗ || ✓ || Само десно ||
     77|| category_description || ✗ || ✓ || Само десно ||
     78|| supplierId || ✓ (FD8) || ✓ (FD5,FD6,FD11) || Обете ||
     79|| supplier_name || ✗ || ✓ || Само десно ||
     80|| supplier_contact_person || ✗ || ✓ || Само десно ||
     81|| supplier_phone || ✗ || ✓ || Само десно ||
     82|| supplier_email || ✗ || ✓ || Само десно ||
     83|| supplier_address || ✗ || ✓ || Само десно ||
     84|| warehouseId || ✓ (FD9,FD14) || ✓ (FD10,FD11) || Обете ||
     85|| warehouse_name || ✗ || ✓ || Само десно ||
     86|| warehouse_location || ✗ || ✓ || Само десно ||
     87|| warehouse_capacity || ✗ || ✓ || Само десно ||
     88|| '''saleId''' || ✓ (FD10,FD12) || ✗ || '''Само лево''' ||
     89|| sale_date_time || ✗ || ✓ || Само десно ||
     90|| sale_total_amount || ✗ || ✓ || Само десно ||
     91|| '''poId''' || ✓ (FD11,FD13) || ✗ || '''Само лево''' ||
     92|| po_order_date || ✗ || ✓ || Само десно ||
     93|| po_expected_delivery_date || ✗ || ✓ || Само десно ||
     94|| po_status || ✗ || ✓ || Само десно ||
     95|| saleitem_quantity || ✗ || ✓ || Само десно ||
     96|| saleitem_unit_price_at_sale || ✗ || ✓ || Само десно ||
     97|| poitem_quantity || ✗ || ✓ || Само десно ||
     98|| poitem_unit_cost || ✗ || ✓ || Само десно ||
     99|| stock_quantity_on_hand || ✗ || ✓ || Само десно ||
     100|| stock_last_updated || ✗ || ✓ || Само десно ||
     101
     102=== 3.3 Атрибути кои се појавуваат САМО на лева страна ===
     103
     104 * '''saleId''' - мора да биде дел од секој кандидат клуч
     105 * '''poId''' - мора да биде дел од секој кандидат клуч
     106
     107=== 3.4 Пресметка на затворач ===
     108
     109'''Чекор 1:''' Започнуваме со {saleId, poId} и пресметуваме затворач:
     110
     111{{{
     112{saleId, poId}⁺:
     113- Од FD10 (saleId →): добиваме sale_date_time, sale_total_amount, userId, customerId, warehouseId
     114- Од FD11 (poId →): добиваме po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId
     115- Од FD1 (userId →): добиваме user_username, user_password, user_full_name, user_email, user_role, user_is_active
     116- Од FD4 (customerId →): добиваме customer_name, customer_email, customer_phone, customer_address
     117- Од FD8 (supplierId →): добиваме supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address
     118- Од FD9 (warehouseId →): добиваме warehouse_name, warehouse_location, warehouse_capacity
     119}}}
     120
     121'''{saleId, poId}⁺ ≠ U'''
     122
     123'''Недостасуваат:''' productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, category_name, category_description, saleitem_quantity, saleitem_unit_price_at_sale, poitem_quantity, poitem_unit_cost, stock_quantity_on_hand, stock_last_updated
     124
     125'''Чекор 2:''' Додаваме productId:
     126
     127{{{
     128{saleId, poId, productId}⁺:
     129- Сè од претходно, плус:
     130- Од FD5 (productId →): добиваме product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId
     131- Од FD7 (categoryId →): добиваме category_name, category_description
     132- Од FD12 ({saleId, productId} →): добиваме saleitem_quantity, saleitem_unit_price_at_sale
     133- Од FD13 ({poId, productId} →): добиваме poitem_quantity, poitem_unit_cost
     134- Од FD14 ({warehouseId, productId} →): добиваме stock_quantity_on_hand, stock_last_updated
     135}}}
     136
     137'''{saleId, poId, productId}⁺ = U''' (ги содржи сите атрибути) ✓
     138
     139=== 3.5 Проверка за минималност ===
     140
     141||= Подмножество =||= Затворач =||= = U? =||
     142|| {saleId, poId}⁺ || Недостасуваат атрибути поврзани со productId || ✗ НЕ ||
     143|| {saleId, productId}⁺ || Недостасуваат атрибути од poId: po_order_date, po_expected_delivery_date, po_status, poitem_quantity, poitem_unit_cost || ✗ НЕ ||
     144|| {poId, productId}⁺ || Недостасуваат атрибути од saleId: sale_date_time, sale_total_amount, saleitem_quantity, saleitem_unit_price_at_sale, userId→атрибути, customerId→атрибути || ✗ НЕ ||
     145
     146'''Заклучок:''' {saleId, poId, productId} е минимален и е кандидат клуч.
     147
     148=== 3.6 Други кандидат клучеви ===
     149
     150Бидејќи постојат еквиваленции (FD5 и FD6 покажуваат дека productId ↔ product_sku), можеме да го замениме productId со product_sku:
     151
     152'''Кандидат клуч 2:''' {saleId, poId, product_sku}
     153
     154{{{
     155Проверка: {saleId, poId, product_sku}⁺:
     156- Од FD6 (product_sku →): добиваме productId и сите атрибути на продукт
     157- Понатаму идентично како горе
     158
     159{saleId, poId, product_sku}⁺ = U ✓
     160}}}
     161
     162=== 3.7 Избор на примарен клуч ===
     163
     164||= Кандидат клуч =||= Број на атрибути =||= Тип на атрибути =||
     165|| {saleId, poId, productId} || 3 || Сурогат клучеви (стабилни) ||
     166|| {saleId, poId, product_sku} || 3 || Мешано (saleId, poId сурогат; sku природен) ||
     167
     168'''Избран примарен клуч: {saleId, poId, productId}'''
     169
     170'''Образложение:'''
     171 1. Сите три атрибути се сурогат клучеви кои не се менуваат со тек на време
     172 2. productId е понумерички и поефикасен за индексирање од product_sku (кој е VARCHAR)
     173 3. Сурогат клучевите обезбедуваат стабилност - ако се промени SKU кодот на продукт, примарниот клуч останува непроменет
     174
     175----
     176
     177== 4. 1NF Декомпозиција ==
     178
     179=== 4.1 Проверка за 1NF ===
     180
     181Универзалната релација U е во 1NF бидејќи:
     182 * Сите атрибути се атомски (нема повеќевредносни атрибути)
     183 * Постои примарен клуч: {saleId, poId, productId}
     184 * Нема повторувачки групи
     185
     186'''Заклучок:''' U е во 1NF. ✓
     187
     188=== 4.2 Проверка која FD ја нарушува 2NF ===
     189
     190За да биде во 2NF, секој не-клучен атрибут мора целосно да зависи од примарниот клуч (нема парцијални зависности).
     191
     192Примарен клуч: '''{saleId, poId, productId}'''
     193
     194'''Парцијални зависности (атрибути кои зависат од дел од клучот):'''
     195
     196||= FD =||= Детерминант =||= Дел од клуч? =||= Парцијална зависност? =||
     197|| FD1: userId → user_* || userId || НЕ (транзитивно преку saleId) || Не директно ||
     198|| FD5: productId → product_*, categoryId, supplierId || productId || ДА (productId е дел од клучот) || '''ДА''' ||
     199|| FD10: saleId → sale_*, userId, customerId, warehouseId || saleId || ДА (saleId е дел од клучот) || '''ДА''' ||
     200|| FD11: poId → po_*, supplierId, warehouseId || poId || ДА (poId е дел од клучот) || '''ДА''' ||
     201|| FD12: {saleId, productId} → saleitem_* || {saleId, productId} || ДА (подмножество на клучот) || '''ДА''' ||
     202|| FD13: {poId, productId} → poitem_* || {poId, productId} || ДА (подмножество на клучот) || '''ДА''' ||
     203|| FD14: {warehouseId, productId} → stock_* || {warehouseId, productId} || warehouseId не е во клучот директно || Посебен случај* ||
     204
     205''*Забелешка за FD14: warehouseId се добива транзитивно од saleId (FD10) и од poId (FD11), но {warehouseId, productId} не е подмножество на примарниот клуч.''
     206
     207'''Идентификувани парцијални зависности што ја нарушуваат 2NF:'''
     208
     209 1. '''FD5:''' productId → product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId
     210 2. '''FD10:''' saleId → sale_date_time, sale_total_amount, userId, customerId, warehouseId
     211 3. '''FD11:''' poId → po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId
     212 4. '''FD12:''' {saleId, productId} → saleitem_quantity, saleitem_unit_price_at_sale
     213 5. '''FD13:''' {poId, productId} → poitem_quantity, poitem_unit_cost
     214
     215----
     216
     217== 5. 2NF Декомпозиција ==
     218
     219=== Чекор 5.1: Декомпозиција по FD5 (productId → ...) ===
     220
     221'''Нарушува 2NF:''' productId е дел од примарниот клуч, а product_name, product_description, итн. зависат само од productId.
     222
     223'''Декомпозиција:'''
     224
     225{{{
     226R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId)
     227   PK: productId
     228
     229U1 = U - {product_name, product_description, product_sku, product_unit_price, product_reorder_level}
     230}}}
     231
     232'''Проверка за lossless join:'''
     233 * R1 ∩ U1 = {productId, categoryId, supplierId}
     234 * productId → R1 (FD5)
     235 * Декомпозицијата е lossless ✓
     236
     237'''Проверка за зачувување на FD:''' FD5 е зачувана во R1 ✓
     238
     239=== Чекор 5.2: Декомпозиција по FD10 (saleId → ...) ===
     240
     241'''Нарушува 2NF:''' saleId е дел од примарниот клуч на U1.
     242
     243'''Декомпозиција:'''
     244
     245{{{
     246R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId)
     247   PK: saleId
     248
     249U2 = U1 - {sale_date_time, sale_total_amount}
     250}}}
     251
     252'''Проверка за lossless join:'''
     253 * R2 ∩ U2 = {saleId, userId, customerId, warehouseId}
     254 * saleId → R2 (FD10)
     255 * Декомпозицијата е lossless ✓
     256
     257=== Чекор 5.3: Декомпозиција по FD11 (poId → ...) ===
     258
     259'''Нарушува 2NF:''' poId е дел од примарниот клуч на U2.
     260
     261'''Декомпозиција:'''
     262
     263{{{
     264R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId)
     265   PK: poId
     266
     267U3 = U2 - {po_order_date, po_expected_delivery_date, po_status}
     268}}}
     269
     270'''Проверка за lossless join:'''
     271 * R3 ∩ U3 = {poId, supplierId, warehouseId}
     272 * poId → R3 (FD11)
     273 * Декомпозицијата е lossless ✓
     274
     275=== Чекор 5.4: Декомпозиција по FD12 ({saleId, productId} → ...) ===
     276
     277'''Нарушува 2NF:''' {saleId, productId} е подмножество на примарниот клуч.
     278
     279'''Декомпозиција:'''
     280
     281{{{
     282R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale)
     283   PK: {saleId, productId}
     284
     285U4 = U3 - {saleitem_quantity, saleitem_unit_price_at_sale}
     286}}}
     287
     288'''Проверка за lossless join:'''
     289 * R4 ∩ U4 = {saleId, productId}
     290 * {saleId, productId} → R4 (FD12)
     291 * Декомпозицијата е lossless ✓
     292
     293=== Чекор 5.5: Декомпозиција по FD13 ({poId, productId} → ...) ===
     294
     295'''Нарушува 2NF:''' {poId, productId} е подмножество на примарниот клуч.
     296
     297'''Декомпозиција:'''
     298
     299{{{
     300R5(poId, productId, poitem_quantity, poitem_unit_cost)
     301   PK: {poId, productId}
     302
     303U5 = U4 - {poitem_quantity, poitem_unit_cost}
     304}}}
     305
     306'''Проверка за lossless join:'''
     307 * R5 ∩ U5 = {poId, productId}
     308 * {poId, productId} → R5 (FD13)
     309 * Декомпозицијата е lossless ✓
     310
     311=== Состојба после 2NF декомпозиција ===
     312
     313{{{
     314R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId)
     315   PK: productId
     316
     317R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId)
     318   PK: saleId
     319
     320R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId)
     321   PK: poId
     322
     323R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale)
     324   PK: {saleId, productId}
     325
     326R5(poId, productId, poitem_quantity, poitem_unit_cost)
     327   PK: {poId, productId}
     328
     329U5(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active,
     330   customerId, customer_name, customer_email, customer_phone, customer_address,
     331   categoryId, category_name, category_description,
     332   supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address,
     333   warehouseId, warehouse_name, warehouse_location, warehouse_capacity,
     334   stock_quantity_on_hand, stock_last_updated)
     335   PK: {saleId, poId, productId}
     336}}}
     337
     338----
     339
     340== 6. 3NF Декомпозиција ==
     341
     342=== Проверка за транзитивни зависности во секоја релација ===
     343
     344==== R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId) ====
     345
     346'''Транзитивни зависности:'''
     347 * productId → categoryId → category_name, category_description?
     348   * category_name и category_description НЕ се во R1, па нема транзитивна зависност во оваа релација.
     349 * productId → supplierId → supplier_*?
     350   * supplier_* атрибутите НЕ се во R1.
     351
     352'''Заклучок:''' R1 е во 3NF ✓
     353
     354==== R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId) ====
     355
     356'''Транзитивни зависности:'''
     357 * saleId → userId → user_*?
     358   * user_* атрибутите НЕ се во R2.
     359 * saleId → customerId → customer_*?
     360   * customer_* атрибутите НЕ се во R2.
     361 * saleId → warehouseId → warehouse_*?
     362   * warehouse_* атрибутите НЕ се во R2.
     363
     364'''Заклучок:''' R2 е во 3NF ✓
     365
     366==== R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId) ====
     367
     368'''Транзитивни зависности:'''
     369 * Слично како R2, supplier_* и warehouse_* не се во R3.
     370
     371'''Заклучок:''' R3 е во 3NF ✓
     372
     373==== R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale) ====
     374
     375'''Транзитивни зависности:'''
     376 * Нема не-клучни атрибути кои одредуваат други не-клучни атрибути.
     377
     378'''Заклучок:''' R4 е во 3NF ✓
     379
     380==== R5(poId, productId, poitem_quantity, poitem_unit_cost) ====
     381
     382'''Заклучок:''' R5 е во 3NF ✓
     383
     384==== U5 - Проверка за транзитивни зависности ====
     385
     386Примарен клуч на U5: '''{saleId, poId, productId}'''
     387
     388'''Транзитивни зависности во U5:'''
     389
     390 1. '''FD1 транзитивно:''' {saleId, poId, productId} → userId (преку R2/saleId) → user_username, user_password, user_full_name, user_email, user_role, user_is_active
     391    * userId не е клуч на U5, а user_* зависат од userId
     392    * '''НАРУШУВА 3NF'''
     393
     394 2. '''FD4 транзитивно:''' {saleId, poId, productId} → customerId (преку R2/saleId) → customer_name, customer_email, customer_phone, customer_address
     395    * '''НАРУШУВА 3NF'''
     396
     397 3. '''FD7 транзитивно:''' {saleId, poId, productId} → categoryId (преку R1/productId) → category_name, category_description
     398    * '''НАРУШУВА 3NF'''
     399
     400 4. '''FD8 транзитивно:''' {saleId, poId, productId} → supplierId → supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address
     401    * '''НАРУШУВА 3NF'''
     402
     403 5. '''FD9 транзитивно:''' {saleId, poId, productId} → warehouseId → warehouse_name, warehouse_location, warehouse_capacity
     404    * '''НАРУШУВА 3NF'''
     405
     406 6. '''FD14:''' {warehouseId, productId} → stock_quantity_on_hand, stock_last_updated
     407    * warehouseId се добива транзитивно, а {warehouseId, productId} не е клуч на U5
     408    * '''НАРУШУВА 3NF'''
     409
     410=== Чекор 6.1: Декомпозиција по FD1 (userId → user_*) ===
     411
     412{{{
     413R6(userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active)
     414   PK: userId
     415
     416U6 = U5 - {user_username, user_password, user_full_name, user_email, user_role, user_is_active}
     417}}}
     418
     419'''Проверка за lossless join:'''
     420 * R6 ∩ U6 = {userId}
     421 * userId → R6 (FD1)
     422 * Lossless ✓
     423
     424=== Чекор 6.2: Декомпозиција по FD4 (customerId → customer_*) ===
     425
     426{{{
     427R7(customerId, customer_name, customer_email, customer_phone, customer_address)
     428   PK: customerId
     429
     430U7 = U6 - {customer_name, customer_email, customer_phone, customer_address}
     431}}}
     432
     433'''Проверка за lossless join:'''
     434 * R7 ∩ U7 = {customerId}
     435 * customerId → R7 (FD4)
     436 * Lossless ✓
     437
     438=== Чекор 6.3: Декомпозиција по FD7 (categoryId → category_*) ===
     439
     440{{{
     441R8(categoryId, category_name, category_description)
     442   PK: categoryId
     443
     444U8 = U7 - {category_name, category_description}
     445}}}
     446
     447'''Проверка за lossless join:'''
     448 * R8 ∩ U8 = {categoryId}
     449 * categoryId → R8 (FD7)
     450 * Lossless ✓
     451
     452=== Чекор 6.4: Декомпозиција по FD8 (supplierId → supplier_*) ===
     453
     454{{{
     455R9(supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address)
     456   PK: supplierId
     457
     458U9 = U8 - {supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address}
     459}}}
     460
     461'''Проверка за lossless join:'''
     462 * R9 ∩ U9 = {supplierId}
     463 * supplierId → R9 (FD8)
     464 * Lossless ✓
     465
     466=== Чекор 6.5: Декомпозиција по FD9 (warehouseId → warehouse_*) ===
     467
     468{{{
     469R10(warehouseId, warehouse_name, warehouse_location, warehouse_capacity)
     470   PK: warehouseId
     471
     472U10 = U9 - {warehouse_name, warehouse_location, warehouse_capacity}
     473}}}
     474
     475'''Проверка за lossless join:'''
     476 * R10 ∩ U10 = {warehouseId}
     477 * warehouseId → R10 (FD9)
     478 * Lossless ✓
     479
     480=== Чекор 6.6: Декомпозиција по FD14 ({warehouseId, productId} → stock_*) ===
     481
     482{{{
     483R11(warehouseId, productId, stock_quantity_on_hand, stock_last_updated)
     484   PK: {warehouseId, productId}
     485
     486U11 = U10 - {stock_quantity_on_hand, stock_last_updated}
     487}}}
     488
     489'''Проверка за lossless join:'''
     490 * R11 ∩ U11 = {warehouseId, productId}
     491 * {warehouseId, productId} → R11 (FD14)
     492 * Lossless ✓
     493
     494=== Анализа на U11 ===
     495
     496{{{
     497U11(saleId, poId, productId, userId, customerId, categoryId, supplierId, warehouseId)
     498   PK: {saleId, poId, productId}
     499}}}
     500
     501Атрибутите userId, customerId, warehouseId се веќе дел од R2 (Sale).
     502Атрибутите categoryId, supplierId се веќе дел од R1 (Product).
     503
     504U11 претставува врска меѓу продажби (saleId), нарачки (poId) и продукти (productId).
     505
     506'''Прашање:''' Дали оваа релација има семантичка смисла?
     507
     508Во реалноста:
     509 * Една продажба (saleId) е независна од една нарачка (poId)
     510 * Нивната врска е само преку productId и warehouseId
     511
     512'''U11 може да се декомпонира понатаму''', бидејќи атрибутите userId, customerId, warehouseId зависат само од saleId (FD10), а supplierId зависи само од poId или productId.
     513
     514Но бидејќи овие атрибути се веќе издвоени во R2 и R1, U11 станува редундантна и може да се отстрани бидејќи информацијата е веќе зачувана во другите релации.
     515
     516=== Состојба после 3NF декомпозиција ===
     517
     518||= Релација =||= Атрибути =||= Примарен клуч =||
     519|| '''R1''' (Product) || productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId || productId ||
     520|| '''R2''' (Sale) || saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId || saleId ||
     521|| '''R3''' (PurchaseOrder) || poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId || poId ||
     522|| '''R4''' (SaleItem) || saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale || {saleId, productId} ||
     523|| '''R5''' (PurchaseOrderItem) || poId, productId, poitem_quantity, poitem_unit_cost || {poId, productId} ||
     524|| '''R6''' (User) || userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active || userId ||
     525|| '''R7''' (Customer) || customerId, customer_name, customer_email, customer_phone, customer_address || customerId ||
     526|| '''R8''' (Category) || categoryId, category_name, category_description || categoryId ||
     527|| '''R9''' (Supplier) || supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address || supplierId ||
     528|| '''R10''' (Warehouse) || warehouseId, warehouse_name, warehouse_location, warehouse_capacity || warehouseId ||
     529|| '''R11''' (WarehouseStock) || warehouseId, productId, stock_quantity_on_hand, stock_last_updated || {warehouseId, productId} ||
     530
     531----
     532
     533== 7. BCNF Декомпозиција ==
     534
     535=== Проверка за BCNF ===
     536
     537За секоја релација проверуваме: за секоја нетривијална FD X → Y, дали X е суперклуч?
     538
     539==== R1 (Product) ====
     540
     541||= FD =||= Детерминант =||= Суперклуч? =||
     542|| productId → сите || productId || ✓ ДА ||
     543|| product_sku → сите (FD6) || product_sku || ✓ ДА (кандидат клуч) ||
     544
     545'''R1 е во BCNF''' ✓
     546
     547==== R2 (Sale) ====
     548
     549||= FD =||= Детерминант =||= Суперклуч? =||
     550|| saleId → сите || saleId || ✓ ДА ||
     551
     552'''R2 е во BCNF''' ✓
     553
     554==== R3 (PurchaseOrder) ====
     555
     556||= FD =||= Детерминант =||= Суперклуч? =||
     557|| poId → сите || poId || ✓ ДА ||
     558
     559'''R3 е во BCNF''' ✓
     560
     561==== R4 (SaleItem) ====
     562
     563||= FD =||= Детерминант =||= Суперклуч? =||
     564|| {saleId, productId} → сите || {saleId, productId} || ✓ ДА ||
     565
     566'''R4 е во BCNF''' ✓
     567
     568==== R5 (PurchaseOrderItem) ====
     569
     570||= FD =||= Детерминант =||= Суперклуч? =||
     571|| {poId, productId} → сите || {poId, productId} || ✓ ДА ||
     572
     573'''R5 е во BCNF''' ✓
     574
     575==== R6 (User) ====
     576
     577||= FD =||= Детерминант =||= Суперклуч? =||
     578|| userId → сите || userId || ✓ ДА ||
     579|| user_username → сите (FD2) || user_username || ✓ ДА (кандидат клуч) ||
     580|| user_email → сите (FD3) || user_email || ✓ ДА (кандидат клуч) ||
     581
     582'''R6 е во BCNF''' ✓
     583
     584==== R7 (Customer) ====
     585
     586||= FD =||= Детерминант =||= Суперклуч? =||
     587|| customerId → сите || customerId || ✓ ДА ||
     588
     589'''R7 е во BCNF''' ✓
     590
     591==== R8 (Category) ====
     592
     593||= FD =||= Детерминант =||= Суперклуч? =||
     594|| categoryId → сите || categoryId || ✓ ДА ||
     595
     596'''R8 е во BCNF''' ✓
     597
     598==== R9 (Supplier) ====
     599
     600||= FD =||= Детерминант =||= Суперклуч? =||
     601|| supplierId → сите || supplierId || ✓ ДА ||
     602
     603'''R9 е во BCNF''' ✓
     604
     605==== R10 (Warehouse) ====
     606
     607||= FD =||= Детерминант =||= Суперклуч? =||
     608|| warehouseId → сите || warehouseId || ✓ ДА ||
     609
     610'''R10 е во BCNF''' ✓
     611
     612==== R11 (WarehouseStock) ====
     613
     614||= FD =||= Детерминант =||= Суперклуч? =||
     615|| {warehouseId, productId} → сите || {warehouseId, productId} || ✓ ДА ||
     616
     617'''R11 е во BCNF''' ✓
     618
     619'''Заклучок: Сите релации се во BCNF.'''
     620
     621----
     622
     623== 8. Финален резултат и дискусија ==
     624
     625=== 8.1 Финални релации ===
     626
     627{{{
     628User(userId, username, password, full_name, email, role, is_active)
     629  - Примарен клуч: userId
     630  - Кандидат клучеви: userId, username, email
     631  - Надворешни клучеви: -
     632
     633Customer(customerId, name, email, phone, address)
     634  - Примарен клуч: customerId
     635  - Кандидат клучеви: customerId
     636  - Надворешни клучеви: -
     637
     638Category(categoryId, name, description)
     639  - Примарен клуч: categoryId
     640  - Кандидат клучеви: categoryId
     641  - Надворешни клучеви: -
     642
     643Supplier(supplierId, name, contact_person, phone, email, address)
     644  - Примарен клуч: supplierId
     645  - Кандидат клучеви: supplierId
     646  - Надворешни клучеви: -
     647
     648Warehouse(warehouseId, name, location, capacity)
     649  - Примарен клуч: warehouseId
     650  - Кандидат клучеви: warehouseId
     651  - Надворешни клучеви: -
     652
     653Product(productId, name, description, sku, unit_price, reorder_level, categoryId, supplierId)
     654  - Примарен клуч: productId
     655  - Кандидат клучеви: productId, sku
     656  - Надворешни клучеви: categoryId → Category, supplierId → Supplier
     657
     658Sale(saleId, date_time, total_amount, userId, customerId, warehouseId)
     659  - Примарен клуч: saleId
     660  - Кандидат клучеви: saleId
     661  - Надворешни клучеви: userId → User, customerId → Customer, warehouseId → Warehouse
     662
     663PurchaseOrder(poId, order_date, expected_delivery_date, status, supplierId, warehouseId)
     664  - Примарен клуч: poId
     665  - Кандидат клучеви: poId
     666  - Надворешни клучеви: supplierId → Supplier, warehouseId → Warehouse
     667
     668SaleItem(saleId, productId, quantity, unit_price_at_sale)
     669  - Примарен клуч: {saleId, productId}
     670  - Кандидат клучеви: {saleId, productId}
     671  - Надворешни клучеви: saleId → Sale, productId → Product
     672
     673PurchaseOrderItem(poId, productId, quantity, unit_cost)
     674  - Примарен клуч: {poId, productId}
     675  - Кандидат клучеви: {poId, productId}
     676  - Надворешни клучеви: poId → PurchaseOrder, productId → Product
     677
     678WarehouseStock(warehouseId, productId, quantity_on_hand, last_updated)
     679  - Примарен клуч: {warehouseId, productId}
     680  - Кандидат клучеви: {warehouseId, productId}
     681  - Надворешни клучеви: warehouseId → Warehouse, productId → Product
     682}}}
     683
     684=== 8.2 Дискусија на разликите од моделот во Фаза P2 ===
     685
     686Преку процесот на нормализација, тргнувајќи од една универзална релација, дојдовме до '''истите 11 релации''' како во концептуалниот модел од Фаза P2. Ова покажува дека:
     687
     688 1. '''Концептуалниот модел беше правилно дизајниран''' - ентитетите и нивните атрибути беа логички групирани.
     689
     690 2. '''Процесот на нормализација го потврди дизајнот''' - секоја декомпозиција базирана на функционалните зависности резултираше со релација која одговара на ентитет од оригиналниот модел.
     691
     692 3. '''Клучот на универзалната релација {saleId, poId, productId}''' беше идентификуван преку анализа на функционалните зависности и класификација лева/десна страна.
     693
     694 4. '''Сите релации се во BCNF''' - нема функционална зависност каде детерминантот не е суперклуч.
     695
     696 5. '''Декомпозицијата е lossless''' - оригиналната информација може да се реконструира.
     697
     698 6. '''Сите функционални зависности се зачувани''' - секоја FD е зачувана во барем една од финалните релации.
     699
     700=== 8.3 Резиме на кандидат клучеви ===
     701
     702||= Релација =||= Примарен клуч =||= Алтернативни кандидат клучеви =||
     703|| User || userId || username, email ||
     704|| Customer || customerId || - ||
     705|| Category || categoryId || - ||
     706|| Supplier || supplierId || - ||
     707|| Warehouse || warehouseId || - ||
     708|| Product || productId || sku ||
     709|| Sale || saleId || - ||
     710|| PurchaseOrder || poId || - ||
     711|| SaleItem || {saleId, productId} || - ||
     712|| PurchaseOrderItem || {poId, productId} || - ||
     713|| WarehouseStock || {warehouseId, productId} || - ||