Normalization:-
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Below are the different normal forms:-
1NF: Eliminate Repeating Groups
A database is said to be in First Normal Form when all entities have a unique identifier or key(primary key), and when every column in every table contains only a single value and doesn't contain a repeating group or composite field. "All columns (fields) must be atomic".
A database is said to be in First Normal Form when all entities have a unique identifier or key(primary key), and when every column in every table contains only a single value and doesn't contain a repeating group or composite field. "All columns (fields) must be atomic".
2NF: Eliminate Redundant Data
A database table is in 2NF when it is in 1NF and if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key. that means "table should be in1NF and every non-key column is fully dependent on the (entire) primary key".
As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns.
3NF: Eliminate Columns Not Dependent On Key
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.All attributes must be directly dependent on the primary key."In 2NF and every non-key column is mutually independent."
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related. "Remove the multivalued dependency."
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
De-normalization:-
It is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
When not to normalize:-
1)Want to keep tables simple so user can make their own queries
2)Avoid processing multiple tables
3)whenever performance issues.Testing shows Normalization has poorer performance
A database table is in 2NF when it is in 1NF and if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key. that means "table should be in1NF and every non-key column is fully dependent on the (entire) primary key".
As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns.
3NF: Eliminate Columns Not Dependent On Key
A database is in Third Normal Form when it is in Second Normal Form and each column that isn't part of the primary key doesn't depend on another column that isn't part of the primary key.All attributes must be directly dependent on the primary key."In 2NF and every non-key column is mutually independent."
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related. "Remove the multivalued dependency."
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
De-normalization:-
It is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
When not to normalize:-
1)Want to keep tables simple so user can make their own queries
2)Avoid processing multiple tables
3)whenever performance issues.Testing shows Normalization has poorer performance
