Changes between Version 36 and Version 37 of Normalization

Timestamp:

05/05/26 09:58:43 (3 weeks ago)

Author:

221296

Comment:

--

Normalization

@@ -232 +232 @@
 
 === Check for 2NF
-R does not satisfy 2NF because it has a composite candidate key and many non-key attributes depend only on part of that key, instead of depending on the whole key.
-
-Examples of partial dependencies are:
-* payment_id → user_id, subscription_id, amount
-* ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at
-* course_id → course_name, course_price, course_status, instructor_id
-* category_id → category_name, category_description
-* attempt_id → user_id, quiz_id, score, attempt_date
-* certificate_id → enrollment_id, issue_date, certificate_code, certificate_status
-
-These are partial dependencies on components of the composite key, so R violates 2NF.
+The universal relation R is not in Second Normal Form (2NF) because it contains a composite primary key and several non-key attributes depend only on a part of that key (partial dependencies), instead of the whole key.
+
+Examples of such partial dependencies:
+
+payment_id → user_id, subscription_id, amount
+ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at
+course_id → course_name, course_price, course_status, instructor_id
+category_id → category_name, category_description
+attempt_id → user_id, quiz_id, score, attempt_date
+certificate_id → enrollment_id, issue_date, certificate_code, certificate_status
+
+Therefore, R violates 2NF.
 
 == Decomposition to 2NF
 
-=== Grouping by determinants (partial dependencies)
-To remove partial dependencies, the attributes are grouped according to the determinants from the functional dependencies:
-
-- user_id → first_name, last_name, email, password, role
-- instructor_id → user_id
-- admin_id → user_id
-- plan_id → plan_name, price, duration_months, plan_description, access_type
-- subscription_id → user_id, plan_id, start_date, end_date, subscription_status
-- payment_id → user_id, subscription_id, amount
-- ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at
-- category_id → category_name, category_description
-- course_id → course_name, course_price, course_status, instructor_id
-- module_id → course_id, module_title, module_description
-- lesson_id → module_id, lesson_title, material
-- quiz_id → lesson_id, total_points, passing_score
-- attempt_id → user_id, quiz_id, score, attempt_date
-- enrollment_id → user_id, course_id, enroll_date, completion_status, progress_percentage
-- certificate_id → enrollment_id, issue_date, certificate_code, certificate_status
-The relation CourseCategory has composite key (course_id, category_id) and no additional non-trivial functional dependencies.
-
-=== 2NF relations (first decomposition)
-After removing the partial dependencies, we obtain the following relations:
-
-- `UserEntity (user_id, first_name, last_name, email, password, role)`
-- `Users(user_id)`
-- `Administrators(admin_id, user_id)`
-- `Instructors(instructor_id, user_id)`
-
-- `SubscriptionPlan(plan_id, plan_name, price, duration_months, plan_description, access_type)`
-- `UserSubscription(subscription_id, user_id, plan_id, start_date, end_date, subscription_status)`
-- `Payment(payment_id, user_id, subscription_id, amount)`
-
-- `SupportTicket(ticket_id, user_id, admin_id, subject, ticket_description, ticket_status, created_at)`
-
-- `Category(category_id, category_name, category_description)`
-- `Course(course_id, course_name, course_price, course_status, instructor_id)`
-- `CourseCategory(course_id, category_id)`
-
-- `Module(module_id, course_id, module_title, module_description)`
-- `Lesson(lesson_id, module_id, lesson_title, material)`
-- `Quiz(quiz_id, lesson_id, total_points, passing_score)`
-- `QuizAttempt(attempt_id, user_id, quiz_id, score, attempt_date)`
-
-- `Enrollment(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)`
-- `Certificate(certificate_id, enrollment_id, issue_date, certificate_code, certificate_status)`
-
-{{{Lossless join:}}}
+=== We eliminate partial dependencies by decomposing R into smaller relations such that each determinant becomes a primary key in its own relation.
+
+{{{R1: UserEntity:}}}
 [[BR]]
-To verify that the decomposition is lossless, we use the formal condition:
-
-For every decomposition of R into relations Ri and Rj, the intersection of their attributes must functionally determine at least one of the relations:
-
-Ri ∩ Rj → Ri  OR  Ri ∩ Rj → Rj
-
-In this decomposition, each relation is formed based on a determinant (such as user_id, plan_id, course_id, etc.), which becomes the primary key of that relation.
-
-The shared attributes between relations (e.g. user_id, course_id, subscription_id) are keys in at least one of the involved relations.
-
-Therefore, the condition for lossless join is satisfied, and the decomposition is lossless with respect to the original universal relation R.
+- UserEntity(user_id, first_name, last_name, email, password, role)
+- PK: user_id
+
+- Derived from:
+- FD1: user_id → first_name, last_name, email, password, role
+
+- Lossless Join Check:
+
+R ∩ UserEntity = {user_id}
+user_id → UserEntity
+
+⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+FD1 is fully contained in UserEntity
+
+⇒ PRESERVED