Anomalies in DBMS are the taboos . You do them and instantly your colleagues will curse you every time they have to do some operations on your DBMS. My rule is , that I always try to understand the meaning of word before understanding the concept, since English is not my native language . This practice has opened many doors for me, as designing a DBMS is exactly how we do things is real life. My advise for you is to do same .
Anomalies In DBMS
An anomaly is an abnormality, a blip on the screen of life that doesn’t fit with the rest of the pattern.
Remember Dr. Codd’s Rules? Physical and logical data independence and integrity of a database management is the most important part of it. Easy to say and very difficult to do . However , this is where you will thank this article because that is precisely what we have today . So let’s begin.
If you implement current ERD of University Management System , you will end up with multiple problems. It may be that you insert a student directly into department when you don’t have the student in the Student table. This will cause a lot of problems in updating and retrieval of records. We call these behaviour an Anomaly in DBMS.
Like Redundancy anomalies are also very important topic for exams if you’re a student, and for interviews if you are looking for a job . Following are the ones we should be concerned about .
INSERT Anomaly in Database
An Insert Anomaly occurs when attributes cannot be inserted into the database without the presence of other attributes. Usually when a child is inserted without parent.
Jerry is a new Student with department id 6. There is no Department with this Dept_ID 6. Hence , the anomaly. The usual behaviour should be a new department id with 6 and only then Student could have it.
UPDATE Anomaly in Database
When duplicated data is updated at one instance and not across all instances where it was duplicated. That’s an update anomaly . See below English department has now Dept_ID 8 , but unfortunately it was not updated in Student table.
DELETE Anomaly in Database
Now if someone decides to delete Computer Science department , he may end up deleting all student’s data who had the department of Computer Science. So to say deletion of some attribute which causes deletion of other attributes is deletion anomaly.
These anomalies are addressed by Normalization . The normalization makes sure that all these three issues and other possible be addressed at the time of designing. We will discuss them in our usual style in next few days.
If you like this and understood it , share with your friends and fellows.
Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows !
Happy Reading and Learning!