COMP9120 Database Management Systems

1

Assignment 3: Database Application Development
Group assignment (15%)
Introduction
The objectives of this assignment are to gain practical experience in interacting with a relational database
management system using an Application Programming Interface (API) (JDBC). This assignment additionally
provides an opportunity to use more advanced features of a database such as functions.
This is a group assignment for teams of about 3 members, and it is assumed that you will continue in your
Assignment 2 group. You should inform the teaching assistant (Iwan Budiman) as soon as possible if you
wish to change groups.
Please also keep an eye on your email and any announcements that may be made on Canvas.
Submission Details
The final submission of your database application is due at 11pm on the 22nd May. You should submit the
items for submission (detailed below) via Canvas.
Items for submission
Please submit your solution to Assignment 3 in the ’Assignment’ section of the unit’s Canvas site by the
deadline, including EXACTLY TWO files:
• A SQL file (IssueTrackerSchema.sql) containing the SQL statements necessary to generate the
database schema and sample data. This should contain the original schema and insert SQL statements,
and any changes or additions you may have made.
• A Java file (PostgresRepositoryProvider.java) containing the Java code you have written to
access the database.
Section 1: Introducing the Issue Tracking System
In this assignment, you will be working with an Issue Tracking System (“Issue Tracker”) which is currently
under development. The system still requires work in numerous areas, including the interaction with the
database. Your main task in this assignment is to handle requests for reads and writes to the database coming
from the user interface (UI). We first describe the main features that the Issue Tracker should include from a
UI perspective, and then discuss where the majority of your database code needs to be implemented.
Main Features
Logging In
The first form a user is presented with when starting Issue Tracker is Login, as shown in Figure 1. This feature
is still under development and currently only requires that a user enters their UserName to log into the system
(secure logins will be implemented at a later stage and are not part of this assignment). Once logged in, a
user is taken to the Issues screen to see their associated issues.
COMP9120 Assignment 3
2
Figure 1 – Login form
Viewing Issue List
Once a user has logged in, they are shown a list of all their associated issues, as illustrated below in Figure
2. This issue list must be ordered by Title. Each issue has a creator, and can also have a resolver and/or
verifier and/or a description. An issue is associated with a user if they are recorded as its creator or resolver
or verifier.
Figure 2 – Viewing Issue List
Finding Issues
A user can search through all issues by entering a word or phrase (a ‘keyword’) in the field above the Find
button, as shown in Figure 3, and then clicking on Find. When such a keyword is specified, then only issues
including this word or phrase in their title will be retrieved and shown in the list. For example, given the
search keyword ‘zero’, Find will return all issues that include the word ‘zero’ in their title. Searching with a
blank/empty keyword field will show all of the logged in user’s associated issues. Any search results
returned must be ordered by Title.
COMP9120 Assignment 3
3
Figure 3 – Finding Issues
Viewing an Issue’s details
Selecting the title of an issue from the issue list will present details for that issue, as shown in Figure 4. This
description appears in the large text area in the bottom-right part of the form.
Figure 4 – Viewing an Issue’s Details
Adding an Issue
Users may also add a new issue by clicking on the Add New Issue button, entering issue details in the
popup dialog that appears, and then clicking on Add Issue, as shown in Figure 5. . Roles that have not yet
been assigned for an issue must be entered as a dash (‘-‘).
COMP9120 Assignment 3
4
Figure 5 – Adding a new Issue
Updating Issues
Users can also update an issue by modifying data in the issue details screen, as shown in Figure 6, and
clicking on the Update Issue Button.
Figure 6 – Updating an Issue
The Refresh/Reset button can be used to refresh the issue details from the database, allowing the user to
check that their changes have been made, or to see if an issue has been updated.
COMP9120 Assignment 3
5
Database Interaction Code
The files that are needed for the Java version of this assignment can be downloaded from the Canvas Modules
page (in the Assignment section). The files you will need are:
1. IssueTrackerSchema.sql: a file which contains SQL statements you need to run to create
and initialise the IssueTracker database.
2. Assignment3_JavaSkeleton.zip: a zip file containing the Java project for IssueTracker.
Before working on the IssueTracker code, you’ll need to import the Eclipse project archive file into Eclipse.
Please begin by trying to import the project into Eclipse using the steps below:
1) Right Click the zip file containing the assignment 3 archive >7zip>Extract Here.
2) Start Eclipse and choose a new location for your workspace in a new folder.
3) Click on File>Import>General>Existing Projects into Workspace>Next
4) Browse to the folder you extracted zip file to, and then click OK.
5) Ensure the Assignment 3 Project is ticked in the Projects box, and click Finish.
If you experience any difficulties importing the eclipse project, ask your tutor for assistance.
Once you import the project in Eclipse, you should notice that there are 3 main packages in the solution:
Presentation, Business, and Data. The UI (in the Presentation package) is currently coded to invoke logic in
the business layer (eg: see IssueProvider class in the Business package), which in turn delegates
responsibility for interacting with the database to an appropriate repository in the data layer (eg: see
PostgresRepositoryProvider in the Data package). Notice that separating concerns in this way makes it easier
to write a different RepositoryProvider in the data layer (eg: we could write a MySQLRepositoryProvider) if we
wanted to change our database management system. In this assignment, you will mainly be working on writing
the appropriate queries and SQL commands to fulfil the IssueTracker functionality described in Section 1;
where these SQL Commands and queries should be written in the PostgresRepositoryProvider. You should
use the correct username/password details as specified in tutorial 7. The application’s main method can be
found in the IssueTrackerFrame class. You can run this main method to run the entire application by right
clicking the IssueTrackerFrame file on the Project Explorer > Run As > Java Application.
Section 2: Your Task
Core Functionality
In this assignment, you are provided with a Java skeleton project that must serve as the starting point for your
assignment. Your task is to provide a complete implementation for the file
PostgresRepositoryProvider.java, as well as make any modifications necessary to the database
schema (IssueTrackerSchema.sql). Specifically, you need to modify and complete these functions:
1. checkUserCredentials (for login)
2. findUserIssues (for viewing issue list)
3. findIssueBasedOnExpressionSearchOnTitle (for finding issue)
4. addIssue (for adding issue)
5. updateIssue (for updating issue)
Note that, for each function, the corresponding action should be implemented by issuing SQL queries to the
database management system. Directly returning results without issuing SQL queries will be considered as
cheating, and you will get zero points for the assignment.
COMP9120 Assignment 3
6
Marking
This assignment is worth 15% of your final grade for the unit of study.
Group member participation
If members of your group do not contribute sufficiently you should alert your tutor as soon as
possible. The course instructor has the discretion to scale the group’s mark for each member as follows:

Marking Rubric
Your submissions will be marked according to the following rubric, with a maximum possible score of 15 points.

The post COMP9120 Database Management Systems appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms