GATE CS Databases questions

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

SELECT operation in SQL is equivalent to

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 …

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,null), b=(1,null,7), c=(3,null,9), d=(4,7,null), e=(1,5,7),$ $f=(3,7,null), g=(4,null,9).$ Which one of the …

With reference to the $B^+$ tree index of order $1$ shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query. "Get all records with a search key greater than or equal to $7$ and less than $15$ " is ______.

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

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

Consider the partial Schedule $S$ involving two transactions $T1$ and $T2$. Only the $\textit{read}$ and the $\textit{write}$ operations have been shown. The $\textit{read}$ operation on data item $P$ is denoted by $\textit{read(P)}$ and $\textit{write}$ operation on data item $P$ is denoted by $\textit{write(P)}$. …

Consider the relation $X(P,Q,R,S,T,U)$ with the following set of functional dependencies $F = \{ \\ \; \; \{P, R\} \rightarrow \{S, T\}, \\ \; \; \{P, S, U\} \rightarrow \{Q, R\} \\ \; \}$ Which of the following is the trivial functional dependency in $F^+$, where $F^+$ is closure to F?

Consider the following relation Cinema($\textit{theater, address, capacity}$) Which of the following options will be needed at the end of the SQL query SELECT P1.address FROM Cinema P1 such that it always finds the addresses of theaters with maximum capacity?

Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) You want to display the last names and hire dates of all latest hires in their respective departments in the location $\text{ID 1700.}$ You issue the following query: SQL>SELECT last-name, hire-date …

Given the following two statements: S1: Every table with two single-valued attributes is in $\text{1NF, 2NF, 3NF}$ and $\text{BCNF}.$ S2: $AB \to C, D \to E, E \to C$ is a minimal cover for the set of functional dependencies $AB \to C, D \to E, AB \to E, E \to C$. Which one of the following is CORRECT?

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?

Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table $R(a,b,c)$ where $a$ and $b$ together form the primary key, the following is a valid table definition. CREATE TABLE S ( a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d), FOREIGN KEY (a) references R) Which …

Consider the relation scheme $R = (E, F, G, H, I, J, K, L, M, N)$ and the set of functional dependencies $$\left\{ \{E, F \} \to \{G\}, \{F\} \to \{I, J\}, \{E, H\} \to \{K, L\}, \\ \{K\} \to \{M\}, \{L\} \to \{N\}\right\}$$ on $R$. What is the key for $R$?

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: select * from R where a in (select S.a from S)

Consider a join (relation algebra) between relations $r(R)$ and $s(S)$ using the nested loop method. There are $3$ buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming $\text{size}(r(R))<\text{size}(s(S)),$ the join will have fewer number of disk block accesses if

Consider the following schedule S of transactions $T1, T2, T3, T4:$ …

Given an instance of the STUDENTS relation as shown as below …

The maximum number of superkeys for the relation schema $R(E,F,G,H)$ with $E$ as the key is _____.