Meaning of "Dependency" in Relation to Elementary DB Normalization

Question

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 "depends on" when describing how some attributes don't "depend" on the whole key? Is it something like "you don't need to know the ward name in order to know what shift a nurse is working" 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 "depends" 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.

Yes, you got it. "you don't need to know the ward name in order to know what is the name of a nurse". In other words, "you can find the NurseName only by the NurseID" or "the NurseName depends on NurseID only". — ypercubeᵀᴹ, Sep 08 '20 at 07:55
The same (for the 3NF case): "you don't need the NurseID to find the TeamSkill, it depends only on TeamCode. — ypercubeᵀᴹ, Sep 08 '20 at 07:55
"Depends on" is short for "functionally depends on". Y functionally depends on X" means that functional dependency X->Y holds. Your textbook tells you what that means. It also tells you what conditions 2NF & 3NF are. https://stackoverflow.com/a/34191604/3404097 https://stackoverflow.com/a/27504915/3404097 PS What is the textbook name & edition & what does it say about the terms in your post? — philipxy, Sep 11 '20 at 22:44
Possible duplicate of Functional Dependency and Superkey PS What is the difference between Primary key and Super key in DBMS — philipxy, Sep 11 '20 at 22:55

score 1 · Accepted Answer · answered Sep 14 '20 at 05:01

In this context "depends on" means "determined by" or "dictated by". 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 "Given a row, if I changed part of the key only, would I still be certain what the non-key columns' values are?" 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.

Awesome. So nice that someone can communicate with a learner at their level. — Robin Andrews, Sep 15 '20 at 09:23

Meaning of "Dependency" in Relation to Elementary DB Normalization

1 Answers1