Database Homework 2 Solved

Consider a system for managing conference paper reviews.  Such systems are used to help administer the process of deciding which researchers’ papers can be presented/published at an academic conference. Researchers submit their papers for consideration for inclusion in the conference, and each paper is reviewed to see whether or not it is worthy of being included.

Assume this system manages a single conference, but the conference is held annually, and all records from each annual meeting must be saved in the database.  These records are important for recruiting authors and reviewers, and for advertising how selective the conference is.  

Researchers write papers about their research and can submit one or more papers to the conference each year.  Researchers can also serve as reviewers during any year (whether or not they submit a paper).  Each submitted paper requires at least 2 reviews.  The system allows reviewers to record their comments about each paper, score the paper with respect to some categories, and recommend whether to accept or reject the paper.  The detailed requirements are as follows:

•       Paper authors are uniquely identified by their email address.  Their first and last names are also kept, as well as their affiliation(s).

•       Each paper is assigned a unique id, and has a title, an abstract, one or more topics, and the filename of   the paper itself.  Once the reviewing process is over, the final decision (which is either accept or reject) for the paper in the conference is stored.  The final decision is made after all reviews are submitted, and is a human decision based on all the scores and comments in each review.

•       A paper may have multiple authors, but a single author is designated as the contact author.  The contact author is the one who gets all the emails about the paper.

•       Reviewers are uniquely identified by email address. A reviewer’s first and last name, phone number, affiliation(s), and set of topics of expertise are kept.  Also, the total number of papers that have been reviewed by this author is stored.

•       Each paper is assigned multiple (at least 2) reviewers.  A reviewer rates a paper on a scale of 110 in four categories: technical merit, readability, originality, and relevance (to the conference). They also give an overall recommendation, which is either accept or reject.

•       A review also includes two text comments: comments to the author, and private comments (to the committee who will make the final decisions about the papers).

•       Authors can also act as reviewers, but they cannot review papers written by themselves or any person who currently shares or has shared the same affiliation as them in the past.

•       An affiliation is a university or research lab where the person works or has worked in the past.  It has a name, country code, and website address. A researcher may have one or more current affiliations, and any number of previous affiliations.  A researcher’s current affiliations have a from-date but the to-date is empty.  Previous affiliations have the from-date and to-date filled in.

•       Presumably, there is some software module that automates the matching of reviewers to papers, using the topics and affiliations.

•       A paper’s most current state in the review process (and the date-time the state was entered), per reviewer, must be stored for each paper.  The review process is as follows:

o    A paper is assigned to a reviewer. o The reviewer either accepts or rejects the offer to review the paper.  If the offer to review is rejected, the reason for rejection is stored.