Register

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

124 views
1 1 vote

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{Titles(title\_id,title,type,pub\_id,price,\ldots)}$

$\mathrm{Publishers(pub\_id,pub\_name,address,city,state)}$

Which expression correctly finds the first names of authors who have a book published by a publisher located in Boston?

  1. $\pi_{\mathrm{au\_fname}}\big( \pi_{\mathrm{au\_id,au\_fname}}(\mathrm{Authors}) \;\bowtie\; \mathrm{TitleAuthors} \;\bowtie\; \mathrm{Titles} \;\bowtie\; \sigma_{\mathrm{city='Boston'}}(\mathrm{Publishers}) \big)$
     
  2. $\pi_{\mathrm{au\_fname}}\big( \mathrm{Authors} \;\bowtie\; \mathrm{TitleAuthors} \;\bowtie\; \mathrm{Titles} \;\bowtie\; \sigma_{\mathrm{city='Boston'}}(\mathrm{Publishers}) \big)$
     
  3. $\pi_{\mathrm{au\_fname}}\big( \mathrm{Authors} \;\bowtie\; \sigma_{\mathrm{city='Boston'}}(\mathrm{Publishers}) \big)$
     
  4. $\pi_{\mathrm{au\_fname}}\big( \sigma_{\mathrm{city='Boston'}}(\mathrm{Authors}) \big)$

1 Answer

1 1 vote

Natural join automatically equates every same-named attribute appearing in its two inputs.

The intended relationship is:

$\mathrm{Authors.au\_id=TitleAuthors.au\_id}$

then

$\mathrm{TitleAuthors.title\_id=Titles.title\_id}$

then

$\mathrm{Titles.pub\_id=Publishers.pub\_id}$.

However, the full $\mathrm{Authors}$ relation also contains

$\mathrm{address}$, $\mathrm{city}$, and $\mathrm{state}$.

$\mathrm{Publishers}$ contains these same attribute names.

If we carry all of these attributes through to the natural join with $\mathrm{Publishers}$, natural join may additionally require:

$\mathrm{Authors.address=Publishers.address}$

$\mathrm{Authors.city=Publishers.city}$

$\mathrm{Authors.state=Publishers.state}$.

Those conditions are not part of the query.

The fix is to project $\mathrm{Authors}$ down to only the attributes needed later:

$\pi_{\mathrm{au\_id,au\_fname}}(\mathrm{Authors})$.

Then the natural-join chain connects only through the intended identifiers.

Therefore, A is correct.

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
85
85 views
GO Classes asked Sep 22
85 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
85
85 views
GO Classes asked Sep 22
85 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
97
97 views
GO Classes asked Sep 22
97 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
143
143 views
GO Classes asked Sep 22
143 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...