Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 4 (modified by 236021, 14 hours 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_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, pt_created_at,

  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, billing_created_at
}

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, user_id

Patient

F5  patient_id -> p_first_name, p_last_name, p_email, date_of_birth, blood_type, gender, phone_number, embg, user_id

Admin

F6  admin_id -> admin_username, admin_name, admin_lastname, admin_email, user_id

Lab Technician

F7  technician_id -> tech_username, tech_name, tech_lastname, tech_email, user_id

User

F8  user_id -> users_username, users_password, users_role,
               users_first_name, users_last_name, is_active

Unlike a typical subtype design where the supertype (Users) would hold nullable foreign keys to each subtype, this schema does it the other way around: Doctors, Patients, Admin, and LabTechnician each carry their own mandatory user_id foreign key back to Users, so it is the subtype row that points up to its login row, not the reverse. Because of that, doctor_id -> user_id, patient_id -> user_id, admin_id -> user_id, and technician_id -> user_id all hold (folded into F4, F5, F6, F7 above), while user_id itself does not determine any of doctor_id, patient_id, admin_id, or technician_id, since a Users row need not have any subtype row pointing to it at all (e.g. a system administrator account with no linked profile). This is the same subtype idea used for Admin, Clients, and Owners elsewhere, except here the four subtypes each own the foreign key to their shared supertype users table instead of the supertype holding a pointer to each of them.

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
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, pt_created_at

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, billing_created_at

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)

{record_id, procedure_id}          {record_id, lab_result_id}        {patient_id, allergy_id}
{patient_id, symptom_id}           {diagnosis_id, symptom_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}      {diagnosis_id, record_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, user_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, and — because of the corrected foreign-key direction discussed under User above — user_id) does not need to be included in the key separately as it is already recoverable once its determinant is present. In particular, user_id is reachable via doctor_id -> user_id (F4) the moment doctor_id is in the key, so unlike an earlier draft of this analysis that assumed Users held the foreign keys, user_id is *not* one of the irreducible identifiers below.

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, 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 15 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, 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, 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, user_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 F8 using user_id (just derived from F4):
user_id -> users_username, users_password, users_role, users_first_name, users_last_name, is_active

From F6:
admin_id -> admin_username, admin_name, admin_lastname, admin_email, user_id

From F7:
technician_id -> tech_username, tech_name, tech_lastname, tech_email, user_id

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

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, pt_created_at

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, user_id

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, billing_created_at

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 15 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.

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.

user_id is deliberately excluded from this list: it *does* appear on the right-hand side of F4 (doctor_id -> ..., user_id), so it is derivable once doctor_id — already irreducible and already in K — is known, and including it separately in K would be redundant.

Every superkey of R must contain all attributes in:

E = { 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 }

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 15 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, 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 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:

doctor_id      -> d_first_name, d_last_name, d_email, level_id, specialization_id, department_id, user_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, user_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, user_id

admin_id         -> admin_username, admin_name, admin_lastname, admin_email, user_id

technician_id    -> tech_username, tech_name, tech_lastname, tech_email, user_id

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_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, pt_created_at

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, billing_created_at

{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, user_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, user_id)
Admin(admin_id, admin_username, admin_name, admin_lastname, admin_email, user_id)
LabTechnician(technician_id, tech_username, tech_name, tech_lastname, tech_email, user_id)
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)
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, pt_created_at)
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, billing_created_at)
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.

Decomposition into relations

Doctors(doctor_id, d_first_name, d_last_name, d_email, level_id, specialization_id, department_id, user_id)

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)

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, user_id)

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)

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, user_id)

R6 = R5 - {admin_username, admin_name, admin_lastname, admin_email} (user_id remains - needed for Users)

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, user_id)

R7 = R6 - {tech_username, tech_name, tech_lastname, tech_email} (user_id remains - needed for Users)

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, 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)

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.

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)

R11 = R10 - {procedure_type, procedure_sched_date, procedure_description, procedure_cost, proc_doctor_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, pt_created_at)

R18 = R17 - {pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at}

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, billing_created_at)

R24 = R23 - {total_cost, payment_status, payment_date, billing_admin_id, billing_created_at}

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, 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.

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 — 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, 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.
ProcedureResults keeps only the foreign key procedure_id, the procedure's own procedure_type, procedure_cost, etc. stay in Procedures.
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.

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. keys `d_email`, `user_id`	Yes
`Patients`	patient_id, alt. keys `embg`, `user_id`	Yes
`Admin`	admin_id, alt. keys `admin_username`, `admin_email`, `user_id`	Yes
`LabTechnician`	technician_id, alt. keys `tech_username`, `tech_email`, `user_id`	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 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. user_id in Doctors, Patients, Admin, and LabTechnician is a genuine alternate key: each carries its own UNIQUE constraint and is required (NOT NULL).

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 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.

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.

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.)

A procedure can be linked to several specializations qualified to perform it, several departments that perform it, and several bills it appears on.

A patient can be linked to several allergies and several symptoms, tracked completely separately from one another.

This gives eight multivalued dependencies:

record_id     ->> diagnosis_id
record_id     ->> procedure_id
record_id     ->> lab_result_id

procedure_id  ->> specialization_id
procedure_id  ->> department_id
procedure_id  ->> bill_id

patient_id    ->> allergy_id
patient_id    ->> symptom_id

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.

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.

MVD check

Nothing is actually being split here. These twelve 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.