| 89 | | **user_id** also has four nullable foreign keys: patient_id, doctor_id, admin_id, |
| 90 | | and technician_id. Only one of these is ever filled in for a given user, depending |
| 91 | | on their role, and the rest stay null. Because of that, `user_id` is not treated as |
| 92 | | determining any of the four below, since a login row only optionally points to one |
| 93 | | profile row rather than always pointing to the same one. This is the same subtype |
| 94 | | idea used for `Admin`, `Clients`, and `Owners` elsewhere, except in our database it keeps all |
| 95 | | four subtypes together in one `users` table instead of giving each one its own table. |
| | 89 | Unlike a typical subtype design where the supertype (`Users`) would hold nullable |
| | 90 | foreign keys to each subtype, this schema does it the other way around: `Doctors`, |
| | 91 | `Patients`, `Admin`, and `LabTechnician` each carry their own mandatory `user_id` |
| | 92 | foreign key back to `Users`, so it is the subtype row that points up to its login |
| | 93 | row, not the reverse. Because of that, `doctor_id -> user_id`, `patient_id -> user_id`, |
| | 94 | `admin_id -> user_id`, and `technician_id -> user_id` all hold (folded into F4, F5, F6, |
| | 95 | F7 above), while `user_id` itself does not determine any of `doctor_id`, `patient_id`, |
| | 96 | `admin_id`, or `technician_id`, since a `Users` row need not have any subtype row |
| | 97 | pointing to it at all (e.g. a system administrator account with no linked profile). |
| | 98 | This is the same subtype idea used for `Admin`, `Clients`, and `Owners` elsewhere, |
| | 99 | except here the four subtypes each own the foreign key to their shared supertype |
| | 100 | `users` table instead of the supertype holding a pointer to each of them. |
| 180 | | `Many-to-many association tables (no dependent attribute trivial so FDs only on the full pair)` |
| 181 | | |
| 182 | | {{{ |
| 183 | | {diagnosis_id, record_id} {doctor_id, record_id} {record_id, procedure_id} |
| 184 | | {record_id, lab_result_id} {patient_id, allergy_id} {patient_id, symptom_id} |
| 185 | | {diagnosis_id, symptom_id} {diagnosis_id, procedure_id} {specialization_id, procedure_id} |
| | 185 | `Many-to-many association tables (no dependent attribute, trivial so FDs only on the full pair)` |
| | 186 | |
| | 187 | {{{ |
| | 188 | {record_id, procedure_id} {record_id, lab_result_id} {patient_id, allergy_id} |
| | 189 | {patient_id, symptom_id} {diagnosis_id, symptom_id} {specialization_id, procedure_id} |
| 204 | | prescription_id, record_id, patient_id, level_id, specialization_id, department_id) |
| 205 | | does not need to be included in the key separately as it is already recoverable once |
| 206 | | its determinant is present. |
| | 208 | prescription_id, record_id, patient_id, level_id, specialization_id, department_id, |
| | 209 | and — because of the corrected foreign-key direction discussed under `User` above — |
| | 210 | user_id) does not need to be included in the key separately as it is already |
| | 211 | recoverable once its determinant is present. In particular, `user_id` is reachable |
| | 212 | via `doctor_id -> user_id` (F4) the moment `doctor_id` is in the key, so unlike an |
| | 213 | earlier draft of this analysis that assumed `Users` held the foreign keys, `user_id` |
| | 214 | is *not* one of the irreducible identifiers below. |
| 389 | | 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: |
| 390 | | |
| 391 | | {{{ |
| 392 | | doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id |
| | 400 | R has a composite primary key of 15 attributes, and none of its non-key attributes depends on the whole 15 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: |
| | 401 | |
| | 402 | {{{ |
| | 403 | doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id, user_id |
| 412 | | patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg |
| 413 | | |
| 414 | | admin_id -> admin_username, admin_name, admin_lastname, admin_email |
| 415 | | |
| 416 | | technician_id -> tech_username, tech_name, tech_lastname, tech_email |
| | 423 | patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg, user_id |
| | 424 | |
| | 425 | admin_id -> admin_username, admin_name, admin_lastname, admin_email, user_id |
| | 426 | |
| | 427 | technician_id -> tech_username, tech_name, tech_lastname, tech_email, user_id |
| 469 | | Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg |
| 470 | | Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email |
| 471 | | LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email |
| | 480 | Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg, user_id) |
| | 481 | Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email, user_id) |
| | 482 | LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email, user_id) |
| 506 | | '''`Doctors`(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id |
| 507 | | |
| 508 | | 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) |
| | 517 | '''`Doctors`(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id, user_id)''' |
| | 518 | |
| | 519 | R1 = R - {d_first_name, d_last_name, d_email} (level_id, specialization_id, department_id, user_id remain in R since they are needed as determinants for steps 2-4 and for `Users`) |
| 538 | | '''`Patients`(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg |
| 539 | | |
| 540 | | 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 |
| | 549 | '''`Patients`(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg, user_id)''' |
| | 550 | |
| | 551 | 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; user_id remains - needed for `Users`) |
| 546 | | '''`Admin`(admin_id, admin_username, admin_name, admin_lastname, admin_email |
| 547 | | |
| 548 | | R6 = R5 - {admin_username, admin_name, admin_lastname, admin_email} |
| | 557 | '''`Admin`(admin_id, admin_username, admin_name, admin_lastname, admin_email, user_id)''' |
| | 558 | |
| | 559 | R6 = R5 - {admin_username, admin_name, admin_lastname, admin_email} (user_id remains - needed for `Users`) |
| 554 | | '''`LabTechnician`(technician_id, tech_username, tech_name, tech_lastname, tech_email |
| 555 | | |
| 556 | | R7 = R6 - {tech_username, tech_name, tech_lastname, tech_email} |
| | 565 | '''`LabTechnician`(technician_id, tech_username, tech_name, tech_lastname, tech_email, user_id)''' |
| | 566 | |
| | 567 | R7 = R6 - {tech_username, tech_name, tech_lastname, tech_email} (user_id remains - needed for `Users`) |
| 564 | | R8 = R7 - {users_username, users_password, users_role, users_first_name, users_last_name, is_active} |
| 565 | | |
| 566 | | Lossless join: Shared column `user_id`. `user_id -> Users` holds (F8) |
| | 575 | R8 = R7 - {users_username, users_password, users_role, users_first_name, users_last_name, is_active, user_id} (user_id itself is dropped here too, since once `Users` is split off, every remaining relation that needs it — `Doctors`, `Patients`, `Admin`, `LabTechnician` — already carries it as its own foreign key) |
| | 576 | |
| | 577 | Lossless join: Shared column `user_id`. `user_id -> Users` holds (F8), and `user_id` is already present in `Doctors`, `Patients`, `Admin`, and `LabTechnician` from steps 1, 5, 6, 7, so the join back is via any of those four tables' `user_id` column. |
| 586 | | '''`Procedures`(procedure_id, procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id)''' |
| 587 | | |
| 588 | | R11 = R10 - {procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id, proc_diagnosis_id} (procedure_id remains - needed for ProcedureResults) |
| | 597 | '''`Procedures`(procedure_id, procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id)''' |
| | 598 | |
| | 599 | R11 = R10 - {procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_id} (procedure_id remains - needed for ProcedureResults) |
| 724 | | '''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. |
| | 735 | '''Final result:''' after step 27, R27 = { doctor_id, admin_id, technician_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 15 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. |
| 733 | | * `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`. |
| 734 | | * `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`. |
| | 744 | * `Doctors` keeps level_id, specialization_id, department_id, and user_id as foreign keys, but level_name, specialization_name, department_name, and the rest of the login account's own fields stay in `DoctorLevels`, `Specializations`, `Departments`, and `Users` respectively — they are never duplicated back into `Doctors`. |
| | 745 | * `ProcedureResults` keeps only the foreign key procedure_id, the procedure's own procedure_type, procedure_cost, etc. stay in `Procedures`. |
| 748 | | || `Doctors` || '''doctor_id''', alt. key `d_email` || Yes || |
| 749 | | || `Patients` || '''patient_id''', alt. key `embg` || Yes || |
| 750 | | || `Admin` || '''admin_id''', alt. keys `admin_username`, `admin_email` |
| 751 | | || `LabTechnician` || '''technician_id''', alt. keys `tech_username`, `tech_email` |
| | 759 | || `Doctors` || '''doctor_id''', alt. keys `d_email`, `user_id` || Yes || |
| | 760 | || `Patients` || '''patient_id''', alt. keys `embg`, `user_id` || Yes || |
| | 761 | || `Admin` || '''admin_id''', alt. keys `admin_username`, `admin_email`, `user_id` || Yes || |
| | 762 | || `LabTechnician` || '''technician_id''', alt. keys `tech_username`, `tech_email`, `user_id` || Yes || |
| 781 | | 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. |
| 782 | | |
| 783 | | 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. |
| 784 | | |
| 785 | | * 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. |
| 786 | | |
| 787 | | * 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. |
| 788 | | |
| 789 | | * 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. |
| | 792 | BCNF only removes anomalies caused by functional dependencies. This domain still has the twelve 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. |
| | 793 | |
| | 794 | Three 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. |
| | 795 | |
| | 796 | * A '''medical record''' can be linked to several diagnoses, several procedures, and several lab results, and each of those three lists grows and shrinks on its own. Adding another diagnosis to a record says nothing about how many procedures or lab results are attached to that same record. (A record's link to its doctors is not modeled as a separate many-to-many relationship in this schema — a doctor is reached only indirectly, through the diagnoses, procedures and reports already tied to that record — so there is no fourth competing group here.) |
| | 797 | |
| | 798 | * A '''procedure''' can be linked to several specializations qualified to perform it, several departments that perform it, and several bills it appears on. |
| 812 | | 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. |
| 813 | | |
| 814 | | 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. |
| | 817 | The remaining four pairs, `{diagnosis_id, symptom_id}`, `{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 three groups above are. |
| | 818 | |
| | 819 | 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 twelve pairs — the eight grouped above plus these four standalone ones — each gets their own table. |
| | 853 | '''`SpecializationProcedures`(specialization_id, procedure_id)''' |
| | 854 | |
| | 855 | MVD: `procedure_id ->> specialization_id` |
| | 856 | |
| | 857 | Lossless join: `X = procedure_id`, `Y = specialization_id`. `procedure_id ->> specialization_id` holds, so `(procedure_id, specialization_id)` splits losslessly from the rest. |
| | 858 | |
| | 859 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| | 860 | |
| | 861 | '''`DepartmentProcedures`(department_id, procedure_id)''' |
| | 862 | |
| | 863 | MVD: `procedure_id ->> department_id` |
| | 864 | |
| | 865 | Lossless join: `X = procedure_id`, `Y = department_id`. `procedure_id ->> department_id` holds, so `(procedure_id, department_id)` splits losslessly from the rest. |
| | 866 | |
| | 867 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| | 868 | |
| | 869 | '''`BillingProcedures`(bill_id, procedure_id)''' |
| | 870 | |
| | 871 | MVD: `procedure_id ->> bill_id` |
| | 872 | |
| | 873 | Lossless join: `X = procedure_id`, `Y = bill_id`. `procedure_id ->> bill_id` holds, so `(procedure_id, bill_id)` splits losslessly from the rest. |
| | 874 | |
| | 875 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| | 876 | |
| | 877 | '''`PatientAllergies`(patient_id, allergy_id)''' |
| | 878 | |
| | 879 | MVD: `patient_id ->> allergy_id` |
| | 880 | |
| | 881 | Lossless join: `X = patient_id`, `Y = allergy_id`. `patient_id ->> allergy_id` holds, so `(patient_id, allergy_id)` splits losslessly from the rest. |
| | 882 | |
| | 883 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| | 884 | |
| | 885 | '''`PatientSymptoms`(patient_id, symptom_id)''' |
| | 886 | |
| | 887 | MVD: `patient_id ->> symptom_id` |
| | 888 | |
| | 889 | Lossless join: `X = patient_id`, `Y = symptom_id`. `patient_id ->> symptom_id` holds, so `(patient_id, symptom_id)` splits losslessly from the rest. |
| | 890 | |
| | 891 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| | 892 | |
| | 893 | ---- |
| | 894 | |
| | 895 | The remaining four 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: |
| | 896 | |
| 858 | | MVD: `diagnosis_id ->> symptom_id` |
| 859 | | |
| 860 | | Lossless join: `X = diagnosis_id`, `Y = symptom_id`. `diagnosis_id ->> symptom_id` holds, so `(diagnosis_id, symptom_id)` splits losslessly from the rest. |
| 861 | | |
| 862 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 863 | | |
| 864 | | '''`DiagnosisProcedures`(diagnosis_id, procedure_id)''' |
| 865 | | |
| 866 | | MVD: `diagnosis_id ->> procedure_id` |
| 867 | | |
| 868 | | Lossless join: `X = diagnosis_id`, `Y = procedure_id`. `diagnosis_id ->> procedure_id` holds, so `(diagnosis_id, procedure_id)` splits losslessly from the rest. |
| 869 | | |
| 870 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 871 | | |
| 872 | | '''`SpecializationProcedures`(specialization_id, procedure_id)''' |
| 873 | | |
| 874 | | MVD: `procedure_id ->> specialization_id` |
| 875 | | |
| 876 | | Lossless join: `X = procedure_id`, `Y = specialization_id`. `procedure_id ->> specialization_id` holds, so `(procedure_id, specialization_id)` splits losslessly from the rest. |
| 877 | | |
| 878 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 879 | | |
| 880 | | '''`DepartmentProcedures`(department_id, procedure_id)''' |
| 881 | | |
| 882 | | MVD: `procedure_id ->> department_id` |
| 883 | | |
| 884 | | Lossless join: `X = procedure_id`, `Y = department_id`. `procedure_id ->> department_id` holds, so `(procedure_id, department_id)` splits losslessly from the rest. |
| 885 | | |
| 886 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 887 | | |
| 888 | | '''`BillingProcedures`(bill_id, procedure_id)''' |
| 889 | | |
| 890 | | MVD: `procedure_id ->> bill_id` |
| 891 | | |
| 892 | | Lossless join: `X = procedure_id`, `Y = bill_id`. `procedure_id ->> bill_id` holds, so `(procedure_id, bill_id)` splits losslessly from the rest. |
| 893 | | |
| 894 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 895 | | |
| 896 | | '''`PatientAllergies`(patient_id, allergy_id)''' |
| 897 | | |
| 898 | | MVD: `patient_id ->> allergy_id` |
| 899 | | |
| 900 | | Lossless join: `X = patient_id`, `Y = allergy_id`. `patient_id ->> allergy_id` holds, so `(patient_id, allergy_id)` splits losslessly from the rest. |
| 901 | | |
| 902 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 903 | | |
| 904 | | '''`PatientSymptoms`(patient_id, symptom_id)''' |
| 905 | | |
| 906 | | MVD: `patient_id ->> symptom_id` |
| 907 | | |
| 908 | | Lossless join: `X = patient_id`, `Y = symptom_id`. `patient_id ->> symptom_id` holds, so `(patient_id, symptom_id)` splits losslessly from the rest. |
| 909 | | |
| 910 | | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 911 | | |
| 912 | | ---- |
| 913 | | |
| 914 | | 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: |
| | 899 | Lossless join: `{diagnosis_id, symptom_id}` is the only many-to-many link at either anchor (diagnosis no longer links to procedures directly), so it 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 `{diagnosis_id, symptom_id}`. |
| | 900 | |
| | 901 | Dependency preservation: This relation carries no non-trivial FD of its own. Both attributes are needed together simply to identify a membership row. |
| 940 | | * {{{Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id |
| 941 | | * {{{Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg |
| 942 | | * {{{Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email |
| 943 | | * {{{LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email |
| | 927 | * {{{Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id, user_id)}}} |
| | 928 | * {{{Patients(patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg, user_id)}}} |
| | 929 | * {{{Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email, user_id)}}} |
| | 930 | * {{{LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email, user_id)}}} |
| 982 | | 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. |
| | 967 | Decomposing the de-normalized relation from scratch, following only the formal rules through 1NF, 2NF, 3NF, BCNF, and 4NF, produced the same 39 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. |
