GATE CSE 2017 | Set 1 | Question: 46

Question

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

Comments

How many Students have enrolled in all the same courses that Student “SA” has enrolled in :

SA enrolled in  CA, CB, CC courses → $T1 = \{ CA, CB, CC \}$

from Course table we apply division operator since we want “all” students who enrolled in $T1$  $ = \frac{CR}{T1}$  

SA → {CA , CB, CC}  $\supseteq T1$              → (1)

SB → {CB, CC} $\nsupseteq T1$

SC → {CA , CB, CC}  $\supseteq T1$              → (2)

SD → {CA , CB, CC, CD} $\supseteq T1$        → (3)

SE → {CA , CB, CD} $\nsupseteq T1$

SF → {CA , CB, CC} $\supseteq T1$                → (4)

So 4 Out of 6 students are enrolled in ALL courses enrolled by Student SA

---

T1 contains  CA , CB , CC
T2 will contain those tuples which have completely T1 (means CA, CB, CC should be present for that student name then only it would be considered)

Answer 1

ANS) 4

T1 WILL GIVE :- $\begin{array}{|c|c|c|} \hline \text {1. CA} \\\hline  \text {2. CB} \\\hline  \text {3. CC} \\\hline \end{array}$

T2 = CR $\div$  T1 $=$ All the tuples in CR which are matched with every tuple in T1 : $\begin{array}{|c|c|c|} \hline \text {1. SA} \\\hline  \text {2. SC} \\\hline  \text {3. SD} \\\hline \text{4. SF} \\\hline \end{array}$

//SB IS NOT MATCHED WITH CA, SE IS NOT MATCHED WITH CC 

Comments

One row of SD is also not matched. What about that?

---

The values taken in T2 will be the tuples in CR which matches with every tuple in T1

SA matches with 

SA	CA
SA	CB
SA	CC

SB matches with only CB CC but not CA so it has not been taken

SB	CB
SB	CC

Similarly, SC matches with all the tuples in T1 CA, CB and CC so it has been taken 

SD matches with all the tuples in T1 CA,CB and CC so it has been taken. Since, CD is not in table T1 so it doesn't affect.

SE doesn't match with every tuple in T1, it matches only with CA, CB(in T1) and CD but CD is not present in T1.

SF matches with all the tuples present in T1 so it has also been taken.

 

Answer 2

plz make me correct if i m wrong.....

Answer 3

Be executing query on T1 → CA,CB,CC

T2 <--CR/T1  ,So ans will be those Student name having T1(i.e CA,CB,CC(all 3)) ,So studend name -->SA,SC ,SD,SF  contains T1 &lsquo;s 3 course name ,so answer will be 4 .

Answer 4

Be executing query on T1 we get -->CA,CB,CC

T2 = CR/T1  ,So ans will be those Student name having T1( i.e CA,CB,CC(all 3)) ,So student name -->SA,SC ,SD,SF  contains T1&rsquo;s  all 3 course name ,so answer will be 4 .

Answer 5

T2 states to mention the tuples who have all the courses Ca, Cb, Cc. Hence 4 tuples.