Introduction

The goal of this assignment is to improve your skills of writing declarative queries on a relational database, in general, and also to improve your fluency in SQL (and SQLite).

Assume you have been provided with the following relational schema (loosely based on the ER used for Assignment #1):

Table PAPERS (Information on author papers)

Attribute	Description
ID	Unique paper identification number (primary key)
TITLE	Title of the paper
DECISION	Domain: A (Accepted), R (Rejected), or NULL
AREA	A keyword which describes the area of the paper
AUTHOR	Email of the contact author
CSESSION	To be presented in this conference session if accepted

Note: AREA is a foreign key referencing AREAS.NAME, AUTHOR is a foreign key referencing USERS.EMAIL and CSESSION is a foreign key referencing SESSIONS.NAME

Table USERS (Information on all users of the system)

Attribute	Description
EMAIL	Unique identification of any user (primary key)
NAME	Name of the user
PHONE	Phone number of the user

Table SESSIONS (Information on conference sessions)

Attribute	Description
NAME	The name of the session (primary key)
CHAIR	The chair of the session

Note: CHAIR is a foreign key referencing USERS.EMAIL.

Table AREAS

Attribute	Description
NAME	Keyword/name of an area (primary key)

Table REVIEWS (Information on the review of each author paper)

PAPER	The paper being reviewed (primary key)
REVIEWER	The reviewer which submitted these marks (primary key)
ORIGINALITY	The originality mark of the paper
IMPORTANCE	The importance mark of the paper
SOUNDNESS	The soundness mark of the paper
OVERALL	The overall mark of the paper

Note: PAPER is a foreign key referencing PAPERS.ID and REVIEWER is a foreign key referencing USERS.EMAIL.

Table EXPERTISE (Information on the expertise of each internal reviewer)

Attribute	Description
AREA	An area (keyword) in this conference (primary key)
REVIEWER	Identification (email) of the reviewer (primary key)

Note: AREA is a foreign key referencing AREAS.NAME and REVIEWER is a foreign key referencing USERS.EMAIL.

Creating the database

Using the SQL CREATE TABLE statements provided here. The SQL statements are provided at the bottom.

You are to create your own testing database.

(90 marks) Queries

Note that you cannot use views or temporary tables unless you are explicitly asked to do so.

1. List the number of accepted papers in every area in which there is at least one paper submitted, results must be presented sorted in alphabetical order by area.

2. For each author, find the number of distinct sessions that s/he will be presenting papers in.

3. For each author and session, find the number of papers s/he will be presenting in that session. If an author is not presenting in a session, no row for that author and session should be shown in your result.

4. For each author and session, find the number of papers s/he will be presenting in that session. If an author is not presenting in a session, number zero should be shown for that author and session.

5. List the title of every paper whose overall mark is more than the average of the other three marks (originality, importance and soundness) for at least one reviewer (i.e., each paper needs to be listed only once).

6. For each area whose papers have on average overall mark greater than 4, show the name of the area, the email addresses and the names of the reviewers that have reviewed papers in this area.

7. For every user show his/her name and which papers (title) he/she was assigned to review, sorted by the name of the user. If a particular user was not assigned to review any paper, his/her name should be listed besides a NULL or empty value.

8. List the names of the areas (with no repetition) where all reviewers with an expertise in that area are assigned exactly 2 papers (regardless of the areas of the papers).

9. List the area(s) that has the largest average originality mark.

BEGIN TRANSACTION; CREATE TABLE `areas` ( `name` TEXT, PRIMARY KEY(name) ); CREATE TABLE `users` ( `email` TEXT, `name` TEXT, `phone` TEXT, PRIMARY KEY(email) ); CREATE TABLE `sessions` ( `name` TEXT, `chair` TEXT, PRIMARY KEY(name), CONSTRAINT fk_chair FOREIGN KEY (chair) REFERENCES users(email) ); CREATE TABLE "reviews" ( `paper` INTEGER, `reviewer` TEXT, `originality` INTEGER, `importance` INTEGER, `soundness` INTEGER, `overall` INTEGER, CONSTRAINT fk_reviewer FOREIGN KEY (reviewer) REFERENCES users(email), CONSTRAINT fk_paper FOREIGN KEY (paper) REFERENCES papers(Id) ); CREATE TABLE "papers" ( `Id` INTEGER PRIMARY KEY AUTOINCREMENT, `title` TEXT, `decision` TEXT, `area` TEXT, `author` TEXT, `csession` TEXT, CONSTRAINT fk_author FOREIGN KEY (author) REFERENCES users(email), CONSTRAINT fk_area FOREIGN KEY (area) REFERENCES areas(name), CONSTRAINT fk_csession FOREIGN KEY (csession) REFERENCES sessions(name) ); CREATE TABLE `expertise` ( `area` TEXT, `reviewer` TEXT, CONSTRAINT fk_area FOREIGN KEY (area) REFERENCES areas(name), CONSTRAINT fk_reviewer FOREIGN KEY (reviewer) REFERENCES users(email) ); COMMIT;