Problem 1. Please create the following tables for a journal article review database with appropriate primary keys & foreign keys. [50 points]

Assumptions:

1. Each paper has one or more authors, each author can write one or more papers.
2. Each paper is assigned to an editor. Each editor can oversee multiple papers.
3. 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

raffiliation, --- reviewer's affiliation

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]