Meaning of "Dependency" in Relation to Elementary DB Normalization In trying to figure out why the following table definitions from a textbook exercise about nurses in hospital wards is not in 2NF:
Ward (WName, Location, WType)
Ward-Nurse (WName(fk), NurseID, NurseName, TeamCode, TeamSkill,
Shift)
what is the meaning of &quot;depends on&quot; when describing how some attributes don't &quot;depend&quot; on the whole key? Is it something like &quot;you don't need to know the ward name in order to know what shift a nurse is working&quot; for example?
What is the thought process by which it becomes obvious that for this DB to be in 2NF I need to change the definition to
Ward ( WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode, TeamSkill)
Likewise for 3NF, what interpretation of the word &quot;depends&quot; and its application to the table definitions above allows me to know that the solution for 3NF is
Ward (WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode(fk))
Team (TeamCode, TeamSkill)
All the above being from a textbook exercise where no explanation is given beyond the proposed table definitions.
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.