数据库代写 | DATABASE SYSTEMS CMPT 354 Final Project

这个作业是针对竞赛相关的数据库系统进行查询、操作等

DATABASE SYSTEMS

CMPT 354 Final Project

Important Note: Before you begin, please read carefully the policy on Academic misconduct, in particular about the publication of assignments and tests in online repositories.

Students may work in teams up to 3 people. You may NOT provide or use any solution, in whole or
in part, to or by another student/team. Students are not allowed to publish tests, assignments and their
solutions (e.g., put them on an open public repository).

In this project, you are allowed (and even encouraged) to share the techniques of how to establish
connectivity, and overcome potential technical problems, but you are not allowed to share your team’s
programs. Sharing unrelated to the questions in this project “dummy” or “prototype” programs is fine.

You are NOT allowed to share SQL solutions for the queries.

Description (slightly modified from the first SQL/Lab assignment). A research grant council
provides grants to researchers to run research projects. It holds basic information about each grant
competition, submitted grant applications, grant reviewers and about awarded applications. It also has
information about the scheduled meetings to decide on the submitted proposals, for each competition.

• The database stores information about all researchers. For each researcher, the database should
keep the first and last name and the email address, and the organization (university) they work for.

• Each call for grant proposals has a number, title, application deadline, a short description, and an
area, e.g., biology, computer science, etc., and the current status of the competition (open/closed).

• Each submitted grant proposal has its number, the requested amount, which call (competition)
it is for, a principle investigator, and other collaborators (researchers). It also has an application
status, e.g., submitted, awarded, not awarded, and the amount of money, if awarded, all for a
specific date. Note that the awarded amount can be the same, bigger or smaller than the
requested amount (usually, smaller).

• Researchers not only submit research proposals to grant competitions, but also sometimes act as
reviewers for grant applications. Each reviewer has records about which grant applications they
reviewed, and who their co-reviewers were for each grant. Their records also include conflicts of
interests, i.e., the names of other researchers whose grants they are not allowed to review. For
example, this happens if they are from the same department, or they co-authored a paper in the
past two years.
1

• Each reviewer assignment specifies which grant competition it is for, who the reviewers are, the
deadline for the reviews and whether a review is submitted or not, for each reviewer.

• Reviewers participate in Grant Selection Committees’ meetings. At each meeting, several calls for
grant proposals are discussed. For each call, only reviewers who reviewed proposals for that call can
participate in the discussion, but all participating reviewers come to the meeting.1 Each meeting
has a specific date (don’t worry about the time).
We are providing you with a set of DDL statements defining the table structure. It will be posted on
PIAZZA under Resources.

Task 1. (10 points) Set up the corresponding database in PostgreSQL using the provided DDL statements. Extend it as needed. Populate your database with realistically looking data.

Task 2. (120 points) Create an Embedded SQL interface (in your favourite programming language)
to this database that provides the following query capabilities. Your program should be able to catch
any PostgrSQL error message and should give the appropriate message to the user. At the end of each
operation the user should get a message indicating whether the corresponding operation was successful.
Your program should indicate clearly to the user what data is expected as input and check if the input
entered by the user confirms to the requirements. It should not crash, even if the user enters incorrect
data.

Q1 (20 points) Find all competitions (calls for grant proposals) open at a user-specified month, which
already have at least one submitted large proposal. For a proposal to be large, it has to request more
than $20,000 or to have more than 10 participants, including the principle investigator. Return both
IDs and the titles.

Q2 (10 points) Next, modify your program for Q1 by allowing the user to specify the areas (e.g., biology
and chemistry) (s)he is interested in, and only displaying the competitions where the submitted large
proposals have principle investigators specified by the user.

Q3 (10 points) For a user-specified area, find the proposal(s) that request(s) the largest amount of
money.

Q4 (10 points) For a user-specified date, find the proposals submitted before that date that are awarded
the largest amount of money.

Q5 (10 points) For an area specified by the user, output its average requested/awarded discrepancy,
that is, the absolute value of the difference between the amounts.

Q6 (30 points) Reviewer assignment: Provide the user with the option of assigning a set of reviewers to
review a specific grant application (research proposal), one proposal at a time. The proposal ID
should be specified by the user. Before doing the reviewers assignment, the user should be able to
request and receive a list of reviewers who are not in conflict with the proposal being reviewed,
and who still have not reached the maximum of three proposals to review.

1For your information (not to be implemented): The purpose of the meeting is to discuss, for each competition, all the
submitted proposals, to evaluate them, and to rank them. The ranking will determine which proposals will be awarded the
money, and also how much.

2
Q7 (30 points) Meeting scheduling: Your application should check if the user-entered room is available
at a the user-entered date. If yes, the user should be prompted to enter 3 competitions (calls)
IDs to be discussed and decided on that day. If a competition cannot be scheduled to be discussed
on that day (because some of the reviewers are not available), then the user should be prompted
that scheduling a discussion on that particular competition is impossible on that day (a simplified
version just returns “Impossible”). Here, for a reviewer “not to be available” means that he or she
is scheduled to be in another room on the same day.

Task 3. (40 points) This is a on-paper-only part (i.e., not to be implemented). Write the following
assertions. Each is worth 10 points.

• Conflicts of interests must be taken into account in reviewer assignments. That is, if currently there
is a conflict, the corresponding reviewer cannot be assigned.

• Reviewers of a proposal must not be collaborators or principle investigators on a proposal submitted
for the same grant call.

• A reviewer cannot be assigned to attend more than one meeting in two consecutive days.

• Meeting attendees must have reviewed proposals for at least one competition (call) discussed on
that day.

What to submit: For each team: two files, named as follows:

• LastName1LastName2LastName3-Report.pdf — a pdf report (for Tasks 2 and 3) that lists the
team’s names and student numbers, explains your work, lists all assumptions and provides all
necessary instructions to the TAs on how to test your implementation;

• LastName1LastName2LastName3-Commands —.zip-ed directory (for Tasks 1 and 2) with:
– LastName1LastName2LastName3-Shema — all DDL statements,
– LastName1LastName2LastName3-Insert — all Insert statements,
– LastName1LastName2LastName3-Drop — drop statements for all tables that you create,
– LastName1LastName2LastName3-Program — embedded SQL program,
– LastName1LastName2LastName3-Makefile — we must be able to use it to get a correct executable.

The Report should be clear and concise (do not write unnecessary words). Please submit both files
through the CourSys system.

Note: One submission per team. Do not repeat submission for each team member.
Important: The TA should be able to run your program on his/her database (created with your DDL
commands) in CSIL remotely. Please provide all necessary instructions in your .pdf report.

Bonus: Up to 30 bonus points will be given to individual students (not teams) who write a short tutorial
for other students on technical issues you encountered (specific to a chosen programming language) and
helps others on Piazza. The HowTo tutorial should be similar to the PostgreSQL tutorial we posted at
the beginning of the course. Please see the Important Note at the beginning of this Project Description
regarding what you are allowed to discuss.