What is the Difference Between 1NF and 2NF and 3NF?
🆚 Go to Comparative Table 🆚The difference between 1NF, 2NF, and 3NF lies in the degree of data normalization, which is a process that organizes data to reduce redundancy and improve data integrity. Here's a brief explanation of each normal form:
- First Normal Form (1NF):
- A relation is in 1NF if it contains only atomic values (values that cannot be further divided).
- Each column in a 1NF table should contain only one value for each row.
- Second Normal Form (2NF):
- A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
- In other words, a table is in 2NF if it has no partial dependency, meaning all non-key attributes are fully dependent on the primary key.
- Third Normal Form (3NF):
- A relation is in 3NF if it is in 2NF and no transition dependency exists.
- For a table to be in 3NF, it must be in 2NF and have no transitive partial dependency, meaning no non-prime attribute (an attribute that is not part of the candidate key) is dependent on another non-prime attribute.
In summary, 1NF focuses on removing repeating groups and ensuring atomic values, 2NF eliminates redundancy and partial dependency by making non-key attributes fully dependent on the primary key, and 3NF goes a step further by eliminating transitive partial dependency.
Comparative Table: 1NF vs 2NF vs 3NF
Here is a table comparing the differences between First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF):
Normal Form | Criteria |
---|---|
1NF | - Atomic values: Each attribute must contain a single, indivisible value (no multi-valued or composite attributes). - No repeating groups: Each row must be unique, with no duplicate data. |
2NF | - Already in 1NF - Full functional dependency: All non-key attributes must be fully dependent on the primary key. |
3NF | - Already in 2NF - No indirect (transitive) dependencies: No non-key attribute can be dependent on another non-key attribute. |
Here's a brief explanation of each normal form:
- 1NF: Ensures that each table cell contains only one value, and each column in a table is unique. It removes repeating groups but does not handle redundancy or update anomalies.
- 2NF: Eliminates redundancy by ensuring that all non-key attributes are fully dependent on the primary key. It handle update anomalies, but not indirect dependencies between attributes.
- 3NF: Removes indirect dependencies between non-key attributes, ensuring that no non-key attribute depends on another non-key attribute. It builds upon the constraints of 1NF and 2NF, resulting in a more efficient and reliable database.
Read more:
- 3NF vs BCNF
- BCNF vs 4NF (4th Normalization)
- NF1 vs NF2
- NFkb1 vs NFkb2
- SF1 vs SF2
- NF3 vs NH3
- FDDI 1 vs FDDI 2
- NRF1 vs NRF2
- Normalization vs Denormalization
- Copper 1 vs Copper 2
- N2 vs 2N
- NM3 vs M3
- Basel 1 2 vs 3
- Web 1.0 vs Web 2.0 vs Web 3.0
- First Second vs Third Transition Series
- NFC vs AFC
- HIF-1 vs HIF-2
- PFK-1 vs PFK-2
- AND Gate vs NAND Gate