GATE CS Databases questions

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?

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. $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)$ Which one of the following options is correct?

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. …

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 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 $\textrm{RollNo}$. The size of a record pointer for this disk is $7$ bytes. Each student-record has a candidate key attribute called $\textrm{ANum}$ of size $12$ bytes. Suppose an index file …

The relation scheme given below is used to store information about the employees of a company, where $\textsf{empId}$ is the key and $\textsf{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 …

​​​​​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$ is conflict-serializable. $Q$: If $T_3$ commits before $T_1$ finishes, then $S$ …

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?

Consider a relational database containing the following schemas. …

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?

Consider a relational table $R$ that is in $3NF$, but not in BCNF. Which one of the following statements is TRUE?

Consider a database implemented using $B^+$ tree for file indexing and installed on a disk drive with block size of $\text{4 KB}$. The size of search key is $\text{12 bytes}$ and the size of tree/disk pointer is $\text{8 bytes}$. Assume that the database has one million records. Also assume that no node of the $B^+$ tree and no records are present …

Consider the following two statements about database transaction schedules: Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable. Timestamp-ordering concurrency control protocol with Thomas’ Write Rule can generate view serializable schedules that are not conflict serializable Which of the above statements …

Which one of the following statements is NOT correct about the $B^+$ tree data structure used for creating an index of a relational database table?

Let the set of functional dependencies $F=\{QR \rightarrow S, \: R \rightarrow P, \: S \rightarrow Q \}$ hold on a relation schema $X=(PQRS)$. $X$ is not in BCNF. Suppose $X$ is decomposed into two schemas $Y$ and $Z$, where $Y=(PR)$ and $Z=(QRS)$. Consider the two statements given below. Both $Y$ and $Z$ are in BCNF Decomposition of …

A relational database contains two tables Student and Performance as shown below: …

Consider the following relations $P(X,Y,Z), Q(X,Y,T)$ and $R(Y,V)$. …

Consider the following four relational schemas. For each schema , all non-trivial functional dependencies are listed, The bolded attributes are the respective primary keys. $\text{Schema I:}$ Registration(rollno, courses) Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for. Non-trivial functional dependency …

Consider the relations $r(A, B)$ and $s(B, C)$, where $s.B$ is a primary key and $r.B$ is a foreign key referencing $s.B$. Consider the query $Q: r \bowtie (\sigma_{B<5} (s))$ Let LOJ denote the natural left outer-join operation. Assume that $r$ and $s$ contain no null values. Which of the following is NOT equivalent to $Q$?

In an Entity-Relationship (ER) model, suppose $R$ is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in $R$ and that the cardinality of E1 is greater than the cardinality of E2. Which one of the following is true about $R$?