Question 1 |

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?

P \rightarrow QR \\ RS \rightarrow T

Which of the following functional dependencies can be inferred from the above functional dependencies?

**[MSQ]**PS \rightarrow T | |

R \rightarrow T | |

P \rightarrow R | |

PS \rightarrow Q |

Question 1 Explanation:

Question 2 |

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?

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?

Both P and Q are true | |

P is true and Q is false | |

P is false and Q is true | |

Both P and Q are false |

Question 2 Explanation:

Question 3 |

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:

\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 ofemployees in the department | |

employees in the company | |

female employees in the department | |

female employees in the company |

Question 3 Explanation:

Question 4 |

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 ______

235 | |

248 | |

488 | |

698 |

Question 4 Explanation:

Question 5 |

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?

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?

Both S1 and S2 are true | |

S1 is true and S2 is false | |

S1 is false and S2 is true | |

Both S1 and S2 are false |

Question 5 Explanation:

Question 6 |

Consider the relation R(P,Q,S,T,X,Y,Z,W) with the following functional dependencies.

PQ\rightarrow X;\quad P\rightarrow YX;\quad Q\rightarrow Y; \quad Y\rightarrow ZW

Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.

D1:\quad R=[(P,QS,T);\;(P,T,X);\;(Q,Y);\;(Y,Z,W)]

D2:\quad R=[(P,Q,S);\;(T,X);\;(Q,Y);\;(Y,Z,W)]

Which one of the following options is correct?

PQ\rightarrow X;\quad P\rightarrow YX;\quad Q\rightarrow Y; \quad Y\rightarrow ZW

Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.

D1:\quad R=[(P,QS,T);\;(P,T,X);\;(Q,Y);\;(Y,Z,W)]

D2:\quad R=[(P,Q,S);\;(T,X);\;(Q,Y);\;(Y,Z,W)]

Which one of the following options is correct?

D1 is a lossless decomposition, but D2 is a lossy decomposition. | |

D1 is a lossy decomposition, but D2 is a lossless decomposition. | |

Both D1 and D2 are lossless decompositions. | |

Both D1 and D2 are lossy decompositions. |

Question 6 Explanation:

Question 7 |

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.

S1: r_1(x)r_1(y)r_2(x)r_2(y)w_2(y)w_1(x)

S2: r_1(x)r_2(x)r_2(y)w_2(y)r_1(y)w_1(x)

Which one of the following options is correct?

S1: r_1(x)r_1(y)r_2(x)r_2(y)w_2(y)w_1(x)

S2: r_1(x)r_2(x)r_2(y)w_2(y)r_1(y)w_1(x)

Which one of the following options is correct?

S1 is conflict serializable, and S2 is not conflict serializable | |

S1 is not conflict serializable, and S2 is conflict serializable | |

Both S1 and S2 are conflict serializable | |

Niether S1 nor S2 is conflict serializable |

Question 7 Explanation:

Question 8 |

The following relation records the age of 500 employees of a company, where empNo ( indicating the employee number) is the key:

empAge(\underline{empNo},age)

Consider the following relational algebra expression:

\Pi_{empNo}(empAge \Join_{(age > age1)} \rho_{empNo1,age1}(empAge))

What does the above expression generate?

empAge(\underline{empNo},age)

Consider the following relational algebra expression:

\Pi_{empNo}(empAge \Join_{(age > age1)} \rho_{empNo1,age1}(empAge))

What does the above expression generate?

Employee numbers of only those employees whose age is the maximum | |

Employee numbers of only those employees whose age is more than the age of exactly one other employee | |

Employee numbers of all employees whose age is not the minimum | |

Employee numbers of all employees whose age is the minimum |

Question 8 Explanation:

Question 9 |

A relation r(A,B) in a relational database has 1200 tuples. The attribute A has integer values ranging from 6 to 20, and the attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed.

The estimated number of tuples in the output of \sigma _{(A > 10)\vee(B=18)}(r) is ____________

The estimated number of tuples in the output of \sigma _{(A > 10)\vee(B=18)}(r) is ____________

1200 | |

205 | |

15 | |

820 |

Question 9 Explanation:

Question 10 |

Consider a linear list based directory implementation in a file system. Each directory is a list of nodes, where each node contains the file name along with the file metadata, such as the list of pointers to the data blocks. Consider a given directory foo.

Which of the following operations will necessarily require a full scan of foo for successful completion?

Which of the following operations will necessarily require a full scan of foo for successful completion?

**[MSQ]**Creation of a new file in foo | |

Deletion of an existing file from foo | |

Renaming of an existing file in foo | |

Opening of an existing file in foo |

Question 10 Explanation:

There are 10 questions to complete.