Database Normalization
Normalization is a process of removing the redundancy from our table because redundancy may cause insert, delete or update anomalies.
But what is anomalies?
Database anomaly is normally the flaw in databases which occurs because of poor planning and storing everything in a flat database.
Insert anomaly :-
If a tuple is inserted in referencing table and referencing attribute value is not present in referenced table, it will not allow inserting in referencing relation.
Delete anomaly :-
If a tuple is deleted from referenced table and referenced attribute value is used by referencing table, it will not allow deleting the tuple from referenced table.
Update anomaly :-
If a tuple is updated from referenced table and referenced attribute value is used by referencing table, it will not allow updating the tuple from referenced table.
Types of Normalization :-
1NF(First Normal Form) :-
Each attribute of a table must have single values only then we can say that the table is in 1NF.
ID Name Courses
------------------
1 A c1, c2
2 E c3
3 M c2, c3
the above table is not in 1NF because it contain multiple values in single attribute.
ID Name Course
------------------
1 A c1
1 A c2
2 E c3
3 M c2
3 M c3
Now, the above table is in 1NF.
2NF(Second Normal Form) :-
In the second normal form, all non-key attributes are fully functional dependent on the primary key and It should be in 1NF.
CustomerID StoreID Location
-------------------------------
1 1 Delhi
1 3 Mumbai
2 1 Delhi
3 2 Bangalore
4 3 Mumbai
CustomerID and StoreID are candidate keys, and Location is a non-prime attribute.
However, if we notice that Candidate Key(CustomerID, StoreID) are not determining the location, only StoreID which is a part of Candidate Key is determining the location, that means our table is partially independent and it does not belong to 2NF.
For our table to be in 2NF, we must divide it.
Table Name :- ACustomerID StoreID
---------------------
1 1
1 3
2 1
3 2
4 3 Table Name :- BStoreID Location
-------------------
1 Delhi
2 Bangalore
3 Mumbai
Since StoreID is the candidate key which determines the location in table B and CustomerID and StoreID are the candidate keys which do not determine anything in table A, our table is in 2NF.
3NF(Third Normal Form):-
A relation is in 3NF when it is in 2NF and there is no transitive dependency or a relation is in 3NF, when it is in 2NF and all non-key attributes directly depend on candidate key.
ID Name State City
-----------------------------------------------
1 Ayush Madhya Pradesh Indore
2 Aman Haryana Ambala
3 Akshat Punjab Mohali
4 Amit Madhya Pradesh Indore
5 Riya Haryana Ambala
ID is a Candidate Key.
Name, State and City are Non prime attribute.
As we can see, ID determines the State, which means candidate key determines the non-prime attribute, but State itself determines the City, which means non-prime attribute determines the non-prime attribute. By using that ID, the city is also determined.
In simple word,
ID -> State and State -> City
So, ID -> City
which shows the transitive dependency in our table.
Table Name:- AID Name State
-----------------------------------
1 Ayush Madhya Pradesh
2 Aman Haryana
3 Akshat Punjab
4 Amit Madhya Pradesh
5 Riya Haryana
Table Name:- B State City
------------------------------
Madhya Pradesh Indore
Punjab Mohali
Haryana Ambala
Table A has Id as a Candidate key, and Table B has State as a Candidate key determining the Non-prime attribute.
BCNF(Boyce and Codd Normal Form) :-
Also known as a special form of 3NF as BCNF puts a restriction on 3NF. In BCNF each functional dependency should be derived from Candidate Key or Super Key.