British authors

You are required to finish all the following 4 tasks:
ˆ Task 1 (SQL) (10 Marks): This task requires you to write Oracle SQL
DML statements. For each of the following, give the Oracle SQL DML
query which fulfils the request:
a) List the IDs and full names of all authors, sorted alphabetically by
last name. (2 Marks)
b) Return the number of British authors. (3 Marks)
c) For each pending (has not been completed yet) paper review, list the
the title of the paper and the full name of the reviewer responsible
for the review, ordering the result by paper submission date, earliest
first. (5 Marks)
ˆ Task 2 (PL/SQL) (10 Marks): This task requires you to write Oracle
PL/SQL code:
a) Write an Oracle stored procedure that receives as input a number
representing a paper ID and then lists the full names of all reviewers
who have a conflict of Interest with the paper in question. You should
handle any necessary exceptions/errors which may arise, e.g. the paper ID does not exist. (10 Marks)
ˆ Task 3 (Python & Oracle) (22 Marks): This task requires you to
write a Python desktop application that interfaces with the Conference
database. Your application needs to perform the following 2 tasks:
4
UFCFTK-30-1 Resit Coursework Deadline: 3rd August 2021 at 14:00
a) Allow the user to add a new institute. The user will be prompted to
enter an institute name and a country name and then the application
adds the new institute to the INSTITUTE table. The ID for the new
institute must be in sequence and takes into account the most recent existing InstituteID. Perform the required checks, e.g. to check
whether the country exists and if there is any violation of any of the
constraints on the INSTITUTE table. (10 Marks)
b) Allow the user to obtain the highest (completed) review score of a
particular paper. The user will be prompted to enter a paper ID
and then the highest review score of that particular paper will be
displayed to the user. You should also display the number of completed reviews used in calculating the maximum score. You should
handle any relevant exceptions and display appropriate messages. (8
Marks)
4 Marks will be given if the application has desirable features, i.e. main
interface, appropriate input validation, well-commented code, modular design, and appropriate error handling.
ˆ Task 4 (MongoDB) (8 Marks): This task requires you to write MongoDB code. Perform the following 2 tasks:
a) Design a MongoDB database that stores the IDs, full names, and
the country name of the first 5 authors from the Author table in the
Conference relational database. You do not need to worry about the
rest of the Author/Country details. (5 Marks)
b) Give a MongoDB query that returns all details of author’s whose
country name contains either the phrase mar or the phrase an (irrespective of the case). (3 Mark)