Data Base Management System | Week 8

9 months ago

user

5 minutes

Course Name: Data Base Management System

Course Link: Click Here

These are Data Base Management System Week 8 Assignment Answers

Q1. Assume that immediate database modification scheme is followed in this question. Consider the following log records for transactions TO, T1, T2, T3 and T4:

If there is a crash just after step 13 and the recovery of the system is successfully completed, identify the correct action for the above scenario.
a) After recovery completion, value of A will be 200.
b) After recovery completion, value of C will be 800.
c) After recovery completion, value of D will be 500.
d) After recovery completion, value of E will be 700.

Answer: a, b

These are Data Base Management System Week 8 Assignment Answers

Q2. Assume that immediate database modification scheme is followed in this question. Consider the following log records for transactions T0, T1, T2, T3 and T4:

If there is a crash just after step 14 and the recovery of the system is successfully completed, identify the correct action for the above scenario.
a) No Action: TO, T1; Redo: T3 and Undo: T2, T4
b) No Action: TO; Redo: T3 and Undo: T1, T2, T4
c) No Action: T1; Redo: T2, T3 and Undo: TO, T4
d) No Action: T1; Redo: T2, T4 and Undo: TO, T3

Answer: c) No Action: T1; Redo: T2, T3 and Undo: TO, T4

These are Data Base Management System Week 8 Assignment Answers

Q3. Identify the cost estimation of a query evaluation plan, if 6000 blocks are required to be transferred from the disk and the required number of disk seeks are 30.
Time to transfer one block: ty = 2 milliseconds.
Time for one seek: tg = 0.2 seconds.
a) 12.2 Seconds
b) 16 Seconds
c) 18 Seconds
d) 48 Seconds

Answer: c) 18 Seconds

These are Data Base Management System Week 8 Assignment Answers

Q4. Let us consider the following statistics for two relations Professor and Appointment Details:
Number of records of Professor: nprofessor = 4000.
Number of blocks of Professor: bprofessor = 50
Number of records of Appointment Details: npppointment Details = 2000.
Number of blocks of Appointment Details: bppointment Details = 20
Let us consider a natural join of Professor and Appointment Details relations (Professor» Appointment Details).
Identify the required number of block transfers in the worst case (enough memory only to hold one block of each relation) using Nested-loop join and assume Professor as the outer relation.
a) 120050 block transfers
b) 120000 block transfer
c) 80050 block transfers
d) 80000 block transfers

Answer: c) 80050 block transfers

These are Data Base Management System Week 8 Assignment Answers

Q5. Consider the following state of transactions and the statements below.

1. T1, T2 and T3 can be ignored.
2. T2 and T4 can be ignored.
3. T3, T4 and T5 need to be redone.
4. T4 and T5 need to be redone.
5. Only T6 needs to be undone.
Identify the correct group of statements from the options below.
a) 1), 2), 3), 5)
b) 1), 3), 4), 5)
c) 1), 4), 5)
d) 2), 4), 5)

Answer: c) 1), 4), 5)

Q6. Consider the following relational schema:
Professor(pid, pname, qualification, emaillD)
Department (dname, building, budget)
Appointment Details(pid, dname, date, time)
Two query trees are given below.

Identify the correct statement for the above two query trees.
a) Two query trees are equivalent as identical operations (irrespective of their positions) are used in both the trees.
b) Two query trees are not equivalent as selection or projection operation cannot be carried out before or after the natural join operation.
c) Two query trees are equivalent and the query tree of Figure 1 will lead to more efficient query processing.
d) Two query trees are equivalent and the query tree of Figure 2 will lead to more efficient query processing.

Answer: d) Two query trees are equivalent and the query tree of Figure 2 will lead to more efficient query processing.

These are Data Base Management System Week 8 Assignment Answers

Q7. Consider the following relational schema:
Professor (pid, pname, qualification, emaillD)
Department (dname, building, budget)
Appointment Details(pid, dname, date, time)
Four relational algebra queries are given below:
Q1: (9building=‘Watson’ (7date=*2021-04-15’ (Department I Appointment Details)))
Q2: (pyilding, date (Department > Appointment Details))
Q3: Tpname (Professor > Appointment Details)
Q4: lpname (9professor. pid=Appointment Details.pid(ProfessorxAppointment Details))
Identify the correct options from the options given below.
a) Q1 is equivalent to Q2.
b) Q1 is not equivalent to Q2.
c) Q3 is equivalent to Q4.
d) Q3 is not equivalent to Q4.

Answer: b, c

These are Data Base Management System Week 8 Assignment Answers

Q8. Consider the following relational schema:
Professor (pid, pname, qualification, emaillD)
Department (dname, building, budget)
Appointment (pid, dname, date, time)
A relational algebra expression given below:
Tpname (Ipid, pname (“building=‘Packard’ (Professor i Appointment » Department)))
Identify the most optimized relational algebra expression equivalent to the above given relational algebra expression.
a) IIpname,pid (7building=‘Packard’ (Professor X Appointment X Department) )
b) IIpname, pid (Professor i (Appointment MX (0pyj1ding= ‘Packard’ (Department))))
c) lIpnane, pid (Professor i (Appointment X Ilgnane (7building=*Packard’ (Department))))
d) IIpname (Professor » (Appointment M (7hy;1ding=”‘Packard’ (Department))))

Answer: d) IIpname (Professor » (Appointment M (7hy;1ding=”‘Packard’ (Department))))

These are Data Base Management System Week 8 Assignment Answers

Q9. Consider the following two relational algebra expressions (RA) given below:
RA I : 001 (PM02 Q) = (PM0102 Q)
RA II : ((PQ) AR) = (PA (QAR))
where P, Q, and R are relational algebra expressions.
Identify the correct statement(s) from the followings.
a) Both RA I and RA II are true.
b) Both RA I and RA II are false.
c) RA I is true but RA II is false.
d) RA I is false but RA II is true.

Answer: d) RA I is false but RA II is true.

These are Data Base Management System Week 8 Assignment Answers

Q10. Consider the log record of Transaction T1 with two operation instances 01 and 02 used in recovery system with early lock release, B+ tree based concurrency control.