Tables Created in Assignment 1

drop table paper_review cascade constraints;

drop table reviewer cascade constraints;

drop table paper_author cascade constraints;

drop table paper cascade constraints;

drop table editor cascade constraints;

drop table author cascade constraints;

create table author

(

aid int, -- author id

aname varchar(50), --- author name

aemail varchar(50), --- author email

affliation varchar(50),

primary key (aid));

insert into author values(1,'Dr. Chen','chen@umbc.edu','UMBC');

insert into author

values(2,'Susan','susan@umbc.edu','UMBC');

insert into author

values(3,'Steve','steve@umb.edu','UMB');

insert into author

values(4,'Carole','carole@umb.edu','UMB');

create table Editor

(

eid int, --- editor ID

ename varchar(50), --- editor name

eemail varchar(50), --- email

primary key (eid));

insert into editor

values(1,'Claire','claire g mail.com');

insert into editor

values(2,'David','david g mail.com');

create table paper(

pid int, -- paper id

title varchar(50),

eid int, -- assigned editor id

submit_date date, --- date of submission

status int, -- status: 1 accept, 2 under review, 3 revision, 4 reject

primary key(pid),

foreign key(eid) references editor);

--- accept

insert into paper values(1,'Comparing big data systems',2,date '2020-1-10',3);

-- under review

insert into paper values(2,'A novel approach of mining EHR data',2,date '2020-2-10',2);

--- revision,

insert into paper

values(3, 'A new SQL Benchmark', 1, date '2020-1-1',1);

create table paper_author (

pid int, -- paper id

aid, -- author id

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

insert into paper_author values(2,4);

insert into paper_author values(3,1);

insert into paper_author values(3,2);

create table Reviewer (

rid int, --- reviewer id

rname varchar(50), --- reviewer name

remail varchar(50), --- reviewer email

raffiliation varchar(50),

primary key(rid));

insert into reviewer values(1,

'Ellen','ellen g mail.com','Johns Hopkins');

insert into reviewer values(2,

'Cathy','cathy g mail.com','Johns Hopkins');

insert into reviewer values(3,

'Grace','grace g mail.com','Stanford');

insert into reviewer values(4,

'Eric','eric g mail.com','Stanford');

insert into reviewer values(5,

'Ethan','ethan@umbc.edu','UMBC');

create table paper_review (

prid int, -- paper review id

pid int, -- paper id

rid int, -- reviwer id

due_date date, -- review due date

receive_date date, -- review received date

round int, -- round of review, 1 means first round, 2 means second round, most paper will go through 2 rounds of review

decision int, -- 1 accept, 2 minor revision, 3 major revision, 4 reject

rcomment varchar(200), -- comments of review

primary key(prid),

foreign key(pid) references paper,

foreign key(rid) references reviewer);

-- paper 1, first round,

-- 2 major, 1 minor

insert into paper_review

values(1, 1, 1, date '2020-2-10',date '2020-2-9',

1,3,'Good paper but needs to improve writing');

insert into paper_review

values(2, 1, 3, date '2020-2-10',date '2020-2-10',

1,2,'Good paper fix a few typo');

insert into paper_review

values(3, 1, 4, date '2020-2-10',date '2020-2-12',

1,3,'Please add more experiments');

--- paper 2 is under review and no review provided.

insert into paper_review

values(4, 2, 2, date '2020-3-10',null,

1,null,null);

insert into paper_review

values(5, 2, 1, date '2020-3-10',null,

1,null,null);

insert into paper_review

values(6, 2, 3, date '2020-3-10',null,

1,null,null);

-- paper 3 has two rounds of reviews, r1: 1 major, 1 minor

-- r2: 2 accept

insert into paper_review

values(7, 3, 4, date '2020-2-1',date '2020-1-15',

1,2,'good paper, fix a few typo');

insert into paper_review

values(8, 3, 2, date '2020-2-1',date '2020-1-11',

1,3,'good paper, but please add more related work');

insert into paper_review

values(9, 3, 4, date '2020-3-1',date '2020-2-15',

2,1,'all comments addressed');

insert into paper_review

values(10, 3, 2, date '2020-3-1',date '2020-2-18',

2,1,'all comments addressed');

____________________________________________________________________

Suppose you have created tables in assignment 1. Please write an anonymous PL/SQL program for each the following problems. You can use the same code in assignment 2 to create the tables. Please include both your code and screenshots.

Problem 1: [30 points] Please write an anonymous PL/SQL program to print out the first 10 numbers of the following sequence F(n).

F(0) = 2, F(1)=5. F(n)=5*F(n-1)-4*F(n-2).

E.g., F(3) = 5*F(2) - 4 F(1) = 5*5-4*2=17, F(4)=5*F(3)-4*F(2)=5*17-4*5=65,

Your program will print out F(0),F(1),..., F(9).

Hint: use three variables, the first storing F(n), the second storing F(n-1), and the third storing F(n-2). Think of how to compute the first variable from the other two and how to update the other two variables in each iteration.

Problem 2: Write anonymous PL/SQL code to print out number of reviewers who have NOT submitted round 1 review for the paper titled 'A novel approach of mining EHR data'.

Hint: a review is not submitted if the receive_date column in paper_review table is null.

[30 points]

Problem 3: [40 points] Write an anonymous PL/SQL program to print out review decision and comments for round 1 review of paper titled 'Comparing big data systems'. In the same program, please also print out an automatic suggestion for this paper.

The suggestion should be 'reject' if at least two reviewers' decisions are

reject. The suggestion should be 'accept' if all reviewers' decisions are accept. All other cases the suggestion is 'to be decided by editor'.

Hint: you can use an explicit cursor to print out the review decision and comments, and a few implicit cursors to compute number of round 1 review for that paper, number of accept and number of reject decisions, and use if then else to come up with suggestion.