can a table in 2nd normal form have a FD in which the non prime attribute depend on a super key having an extraneous attribute?

Question

For 2NF every source I can get hands on says the following:

The non prime attribute must be fully dependent on a candidate key.

But can the non prime attribute be dependent on a super set of the candidate key? I mean can the left side contain an extraneous attribute.

Also, please explain, if it is necessary that all non prime attributes have to be fully dependent upon on any of the candidate keys . Can't one non prime attribute just be dependent on another non prime attribute?

Answer 1

You are asking:

But can the non prime attribute be dependent on a super set of the candidate key ? I mean can the left side contain an extraneous attribute.

If a set of attributes is a candidate key, then it determines all the other attributes, including non prime attributes. So, if you add attributes on the left side nothing changes with respect to the definition of 2NF, the only important thing is that a non prime attribute cannot depend on a proper subset of a candidate key.

Then, you are asking:

Can't one non prime attribute just be dependent on another non prime attribute?

If in a relation schema a non prime attribute depends from another non prime attribute we can only say that this dependency does not violates the original Codd’s definition, that requires only that every non prime attribute must be “fully dependent on each candidate key of R”, which can also be expressed as: does not exists a dependency in which the left part is a proper subset of a candidate key and the right part is a non prime attribute.

So, for the same reason, if a dependency contains in the left part a proper subset of a candidate key plus a non-prime attribute, this does not fall under the Codd’s definition, meaning that it does not violate such definition.

Finally, note that the 2NF has not interest neither in practice nor in theory: it has only historical interest. In fact, a relation is said properly normalized if it is in Boyce-Codd Normal Form or in Third Normal Form (or in Fourth or greater normal forms). In fact in many important database books you can find formal algorithms to bring a relation in BCNF or 3NF, but not in 2NF.