1 School of Computer Science COMP9120 Database Management Systems Semester 2 2020 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) (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 2 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 Canvas. Submission Details The final submission of your database application is due at 11:59pm on the 20th Nov. 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 (HealthCareSchema.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. Section 1: Introducing the Health Care System In this assignment, you will be working with a Health Care System 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 Health Care 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 Health Care System is Login, as shown in Figure 1. This feature is still under development and currently requires that a user (doctor) enters their Username and Password to be validated prior to successfully log into the system. Security features such as password encryption/hashing will be implemented at a later stage (and not as part of this assignment). Once logged in, a user is taken to the Patients List page to see their associated patients. COMP9120 Assignment 3 2 Figure 1 – Login form Viewing Patient List Once a user has logged in, they are shown a list of all their associated patients, and their details, as shown below in Figure 2. This patient list must be ordered by patient’s name. Each patient may or may not have an assigned doctor, and can also have a name, an age, symptoms and a description of doctor advice. A patient is associated with a user if the user is recorded as the patient’s assigned doctor. Figure 2 – Viewing Patient List Finding Patients A user can search through all patients by entering a word or phrase (a ‘keyword’) 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 only patients including this word or phrase in their names or assigned doctor’s usernames will be retrieved and shown in the list. The search must also ignore case sensitivity. For example, given the search keyword ‘wade’, Find will return all patients that include the word ‘wade’ in their names or assigned doctor’s usernames. Searching with a blank/empty keyword field will show all of the logged in user’s associated COMP9120 Assignment 3 3 patients. Any search results returned must be ordered by patient’s name. Figure 3 – Finding Patients Adding a Patient Users may also add a new patient by clicking on the Add Patient tab in the title bar, entering patient details in the popup dialog that appears, and then clicking on Add Patient. Doctor that has not yet been assigned for a patient must be entered as a dash (‘-‘). Figure 4 – Adding a new Patient COMP9120 Assignment 3 4 Updating Patients Users can also update a patient by modifying data in the patient details screen as shown in Figure 5, and clicking on the Update Patient Button. You can access this update screen by clicking on a patient from the list of patients in the Patients tab. Figure 5 – Updating a Patient Database Interaction Code The files that are needed for the Python version of assignment are as follows. 1. HealthCareSchema.sql: a file which contains SQL statements you need to run to create and initialise the HealthCareSystem database, before starting the application https://canvas.sydney.edu.au/files/13210637/download?download_frd=1 2. Assignment3_PythonSkeleton.zip: a zip file encapsulating the Python project for HealthCareSystem https://canvas.sydney.edu.au/courses/25908/files/13233429/download?download_frd=1 To inspect the HealthCareSystem code, you need to unzip the ZIP archive first, which will create a folder that includes the name Assignment3_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 health care 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. COMP9120 Assignment 3 5 • 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. Section 2: Your Task 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., HealthCareSchema.sql). Specifically, you need to modify and complete these functions: 1. checkUserCredentials (for login) 2. findPatientsByDoctor (for viewing patient list) 3. findPatientsBasedOnName (for finding patient) 4. addPatient (for adding patient) 5. updatePatient (for updating patient) Note that, for each function, the corresponding action should be implemented by issuing SQL queries to the database management system. If you directly output the result without issuing SQL queries, you are considered as cheating, and you will get zero points for the assignment. Marking This assignment is worth 15% 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 your tutor as soon as possible. The course instructor has the discretion to scale the group’s mark for each member as follows: Level of contribution Proportion of final grade received No participation. 0% Full understanding of the submitted work. 50% Minor contributor to the group’s submission. 75% Major contributor to the group’s submission. 100% COMP9120 Assignment 3 6 Marking Rubric Your submissions will be marked according to the following rubric, with a maximum possible score of 15 points. Part marks (~1.5) Full marks (2.5) Login Can correctly login the user ‘Dave’ and validate his username and password. All valid users can be logged in successfully, and unsuccessful user logins should be rejected. View Patient List Correctly list all patients associated with user ‘Dave’ in the correct order (see Figure 2). Correctly list all patients associated with a user, in the correct order, for all possible username input from Figure 1. Find Patient Correctly list patients for keyword “wade” (see Figure 3). Correctly list patients for all possible keywords. Add Patient Can correctly add a patient (‘John Doe’, 21, ‘No symptoms’, ‘Dave’, ‘Drink plenty’) to the database. Can correctly add all valid patients to the database. Patients associated with an invalid doctor should be rejected. Update Patient Can correctly update the advice description given to a patient as shown in Figure 5. Can correctly update details of all patients, ensuring all assigned doctors are valid. Stored Procedure A stored procedure is correctly created in the submitted SQL file. A stored procedure is correctly created in the submitted SQL file, and is correctly called in one of the specified functions.
欢迎咨询51作业君
