Gate MCQ Quiz Hub

1. R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
A->B, B->CD
A->B, B->C, C->D
AB->C, C->AD
A ->BCD

2. Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x. create table geq ( ib integer not null ub integer not null primary key 1b foreign key (ub) references geq on delete cascade ) Which of the following is possible if a tuple (x,y) is deleted?
A tuple (z,w) with z > y is deleted
A tuple (z,w) with z > x is deleted
A tuple (z,w) with w < x is deleted
The deletion of (x,y) is prohibited

3. Given the following relation instance. x y z 1 4 2 1 5 3 1 6 3 3 2 2 Which of the following functional dependencies are satisfied by the instance? (GATE CS 2000)
XY -> Z and Z -> Y
YZ -> X and Y -> Z
YZ -> X and X -> Z
XZ -> Y and Y -> X

4. Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies F = { {P, R} → {S,T}, {P, S, U} → {Q, R} } Which of the following is the trivial functional dependency in F+ is closure of F?
{P,R}→{S,T}
{P,R}→{R,T}
{P,S}→{S}
{P,S,U}→{Q}

5. A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables: Insert into department values (1, 'Mathematics') Insert into department values (2, 'Physics') Insert into student values (l, 'Navin', 1) Insert into student values (2, 'Mukesh', 2) Insert into student values (3, 'Gita', 1) How many rows and columns will be retrieved by the following SQL statement? Select * from student, department
0 row and 4 columns
3 rows and 4 columns
3 rows and 5 columns
6 rows and 5 columns

6. A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies F1 → F3 F2→ F4 (F1 . F2) → F5 In terms of Normalization, this table is in
1 NF
2 NF
3 NF
none of the above

7. Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?
V X Y Z
V W X Z
V W X Y
V W X Y Z

8. Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is
in BCNF
in 3NF, but not in BCNF
in 2NF, but not in 3NF
not in 2NF

9. Which option is true about the SQL query given below? SELECT firstName, lastName FROM Employee WHERE lastName BETWEEN 'A%' AND 'D%';
It will display all the employees having last names starting with the alphabets 'A' till 'D' inclusive of A and exclusive of D.
It will throw an error as BETWEEN can only be used for Numbers and not strings.
It will display all the employees having last names starting from 'A' and ending with 'D'.
It will display all the employees having last names in the range of starting alphabets as 'A' and 'D' excluding the names starting with 'A' and 'D'.

10. Which of the given options define a transaction correctly?
A transaction consists of DDL statements on the database schema.
A transaction consists of COMMIT or ROLLBACK in a database session.
A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session.
A transaction consists of collection of DML and DDL statements in different sessions of the database.

11. Consider the following transactions with data items P and Q initialized to zero: T1: read (P) ; read (Q) ; if P = 0 then Q : = Q + 1 ; write (Q) ; T2: read (Q) ; read (P) ; if Q = 0 then P : = P + 1 ; write (P) ; Any non-serial interleaving of T1 and T2 for concurrent execution leads to
A serializable schedule
A schedule that is not conflict serializable
A conflict serializable schedule
A schedule for which a precedence graph cannot be drawn

12. Which of the following concurrency control protocols ensure both conflict serialzability and freedom from deadlock? I. 2-phase locking II. Time-stamp ordering
I only
II only
Both I and II
Neither I nor II

13. Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below. T1: r1(X); r1(Z); w1(X); w1(Z) T2: r2(Y); r2(Z); w2(Z) T3: r3(Y); r3(X); w3(Y) S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z) S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z) Which one of the following statements about the schedules is TRUE?
Only S1 is conflict-serializable
Only S2 is conflict-serializable
Both S1 and S2 are conflict-serializable
Neither S1 nor S2 is conflict-serializable

14. 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=12000 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

15. 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=12000 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

16. Consider the following transaction involving two bank accounts x and y. read(x); x := x – 50; write(x); read(y); y := y + 50; write(y) The constraint that the sum of the accounts x and y should remain constant is that of
Atomicity
Consistency
Isolation
Durability

17. Consider the following transaction involving two bank accounts x and y. read(x); x := x – 50; write(x); read(y); y := y + 50; write(y) The constraint that the sum of the accounts x and y should remain constant is that of
Atomicity
Consistency
Isolation
Durability

18. Consider a simple checkpointing protocol and the following set of operations in the log. (start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7); (checkpoint); (start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3); (write, T3, z, 7, 2); If a crash happens now and the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list
Undo: T3, T1; Redo: T2
Undo: T3, T1; Redo: T2, T4
Undo: none; Redo: T2, T4, T3; T1
Undo: T3, T1, T4; Redo: T2

19. Which level of locking provides the highest degree of concurrency in a relational data base?
Page
Table
Row
Page, table and row level locking allow the same degree of concurrency

20. Which one of the following is NOT a part of the ACID properties of database transactions? c. d.
Atomicity
Consistency
Isolation
Deadlock-freedom

21. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will
guarantee serializability and deadlock-freedom
guarantee neither serializability nor deadlock-freedom
guarantee serializability but not deadlock-freedom
guarantee deadlock-freedom but not serializability

22. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will
guarantee serializability and deadlock-freedom
guarantee neither serializability nor deadlock-freedom
guarantee serializability but not deadlock-freedom
guarantee deadlock-freedom but not serializability

23. Suppose a database schedule S involves transactions T1, ....Tn. Construct 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

24. Consider the following database schedule with two transactions, T1 and T2. S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2; where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti . Which one of the following statements about the above schedule is TRUE?
S is non-recoverable
S is recoverable, but has a cascading abort
S does not have a cascading abort
S is strict

25. Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by transac­tion N on object O.] (S1) 2RA 2WA 3RC 2WB 3WA 3WC 1RA 1RB 1WA 1WB (S2) 3RC 2RA 2WA 2WB 3WA 1RA 1RB 1WA 1WB 3WC (S3) 2RA 3RC 3WA 2WA 2WB 3WC 1RA 1RB 1WA 1WB Which of the following statements is TRUE?
S1, S2 and S3 are all conflict equivalent to each other
No two of S1, S2 and S3 are conflict equivalent to each other
S2 is conflict equivalent to S3, but not to S1
S1 is conflict equivalent to S2, but not to S3

26. Which of the following statement is/are incorrect? A: A schedule following strict two phase locking protocol is conflict serializable as well as recoverable. B: Checkpoint in schedules are inserted to ensure recoverability.
Only 1
Only 2
Both 1 and 2
None

27. An index is clustered, if
it is on a set of fields that form a candidate key
it is on a set of fields that include the primary key
the data records of the file are organized in the same order as the data entries of the index
the data records of the file are organized not in the same order as the data entries of the index

28. A clustering index is defined on the fields which are of type
non-key and ordering
non-key and non-ordering
key and ordering
key and non-ordering

29. A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called
Dense
Sparse
Clustered
Unclustered

30. Given the following input (4322, 1334, 1471, 9679, 1989, 6171, 6173, 4199) and the hash function x mod 10, which of the following statements are true? 1. 9679, 1989, 4199 hash to the same value 2. 1471, 6171 hash to the same value 3. All elements hash to the same value 4. Each element hashes to a different value
1 only
2 only
1 and 2 only
3 or 4

31. How many serial schedules are possible using n-transactions?
(n-1)!
n!
n(n-1)!
n(n+1)! / 2

32. Which of the following is wrong? The goal of concurrent execution is
Improved Throughput
Reduced Probability of Deadlock
Improved resource Utilization
Reduced waiting time

33. Consider the following ordering of transactions: T1 : R(X); T2 : R(X); T1 : w(x); T1 : r(y); T2 : w(x); T2 : Commit; T1: w(y) ; T1: Commit Which of the following is true?
It is a strict schedule
Cascade less schedule
Irrecoverable
Recoverable

34. In strict two-phase locking protocol
All exclusive mode lock taken by transaction be held until transaction commits
All exclusive mode locks taken by transaction can be released before transaction commits
All locks can be released before transaction commits
None of these

35. Suppose three are 3 transactions T22, T23, T24 with timestamps 10, 20, 30. Now T23 occupies a data item that is required by T22 and T24. Among the four possibilities what will be true in wait–die schema?
If T22 request the data item, T22 will wait
If T24 request the data item, T24 will wait
If T22 request the data item, T23 will wait
If T24 request the data item, T23 will wait

36. Two transactions T₁ and T₂ are given as follows: T₁: r₁ (A); w₁(A); r₁ (B); w₁(B) Find the no. of conflict serializable schedules that can be formed over T₁ and T₂.
12
13
14
15

37. Which of the following schedule are not conflict serializable?
r₁(A); w₁(A); r₂ (A); w₂(A); w₁(B)
r₁(A); r₁ (B); w₂(A); r₃(A); w₁(B); w₃(A); r₂ (B); w₂ (B)
r₁(A); w₁(A); r₂ (A); w₂(A); w₁(B)
w₃ (A); r₁ (A); w₁ (B); r₂ (B); w₂(c); r₃ (c)