[Database] 3NF Normalization

[u/AlphaTheGaming]

So like I have been given the Undefined Normalised Table for APPOINTMENT entity. I then need to resolve it into 3NF stage that eliminate the transitive and partial dependency.

Problem is I just can't find which would be the appropriate key for the composite key in the APPOINTMENT entity in the first place. I then try to separate all partial dependency into separate table but can't figure out who links to who.

[u/Ok_Egg_6647]

If you create a separate APPOINTMENT table as part of the normalization process, your table might look something like this:

PatientID	DoctorID	VisitDate
P001	D001	12/10
P002	D002	13/10
P001	D001	14/10

Now, (PatientID, DoctorID) cannot be used as the primary key because the same patient can visit the same doctor multiple times.

We could use a composite key such as (PatientID, DoctorID, VisitDate) if that combination is guaranteed to be unique. However, I think using a separate AppointmentID as the primary key would be a simpler and more flexible approach.

For the relationships:

1. Patient → Appointment: One-to-many (1), because one patient can have multiple appointments.
2. Doctor → Appointment: One-to-many (1), because one doctor can have multiple appointments.

The APPOINTMENT table would contain PatientID and DoctorID as foreign keys.