ICT701 Relational Database Systems

ICT701 Relational Database Systems

ICT701 Relational Database Systems

Task2

The value of this assessment is 30%.

Case study

Australia Zoo Wildlife Hospital (AZWH) is a charity organization or any other similar company that exists to treat and or cares for sick, injured or orphaned wildlife. They are brought animals from across South East Queensland, and beyond, and are re-known for their specialization in both Koalas and Sea Turtles.

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 is grouped into types which represent both their ‘taxon’ and a macro-level grouping within that type, these are different per type. E.g:

ICT701 Relational Database Systems

In addition to type, animals are sorted into ‘breeds’, of which there are nearly 100

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 overwritten).

All animals may have a name, and a picture associated. (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 design. 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 lossless, no data should be overwritten.

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 (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 specializations. 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 organizations, wildlife carers, vets, researchers, volunteers and the 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.

1. List the patient id, accession number, 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 details for Carer Groups with an expired permit.

Instruction

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 the Course Coordinator.

You must use MySQL to develop the database information. MS Access is not appropriate for any section of this assignment.

You must use the ER notation that was taught in ICT701. Penalties will apply to incorrect notations.

The database schema 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 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.

Challenge may face by students

Students face off various problems in completing this assessment such as understanding the complex problems of making references, lack of writing skills, portfolio etc. Students can take help and guidance from our technical experts and get better grades in their assessments.