Assumptions: Each paper has one or more authors, each author can write one or more papers.
Each paper is assigned to an editor. Each editor can oversee multiple papers.
Each paper is assigned to multiple (normally at least two) reviewers. The review process usually has two or more rounds. In each round assigned reviewers will submit a decision (accept, minor revision, major revision, or reject) and comments. Editor will make a decision based on reviews. Normally a paper can only be accepted if all reviewers agree to accept. Otherwise the paper is either rejected (e. g., if majority reviewers recommend to reject), or go for a major or minor revision. After revision, the paper will be sent to the same reviewers for the next round.
The list of tables is:
Author table has the following columns
aid, -- author id
aname, --- author name
aemail, --- author email
affliation, --- author affliation
Editor table has the following columns
eid, --- editor ID
ename, --- editor name
eemail, --- email
paper table has the following columns
pid, -- paper id
title, -- paper title
eid, -- assigned editor id
submit_date, --- date of submission
status, -- status: 1 accept, 2 under review, 3 revision, 4 reject
paper_author table has the following columns
pid, -- paper id
aid, -- author id
Reviewer table has the following columns
rid, --- reviewer id
rname, --- reviewer name
remail, --- reviewer email
paper_review table has the following columns
prid, -- paper review id
pid, -- paper id
rid, -- reviwer id
due_date, -- review due date
receive_date, -- review received date
round, -- round of review, 1 means first round, 2 means second round, most paper will go through 2 rounds of review
decision, -- 1 accept, 2 minor revision, 3 major revision, 4 reject
rcomment, -- comments of review
Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [50 points]