Register

GO Classes DPP | GATE CS, DA | DBMS | Insertion - Deletion Anomaly

98 views
1 1 vote

Consider the single relation containing $\text{Course, Teacher, Room, Hour, StudentID, Grade}$.

\[
\begin{array}{|c|c|c|c|c|c|}
\hline
\text{Course} & \text{Teacher} & \text{Room} & \text{Hour} & \text{Student ID} & \text{Grade} \\
\hline
\text{CS 186} & \text{Hellerstein} & \text{Soda 306} & \text{12:30 TR} & \text{2154} & \text{A} \\
\hline
\text{CS 186} & \text{Hellerstein} & \text{Soda 306} & \text{12:30 TR} & \text{1129} & \text{A} \\
\hline
\text{CS 186} & \text{Hellerstein} & \text{Soda 306} & \text{12:30 TR} & \text{8510} & \text{AB} \\
\hline
\text{CS 186} & \text{Hellerstein} & \text{Soda 306} & \text{12:30 TR} & \text{4243} & \text{C} \\
\hline
\text{CS 286} & \text{Stonebraker} & \text{Cory 150} & \text{11:00 TR} & \text{9821} & \text{C} \\
\hline
\text{CS 286} & \text{Stonebraker} & \text{Cory 150} & \text{11:00 TR} & \text{8435} & \text{A} \\
\hline
\text{CS 286} & \text{Stonebraker} & \text{Cory 150} & \text{11:00 TR} & \text{2835} & \text{A} \\
\hline
\text{CS 150} & \text{Patterson} & \text{Soda 505} & \text{1:00 TRF} & \text{7235} & \text{AB} \\
\hline
\text{CS 150} & \text{Patterson} & \text{Soda 505} & \text{1:00 TRF} & \text{8510} & \text{AB} \\
\hline
\text{CS 150} & \text{Patterson} & \text{Soda 505} & \text{1:00 TRF} & \text{2449} & \text{AB} \\
\hline
\text{CS 170} & \text{Blum} & \text{Soda 405} & \text{2:25 MWF} & \text{9821} & \text{B} \\
\hline
\text{CS 170} & \text{Blum} & \text{Soda 405} & \text{2:25 MWF} & \text{8510} & \text{A} \\
\hline
\text{CS 170} & \text{Blum} & \text{Soda 405} & \text{2:25 MWF} & \text{2154} & \text{BC} \\
\hline
\end{array}
\]

Two situations occur:

  1. A new course is introduced, but no student has enrolled yet, so the course information cannot be stored cleanly without inventing or leaving unrelated student information.
     
  2. Every student enrolled in $\text{CS 186}$ drops the course. Deleting their tuples also removes the only stored information about the course, teacher, room, and time.

Which classification is correct?

  1. Situation $1$ is an update anomaly and Situation $2$ is an insertion anomaly.
     
  2. Situation $1$ is an insertion anomaly and Situation $2$ is a deletion anomaly.
     
  3. Both are deletion anomalies.
     
  4. Both are insertion anomalies.

1 Answer

1 1 vote

In Situation $\textbf{1}$, we want to store information about a new course independently of student enrollment.

But the schema forces course information and student information into the same tuple.

Therefore, we cannot conveniently insert the course fact without also supplying unrelated student information.

This is an insertion anomaly.

Now consider Situation $\textbf{2}$.

The tuples are deleted because the students dropped the course.

However, deleting those student tuples also unintentionally removes information about the course itself.

This is a deletion anomaly.

Therefore:

Situation $1 \to$ insertion anomaly

Situation $2 \to$ deletion anomaly

Hence, the correct answer is B.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

1 1 vote
1 1 answer
82
82 views
GO Classes asked Sep 14
82 views
GO Classes DPP | GATE CS, DA | DBMS | Update Anomaly
A relation contains four tuples for course $\text{CS 186}$, and every tuple stores the teacher as $\text{Hellerstein}$.Lets consider the set of data is as follows :\[\beg...
2 2 votes
2 2 answers
154
154 views
GO Classes asked Sep 14
154 views
GO Classes DPP | GATE CS, DA | DBMS | Decomposition
Consider relation $R(A,B,C,D,E,F)$ with$A\to B$$A\to C$$F\to D$$F\to E$Suppose $R$ is decomposed into $R_1(A,B,C)$ and $R_2(D,E,F)$.Which statement best describes this de...
1 1 vote
1 1 answer
118
118 views
GO Classes asked Sep 14
118 views
GO Classes DPP | GATE CS, DA | DBMS | Decomposition & Redundancy
A redundant relation is decomposed appropriately to improve its logical database design.Which of the following benefits can be relied upon as a purpose of the decompositi...
1 1 vote
1 1 answer
85
85 views
GO Classes asked Sep 14
85 views
GO Classes DPP | GATE CS, DA | DBMS | Natural Join
Consider $\text{Postings(post, position, user, ptext)}$.Two aliases of this relation are used:$\text{P1 = Postings}$$\text{P2 = Postings}$Consider the query:SELECT count(...