Changes between Version 32 and Version 33 of Normalization

Timestamp:

05/04/26 11:14:03 (3 weeks ago)

Author:

221296

Comment:

--

Normalization

@@ -96 +96 @@
 
 {{{
-K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
+K = {payment_id, ticket_id, category_id, attempt_id, certificate_id}
 }}}
 
@@ -104 +104 @@
 {{{
 
-Let K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
+Let K = {payment_id, ticket_id, category_id, attempt_id, certificate_id}
 
 Compute K+:
-Start: K+ = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
+Start: K+ = {payment_id, ticket_id, category_id, attempt_id, certificate_id}
 
 From FD6:
@@ -138 +138 @@
 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:
-instructor_id → user_id
-user_id already belongs to K+
-
 From FD13:
 attempt_id → user_id, quiz_id, score, attempt_date
@@ -161 +153 @@
 From FD10:
 module_id → course_id, module_title, module_description
-Add: module_title, module_description
-(course_id already belongs to K+)
+Add: course_id, module_title, module_description
+
+From FD9:
+course_id → course_name, course_price, course_status, instructor_id
+Add: course_name, course_price, course_status, instructor_id
+
+From FD2:
+instructor_id → user_id
+user_id already belongs to K+
 
 From FD15:
@@ -178 +177 @@
 
 - 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
+- without ticket_id, support ticket information is lost
+- without category_id, the course-category association is lost
+- without attempt_id, quiz attempt, course content and course information are lost
+- without certificate_id, certificate and enrollment completion information is lost
 
 So, K is a candidate key.
 }}}
+
+=== Discussion about other possible candidate keys
+To prove that there are no other candidate keys for the initial universal relation R, we analyze the attributes that do not appear on the right-hand side of any functional dependency.
+
+A candidate key must be a minimal superkey, meaning that its closure must contain all attributes of R, and no proper subset of it may also determine all attributes.
+
+From the initial set of functional dependencies, the attributes that are not functionally determined by any other attribute are:
+
+payment_id, ticket_id, category_id, attempt_id, certificate_id
+
+Therefore, every candidate key must contain all of these attributes.
+
+The attribute course_id is not required in the candidate key, because it can be functionally determined from attempt_id:
+
+attempt_id → quiz_id
+quiz_id → lesson_id
+lesson_id → module_id
+module_id → course_id
+
+This means that adding course_id to K would make the key non-minimal.
+
+Therefore, the only candidate key, according to the given set of functional dependencies, is:
+
+K = {payment_id, ticket_id, category_id, attempt_id, certificate_id}
+
+Since there is no alternative determinant for any of these required attributes in the initial set of functional dependencies, there are no other candidate keys.
+
 === Chosen primary key
 We select the candidate key above as the primary key of the initial de-normalized relation:
 
 {{{
-K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id}
+K = {payment_id, ticket_id, category_id, attempt_id, certificate_id}
 }}}