I am having trouble identifying transitive dependencies. I understand the concept behind it but looking at them and pulling them out is where I have my problems.
So I know
A → B
It is not the case that B → A
B → C
Then A → C is a transitive dependency.
I can't seem to identify the transitive dependency in this scenario:
Patient ID (PK)
Insurance_Co_ID (PK)
Doctor_ID (PK)
Fname
P_Lname
P_Street
P_City
P_Zip
P_Phone
Ins_First_Contact
Ins_Second_Contact
Ins_Phone
Doc_Fname
Doc_Lname
Doc_Beeper
I understand how to make 1NF, 2NF, ect diagrams, identifying partial dependencies and determinant, just having trouble on this. My take on this after doing research is finding only one transitive dependency but even then I am not sure. (Doc_Fname) → (Doc_Lname) → (Doc_Beeper) so (Doc_Fname) → (Doc_Beeper) or (Doc_Fname, Doc_Lname) → (Doc_Beeper)?
Please help and thanks!
Your example looks incorrect. If you know DOC_FNAME do you absolutely know DOC_LNAME? What if two docs have the same DOC_FNAME?
As for 3NF, the insurance company fields look suspect to me. What is INS_SECOND_CONTACT? Does that imply that you need a FIRST_CONTACT before you have a SECOND_CONTACT? Looks like a prime candidate for normalization to me.