Assignment

Question 1

Describe situations in whichdenormalization of a database schema would be used? Answer in your own words and give examples.

Question 2

Give a brief explanation of why the following query may not be promoting the query optimizer to use any index and show how it can be improved to be able to use an index.

SELECT ESSN, FName, LName, Salary, Age
FROM EMPLOYEE
WHERE Age < 35 OR Salary < 2000

Question 3

What is a schedule? How many number of serial schedules can be formed with 3 transactions? Write down all serial schedule.

Question 4

Check whether the schedules is conflict serializable or not? If yes, then why? Explain by drawing precedence graph.

a) S: R2(A); W2(A); R3(C); W2(B); W3(A); W3(C); R1(A);
R1(B); W1(A); W1(B)

b) S: R2(A); R3(C); W3(A); W2(A); W2(B); W3(C); R1(A);
R1(B); W1(A); W1(B)

Question 5

What are the problems they may encounter when the transactions are run concurrently? Explain with examples.