(Solved) : Paper Review Drop Table Paperreview Cascade Constraint Drop Table Paperauthor Cascade Cons Q30557621 . . .
— 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’,’golden@umbc.com’,’UMBC’);
insert into reviewer values(2,’Ann Stonebraker’,’ann@umd.edu’,’UMD’);
insert into reviewer values(3,’Karen Smith’,’karen@umb.com’,’UMB’);
insert into reviewer values(4,’Richard Wallas’,’richard@umbc.edu’,’UMBC’);
insert into reviewer values(5,’Ravi Krishnan’,’ravi@jhu.edu’,’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’,’smith@umbc.edu’,’UMBC’);
insert into author values(2,’Nancy Chang’,’nanch@umd.edu’,’UMD’);
insert into author values(3,’Carrol Steinberg’,’carrol@umb.edu’,’UMB’);
insert into author values(4,’Daniel Kerry’,’daniel@jhu.edu’,’JHU’);
create table paper(
pid int,— paper id
ptitle varchar(200),— title of paper
corr_aid int, — id of corresponding author, only one perpaper
sub_date date, — date of submission
primary key(pid),
foreign key(corr_aid) references author);
insert into paper values(1,’A novel intrusion detection methodusing deep learning’, 1,date ‘2018-3-1’);
insert into paper values(2,’A comparison study of differentmachine learning methods’, 2,date ‘2018-3-2’);
insert into paper values(3,’The benefits of exercises todementia 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 inpaper_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 reviewerassigned, 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 greatpaper’,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 havequestion 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 toprint 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 reviewerdoes not have the same affiliation as any of the authors of thepaper. For example, for paper ID 1, there are two authors AdamSmith and Nancy Chang, their affiliations are UMBC and UMD. So anyreviewer affiliated with those two schools cannot serve asreviewer.
Please also handle the case the input paper ID is invalid.
If there is no eligible reviewer, print out a message there isno eligible reviewer.
Expert Answer
Answer to Paper Review Drop Table Paperreview Cascade Constraint Drop Table Paperauthor Cascade Cons Q30557621 . . .
OR