Database Systems and Big Data – Statistics Assignment Help

Assignment Task

1. Consider the following Student table in a University Student Records database.
tudentId fName lName discipline

• 432987 Peter Gates Computing
• 452318 Mark Best Mathematics
• 485496 Joanne Perry Psychology
• 421267 David Murray Mathematics
• 489023 Jane Johnson Computing
• 458623 Nick Williams Psychology
• 415634 Mary Cummins Mathematics

Assume that admissions office created the following transaction to create the required report:
COMMIT;

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• START TRANSACTION;
• SELECT COUNT(*) AS ‘Number of Students’
• FROM student;
• SELECT discipline AS Discipline, COUNT(*) AS ‘Number of Students’
• FROM student
• GROUP BY discipline;
• COMMIT;
• Assume that when the above transaction has completed executing the first SELECT query that computes the grand total, two new students, who were allowed to late register, added themselves to the Student table,
• each running an insert query on the Student table:
• INSERT INTO student VALUES(465891,’John’,’Jobs’, ‘Computing’);
• INSERT INTO student VALUES(434567,’Linda’,’Ford’, ‘Psychology‘);

i) Explain the problem with the above described scenario involving multiple transactions running concurrently on the Student table.
ii) Explain how you would solve the problem from (i) using your knowledge of transaction management

2.Consider the following transaction scenario where Tis are transactions accessing several data items Xis:

Transaction Data Items Locked by Transaction Data Items Transaction is Waiting for

• T1 X2 X1,X3
• T2 X3, X10 X7, X8
• T3 X8 X4, X5
• T4 X7 X1
• T5 X1,X5 X3
• T6 X4,X9 X6
• T7 X6 X5

i) Produce a wait-for-graph for the above transaction scenario. (5)
ii) Using the wait-for-graph from part (i), determine whether the transactions are in a deadlock and explain how you detected deadlock. That is, you need to identify all the cycles in the WFG. (3)
iii) Explain how you would resolve the deadlock if it is detected in part(ii)