Consider therelations Paper (paper_id, paper domain, paper authorld, citation_nu
ID: 3733929 • Letter: C
Question
Consider therelations Paper (paper_id, paper domain, paper authorld, citation_number) Author (author_id, author_name, author_city) located at Pittsburgh and NYC correspondingly. (a)[10pt] Specify two semi-join strategies to execute the following query depending on the order of relations in the semi-joinoperation (theresult can be generated either in NYC or in Pittsburgh): select from Paper, Author where Paper.paper_authorld-Author. author_idand Paper.citation_number> 100 (b)[15pt] Illustrate the benefit and disadvantages from using each of the semi-join strategies considered in the previous problem with actual Poper and Author tuples. Explain how that benefit depends on actual values of the Paper and Author tuples. Provide no more than two sentences of explanation per strategy.Explanation / Answer
a.1)--------------------
select*
from Paper
where exist
(select*
from Paper, Author
where Paper.paper_authorid = Author.author_id and paper.citation_number> 100)
a.2)------------
select*
from Paper
where paper.citation_number> 100 and
Paper.paper_authorid
in (select*
from Author
where Paper.paper_authorid = Author.author_id )
b)-----------
Both strategies gave benefit for distributed database system and also reducing the cost of communication.
semijoin between Paper and Author table return row from the Paper table where one or more match are found in the Author table.Semijoin also remove duplicates in the final table.
the given query return all tuple in the both the table ie Paper table can have two or more paper of same author so it print every time all detail of Author but semijoin return only Author_id associated with the paper.
a.1 semijoin compare id's and citation_number at same time
but a.2 semijoin first return the citation no.>100 and result compared with id's comprision