ICT320 Database Programming Task 2 Version 3 Semester 2, 2016 Page 1 of 13 ICT320 Database Programming Task 2 Page 2 of 13 Assessment and Submission Details Marks: 30% of the Total Assessment for the Course Due Date: 11:59pm SUNDAY 6 th November 2016 Submit your assignment to the link under Assessment->Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided. The assignment will be marked out of a total of 100 marks and forms 30% of the total assessment for the course. ALL assignments will be checked for plagiarism by SafeAssign system provided by Blackboard automatically. Refer to your Course Outline or the Course Web Site for a copy of the "Student Misconduct, Plagiarism and Collusion" guidelines. Assignment submission extensions will only be made using the official Faculty of Arts, Business & Law, University of the Sunshine Coast Guidelines. Requests for an extension to an assignment MUST be made to the course coordinator prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptionalcircumstances. ICT320 Database Programming Task 2 Page 3 of 13 Background Australia Zoo Wildlife Hospital (AZWH) is a charity organization that exists to treat and or care for sick, injured or orphaned wildlife. They are brought animals from across South East Queensland, and beyond, and are re-knowned for their specialization in both Koalas and Sea Turtles. As a charity that operates separately from the main Australia Zoo company, the Zoo runs with very little funds. As part of an ongoing agreement between University of the Sunshine Coast and Australia Zoo Wildlife Hospital (AZWH), we are re-developing their database systems. Stage one is the Accession (admissions) system which stores information on who brought in the wildlife, where it was found, suspected injuries, initial triage and/or vet notes and what wildlife career if any the animal is assigned to for re-habilitation or care. Animals are brought for a large variety of reasons and sometimes multiple reasons, and the database is to record these and be able to query them. The Wildlife hospital can see up to 6000-8000 admissions per year, and there is a large database of information (over 65000 entries) that is maintained both for their own record keeping and for regulatory requirements set down by the State and Federal Governments. One such regulatory requirement is that all Koalas treated in Queensland are given a unique QPWS (Queensland Parks and Wildlife Service) identifier that must be maintained. A monthly report is generated to give to QPWS on these koalas and their treatment. Other wildlife such as birds can be either transferred in or out of AZWL, and as such may have more than one ID that the database needs to be able to store, recall and query. In appendices of this document you will find an example of a monthly report that currently takes up to 1 business day to prepare, a blank Australia Zoo Wildlife Hospital Accession form and a partially completed form. These will form the basis of your universe of discourse. AZWH have a system in place, however it suffers from a very large number of issues and is far from efficient for them to use. The more time it takes them to enter their data, the less time and resources they can devote to saving wildlife. Your task is to look at the supplied database schema and propose, justify and implement improvements to this system to make it more efficient. You will need to identify and write the SQL DDL to add foreign key and uniqueness constraints, identify columns needing indexes and create those indexes, propose, with justification, re-design of tables to remove repeated data or for de-normalization for optimisation (implementation is not required), implement stored procedures for the identified common queries and implement part of the monthly report as python files that export to .csv format. ICT320 Database Programming Task 2 Page 4 of 13 System Requirements These requirements and sample reports are provided to assist in you in gaining an understanding of the existing system and the hospital's needs. You do not need to implement these requirements nor all the reports. User Requirements - Every patient admitted has a unique patient id. If the patient is a koala, it will have also have a koala tag. It may have a microchip. Animals including, but not limited to, wallabies, kangaroos, and possums may have ear tags in one or both ears that uniquely identify them (The tags should have the same number but should be able to tell if one is missing). Turtles may also have a tag. Not all tag number formats will be the same - In addition to formal tags, some animals will have one or more alternate identifiers, being either a Queensland Parks and Wildlife identifier, or transfer from or to another facility such as Currumbin Wildlife Hospital, RSPCA, or Australia Zoo, these must all be maintained and searchable. - In addition to type, animals are sorted into 'breeds', of which there are nearly 1000 in the current system. Each breed must be associated with exactly one'type'. - All animal wildlife may be admitted more than once, if they are re-admitted their previous patient number should be re-used, along with the date they were re- admitted – all historical admissions should be maintained (and not overwritten). - The database needs to record who brought in the animal, where it was found, including the regional or local council area it was found it – reports are generated for particular councils upon request. There should be a link between the postcode that the animal was found in and the local council it belongs to. - The system should be loss-less, no data should be over written. - Aetiology is the term used to describe the diagnosis categories for the wildlife. Animals can and will present with more than one aetiology. In addition, animals may be diagnosed with multiple diagnoses within a category – e.g. an animal may have multiple broken bones/anatomical issues. - During treatment, the vets will put notes on the forms, this information should be maintained where possible using searchable text fields - A wildlife patient can be assigned a treatment, this could be multiple medicines, or particular surgery or other actions. For medicine, the system should allow the start and stop date of each medicine/treatment. A treatment will be uniquely identified for patient, accession, and date it was prescribed. - AZWH maintains a contact list – they have other hospitals, other zoos/wildlife parks, government departments, other organizations, wildlife carers, vets, researchers, volunteers and general public that have brought in a patient. For all contacts, AZWH maintains, their first name, last name, title/salutation, email, phone number(s), street address, suburb, state, country, postcode, and what sort of contact they are. ICT320 Database Programming Task 2 Page 5 of 13 User Reports 1. List the patient id, accession id, animal name, and breed for all animals, sorted by animal type, that are currently being treated (where they have not been released, or sent to a carer or other facility). 2. List all animals with a microchip, Qld Parks and Wildlife Id or ear tag within a given time period (ie. A month or year) 3. Monthly report (this is multiple queries): a. list the total for all in-coming accessions in the previous calendar month grouped by i. Diagnosis/Aetiology ii. Taxon group iii. Local government area iv. Cause of affliction b. List the total number of accessions for this month in the previous years. c. List the total number of accessions for each month in the previous 12 months. 4. List all Koalas in 2009 that were treated for Chlamydial Conjunctivitis Specific Instructions You are not to contact the hospital directly as this takes valuable resources away from treating the wildlife. All client communication is to be directed through Course Coordinator Dr Erica Mealy. All SQL Code for your assignment should be submitted under an open- source royalty free license, this allows you to use the database in your portfolio when you are seeking work as well as allowing for further development of the database for AZWH. The license we have selected is CC-BY 4.0. Please include the comment text in Appendix E at the start of your .sql & .py files. Please note that all data is copyright and owned by Australia Zoo Wildlife Hospital and is used with their permission for the purposes of this assignment. Further distribution of this data is not permitted. Submission Format and Requirements For Part A you are to include an analysis of the current system's design in a word document or PDF. You should include: - Identification of major implementation flaws in the existing system, and/or areas for improvement, including o Removing repeated data, o De-normalization for optimization o changing some areas to NoSQL (if so include what type of NoSQL database). o Rational for the creation of constraints (Foreign Key and Unique) and indexes - Identification of the current Normal Form of the system. ICT320 Database Programming Task 2 Page 6 of 13 For Part B you are to submit - A single plain text file, named _ict320_azwh.sql. In this file you are to include all the SQL for your database modification. This includes: o The License agreement as seen in Appendix E with your name as author o ALTER TABLE CREATE CONSTRAINT commands for • the missing Foreign Keys • UNIQUEness constraints. o CREATE INDEXs for the appropriate indexes for optimizing the database for the queries listed in User Reports. o CREATE PROCEDUREs for • User report 3.a.i: Monthly report: list the total for all in- coming accessions in a given calendar month (and year) grouped by • Taxon Group (aka Patient's type) • User report 3.a.iii: Monthly report: list the total for all in- coming accessions a given calendar month (and year) grouped by • Local government area • User report 3.c.: List the total number of accessions for each month in the previous 12 months from a supplied calendar month & year. - A single plain text file named _ict320_azwh.py. In this file you are to include the Python for your programming implementation. This includes: o The License agreement as seen in Appendix E. o Code for the Monthly report – Taxon Group section o Code for the Monthly report – Local Gov't Area section o Code for the Monthly report – Monthly Comparisons section Submission The completed assignment is to be submitted to Blackboard by the due date of 11:59pm SUNDAY 6th November, 2016. The assignment will be assessed according to the marking sheet. Late submission will be penalized according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late. ICT320 Database Programming Task 2 Page 7 of 13 Appendix A Marking Sheet for ICT320 2016.S2 Task 2 Student name: Student ID: Items Maximum Marks Marks Obtained PART A (30 marks) - Justification for index and constraint selection - Identification of major implementation flaws and/or areas for improvement, including • Identification of & proposal to remove repeated data • Potential areas of NoSQL (if so include what type of NoSQL database). • Identification of current Normal Form and rationale for denormalization 30 PART B: IMPLEMENTATION(70 marks) - SQL STATEMENTS: (40 marks) o Constraints (Foreign Keys & Unique) o Indexes o Stored Procedures - Python Code (30 marks) o Monthly report - Diagnosis (Aetiology) o Monthly report – Local Gov't Area o Monthly report – Monthly Comparisons 70 Total = 100 /30% OVERALL COMMENTS: ICT320 Database Programming Task 2 Page 8 of 13 Appendix B – Sample Monthly Report ICT320 Database Programming Task 2 Page 9 of 13 Appendix C – Blank Accession form A}PC tvNU Smof,tno dci SrHr asYS ilr; " I lSqtO t- toot ,{161 1Cog ", u1 t"t '. - ", - leulg - O :::: a6ra1 t:t- a1b16s1 'tt' ],,, ,.,. .,,, ,,.,,, teotdol spo!\ -- --' a{qe}3a[ul spalj ,llu,i;tttt - :r:,")l;lrf>:.:r;.'',i;r;uir;flpeiiji,iisi - 56e3 lsar -arcarls ttt 5ues dnoi6 in '-t" 5oell rjlrar e par el, l .are3 .abuaralJrd :aieo oraqcau la/ oN olec rarBS I soA I ias i,{6i5/u a6v leujrus ueuBuualan oLl^A palearl S-lrVIle A]NCI S3I.ON oEeul sasrnu :CVIH1 71: :' i:!: , slrlplrm .lsrm plnoif, no{ slll ol auo3sq p ,,., :.,, ,,,,, ,, slrlplra aJalm oJV no,( P poJalslooi ol ttei no,( oi asiaial srqr |euriue a)y em atqp aueN a:n1eu6r5 aleo paLuoap 'fuesseceu ir /ieu aq {1eueu:nq posrueqlne I ue areme }eq} slqi lerxrue . '.ralm ,ieu aq ponolsue4 o1 B pa.relsroer pue pe$lenb olrlplrM 0uun6 1uaul1ee4 oq1 lue[ed 6uol aoOB aleqryl iAoH isaA: oN : IeuIruP uaaq apslesrporuipsj seH ui ;Jjl ---r ,......'.....,-. - a$e Sojlro r3r{}o:l Ulnour ilal; uO': ptoJ oJlqs/eeJP l^06leml uO punoj$ uf tanott .: aou;l'ug ,--i i;;O l--:, =,l jiuno ui srqi leurtue lut ', pip'uorrenirs nolt puri teqnn q.rnqns Euol ul ifltaudec lBulruv aupLr