Database Anomalies

Anomalies are caused by bad database design

Example : ACTIVITY(StudentID,Activity,Fee)

StudentID	Activity	Fee
101	Swimming	1000
101	Skating	1500
102	Bungy Jump	5000
102	Swimming	1000
103	Bungy Jump	5000
104	Swimming	1000

Insertion Anomaly: Occurs when a row cannot be added to a relation, because not all data is available (Or need to insert Dummy data)
Eg: If we want to store the new activity Trekking costs 500, we don't have place to put this information until a student takes up Trekking (Unless we need to create a fake student).

Deletion Anomaly: Occurs when data is deleted from a relation, another critical data is unintentionally deleted.
Eg: If we delete the record with StudentID=100, then we lose the price of Skating

Update Anomaly: Occurs when one must make many changes to reflect the modification of a single data
Eg: If the Cost of Swimming changes, then all entries with Swimming activity must be changed

Causes of Anomalies:

Anomalies are primarily caused by :

Data Redundancy :Replication of same fields in multiple tables other than Foreign keys
Functional Dependencies : Whose determinants are not Candidate keys including Partial Dependency , Transitive Dependency