= Формална анализа и доказ за нормализација на 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 Проверка за минималност === ||= Подмножество =||= Затворач =||= = Universal_Relation_Shifter? =|| || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned}⁺ || Недостасуваат атрибути од user_course_progress || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress}⁺ || Недостасуваат атрибути од expert || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id}⁺ || Недостасуваат атрибути од course_translate || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_translate_id}⁺ || Недостасуваат атрибути од course_lecture_translate || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_translate_id, course_lecture_translate_id}⁺ || Недостасуваат атрибути од course_content_translate || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_translate_id, course_lecture_translate_id, course_content_translate_id}⁺ || Недостасуваат атрибути од tag_translate || ✗ НЕ || || {verification_token_uuid, meeting_reminder_id, course_what_will_be_learned, user_course_progress_id, expert_id, course_translate_id, course_lecture_translate_id, course_content_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} е минимален и е кандидат клуч. === 3.6 Други кандидат клучеви === Нема === 3.7 Избор на примарен клуч === '''Избран примарен клуч: {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}''' '''Образложение:''' Ова е примарен клуч (минимален кандидат-клуч) бидејќи само со овие атрибути може да се добијат сите останати атрибути од универзалната релација преку затворање на функционалните зависности. Причината за големиот број атрибути во примарниот клуч е што универзалната релација содржи повеќе независни ентитети (корисници, експерти, курсеви, оцени, преводи, тагови) кои не се директно поврзани преку функционални зависности. Секој од овие независни ентитети бара најмалку еден идентификатор во клучот за да може да се пристапи до сите негови атрибути. Овој клуч е минимален - отстранувањето на било кој атрибут од него би довело до тоа да не може да се изведат некои од атрибутите во релацијата. ---- == 4. 1NF Декомпозиција == === Чекор 4.1: Декомпозиција на релацијата Universal_Relation_Shifter === '''Нарушува 1NF:'''Релацијата не е во 1NF бидејќи атрибутот course_what_will_be_learned претставува повеќевредносен (multi-valued) атрибут. Секој course_translate може да има повеќе ставки од course_what_will_be_learned. '''Декомпозиција:''' {{{ Course_What_Will_Be_Learned(course_translate_id, course_what_will_be_learned) PK: {course_translate_id, course_what_will_be_learned} Universal_Relation_Shifter_Base = Universal_Relation_Shifter - {course_what_will_be_learned} PK: {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} '''Проверка за lossless join:''' Може да се реконструира преку course_translate_id '''Проверка за dependency preservation:''' ✓ Сите FD28 е зачувана во Course_What_Will_Be_Learned === 4.2 Проверка која FD ја нарушува 2NF === За да биде во 2NF, секој не-клучен атрибут мора целосно да зависи од примарниот клуч (нема парцијални зависности). Примарен клуч во Universal_Relation_Shifter_Base: '''{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}''' '''Парцијални зависности (атрибути кои зависат од дел од клучот):''' ||= FD =||= Детерминант =||= Дел од клуч? =||= Парцијална зависност? =|| || FD1: user_id → user_* || user_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD2: verification_token_uuid → token_created_at, token_expired_at, user_id || verification_token_uuid || ДА (дел од примарен клуч) || '''ДА''' || || FD3: meeting_reminder_id → meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id || meeting_reminder_id || ДА (дел од примарен клуч) || '''ДА''' || || FD4: expert_id → expert_name, expert_email, expert_login_provider, expert_password_hash || expert_id || ДА (дел од примарен клуч) || '''ДА''' || || FD5: course_id → course_image_url, course_color, course_difficulty, course_duration_minutes, course_price || course_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD6: enrollment_id → enrollment_*, user_id, course_version_id, payment_id, review_id || enrollment_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD7: payment_id → payment_amount, payment_date, payment_method, payment_status, enrollment_id || payment_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD8: review_id → review_rating, review_comment, review_date, enrollment_id || review_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD9: tag_id → tag_type || tag_id || НЕ (транзитивно преку tag_translate_id) || Не директно || || FD10: tag_translate_id → tag_translate_language, tag_value, tag_id || tag_translate_id || ДА (дел од примарен клуч) || '''ДА''' || || FD11: course_version_id → version_number, version_creation_date, version_active, course_id || course_version_id || НЕ (транзитивно преку user_course_progress_id) || Не директно || || FD12: course_translate_id → course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id || course_translate_id || ДА (дел од примарен клуч) || '''ДА''' || || FD13: course_content_id → content_position, course_version_id || course_content_id || НЕ (транзитивно преку course_content_translate_id) || Не директно || || FD14: course_content_translate_id → content_translate_title, content_translate_language, course_content_id || course_content_translate_id || ДА (дел од примарен клуч) || '''ДА''' || || FD15: course_lecture_id → lecture_duration_minutes, lecture_position, lecture_content_type, course_content_id || course_lecture_id || НЕ (транзитивно преку course_lecture_translate_id) || Не директно || || FD16: course_lecture_translate_id → lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id || course_lecture_translate_id || ДА (дел од примарен клуч) || '''ДА''' || || FD17: user_course_progress_id → progress_completed, progress_completed_at, enrollment_id, course_lecture_id || user_course_progress_id || ДА (дел од примарен клуч) || '''ДА''' || || FD18: user_email → user_id || user_email || НЕ || Не || || FD19: expert_email → expert_id || expert_email || НЕ || Не || || FD20: (course_id, version_number) → course_version_id || course_id, version_number || НЕ || Не || || FD21: (course_id, course_translate_language) → course_translate_id || course_id, course_translate_language || НЕ || Не || || FD22: (course_lecture_id, lecture_language) → course_lecture_translate_id || course_lecture_id, lecture_language || НЕ || Не || || FD23: (course_content_id, content_translate_language) → course_content_translate_id || course_content_id, content_translate_language || НЕ || Не || || FD24: (tag_id, tag_translate_language) → tag_translate_id || tag_id, tag_translate_language || НЕ || Не || || FD25: (enrollment_id, course_lecture_id) → user_course_progress_id || enrollment_id, course_lecture_id || НЕ || Не || || FD26: (course_version_id, content_position) → course_content_id || course_version_id, content_position || НЕ || Не || || FD27: (course_content_id, lecture_position) → course_lecture_id || course_content_id, lecture_position || НЕ || Не || '''Заклучок:''' Релацијата не е во 2NF бидејќи има парцијални зависности. Атрибутите на '''FD2, FD3, FD4, FD10, FD12, FD14, FD16, FD17''' зависат само од дел од примарниот клуч, а не од целиот клуч. '''Идентификувани парцијални зависности што ја нарушуваат 2NF:''' 1. '''FD2:''' verification_token_uuid → token_created_at, token_expired_at, user_id 2. '''FD3:''' meeting_reminder_id → meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id 3. '''FD4:''' expert_id → expert_name, expert_email, expert_login_provider, expert_password_hash 4. '''FD10:''' tag_translate_id → tag_translate_language, tag_value, tag_id 5. '''FD12:''' course_translate_id → course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id 6. '''FD14:''' course_content_translate_id → content_translate_title, content_translate_language, course_content_id 7. '''FD16:''' course_lecture_translate_id → lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id ---- == 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 е зачувана во барем една од финалните релации.