ICT211 Database Design

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

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:

• 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 over written).

• All animals may have a name, and a picture associated. (Erica’s hint: look at ‘MEDUIMBLOB’ type for the image!)

• 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.