GATE Overflow Test Series | Databases | Test 1 | Question: 13

Question

Consider the following relation instance.
$$\textbf{Student}$$$$\begin{array}{|c|c|c|c|c|}
\hline 
\textbf{Roll}& \textbf{FirstName} & \textbf{LastName} & \textbf{Weight}\\
\hline 
101 & \text{Rahul} & \text{Soni} & 40 \\
\hline 
102 & \text{Anam} & \text{Patel} & 55 \\
\hline 
103 & \text{Donald} & \text{Trump} & 75 \\
\hline 
104& \text{Anuj} & \text{Sahu} & 45 \\
\hline 
105 & \text{Kim-jong} & \text{Un} & 105 \\
\hline 
106 & \text{Alex} & \text{Carry} & 50 \\
\hline 
\end{array}$$
$$\textbf{Enrol}$$$$\begin{array}{|c|c|c|c|c|}
\hline
\textbf{Roll} & \textbf{CourseId} \\
\hline 
101& 1000 \\
\hline 
101 & 1001 \\
\hline 
101 & 1006 \\
\hline 
102 & 1000 \\
\hline 
103 & 1006 \\
\hline 
103 & 1001 \\
\hline
\end{array}$$

Which of the following queries will correctly retrieve the course IDs of all the courses taken by students weighing more than $45$ units?
(Mark all the appropriate options)

• A. $\{t\cdot \text{CourseId} \mid \text{Enrol}(t)\wedge \exists s(\text{Student}(s) \wedge s.Roll=t.Roll \wedge s.weight > 45)\}$
• B. $\Pi_{\text{CourseId}}(\sigma_{weight>45} (\text{Student} \bowtie_{Student.Roll=Enroll.Roll} Enroll))$
• C. $\Pi_{\text{CourseId}}(\sigma_{weight>45 \wedge Student.Roll=Enroll.Roll } (\text{Student} \times Enroll))$
• D. Select Enrol.CourseId from Enrol,Student where Student.weight > 45

Comments

i think option D also give Duplicate ID.

---

@theredeepakb

I guess we have to follow the given instance rather than taking query in general. If condition is applied on given instance,then duplicate will not come.

Otherwise distinct would be required in SQL query.

Only doing join is fine in place of cross product here as written by @gatecse Sir below.

Please correct me If i am wrong.

 

Answer 1

Options A, B and C are correct. Option D is not correct because it is doing a cross product and not a join on the Roll attribute. It can be corrected as
Select Enrol.CourseId from Enrol,Student where Student.weight $> 45$ and Enrol.Roll$=$Student.Roll
(OR)
Select Enrol.CourseId from Enrol JOIN Student where Student.weight $> 45.$