HOME

TheInfoList



Click Here for Items Related To - Second normal form
OR:
Second normal form on:   [Wikipedia]   [Google]   [Amazon]

Second normal form (2NF) is a normal form used in
database normalization Database normalization or database normalisation (see spelling differences) is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. ...
. 2NF was originally defined by
E. F. Codd Edgar Frank "Ted" Codd (19 August 1923 – 18 April 2003) was an English computer scientist who, while working for IBM, invented the relational model for database management, the theoretical basis for relational databases and relational databa ...
in 1971.Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems", New York City, May 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), ''Data Base Systems: Courant Computer Science Symposia Series 6''. Prentice-Hall, 1972. A relation is in the second normal form'' if it fulfills the following two requirements: # It is in
first normal form First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements. Or more informally, that no table column can have tables as values (or no ...
. # It does not have any
non-prime attribute A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removi ...
that is functionally dependent on any
proper subset In mathematics, set ''A'' is a subset of a set ''B'' if all elements of ''A'' are also elements of ''B''; ''B'' is then a superset of ''A''. It is possible for ''A'' and ''B'' to be equal; if they are unequal, then ''A'' is a proper subset of ...
of any
candidate key A candidate key, or simply a key, of a relational database is a minimal superkey. In other words, it is any set of columns that have a unique combination of values in each row (which makes it a superkey), with the additional constraint that removi ...
of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation. Put simply, a relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key. Note that it does not put any restriction on the non-prime to non-prime attribute dependency. That is addressed in
third normal form Third normal form (3NF) is a database schema design approach for relational databases which uses normalizing principles to reduce the duplication of data, avoid data anomalies, ensure referential integrity, and simplify data management. It was de ...
.


2NF and candidate keys

A
functional dependency In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, a functional dependency is a constraint between two attributes in a relation. Given a relation '' ...
on part of any candidate key is a violation of 2NF. In addition to the
primary key In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes (Column (database), columns) that uniquely specify a tuple (Row (database), row) in a Relation (database), relation (Table (database), t ...
, the relation may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on ''any'' of these candidate keys. The following relation ''does not'' satisfy 2NF because: * is functionally dependent on . * is not part of a candidate key, so it is a non-prime attribute. (It is assumed that it is possible for two manufacturers in the same country to make a toothbrush with the same model name, so is not a candidate key even though in the current table the pair uniquely identify rows.) * is a proper subset of candidate key. Since is a non-prime attribute functionally dependent on a part of a candidate key, the relation is in violation of 2NF. The relation is not in 2NF. is a candidate key, and Manufacturer country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two relations:


See also

* Attribute-value system


References


Further reading


Litt's Tips: Normalization
* * *


External links


Database Normalization Basics
by Mike Chapple (About.com)
An Introduction to Database Normalization
by Mike Hillyer.
A tutorial on the first 3 normal forms
by Fred Coulson
Description of the database normalization basics
by Microsoft {{Database normalization 2NF de:Normalisierung (Datenbank)#Zweite Normalform (2NF)