What is Data Anomalies and Normalization Please explain this

What is Data Anomalies and Normalization? Please explain this completely for a beginner. show clear examples

Solution

Anomalies are caused due to redundancy and poor construction of tables in ralational database.when the database is in un-normalize form it causes Anomalies.so it is very important to construct a tables which is not redundancy.
There are three types of Anomalies.
1.Insertion Anomalies.
2.Deletion Anomalies.
3.Updatae Anomalies.
Insertion Anomalies:insertion anomalies happens when we want insert some data in to data base is not possible because other related to this data is not present.
for example if we want to insert a student department whose has not yet had a Registration id.

Deletion Anomaly:
suppose for example if we want to delete some unwanted data from the table which is not required for futher use.and when we deleted this unwanted data along with this unwanted data .required data too get deleted .this is known as Deletion Anomaly.

example:suppose if we want to delete one student entire data .and it causes his departmanet also deleted

Update Anomaly:suppose a data is not updated properly there we can have multiple tuples which causes redundancy.

for example:suppose all staff roll had been changed and if all these changes are not updated properly then we can have multiple redunadancy records in table.
Update Anomaly is also called as Modified Anomaly

Normalization :
so far we have discussed about anomolies which causes redundancy of data if there is poor database construction and here is a techniques to reduce anomalies and redundance .
and techniques is calles Normalization.

Normalization is mainly used for reduce redundancy
and make proper sense of data.

Normalization Rules are of 4types. 1.FirstNormalForm
2.SecondNormalForm
3.ThirdNormalForm
4.BCNFFirstNormalForm:
in firstnormalform the colum should contains only unique values .which means no colum contains multiple values
for example:
id name city
1 a delhi,mumbai
2 b chennai
In order to avoid this we can separate this row into multiple recoreds
for example:
id name city
1 a mumbai
1 a delhi

SecondNormalForm:
in secondnormal form there should not be any partail dependency of any colum on primary key.
the primary key must depend on entire concatinated.
if any tabel is partially dependent on primary key then the tabel fails in second normal form.
example:
employee dept
a it
b ece
employee desg
a Asst.prof
b HOD

Third Normal Form:in order to satisfy third normal form every non prime atributes should be depend on primary key.example:
empid empname city state code
1 a z z 83
here city state depends on code so in order to satify third normal form we should make it a new table like:
empid empname code
and
code city state

BCNF:
stand for boycoded noraml form which is an extension for third normal form

for every non trival function dependencies
X->A and X must be super key