COSC 61 Lab 2: Manuscript Database

Charles Ihara

As part of a project for CS61 (Database Systems), I created a database system to store manuscript data for an academic journal. It is comprised of 3 parts:

1. Part a: to design the databse, I created an Entity-Relationship Diagram in MySQLWorkbench based on the provided domain description.

2. Part b: to build the database, I wrote SQL scripts to create the tables for storing the manuscript data and inserted fake manuscript data for testing.

3. Part c: to automate the database, I created views of the data and implemented triggers to appropriately handle data insertion, deletion, and updates.

Part a: Desiging the database

Lab 2a ERD.mwb contains an Entity Relationship Diagram (ERD) that I constructed based on the following domain description:

    The Journal Of Competitive Kinesiology is a prestigious research journal for collegiate sports team physicians. It uses a peer-review process to select manuscripts for publication. About 10 percent of the manuscripts submitted to the journal are accepted for publication. A new issue of the journal is published each quarter.

    Unsolicited manuscripts are submitted by authors. When a manuscript is received, it is assigned a unique manuscript number, and basic information about it is recorded in the system database. The title of the manuscript, the date it was received, and an initial manuscript status of "submitted" are entered. Information about the author(s) is recorded. For each author, the author’s name and current affiliation (school or company for which the author works) is recorded. When a manuscript has multiple authors, it is important to record the order in which the authors are listed in the manuscript credits. The first author is designated as the primary author and their e-mail address is recorded.

    Every manuscript must have at least one author and one InterestCode (ICode, which are defined here). The information about Authors that have submitted manuscripts is kept in the system. It is typical for a manuscript to have several authors. A single author may have co-authored many different manuscripts submitted to the journal.

    Once the manuscript is received and entered into the database, the editor will briefly review the topic of the manuscript to ensure that the manuscript’s contents fall within the scope of the journal. This scope is defined by a set of ICodes defined for the Journal. If the content is not within the scope of the journal, the manuscript’s status is changed to “rejected” and the primary author is notified via e-mail. If the manuscript is among those appropriate for the Journal, the editor begins the selection of three or more reviewers to review the manuscript. The manuscript status will remain “submitted” until the three reviewers are assigned, whereupon the status will change to “review”.

    Reviewers work for other companies or universities and read manuscripts to ensure the scientific validity of the manuscripts. For each reviewer, the system records a reviewer number, reviewer name, reviewer e-mail address, affiliation, and one to three ICodes which represent the reviewer’s areas of expertise. A reviewer may have up to three areas of expertise, and an area of expertise may be associated with many reviewers. All reviewers must specify at least one area of expertise.

    If at least three reviewers have identified the manuscript’s ICode as one in which they have expertise, the editor will change the status of the manuscript to “under review”, email the manuscript to the reviewer with a cover letter, and record which reviewers the manuscript was sent to and the date on which it was sent to each reviewer. A reviewer may receive several manuscripts to review each year, while others may not have reviewed any manuscripts yet.

    If there are an insufficient number (i.e., less than 3) of reviewers in the system with that ICode, the editor has to reject the manuscript. The manuscript’s status is changed to “rejected” and the primary author is notified via e-mail

    The reviewers will read the manuscript at their earliest convenience and provide feedback to the editor. The feedback from each reviewer includes rating the manuscript on a 10-point ACME scale (10 is best) for Appropriateness, Clarity, Methodology, and Experimental results, for a total score of up to 40 points, a recommendation for publication (ACCEPT (10 points) or REJECT (0 points)). The editor will add all this information for each review received and the date that the feedback was received. Once all the reviewers have provided their evaluation of the manuscript, the editor will decide whether to publish the manuscript based on a minimum average of all the reviewer’s final scores. This minimum average is defined by the system at startup (e.g., 40 or the possible 50 points might be a good minimum for a selective journal). If the editor decides to publish the manuscript, the manuscript’s status is changed to “accepted” and the date of acceptance for the manuscript is recorded. If the manuscript is not acceptable, the status is changed to “rejected” and the primary author is notified by email.

Part b: Constructing the database

Using my ERD from Part a, I wrote the following .sql scripts:

1. tables.sql to create all tables. I included DROP ... IF EXIST ... before CREATE statements, key and foreign key constraints, and checks where possible to ensure that valid data can be inserted or updated in the database.

2. inserts.sql to fill the tables with example data. I included a sufficient quantity and variety of data in the database to test that the database can handle the various operations specified above and to ensure that the database works as expected in all cases, both common occurrences and rare or edge cases.

3. table_insert_tests.sql contains the SQL commands to check that tables.sql and inserts.sql are working properly.

Part c: Automating the database

Views

In views.sql, I created the following SQL VIEWS to provide limited views of the database information in a simple form:

1. LeadAuthorManuscripts

For all authors, return tuples with the following:

• last name
• author id
• the manuscript id for which they are the primary author, along with the current status of the manuscript, and the timestamp of the most recent status change.

If an author currently has two manuscripts in the system, the view would return two tuples (rows). Results ordered by author last name, author id number, and then by increasing timestamp of the most recent status change.

2. AnyAuthorManuscripts

For all authors, display the following:

• full name
• author id
• the manuscript(s) for which they are among the authors (if any)
• the status of the manuscript(s)

with results ordered by author last name and then by increasing timestamp of the most recent status change.

3. PublishedIssues

For all completed (published) issues, display the following:

• title of each manuscript accepted in that issue

4. ReviewQueue

For all manuscripts in "under review" state. For each such manuscript the view should include a row(s) containing:

• primary author full name
• primary author id
• manuscript id
• assigned reviewer id(s)

with the rows ordered by increasing submission timestamp. This View is also used by ReviewStatus.

5. ReviewStatus

For all manuscripts assigned to a specified Reviewer. For each such manuscript, the view should include a row containing:

• the timestamp when it was assigned to this review
• the manuscript id
• the manuscript title
• the review results (integer values 1-10)
  • appropriatenes score
  • clarity score
  • methodology score
  • experimental resuts score
  • recommendation
• status (either "received", “under review”, “accepted” or “rejected”)

ordered by increasing submission timestamp.

Triggers

In triggers.sql, I implemented the following SQL TRIGGERS to handle some key constraints in the day-to-day use of the database:

1. triggerNoReviewer When a new manuscript is submitted the submitting author must specify an ICode. It is possible that there may no reviewer in the system that indicated that ICode as one he/she could handle. Thus, when a new manuscript is added to the system with an ICode for which there are not at least 3 reviewers who handle that ICode this trigger should raise an exception that informs user the paper can not be considered at this time.

2. triggerReviewerResign Reviewers sometimes unexpectedly withdraw their services. If there are no reviewers with ICodes matching the manuscript’s that are not already assigned to the manuscript, the manuscript must be rejected. When a reviewer resigns (deleted from database), any manuscript in “review” state for which that reviewer was a reviewer AND there is another reviewer in the system with the matching ICode that is not already assigned to review it, that manuscript must be reset to “submitted” and an appropriate exception message displayed. Otherwise, the manuscript must be rejected by setting its state to “rejected” and an appropriate exception message displayed.

3. triggerNoReviewer If no reviewer with the required ICode is available, the manuscript should be rejected. Note that “available” means a reviewer exists with the right ICode and who is not already assigned to review that manuscript.

4. triggerPublish When a manuscript’s status changes to “accepted”, that update triggers an immediate status change to “publish”.

Stored Procedure

In procedures.sql, I provide a stored procedure (function) that can be invoked to make the accept/reject decision based upon the average score from the reviewers for a manuscript. All the logic for the decision is localized in this stored procedure/function for ease of update. It returns an indication of its result.

The minimum score for a manuscript to be accepted should be stored in a global SQL variable and set in a .sql file that is run before this one.

Testing

I wrote the following scripts to test the functionality described above:

1. view_tests.sql for testing the views.
2. trigger_tests.sql for testing the triggers.
3. procedure_tests.sql for testing the stored procedure.