Changes between Version 16 and Version 17 of Normalization

Timestamp:

04/26/26 23:55:48 (5 days ago)

Author:

221296

Comment:

--

Normalization

@@ -195 +195 @@
 
 === Check for 1NF
-R satisfies 1NF because all attributes are atomic and there are no repeating groups.
-Each row is uniquely identified by the chosen composite candidate key.
+R satisfies 1NF because all attributes are atomic and there are no repeating groups or multi-valued attributes inside a single tuple.
+Each attribute stores a single value, and the universal relation is identified by the chosen composite candidate key.
+Therefore, R is in 1NF
 
 === Check for 2NF
-R does not satisfy 2NF because it has a composite primary key and many non-key attributes depend only on part of that key rather than on the whole key.
-
-Examples:
-* listing_id → listing_status, listing_created_at, price, listing_description
-* animal_id → animal_name, species, breed, sex, date_of_birth, photo_url, owner_id
-* clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
-* review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
-* notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
+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.
@@ -213 +215 @@
 
 === Grouping by determinants (partial dependencies)
-* user_id → username, email, name, surname, full_name, password_hash, user_created_at
-* client_id → user_id, is_blocked, blocked_at, blocked_reason
-* admin_id → user_id
-* owner_id → client_id
-* notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
-* clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
-* application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
-* animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
-* listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
-* appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
-* healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
-* review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
-* review_id → target_user_id / target_clinic_id
-* FavoriteListings has key (saved_user_id, saved_listing_id) only
+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)
-* Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at)
-* Clients(client_id, user_id, is_blocked, blocked_at, blocked_reason)
-* Admins(admin_id, user_id)
-* Owners(owner_id, client_id)
-* Notifications(notification_id, user_id, notification_type, notification_message, notification_created_at, is_read)
-* VetClinics(clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city)
-* VetClinicApplications(application_id, clinic_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason)
-* Animals(animal_id, owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url)
-* Listings(listing_id, admin_id, animal_id, listing_status, listing_created_at, price, listing_description)
-* Appointments(appointment_id, clinic_id, animal_id, date_time, appointment_status, notes)
-* HealthRecords(healthrecord_id, appointment_id, animal_id, hr_type, hr_description, hr_date)
-* Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted)
-* FavoriteListings(saved_user_id, saved_listing_id)
+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:}}}
 [[BR]]
-The decomposition is lossless because each new relation is formed from a determinant that becomes the key of that relation. The common attributes used in the decomposition are keys in at least one of the resulting relations, so no information is lost.
+The decomposition is lossless because each new relation is formed around a determinant that becomes the key of that relation. The common attributes used in the decomposition are keys in at least one of the resulting relations, so no information is lost.
+
 
 {{{Dependency preservation:}}}
 [[BR]]
-The decomposition is dependency-preserving because each functional dependency FD1–FD14 is represented fully inside one of the resulting relations. FavoriteListings has only its composite key and no additional non-trivial dependencies.
+The decomposition is dependency-preserving because each functional dependency FD1–FD15 is represented fully inside one of the resulting relations. CourseCategory has only its composite key and no additional non-trivial functional dependencies.
 
 == Check for 3NF (and decomposition)