List of top Database Management Systems Questions asked in GATE CS

Consider the following relational schema along with all the functional dependencies that hold on them.
R1(A, B, C, D, E): { $ D \rightarrow E $, $ EA \rightarrow B $, $ EB \rightarrow C $ }
R2(A, B, C, D): { $ A \rightarrow D $, $ A \rightarrow B $, $ C \rightarrow A $ }
Which of the following statement(s) is/are TRUE?

The Lucas sequence L_n is defined by the recurrence relation: L_n = L_n−1 + L_n−2, for n ≥ 3, with L₁ = 1 and L₂ = 3. Which one of the options given is TRUE?

Consider a relation $R(A, B, C, D, E)$ with the following three functional dependencies: \[ AB \rightarrow C, BC \rightarrow D, C \rightarrow E \] The number of superkeys in the relation is

Consider the following three relations in a relational database.

Employee($eId$, Name), Brand($bId$, bName), Own($eId$, $bId$)

Which of the following relational algebra expressions return the set of $eId$'s who own all the brands?

In a relational data model, which one of the following statements is TRUE?

Consider the relational database with the following four schemas and their respective instances: \[ \text{Student(sNo, sName, dNo)} \text{ }\text{Dept(dNo, dName)} \\ \text{Course(cNo, cName, dNo)} \text{ } \text{Register(sNo, cNo)} \]

SQL Query:

SELECT * FROM Student AS S WHERE NOT EXIST
 (SELECT cNo FROM Course WHERE dNo = “D01”
	EXCEPT 
 SELECT cNo FROM Register WHERE sNo = S.sNo)

The number of rows returned by the above SQL query is___________.

Let $R(z)$ and $W(z)$ denote read and write operations on a data element $z$ by a transaction $T_i$, respectively. Consider the schedule $S$ with four transactions.
\[ S: R_1(x) R_2(x) R_3(y) W_1(y) W_2(x) W_3(y) R_4(y) \] Which one of the following serial schedules is conflict equivalent to $S$?

Consider the relation $R(P,Q,S,T,X,Y,Z,W)$ with the following functional dependencies:
\[ PQ \rightarrow X; P \rightarrow YX; Q \rightarrow Y; Y \rightarrow ZW \] Consider the decomposition of the relation $R$ into the constituent relations according to the following two decomposition schemes. \[ \begin{aligned} D_1 &: R = [(P,Q,S,T);\; (P,T,X);\; (Q,Y);\; (Y,Z,W)] \\ D_2 &: R = [(P,Q,S);\; (T,X);\; (Q,Y);\; (Y,Z,W)] \end{aligned} \] Which one of the following options is correct?

Let $ r_i(z) $ and $ w_i(z) $ denote read and write operations respectively on a data item $ z $ by a transaction $ T_i $. Consider the following two schedules.
\[ \begin{aligned} S_1 &: r_1(x)\; r_1(y)\; r_2(x)\; r_2(y)\; w_2(y)\; w_1(x) \\ S_2 &: r_1(x)\; r_2(x)\; r_2(y)\; w_2(y)\; r_1(y)\; w_1(x) \end{aligned} \] Which one of the following options is correct?

A relation $ r(A,B) $ has 1200 tuples.
Attribute $ A $ ranges from 6 to 20 and attribute $ B $ ranges from 1 to 20. Assume independent uniform distribution. The estimated number of tuples in $ \sigma_{(A>10)\vee(B=18)}(r) $ is $\underline{\hspace{2cm}}$.

The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key:
\[ empAge(empNo, age) \] Consider the following relational algebra expression:
\[ \Pi_{empNo}\big( empAge \;\Join_{age > age_1}\; \rho_{empNo_1,\,age_1}(empAge) \big) \] What does the above expression generate?

Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery. Which of the following statements is/are correct?

The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.
\[ \text{emp}(\text{empId},\ \text{name},\ \text{gender},\ \text{salary},\ \text{deptId}) \]

Consider the following SQL query:

select deptId, count(*) 
from emp 
where gender = "female" and salary > (select avg(salary) from emp) 
group by deptId;

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

Suppose the following functional dependencies hold on a relation $U$ with attributes $P, Q, R, S,$ and $T$:
\[ P \rightarrow QR, RS \rightarrow T \] Which of the following functional dependencies can be inferred from the above functional dependencies?

Let $S$ be the following schedule of operations of three transactions $T_1, T_2$ and $T_3$ in a relational database system:
\[ R_2(Y),\; R_1(X),\; R_3(Z),\; R_1(Y),\; W_1(X),\; R_2(Z),\; W_2(Y),\; R_3(X),\; W_3(Z) \] Consider the statements $P$ and $Q$ below:
\[ P:\ S \text{ is conflict-serializable.} \] \[ Q:\ \text{If } T_3 \text{ commits before } T_1 \text{ finishes, then } S \text{ is recoverable.} \] Which one of the following choices is correct?

A data file with 1,50,000 student records is stored on disk with block size 4096 bytes.
Each index record consists of an ANum field of size 12 bytes and a record pointer of size 7 bytes. The number of blocks in the index file is $\underline{\hspace{2cm}}$.

Consider the following statements S1 and S2 about the relational data model:
S1: A relation scheme can have at most one foreign key.
S2: A foreign key in a relation scheme $R$ cannot be used to refer to tuples of $R$.
Which one of the following choices is correct?