wiki:normalization

Context Navigation

Version 10 (modified by 231175, 16 hours ago) ( diff )
--

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

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
FD10: tag_translate_id → tag_translate_language, tag_value, tag_id
FD12: course_translate_id → course_translate_language, course_title_short, course_title, course_description_short, course_description, course_description_long, course_id
FD14: course_content_translate_id → content_translate_title, content_translate_language, course_content_id
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:

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

FD4 транзитивно: {saleId, poId, productId} → customerId (преку R2/saleId) → customer_name, customer_email, customer_phone, customer_address
- НАРУШУВА 3NF

FD7 транзитивно: {saleId, poId, productId} → categoryId (преку R1/productId) → category_name, category_description
- НАРУШУВА 3NF

FD8 транзитивно: {saleId, poId, productId} → supplierId → supplier_name, supplier_contact_person, supplier_phone, supplier_email, supplier_address
- НАРУШУВА 3NF

FD9 транзитивно: {saleId, poId, productId} → warehouseId → warehouse_name, warehouse_location, warehouse_capacity
- НАРУШУВА 3NF

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. Ова покажува дека:

Концептуалниот модел беше правилно дизајниран - ентитетите и нивните атрибути беа логички групирани.

Процесот на нормализација го потврди дизајнот - секоја декомпозиција базирана на функционалните зависности резултираше со релација која одговара на ентитет од оригиналниот модел.

Клучот на универзалната релација {saleId, poId, productId} беше идентификуван преку анализа на функционалните зависности и класификација лева/десна страна.

Сите релации се во BCNF - нема функционална зависност каде детерминантот не е суперклуч.

Декомпозицијата е lossless - оригиналната информација може да се реконструира.

Сите функционални зависности се зачувани - секоја FD е зачувана во барем една од финалните релации.

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text