COMP9120 Database Management Systems

Assignment 2: Database Application Development

Group Assignment (13%)

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) (Python DB-API). 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 1 group. You should inform. the unit coordinator 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 Ed.

Submission Details

The final submission of your database application is due at 11:59pm on Tuesday 1th  November. You should submit the items for submission (detailed below) via Canvas.

Items for submission

Please submit your solution to Assignment 2 in the ’Assignment’ section of the unit’s Canvas site by the deadline, including EXACTLY THREE files:

•    An assignment coversheet as a PDF document (.pdf file suffix) which is available for download from this link on Canvas.

•    A SQL file  (SOTSchema.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 Python file (database.py) containing the Python code you have written to access the database.

Task 1: The SharePlus Online Trading (SOT) System

In this assignment, you will be working with the SOT system which is currently under development. The system still requires work in numerous areas, including 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 SOT system should include from a UI perspective, and then discuss where the majority of your database code needs to be implemented.

Logging In

The first form a user is presented with when starting the SOT system is Login, as shown in Figure 1. This feature is still under development and currently requires that an administrator enters their username and password to be validated prior to successfully log in to the system. Security features such as password encryption/hashing will be implemented at a later stage (and is out of scope for this assignment). Once logged in, the user is taken to the regular investment instructions list page to view their associated instructions for their customers.

Figure 1 – Login form.

Viewing Instructions List

Once a user has logged in, they are shown a list of all their associated instructions, as shown below in Figure 2. This list must be ordered such that only active instructions appear at the top. The list is also sorted by expiry date in ascending order, and customer full name in descending order. Each instruction has an amount, frequency, expiry date, customer full name, ETF name, and notes. An instruction is associated with an administrator if the administrator is allocated as the staff assisting customers with their investment instructions.

Figure 2 – Viewing Instructions List

Finding Instructions

A user can search through all investment instructions by entering a word or phrase (a ‘keyword9) in the field next to the Find button, as shown in Figure 3, and then clicking on Find. When such a keyword is specified, then the retrieved instructions and shown on the list are those that include this word or phrase in the customer9s full name, ETF name, or notes. The search must ignore case sensitivity. For example, given the search keyword ‘carie b’, Find will return all instructions that include the keyword ‘carie b’ in the customer’s full name, ETF name, or notes. Searching with a blank/empty keyword field will show all of the logged in user’s associated instructions. Any search results returned must be ordered such that instructions without an administrator would appear at the top, and by expiry date in ascending order. The search results must also exclude instructions that have expired.

Figure 3 - Finding Instructions

Adding an Instruction

Users may also add a new instruction by clicking on the Add Instruction tab in the title bar, entering investment instruction details such as amount, frequency, and customer, in the popup dialog that appears, and then clicking on Add Instruction button, as shown in Figure 4. A new regular investment instruction has a default expiry of twelve months from the current date.

Figure 4 - Adding an Instruction

Updating an Instruction

Users can also update an instruction by modifying data in the instruction details screen as shown in Figure 5, and clicking on Update Instruction button. You can access this update screen by clicking on an instruction from the list of instructions in the Instructions tab.

Figure 5 - Updating an Instruction

Database Interaction Code

The files that are needed for the Python version of assignment are as follows:

1. SOTSchema.sql: a file which contains SQL statements you need to run to create and initialise the SOT system database, before starting the application

https://canvas.sydney.edu.au/files/26926696/download?download_frd=1

2. Assignment2_PythonSkeleton.zip: a zip file encapsulating the Python project for the

SOT systemhttps://canvas.sydney.edu.au/files/26926697/download?download_frd=1

To inspect the SOT system code, you need to unzip the ZIP archive first, which will create a folder that includes the name Assignment2_PythonSkeleton. If you experience any difficulties installing and exploring the project, ask your tutor or lecturer for assistance.

The skeleton code uses a number of Python modules to implement a simple browser-based GUI for the SOT system. The main modules are the Flask framework for the GUI and the psycopg2 module for the PostgreSQL database access. Similar to tutorial 7, you will need to install the Psycopg2 module and the Flask module. The skeleton code follows the structure described below:

•  The main program starts in the main.py file. You need to use the correct username/password details as specified in tutorial 7, and then implement the missing database access functions - including any necessary SQL code statements required - in the data layer database.py.

•  The presentation layer is done via a simple HTML interface that can be accessed from a web browser.

The corresponding page templates are located in the templates/ subdirectory, their CSS style file is static/css.

•  The transition between the different GUI pages and the initialisation of the Flask framework is done in the routes.py file. It currently just invokes the pages, but there is no further business logic implemented yet.

You can run the code by running “python main.py”.  This starts a local web server and prints out some debug messages in the terminal; the GUI can then be accessed with any web browser on the same computer via   the   local    URL http://127.0.0.1:5000/ (If   that   doesn’t   work  you    can   also   try http://0.0.0.0:5000/). Please note that, to terminate the application, you will need to stop the local web server which is running in the background.

Task 2: Functions Implementation

Core Functionality

In this assignment, you are provided with a Python skeleton project that must serve as the starting point for your assignment. Your task is to provide a complete implementation for the file database.py, as well as make any modifications necessary to the database schema (i.e., SOTSchema.sql). Specifically, you need to modify and complete these five functions:

1. checkAdmCredentials (for login)

2. findInstructionsByAdm (for viewing instructions list)

3. findInstructionsByCriteria (for finding instructions)

4. addInstruction (for adding instruction)

5. updateInstruction (for updating instruction)

Note that, for each function, the corresponding action should be implemented by issuing SQL queries to the database management system. If you directly output and/or manipulate the result without issuing SQL queries, you are considered as cheating, and you will get zero point for the assignment.

Marking

This assignment is worth 13% of your final grade for the unit of study. Your group’s submission will be marked in line with the rubric that follows.

Group member participation

If members of your group do not contribute sufficiently, you should alert the unit coordinator as soon as possible. The course instructor has the discretion to scale the group’s mark for each member as follows:

Note:  The above table assumes that each group will have 3 members, so, on average, around 33% contribution is expected from each member of the group. In special case, if the group has less than 3 members then the contribution percentage will be adjusted accordingly. You must justify your contribution percentage by providing detailed explanation of your contribution in the cover sheet mentioned before. Furthermore, we will run random face-to-face interviews to understand your contribution, if needed.

Marking Rubric

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