Second Normal Form

Second normal form (2NF) is based on the concept of full functional dependency.
Functional Dependency:
The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B. Example: PROJ_NUM? PROJ_NAME
In this case, the attribute PROJ_NUM is known as the determinant attribute and the attribute PROJ_NAME is known as the dependent attribute.

Generalized Definition:
Attribute A determines attribute B ( that is B is functionally dependent on A) if all of the rows in the table that agree in value for attribute A also agree in value for attribute B.

Fully functional dependency (composite key)
If attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is fully functionally dependent on A.
Partial Dependency:
When there is a functional dependence in which the determinant is only part of the primary key, then there is a partial dependency. For example if (A, B) ? (C, D) and B? C and (A, B) is the primary key, then the functional dependence B? C is a partial dependency.
{Ssn, Pnumber} ??Hours is a full dependency
(neither Ssn ??Hours nor Pnumber?Hours holds).
However, the dependency {Ssn, Pnumber}?Ename is partial because Ssn?Ename holds.
Transitive Dependency:


When there are the following functional dependencies such that X?Y, Y? Z and X is the primary key, then X? Z is a transitive dependency because X determines the value of Z via Y. Whenever a functional dependency is detected amongst non- prime, there is a transitive dependency.


Definition. A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R.
The test for 2NF involves testing for functional dependencies whose left-hand side attributes are part of the primary key.
If the primary key contains a single attribute, the test need not be applied at all.
If a relation schema is not in 2NF, it can be second normalized or
2NF normalized into a number of 2NF relations in which nonprime attributes are associated only with the part of the primary key on which they are fully functionally dependent.