What is Normalization?
Normalization is the process of organizing the data and the attributes of a database.
It is performed to reduce the data redundancy in a database and ensure that data is stored logically.
Normalization is systematic approach of decomposing table to eliminate data redundancy and undesirable characteristics like insertion, update and delete.
Normalization is multi-step process that puts data in tabular form and remove duplicate data from relation tables.
Example Employees Table
| Id | Name | Address | Profession |
|---|---|---|---|
| 101 | Mary | 1245 | Developer |
| 102 | David | 5234 | Accountant |
| 103 | Juliet | 1444 | Salesperson |
| 104 | Elizabeth | 8745 | Manager |
| 105 | Haskell | 3251 | Operation |
In this table, we have data of office employees.
1. Insertion Anomaly
An insertion anomaly occurs in the relation database when some attributes or data items are inserted into database without existence of other attributes.
2. Updation Anomaly
Updation Anomaly occurs when the same data item is repeated with the same values are not linked to each other.
3. Deletion Anomalies
Deletion Anomalies occurs when deleting one part of the data deletes the other necessary information from the database.
Types of Normalization
1NF
2NF
3NF
BCNF
4NF
5NF
Diagram:
1. 1NF (First Normal Form)
In 1NF relation, each table cell should contain a single value. Each record looks like unique.
Example
| CouserId | Course Name | Framework |
|---|---|---|
| JAV101 | Java | NetBeans |
| SQL102 | SQL | MySQL, PostgreSQL |
| PY214 | Python | Flask |
Here in the Framework row, we stored two frameworks of course Name MySQL, PostgreSQL so it is *multi-valued attribute. * it is not 1NF relation. We need to convert it into 1NF.
Convert it into 1NF
| CourseId | Course Name | Framework |
|---|---|---|
| JAV101 | Java | NetBeans |
| SQL102 | SQL | MySQL |
| SQL102 | SQL | PostgreSQL |
| PY214 | Python | Flask |
It's a simple method to store Framework separately in 1NF. Now this is First Normal Form. 1NF wants to store unique information in table without data repetition.
2. 2NF (Second Normal Form)
In 2NF, relation must in 1NF. In the Second Normal Form, all non-key attributes are fully functionally dependent on the primary key.
| StudentID | Specialization | Student Age |
|---|---|---|
| 501 | Data Analyst | 22 |
| 501 | Data Engineer | 22 |
| 502 | Full Stack Developer | 24 |
| 503 | Web Developer | 23 |
Top comments (1)