[!abstract] Normalization in relational databases is a design process that minimizes data redundancy and avoids UPDATE ANOMALIES. Basically, you want each piece of information to be stored exactly once; if the information changes, you only have to update it in one place. ^[https://vertabelo.com/blog/normalization-1nf-2nf-3nf/]
A normal form is a data base which follows a formal set of rules. By following these rules practitioner can reduce the number of unpleasant data discrepancies like Insertion, Update and Deletion Anomalies. ^[https://www.guru99.com/database-normalization.html] The two goals of normalisation, Eliminate redundant data and make sure dependencies make sense, reduce the amount of space a database takes up, ensure logic is consistent and reduces anomalies. ^[https://www.lifewire.com/database-normalization-basics-1019735]
This anomalies often arise when duplicates or confused relation dependencies are not properly handled in a data structure. Some believe that normalisation is a luxury only academics have time for, but keeping them in mind when designing databases could improve the performance of your databases ^[https://www.lifewire.com/database-normalization-basics-1019735]
The Normal Form are indicated as (n)NF. While the first three, 1NF, 2NF and 3NF are quite common there are other normal forms ranging all the way up to 6NF as well as other types of structure like UNF, EKNF, BCNF, ETNF and DKNF.
In an academic sense the above forms are strike and quite formal. In a practical application however these form are simply guidelines and frameworks for thinking about and working on database design. ^[https://www.lifewire.com/database-normalization-basics-1019735]
| Character | UNF | 1NF | 2NF | 3NF | EKNF | BCNF | 4NF | ETNF | 5NF | DKNF | 6NF |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Primary Key | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ |
| Atomic Columns | ❌ | ||||||||||
| no partial functional dependencies of non-prime attributes on candidate keys | ❌ | ❌ | |||||||||
| no transitive functional dependencies of non-prime attributes on candidate key | ❌ | ❌ | ❌ | ||||||||
| Every non-trivial functional dependency either begins with a superkey or ends with an elementary prime attribute | ❌ | ❌ | ❌ | ❌ | - | ||||||
| Every non-trivial functional dependency begins with a superkey | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ✅ | ✅ | ✅ | ✅ | - |
| Every non-trivial multivalued dependency begins with a superkey | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ✅ | ✅ | ✅ | - |
| Every join dependency has a superkey component | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ✅ | ✅ | - |
| Every join dependency has only superkey components | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ✅ | - |
| Every constraint is a consequence of domain constraints and key constraints | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ❌ |
| Every Join dependency is trivial | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ |
In academic literature the way
[!tip] summary
- The first normal form (1NF) states that each attribute in the relation is atomic.
- The second normal form (2NF) states that non-prime attributes must be functionally dependent on the entire candidate key.
- The third normal form (3NF) states that non-prime attributes must be directly (non-transitively) dependent on candidate keys. ^[https://vertabelo.com/blog/normalization-1nf-2nf-3nf/]