Transactions and concurrency control

Question 21

Let R(a,b,c) and S(d,e,f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S 1. Insert into R 2. Insert into S 3. Delete from R 4. Delete from S Which of the following can cause violation of the referential integrity constraint above?

• None of (1), (2), (3) or (4) can cause its violation
• All of (1), (2), (3) and (4) can cause its violation
• Both (1) and (4) can cause its violation
• Both (2) and (3) can cause its violation

Question 22

Assume that Ti requests a lock held by Tj. The following table summarizes the actions taken for wait-die and wound-wait scheme: Fill correct status of Ti and Tj at W, Y, X, and Z respectively.

• Ti dies, Ti waits, Ti waits, and Tj aborts respectively.
• Ti dies, Ti waits, Ti waits, and Tj aborts respectively.
• Ti waits, Ti dies, Ti waits, and Tj aborts respectively.
• None of these

Question 23

Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item X, denoted by r(X) and w(X) respectively. Which one of them is conflict serializable ?

S1: r1(X); r2(X); w1(X); r3(X); w2(X)

S2: r2(X); r1(X); w2(X); r3(X); w1(X)

S3: r3(X); r2(X); r1(X); w2(X); w1(X)

S4: r2(X); w2(X); r3(X); r1(X); w1(X)

• S₁
• S₂
• S₃
• S₄

Question 24

Suppose a database schedule S involves transactions T1, T2, .............,Tn. Consider the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule ?

• Topological order
• Depth - first order
• Breadth - first order
• Ascending order of transaction indices

Question 25

ACID properties of a transactions are

• Atomicity, consistency, isolation, database
• Atomicity, consistency, isolation, durability
• Atomicity, consistency, integrity, durability
• Atomicity, consistency, integrity, database

Question 26

Which of the following concurrency control protocol ensures both conflict serializability and free from deadlock?

• Time stamp ordering
• 2 Phase locking
• Both (a) and (b)
• None of the above

Question 27

Which one of these is characteristic of RAID 5?

• Dedicated parity
• Double parity
• Hamming code parity
• Distributed parity

Question 28

Consider following schedules involving two transactions: S1 : r1(X); r1(Y); r2(X); r2(Y); w2(Y); w1(X) S2 : r1(X); r2(X); r2(Y); w2(Y); r1(Y); w1(X) Which of the following statement is true?

• Both S1 and S2 are conflict serializable.
• S1 is conflict serializable and S2 is not conflict serializable.
• S1 is not conflict serializable and S2 is conflict serializable.
• Both S1 and S2 are not conflict serializable.

Question 29

Consider the following relation:

Works (emp_name, company_name, salary)

Here, emp_name is primary key. Consider the following SQL query

Select emp_name
From works T
where salary > (select avg (salary)
                from works S
                where T.company _ name = S.company _ name)

The above query is for following :

• Find the highest paid employee who earns more than the average salary of all employees of his company.
• Find the highest paid employee who earns more than the average salary of all the employees of all the companies.
• Find all employees who earn more than the average salary of all employees of all the companies.
• Find all employees who earn more than the average salary of all employees of their company.

Question 30

Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment and then apply a 5% interest. 1. T1 start 2. T1 B old=1200 new=10000 3. T1 M old=0 new=2000 4. T1 commit 5. T2 start 6. T2 B old=10000 new=10500 7. T2 commit Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery procedure?

• We must redo log record 6 to set B to 10500

• We must undo log record 6 to set B to 10000 and then redo log records 2 and 3

• We need not redo log records 2 and 3 because transaction T1 has committed

• We can apply redo and undo operations in arbitrary order because they are idempotent.