What is insertion anomaly in dbms when null values can be inserted to remaining positions?
Answers
Answer:
due to loss of information and duplicate data in the given database. Anomalies causes problem during modification of the data in the database table. There are three types of anomalies. They are
• Insertion anomaly
• Deletion anomaly
• Update anomaly
Insertion anomaly
Insertion anomalies occur when inserting the new record into the database table. In some cases there is a chance to get null values during insertion of row in the database table. The problem in insertion anomaly is data inconsistency. It is not possible to add a new data when data value for a particular field is not known.
Example
Consider the database table product with the attributes such as product id, product name, quantity, and price.
Product id
Product name
Quantity
Price
D1
Urad dhal
200g
52
R2
Rice
10kg
459
T4
Tea powder
500g
80
Here the user needs to enter a new row
S7
Sugar
500kg
In the above row price of the product sugar is not decided yet. So the data in that field is absent and the entire row cannot be added to the table. This causes insertion anomaly.
Update anomaly
Update anomalies occur when data redundancy in data values of a particular attribute. Incorrect data in the table must have to be changed. Change in single record value does not make the data consistent. There may be many records in a database table which takes more time to modify the values. If the modification is not done in all the relevant rows the database will result in data inconsistency.
Example
Consider the table bank info which contains the attributes such as account number, account type, name, address, transaction date, balance.
Acc no
Acc type
Name
Address
Trans date
balance
1014
Savings
George
13,kondwa,michigun
01/01/2014
40000
1015
Savings
Henry
14,florell,lansing
05/01/2014
35000
1015
Current
Henry
14,florell,lansing
24/01/2014
55000
1019
Current
Sara
14,florell,lansing
10/02/2014
100000
The address of 1015 and 1019 is changed to 23, apt-125, New Jersey. It is not enough to update a single row. The address of the entire row containing the wrong value must be modified to make the data consistent in the relation.