Changes between Version 22 and Version 23 of Normalization

Timestamp:

04/27/26 02:46:10 (5 days ago)

Author:

221296

Comment:

--

Normalization

@@ -281 +281 @@
   Because subscription details are stored in UserSubscription, they should not be repeated in Payment. Payment keeps only payment_id, user_id, subscription_id and amount, so no transitive dependency remains inside Payment.
  
-* In Listings(listing_id, admin_id, animal_id, …), we have listing_id → admin_id and globally admin_id → user_id, but user attributes are stored in Users, not in Listings, so no transitive dependency remains inside Listings.
-* In VetClinicApplications(application_id, clinic_id, …), we have application_id → clinic_id and clinic_id determines clinic attributes, but those attributes are stored in VetClinics, not in VetClinicApplications, so no transitive dependency remains there either.
-
-=== Review subtypes (User Review and Clinic Review)
-The ER model contains a specialization of Review into UserReview and ClinicReview.
-If both target_user_id and target_clinic_id are stored in Reviews, the relation mixes subtype-specific semantics and may introduce nulls or invalid combinations.
-So we decompose:
-{{{
-UserReviews(review_id, target_user_id)
-ClinicReviews(review_id, target_clinic_id)
-}}}
-
-Now:
-* Reviews stores the common review attributes
-* UserReviews stores the target user of a user review
-* ClinicReviews stores the target clinic of a clinic review
+* In UserSubscription(subscription_id, user_id, plan_id, start_date, end_date, subscription_status), we have:
+  subscription_id → plan_id
+  plan_id → plan_name, price, duration_months, plan_description, access_type
+  Since plan descriptive attributes are stored in SubscriptionPlan, no transitive dependency remains inside UserSubscription.
+
+* In Course(course_id, course_name, course_price, course_status, instructor_id), we have:
+  course_id → instructor_id
+  instructor_id → user_id
+The user descriptive attributes are stored in UserEntity, not in Course, so no transitive dependency remains inside Course.
+
+* In QuizAttempt(attempt_id, user_id, quiz_id, score, attempt_date), we have:
+  attempt_id → quiz_id
+  quiz_id → lesson_id, total_points, passing_score
+  Quiz details are stored in Quiz, so QuizAttempt does not contain transitive dependencies.
+
+* In Certificate(certificate_id, enrollment_id, issue_date, certificate_code, certificate_status), we have:
+  certificate_id → enrollment_id
+  enrollment_id → user_id, course_id, enroll_date, completion_status, progress_percentage
+
+  Enrollment details are stored in Enrollment, so no transitive dependency remains inside Certificate.
+
+*
+=== User specializations (User, Administrator and Instructor)
+The ER model contains a specialization of UserEntity into User, Administrator and Instructor.
+
+The common user attributes are stored in UserEntity:
+
+UserEntity(user_id, first_name, last_name, email, password, role)
+
+The subtype relations store only the identifier that connects the subtype with the general user entity:
+{{{
+Users(user_id)
+Administrators(admin_id, user_id)
+Instructors(instructor_id, user_id)
+}}}
+This avoids storing user descriptive attributes multiple times in the subtype relations. Therefore, no transitive dependency remains inside the specialization relations.
 
 === Results after 3NF step
-* UserReviews(review_id, target_user_id)
-* ClinicReviews(review_id, target_clinic_id)
-
-After separating subtype-specific attributes into UserReviews and ClinicReviews, all resulting relations satisfy 3NF.
+After checking the relations obtained from the 2NF decomposition, no additional decomposition is required for 3NF.
+
+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