ITM422 Final Exam Professor Robert Hendry Due: December 8, 2013
Please answer the following questions in your own words. All questions are worth 5 points each. Each question can/should be answered in less than 100 words. Best of luck.
1. You are working as a database architect for your organization. An application programmer that uses your database has a performance problem. The application she is writing has a looping structure that fires off a SQL statement for each looping iteration. In each SQL statement, only the WHERE statement is altered. What would be your recommendation to her?
If the database in question supports batch processing, the looping structure could instead be used to build a batch of SQL statements one by one, which are then executed together once the loop is done. Because the changes in each statement are fairly minimal, implementation of program logic to accomplish this would be relatively simple, and the performance would be considerably better.
2. In class we discussed Logical Units of Work (LUW) and transaction management. How many logical units of work can you have in a stored procedure? Also, discuss transaction management and how LUW(s) relate to triggers.
It is generally inadvisable/not standard practice to use commit statements within a stored procedure, which means a stored procedure will have only one logical unit of work. Transaction management is used to control changes made to the database, and triggers can be employed in conjunction with this functionality to maintain data integrity by rolling back transactions that clash with business rules.
3. In class we discussed object and system level permissions. Discuss what the
'default' access is for tables, and an accepted encapsulation technique for table access as far as SQL DELETE, UPDATE, and INSERTs are concerned.
By default, users...