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

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