laboursonline – MCQs, Mock Tests | Gate GATE STUDY MATERIAL /DBMS SET 2

R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

1. A->B, B->CD
2. A->B, B->C, C->D
3. AB->C, C->AD
4. A ->BCD

✅ Correct Answer: 3

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?

1. A tuple (z,w) with z > y is deleted
2. A tuple (z,w) with z > x is deleted
3. A tuple (z,w) with w < x is deleted
4. The deletion of (x,y) is prohibited

✅ Correct Answer: 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)

1. XY -> Z and Z -> Y
2. YZ -> X and Y -> Z
3. YZ -> X and X -> Z
4. XZ -> Y and Y -> X

✅ Correct Answer: 2

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?

1. {P,R}→{S,T}
2. {P,R}→{R,T}
3. {P,S}→{S}
4. {P,S,U}→{Q}

✅ Correct Answer: 3

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

1. 0 row and 4 columns
2. 3 rows and 4 columns
3. 3 rows and 5 columns
4. 6 rows and 5 columns

✅ Correct Answer: 4

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. 1 NF
2. 2 NF
3. 3 NF
4. none of the above

✅ Correct Answer: 1

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

1. V X Y Z
2. V W X Z
3. V W X Y
4. V W X Y Z

✅ Correct Answer: 2

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is

1. in BCNF
2. in 3NF, but not in BCNF
3. in 2NF, but not in 3NF
4. not in 2NF

✅ Correct Answer: 4

Which option is true about the SQL query given below? SELECT firstName, lastName FROM Employee WHERE lastName BETWEEN 'A%' AND 'D%';

1. It will display all the employees having last names starting with the alphabets 'A' till 'D' inclusive of A and exclusive of D.
2. It will throw an error as BETWEEN can only be used for Numbers and not strings.
3. It will display all the employees having last names starting from 'A' and ending with 'D'.
4. 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'.

✅ Correct Answer: 1

Which of the given options define a transaction correctly?

1. A transaction consists of DDL statements on the database schema.
2. A transaction consists of COMMIT or ROLLBACK in a database session.
3. 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.
4. A transaction consists of collection of DML and DDL statements in different sessions of the database.

✅ Correct Answer: 4

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

1. A serializable schedule
2. A schedule that is not conflict serializable
3. A conflict serializable schedule
4. A schedule for which a precedence graph cannot be drawn

✅ Correct Answer: 2

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

1. I only
2. II only
3. Both I and II
4. Neither I nor II

✅ Correct Answer: 2

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?

1. Only S1 is conflict-serializable
2. Only S2 is conflict-serializable
3. Both S1 and S2 are conflict-serializable
4. Neither S1 nor S2 is conflict-serializable

✅ Correct Answer: 1

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?

1. We must redo log record 6 to set B to 10500
2. We must undo log record 6 to set B to 10000 and then redo log records 2 and 3
3. We need not redo log records 2 and 3 because transaction T1 has committed
4. We can apply redo and undo operations in arbitrary order because they are idempotent

✅ Correct Answer: 4

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?

1. We must redo log record 6 to set B to 10500
2. We must undo log record 6 to set B to 10000 and then redo log records 2 and 3
3. We need not redo log records 2 and 3 because transaction T1 has committed
4. We can apply redo and undo operations in arbitrary order because they are idempotent

✅ Correct Answer: 4

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

1. Atomicity
2. Consistency
3. Isolation
4. Durability

✅ Correct Answer: 2

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

1. Atomicity
2. Consistency
3. Isolation
4. Durability

✅ Correct Answer: 2

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

1. Undo: T3, T1; Redo: T2
2. Undo: T3, T1; Redo: T2, T4
3. Undo: none; Redo: T2, T4, T3; T1
4. Undo: T3, T1, T4; Redo: T2

✅ Correct Answer: 1

Which level of locking provides the highest degree of concurrency in a relational data base?

1. Page
2. Table
3. Row
4. Page, table and row level locking allow the same degree of concurrency

✅ Correct Answer: 3

Which one of the following is NOT a part of the ACID properties of database transactions? c. d.

1. Atomicity
2. Consistency
3. Isolation
4. Deadlock-freedom

✅ Correct Answer: 4

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

1. guarantee serializability and deadlock-freedom
2. guarantee neither serializability nor deadlock-freedom
3. guarantee serializability but not deadlock-freedom
4. guarantee deadlock-freedom but not serializability

✅ Correct Answer: 1

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

1. guarantee serializability and deadlock-freedom
2. guarantee neither serializability nor deadlock-freedom
3. guarantee serializability but not deadlock-freedom
4. guarantee deadlock-freedom but not serializability

✅ Correct Answer: 1

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?

1. Topological order
2. Depth-first order
3. Breadth-first order
4. Ascending order of transaction indices

✅ Correct Answer: 1

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?

1. S is non-recoverable
2. S is recoverable, but has a cascading abort
3. S does not have a cascading abort
4. S is strict

✅ Correct Answer: 3

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 transaction 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?

1. S1, S2 and S3 are all conflict equivalent to each other
2. No two of S1, S2 and S3 are conflict equivalent to each other
3. S2 is conflict equivalent to S3, but not to S1
4. S1 is conflict equivalent to S2, but not to S3

✅ Correct Answer: 4

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.

1. Only 1
2. Only 2
3. Both 1 and 2
4. None

✅ Correct Answer: 2

An index is clustered, if

1. it is on a set of fields that form a candidate key
2. it is on a set of fields that include the primary key
3. the data records of the file are organized in the same order as the data entries of the index
4. the data records of the file are organized not in the same order as the data entries of the index

✅ Correct Answer: 3

A clustering index is defined on the fields which are of type

1. non-key and ordering
2. non-key and non-ordering
3. key and ordering
4. key and non-ordering

✅ Correct Answer: 1

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

1. Dense
2. Sparse
3. Clustered
4. Unclustered

✅ Correct Answer: 3

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. 1 only
2. 2 only
3. 1 and 2 only
4. 3 or 4

✅ Correct Answer: 4

How many serial schedules are possible using n-transactions?

1. (n-1)!
2. n!
3. n(n-1)!
4. n(n+1)! / 2

✅ Correct Answer: 2

Which of the following is wrong? The goal of concurrent execution is

1. Improved Throughput
2. Reduced Probability of Deadlock
3. Improved resource Utilization
4. Reduced waiting time

✅ Correct Answer: 2

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?

1. It is a strict schedule
2. Cascade less schedule
3. Irrecoverable
4. Recoverable

✅ Correct Answer: 2

In strict two-phase locking protocol

1. All exclusive mode lock taken by transaction be held until transaction commits
2. All exclusive mode locks taken by transaction can be released before transaction commits
3. All locks can be released before transaction commits
4. None of these

✅ Correct Answer: 1

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?

1. If T22 request the data item, T22 will wait
2. If T24 request the data item, T24 will wait
3. If T22 request the data item, T23 will wait
4. If T24 request the data item, T23 will wait

✅ Correct Answer: 1

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₂.

1. 12
2. 13
3. 14
4. 15

✅ Correct Answer: 4

Which of the following schedule are not conflict serializable?

1. r₁(A); w₁(A); r₂ (A); w₂(A); w₁(B)
2. r₁(A); r₁ (B); w₂(A); r₃(A); w₁(B); w₃(A); r₂ (B); w₂ (B)
3. r₁(A); w₁(A); r₂ (A); w₂(A); w₁(B)
4. w₃ (A); r₁ (A); w₁ (B); r₂ (B); w₂(c); r₃ (c)

✅ Correct Answer: 4