Changes between Version 1 and Version 2 of Normalization

Timestamp:

09/04/26 10:56:33 (6 days ago)

Author:

236021

Comment:

--

Normalization

@@ -321 +321 @@
 given FD set:
 
-
-
  **doctor_id** does not appear on the right-hand side of any FD, so it cannot be derived.
 
@@ -387 +385 @@
 
 ----
-
 == 2NF decomposition ==
 
-`R` has a composite primary key of 16 attributes, and none of its non-key attributes depends on the whole 16 attribute keys as each one is reachable from just one or two members of K, either directly (F4, F5, F6, ... etc) or through a short chain (e.g. `proc_result_id -> procedure_id -> procedure_type`, `referral_id -> record_id -> patient_id`). Either way, that is a '''partial dependency''' with respect to K, so '''`R` violates 2NF'''. Examples:
+R has a composite primary key of 16 attributes, and none of its non-key attributes depends on the whole 16 attribute keys as each one is reachable from just one or two members of K, either directly (F4, F5, F6, ... etc) or through a short chain (e.g. proc_result_id -> procedure_id -> procedure_type, referral_id -> record_id -> patient_id). Either way, that is a partial dependency with respect to K, so R violates 2NF. Examples:
 
 {{{
@@ -461 +458 @@
 }}}
 
-`MedicalRecordSymptoms`, `MedicalRecordAllergies` and `PrescriptionRecords` already depend on their whole composite key so these three are already 2NF compliant  and are not split further.
+MedicalRecordSymptoms, MedicalRecordAllergies and PrescriptionRecords already depend on their whole composite key so these three are already 2NF compliant and are not split further.
 
 === 2NF relations ===
 
-* {{{Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id)}}}
-* {{{DoctorLevels(level_id, level_name)}}}
-* {{{Specializations(specialization_id, specialization_name)}}}
-* {{{Departments(department_id, department_name)}}}
-* {{{Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg)}}}
-* {{{Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email)}}}
-* {{{LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email)}}}
-* {{{Users(user_id, users_username, users_password, users_role, users_first_name, users_last_name, is_active)}}}
-* {{{Appointments(appointment_id, appt_patient_id, appt_doctor_id, appointment_date, appointment_time, appointment_status)}}}
-* {{{Diagnoses(diagnosis_id, diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id)}}}
-* {{{Procedures(procedure_id, procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id)}}}
-* {{{ProcedureResults(proc_result_id, proc_result_description, proc_result_date, procedure_id)}}}
-* {{{PerformedProcedures(performed_id, perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes)}}}
-* {{{Prescriptions(prescription_id, medication_name)}}}
-* {{{PrescriptionRestrictions(restriction_id, restriction_description, prescription_id)}}}
-* {{{LabTests(test_id, test_name, test_description, test_cost)}}}
-* {{{LabResults(lab_result_id, lab_result_value, lab_result_date, test_id)}}}
-* {{{PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)}}}
-* {{{MedicalRecords(record_id, patient_id)}}}
-* {{{Allergies(allergy_id, allergy_name, allergy_severity)}}}
-* {{{Symptoms(symptom_id, symptom_name, symptom_description)}}}
-* {{{Referrals(referral_id, referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id)}}}
-* {{{MedicalReports(report_id, report_description, report_date, record_id, report_doctor_id)}}}
-* {{{Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)}}}
-* {{{MedicalRecordSymptoms(record_id, symptom_id, record_symptom_severity)}}}
-* {{{MedicalRecordAllergies(record_id, allergy_id, record_allergy_reaction, record_allergy_severity)}}}
-* {{{PrescriptionRecords(prescription_id, record_id, dosage, frequency, duration, prescription_notes)}}}
+{{{
+Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id)
+DoctorLevels(level_id, level_name)
+Specializations(specialization_id, specialization_name)
+Departments(department_id, department_name)
+Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg)
+Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email)
+LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email)
+Users(user_id, users_username, users_password, users_role, users_first_name, users_last_name, is_active)
+Appointments(appointment_id, appt_patient_id, appt_doctor_id, appointment_date, appointment_time, appointment_status)
+Diagnoses(diagnosis_id, diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id)
+Procedures(procedure_id, procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id)
+ProcedureResults(proc_result_id, proc_result_description, proc_result_date, procedure_id)
+PerformedProcedures(performed_id, perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes)
+Prescriptions(prescription_id, medication_name)
+PrescriptionRestrictions(restriction_id, restriction_description, prescription_id)
+LabTests(test_id, test_name, test_description, test_cost)
+LabResults(lab_result_id, lab_result_value, lab_result_date, test_id)
+PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)
+MedicalRecords(record_id, patient_id)
+Allergies(allergy_id, allergy_name, allergy_severity)
+Symptoms(symptom_id, symptom_name, symptom_description)
+Referrals(referral_id, referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id)
+MedicalReports(report_id, report_description, report_date, record_id, report_doctor_id)
+Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)
+MedicalRecordSymptoms(record_id, symptom_id, record_symptom_severity)
+MedicalRecordAllergies(record_id, allergy_id, record_allergy_reaction, record_allergy_severity)
+PrescriptionRecords(prescription_id, record_id, dosage, frequency, duration, prescription_notes)
+}}}
 
 === Lossless join ===
 
-A split of relation R into R1 and R2 keeps every row reconstructable  as long as the columns R1 and R2 have in common are by themselves
-sufficient to pin down one of the two sides completely:
+A split of relation R into R1 and R2 keeps every row reconstructable as long as the columns R1 and R2 have in common are by themselves sufficient to pin down one of the two sides completely:
 
 {{{
@@ -502 +500 @@
 }}}
 
-Each relation below comes from one FD: its determinant becomes the new table's key, and
-that same determinant stays behind as a foreign key connecting it to the rest. Because
-the FD already guarantees the determinant decides everything in the new table, every
-split passes the lossless-join test automatically.
-
-||= # =||= Relation produced =||= Shared column =||= FD used =||
-|| 1  || `Doctors` || `doctor_id` || F4 ||
-|| 2  || `DoctorLevels` || `level_id` || F1 ||
-|| 3  || `Specializations` || `specialization_id` || F2 ||
-|| 4  || `Departments` || `department_id` || F3 ||
-|| 5  || `Patients` || `patient_id` || F5 ||
-|| 6  || `Admin` || `admin_id` || F6 ||
-|| 7  || `LabTechnician` || `technician_id` || F7 ||
-|| 8  || `Users` || `user_id` || F8 ||
-|| 9  || `Appointments` || `appointment_id` || F9 ||
-|| 10 || `Diagnoses` || `diagnosis_id` || F10 ||
-|| 11 || `Procedures` || `procedure_id` || F11 ||
-|| 12 || `ProcedureResults` || `proc_result_id` || F12 ||
-|| 13 || `PerformedProcedures` || `performed_id` || F13 ||
-|| 14 || `Prescriptions` || `prescription_id` || F14 ||
-|| 15 || `PrescriptionRestrictions` || `restriction_id` || F15 ||
-|| 16 || `LabTests` || `test_id` || F16 ||
-|| 17 || `LabResults` || `lab_result_id` || F17 ||
-|| 18 || `PerformedLabTests` || `performed_test_id` || F18 ||
-|| 19 || `MedicalRecords` || `record_id` || F19 ||
-|| 20 || `Allergies` || `allergy_id` || F20 ||
-|| 21 || `Symptoms` || `symptom_id` || F21 ||
-|| 22 || `Referrals` || `referral_id` || F22 ||
-|| 23 || `MedicalReports` || `report_id` || F23 ||
-|| 24 || `Billing` || `bill_id` || F24 ||
-|| 25 || `MedicalRecordSymptoms` || `{record_id, symptom_id}` || F25 ||
-|| 26 || `MedicalRecordAllergies` || `{record_id, allergy_id}` || F26 ||
-|| 27 || `PrescriptionRecords` || `{prescription_id, record_id}` || F27 ||
-
-Rows 25–27 work the same way, just with two columns acting as the determinant instead of
-one. F25, F26, and F27 each show that the pair alone determines the whole relation and
-that pair is the new table's primary key, so the same test still applies.
-
-Since each of the 27 splits is lossless on its own, the whole chain is too, therefore joining the
-pieces back together, one at a time in reverse order, which rebuilds R exactly.
-
-=== Dependency preservation ===
-
-Every FD from F1 through F27 landed fully inside one relation, its determinant and all
-of its dependents together, never split across two tables and never left out entirely.
-So the FDs that hold across `Doctors`, `DoctorLevels`, and every relation through
-`PrescriptionRecords` are the same as the FDs we started with, which is what makes the
-decomposition dependency preserving.
+Each relation below comes from one FD: its determinant becomes the new table's key, and that same determinant stays behind as a foreign key connecting it to the rest. Because the FD already guarantees the determinant decides everything in the new table, every split passes the lossless-join test automatically.
+
+=== Decomposition into relations ===
+
+'''`Doctors`(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id)'''
+
+R1 = R - {d_first_name, d_last_name, d_email} (level_id, specialization_id, department_id remain in R since they are needed as determinants for steps 2-4)
+
+Lossless join: The shared column with R is `doctor_id`. Since `doctor_id -> Doctors` holds (F4), the relation can be reconstructed via join on `doctor_id`.
+
+Dependency preservation: F4 is preserved entirely within the new relation `Doctors`.
+
+'''`DoctorLevels`(level_id, level_name)'''
+
+R2 = R1 - {level_name, level_id}
+
+Lossless join: Shared column is `level_id`. `level_id -> DoctorLevels` holds (F1), so the relation is reconstructed via join on `level_id`.
+
+Dependency preservation: F1 preserved entirely in `DoctorLevels`.
+
+'''`Specializations`(specialization_id, specialization_name)'''
+
+R3 = R2 - {specialization_name, specialization_id}
+
+Lossless join: Shared column `specialization_id`. `specialization_id -> Specializations` holds (F2).
+
+Dependency preservation: F2 preserved entirely in `Specializations`.
+
+'''`Departments`(department_id, department_name)'''
+
+R4 = R3 - {department_name, department_id}
+
+Lossless join: Shared column `department_id`. `department_id -> Departments` holds (F3).
+
+Dependency preservation: F3 preserved entirely in `Departments`.
+
+'''`Patients`(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg)'''
+
+R5 = R4 - {p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg} (patient_id remains in R - needed later for MedicalRecords)
+
+Lossless join: Shared column `patient_id`. `patient_id -> Patients` holds (F5).
+
+Dependency preservation: F5 preserved entirely in `Patients`.
+
+'''`Admin`(admin_id, admin_username, admin_name, admin_lastname, admin_email)'''
+
+R6 = R5 - {admin_username, admin_name, admin_lastname, admin_email}
+
+Lossless join: Shared column `admin_id`. `admin_id -> Admin` holds (F6).
+
+Dependency preservation: F6 preserved entirely in `Admin`.
+
+'''`LabTechnician`(technician_id, tech_username, tech_name, tech_lastname, tech_email)'''
+
+R7 = R6 - {tech_username, tech_name, tech_lastname, tech_email}
+
+Lossless join: Shared column `technician_id`. `technician_id -> LabTechnician` holds (F7).
+
+Dependency preservation: F7 preserved entirely in `LabTechnician`.
+
+'''`Users`(user_id, users_username, users_password, users_role, users_first_name, users_last_name, is_active)'''
+
+R8 = R7 - {users_username, users_password, users_role, users_first_name, users_last_name, is_active}
+
+Lossless join: Shared column `user_id`. `user_id -> Users` holds (F8).
+
+Dependency preservation: F8 preserved entirely in `Users`.
+
+'''`Appointments`(appointment_id, appt_patient_id, appt_doctor_id, appointment_date, appointment_time, appointment_status)'''
+
+R9 = R8 - {appt_patient_id, appt_doctor_id, appointment_date, appointment_time, appointment_status}
+
+Lossless join: Shared column `appointment_id`. `appointment_id -> Appointments` holds (F9).
+
+Dependency preservation: F9 preserved entirely in `Appointments`.
+
+'''`Diagnoses`(diagnosis_id, diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id)'''
+
+R10 = R9 - {diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id}
+
+Lossless join: Shared column `diagnosis_id`. `diagnosis_id -> Diagnoses` holds (F10).
+
+Dependency preservation: F10 preserved entirely in `Diagnoses`.
+
+'''`Procedures`(procedure_id, procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id)'''
+
+R11 = R10 - {procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id} (procedure_id remains - needed for ProcedureResults)
+
+Lossless join: Shared column `procedure_id`. `procedure_id -> Procedures` holds (F11).
+
+Dependency preservation: F11 preserved entirely in `Procedures`.
+
+'''`ProcedureResults`(proc_result_id, proc_result_description, proc_result_date, procedure_id)'''
+
+R12 = R11 - {proc_result_description, proc_result_date, procedure_id}
+
+Lossless join: Shared column `proc_result_id`. `proc_result_id -> ProcedureResults` holds (F12).
+
+Dependency preservation: F12 preserved entirely in `ProcedureResults`.
+
+'''`PerformedProcedures`(performed_id, perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes)'''
+
+R13 = R12 - {perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes}
+
+Lossless join: Shared column `performed_id`. `performed_id -> PerformedProcedures` holds (F13).
+
+Dependency preservation: F13 preserved entirely in `PerformedProcedures`.
+
+'''`Prescriptions`(prescription_id, medication_name)'''
+
+R14 = R13 - {medication_name} (prescription_id remains - needed for PrescriptionRestrictions and PrescriptionRecords)
+
+Lossless join: Shared column `prescription_id`. `prescription_id -> Prescriptions` holds (F14).
+
+Dependency preservation: F14 preserved entirely in `Prescriptions`.
+
+'''`PrescriptionRestrictions`(restriction_id, restriction_description, prescription_id)'''
+
+R15 = R14 - {restriction_description}
+
+Lossless join: Shared column `restriction_id`. `restriction_id -> PrescriptionRestrictions` holds (F15).
+
+Dependency preservation: F15 preserved entirely in `PrescriptionRestrictions`.
+
+'''`LabTests`(test_id, test_name, test_description, test_cost)'''
+
+R16 = R15 - {test_name, test_description, test_cost} (test_id remains - needed for LabResults)
+
+Lossless join: Shared column `test_id`. `test_id -> LabTests` holds (F16).
+
+Dependency preservation: F16 preserved entirely in `LabTests`.
+
+'''`LabResults`(lab_result_id, lab_result_value, lab_result_date, test_id)'''
+
+R17 = R16 - {lab_result_value, lab_result_date, test_id}
+
+Lossless join: Shared column `lab_result_id`. `lab_result_id -> LabResults` holds (F17).
+
+Dependency preservation: F17 preserved entirely in `LabResults`.
+
+'''`PerformedLabTests`(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)'''
+
+R18 = R17 - {pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes}
+
+Lossless join: Shared column `performed_test_id`. `performed_test_id -> PerformedLabTests` holds (F18).
+
+Dependency preservation: F18 preserved entirely in `PerformedLabTests`.
+
+'''`MedicalRecords`(record_id, patient_id)'''
+
+R19 = R18 - {patient_id} (record_id remains - needed for Referrals, MedicalReports, Billing, and the three composite-key relations)
+
+Lossless join: Shared column `record_id`. `record_id -> MedicalRecords` holds (F19).
+
+Dependency preservation: F19 preserved entirely in `MedicalRecords`.
+
+'''`Allergies`(allergy_id, allergy_name, allergy_severity)'''
+
+R20 = R19 - {allergy_name, allergy_severity}
+
+Lossless join: Shared column `allergy_id`. `allergy_id -> Allergies` holds (F20).
+
+Dependency preservation: F20 preserved entirely in `Allergies`.
+
+'''`Symptoms`(symptom_id, symptom_name, symptom_description)'''
+
+R21 = R20 - {symptom_name, symptom_description}
+
+Lossless join: Shared column `symptom_id`. `symptom_id -> Symptoms` holds (F21).
+
+Dependency preservation: F21 preserved entirely in `Symptoms`.
+
+'''`Referrals`(referral_id, referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id)'''
+
+R22 = R21 - {referral_reason, referral_date, ref_from_doctor_id, ref_to_doctor_id}
+
+Lossless join: Shared column `referral_id`. `referral_id -> Referrals` holds (F22).
+
+Dependency preservation: F22 preserved entirely in `Referrals`.
+
+'''`MedicalReports`(report_id, report_description, report_date, record_id, report_doctor_id)'''
+
+R23 = R22 - {report_description, report_date, report_doctor_id}
+
+Lossless join: Shared column `report_id`. `report_id -> MedicalReports` holds (F23).
+
+Dependency preservation: F23 preserved entirely in `MedicalReports`.
+
+'''`Billing`(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)'''
+
+R24 = R23 - {total_cost, payment_status, payment_date, billing_admin_id}
+
+Lossless join: Shared column `bill_id`. `bill_id -> Billing` holds (F24).
+
+Dependency preservation: F24 preserved entirely in `Billing`.
+
+'''`MedicalRecordSymptoms`(record_id, symptom_id, record_symptom_severity)'''
+
+R25 = R24 - {record_symptom_severity}
+
+Lossless join: Shared column {`record_id`, `symptom_id`}. `{record_id,symptom_id} -> MedicalRecordSymptoms` holds (F25) - the same principle, just with a composite determinant.
+
+Dependency preservation: F25 preserved entirely; the relation already depends on the whole composite key, so it is not split further.
+
+'''`MedicalRecordAllergies`(record_id, allergy_id, record_allergy_reaction, record_allergy_severity)'''
+
+R26 = R25 - {record_allergy_reaction, record_allergy_severity}
+
+Lossless join: Shared column {`record_id`, `allergy_id`}. `{record_id,allergy_id} -> MedicalRecordAllergies` holds (F26).
+
+Dependency preservation: F26 preserved entirely; already depends on the whole composite key.
+
+'''`PrescriptionRecords`(prescription_id, record_id, dosage, frequency, duration, prescription_notes)'''
+
+R27 = R26 - {dosage, frequency, duration, prescription_notes, prescription_id, record_id}
+
+Lossless join: Shared column {`prescription_id`, `record_id`}. `{prescription_id,record_id} -> PrescriptionRecords` holds (F27).
+
+Dependency preservation: F27 preserved entirely; already depends on the whole composite key.
+
+----
+
+'''Final result:''' after step 27, R27 = { doctor_id, admin_id, technician_id, user_id, appointment_id, diagnosis_id, proc_result_id, performed_id, restriction_id, lab_result_id, performed_test_id, allergy_id, symptom_id, referral_id, report_id, bill_id } - exactly the 16 PK attributes, nothing more. This confirms the decomposition is complete: everything partially dependent has been removed, leaving only the composite key that "links" the rest through foreign keys in all 27 new relations.
+
+Since each of the 27 splits is lossless on its own, the whole chain of decompositions is lossless too (joining all the pieces back together, one at a time, reconstructs R exactly). Likewise, every FD from F1 through F27 is fully contained within a single relation, none split across tables, which means the decomposition is also dependency preserving.
 
 ----
 == 3NF check ==
 
-2NF removed **partial** dependencies. We now check whether any **transitive**
-dependency remains so a non-key attribute reachable only through another non-key
-attribute. Because the 2NF grouping step split on every determinant found in F1–F27,
-no relation ended up keeping a borrowed descriptive attribute alongside the foreign
-key that points to where it actually lives — so no transitive dependency survived
-into the 2NF output. A few examples confirm this:
-
-`Doctors` keeps level_id, specialization_id, department_id as foreign keys, but
-level_name, specialization_name, department_name stay in `DoctorLevels`,
-`Specializations`, `Departments` are never duplicated back into `Doctors`.
-
-`ProcedureResults` keeps only the foreign key procedure_id, the procedure's own
-procedure_type, procedure_cost, etc. stay in `Procedures`. `Procedures`
-keeps only proc_diagnosis_id, the diagnosis's diagnosis_name and diagnosis_description
-stay in `Diagnoses`.
-
-`LabResults` keeps only the foreign key test_id. test_name, test_description and
-test_cost stay in `LabTests`.
-
-`Referrals`, `MedicalReports`, `Billing`, `Appointments`, `PerformedProcedures`,
-`PerformedLabTests`, `PrescriptionRestrictions` each carry only foreign keys back to
-record_id / doctor_id / patient_id / admin_id / technician_id / procedure_id
-/ test_id / prescription_id and never the descriptive attributes belonging to the
-entity on the other end of that key. 
-`MedicalRecordSymptoms`, `MedicalRecordAllergies`,
-`PrescriptionRecords` have composite keys with no non-key attribute that could point
-through another non-key attribute, so they aren't affected either way.
-
-In conclusion, every non-key attribute in every relation depends only and
-fully on that relation's own key. The schema is in '''3NF''', and since this is a
-by-product of how the 2NF split was done rather than a separate fix meaning that no further
-decomposition is needed at this stage.
+2NF removed partial dependencies. We now check whether any transitive dependency remains — i.e. a non-key attribute reachable only through another non-key attribute. Because the 2NF grouping step split on every determinant found in F1–F27, no relation ended up keeping a borrowed descriptive attribute alongside the foreign key that points to where it actually lives — so no transitive dependency survived into the 2NF output. A few examples confirm this:
+
+* `Doctors` keeps level_id, specialization_id, department_id as foreign keys, but level_name, specialization_name, and department_name stay in `DoctorLevels`, `Specializations`, and `Departments` — they are never duplicated back into `Doctors`.
+* `ProcedureResults` keeps only the foreign key procedure_id, the procedure's own procedure_type, procedure_cost, etc. stay in `Procedures`. `Procedures` keeps only proc_diagnosis_id, the diagnosis's diagnosis_name and diagnosis_description stay in `Diagnoses`.
+* `LabResults` keeps only the foreign key test_id. test_name, test_description and test_cost stay in `LabTests`.
+* `Referrals`, `MedicalReports`, `Billing`, `Appointments`, `PerformedProcedures`, `PerformedLabTests`, `PrescriptionRestrictions` each carry only foreign keys back to record_id / doctor_id / patient_id / admin_id / technician_id / procedure_id / test_id / prescription_id and never the descriptive attributes belonging to the entity on the other end of that key. `MedicalRecordSymptoms`, `MedicalRecordAllergies`, `PrescriptionRecords` have composite keys with no non-key attribute that could point through another non-key attribute, so they aren't affected either way.
+
+In conclusion, every non-key attribute in every relation depends only and fully on that relation's own key. The schema is in 3NF. This is a by-product of how the 2NF split was done, rather than something that required a separate fix — so no further decomposition is needed at this stage.
 
 == BCNF check ==
 
-BCNF requires every non-trivial determinant to be a superkey. This is stronger than
-3NF, which still allows a non-superkey determinant as long as the dependent attribute
-is itself part of some candidate key. Below is a check of each 3NF relation. The primary key is bolded.
+BCNF requires every non-trivial determinant to be a superkey. This is stronger than 3NF, which still allows a non-superkey determinant as long as the dependent attribute is itself part of some candidate key. Below is a check of each 3NF relation. The primary key is bolded.
 
 ||= Relation =||= Key =||= BCNF? =||
@@ -622 +771 @@
 || `PrescriptionRecords` || '''{prescription_id, record_id}''' || Yes ||
 
-`p_email` (`patients.email_address`) is left off as an alternate key as it carries a `UNIQUE`
-constraint but is nullable, so it doesn't function as a true candidate key under the
-relational model.
-
-No relation has a non-key attribute determining part of a candidate key without itself being a superkey so the schema **satisfies BCNF**.
+`p_email` is left off as an alternate key because it carries a `UNIQUE` constraint but is nullable, so it doesn't function as a true candidate key under the relational model.
+
+No relation has a non-key attribute determining part of a candidate key without itself being a superkey, so the schema **satisfies BCNF**.
 
 ----
@@ -632 +779 @@
 == 4NF check ==
 
-BCNF only removes anomalies caused by functional dependencies. This domain still has
-the fourteen pure many-to-many pairs listed at the end of the FD section, and none of
-them was involved in any FD above, so BCNF does not evaluate them at all.
-
-Four entities in this schema each sit at the center of more than one independent
-many-to-many relationship at once. That's exactly the situation that produces a
-genuine multivalued dependency. If you tried to store two of those relationships in
-the same table, you'd be forced to repeat every combination of the two, even though
-the two facts have nothing to do with each other.
-
-- A **medical record** can be linked to several diagnoses, several doctors, several
-procedures, and several lab results, and each of those four lists grows and shrinks
-on its own. Adding another diagnosis to a record says nothing about how many doctors,
-procedures, or lab results are attached to that same record.
-
-- A **diagnosis** can be linked to several typical symptoms and several procedures used to
-investigate or treat it. Neither list has any bearing on the other.
-
-- A **procedure** can be linked to several specializations qualified to perform it, several
-departments that perform it, and several bills it appears on. Its link to a diagnosis
-was already covered above and isn't repeated here.
-
-- A **patient** can be linked to several allergies and several symptoms, tracked completely
-separately from one another.
+BCNF only removes anomalies caused by functional dependencies. This domain still has the fourteen pure many-to-many pairs listed at the end of the FD section, and none of them was involved in any FD above, so BCNF does not evaluate them at all.
+
+Four entities in this schema each sit at the center of more than one independent many-to-many relationship at once. That's exactly the situation that produces a genuine multivalued dependency. If you tried to store two of those relationships in the same table, you'd be forced to repeat every combination of the two, even though the two facts have nothing to do with each other.
+
+* A '''medical record''' can be linked to several diagnoses, several doctors, several procedures, and several lab results, and each of those four lists grows and shrinks on its own. Adding another diagnosis to a record says nothing about how many doctors, procedures, or lab results are attached to that same record.
+
+* A '''diagnosis''' can be linked to several typical symptoms and several procedures used to investigate or treat it. Neither list has any bearing on the other.
+
+* A '''procedure''' can be linked to several specializations qualified to perform it, several departments that perform it, and several bills it appears on. Its link to a diagnosis was already covered above and isn't repeated here.
+
+* A '''patient''' can be linked to several allergies and several symptoms, tracked completely separately from one another.
 
 This gives eleven multivalued dependencies:
@@ -676 +810 @@
 }}}
 
-The remaining three pairs, `{test_id, bill_id}`, `{report_id, lab_result_id}`, and
-`{allergy_id, restriction_id}`, are different. Each one is the only many-to-many link
-at its anchor, so there's no competing relationship to collide with. They still need
-their own table, since a plain many-to-many link can never be expressed as a
-functional dependency, but they aren't 4NF violations the way the four groups above
-are.
-
-None of the MVDs above are trivial. In each case, the right side isn't already part
-of the left side, and the left side isn't a key of any table that currently holds
-both sides together. So '''4NF is violated''' until all fourteen pairs, the eleven
-grouped above plus these three standalone ones, each get their own table.
-
+The remaining three pairs, `{test_id, bill_id}`, `{report_id, lab_result_id}`, and `{allergy_id, restriction_id}`, are different. Each one is the only many-to-many link at its anchor, so there's no competing relationship to collide with. They still need their own table, since a plain many-to-many link can never be expressed as a functional dependency, but they aren't 4NF violations the way the four groups above are.
+
+None of the MVDs above is trivial. In each case, the right side isn't already part of the left side, and the left side isn't a key of any table that currently holds both sides together. So '''4NF is violated''' until all fourteen pairs — the eleven grouped above plus these three standalone ones — each gets their own table.
 
 ----
@@ -693 +818 @@
 == MVD check ==
 
-Nothing is actually being split here. These fourteen pairs were already separate,
-attribute only tables back in the FD section, since none of them ever had an extra
-column that would tie them to something else during 2NF. This section just confirms
-that keeping them separate was correct. If any two had been merged that would create redundant, repeated rows. Keeping each pair on its own avoids that.
-
-* {{{DiagnosisMedicalRecords(diagnosis_id, record_id)}}}
-* {{{DoctorMedicalRecords(doctor_id, record_id)}}}
-* {{{MedicalRecordProcedures(record_id, procedure_id)}}}
-* {{{MedicalRecordLabResults(record_id, lab_result_id)}}}
-* {{{PatientAllergies(patient_id, allergy_id)}}}
-* {{{PatientSymptoms(patient_id, symptom_id)}}}
-* {{{DiagnosisSymptoms(diagnosis_id, symptom_id)}}}
-* {{{DiagnosisProcedures(diagnosis_id, procedure_id)}}}
-* {{{SpecializationProcedures(specialization_id, procedure_id)}}}
-* {{{DepartmentProcedures(department_id, procedure_id)}}}
-* {{{BillingProcedures(bill_id, procedure_id)}}}
-* {{{BillingLabTests(bill_id, test_id)}}}
-* {{{MedicalReportLabResults(report_id, lab_result_id)}}}
-* {{{AllergyPrescriptionRestrictions(allergy_id, restriction_id)}}}
-
-'''Lossless join'''
-The general MVD theorem guarantees that for any relation
-containing `X`, `Y`, and other attributes `Z` where `X ->> Y` holds, splitting into
-`(X, Y)` and `(X, Z)` always reconstructs the original without loss. Since none of
-these fourteen pairs was ever combined with a competing multivalued group in any
-table we produced, that theorem is satisfied trivially rather than needing to be
-invoked as an active repair.
-
-'''Dependency preservation''' 
-None of these fourteen relations carries a non-trivial
-FD of its own. Both attributes are needed together just to identify a membership row,
-so there's nothing beyond the membership fact itself that needs preserving.
+Nothing is actually being split here. These fourteen pairs were already separate, attribute-only tables back in the FD section, since none of them ever had an extra column that would tie them to something else during 2NF. This section just confirms that keeping them separate was correct. If any two had been merged, that would create redundant, repeated rows. Keeping each pair on its own avoids that.
+
+=== Lossless join & dependency preservation per relation ===
+
+'''`DiagnosisMedicalRecords`(diagnosis_id, record_id)'''
+
+MVD: `record_id ->> diagnosis_id`
+
+Lossless join: The general MVD theorem states that for a relation containing `X`, `Y`, and other attributes `Z` where `X ->> Y` holds, splitting into `(X, Y)` and `(X, Z)` reconstructs the original without loss. Here `X = record_id`, `Y = diagnosis_id`, so `(record_id, diagnosis_id)` and the rest of `MedicalRecords`'s attributes split losslessly.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`DoctorMedicalRecords`(doctor_id, record_id)'''
+
+MVD: `record_id ->> doctor_id`
+
+Lossless join: `X = record_id`, `Y = doctor_id`. `record_id ->> doctor_id` holds, so `(record_id, doctor_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`MedicalRecordProcedures`(record_id, procedure_id)'''
+
+MVD: `record_id ->> procedure_id`
+
+Lossless join: `X = record_id`, `Y = procedure_id`. `record_id ->> procedure_id` holds, so `(record_id, procedure_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`MedicalRecordLabResults`(record_id, lab_result_id)'''
+
+MVD: `record_id ->> lab_result_id`
+
+Lossless join: `X = record_id`, `Y = lab_result_id`. `record_id ->> lab_result_id` holds, so `(record_id, lab_result_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`DiagnosisSymptoms`(diagnosis_id, symptom_id)'''
+
+MVD: `diagnosis_id ->> symptom_id`
+
+Lossless join: `X = diagnosis_id`, `Y = symptom_id`. `diagnosis_id ->> symptom_id` holds, so `(diagnosis_id, symptom_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`DiagnosisProcedures`(diagnosis_id, procedure_id)'''
+
+MVD: `diagnosis_id ->> procedure_id`
+
+Lossless join: `X = diagnosis_id`, `Y = procedure_id`. `diagnosis_id ->> procedure_id` holds, so `(diagnosis_id, procedure_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`SpecializationProcedures`(specialization_id, procedure_id)'''
+
+MVD: `procedure_id ->> specialization_id`
+
+Lossless join: `X = procedure_id`, `Y = specialization_id`. `procedure_id ->> specialization_id` holds, so `(procedure_id, specialization_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`DepartmentProcedures`(department_id, procedure_id)'''
+
+MVD: `procedure_id ->> department_id`
+
+Lossless join: `X = procedure_id`, `Y = department_id`. `procedure_id ->> department_id` holds, so `(procedure_id, department_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`BillingProcedures`(bill_id, procedure_id)'''
+
+MVD: `procedure_id ->> bill_id`
+
+Lossless join: `X = procedure_id`, `Y = bill_id`. `procedure_id ->> bill_id` holds, so `(procedure_id, bill_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`PatientAllergies`(patient_id, allergy_id)'''
+
+MVD: `patient_id ->> allergy_id`
+
+Lossless join: `X = patient_id`, `Y = allergy_id`. `patient_id ->> allergy_id` holds, so `(patient_id, allergy_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`PatientSymptoms`(patient_id, symptom_id)'''
+
+MVD: `patient_id ->> symptom_id`
+
+Lossless join: `X = patient_id`, `Y = symptom_id`. `patient_id ->> symptom_id` holds, so `(patient_id, symptom_id)` splits losslessly from the rest.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+----
+
+The remaining three pairs are standalone many-to-many links rather than part of a competing MVD group, so they were never at risk of collision, but they still need their own table since a plain many-to-many relationship can't be expressed as a functional dependency:
+
+'''`BillingLabTests`(bill_id, test_id)'''
+
+Lossless join: `{bill_id, test_id}` is the only many-to-many link at either anchor, so this pair was never combined with a competing multivalued group in any table produced. The pairing is kept in its own relation, which trivially reconstructs via join on `{bill_id, test_id}`.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`MedicalReportLabResults`(report_id, lab_result_id)'''
+
+Lossless join: `{report_id, lab_result_id}` is the only many-to-many link at either anchor, kept in its own relation, trivially reconstructed via join on `{report_id, lab_result_id}`.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+
+'''`AllergyPrescriptionRestrictions`(allergy_id, restriction_id)'''
+
+Lossless join: `{allergy_id, restriction_id}` is the only many-to-many link at either anchor, kept in its own relation, trivially reconstructed via join on `{allergy_id, restriction_id}`.
+
+Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row.
+----
 
 The schema satisfies '''4NF'''.
@@ -769 +976 @@
 * {{{MedicalReportLabResults(report_id, lab_result_id)}}}
 * {{{AllergyPrescriptionRestrictions(allergy_id, restriction_id)}}}
-
 ----
 
 == Conclusion ==
 
-Decomposing the de-normalized relation from scratch, following only the formal rules through 1NF, 2NF, 3NF, BCNF, and 4NF, produced the same 41 relations already present in the Phase 2 design — same keys, same columns, same many-to-many tables. The normalization process confirms the same structural design obtained from the ER model in Phase 2.
+Decomposing the de-normalized relation from scratch, following only the formal rules through 1NF, 2NF, 3NF, BCNF, and 4NF, produced the same 41 relations already present in the Phase 2 design the same keys, same columns, same many-to-many tables. The normalization process confirms the same structural design obtained from the ER model in Phase 2.