| Version 1 (modified by , 13 days ago) ( diff ) |
|---|
Normalization
De-normalized database form
Global set of attributes
R = {
doctor_id, d_first_name, d_last_name, d_email,
level_id, level_name,
specialization_id, specialization_name,
department_id, department_name,
patient_id, p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg,
admin_id, admin_username, admin_name, admin_lastname, admin_email,
technician_id, tech_username, tech_name, tech_lastname, tech_email,
user_id, users_username, users_password, users_role,
users_first_name, users_last_name, is_active,
appointment_id, appointment_date, appointment_time, appointment_status,
appt_patient_id, appt_doctor_id,
diagnosis_id, diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id,
procedure_id, procedure_type, procedure_sched_date, procedure_description,
procedure_cost, proc_doctor_id, proc_diagnosis_id,
proc_result_id, proc_result_description, proc_result_date,
performed_id, perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes,
test_id, test_name, test_description, test_cost,
lab_result_id, lab_result_value, lab_result_date,
performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes,
prescription_id, medication_name,
restriction_id, restriction_description,
dosage, frequency, duration, prescription_notes,
allergy_id, allergy_name, allergy_severity,
record_allergy_reaction, record_allergy_severity,
symptom_id, symptom_name, symptom_description, record_symptom_severity,
record_id,
referral_id, referral_reason, referral_date, ref_from_doctor_id, ref_to_doctor_id,
report_id, report_description, report_date, report_doctor_id,
bill_id, total_cost, payment_status, payment_date, billing_admin_id
}
Functional dependencies
Doctor
F1 level_id -> level_name F2 specialization_id -> specialization_name F3 department_id -> department_name F4 doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id
Patient
F5 patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg
Admin
F6 admin_id -> admin_username, admin_name, admin_lastname, admin_email
Lab Technician
F7 technician_id -> tech_username, tech_name, tech_lastname, tech_email
User
F8 user_id -> users_username, users_password, users_role,
users_first_name, users_last_name, is_active
user_id also has four nullable foreign keys: patient_id, doctor_id, admin_id,
and technician_id. Only one of these is ever filled in for a given user, depending
on their role, and the rest stay null. Because of that, user_id is not treated as
determining any of the four below, since a login row only optionally points to one
profile row rather than always pointing to the same one. This is the same subtype
idea used for Admin, Clients, and Owners elsewhere, except in our database it keeps all
four subtypes together in one users table instead of giving each one its own table.
Appointment
F9 appointment_id -> appointment_date, appointment_time, appointment_status,
appt_patient_id, appt_doctor_id
Diagnosis
F10 diagnosis_id -> diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id
Procedure
F11 procedure_id -> procedure_type, procedure_sched_date, procedure_description,
procedure_cost, proc_doctor_id, proc_diagnosis_id
F12 proc_result_id -> proc_result_description, proc_result_date, procedure_id
F13 performed_id -> perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id,
perf_date, perf_notes
Prescription
F14 prescription_id -> medication_name F15 restriction_id -> restriction_description, prescription_id
Lab test
F16 test_id -> test_name, test_description, test_cost
F17 lab_result_id -> lab_result_value, lab_result_date, test_id
F18 performed_test_id -> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id,
pt_test_date, pt_notes
Medical record
F19 record_id -> patient_id
Allergy
F20 allergy_id -> allergy_name, allergy_severity
Symptom
F21 symptom_id -> symptom_name, symptom_description
Referral
F22 referral_id -> referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id
Medical report
F23 report_id -> report_description, report_date, record_id, report_doctor_id
Billing
F24 bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id
Medical record associations (composite key attributes)
F25 {record_id, symptom_id} -> record_symptom_severity
F26 {record_id, allergy_id} -> record_allergy_reaction, record_allergy_severity
F27 {prescription_id, record_id} -> dosage, frequency, duration, prescription_notes
Many-to-many association tables (no dependent attribute trivial so FDs only on the full pair)
{diagnosis_id, record_id} {doctor_id, record_id} {record_id, procedure_id}
{record_id, lab_result_id} {patient_id, allergy_id} {patient_id, symptom_id}
{diagnosis_id, symptom_id} {diagnosis_id, procedure_id} {specialization_id, procedure_id}
{department_id, procedure_id} {bill_id, procedure_id} {bill_id, test_id}
{report_id, lab_result_id} {allergy_id, restriction_id}
Candidate keys and primary key
A candidate key of the de-normalized relation is found by picking a minimal set of identifiers whose closure reaches every attribute of R. Several identifiers already determine others through chains inside the FD set — for instance:
proc_result_id -> procedure_id lab_result_id -> test_id restriction_id -> prescription_id referral_id -> record_id, and in turn record_id -> patient_id doctor_id -> level_id, specialization_id, department_id
These chains matter because whatever gets reached this way (procedure_id, test_id, prescription_id, record_id, patient_id, level_id, specialization_id, department_id) does not need to be included in the key separately — it is already recoverable once its determinant is present.
Left-hand side only (identifiers that never appear as a dependent on the right side of any FD, and therefore cannot be reached this way): `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`
Since none of these 16 identifiers can be derived from anything else in R, every candidate key of R is required to contain all of them:
K = { 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 }
Closure proof for K
Start:
K+ = {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}
From F4:
doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id
From F1 using level_id:
level_id -> level_name
From F2 using specialization_id:
specialization_id -> specialization_name
From F3 using department_id:
department_id -> department_name
From F6:
admin_id -> admin_username, admin_name, admin_lastname, admin_email
From F7:
technician_id -> tech_username, tech_name, tech_lastname, tech_email
From F8:
user_id -> users_username, users_password, users_role, users_first_name, users_last_name, is_active
From F9:
appointment_id -> appointment_date, appointment_time, appointment_status, appt_patient_id, appt_doctor_id
From F10:
diagnosis_id -> diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id
From F12:
proc_result_id -> proc_result_description, proc_result_date, procedure_id
From F11 using procedure_id:
procedure_id -> procedure_type, procedure_sched_date, procedure_description,
procedure_cost, proc_doctor_id, proc_diagnosis_id
From F13:
performed_id -> perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes
From F15:
restriction_id -> restriction_description, prescription_id
From F14 using prescription_id:
prescription_id -> medication_name
From F17:
lab_result_id -> lab_result_value, lab_result_date, test_id
From F16 using test_id:
test_id -> test_name, test_description, test_cost
From F18:
performed_test_id -> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes
From F20:
allergy_id -> allergy_name, allergy_severity
From F21:
symptom_id -> symptom_name, symptom_description
From F22:
referral_id -> referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id
From F19 using record_id:
record_id -> patient_id
From F5 using patient_id:
patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg
From F23:
report_id -> report_description, report_date, record_id, report_doctor_id
From F24:
bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id
From F25 using record_id and symptom_id:
{record_id, symptom_id} -> record_symptom_severity
From F26 using record_id and allergy_id:
{record_id, allergy_id} -> record_allergy_reaction, record_allergy_severity
From F27 using prescription_id and record_id:
{prescription_id, record_id} -> dosage, frequency, duration, prescription_notes
Therefore, K+ determines all attributes of R, so K is a superkey.
Minimality / uniqueness
To promote K from only "a candidate key" to the chosen primary key, we also need to show that no other candidate key exists.
None of the 16 identifiers in K is functionally determined by another determinant in the given FD set:
doctor_id does not appear on the right-hand side of any FD, so it cannot be derived.
admin_id does not appear on the right-hand side of any FD, so it cannot be derived.
technician_id does not appear on the right-hand side of any FD, so it cannot be derived.
user_id does not appear on the right-hand side of any FD, so it cannot be derived.
appointment_id does not appear on the right-hand side of any FD, so it cannot be derived.
diagnosis_id does not appear on the right-hand side of any FD, so it cannot be derived.
proc_result_id does not appear on the right-hand side of any FD, so it cannot be derived.
performed_id does not appear on the right-hand side of any FD, so it cannot be derived.
restriction_id does not appear on the right-hand side of any FD, so it cannot be derived.
lab_result_id does not appear on the right-hand side of any FD, so it cannot be derived.
performed_test_id does not appear on the right-hand side of any FD, so it cannot be derived.
allergy_id does not appear on the right-hand side of any FD, so it cannot be derived.
symptom_id does not appear on the right-hand side of any FD, so it cannot be derived.
referral_id does not appear on the right-hand side of any FD, so it cannot be derived.
report_id does not appear on the right-hand side of any FD, so it cannot be derived.
bill_id does not appear on the right-hand side of any FD, so it cannot be derived.
Every superkey of R must contain all attributes in:
E = { 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 }
Because K and E are the same set, and we already showed K+ = R, K qualifies as a superkey. And because every superkey is required to contain E, so nothing smaller than K could ever work as a key. There's also no way to swap out one of these 16 attributes for a different one and still get a valid candidate key, since none of the attributes in E can be reached from any other determinant in the functional dependency set so there's simply no substitute available for any of them.
This means K stands as the sole candidate key of the de-normalized relation, given the functional dependencies defined above.
Primary key of the de-normalized relation:
PK = { 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 }
1NF decomposition
Every attribute in R is single valued and atomic, and the composite PK above uniquely identifies each row, so R satisfies 1NF.
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:
doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id diagnosis_id -> diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id proc_result_id -> proc_result_description, proc_result_date, procedure_id referral_id -> referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id
Grouping by determinants
doctor_id -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id
level_id -> level_name
specialization_id-> specialization_name
department_id -> department_name
patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg
admin_id -> admin_username, admin_name, admin_lastname, admin_email
technician_id -> tech_username, tech_name, tech_lastname, tech_email
user_id -> users_username, users_password, users_role, users_first_name, users_last_name, is_active
appointment_id -> appt_patient_id, appt_doctor_id, appointment_date, appointment_time, appointment_status
diagnosis_id -> diagnosis_name, diagnosis_description, diag_patient_id, diag_doctor_id
procedure_id -> procedure_type, procedure_sched_date, procedure_description, procedure_cost,
proc_doctor_id, proc_diagnosis_id
proc_result_id -> proc_result_description, proc_result_date, procedure_id
performed_id -> perf_proc_id, perf_doctor_id, perf_patient_id, perf_diagnosis_id, perf_date, perf_notes
prescription_id -> medication_name
restriction_id -> restriction_description, prescription_id
test_id -> test_name, test_description, test_cost
lab_result_id -> lab_result_value, lab_result_date, test_id
performed_test_id-> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes
record_id -> patient_id
allergy_id -> allergy_name, allergy_severity
symptom_id -> symptom_name, symptom_description
referral_id -> referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id
report_id -> report_description, report_date, record_id, report_doctor_id
bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id
{record_id, symptom_id} -> record_symptom_severity
{record_id, allergy_id} -> record_allergy_reaction, record_allergy_severity
{prescription_id, record_id} -> dosage, frequency, duration, prescription_notes
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)
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:
(R1 ∩ R2) -> R1 or (R1 ∩ R2) -> R2
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.
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.
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.
| Relation | Key | BCNF? |
|---|---|---|
DoctorLevels | level_id, alt. key level_name | Yes |
Specializations | specialization_id, alt. key specialization_name | Yes |
Departments | department_id, alt. key department_name | Yes |
Doctors | doctor_id, alt. key d_email | Yes |
Patients | patient_id, alt. key embg | Yes |
Admin | admin_id, alt. keys admin_username, admin_email | Yes |
LabTechnician | technician_id, alt. keys tech_username, tech_email | Yes |
Users | user_id, alt. key users_username | Yes |
Appointments | appointment_id | Yes |
Diagnoses | diagnosis_id | Yes |
Procedures | procedure_id | Yes |
ProcedureResults | proc_result_id | Yes |
PerformedProcedures | performed_id | Yes |
Prescriptions | prescription_id | Yes |
PrescriptionRestrictions | restriction_id | Yes |
LabTests | test_id | Yes |
LabResults | lab_result_id | Yes |
PerformedLabTests | performed_test_id | Yes |
MedicalRecords | record_id | Yes |
Allergies | allergy_id, alt. key allergy_name | Yes |
Symptoms | symptom_id, alt. key symptom_name | Yes |
Referrals | referral_id | Yes |
MedicalReports | report_id | Yes |
Billing | bill_id | Yes |
MedicalRecordSymptoms | {record_id, symptom_id} | Yes |
MedicalRecordAllergies | {record_id, allergy_id} | Yes |
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.
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.
This gives eleven multivalued dependencies:
record_id ->> diagnosis_id record_id ->> doctor_id record_id ->> procedure_id record_id ->> lab_result_id diagnosis_id ->> symptom_id diagnosis_id ->> procedure_id procedure_id ->> specialization_id procedure_id ->> department_id procedure_id ->> bill_id patient_id ->> allergy_id patient_id ->> symptom_id
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.
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.
The schema satisfies 4NF.
DoctorLevels(level_id, level_name)Specializations(specialization_id, specialization_name)Departments(department_id, department_name)Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id)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)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)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)Prescriptions(prescription_id, medication_name)PrescriptionRestrictions(restriction_id, restriction_description, prescription_id)Allergies(allergy_id, allergy_name, allergy_severity)Symptoms(symptom_id, symptom_name, symptom_description)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)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)
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.
