Question

--- Paper Review

drop table paper_review cascade constraint;

drop table paper_author cascade constraint;

drop table paper cascade constraints;

drop table author cascade constraints;

drop table reviewer cascade constraints;

create table reviewer (

rid int, --- reviewer id

rname varchar(50), --- reviewer name

remail varchar(50),-- reviewer email

raffiliation varchar(50),-- reviewer affiliation

primary key (rid)

);

insert into reviewer values(1,'Alex Golden', '[email protected]','UMBC');

insert into reviewer values(2,'Ann Stonebraker', '[email protected]','UMD');

insert into reviewer values(3,'Karen Smith', '[email protected]','UMB');

insert into reviewer values(4,'Richard Wallas', '[email protected]','UMBC');

insert into reviewer values(5,'Ravi Krishnan', '[email protected]','JHU');

create table author

(aid int, -- author id

aname varchar(50), -- author name

aemail varchar(50), -- author email

aaffiliation varchar(50),-- author affiliation

primary key(aid));

insert into author values(1,'Adam Smith', '[email protected]','UMBC');

insert into author values(2,'Nancy Chang', '[email protected]','UMD');

insert into author values(3,'Carrol Steinberg', '[email protected]','UMB');

insert into author values(4,'Daniel Kerry', '[email protected]','JHU');

create table paper(

pid int,--- paper id

ptitle varchar(200),--- title of paper

corr_aid int, --- id of corresponding author, only one per paper

sub_date date, --- date of submission

primary key(pid),

foreign key(corr_aid) references author);

insert into paper values(1,'A novel intrusion detection method using deep learning', 1,date '2018-3-1');

insert into paper values(2,'A comparison study of different machine learning methods', 2,date '2018-3-2');

insert into paper values(3,'The benefits of exercises to dementia patients', 3,date '2018-4-3');

insert into paper values(4,'This paper has everyone has author', 3,date '2018-4-3');

create table paper_author

(

pid int,--- paper id

aid int,--- author id, corresponding author will also appear in paper_author table

primary key(pid, aid),

foreign key(pid) references paper,

foreign key(aid) references author

);

insert into paper_author values(1,1);

insert into paper_author values(1,2);

insert into paper_author values(2,2);

insert into paper_author values(2,1);

insert into paper_author values(3,3);

insert into paper_author values(3,4);

insert into paper_author values(4,1);

insert into paper_author values(4,2);

insert into paper_author values(4,3);

insert into paper_author values(4,4);

create table paper_review

(

pid int,-- paper id

rid int,--- reviewer id, each paper has multile reviewer assigned, same reviewer

-- can review multiple papers

content varchar(1000), --- content of review

rscore int, --- review score, 1-5

rdate date, --- review date

primary key(pid, rid),

foreign key(pid) references paper,

foreign key(rid) references reviewer

);

insert into paper_review values(1,1,'This is a great paper',5,date '2018-4-1');

insert into paper_review values(1,2,'Execclent paper',4,date '2018-3-28');

insert into paper_review values(2,3,'Nice paper',4,date '2018-4-1');

insert into paper_review values(2,1,'Good paper but I have question regarding figure 2',3,date '2018-4-2');

insert into paper_review values(3,3,'Interesting results',4,date '2018-5-2');

insert into paper_review values(3,4,'Timely paper',4,date '2018-5-3');

commit;

Problem: Please write a PL/SQL procedure to print out names of reviewers who are eligible to review a paper. Please pay attention to the following:

The input is the id of the paper.

A reviewer will be eligible to review a paper if the reviewer does not have the same affiliation as any of the authors of the paper. For example, for paper ID 1, there are two authors Adam Smith and Nancy Chang, their affiliations are UMBC and UMD. So any reviewer affiliated with those two schools cannot serve as reviewer.

Please also handle the case the input paper ID is invalid.

If there is no eligible reviewer, print out a message there is no eligible reviewer.

Answer 1

create or replace

PROCEDURE ReviewCheckName (id IN paper.pid%type)

AS

Reviewer_name reviwer.rname%type;

CURSOR c1 IS

SELECT r.rname INTO Reviewer_name FROM reviwer r INNER JOIN paper_review pr ON r.rid=pr.rid

WHERE r.raffiliation NOT IN (SELECT a.aaffiliation FROM author a INNER JOIN paper p ON a.aid=p.corr_aid WHERE p.pid=id and a.aaffiliation in ('UMBC','UMD'));

BEGIN

open c1;   

LOOP

IF c1%NOTFOUND

THEN

IF c1%ROWCOUNT=0

THEN

DBMS_OUTPUT.PUT_LINE('NO records found,input paper ID is invalid');

END IF;

EXIT;

ELSE

FETCH c1 INTO Reviewer_name;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(Reviewer_name);

END IF;

end loop;

close c1;

end;