Databases — GATE Previous Year Questions

Which one of the following statements about normal forms is FALSE?

Suppose \(R_1(A, B)\) and \(R_2(C, D)\) are two relation schemas. Let \(r_1\) and \(r_2\) be the corresponding relation instances. \(B\) is a foreign key that refers to \(C\) in \(R_2\). If data in \(r_1\) and \(r_2\) satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

Consider the following relational schema:

employee(empId, empName, empDept)
customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM   employee E
WHERE  NOT EXISTS (SELECT custId
                   FROM   customer C
                   WHERE  C.salesRepId = E.empId
                     AND  C.rating <> 'GOOD');

Consider a B+ tree in which the search key is 12 bytes long, block size is 1024 bytes, record pointer is 10 bytes long and the block pointer is 8 bytes long. The maximum number of keys that can be accommodated in each non-leaf node of the tree is ______.

Consider two relations \(R_1(A, B)\) with the tuples \((1, 5), (3, 7)\) and \(R_2(A, C) = (1, 7), (4, 9)\). Assume that \(R(A, B, C)\) is the full natural outer join of \(R_1\) and \(R_2\). Consider the following tuples of the form \((A, B, C)\):

\(a = (1, 5, \text{null})\), \(b = (1, \text{null}, 7)\), \(c = (3, \text{null}, 9)\), \(d = (4, 7, \text{null})\), \(e = (1, 5, 7)\), \(f = (3, 7, \text{null})\), \(g = (4, \text{null}, 9)\)

Which one of the following statements is correct?

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?

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

The following functional dependencies hold true for the relational schema \(R\{V, W, X, Y, Z\}\):

V  -> W
VW -> X
Y  -> VX
Y  -> Z

Which of the following is irreducible equivalent for this set of functional dependencies?

Consider the following two statements about database transaction schedules:

I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable.

II. Timestamp-ordering concurrency control protocol with Thomas' Write Rule can generate view serializable schedules that are not conflict serializable.

Which of the above statements is/are TRUE?

Consider a relational table \(R\) that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?

Consider a database implemented using a B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of the search key is 12 bytes and the size of a tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block. The minimum number of disk accesses required to retrieve any record in the database is ______.