Description

Consider the design of the following database system for managing a particular conference: a

collection of papers are submitted to the conference, each of which has a unique paper ID, a list of

authors (names, affiliations, emails) in the order of contribution significance, title, abstract, and a PDF

file for its content. The conference has a list of program committee (PC) members to review the

papers. To ensure review quality, each paper is assigned to 3 PC members for review. To avoid

overloading, each PC member is assigned with at most 5 papers, assuming that there are enough PC

members. Each review report consists of a report ID, a description of review comment, a final

recommendation (accept, reject), and the date the review report is submitted. A PC member can

submit at most one review report for the paper that is assigned to him/her.

Part 1

Design the database using the ER approach. Then using Java and SQL, implement the following

functionality:

1. Implement a button called

Initialize Database

. When a user clicks it, all necessary tables

will be created (or recreated) automatically, with each table be populated with at least 10

tuples so that each query below will return some results. All students should use the database

name sampledb, username john, and password pass1234.

2. Create the functionality to assign three reviewers to a paper.

Your system must be web-based. Some simple GUI interfaces are required for each functionality.

All

functionality must be performed via the interface of your system, direct SQL statement

execution via any tools (MySQL workbench) is not allowed.

Part 2

Based on part 1, implement the following functionality using Java and SQL with simple GUI interface.

Populate each table with sufficient tuples so that each query below will return some results.

1. Insert/delete/update a paper (all attributes except the paper id).The paper id should

be generated by the system automatically using MySQL autoincrement.

2. Insert/delete/update a PC member (all attributes except the member id).The

member id should be generated by the system automatically using MySQL

autoincrement.

3. Insert/delete/update a review report (all attributes except the report id). The report

id should be generated by the system automatically using MySQL autoincrement.

4. List all the papers by Fotouhi (last name) as the single author.

5. List all the papers in which Fotouhi is the first author.

6. Find all the papers coauthored by Lu and Fotouhi.

7. List the PC member who reviews the most number of papers; if there is a tie, list

all PC members that are part of the tie.

8. List the PC members who have no papers assigned to them for review.

9. List all papers rejected by both PC members Matt and John.