Normalization in DBMS: 1NF, 2NF, 3NF and BCNF
Normalization : Normalization is a process of simplifying the relationship among data elements in a record. Normalization replaces a collection of data in a record structure by another record design which is simpler and more predictable. Normalization covert E-R model into Tables or Relations.
Benefits of Normalization : (i) Normalization reduces redundancy , it is the unnecessary repetition of a field.
(ii) During the process of normalization, we can identify dependencies that causing the problems when deleting or updating.
(iii) Normalization helps in simplify the structure of the tables.
First Normal Form (1NF) : When a table has no repeating groups, then it said to be First Normal Form (1NF). The repeating columns or fields present in an unauthorized table are removed from the table and put into a separate table. These tables are dependent on the parent table from which is derived. The key of these tables must also be a part of the parent table so that the parent table and the derived tables can be related to each other.
Second Normal Form (2NF) : If a table, all non-key fields are fully dependent on the whole key then it is said to be Second Normal Form (2NF). It means that each field in a table must depend upon the entire key, they don’t depend upon the combination key, they are a move to another table on whose key they depend on. The structure which doesn’t contain combination keys are automatically in the Second Normal Form.
Third Normal Form (3NF) : If a table, all non-key fields are fully independent of all other non-key fields then it is said to be Third Normal Form (3NF).
Boyce Codd Normal Form (BCNF) : Boyce Codd Normal Form (BCNF) was proposed as a simpler form of Third Normal Form (3NF). But it is much more strict than 3NF that means every relation in BCNF is also in 3NF.