= Формална анализа и доказ за нормализација на Shifter = == 1. Дефиниција на универзалната релација == Тргнуваме од една единствена универзална релација која ги содржи сите атрибути од доменот. {{{ Universal_Relation_Shifter(
user_id, user_name, user_email, user_login_provider, user_password_hash, user_verified, user_profile_complete, user_used_free_consultation, user_company_size, user_work_position, user_points, meeting_reminder_id, meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash, course_id, course_image_url, course_color, course_difficulty, course_duration_minutes, course_price, course_version_id, version_number, version_creation_date, version_active, enrollment_id, enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date, payment_id, payment_amount, payment_date, payment_method, payment_status, review_id, review_rating, review_comment, review_date, course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_what_will_be_learned, course_content_id, content_position, course_content_translate_id, content_translate_title, content_translate_language, course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type, course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, user_course_progress_id, progress_completed, progress_completed_at, verification_token_uuid, token_created_at, token_expired_at, tag_id, tag_type, tag_translate_id, tag_translate_language, tag_value
) }}} ---- == 2. Функционални зависности == Ги идентификуваме сите функционални зависности што важат во универзалната релација: '''F = {''' * '''FD1:''' user_id → user_name, user_email, user_login_provider, user_password_hash, user_verified, user_profile_complete, user_used_free_consultation, user_company_size, user_work_position, user_points * '''FD2:''' verification_token_uuid → token_created_at, token_expired_at, user_id * '''FD3:''' meeting_reminder_id → meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id * '''FD4:''' expert_id → expert_name, expert_email, expert_login_provider, expert_password_hash * '''FD5:''' course_id → course_image_url, course_color, course_difficulty, course_duration_minutes, course_price * '''FD6:''' enrollment_id → enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date, user_id, course_version_id, payment_id, review_id * '''FD7:''' payment_id → payment_amount, payment_date, payment_method, payment_status, enrollment_id * '''FD8:''' review_id → review_rating, review_comment, review_date, enrollment_id * '''FD9:''' tag_id → tag_type * '''FD10:''' tag_translate_id → tag_translate_language, tag_value, tag_id * '''FD11:''' course_version_id → version_number, version_creation_date, version_active, course_id * '''FD12:''' course_translate_id → course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id * '''FD13:''' course_content_id → content_position, course_version_id * '''FD14:''' course_content_translate_id → content_translate_title, content_translate_language, course_content_id * '''FD15:''' course_lecture_id → lecture_duration_minutes, lecture_position, lecture_content_type, course_content_id * '''FD16:''' course_lecture_translate_id → lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id * '''FD17:''' user_course_progress_id → progress_completed, progress_completed_at, enrollment_id, course_lecture_id * '''FD18:''' user_email → user_id * '''FD19:''' expert_email → expert_id * '''FD20:''' (course_id, version_number) → course_version_id * '''FD21:''' (course_id, course_translate_language) → course_translate_id * '''FD22:''' (course_lecture_id, lecture_language) → course_lecture_translate_id * '''FD23:''' (course_content_id, content_translate_language) → course_content_translate_id * '''FD24:''' (tag_id, tag_translate_language) → tag_translate_id * '''FD25:''' (enrollment_id, course_lecture_id) → user_course_progress_id * '''FD26:''' (course_version_id, content_position) → course_content_id * '''FD27:''' (course_content_id, lecture_position) → course_lecture_id * '''FD28:''' (course_translate_id, course_what_will_be_learned) → ∅ '''}''' ---- == 3. Кандидат клучеви и примарен клуч == === 3.1 Определување на кандидат клучеви === За да најдеме кандидат клуч, треба да најдеме минимално множество атрибути чиј затворач ги содржи сите атрибути на релацијата. === 3.2 Класификација на атрибути (Лева / Десна страна) === ||= Атрибут =||= Лева страна =||= Десна страна =||= Класификација =|| || user_id || ✓ (FD1) || ✓ (FD2, FD3, FD6, FD18) || и лево и десно || || user_name || ✗ || ✓ (FD1) || десно || || user_email || ✓ (FD18) || ✓ (FD1) || и лево и десно || || user_login_provider || ✗ || ✓ (FD1) || десно || || user_password_hash || ✗ || ✓ (FD1) || десно || || user_verified || ✗ || ✓ (FD1) || десно || || user_profile_complete || ✗ || ✓ (FD1) || десно || || user_used_free_consultation || ✗ || ✓ (FD1) || десно || || user_company_size || ✗ || ✓ (FD1) || десно || || user_work_position || ✗ || ✓ (FD1) || десно || || user_points || ✗ || ✓ (FD1) || десно || || verification_token_uuid || ✓ (FD2) || ✗ || лево || || token_created_at || ✗ || ✓ (FD2) || десно || || token_expired_at || ✗ || ✓ (FD2) || десно || || meeting_reminder_id || ✓ (FD3) || ✗ || лево || || meeting_at || ✗ || ✓ (FD3) || десно || || meeting_scheduled_at || ✗ || ✓ (FD3) || десно || || meeting_sent || ✗ || ✓ (FD3) || десно || || meeting_link || ✗ || ✓ (FD3) || десно || || expert_id || ✓ (FD4) || ✓ (FD19) || и лево и десно || || expert_name || ✗ || ✓ (FD4) || десно || || expert_email || ✓ (FD19) || ✓ (FD4) || и лево и десно || || expert_login_provider || ✗ || ✓ (FD4) || десно || || expert_password_hash || ✗ || ✓ (FD4) || десно || || course_id || ✓ (FD5, FD20, FD21) || ✓ (FD11, FD12) || и лево и десно || || course_image_url || ✗ || ✓ (FD5) || десно || || course_color || ✗ || ✓ (FD5) || десно || || course_difficulty || ✗ || ✓ (FD5) || десно || || course_duration_minutes || ✗ || ✓ (FD5) || десно || || course_price || ✗ || ✓ (FD5) || десно || || enrollment_id || ✓ (FD6, FD25) || ✓ (FD7, FD8, FD17) || и лево и десно || || enrollment_status || ✗ || ✓ (FD6) || десно || || enrollment_purchase_date || ✗ || ✓ (FD6) || десно || || enrollment_activation_date || ✗ || ✓ (FD6) || десно || || enrollment_completion_date || ✗ || ✓ (FD6) || десно || || payment_id || ✓ (FD7) || ✓ (FD6) || и лево и десно || || payment_amount || ✗ || ✓ (FD7) || десно || || payment_date || ✗ || ✓ (FD7) || десно || || payment_method || ✗ || ✓ (FD7) || десно || || payment_status || ✗ || ✓ (FD7) || десно || || review_id || ✓ (FD8) || ✓ (FD6) || и лево и десно || || review_rating || ✗ || ✓ (FD8) || десно || || review_comment || ✗ || ✓ (FD8) || десно || || review_date || ✗ || ✓ (FD8) || десно || || tag_id || ✓ (FD9, FD24) || ✓ (FD10) || и лево и десно || || tag_type || ✗ || ✓ (FD9) || десно || || tag_translate_id || ✓ (FD10) || ✓ (FD24) || и лево и десно || || tag_translate_language || ✓ (FD24) || ✓ (FD10) || и лево и десно || || tag_value || ✗ || ✓ (FD10) || десно || || course_version_id || ✓ (FD11, FD26) || ✓ (FD6, FD13, FD20) || и лево и десно || || version_number || ✓ (FD20) || ✓ (FD11) || и лево и десно || || version_creation_date || ✗ || ✓ (FD11) || десно || || version_active || ✗ || ✓ (FD11) || десно || || course_translate_id || ✓ (FD12) || ✓ (FD21) || и лево и десно || || course_translate_language || ✓ (FD21) || ✓ (FD12) || и лево и десно || || course_title_short || ✗ || ✓ (FD12) || десно || || course_title || ✗ || ✓ (FD12) || десно || || course_description_short || ✗ || ✓ (FD12) || десно || || course_description || ✗ || ✓ (FD12) || десно || || course_description_long || ✗ || ✓ (FD12) || десно || || course_what_will_be_learned || ✓ (FD28) || ✗ || лево || || course_content_id || ✓ (FD13, FD23, FD27) || ✓ (FD14, FD15, FD26) || и лево и десно || || content_position || ✓ (FD26) || ✓ (FD13) || и лево и десно || || course_content_translate_id || ✓ (FD14) || ✓ (FD23) || и лево и десно || || content_translate_title || ✗ || ✓ (FD14) || десно || || content_translate_language || ✓ (FD23) || ✓ (FD14) || и лево и десно || || course_lecture_id || ✓ (FD15, FD22, FD25) || ✓ (FD16, FD17, FD27) || и лево и десно || || lecture_duration_minutes || ✗ || ✓ (FD15) || десно || || lecture_position || ✓ (FD27) || ✓ (FD15) || и лево и десно || || lecture_content_type || ✗ || ✓ (FD15) || десно || || course_lecture_translate_id || ✓ (FD16) || ✓ (FD22) || и лево и десно || || lecture_title || ✗ || ✓ (FD16) || десно || || lecture_language || ✓ (FD22) || ✓ (FD16) || и лево и десно || || lecture_content_file_name || ✗ || ✓ (FD16) || десно || || lecture_description || ✗ || ✓ (FD16) || десно || || lecture_content_text || ✗ || ✓ (FD16) || десно || || user_course_progress_id || ✓ (FD17) || ✓ (FD25) || и лево и десно || || progress_completed || ✗ || ✓ (FD17) || десно || || progress_completed_at || ✗ || ✓ (FD17) || десно || === 3.3 Атрибути кои се појавуваат САМО на лева страна === * '''verification_token_uuid''' - мора да биде дел од секој кандидат клуч * '''meeting_reminder_id''' - мора да биде дел од секој кандидат клуч * '''course_what_will_be_learned''' - мора да биде дел од секој кандидат клуч === 3.4 Пресметка на затворач === '''Чекор 1:''' Започнуваме со {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned} и пресметуваме затворач: {{{ {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned}⁺: - Од FD2 (verification_token_uuid →): добиваме token_created_at, token_expired_at, user_id - Од FD3 (meeting_reminder_id →): добиваме meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id - Од FD1 (user_id →): добиваме user_name, user_email, user_login_provider, user_password_hash, user_verified, user_profile_complete, user_used_free_consultation, user_company_size, user_work_position, user_points - Од FD18 (user_email →): добиваме user_id }}} '''{verification_token_uuid, meeting_reminder_id, course_what_will_be_learned}⁺ ≠ Universal_Relation_Shifter''' '''Недостасуваат:''' expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash, course_id, course_image_url, course_color, course_difficulty, course_duration_minutes, course_price, course_version_id, version_number, version_creation_date, version_active, enrollment_id, enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date, payment_id, payment_amount, payment_date, payment_method, payment_status, review_id, review_rating, review_comment, review_date, course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_content_id, content_position, course_content_translate_id, content_translate_title, content_translate_language, course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type, course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, user_course_progress_id, progress_completed, progress_completed_at, tag_id, tag_type, tag_translate_id, tag_translate_language, tag_value '''Чекор 2:''' Додаваме user_course_progress_id: {{{ {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id}⁺: - Сè од претходно, плус: - Од FD6 (user_course_progress_id →): добиваме progress_completed, progress_completed_at, enrollment_id, course_lecture_id - Од FD15 (course_lecture_id →): добиваме lecture_duration_minutes, lecture_position, lecture_content_type, course_content_id - Од FD13 (course_content_id →): добиваме content_position, course_version_id - Од FD6 (enrollment_id →): добиваме enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date, user_id, course_version_id, payment_id, review_id - Од FD7 (payment_id →): добиваме payment_amount, payment_date, payment_method, payment_status, enrollment_id - Од FD8 (review_id →): добиваме review_rating, review_comment, review_date, enrollment_id - Од FD11 (course_version_id →): добиваме version_number, version_creation_date, version_active, course_id - Од FD5 (course_id →): добиваме course_image_url, course_color, course_difficulty, course_duration_minutes, course_price }}} '''{verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id}⁺ ≠ Universal_Relation_Shifter''' '''Недостасуваат:''' expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash, course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_content_translate_id, content_translate_title, content_translate_language, course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, tag_id, tag_type, tag_translate_id, tag_translate_language, tag_value '''Чекор 3:''' Додаваме expert_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id: {{{ {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id}⁺: - Сè од претходно, плус: - Од FD4 (expert_id →): добиваме expert_name, expert_email, expert_login_provider, expert_password_hash - Од FD16 (course_lecture_translate_id →): добиваме lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id - Од FD14 (course_content_translate_id →): добиваме content_translate_title, content_translate_language, course_content_id - Од FD12 (course_translate_id →): добиваме course_translate_language, course_title_short, course_title, course_description_short, course_description - Од FD10 (tag_translate_id →): добиваме tag_translate_language, tag_value, tag_id - Од FD9 (tag_id →): добиваме tag_type - Од FD28 ({course_translate_id, course_what_will_be_learned} →): добиваме ∅ }}} '''{verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id}⁺ = Universal_Relation_Shifter''' (ги содржи сите атрибути) ✓ === 3.5 Проверка за минималност === ||= Подмножество =||= Затворач =||= = U? =|| || {saleId, poId}⁺ || Недостасуваат атрибути поврзани со productId || ✗ НЕ || || {saleId, productId}⁺ || Недостасуваат атрибути од poId: po_order_date, po_expected_delivery_date, po_status, poitem_quantity, poitem_unit_cost || ✗ НЕ || || {poId, productId}⁺ || Недостасуваат атрибути од saleId: sale_date_time, sale_total_amount, saleitem_quantity, saleitem_unit_price_at_sale, userId→атрибути, customerId→атрибути || ✗ НЕ || '''Заклучок:''' {verification_token_uuid, meeting_reminder_id, user_course_progress_id, expert_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id} е минимален и е кандидат клуч. === 3.6 Други кандидат клучеви === Бидејќи постојат еквиваленции (FD5 и FD6 покажуваат дека productId ↔ product_sku), можеме да го замениме productId со product_sku: '''Кандидат клуч 2:''' {saleId, poId, product_sku} {{{ Проверка: {saleId, poId, product_sku}⁺: - Од FD6 (product_sku →): добиваме productId и сите атрибути на продукт - Понатаму идентично како горе {saleId, poId, product_sku}⁺ = U ✓ }}} === 3.7 Избор на примарен клуч === ||= Кандидат клуч =||= Број на атрибути =||= Тип на атрибути =|| || {saleId, poId, productId} || 3 || Сурогат клучеви (стабилни) || || {saleId, poId, product_sku} || 3 || Мешано (saleId, poId сурогат; sku природен) || '''Избран примарен клуч: {saleId, poId, productId}''' '''Образложение:''' 1. Сите три атрибути се сурогат клучеви кои не се менуваат со тек на време 2. productId е понумерички и поефикасен за индексирање од product_sku (кој е VARCHAR) 3. Сурогат клучевите обезбедуваат стабилност - ако се промени SKU кодот на продукт, примарниот клуч останува непроменет ---- == 4. 1NF Декомпозиција == === 4.1 Проверка за 1NF === Универзалната релација U е во 1NF бидејќи: * Сите атрибути се атомски (нема повеќевредносни атрибути) * Постои примарен клуч: {saleId, poId, productId} * Нема повторувачки групи '''Заклучок:''' U е во 1NF. ✓ === 4.2 Проверка која FD ја нарушува 2NF === За да биде во 2NF, секој не-клучен атрибут мора целосно да зависи од примарниот клуч (нема парцијални зависности). Примарен клуч: '''{saleId, poId, productId}''' '''Парцијални зависности (атрибути кои зависат од дел од клучот):''' ||= FD =||= Детерминант =||= Дел од клуч? =||= Парцијална зависност? =|| || FD1: userId → user_* || userId || НЕ (транзитивно преку saleId) || Не директно || || FD5: productId → product_*, categoryId, supplierId || productId || ДА (productId е дел од клучот) || '''ДА''' || || FD10: saleId → sale_*, userId, customerId, warehouseId || saleId || ДА (saleId е дел од клучот) || '''ДА''' || || FD11: poId → po_*, supplierId, warehouseId || poId || ДА (poId е дел од клучот) || '''ДА''' || || FD12: {saleId, productId} → saleitem_* || {saleId, productId} || ДА (подмножество на клучот) || '''ДА''' || || FD13: {poId, productId} → poitem_* || {poId, productId} || ДА (подмножество на клучот) || '''ДА''' || || FD14: {warehouseId, productId} → stock_* || {warehouseId, productId} || warehouseId не е во клучот директно || Посебен случај* || *Забелешка за FD14: warehouseId се добива транзитивно од saleId (FD10) и од poId (FD11), но {warehouseId, productId} не е подмножество на примарниот клуч. '''Идентификувани парцијални зависности што ја нарушуваат 2NF:''' 1. '''FD5:''' productId → product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId 2. '''FD10:''' saleId → sale_date_time, sale_total_amount, userId, customerId, warehouseId 3. '''FD11:''' poId → po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId 4. '''FD12:''' {saleId, productId} → saleitem_quantity, saleitem_unit_price_at_sale 5. '''FD13:''' {poId, productId} → poitem_quantity, poitem_unit_cost ---- == 5. 2NF Декомпозиција == === Чекор 5.1: Декомпозиција по FD5 (productId → ...) === '''Нарушува 2NF:''' productId е дел од примарниот клуч, а product_name, product_description, итн. зависат само од productId. '''Декомпозиција:''' {{{ R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId) PK: productId U1 = U - {product_name, product_description, product_sku, product_unit_price, product_reorder_level} U1(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, sale_date_time, sale_total_amount, po_order_date, po_expected_delivery_date, po_status, saleitem_quantity, saleitem_unit_price_at_sale, poitem_quantity, poitem_unit_cost, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R1 ∩ U1 = {productId, categoryId, supplierId} * productId → R1 (FD5) * Декомпозицијата е lossless ✓ '''Проверка за зачувување на FD:''' FD5 е зачувана во R1 ✓ === Чекор 5.2: Декомпозиција по FD10 (saleId → ...) === '''Нарушува 2NF:''' saleId е дел од примарниот клуч на U1. '''Декомпозиција:''' {{{ R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId) PK: saleId U2 = U1 - {sale_date_time, sale_total_amount} U2(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, po_order_date, po_expected_delivery_date, po_status, saleitem_quantity, saleitem_unit_price_at_sale, poitem_quantity, poitem_unit_cost, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R2 ∩ U2 = {saleId, userId, customerId, warehouseId} * saleId → R2 (FD10) * Декомпозицијата е lossless ✓ === Чекор 5.3: Декомпозиција по FD11 (poId → ...) === '''Нарушува 2NF:''' poId е дел од примарниот клуч на U2. '''Декомпозиција:''' {{{ R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId) PK: poId U3 = U2 - {po_order_date, po_expected_delivery_date, po_status} U3(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, saleitem_quantity, saleitem_unit_price_at_sale, poitem_quantity, poitem_unit_cost, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R3 ∩ U3 = {poId, supplierId, warehouseId} * poId → R3 (FD11) * Декомпозицијата е lossless ✓ === Чекор 5.4: Декомпозиција по FD12 ({saleId, productId} → ...) === '''Нарушува 2NF:''' {saleId, productId} е подмножество на примарниот клуч. '''Декомпозиција:''' {{{ R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale) PK: {saleId, productId} U4 = U3 - {saleitem_quantity, saleitem_unit_price_at_sale} U4(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, poitem_quantity, poitem_unit_cost, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R4 ∩ U4 = {saleId, productId} * {saleId, productId} → R4 (FD12) * Декомпозицијата е lossless ✓ === Чекор 5.5: Декомпозиција по FD13 ({poId, productId} → ...) === '''Нарушува 2NF:''' {poId, productId} е подмножество на примарниот клуч. '''Декомпозиција:''' {{{ R5(poId, productId, poitem_quantity, poitem_unit_cost) PK: {poId, productId} U5 = U4 - {poitem_quantity, poitem_unit_cost} U5(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R5 ∩ U5 = {poId, productId} * {poId, productId} → R5 (FD13) * Декомпозицијата е lossless ✓ === Состојба после 2NF декомпозиција === {{{ R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId) PK: productId R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId) PK: saleId R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId) PK: poId R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale) PK: {saleId, productId} R5(poId, productId, poitem_quantity, poitem_unit_cost) PK: {poId, productId} U5(saleId, poId, productId, userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) PK: {saleId, poId, productId} }}} ---- == 6. 3NF Декомпозиција == === Проверка за транзитивни зависности во секоја релација === ==== R1(productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId) ==== '''Транзитивни зависности:''' * productId → categoryId → category_name, category_description? * category_name и category_description НЕ се во R1, па нема транзитивна зависност во оваа релација. * productId → supplierId → supplier_*? * supplier_* атрибутите НЕ се во R1. '''Заклучок:''' R1 е во 3NF ✓ ==== R2(saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId) ==== '''Транзитивни зависности:''' * saleId → userId → user_*? * user_* атрибутите НЕ се во R2. * saleId → customerId → customer_*? * customer_* атрибутите НЕ се во R2. * saleId → warehouseId → warehouse_*? * warehouse_* атрибутите НЕ се во R2. '''Заклучок:''' R2 е во 3NF ✓ ==== R3(poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId) ==== '''Транзитивни зависности:''' * Слично како R2, supplier_* и warehouse_* не се во R3. '''Заклучок:''' R3 е во 3NF ✓ ==== R4(saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale) ==== '''Транзитивни зависности:''' * Нема не-клучни атрибути кои одредуваат други не-клучни атрибути. '''Заклучок:''' R4 е во 3NF ✓ ==== R5(poId, productId, poitem_quantity, poitem_unit_cost) ==== '''Заклучок:''' R5 е во 3NF ✓ ==== U5 - Проверка за транзитивни зависности ==== Примарен клуч на U5: '''{saleId, poId, productId}''' '''Транзитивни зависности во U5:''' 1. '''FD1 транзитивно:''' {saleId, poId, productId} → userId (преку R2/saleId) → user_username, user_password, user_full_name, user_email, user_role, user_is_active * userId не е клуч на U5, а user_* зависат од userId * '''НАРУШУВА 3NF''' 2. '''FD4 транзитивно:''' {saleId, poId, productId} → customerId (преку R2/saleId) → customer_name, customer_email, customer_phone, customer_address * '''НАРУШУВА 3NF''' 3. '''FD7 транзитивно:''' {saleId, poId, productId} → categoryId (преку R1/productId) → category_name, category_description * '''НАРУШУВА 3NF''' 4. '''FD8 транзитивно:''' {saleId, poId, productId} → supplierId → supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address * '''НАРУШУВА 3NF''' 5. '''FD9 транзитивно:''' {saleId, poId, productId} → warehouseId → warehouse_name, warehouse_location, warehouse_capacity * '''НАРУШУВА 3NF''' 6. '''FD14:''' {warehouseId, productId} → stock_quantity_on_hand, stock_last_updated * warehouseId се добива транзитивно, а {warehouseId, productId} не е клуч на U5 * '''НАРУШУВА 3NF''' === Чекор 6.1: Декомпозиција по FD1 (userId → user_*) === {{{ R6(userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active) PK: userId U6 = U5 - {user_username, user_password, user_full_name, user_email, user_role, user_is_active} U6(saleId, poId, productId, userId, customerId, customer_name, customer_email, customer_phone, customer_address, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R6 ∩ U6 = {userId} * userId → R6 (FD1) * Lossless ✓ === Чекор 6.2: Декомпозиција по FD4 (customerId → customer_*) === {{{ R7(customerId, customer_name, customer_email, customer_phone, customer_address) PK: customerId U7 = U6 - {customer_name, customer_email, customer_phone, customer_address} U7(saleId, poId, productId, userId, customerId, categoryId, category_name, category_description, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R7 ∩ U7 = {customerId} * customerId → R7 (FD4) * Lossless ✓ === Чекор 6.3: Декомпозиција по FD7 (categoryId → category_*) === {{{ R8(categoryId, category_name, category_description) PK: categoryId U8 = U7 - {category_name, category_description} U8(saleId, poId, productId, userId, customerId, categoryId, supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R8 ∩ U8 = {categoryId} * categoryId → R8 (FD7) * Lossless ✓ === Чекор 6.4: Декомпозиција по FD8 (supplierId → supplier_*) === {{{ R9(supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address) PK: supplierId U9 = U8 - {supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address} U9(saleId, poId, productId, userId, customerId, categoryId, supplierId, warehouseId, warehouse_name, warehouse_location, warehouse_capacity, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R9 ∩ U9 = {supplierId} * supplierId → R9 (FD8) * Lossless ✓ === Чекор 6.5: Декомпозиција по FD9 (warehouseId → warehouse_*) === {{{ R10(warehouseId, warehouse_name, warehouse_location, warehouse_capacity) PK: warehouseId U10 = U9 - {warehouse_name, warehouse_location, warehouse_capacity} U10(saleId, poId, productId, userId, customerId, categoryId, supplierId, warehouseId, stock_quantity_on_hand, stock_last_updated) }}} '''Проверка за lossless join:''' * R10 ∩ U10 = {warehouseId} * warehouseId → R10 (FD9) * Lossless ✓ === Чекор 6.6: Декомпозиција по FD14 ({warehouseId, productId} → stock_*) === {{{ R11(warehouseId, productId, stock_quantity_on_hand, stock_last_updated) PK: {warehouseId, productId} U11 = U10 - {stock_quantity_on_hand, stock_last_updated} U11(saleId, poId, productId, userId, customerId, categoryId, supplierId, warehouseId) }}} '''Проверка за lossless join:''' * R11 ∩ U11 = {warehouseId, productId} * {warehouseId, productId} → R11 (FD14) * Lossless ✓ === Анализа на U11 === {{{ U11(saleId, poId, productId, userId, customerId, categoryId, supplierId, warehouseId) PK: {saleId, poId, productId} }}} Атрибутите userId, customerId, warehouseId се веќе дел од R2 (Sale). Атрибутите categoryId, supplierId се веќе дел од R1 (Product). U11 претставува врска меѓу продажби (saleId), нарачки (poId) и продукти (productId). '''Прашање:''' Дали оваа релација има семантичка смисла? Во реалноста: * Една продажба (saleId) е независна од една нарачка (poId) * Нивната врска е само преку productId и warehouseId '''U11 може да се декомпонира понатаму''', бидејќи атрибутите userId, customerId, warehouseId зависат само од saleId (FD10), а supplierId зависи само од poId или productId. Но бидејќи овие атрибути се веќе издвоени во R2 и R1, U11 станува редундантна и може да се отстрани бидејќи информацијата е веќе зачувана во другите релации. === Состојба после 3NF декомпозиција === ||= Релација =||= Атрибути =||= Примарен клуч =|| || '''R1''' (Product) || productId, product_name, product_description, product_sku, product_unit_price, product_reorder_level, categoryId, supplierId || productId || || '''R2''' (Sale) || saleId, sale_date_time, sale_total_amount, userId, customerId, warehouseId || saleId || || '''R3''' (!PurchaseOrder) || poId, po_order_date, po_expected_delivery_date, po_status, supplierId, warehouseId || poId || || '''R4''' (!SaleItem) || saleId, productId, saleitem_quantity, saleitem_unit_price_at_sale || {saleId, productId} || || '''R5''' (!PurchaseOrderItem) || poId, productId, poitem_quantity, poitem_unit_cost || {poId, productId} || || '''R6''' (User) || userId, user_username, user_password, user_full_name, user_email, user_role, user_is_active || userId || || '''R7''' (Customer) || customerId, customer_name, customer_email, customer_phone, customer_address || customerId || || '''R8''' (Category) || categoryId, category_name, category_description || categoryId || || '''R9''' (Supplier) || supplierId, supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address || supplierId || || '''R10''' (Warehouse) || warehouseId, warehouse_name, warehouse_location, warehouse_capacity || warehouseId || || '''R11''' (!WarehouseStock) || warehouseId, productId, stock_quantity_on_hand, stock_last_updated || {warehouseId, productId} || ---- == 7. BCNF Декомпозиција == === Проверка за BCNF === За секоја релација проверуваме: за секоја нетривијална FD X → Y, дали X е суперклуч? ==== R1 (Product) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || productId → сите || productId || ✓ ДА || || product_sku → сите (FD6) || product_sku || ✓ ДА (кандидат клуч) || '''R1 е во BCNF''' ✓ ==== R2 (Sale) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || saleId → сите || saleId || ✓ ДА || '''R2 е во BCNF''' ✓ ==== R3 (!PurchaseOrder) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || poId → сите || poId || ✓ ДА || '''R3 е во BCNF''' ✓ ==== R4 (!SaleItem) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || {saleId, productId} → сите || {saleId, productId} || ✓ ДА || '''R4 е во BCNF''' ✓ ==== R5 (!PurchaseOrderItem) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || {poId, productId} → сите || {poId, productId} || ✓ ДА || '''R5 е во BCNF''' ✓ ==== R6 (User) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || userId → сите || userId || ✓ ДА || || user_username → сите (FD2) || user_username || ✓ ДА (кандидат клуч) || || user_email → сите (FD3) || user_email || ✓ ДА (кандидат клуч) || '''R6 е во BCNF''' ✓ ==== R7 (Customer) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || customerId → сите || customerId || ✓ ДА || '''R7 е во BCNF''' ✓ ==== R8 (Category) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || categoryId → сите || categoryId || ✓ ДА || '''R8 е во BCNF''' ✓ ==== R9 (Supplier) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || supplierId → сите || supplierId || ✓ ДА || '''R9 е во BCNF''' ✓ ==== R10 (Warehouse) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || warehouseId → сите || warehouseId || ✓ ДА || '''R10 е во BCNF''' ✓ ==== R11 (!WarehouseStock) ==== ||= FD =||= Детерминант =||= Суперклуч? =|| || {warehouseId, productId} → сите || {warehouseId, productId} || ✓ ДА || '''R11 е во BCNF''' ✓ '''Заклучок: Сите релации се во BCNF.''' ---- == 8. Финален резултат и дискусија == === 8.1 Финални релации === {{{ User(userId, username, password, full_name, email, role, is_active) - Примарен клуч: userId - Кандидат клучеви: userId, username, email - Надворешни клучеви: - Customer(customerId, name, email, phone, address) - Примарен клуч: customerId - Кандидат клучеви: customerId - Надворешни клучеви: - Category(categoryId, name, description) - Примарен клуч: categoryId - Кандидат клучеви: categoryId - Надворешни клучеви: - Supplier(supplierId, name, contact_person, phone, email, address) - Примарен клуч: supplierId - Кандидат клучеви: supplierId - Надворешни клучеви: - Warehouse(warehouseId, name, location, capacity) - Примарен клуч: warehouseId - Кандидат клучеви: warehouseId - Надворешни клучеви: - Product(productId, name, description, sku, unit_price, reorder_level, categoryId, supplierId) - Примарен клуч: productId - Кандидат клучеви: productId, sku - Надворешни клучеви: categoryId → Category, supplierId → Supplier Sale(saleId, date_time, total_amount, userId, customerId, warehouseId) - Примарен клуч: saleId - Кандидат клучеви: saleId - Надворешни клучеви: userId → User, customerId → Customer, warehouseId → Warehouse PurchaseOrder(poId, order_date, expected_delivery_date, status, supplierId, warehouseId) - Примарен клуч: poId - Кандидат клучеви: poId - Надворешни клучеви: supplierId → Supplier, warehouseId → Warehouse SaleItem(saleId, productId, quantity, unit_price_at_sale) - Примарен клуч: {saleId, productId} - Кандидат клучеви: {saleId, productId} - Надворешни клучеви: saleId → Sale, productId → Product PurchaseOrderItem(poId, productId, quantity, unit_cost) - Примарен клуч: {poId, productId} - Кандидат клучеви: {poId, productId} - Надворешни клучеви: poId → PurchaseOrder, productId → Product WarehouseStock(warehouseId, productId, quantity_on_hand, last_updated) - Примарен клуч: {warehouseId, productId} - Кандидат клучеви: {warehouseId, productId} - Надворешни клучеви: warehouseId → Warehouse, productId → Product }}} === 8.2 Дискусија на разликите од моделот во Фаза P2 === Преку процесот на нормализација, тргнувајќи од една универзална релација, дојдовме до '''истите 11 релации''' како во концептуалниот модел од Фаза P2. Ова покажува дека: 1. '''Концептуалниот модел беше правилно дизајниран''' - ентитетите и нивните атрибути беа логички групирани. 2. '''Процесот на нормализација го потврди дизајнот''' - секоја декомпозиција базирана на функционалните зависности резултираше со релација која одговара на ентитет од оригиналниот модел. 3. '''Клучот на универзалната релација {saleId, poId, productId}''' беше идентификуван преку анализа на функционалните зависности и класификација лева/десна страна. 4. '''Сите релации се во BCNF''' - нема функционална зависност каде детерминантот не е суперклуч. 5. '''Декомпозицијата е lossless''' - оригиналната информација може да се реконструира. 6. '''Сите функционални зависности се зачувани''' - секоја FD е зачувана во барем една од финалните релации.