Changes between Version 27 and Version 28 of Normalization

Timestamp:

04/27/26 03:01:22 (5 days ago)

Author:

221296

Comment:

--

Normalization

@@ -323 +323 @@
 The resulting relations after the 3NF step are:
 
+`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)
+
+All resulting relations satisfy 3NF because for every non-trivial functional dependency X → Y inside a relation, X is a key of that relation, or the dependent attributes are stored in a separate relation determined by their own key.
+
+== Check for BCNF
+Each resulting relation is also in BCNF, because in every non-trivial functional dependency within a relation, the determinant is a superkey of that relation.
+* `UserEntity`: user_id is key → BCNF
+* `Users`: user_id is key → BCNF
+* `Administrators`: admin_id is key → BCNF
+* `Instructors`: instructor_id is key → BCNF
+* `SubscriptionPlan`: plan_id is key → BCNF
+* `UserSubscription`: subscription_id is key → BCNF
+* `Payment`: payment_id is key → BCNF
+* `SupportTicket`: ticket_id is key → BCNF
+* `Category`: category_id is key → BCNF
+* `Course`: course_id is key → BCNF
+* `CourseCategory`: (course_id, category_id) is composite key → BCNF
+* `Module`: module_id is key → BCNF
+* `Lesson`: lesson_id is key → BCNF
+* `Quiz`: quiz_id is key → BCNF
+* `QuizAttempt`: attempt_id is key → BCNF
+* `Enrollment`: enrollment_id is key → BCNF
+* `Certificate`: certificate_id is key → BCNF
+{{{Therefore, the resulting schema satisfies BCNF.}}}
+
+== Check for 4NF (multivalued dependencies)
+No additional non-trivial multivalued dependencies are identified in the resulting schema.
+
+The many-to-many relationship between Course and Category is already decomposed into the separate relation:
+
+CourseCategory(course_id, category_id)
+
+This relation contains only the composite key and no additional descriptive attributes, so no further decomposition is required.
+
+All other relations describe a single entity or relationship around one key, and no independent multi-valued facts are stored together in the same relation.
+
+{{{Therefore, the schema satisfies 4NF.}}}
+
+== Final relations
+Final relations
+
+After applying normalization up to 4NF, the final normalized schema consists of the following relations:
+
+{{{
 UserEntity(user_id, first_name, last_name, email, password, role)
 Users(user_id)
@@ -345 +410 @@
 Enrollment(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
 Certificate(certificate_id, enrollment_id, issue_date, certificate_code, certificate_status)
-
-All resulting relations satisfy 3NF because for every non-trivial functional dependency X → Y inside a relation, X is a key of that relation, or the dependent attributes are stored in a separate relation determined by their own key.
-
-== Check for BCNF
-Each resulting relation is also in BCNF, because in every non-trivial functional dependency within a relation, the determinant is a superkey of that relation.
-* `UserEntity`: user_id is key → BCNF
-* Users: user_id is key → BCNF
-* Administrators: admin_id is key → BCNF
-* Instructors: instructor_id is key → BCNF
-* SubscriptionPlan: plan_id is key → BCNF
-* UserSubscription: subscription_id is key → BCNF
-* Payment: payment_id is key → BCNF
-* SupportTicket: ticket_id is key → BCNF
-* Category: category_id is key → BCNF
-* Course: course_id is key → BCNF
-* CourseCategory: (course_id, category_id) is composite key → BCNF
-* Module: module_id is key → BCNF
-* Lesson: lesson_id is key → BCNF
-* Quiz: quiz_id is key → BCNF
-* QuizAttempt: attempt_id is key → BCNF
-* Enrollment: enrollment_id is key → BCNF
-* Certificate: certificate_id is key → BCNF
-{{{Therefore, the resulting schema satisfies BCNF.}}}
-
-== Check for 4NF (multivalued dependencies)
-No additional non-trivial multivalued dependencies are identified in the resulting schema.
-
-The many-to-many relationship between Course and Category is already decomposed into the separate relation:
-
-CourseCategory(course_id, category_id)
-
-This relation contains only the composite key and no additional descriptive attributes, so no further decomposition is required.
-
-All other relations describe a single entity or relationship around one key, and no independent multi-valued facts are stored together in the same relation.
-
-{{{Therefore, the schema satisfies 4NF.}}}
-
-== Final relations
-Final relations
-
-After applying normalization up to 4NF, the final normalized schema consists of 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)
 }}}