The process of applying the rules of Normalization to your database design is called Normalizing the database, or just Normalization.
Why Normalization?
- To see if the tables are structured correctly.
Why Normalization?
- To see if the tables are structured correctly.
- To divided your information items into the appropriate tables.
- To avoid data Redundancy (Video Example)
- To avoid data Redundancy (Video Example)
Five normal forms are widely accepted - the first normal form through the fifth normal form. But the first three are required for the majority of database designs.
1NF - First Normal Form
Number of things should be satisfied by applying 1NF -
1. Value should be Atomic.
First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values.
For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.
2. All entities in any column must be same type
3. Each column name must be unique
4. NO two rows are identical
Video link - https://www.youtube.com/watch?v=BtUF8JTgs7A
1. Value should be Atomic.
First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values.
For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.
2. All entities in any column must be same type
3. Each column name must be unique
4. NO two rows are identical
Video link - https://www.youtube.com/watch?v=BtUF8JTgs7A
2NF - Second Normal Form
First of all, should be satisfied 1NF.
All the No prime attribute should be fully Functional dependent on candidate key or each candidate key.
Second normal form requires that Each non-key column be fully dependent on the entire primary key, not on just part of the key. This rule applies when you have a primary key that consists of more than one column.
For example, suppose you have a table containing the following columns, where Order ID and Product ID form the primary key:
Order ID + Product ID (primary key)
Product Name
This design violates second normal form, because Product Name is dependent on Product ID, but not on Order ID, so it is not dependent on the entire primary key. You must remove Product Name from the table. It belongs in a different table (Products).
3NF - Third Normal Form
First of all, should be satisfied 1NF and 2NF.
Third normal form requires that not only every non-key column be dependent on the entire primary key, but that non-key columns be independent of each other.
Another way of saying this is that each non-key column must be dependent on the primary key.
For example, suppose you have a table containing the following columns:
ProductID (primary key)
Name
SRP
Discount
Assume that Discount depends on the suggested retail price (SRP). This table violates third normal form because a non-key column, Discount, depends on another non-key column, SRP. Column independence means that you should be able to change any non-key column without affecting any other column. If you change a value in the SRP field, the Discount would change accordingly, thus violating that rule. In this case Discount should be moved to another table that is keyed on SRP.
