Consider a database that has the relation schema CR(StudentName, CourseName). — GATE CS 2017 Set 1 Q46

Consider a database that has the relation schema CR(StudentName, CourseName). An instance of the schema CR is as given below.
$\begin{array}{|c|c|} \hline \textbf{StudentName} & \textbf{CourseName} \\ \hline \text{SA} & \text{CA} \\ \hline \text{SA} & \text{CB}\\ \hline \text{SA} & \text{CC} \\ \hline \text{SB} & \text{CB} \\ \hline \text{SB}& \text{CC} \\ \hline \text{SC} & \text{CA}\\ \hline \text{SC}&\text{CB} \\ \hline \text{SC} & \text{CC} \\ \hline \text{SD} & \text{CA} \\ \hline \text{SD} & \text{CB}\\ \hline \text{SD} & \text{CC} \\ \hline \text{SD} & \text{CD} \\ \hline \text{SE}& \text{CD} \\ \hline \text{SE} & \text{CA}\\ \hline \text{SE}&\text{CB} \\ \hline \text{SF}& \text{CA} \\ \hline \text{SF} & \text{CB }\\ \hline\text{SF} & \text{CC} \\ \hline \end{array}$
The following query is made on the database.

$T1 \leftarrow \pi _{CourseName}\left ( \sigma _{StudentName=SA}\left ( CR \right ) \right )$
$T2 \leftarrow CR\div T1$

The number of rows in $T2$ is ______________ .

View worked solution

Answer: 4

To find the number of rows in $T2$ , let us evaluate the relational algebra operations step-by-step.

Step 1: Evaluate $T1$

The query for $T1$ is:

T1 \leftarrow \pi_{CourseName}\left( \sigma_{StudentName = \text{SA}}(CR) \right)

This selects all courses taken by the student SA. Looking at the relation $CR$ , the courses associated with SA are:

Thus, $T1 = \{\text{CA}, \text{CB}, \text{CC}\}$ .

Step 2: Evaluate $T2$

The query for $T2$ is:

T2 \leftarrow CR \div T1

The division operator ( $CR \div T1$ ) finds all StudentName values in $CR$ that are associated with every CourseName present in $T1$ . In other words, we need to find students who have registered for at least all three courses: CA, CB, and CC.

Let's list the set of courses taken by each student in $CR$ :

SA: $\{\text{CA}, \text{CB}, \text{CC}\}$ — Contains $T1$
SB: $\{\text{CB}, \text{CC}\}$ — Does not contain $T1$
SC: $\{\text{CA}, \text{CB}, \text{CC}\}$ — Contains $T1$
SD: $\{\text{CA}, \text{CB}, \text{CC}, \text{CD}\}$ — Contains $T1$
SE: $\{\text{CA}, \text{CB}, \text{CD}\}$ — Does not contain $T1$
SF: $\{\text{CA}, \text{CB}, \text{CC}\}$ — Contains $T1$

The students who satisfy the condition are SA, SC, SD, and SF.

Therefore, the relation $T2$ is:

\begin{array}{|c|} \hline \textbf{StudentName} \\ \hline \text{SA} \\ \hline \text{SC} \\ \hline \text{SD} \\ \hline \text{SF} \\ \hline \end{array}

The number of rows in $T2$ is 4.

Free practice · Your answers on this page are not saved.

GATE CS 2017 Set 1 Q46 — Relational Model: Relational Algebra

Step 1: Evaluate T1T1T1

Step 2: Evaluate T2T2T2

Related questions

Step 1: Evaluate $T1$

Step 2: Evaluate $T2$