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.
Previous answers to this question
This is a preview of an assignment submitted on our website by a student. If you need help with this question or any assignment help, click on the order button below and get started. We guarantee authentic, quality, 100% plagiarism free work or your money back.
Get The Answer