CICT211 Database Design Task 2 Version 2 - 20th May Semester 1, 2016ICT211 Database Design Task 2 Page 2 of 6 Assessment and Submission Details Marks: 30% of the Total Assessment for the Course Due Date: 11:59pm SUNDAY, 5th June 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 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 exceptional circumstances.ICT211 Database Design Task 2 Page 3 of 6 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, 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 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 fill find an example of a blank Australia Zoo Wildlife Hospital Accession form, a list of conditions that the animals may suffer, a partially completed form. These will form the basis of your universe of discourse. Sample data will be made available in or before in week 11 of semester (this is to have you consider your design before implementation). 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 (more info to come in the sample data). • 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. • Wildlife are grouped into types which represent both their 'taxon' and a macro-level grouping within that type, these are different per type. E.g:ICT211 Database Design Task 2 Page 4 of 6 • 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 readmitted – all historical admissions should be maintained (and not over written). • All animals may have a name, and a picture associated. (Erica's hint: look at 'MEDUIMBLOB' type for the image! - also user TEXT for the notes on the accession form) • DNA samples may be taken from the animals and results recorded in the database. This should be stored with the date/time the sample was taken, the date/time it was entered into the system, and the results of the sample analysis (for the results use the TEXT datatype). • 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. • A set of wildlife carers are maintained by the system. These are persons who have animals released into their care after acute treatment, but before they are able to be released back into the wild. • 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 beICT211 Database Design Task 2 Page 5 of 6 maintained where possible using searchable text fields (Use the TEXT datatype). • 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. • Once the patient is improving it may be sent to a wildlife carer. Wildlife carers are part of carer groups. These groups must have a current permit, which has a permit number and an expiry date. The groups have particular specialisations. Within the groups, are a number of individual people that can be contacted. • AZWH maintains a contact list – they have other hospitals, other zoos/wildlife parks, government departments, other organisations, 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. User Reports For the purposes of your assignment you are to create queries for the following user reports. The queries should be in the main .sql file but separated by a comment showing which query it is. Eg. # Query 2.a.i You should include the query used on your database design to get that data, and the result of the query on the supplied data set. 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. Monthly report (this is multiple queries): a. list the total for all in-coming accessions in the previous calendar month grouped by i. Local government area ii. Cause of affliction b. List the total number of accessions for this month in the previous years. 3. List all Carer Groups with an expired permit. 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. You will be given the opportunity to visit the Hospital during the semester, however this is an extra-curricular activity, there is no requirement to attend, nor is there any disadvantage if you are not able to attend at the allocated time.ICT211 Database Design Task 2 Page 6 of 6 If you wish you can submit a draft design to Erica no later than Sunday of week 11 for formative feedback that may assist you with your final assignment. The database schema for your assignment should be submitted under an opensource 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 file. 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 For Part A you are to include a word document or PDF that contains: - ER Diagram - Relational Schema (including primary & foreign keys) - Supplementary design requirements (e.g. any information on length of identifiers, postcodes, names, what data attributes are compulsory, structure and or format of any columns etc.) - Assumptions For Part B you are to submit - A single plain text file, name _azwh.sql. In this file you are to include all the SQL for your implementation. This includes: o The License agreement as seen in Appendix E. o CREATE TABLE statements including all integrity constraints, and actions on update and delete o INSERT INTO statements for populating the database (if this must happen in a particular order then make sure you order it appropriately!) o SELECT statements for the required demonstration queries. Submission The completed assignment is to be submitted to Blackboard by the due date of 11:59pm SUNDAY, 5th June 2016. The assignment will be assessed according to the marking sheet. Late submission will be penalised according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late.ICT211 Database Design Task 2 Page 7 of 6 Appendix A Marking Sheet for ICT211 2016.S1 Assignment 2 Student name: Student ID: Items Maximum Marks Marks Obtained PART A: DESIGN (50 marks made up of) - ER Diagram (20 marks) o Completeness (participation & cardinality constraints & all relevant data represented) o Accuracy - Relational Schema & normalisation (20 marks) - Assumptions/Additional Information (10 marks) 50 PART B: IMPLEMENTATION - SQL STATEMENTS: o CREATE TABLE (15 marks) § Consider data duplication & appropriateness of table design o Integrity Constraints (5 marks) o INSERT Statements (10 Marks) o QUERIES – (20 marks total) 50 Total = 100 0.0 0.0 /30% OVERALL COMMENTS:ICT211 Database Design Task 2 Page 8 of 6 Appendix B – Blank Accession formA}PC asYS SrHr dci Smof,tno tvNU ilr; " I lSqtO t- toot u1", 1Cog ,{161 t"t '. - ", - leulg :::: O - a6ra1 t:t- a1b16s1 'tt' ,,.,,, .,,, ,.,. ],,, spo!\ teotdol -- spalj a{qe}3a[ul --' ,llu,i;tttt - - :r:,")l;lrf>:.:r;.'',i;r;uir;flpeiiji,iisi lsar 56e3 ttt -arcarls 5ues in dnoi6 e rjlrar 5oell '-t" par l el, .abuaralJrd .are3 :aieo oraqcau la/ rarBS olec oN I soA I a6v i,{6i5/u ias leujrus palearl oLl^A ueuBuualan A]NCI S-lrVIle sasrnu oEeul S3I.ON :CVIH1 71: , i:!: :' .lsrm slrlplrm p auo3sq ol slll no{ plnoif, ,, ,,,,, :.,, ,,., aJalm slrlplra poJalslooi P no,( oJV |euriue srqr asiaial oi no,( ttei ol atqp em a)y aleo a:n1eu6r5 aueN 'fuesseceu paLuoap ir posrueqlne {1eueu:nq aq /ieu lerxrue slqi }eq} areme ue I . '.ralm olrlplrM pe$lenb pue pa.relsroer B o1 ponolsue4 aq ,ieu lue[ed oq1 1uaul1ee4 0uun6 aoOB 6uol iAoH aleqryl isaA: oN : apslesrporuipsj uaaq IeuIruP seH ;Jjl ui ---r ,......'.....,-. Sojlro a$e - r3r{}o:l Ulnour ilal; ptoJ uO': oJlqs/eeJP l^06leml punoj$ uO .: tanott uf ,--i aou;l'ug l--:, i;;O ,l = jiuno ui ', lut leurtue srqi puri nolt pip'uorrenirs teqnn q.rnqns ifltaudec ul Euol /v\oH aupLr lBulruv (e,roqe sB ro) Snisei lJulue lo uonecoilcei: satssds s]tvL30'tvt{lNV iltuJ: ai!qcbs pailHpE a$rrl ouoqd ,uroH sder$py alPc s'ilvrlc uln3sSu J{",1: \ 'tvtid50H tillSlt&t 0z YnYUlsnv lig{sisgix ;ei ;il' 4. * w \-, ^=n.! \., '*\J L U38tilnNN0lSS333V $.ai ffi wffid ruffi$'ffisffi#ffiw ffid&reffiTgffi if eufue if qi oi piuaOOiq seq f aal noX op- leqmICT211 Database Design Task 2 Page 9 of 6 Appendix C – Accession Aetiology CategoriesUPDATED CONDITIONS LIST Date ...... .. ACC * ....................... . . Animai narne .......... .... . Species. Vet............. Updated outcome....... i"-li_.tnder vet care LlWith carer [-]ReleaseC t,-rOtner ilFungal - Aspergillus Lllungal Landt('jd fJFr:ngal - Chyirio' nFungal CryptocDCcus flFungal - other ............... fl Gastro-intestrnat dysbiosis []Gastro-iniesttrral - other.......... !Gastro-intestinal 3b;truction !Gastro.intestinal trauma nlnternaltacklE Dloint injury n Koala flu tr Leukaemia il Lorikeet paralysis syndrome n LYrn P6661, ! Mesothelioma nMetabolic bone Cisease I Multi-organ/system fail u re IMyelodysplasia nMyopathy !NAD INeoplasia - other................ DNeurological - other.....-......-... D Non-viable young nNutritional disorder - milk intoierance Eflurrltional Cisorder - other .. trOsteochondroma ilother I Papilloma il Paralysis......... -r lParasttes . l oxoplasma n Parasitic - Angiostrongylus ilParasitic - blood l_-lParasltrc - Lcccldra llParasitic - internal/intestinai IParasitic - Other,...,........... EPneurnonia - asDir3tion EPneumonia - bacterial !Pneumonia, cryptococcus !Pneumonia - fungal E Pneumonra - other/unknown n Poisoning n Possum derrnatitis n Pox I Reprod. Disease (not/unknown chlamydiosrs re lated ) XRenal disease nRenal disease - oxaiate nephrosis fJSarcorna/ca rcinoma nSepticaemia DSoft tissue injury nSpinal disease (ercl. fracture) ETraLrma - rnentbrane injury XTraurna - cther............... []Typhlocolitis {koala) lviral KoRV(?) ilViral - iyssavirus ilviral - PBFD r_-j \/i r r ts. ...,.. ......,j.................... ct he r... .,... Updaied Diagnqsis {anatomicall L. Reproductive 2. U rina ry traciT'kidney 3. CNS/neurologicai 4. Gl'f 5. Respiratory 6. Mrrsculoskeletai 7. Traurna - mLrltl^6rg3;1 8. Skinifeathers/scales 9. Meta bolic/nutritiona I 10. Sensory orgao 11. Ocular 12. Haematopoietic 13. Endocrine 14. NAt) Uodated Diaenosis (aetiglesv/specifi c) nAbscess trArDS(?) , DAlopecia DAmputarion EAnaemra - babesiosis []Anaen-ria - chronic disease IAnaemia - other ............. . l---1Anaemia - trypan osomiasrs i-lArthritis osteoarttrritis ilArthritis .septic EBacterial - Botulism f Bacte'ia - o1ner......... EBloat - ringtail possurrr IBurn........-....... nCardiac disorder ICataracts -Chtamydiosis - conjLnctrvrtrs EChlamydiosis - cystilis f Chlamydiosis - other............ CChlamydiosis - reprod. disease nChlamydiosis - respiratorV f Clearview test +ve nClearview test -ve DDeformity IDegloving injury n Dental/periodontal disease/injury E Dermatiti.s E Diabetes n Diarrhoea - bacterial E Diarrhoea - nutritional nDiarrhoea - other/undiagnosed .................."..... nDiarrhoea - yeast DEye in.1ury nEye - other nFibropapillonra itr,rtlei -Foreign body (ex,:i rnte,rrai tackle) f Foreign body inqi::ti,-rn iexci rar;kle) IFracture'beak lFracture - claviclc IFracture - coracc)r(i nFracture-1aw trFracture leg .lFracture otncr.. IFracture-peivis []Fracture ribs nFracture - scapr-ria flFracture - shell DFracture . sktrll [!Fracture- spine . lFractLrre ,ryrngICT211 Database Design Task 2 Page 10 of 6 Appendix D - Sample Partially Complete Accession FormWILDLIFE ACCESSION FORM ACCESSIONNUMBER 65105 - AIDE�{ LS _A�d _ _re��-----------------) ---- · ------- · ANIMAL DETAILS ______ ._______ �(v _ _C"�_i" captiyity? __ ·_________ ____________ �(��-I �<�t 3rea/s�ir�_ � In gutter D On road D Pets mo uth D Base of tree D In tree �" W°U"� D Other No . Yes, what? ___ ------------·-- ___.,__,____,._ ____ ·--·-.. -----�?" l