Changes between Version 4 and Version 5 of Normalization

Timestamp:

04/21/26 00:43:56 (11 days ago)

Author:

221296

Comment:

--

Normalization

@@ -41 +41 @@
 === Functional dependencies (initial set)
 
+The following functional dependencies represent the initial global set of dependencies that hold in the universal de-normalized relation R for the Online Learning Platform Management System (OLPMS).
+
+They are derived from the semantics of the ER model and the business rules of the system. The goal is to capture the essential determinants that will later guide the normalization process.
+
 User / specialization
 {{{
-FD1: user_id → username, email, name, surname, full_name, password_hash, user_created_at
-FD2: client_id → user_id, is_blocked, blocked_at, blocked_reason
+FD1: user_id → first_name, last_name, email, password, role  
+FD2: instructor_id → user_id  
 FD3: admin_id → user_id
-FD4: owner_id → client_id
-}}}
-
-Notification
-{{{
-FD5: notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
-}}}
-
-Vet Clinic / application
-{{{
-FD6: clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
-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
-}}}
-
-Animal
-{{{
-FD8: animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
-}}}
-
-Listing
-{{{
-FD9: listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
-}}}
-
-Appointment
-{{{
-FD10: appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
-}}}
-
-Health Record
-{{{
-FD11: healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
-}}}
-
-Review and subtypes
-{{{
-FD12: review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
-FD13: review_id → target_user_id
-FD14: review_id → target_clinic_id
-}}}
-
-Favorite Listing
-{{{
-The relation FavoriteListings has composite key (saved_user_id, saved_listing_id)
-and no additional non-trivial functional dependencies.
+}}}
+
+Subscription plan / subscription / payment
+{{{
+FD4: plan_id → plan_name, price, duration_months, access_type  
+FD5: subscription_id → user_id_sub, plan_id_sub, start_date, end_date, subscription_status  
+FD6: payment_id → user_id_pay, subscription_id_pay, amount  
+}}}
+
+Support ticket
+{{{
+FD7: ticket_id → user_id_ticket, admin_id, subject, description, ticket_status, created_at
+}}}
+
+Category / course
+{{{
+FD8: category_id → category_name, category_description  
+FD9: course_id → course_name, course_price, course_status, instructor_id
+}}}
+
+CourseCategory
+{{{
+The relation CourseCategory has composite key (course_id_cc, category_id_cc) and no additional non-trivial functional dependencies.
+}}}
+
+Course content hierarchy
+{{{
+FD10: module_id → course_id_mod, module_title, module_description  
+FD11: lesson_id → module_id_lesson, lesson_title, material  
+FD12: quiz_id → lesson_id_quiz, total_points, passing_score  
+FD13: attempt_id → user_id_attempt, quiz_id_attempt, score, attempt_date
+}}}
+
+Enrollment / certificate
+{{{
+FD14: enrollment_id → user_id_enroll, course_id_enroll, enroll_date, completion_status, progress_percentage  
+FD15: certificate_id → enrollment_id_cert, issue_date, certificate_code, certificate_status
 }}}