0

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!

philipxy
  • 14,867
  • 6
  • 39
  • 83
wizkid
  • 3
  • 2

1 Answers1

0

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.

tp9
  • 341
  • 3
  • 14
  • I do believe that my example is incorrect. I think you nailed it right on the head with Ins_First_Contact and Ins_second_contact. If this did need normalization though it would be (Ins_First_Contact) → (Ins_Second_Contact) → ??(Ins_Phone)?? or ??(Ins_Co_ID)??. Pk's can't be part of transitive dependencies though right so that is ruled out? – wizkid May 06 '12 at 20:12
  • @wizkid You need to provide more info. Is this for a homework assignment? Was there sample data provided or descriptions of the fields? At this point INS_FIRST_CONTACT could mean anything. Is INS_PHONE the phone number of the insurance company? The first contact? An agent? – tp9 May 06 '12 at 20:55
  • Yes it is a homework assignment, but unfortunately what I have here is all that is provided. I think the professor is just taking a shot in the dark here, I think I might assume that (Ins_First_Contact) → (Ins_Second_Contact) → (Ins_Phone) just to show him that I can do it because the assignment specifies not to go beyond 3NF. If I did that, would it make sense assuming that these three attribrutes could mean anything? – wizkid May 06 '12 at 21:59
  • @wizkid With the information given I would probably go the other way with INS_PHONE -> INS_FIRST_CONTACT -> INS_SECOND_CONTACT. Assuming phone is the phone number for the insurance company and that is how you contact first contact and then second contact if first contact is not available. – tp9 May 07 '12 at 00:17