Changes between Version 10 and Version 11 of normalization

Timestamp:

01/30/26 00:58:40 (15 hours ago)

Author:

231175

Comment:

--

normalization

@@ -301 +301 @@
 
 '''Проверка за lossless join:''' Може да се реконструира преку course_translate_id
-'''Проверка за dependency preservation:''' ✓ Сите FD28 е зачувана во Course_What_Will_Be_Learned
+'''Проверка за dependency preservation:''' FD28 е зачувана во Course_What_Will_Be_Learned
 
 
@@ -351 +351 @@
  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
 
 
@@ -357 +358 @@
 == 5. 2NF Декомпозиција ==
 
-=== Чекор 5.1: Декомпозиција по FD5 (productId → ...) ===
-
-'''Нарушува 2NF:''' productId е дел од примарниот клуч, а product_name, product_description, итн. зависат само од productId.
+=== Чекор 5.1: Декомпозиција по FD2 (verification_token_uuid → ...) ===
+
+'''Нарушува 2NF:''' verification_token_uuid е дел од примарниот клуч, а token_created_at, token_expired_at зависат само од verification_token_uuid.
 
 '''Декомпозиција:'''
 
 {{{
-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.
+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.
 
 '''Декомпозиција:'''
 
 {{{
-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.
+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.
 
 '''Декомпозиција:'''
 
 {{{
-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} е подмножество на примарниот клуч.
+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.
 
 '''Декомпозиција:'''
 
 {{{
-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} е подмножество на примарниот клуч.
+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.
 
 '''Декомпозиција:'''
 
 {{{
-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 ✓
+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 декомпозиција ===