Normalization in Database Management System
Normalization
Normalization is a method to organize the data of the table for transactional databases and data warehouses. In simple words, the process of reducing data redundancy in a relational database is called Normalization. Normalization is a refinement process. This includes creating tables and establishing relationship between those tables according to the rules, design both to protect data and make the database flexile by eliminating 2 factors:
1) Redundancy: Finding the same data in more than one location within database tables is called data redundancy.
2) Inconsistent dependency: If we have multiple copies of the same data then changes have to be made only once.
When we normalize the database, we have the following rules:
1. Minimizing the redundancy
2. Organizing data such that we have to modify it to make the changes at only one place.
3. Building a database such that we can access and manipulate the data quickly and efficiently without compromising the correctness of the data.
4. Arranging data into logical groupings such that each group describes a small part of the whole database.
Normalization is a part of successful database design. Without normalization the database system may be inaccurate, slow and inefficient.
Types of Normalization
1. 1NF: It states that the domain of an attribute must have only atomic (simple indivisible) values. It does not allow multivalued attributes(attributes having more than one value), composite attributes(attributes that can be divided into subparts) and their combinations.
2. 2NF: For a relation to be in 2NF, it must be in 1NF and must not contain any partial dependencies. If a proper subset of a candidate key determines the non prime attributes (attributes which are not a part of any candidate key) are called partial dependencies.
3. 3NF: For a relation to be in 3NF, it must be in 2NF and there should not be any transitive dependencies for non prime attributes(Let x,y and z be the attributes of a relation if x->y and y->z, then x->z via y is called transitive dependency).
Let us consider a functional dependency p->q (p determines q), then at least one of the following condition should hold:
1. p is a superkey.
2. q is a prime attribute(each attribute of q is a part of some candidate key).
4. BCNF(Boyce Codd Normal Form): BCNF is stricter than 3NF. Every relation in BCNF is in 3NF. However, a relation in 3NF is not necessary in BCNF. Let us consider a functional dependency p->q (p determines q), then p should be a superkey to be in BCNF.