Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Question 7 (chapter 16) a. Normally an action such as an insertion/deletion/upda

ID: 3868055 • Letter: Q

Question

Question 7 (chapter 16)

a. Normally an action such as an insertion/deletion/update of a relation also updates all indices on the relation. Suppose, index maintenance is deferred, that is, it is performed only at the end of the transaction. What problems can this cause for a transaction that involves multiple steps.

b. Consider an update statement that scans a relation and deletes tuples with a salary field of less than 50, and reinserts the tuple with the salary value increased by 10. What problems could arise if the update to the relation

is done while the scan is in progress. Describe a technique to avoid the problem.

Explanation / Answer

a
Indexing is an important part of DBMS since indexes are required for performances, and these are so important that some DBMS does not allow serching on non indexed.
So if we deffer the indexing process, this might lead to anomali, such as, a searched item might be pointed to a wrong address/location based on previous indexing, OR altogether may report an item as 'not found' even if the item is present or vise-versa. Such situations may be avoided by marking index as invalid, but will have adverse effect on DBMS by hampering the search performance to a huge extinct.

b
Suppose salary entry of a tuple is 30, and the query is suppose to delete that entry and re-enter the tuple with salary as 40. But since the entry is re-entered as a completely new tuple, the statement would re consider the entry again for updation and will again delete the tuple and re-enter the tuple with salary as 50.
So as we have seen, the salary is increased by 20 instead of 10 causing anomali.

To fix this problem, we should update the salary feld only instead of deleting the tuple and re-entering the tuple. Another alternative is to list all the tuple with salary < 50 and then from the list either update the slary field or delete the tuple and re enter the tuple. (NOTE that this process is similar, but concurrent process must not be supported.

Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote