Performance of the database

Q: A shop stores information on Customers(cid, name, address, last_visit), Inventory(iid, name, cost, price, stock) and Purchases( cid, iid, when, price, quantity) FK cid REF Customers(cid) ON DELETE RESTRICT ON UPDATE CASCADE, FK iid REF Inventory(iid) ON DELETE RESTRICT ON UPDATE CASCADE.
Which of the following actions are more likely than not to be true regarding the performance of the database? Select all that apply.

 

1.	Using the CREATE INDEX statement to create an index to cid in Customers would make no difference at all.
2.	Creating indexes on Purchases may be harmful if we are expecting very frequent INSERT operations.
3.	Attribute iid of Inventory having an index is likely to assist with an INSERT operation on Customers.
4.	Creating an index on the stock attribute of Inventory would be useful if changes are often due to customers purchasing items (the integer stored in stock decreases by the quantity of every purchase for the product with the iid given in the Purchases tuple), but there are few INSERT operations on the table.

Solution:

NOTE : The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while doing insert or update, a database needs to insert or update the index values as well.
1. False
Explanation : Indexing in Customers is likely to improve its performance because it is less likely to have new customers very often so INSERT (or UPDATE) operations will be less on Customers. On the other hand, INSERT operations on Purchases is more likely and that would require lookup of cid in Customers table due to the presence of foreign key. So, indexing will decrease the lookup time in Customers table and hence, will increase the performance.
2. True
Explanation : As INSERT operations are expected to be more frequent, the indexing may slow down the operations and hence, may degrade the performance.
3. False
Explanation : INSERT operation on Customers has nothing to do with attribute iid of Inventory.
4. False
Explanation : Changes in stock attribute of Inventory due to customers purchasing items would be UPDATE operations. And UPDATE operations also take more time on tables having indexes and hence, may degrade the performance.