Normalization
Normalization of databases is a devised process makes the database design less prone to data anomalies. An unnormalized database design makes the database inefficient and redundant. A good database designing ability comes with experience, but normalization is systematic way of making a database design better.
Database Anomalies
Anomalies in database make the data handling difficult as the data grows. It makes it harder to maintain data integrity. In most of types of anomalies it even becomes a task to make the data consistent.
Suppose a database table with bad design
Article ID | Article Name | ... | ... | Author | Author Profile |
---|---|---|---|---|---|
186749 | Entity Relationship Model | ... | ... | A | profile-link-a |
352509 | Databases | ... | ... | B | profile-link-b |
730645 | Normalization | ... | ... | A | profile-link-a |
Insertion Anomaly It becomes difficult to insert data since we must add complete details of author with every article. The data becomes prone to inconsistency.
Updation Anomaly Just like insertion, while updating LinkedIn Profile of an author, let's suppose, we must update it in every article of that author.
Deletion Anomaly Suppose we delete a couple of articles and it eliminates an author. We may not want that. This scenario is known as deletion anomaly. It occurs when a last row of such design is deleted, and no such row is left which can tell that an author exists.
This is just one example. A bad and un-normalized database design can cause a whole lot of difficulties in handling the data.
The Normal Forms
1NF
In 1NF a table should have a key to distinguish the rows and no column of a tuple should be used to store two different type of attribute values.
2NF
1NF is must. There should be no partial dependency which means that no non-prime attribute can be dependent on a part of the key.
3NF
2NF is must. There should be no transitive dependency which means that there can be no non-prime attribute which identifies another non-prime attribute.
BCNF
3NF is must. Non-prime attribute cannot determine any prime attribute.
by Arifullah Jan and last modified on