1) When a customer buys an item (or several) from the store, several changes to database tables will occur. Explain what those might be.
2) The owner of the antique store doesn't want to train his people in SQL, or to teach them about all the tables in the database. He asks you to write a program that will take sales information, and then make all the changes automatically, including whatever error checking may be necessary. The clerk will enter a customer phone number (assume this is used as the Customer ID primary key in the Customer
table), an item ID (from a tag on the item), and the sale price.
In pseudo code, outline how your program would record a sale. Show how you would use a transaction to insure the integrity of the database updates. Also, explain the error checking (such as verifying that the customer is already in the database) you would perform in your program, and how your program would recover from problems such as:
• The customer is not in the database.
• The item number is not in the database.
• The item is already sold.
3) When an item is sold, should the item row be removed from the Items table? Why or why not?
4) What transaction isolation level should apply to this application? Why do you make this choice?