Database Management System

Question 1
Consider the relational database with the following four schemas and their respective instances.


The number of rows returned by the above SQL query is ___
A
1
B
2
C
4
D
8
GATE CSE 2022      SQL
Question 2
Let R_i(z) and W_i(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_4(x)R_2(x)R_3(x)R_1(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?
A
T_1\rightarrow T_3\rightarrow T_4\rightarrow T_2
B
T_1\rightarrow T_4\rightarrow T_3\rightarrow T_2
C
T_4\rightarrow T_1\rightarrow T_3\rightarrow T_2
D
T_3\rightarrow T_1\rightarrow T_4\rightarrow T_2
GATE CSE 2022      Transaction
Question 3
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 R is .
A
12
B
8
C
6
D
5
GATE CSE 2022      Relational Schema
Question 4
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 eIds who own all the brands?
MSQ
A
\Pi _{eId}(\Pi _{eId,bId}(Own)/\Pi _{bId}(Brand))
B
\Pi _{eId}(Own)-\Pi _{eId}\left ((\Pi _{eId}(Own) \times \Pi _{bId}(Brand) )-\Pi _{eId,bId}(Own) \right )
C
\Pi _{eId}(\Pi _{eId,bId}(Own)/\Pi _{bId}(Own))
D
\Pi _{eId}\left ((\Pi _{eId}(Own) \times \Pi _{bId}(Own) )/\Pi _{bId}(Brand) \right )
GATE CSE 2022      Relational Algebra
Question 5
In a relational data model, which one of the following statements is TRUE?
A
A relation with only two attributes is always in BCNF.
B
If all attributes of a relation are prime attributes, then the relation is in BCNF.
C
Every relation has at least one non-prime attribute.
D
BCNF decompositions preserve functional dependencies.
GATE CSE 2022      Normal Form
Question 6
Suppose the following functional dependencies hold on a relation U with attributes P, Q, R, S, \text{ and } T:

P \rightarrow QR \\ RS \rightarrow T

Which of the following functional dependencies can be inferred from the above functional dependencies?
[MSQ]
A
PS \rightarrow T
B
R \rightarrow T
C
P \rightarrow R
D
PS \rightarrow Q
GATE CSE 2021 SET-2      Normal Form
Question 7
Let S be the following schedule of operations of three transactions T_1, T_2 \text{ 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 is conflict-serializable.
Q: If T_3 commits before T_1 finishes, then S is recoverable.

Which one of the following choices is correct?
A
Both P and Q are true
B
P is true and Q is false
C
P is false and Q is true
D
Both P and Q are false
GATE CSE 2021 SET-2      Transaction
Question 8
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.

\textsf{emp($\underline{\textsf{empId}}$, name, gender, salary, 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
A
employees in the department
B
employees in the company
C
female employees in the department
D
female employees in the company
GATE CSE 2021 SET-2      SQL
Question 9
A data file consisting of 1,50,000 student-records is stored on a hard disk with block size of 4096 bytes. The data file is sorted on the primary key RollNo. The size of a record pointer for this disk is 7 bytes. Each student-record has a candidate key attribute called ANum of size 12 bytes. Suppose an index file with records consisting of two fields, ANum value and the record pointer the corresponding student record, is built and stored on the same disk. Assume that the records of data file and index file are not split across disk blocks. The number of blocks in the index file is ______
A
235
B
248
C
488
D
698
GATE CSE 2021 SET-2      File System
Question 10
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?
A
Both S1 and S2 are true
B
S1 is true and S2 is false
C
S1 is false and S2 is true
D
Both S1 and S2 are false
GATE CSE 2021 SET-2      Relational Schema


There are 10 questions to complete.

Leave a Comment