= Формална анализа и доказ за нормализација на 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}

Universal_Relation_Shifter_Base(
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_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
)
   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
 8. '''FD17:''' user_course_progress_id → progress_completed, progress_completed_at, enrollment_id, course_lecture_id


----

== 5. 2NF Декомпозиција ==

=== Чекор 5.1: Декомпозиција по FD2 (verification_token_uuid → ...) ===

'''Нарушува 2NF:''' verification_token_uuid е дел од примарниот клуч, а token_created_at, token_expired_at зависат само од verification_token_uuid.

'''Декомпозиција:'''

{{{
Verification_Token(verification_token_uuid, token_created_at, token_expired_at, user_id)
   PK: verification_token_uuid

Universal_Relation_Shifter_Base_1 = Universal_Relation_Shifter_Base - {verification_token_uuid, token_created_at, token_expired_at}

Universal_Relation_Shifter_Base_1(
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_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
)
   PK: {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:''' Може да се реконструира преку user_id
'''Проверка за dependency preservation:''' FD2 е зачувана во Verification_Token

=== Чекор 5.2: Декомпозиција по FD3 (meeting_reminder_id → ...) ===

'''Нарушува 2NF:''' meeting_reminder_id е дел од примарниот клуч, а meeting_at, meeting_scheduled_at, meeting_sent, meeting_link зависат само од meeting_reminder_id.

'''Декомпозиција:'''

{{{
Meeting_Reminder(meeting_reminder_id, meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id)
   PK: meeting_reminder_id

Universal_Relation_Shifter_Base_2 = Universal_Relation_Shifter_Base_1 - {meeting_reminder_id, meeting_at, meeting_scheduled_at, meeting_sent, meeting_link}

Universal_Relation_Shifter_Base_2(
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,
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
)
   PK: {user_course_progress_id, expert_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку user_id
'''Проверка за dependency preservation:''' FD3 е зачувана во Meeting_Reminder

=== Чекор 5.3: Декомпозиција по FD4 (expert_id → ...) ===

'''Нарушува 2NF:''' expert_id е дел од примарниот клуч, а expert_name, expert_email, expert_login_provider, expert_password_hash зависат само од expert_id.

'''Декомпозиција:'''

{{{
Expert(expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash)
   PK: expert_id

Universal_Relation_Shifter_Base_3 = Universal_Relation_Shifter_Base_2 - {expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash}

Universal_Relation_Shifter_Base_3(
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,
expert_id
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
)
   PK: {user_course_progress_id, course_lecture_translate_id, course_content_translate_id, course_translate_id, tag_translate_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку expert_id
'''Проверка за dependency preservation:''' FD4 е зачувана во Expert

=== Чекор 5.4: Декомпозиција по FD10 (tag_translate_id → ...) ===

'''Нарушува 2NF:''' tag_translate_id е дел од примарниот клуч, а tag_translate_language, tag_value зависат само од tag_translate_id.

'''Декомпозиција:'''

{{{
Tag_Translate(tag_translate_id, tag_translate_language, tag_value, tag_id)
   PK: tag_translate_id

Universal_Relation_Shifter_Base_4 = Universal_Relation_Shifter_Base_3 - {tag_translate_id, tag_translate_language, tag_value}

Universal_Relation_Shifter_Base_4(
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,
expert_id,
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
)
   PK: {user_course_progress_id, course_lecture_translate_id, course_content_translate_id, course_translate_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку tag_id
'''Проверка за dependency preservation:''' FD10 е зачувана во Tag_Translate

=== Чекор 5.5: Декомпозиција по FD12 (course_translate_id → ...) ===

'''Нарушува 2NF:''' course_translate_id е дел од примарниот клуч, а course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long зависат само од course_translate_id.

'''Декомпозиција:'''

{{{
Course_Translate(course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id)
   PK: course_translate_id

Universal_Relation_Shifter_Base_5 = Universal_Relation_Shifter_Base_4 - {course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long}

Universal_Relation_Shifter_Base_5(
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,
expert_id,
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_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
)
   PK: {user_course_progress_id, course_lecture_translate_id, course_content_translate_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку course_id
'''Проверка за dependency preservation:''' FD12 е зачувана во Course_Translate

=== Чекор 5.6: Декомпозиција по FD14 (course_content_translate_id → ...) ===

'''Нарушува 2NF:''' course_content_translate_id е дел од примарниот клуч, а content_translate_title, content_translate_language зависат само од course_content_translate_id.

'''Декомпозиција:'''

{{{
Course_Content_Translate(course_content_translate_id, content_translate_title, content_translate_language, course_content_id)
   PK: course_content_translate_id

Universal_Relation_Shifter_Base_6 = Universal_Relation_Shifter_Base_5 - {course_content_translate_id, content_translate_title, content_translate_language}

Universal_Relation_Shifter_Base_6(
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,
expert_id,
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_content_id, content_position,
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
)
   PK: {user_course_progress_id, course_lecture_translate_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку course_content_id
'''Проверка за dependency preservation:''' FD14 е зачувана во Course_Content_Translate

=== Чекор 5.7: Декомпозиција по FD16 (course_lecture_translate_id → ...) ===

'''Нарушува 2NF:''' course_lecture_translate_id е дел од примарниот клуч, а lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text зависат само од course_lecture_translate_id.

'''Декомпозиција:'''

{{{
Course_Lecture_Translate(course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id)
   PK: course_lecture_translate_id

Universal_Relation_Shifter_Base_7 = Universal_Relation_Shifter_Base_6 - {course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text}

Universal_Relation_Shifter_Base_7(
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,
expert_id,
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_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
user_course_progress_id, progress_completed, progress_completed_at,
tag_id, tag_type
)
   PK: {user_course_progress_id}
}}}

'''Проверка за lossless join:''' Може да се реконструира преку course_lecture_id
'''Проверка за dependency preservation:''' FD16 е зачувана во Course_Lecture_Translate

=== Чекор 5.8: Декомпозиција по FD17 (user_course_progress_id → ...) ===

'''Нарушува 2NF:''' user_course_progress_id е дел од примарниот клуч, а progress_completed, progress_completed_at зависат само од user_course_progress_id.

'''Декомпозиција:'''

{{{
User_Course_Progress(user_course_progress_id, progress_completed, progress_completed_at, enrollment_id, course_lecture_id)
   PK: user_course_progress_id

Universal_Relation_Shifter_Base_8 = Universal_Relation_Shifter_Base_7 - {user_course_progress_id, progress_completed, progress_completed_at}

Universal_Relation_Shifter_Base_8(
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,
expert_id,
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_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
   PK: ∅
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id и course_lecture_id
'''Проверка за dependency preservation:''' FD17 е зачувана во User_Course_Progress

=== Состојба после 2NF декомпозиција ===

{{{
Verification_Token(verification_token_uuid, token_created_at, token_expired_at, user_id)
   PK: verification_token_uuid

Meeting_Reminder(meeting_reminder_id, meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id)
   PK: meeting_reminder_id

Expert(expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash)
   PK: expert_id

Tag_Translate(tag_translate_id, tag_translate_language, tag_value, tag_id)
   PK: tag_translate_id

Course_Translate(course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id)
   PK: course_translate_id

Course_Content_Translate(course_content_translate_id, content_translate_title, content_translate_language, course_content_id)
   PK: course_content_translate_id

Course_Lecture_Translate(course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id)
   PK: course_lecture_translate_id

User_Course_Progress(user_course_progress_id, progress_completed, progress_completed_at, enrollment_id, course_lecture_id)
   PK: user_course_progress_id

Universal_Relation_Shifter_Base_8(
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,
expert_id,
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_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
   PK: ∅
}}}

----

== 6. 3NF Декомпозиција ==

=== Проверка за транзитивни зависности во секоја релација ===

==== Verification_Token(verification_token_uuid, token_created_at, token_expired_at, user_id) ====

'''Транзитивни зависности:'''
 * verification_token_uuid → user_id → user_*?
   * user_* НЕ се во Verification_Token, па нема транзитивна зависност во оваа релација.

'''Заклучок:''' Verification_Token е во 3NF ✓

==== Meeting_Reminder(meeting_reminder_id, meeting_at, meeting_scheduled_at, meeting_sent, meeting_link, user_id) ====

'''Транзитивни зависности:'''
 * meeting_reminder_id → userId → user_*?
   * user_* атрибутите НЕ се во Meeting_Reminder.

'''Заклучок:''' Meeting_Reminder е во 3NF ✓

==== Expert(expert_id, expert_name, expert_email, expert_login_provider, expert_password_hash) ====

'''Транзитивни зависности:'''
 * Нема не-клучни атрибути кои одредуваат други не-клучни атрибути.

'''Заклучок:''' Expert е во 3NF ✓

==== Tag_Translate(tag_translate_id, tag_translate_language, tag_value, tag_id) ====

'''Транзитивни зависности:'''
 * tag_translate_id → tag_id → tag_*?
   * tag_* атрибутите НЕ се во Tag_Translate.

'''Заклучок:''' Tag_Translate е во 3NF ✓

==== Course_Translate(course_translate_id, course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id) ====

'''Транзитивни зависности:'''
 * course_translate_id → course_id → course_*?
   * course_* атрибутите НЕ се во Course_Translate.

'''Заклучок:''' Course_Translate е во 3NF ✓

==== Course_Content_Translate(course_content_translate_id, content_translate_title, content_translate_language, course_content_id) ====

'''Транзитивни зависности:'''
 * course_content_translate_id → course_content_id → course_content_*?
   * course_content_* атрибутите НЕ се во Course_Content_Translate.

'''Заклучок:''' Course_Content_Translate е во 3NF ✓

==== Course_Lecture_Translate(course_lecture_translate_id, lecture_title, lecture_language, lecture_content_file_name, lecture_description, lecture_content_text, course_lecture_id) ====

'''Транзитивни зависности:'''
 * course_lecture_translate_id → course_lecture_id → course_lecture_*?
   * course_lecture_* атрибутите НЕ се во Course_Lecture_Translate.

'''Заклучок:''' Course_Lecture_Translate е во 3NF ✓

==== User_Course_Progress(user_course_progress_id, progress_completed, progress_completed_at, enrollment_id, course_lecture_id) ====

'''Транзитивни зависности:'''
 * user_course_progress_id → enrollment_id → enrollment_*?
   * enrollment_* атрибутите НЕ се во User_Course_Progress.
 * user_course_progress_id → course_lecture_id → course_lecture_*?
   * course_lecture_* атрибутите НЕ се во User_Course_Progress.

'''Заклучок:''' User_Course_Progress е во 3NF ✓

==== Universal_Relation_Shifter_Base_8 - Проверка за транзитивни зависности ====

Примарен клуч на Universal_Relation_Shifter_Base_8: '''∅'''

'''Заклучок:''' После извлекување на сите парцијални зависности (FD2, FD3, FD4, FD10, FD12, FD14, FD16, FD17), универзалната релација е целосно декомпонирана. Примарниот клуч на Universal_Relation_Shifter_Base_8 е празно множество (∅), што значи дека таа релација повеќе не постои како валидна релација.
Останатите функционални зависности (FD1, FD5-FD9, FD11, FD13, FD15) **нарушуваат 3NF** бидејќи нивните детерминанти (user_id, course_id, enrollment_id, итн.) не се примарни клучеви на 
Universal_Relation_Shifter_Base_8, туку се независни детерминанти кои треба да формираат посебни релации.

=== Чекор 6.1: Декомпозиција по FD1 (user_id → user_*) ===

{{{
User(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)
   PK: user_id

Universal_Relation_Shifter_Base_9 = Universal_Relation_Shifter_Base_8 - {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}

Universal_Relation_Shifter_Base_9(
expert_id,
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_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id
'''Проверка за dependency preservation:''' FD1 е зачувана во User

=== Чекор 6.2: Декомпозиција по FD5 (course_id → course_*) ===

{{{
Course(course_id, course_image_url, course_color, course_difficulty, course_duration_minutes, course_price)
   PK: course_id

Universal_Relation_Shifter_Base_10 = Universal_Relation_Shifter_Base_9 - {course_id, course_image_url, course_color, course_difficulty, course_duration_minutes, course_price}

Universal_Relation_Shifter_Base_10(
expert_id,
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_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id → course_version_id
'''Проверка за dependency preservation:''' FD5 е зачувана во Course

=== Чекор 6.3: Декомпозиција по FD6 (enrollment_id → enrollment_*) ===

{{{
Enrollment(enrollment_id, enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date, user_id, course_version_id, payment_id, review_id)
   PK: enrollment_id

Universal_Relation_Shifter_Base_11 = Universal_Relation_Shifter_Base_10 - {enrollment_status, enrollment_purchase_date, enrollment_activation_date, enrollment_completion_date}

Universal_Relation_Shifter_Base_11(
expert_id,
course_version_id, version_number, version_creation_date, version_active,
enrollment_id,
payment_id, payment_amount, payment_date, payment_method, payment_status,
review_id, review_rating, review_comment, review_date,
course_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id
'''Проверка за dependency preservation:''' FD6 е зачувана во Enrollment

=== Чекор 6.4: Декомпозиција по FD7 (payment_id → payment_*) ===

{{{
Payment(payment_id, payment_amount, payment_date, payment_method, payment_status, enrollment_id)
   PK: payment_id

Universal_Relation_Shifter_Base_12 = Universal_Relation_Shifter_Base_11 - {payment_amount, payment_date, payment_method, payment_status}

Universal_Relation_Shifter_Base_12(
expert_id,
course_version_id, version_number, version_creation_date, version_active,
enrollment_id,
review_id, review_rating, review_comment, review_date,
course_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id
'''Проверка за dependency preservation:''' FD7 е зачувана во Payment

=== Чекор 6.5: Декомпозиција по FD8 (review_id → review_*) ===

{{{
Review(review_id, review_rating, review_comment, review_date, enrollment_id)
   PK: review_id

Universal_Relation_Shifter_Base_13 = Universal_Relation_Shifter_Base_12 - {review_rating, review_comment, review_date}

Universal_Relation_Shifter_Base_13(
expert_id,
course_version_id, version_number, version_creation_date, version_active,
enrollment_id,
course_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id, tag_type
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id
'''Проверка за dependency preservation:''' FD8 е зачувана во Review

=== Чекор 6.6: Декомпозиција по FD9 (tag_id → tag_*) ===

{{{
Tag(tag_id, tag_type)
   PK: tag_id

Universal_Relation_Shifter_Base_14 = Universal_Relation_Shifter_Base_13 - {tag_type}

Universal_Relation_Shifter_Base_14(
expert_id,
course_version_id, version_number, version_creation_date, version_active,
enrollment_id,
course_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку tag_id
'''Проверка за dependency preservation:''' FD9 е зачувана во Tag

=== Чекор 6.7: Декомпозиција по FD11 (course_version_id → course_version_*) ===

{{{
Course_Version(course_version_id, version_number, version_creation_date, version_active, course_id)
   PK: course_version_id

Universal_Relation_Shifter_Base_15 = Universal_Relation_Shifter_Base_14 - {course_version_id, version_number, version_creation_date, version_active}

Universal_Relation_Shifter_Base_15(
expert_id,
enrollment_id,
course_content_id, content_position,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id
'''Проверка за dependency preservation:''' FD11 е зачувана во Course_Version

=== Чекор 6.8: Декомпозиција по FD13 (course_content_id → course_content_*) ===

{{{
Course_Content(course_content_id, content_position, course_version_id)
   PK: course_content_id

Universal_Relation_Shifter_Base_16 = Universal_Relation_Shifter_Base_15 - {course_content_id, content_position}

Universal_Relation_Shifter_Base_16(
expert_id,
enrollment_id,
course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type,
tag_id
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id → course_version_id
'''Проверка за dependency preservation:''' FD13 е зачувана во Course_Content

=== Чекор 6.9: Декомпозиција по FD15 (course_lecture_id → course_lecture_*) ===

{{{
Course_Lecture(course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type, course_content_id)
   PK: course_lecture_id

Universal_Relation_Shifter_Base_17 = Universal_Relation_Shifter_Base_16 - {course_lecture_id, lecture_duration_minutes, lecture_position, lecture_content_type}

Universal_Relation_Shifter_Base_17(
expert_id,
enrollment_id,
tag_id
)
}}}

'''Проверка за lossless join:''' Може да се реконструира преку enrollment_id → course_version_id → course_content_id
'''Проверка за dependency preservation:''' FD15 е зачувана во Course_Lecture

=== Анализа на Universal_Relation_Shifter_Base_17 ===

{{{
Universal_Relation_Shifter_Base_17(
expert_id,
enrollment_id,
tag_id
)
}}}


Universal_Relation_Shifter_Base_17 претставува колекција од foreign keys кои треба да се организираат во посебни many-to-many junction tables.

'''Прашање:''' Дали оваа релација има семантичка смисла?
'''Одговор:''' НЕ! Universal_Relation_Shifter_Base_17 е само технички резултат од декомпозициите и не претставува ниеден реален бизнис концепт.

'''Заклучок од 3NF:'''
Сите функционални зависности (FD1-FD27) се организирани во релации кои се во 3NF. Universal_Relation_Shifter_Base_17 повеќе не постои како валидна релација.

'''Останати врски (many-to-many):'''
Foreign keys во Universal_Relation_Shifter_Base_17 укажуваат на many-to-many релации кои НЕ се дефинирани преку функционални зависности, туку преку бизнис логика:

* '''User_Tag'''(user_id, tag_id) - корисник има интереси
* '''Course_Tag'''(course_id, tag_id) - курс има тагови  
* '''Expert_Course'''(expert_id, course_id) - експерт предава курсеви

=== Состојба после 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 е зачувана во барем една од финалните релации.