Changes between Version 41 and Version 42 of Normalization

Timestamp:

05/05/26 10:38:28 (3 weeks ago)

Author:

221296

Comment:

--

Normalization

@@ -250 +250 @@
 
 {{{R1: UserEntity:}}}
-[[BR]]
+
 - !UserEntity(user_id, first_name, last_name, email, password, role)
   
@@ -274 +274 @@
 
 
-{{{Dependency preservation:}}}
-[[BR]]
-To verify dependency preservation, we check whether the union of the projections of the functional dependencies onto each resulting relation is equivalent to the original set of functional dependencies:
-
-(⋃ projection of F onto Ri)+ = F+
-
-In this decomposition, each functional dependency from the initial set (FD1–FD15) is fully contained within at least one of the resulting relations.
-
-For example:
-- user_id → user attributes is preserved in UserEntity
-- course_id → course attributes is preserved in Course
-- subscription_id → subscription attributes is preserved in UserSubscription
-
-Since no functional dependency is lost and all can be enforced locally within the resulting relations, the decomposition is dependency-preserving.
-{{{
-The same argument applies to all resulting relations, since each relation is constructed around a determinant that forms its key.
-}}}
+{{{R2: SubscriptionPlan:}}}
+
+- !SubscriptionPlan(plan_id, plan_name, price, duration_months, plan_description, access_type)
+  
+  PK: plan_id
+
+- Derived from:
+
+ FD4: plan_id → plan_name, price, duration_months, plan_description, access_type
+
+- Lossless Join Check:
+
+  R ∩ !SubscriptionPlan = {plan_id}
+
+  plan_id → !SubscriptionPlan
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD4 is preserved
+
+ ⇒ PRESERVED
+
+{{{R3: UserSubscription:}}}
+
+- !UserSubscription(subscription_id, user_id, plan_id, start_date, end_date, subscription_status)
+  
+  PK: subscription_id
+
+- Derived from:
+
+ FD5: subscription_id → user_id, plan_id, start_date, end_date, subscription_status
+
+- Lossless Join Check:
+
+  R ∩ !UserSubscription = {subscription_id}
+
+  subscription_id → !UserSubscription
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD5 is preserved
+
+ ⇒ PRESERVED
+
+{{{R4: Payment:}}}
+
+- !Payment(payment_id, user_id, subscription_id, amount)
+  
+  PK: payment_id
+
+- Derived from:
+
+ FD6: payment_id → user_id, subscription_id, amount
+
+- Lossless Join Check:
+
+  R ∩ !Payment = {payment_id}
+
+  payment_id → !Payment
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD6 is preserved
+
+ ⇒ PRESERVED
+
+{{{R5: SupportTicket:}}}
+
+- !SupportTicket(ticket_id, user_id, admin_id, subject, ticket_description, ticket_status, created_at)
+  
+  PK: ticket_id
+
+- Derived from:
+
+ FD7: ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at
+
+- Lossless Join Check:
+
+  R ∩ !SupportTicket = {ticket_id}
+
+  ticket_id → !SupportTicket
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD7 is preserved
+
+ ⇒ PRESERVED
+
+{{{R6: Category:}}}
+
+- !Category(category_id, category_name, category_description)
+  
+  PK: category_id
+
+- Derived from:
+
+ FD8: category_id → category_name, category_description
+
+- Lossless Join Check:
+
+  R ∩ !Category = {category_id}
+
+  category_id → !Category
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD8 is preserved
+
+ ⇒ PRESERVED
+
+{{{R7: Course:}}}
+
+- !Course(course_id, course_name, course_price, course_status, instructor_id)
+  
+  PK: course_id
+
+- Derived from:
+
+ FD9: course_id → course_name, course_price, course_status, instructor_id
+
+- Lossless Join Check:
+
+  R ∩ !Course = {course_id}
+
+  course_id → !Course
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD9 is preserved
+
+ ⇒ PRESERVED
+
+{{{R8: Module:}}}
+
+- !Module(module_id, course_id, module_title, module_description)
+  
+  PK: module_id
+
+- Derived from:
+
+ FD10: module_id → course_id, module_title, module_description
+
+- Lossless Join Check:
+
+  R ∩ !Module = {module_id}
+
+  module_id → !Module
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD10 is preserved
+
+{{{R9: Lesson:}}}
+
+- !Lesson(lesson_id, module_id, lesson_title, material)
+  
+  PK: lesson_id
+
+- Derived from:
+
+ FD11: lesson_id → module_id, lesson_title, material
+
+- Lossless Join Check:
+
+  R ∩ !Lesson = {lesson_id}
+
+  lesson_id → !Lesson
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD11 is preserved
+
+{{{R10: Quiz:}}}
+
+- !Quiz(quiz_id, lesson_id, total_points, passing_score)
+  
+  PK: quiz_id
+
+- Derived from:
+
+ FD12: quiz_id → lesson_id, total_points, passing_score
+
+- Lossless Join Check:
+
+  R ∩ !Quiz = {quiz_id}
+
+  quiz_id → !Quiz
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD12 is preserved
+
+
+{{{R11: QuizAttempt:}}}
+
+- !QuizAttempt(attempt_id, user_id, quiz_id, score, attempt_date)
+  
+  PK: attempt_id
+
+- Derived from:
+
+ FD13: attempt_id → user_id, quiz_id, score, attempt_date
+
+- Lossless Join Check:
+
+  R ∩ !QuizAttempt = {attempt_id}
+
+  attempt_id → !QuizAttempt
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD13 is preserved
+
+{{{R12: Enrollment:}}}
+
+- !Enrollment(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
+  
+  PK: enrollment_id
+
+- Derived from:
+
+ FD14: enrollment_id → user_id, course_id, enroll_date, completion_status, progress_percentage
+
+- Lossless Join Check:
+
+  R ∩ !Enrollment = {enrollment_id}
+
+  enrollment_id → !Enrollment
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD14 is preserved
+
+{{{R13: Certificate:}}}
+
+- !Certificate(certificate_id, enrollment_id, issue_date, certificate_code, certificate_status)
+  
+  PK: certificate_id
+
+- Derived from:
+
+ FD15: certificate_id → enrollment_id, issue_date, certificate_code, certificate_status
+
+- Lossless Join Check:
+
+  R ∩ !Certificate = {certificate_id}
+
+  certificate_id → !Certificate
+
+  ⇒ The decomposition is lossless
+
+- Dependency Preservation:
+
+ FD15 is preserved
+
+ ⇒ PRESERVED
+
+{{{R14: CourseCategory:}}}
+- !CourseCategory(course_id, category_id)
+  
+  PK: (course_id, category_id)
+
+  This relation represents a many-to-many relationship and contains no additional non-trivial functional dependencies.
+
+Final Result of 2NF Decomposition
+After removing all partial dependencies, the resulting schema consists of relations where:
+- Every non-key attribute depends on the whole primary key
+- no partial dependencies remain
+
+Therefore, the decomposition satisfies Second Normal Form (2NF).
 
 == Check for 3NF (and decomposition)