Changes between Version 7 and Version 8 of Normalization

Timestamp:

04/21/26 01:00:58 (11 days ago)

Author:

221296

Comment:

--

Normalization

@@ -91 +91 @@
 To determine a candidate key of the universal de-normalized relation, we identify a minimal set of identifiers whose closure determines all attributes of R.
 
-Because the universal relation combines several independent components of the system (subscriptions, payments, support tickets, course-category assignments, quiz attempts, and certificates), the candidate key must include one identifier from each independent component that is not functionally determined by another component..
-{{{#!comment It is not correct to simply collect all identifiers, because some identifiers are functionally determined by others. For example:
-application_id → clinic_id
-listing_id → animal_id, admin_id
-animal_id → owner_id
-owner_id → client_id
-healthrecord_id → appointment_id, animal_id
-appointment_id → clinic_id, animal_id
-
-
-Therefore, attributes such as clinic_id, animal_id, appointment_id, admin_id, owner_id, and client_id do not need to appear explicitly in the candidate key if they can already be derived from other identifiers.
+Because the universal relation combines several independent components of the system (subscriptions, payments, support tickets, course-category assignments, quiz attempts, and certificates), the candidate key must include one identifier from each independent component that is not functionally determined by another component.
+
 }}}
 
@@ -107 +98 @@
 
 {{{
-K = {notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
+K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
 }}}
 
@@ -114 +105 @@
 === Closure proof for K
 {{{
-Let K = {notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
+Let K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
 
 Compute K+:
-Start: K+ = K
+Start: K+ = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
+
+From FD6:
+payment_id → user_id, subscription_id, amount
+Add: user_id, subscription_id, amount
 
 From FD5:
-notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
-Add: notification_type, notification_message, notification_created_at, is_read, user_id
+subscription_id → plan_id, start_date, end_date, subscription_status
+Add: plan_id, start_date, end_date, subscription_status
+
+From FD4:
+plan_id → plan_name, price, duration_months, access_type
+Add: plan_name, price, duration_months, access_type
 
 From FD1:
-user_id → username, email, name, surname, full_name, password_hash, user_created_at
-Add: username, email, name, surname, full_name, password_hash, user_created_at
+user_id → first_name, last_name, email, password, role
+Add: first_name, last_name, email, password, role
 
 From FD7:
-application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
-Add: app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
-
-From FD6:
-clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
-Add: clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
-
-From FD9:
-listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
-Add: listing_status, listing_created_at, price, listing_description, animal_id, admin_id
+ticket_id → admin_id, subject, description, ticket_status, created_at, user_id
+Add: admin_id, subject, description, ticket_status, created_at
+(user_id already belongs to K+)
 
 From FD3:
@@ -144 +136 @@
 
 From FD8:
-animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
-Add: animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
-
-From FD4:
-owner_id → client_id
-Add: client_id
+category_id → category_name, category_description
+Add: category_name, category_description
+
+From FD9:
+course_id → course_name, course_price, course_status, instructor_id
+Add: course_name, course_price, course_status, instructor_id
 
 From FD2:
-client_id → user_id, is_blocked, blocked_at, blocked_reason
-Add: is_blocked, blocked_at, blocked_reason
+instructor_id → user_id
 user_id already belongs to K+
 
+From FD13:
+attempt_id → user_id, quiz_id, score, attempt_date
+Add: quiz_id, score, attempt_date
+(user_id already belongs to K+)
+
+From FD12:
+quiz_id → lesson_id, total_points, passing_score
+Add: lesson_id, total_points, passing_score
+
 From FD11:
-healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
-Add: hr_type, hr_description, hr_date, appointment_id
-animal_id already belongs to K+
+lesson_id → module_id, lesson_title, material
+Add: module_id, lesson_title, material
 
 From FD10:
-appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
-Add: date_time, appointment_status, notes
-animal_id and clinic_id already belong to K+
-
-From FD12:
-review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
-Add: reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
-
-From FD13:
-review_id → target_user_id
-Add: target_user_id
+module_id → module_title, module_description, course_id
+Add: module_title, module_description
+(course_id already belongs to K+)
+
+From FD15:
+certificate_id → enrollment_id, issue_date, certificate_code, certificate_status
+Add: enrollment_id, issue_date, certificate_code, certificate_status
 
 From FD14:
-review_id → target_clinic_id
-Add: target_clinic_id
-}}}
-
-Therefore, K+ = R, so K is a superkey.
-
-K is also minimal, because removing any attribute from K would cause the loss of one independent component of the universal relation
-{{{#!comment
-* without notification_id, notification attributes cannot be derived
-* without application_id, application attributes cannot be derived
-* without listing_id, listing attributes cannot be derived
-* without healthrecord_id, health record attributes cannot be derived
-* without review_id, review attributes cannot be derived
-* without saved_user_id or saved_listing_id, the FavoriteListings relationship cannot be identified
-}}}
-
-So K is a candidate key.
+enrollment_id → user_id, course_id, enroll_date, completion_status, progress_percentage
+Add: enroll_date, completion_status, progress_percentage
+(user_id and course_id already belong to K+)
+}}}
+
+Therefore, K+ contains all attributes of the universal relation R, so K is a superkey.
+
+K is also minimal, because removing any attribute from K would cause the loss of at least one independent component of the universal relation:
+- without payment_id, payment/subscription information is lost
+- without ticket_id, support ticket information is lost
+- without course_id or category_id, the course-category association is lost
+- without attempt_id, quiz attempt and course content assessment information is lost
+- without certificate_id, certificate and enrollment completion information is lost
+
+So, K is a candidate key..
 
 === Chosen primary key
@@ -197 +190 @@
 
 {{{
-{notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
+K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
 }}}