1NF vs 2NF vs 3NF
Normalization is a process that is carried out to minimize the redundancies that are present in data in relational databases. This process will mainly divide large tables in to smaller tables with fewer redundancies. These smaller tables will be related to each other through well defined relationships. In a well normalized database, any alteration or modification in data will requires modifying only a single table. First normal form (1NF), Second normal form (2NF) and the Third Normal Form (3NF) was introduced by Edgar F. Codd, who is also the inventor of the relational model and the concept of normalization.
What is 1NF?
1NF is the First normal form, which provides the minimum set of requirements for normalizing a relational database. A table that complies with 1NF assures that it actually represents a relation (i.e. it does not contain any records that are repeating), but there is no universally accepted definition for 1NF. One important property is that a table that comply with 1NF could not contain any attributes that are relational valued (i.e. all the attributes should have atomic values).
What is 2NF?
2NF is the Second normal form used in relational databases. For a table to comply with 2NF, it should be complied with 1NF and any attribute that is not a part of any candidate key (i.e. non-prime attributes) should fully depend on any of the candidate keys in the table.
What is 3NF?
3NF is the Third normal form used in relational database normalization. According to the Codd’s definition, a table is said to be in 3NF, if and only if ,that table is in the second normal form (2NF), and every attribute in the table that do not belong to a candidate key, should directly depend on every candidate key of that table. In 1982 Carlo Zaniolo produced a differently expressed definition for 3NF. Tables that comply with the 3NF generally do not contain anomalies that occur when inserting, deleting or updating records in the table.
What is the difference between 1NF and 2NF and 3NF?
1NF, 2NF and 3NF are normal forms that are used in relational databases to minimize redundancies in tables. 3NF is considered as a stronger normal form than the 2NF, and it is considered as a stronger normal form than 1NF. Therefore in general, obtaining a table that complies with the 3NF form will require decomposing a table that is in the 2NF. Similarly, obtaining a table that complies with the 2NF will require decomposing a table that is in the 1NF. However, if a table that complies with 1NF contains candidate keys that are only made up of a single attribute (i.e. non-composite candidate keys), such a table would automatically comply with 2NF. Decomposition of tables will result in additional join operations (or Cartesian products) when executing queries. This will increase the computational time. On the other hand, the tables that comply with stronger normal forms would have fewer redundancies than tables that only comply with weaker normal forms.