Register

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

90 views
1 1 vote

Consider the relations:

$\mathrm{Locations(locationid,name,state,altitude)}$  and  $\mathrm{FallColors(week,year,locationid,color,peakpercent)}$.

We want locations in New York for which, in week $\mathbf{50}$ of the same year, at least two different colors each reached a peak percentage of at least $60$.

Let, $\mathrm{R_1}=\sigma_{\mathrm{week=50}\ \land\ \mathrm{peakpercent\geq60}}(\mathrm{FallColors})$  and  $\mathrm{R_2}=\pi_{\mathrm{color,locationid,year}}(\mathrm{R_1})$.

Which construction is correct?

  1. Rename
    $\mathrm{R_3(color_1,locationid_1,year_1)=R_2}$
    and compute
    $\mathrm{R_4}=\mathrm{R_2}\bowtie_{\mathrm{color\neq color_1}\ \land\ \mathrm{locationid=locationid_1}\ \land\ \mathrm{year=year_1}}\mathrm{R_3}$,
    then
    $\pi_{\mathrm{locationid,name}}\left(\mathrm{R_4}\bowtie\sigma_{\mathrm{state='NY'}}(\mathrm{Locations})\right)$.
     
  2. $\mathrm{R_4}=\mathrm{R_2}\bowtie_{\mathrm{color\neq color_1}}\mathrm{R_3}$
    with no equality conditions on location or year.
     
  3. $\mathrm{R_4}=\mathrm{R_2}\bowtie_{\mathrm{color=color_1}\ \land\ \mathrm{locationid=locationid_1}\ \land\ \mathrm{year=year_1}}\mathrm{R_3}$.
     
  4. $\pi_{\mathrm{locationid,name}}\left(\sigma_{\mathrm{state='NY'}}(\mathrm{Locations})\bowtie\mathrm{R_2}\right)$.

1 Answer

1 1 vote

The query asks for two different colors associated with the same location and the same year.

Therefore, we must compare two tuples of $\mathrm{R_2}$.

The colors must differ:

$\mathrm{color\neq color_1}$.

But the location must be the same:

$\mathrm{locationid=locationid_1}$.

And the year must be the same:

$\mathrm{year=year_1}$.

Thus, the theta-join condition is

$\mathrm{color\neq color_1}$ $\land\ \mathrm{locationid=locationid_1}$ $\land\ \mathrm{year=year_1}$.

The result is then connected with $\mathrm{Locations}$ so that we can impose

$\mathrm{state}=\mathrm{'NY'}$

and return the location name.

Therefore, A is correct.

B can pair colors from different locations or years.

C requires the colors to be identical.

D proves only that a qualifying color exists. It does not establish the existence of a second distinct color.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...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
131
131 views
GO Classes asked Sep 22
131 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...
2 2 votes
1 1 answer
89
89 views
GO Classes asked Sep 22
89 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 ...
2 2 votes
1 1 answer
103
103 views
GO Classes asked Sep 22
103 views
GO Classes DPP | GATE CS, DA | DBMS | Join
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 ...
1 1 vote
1 1 answer
146
146 views
GO Classes asked Sep 22
146 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...