What is Normalization?
Normalization is a crucial database design technique used to improve data organization and eliminate anomalies like Insertion, Update, and Deletion Anomalies. By applying normalization rules, larger tables are divided into smaller ones, and relationships are established between them. The primary objective of Normalization in SQL is to remove redundant data and ensure that data is stored logically.
Database Normal Forms
Here is a list of Normal Forms
- 1NF (First Normal Form)
- 2NF (Second Normal Form)
- 3NF (Third Normal Form)
- BCNF (Boyce-Codd Normal Form)
- 4NF (Fourth Normal Form)
- 5NF (Fifth Normal Form)
- 6NF (Sixth Normal Form)
The Theory of Data Normalization in SQL server is still being developed further. For example, there are discussions even on 6th Normal Form. However, in most practical applications, normalization achieves its best in 3rd Normal Form. The evolution of SQL Normalization theories is illustrated below:-
- 1NF (First Normal Form):
- Each table cell should contain a single value.
- Each record must be unique.
- 2NF (Second Normal Form):
- The table must satisfy the rules of 1NF.
- It should have a Single Column Primary Key.
- 3NF (Third Normal Form):
- The table must satisfy the rules of 2NF.
- There should be no transitive functional dependencies.
- BCNF (Boyce-Codd Normal Form):
- This is achieved when the database is in 3NF, but it still has anomalies when it has more than one Candidate Key.
- Sometimes referred to as 3.5 Normal Form.
- 4NF (Fourth Normal Form):
- A table is in 4NF if no table instance contains two or more independent and multivalued data describing the same entity.
- 5NF (Fifth Normal Form):
- A table is in 5NF if it is in 4NF and cannot be further decomposed into smaller tables without losing data.
- 6NF (Sixth Normal Form) (Proposed):
- 6NF is not yet standardized but has been a topic of discussion among database experts.
- Its definition and application are still evolving.
Database Normalization with Examples:
To better understand database normalization, let’s consider an example of a video library that maintains a database of rented movies. Initially, without any normalization, all information is stored in a single table:
| Rental ID | Member ID | Member Name | Movie ID | Movie Name | Date Rented |
|---|---|---|---|---|---|
| 1 | 101 | John | M001 | The Matrix | 2023-07-15 |
| 2 | 102 | Alice | M001 | The Matrix | 2023-07-15 |
| 3 | 103 | Bob | M002 | Inception | 2023-07-16 |
| 4 | 104 | Sarah | M003 | Interstellar | 2023-07-16 |
To achieve 1NF, we need to ensure that each cell contains a single value, and each record is unique. So, we divide the table into two tables:
Table 1: Members
| Member ID | Member Name |
|---|---|
| 101 | John |
| 102 | Alice |
| 103 | Bob |
| 104 | Sarah |
Table 2: Movies
| Movie ID | Movie Name |
|---|---|
| M001 | The Matrix |
| M002 | Inception |
| M003 | Interstellar |
Now, to achieve 2NF, we need a single-column primary key for each table, which we can see that we already have in both tables (Member ID for Table 1 and Movie ID for Table 2).
Next, for 3NF, we must eliminate any transitive functional dependencies. Let’s assume that the library also stores member salutations (Mr., Mrs., etc.):
Table 3: Salutations
| Salutation ID | Salutation |
|---|---|
| 1 | Mr. |
| 2 | Mrs. |
| 3 | Ms. |
Now, we modify Table 1 as follows:
Table 1: Members
| Member ID | Salutation ID | Member Name |
|---|---|---|
| 101 | 1 | John |
| 102 | 2 | Alice |
| 103 | 1 | Bob |
| 104 | 2 | Sarah |
The tables are now in 3NF since there are no transitive functional dependencies.
This example demonstrates how normalization can efficiently organize data, reduce redundancy, and eliminate anomalies, resulting in a well-structured database. Further normalization beyond 3NF may be required in more complex databases. However, the application of higher normal forms depends on specific data modeling requirements. The theoretical development of normalization concepts continues to evolve, with discussions even on the 6th Normal Form, though not yet standardized.
That’s all to SQL Normalization!!!
Hope you find this useful, please leave your feedback in the comment section.
More Imp Topics
How to calculate percent of total in looker?
What are Derived tables in Looker?
How to set conditional formatting in LOOKER?
Looker Interview Questions And Answers
New Looker Performance Recommendations Dashboard
Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary
_____________________________________________________________________________________________
About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.
You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested to evaluate if Looker is right for you or any other BI solution.
