You are developing an order processing system supported by an RDBMS and wrote a piece of SQL code as a transaction to update the customer’s lifetime value as follows:
1> UPDATE Customers SET LifeTimeValue = LifeTimeValue + 99 WHERE CustomerId = 1
2> UPDATE Invoices SET TotalAmount = 99 WHERE InvoiceId = 1
However, your colleague wrote the same code in the reverse sequence. If only your transaction succeeds its updates, which of the following has been hindered? (Source: Wentz QOTD)
A. Entity integrity
B. Semantic integrity
C. Referential integrity
D. Availability
Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.
My suggested answer is D. Availability.
A deadlock between two transactions can be solved by the RDBMS that results in a victim transaction. The victim can retry updating the data later on. The victim’s availability of data is hindered, but integrity is not affected.
Database Integrity
- As transactions are not inserting records, they won’t affect the entity integrity.
- As transactions are updating the invoice.TotalAmount with a reasonable value, $99, they won’t affect semantic integrity.
- As transactions are updating the PK or FK and the transaction itself prevents referential integrity.
Reference
A BLUEPRINT FOR YOUR SUCCESS IN CISSP
My new book, The Effective CISSP: Security and Risk Management, helps CISSP aspirants build a solid conceptual security model. It is not only a tutorial for information security but also a study guide for the CISSP exam and informative reference for security professionals.
- It is available on Amazon.
- Readers from countries or regions not supported by Amazon can get your copy from the author’s web site.