DBMS | Anomalies in Relational Model

2.1

We strongly recommend to refer below posts as a prerequisite of this.
DBMS | Relational Model Introduction and Codd Rules
DBMS | Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)

Anomalies
There are different types of anomalies which can occur in referencing and referenced relation which can be discussed as:

image

Insertion anomaly: If a tuple is inserted in referencing relation and referencing attribute value is not present in referenced attribute, it will not allow inserting in referencing relation. For Example, If we try to insert a record in STUDENT_COURSE with STUD_NO =7, it will not allow.

Deletion and Updation anomaly: If a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation. For Example, If we try to delete a record from STUDENT with STUD_NO =1, it will not allow. To avoid this, following can be used in query:

  • ON DELETE/UPDATE SET NULL: If a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will delete/update the tuple from referenced relation and set the value of referenced attribute to NULL.
  • ON DELETE/UPDATE CASCADE: If a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will delete/update the tuple from referenced relation and referencing relation as well.

This article is contributed by Sonal Tuteja. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

GATE CS Corner    Company Wise Coding Practice

Recommended Posts:



2.1 Average Difficulty : 2.1/5.0
Based on 10 vote(s)










Writing code in comment? Please use ide.geeksforgeeks.org, generate link and share the link here.