Register

GO Classes DPP | GATE CS, DA | DBMS | Join

• edited by
90 views
2 2 votes

Consider the relation:

$\mathrm{Marks(studentID,~courseID,~courseType,~score)}$

Let, $\mathrm{M_1=\rho_{M_1}(Marks)}$ and $\mathrm{M_2=\rho_{M_2}(Marks)}$.

Which expression returns the student IDs of students who have received marks in at least two distinct CPSC courses?

  1. $\pi_{\mathrm{M_1.studentID}}($ $\mathrm{M_1}\bowtie_{\mathrm{M_1.studentID=M_2.studentID}\ \land\ \mathrm{M_1.courseID\neq M_2.courseID}\ \land\ \mathrm{M_1.courseType='CPSC'}\ \land\ \mathrm{M_2.courseType='CPSC'}}\mathrm{M_2}$ $)$
     
  2. $\pi_{\mathrm{M_1.studentID}}($ $\mathrm{M_1}\bowtie_{\mathrm{M_1.studentID=M_2.studentID}\ \land\ \mathrm{M_1.courseID=M_2.courseID}}\mathrm{M_2}$ $)$
     
  3. $\pi_{\mathrm{studentID}}(\sigma_{\mathrm{courseType='CPSC'}}(\mathrm{Marks}))$
     
  4. $\pi_{\mathrm{M_1.studentID}}($ $\mathrm{M_1}\bowtie_{\mathrm{M_1.courseID\neq M_2.courseID}}\mathrm{M_2}$ $)$

1 Answer

1 1 vote

The phrase at least two different courses means that we need to inspect two tuples belonging to the same student.

Therefore, two renamed copies of $\mathrm{Marks}$ are required.

First, they must refer to the same student:

$\mathrm{M_1.studentID=M_2.studentID}$.

Both tuples must correspond to $\mathrm{CPSC}$ courses:

$\mathrm{M_1.courseType}=\mathrm{'CPSC'}$ and $\mathrm{M_2.courseType}=\mathrm{'CPSC'}$.

Finally, they must represent different courses:

$\mathrm{M_1.courseID\neq M_2.courseID}$.

Thus A expresses exactly the required condition.

In B, requiring the same $\mathrm{courseID}$ allows a tuple to match itself, which does not prove that the student took two distinct courses.

C only proves that a student took at least one $\mathrm{CPSC}$ course.

D finds different courses but does not require them to belong to the same student.

Therefore, the correct answer is A.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · Operating system and...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

1 1 vote
1 1 answer
118
118 views
GO Classes asked Sep 22
118 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relations:$\mathrm{Authors(au\_id,au\_lname,au\_fname,phone,address,city,state,zip)}$$\mathrm{TitleAuthors(au\_id,title\_id,au\_ord,royaltyshare)}$$\mathrm{T...
1 1 vote
1 1 answer
78
78 views
GO Classes asked Sep 22
78 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relations:$\mathrm{Locations(locationid,name,state,altitude)}$ and $\mathrm{FallColors(week,year,locationid,color,peakpercent)}$.We want locations in New Y...
2 2 votes
1 1 answer
78
78 views
GO Classes asked Sep 22
78 views
GO Classes DPP | GATE CS, DA | DBMS | Join
Consider the relations:$\mathrm{Posts(pid,folder,summary)}$ and $\mathrm{Postings(post,position,user,ptext)}$.Let $\mathrm{R_1}$ and $\mathrm{R_2}$ be two renamed copies ...
1 1 vote
1 1 answer
126
126 views
GO Classes asked Sep 22
126 views
GO Classes DPP | GATE CS, DA | DBMS | Relational Algebra
Assume the expressions below are schema-valid and relations use set semantics.Which of the following are always true?$(\mathrm{R}\bowtie\mathrm{S})\bowtie\mathrm{T}=(\mat...