You will be using the following tables about a paper review database in the exam

Question

You will be using the following tables about a paper review database in the exam.

Please read the comments that explain meaning of columns.

--- a paper review system

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','Columbia');

insert into reviewer values(2,'Ann Stonebraker', 'ann@umd.edu','Stanford');

insert into reviewer values(3,'Karen Smith', 'karen@umb.com','Harvard');

insert into reviewer values(4,'Richard Wallas', 'richard@umbc.edu','UMBC');

create table author

(aid int, -- author id

aname varchar(50), -- author name

aemail varchar(50), -- author email

aaffilication 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 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');

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);

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;

Task 6: Return names of reviewers who have reviewed at least 2 papers.

Task 7: update the review score by Karen Smith (the name of the reviewer) on paper #3 (3 is paper id) to 5.

Task 8: return names of co-authors for the papers with Adam Smith as corresponding author

Explanation / Answer

Task 6:

Fire below query, it is the solution of task 6

select rname from paper_review join reviewer on reviewer.rid=paper_review.rid group by paper_review.rid having count(*) > 1;

Task 7:

Fire below query, it is the solution of task 7

update paper_review join reviewer on paper_review.rid=reviewer.rid set rscore=5 where rname="Karen Smith" and pid="3";

Task 8:

Fire below query, it is the solution of task 8

select aname from author JOIN paper_author on author.aid=paper_author.aid JOIN paper on paper_author.pid=paper.pid and paper_author.aid=paper.corr_aid WHERE author.aid=(select aid from paper_author where pid=(select pid from paper where paper.corr_aid=1) and aid<>1);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.