Question No: $01$

$($a$)$In a certain log$-$based recovery system with checkpointing, following entries are seen in a partial log: $[$T$1,$ start$]$; $[$T$1,$ A$,10,25]$; $[$T$2,$ start$]$; $[$T$3,$ start$]$; $[$T$2,$ A$,25,5]$; $[$T$2,$ Commit$]$; $[$T$1,$ B$,5,35]$; $[$T$3,$ C$,2000,20]$ ; $[$T$4,$ start$]$; $[$T$1,$ C$,20,10]$; $[$checkpoint$]$; $[$T$4,$ commit$]$; $[$T$3,$ B$,35,18]$; $[$T$5,$ start$]$; $[$T$5,$ C$,10,80]$; $//$System Crush$//.$

Now give actions need to be taken $($as part of the recovery process$)$ for each of the above transactions $($T$1,$ T$2,$ T$3,$ T$4$ and T$5),$ if immediate modification technique is used in recovery.

$[3$ Marks$]$

$($b$)$For the following SQL query, give the final optimized query tree which is the result of applying a number of heuristic optimization rules. $[2$ Marks$]$

SELECT B$.$bid, B$.$Titie, P$.$pname, p$.$plocation

FROM Book B$,$ PublisherP

WHERE B$.$ pid $=$ P$.$pid and P$.$category $=B^{\text{'}}$ and B$.$ price $>500$;