Changes between Version 12 and Version 13 of Normalization
- Timestamp:
- 04/21/26 15:40:37 (11 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Normalization
v12 v13 52 52 Subscription plan / subscription / payment 53 53 {{{ 54 FD4: plan_id → plan_name, price, duration_months, description, access_type54 FD4: plan_id → plan_name, price, duration_months, plan_description, access_type 55 55 FD5: subscription_id → user_id, plan_id, start_date, end_date, subscription_status 56 56 FD6: payment_id → user_id, subscription_id, amount … … 59 59 Support ticket 60 60 {{{ 61 FD7: ticket_id → user_id, admin_id, subject, description_ticket, ticket_status, created_at61 FD7: ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at 62 62 }}} 63 63 … … 68 68 }}} 69 69 70 CourseCategory 70 CourseCategory relation 71 71 {{{ 72 72 The relation CourseCategory has composite key (course_id, category_id) and no additional non-trivial functional dependencies. … … 103 103 === Closure proof for K 104 104 {{{ 105 105 106 Let K = {payment_id, ticket_id, course_id, category_id, attempt_id, certificate_id} 106 107 … … 117 118 118 119 From FD4: 119 plan_id → plan_name, price, duration_months, access_type120 Add: plan_name, price, duration_months, access_type120 plan_id → plan_name, price, duration_months, plan_description, access_type 121 Add: plan_name, price, duration_months, plan_description, access_type 121 122 122 123 From FD1: … … 125 126 126 127 From FD7: 127 ticket_id → admin_id, subject, description, ticket_status, created_at, user_id128 Add: admin_id, subject, description, ticket_status, created_at128 ticket_id → user_id, admin_id, subject, ticket_description, ticket_status, created_at 129 Add: admin_id, subject, ticket_description, ticket_status, created_at 129 130 (user_id already belongs to K+) 130 131 … … 159 160 160 161 From FD10: 161 module_id → module_title, module_description, course_id162 module_id → course_id, module_title, module_description 162 163 Add: module_title, module_description 163 164 (course_id already belongs to K+) … … 171 172 Add: enroll_date, completion_status, progress_percentage 172 173 (user_id and course_id already belong to K+) 173 }}}174 174 175 175 Therefore, K+ contains all attributes of the universal relation R, so K is a superkey. 176 176 177 177 K is also minimal, because removing any attribute from K would cause the loss of at least one independent component of the universal relation: 178 178 179 - without payment_id, payment/subscription information is lost 179 - without ticket_id, support ticket information is lost180 - Without ticket_id, support ticket information is lost 180 181 - without course_id or category_id, the course-category association is lost 181 - without attempt_id, quiz attempt and course content assessment information is lost182 - without certificate_id, certificateand enrollment completion information is lost183 184 So, K is a candidate key. .182 - without attempt_id, quiz attempt, and course content assessment information is lost 183 - Without certificate_id, certificate, and enrollment completion information is lost 184 185 So, K is a candidate key. 185 186 186 187 === Chosen primary key
