GATE CS Databases questions

Let $P, Q, R$ and $S$ be the attributes of a relation in a relational schema. Let $X \rightarrow Y$ indicate functional dependency in the context of a relational database, where $X, Y \subseteq\{P, Q, R, S\}$. Which of the following options is/are always true?

In the context of relational database normalization, which of the following statements is/are true?

Consider a relational database schema with two relations $R(P, Q)$ and $S(X, Y)$. Let $E=\{\langle u\rangle \mid \exists v \exists w\langle u, v\rangle \in R \wedge\langle v, w\rangle \in S\}$ be a tuple relational calculus expression. Which one of the following relational algebraic expressions is equivalent to $E$ ?

Consider a relational database schema with a relation $R(A, B, C, D)$. If $\{A, B\}$ and $\{A, C\}$ are the only two candidate keys of the relation $R$, then the number of superkeys of relation $R$ is $\_\_\_\_$. (answer in integer)

In the context of DBMS, consider the two sets $\mathbf{T}$ and $\mathbf{S}$ given below. …

Consider concurrent execution of two transactions $T 1$ and $T 2$ in a DBMS, both of which access a data object $A$. For these two transactions to not conflict on $A$, which one of the following statements must be true?

In the context of schema normalization in relational DBMS, consider a set $\text{F}$ of functional dependencies. The set of all functional dependencies implied by $\text{F}$ is called the closure of $\text{F}$. To compute the closure of $\text{F}$, Armstrong's Axioms can be applied. Consider $X, Y$, and $Z$ as sets of attributes over a relational …

An index in a DBMS is said to be dense if an index entry appears for every search-key value in the indexed file. Otherwise it is called a sparse index. Consider the following two statements. $\text{S1}$: A hash index must be a dense index $\text{S1}$: A $B^{+}$ tree index can be a sparse index Which one of the following options is correct?

A schedule of three database transactions $T_{1}, T_{2}$, and $T_{3}$ is shown. $R_{i}(A)$ and $W_{i}(A)$ denote read and write of data item $A$ by transaction $T_{i}, i=1,2,3$. The transaction $T_{1}$ aborts at the end. Which other transaction(s) will be required to be rolled back? …

Consider the following $B^{+}$tree with $5$ nodes, in which a node can store at most $3$ key values The value $23$ is now inserted in the $B^{+}$tree. Which of the following options(s) is/are CORRECT?

Consider two relations describing teams and players in a sports league: teams(tid, tname): tid, tname are team-id and team-name, respectively players(pid,pname,tid): pid, pname, and tid denote player-id, playername and the team-id of the player, respectively Which ONE of the following tuple relational calculus queries returns the name of the players who play …

Consider a relational schema team $\text{(name,city,owner)}$, with functional dependencies $\{\text{name} \rightarrow \text{city}, \text{name} \rightarrow \text{owner} \}$. The relation team is decomposed into two relations, $\text{t1(name,city)}$ and $\text{t2(name, owner)}$. Which of the following statement(s) is/are TRUE?

Consider the following database tables of a sports league. player (pid, pname, age) team(tid, tname, city, cid) coach (cid, cname) members (pid,tid) An instance of the table and an SQL query are given. …

Consider the database transactions T1 and T2 , and data items X and Y . Which of the schedule(s) is/are conflict serializable? …

Consider the following relational schema: Students ($\underline{\text{rollno: integer}},$ name: string, age: integer, cgpa: real) Courses ($\underline{\text{courseno: integer}},$ cname: string, credits: integer) Enrolled ($\underline{\text{rollno: integer}},$ $\underline{\text{courseno: integer}},$ grade: string) Which of the following options is/are …

In a $\mathrm{B}^{+}$- tree where each node can hold at most four key values, a root to leaf path consists of the following nodes: $$\mathrm{A}=(49,77,83,-), \mathrm{B}=(7,19,33,44), \mathrm{C}=\left(20^{*}, 22^{*}, 25^{*}, 26^{*}\right)$$ The $*$-marked keys signify that these are data entries in a leaf. Assume that a pointer between keys $k_{1}$ and …

​​​​An audit of a banking transactions system has found that on an earlier occasion, two joint holders of account $A$ attempted simultaneous transfers of Rs. $10000$ each from account $A$ to account $B$. Both transactions read the same value, Rs.$11000$, as the initial balance in $A$ and were allowed to go through. $B$ was credited Rs.$10000$ …

​​​​Consider the following relational schema along with all the functional dependencies that hold on them. $$\begin{array}{l} R 1(A, B, C, D, E):\{D \rightarrow E, E A \rightarrow B, E B \rightarrow C\} \\ R 2(A, B, C, D):\{A \rightarrow D, A \rightarrow B, C \rightarrow A\} \end{array}$$ Which of the following statement(s) is/are TRUE?

Let $\mathrm{S}$ be the specification: "Instructors teach courses. Students register for courses. Courses are allocated classrooms. Instructors guide students." Which one of the following $\mathrm{ER}$ diagrams CORRECTLY represents $\mathrm{S}$?

In a $\mathrm{B}^+$ tree, the requirement of at least half-full $(50 \%)$ node occupancy is relaxed for which one of the following cases?