Meaning of "Dependency" in Relation to Elementary DB Normalization
In this context &quot;depends on&quot; means &quot;determined by&quot; or &quot;dictated by&quot;. Knowing a person's ID we can look up their date of birth. Given a second ID we will, in general, get a different date of birth. (Of course they may incidentally be the same date but that is not dictated by the table definition, just an accident since there are only a finite number of dates to use.) So to know a person's date of birth we must know the ID number. We say date of birth depends on the ID.
To determine if a table is 2NF I would think &quot;Given a row, if I changed part of the key only, would I still be certain what the non-key columns' values are?&quot; For the proposed table Ward-Nurse (WName(fk), NurseID, NurseName, ..), if I changed WName but kept NurseID constant, would I still be certain what NurseName would be? The answer is yes, because NurseName depends on (is determined by / dictated by) NurseID only and is not in any way determined by WName. Similarly with TeamCode and TeamSkill.
The difference between 2NF and 3NF is 2NF talks about keys and 3NF talks about non-keys. Given a row from the 2NF table Nurse (NurseID, NurseName, TeamCode, TeamSkill), if I changed any non-key column will everything else still be necessarily the same? If I change the value of NurseName will TeamCode or TeamSkill remain constant? Yes, they will. If I change TeamCode will TeamSkill and NurseName remain as they were? No, TeamSkill must change; TeamSkill must change when TeamCode changes so its value depends on the value of TeamCode. Hence (TeamCode, TeamSkill) becomes its own table in 3NF.