Database Design for Australia Zoo Wildlife Hospital

Database Design
Name
Institution

Introduction
This report presents an analysis, modelling, redesign and sample implementation of a system
used by Australia Zoo Wildlife Hospital (AZWH), a charity organization that exists to treat
and or care for sick, injured or orphaned wildlife. The first part of the report outlines an
analysis of the current system, which includes the major implementation flaws with the
current system, recommended improvements on the current design to eliminate data
redundancy, de-normalization to enhance reports retrieval, modification of some areas from
relational to NoSQL and rational for imposing additional constraints.
The second part of the assignment presents an implementation of the modified database
structure. The implementation is done on an SQL database and includes the data definition
language for creating the database and data manipulation. Additionally, part B also presents a
single python source file that outputs HTML pages for each procedure developed in the SQL
file.
Analysis of the Current System
Analysis of the current system reveals that the database design has a number of areas where
improvements can be done. To eliminate data duplication, some tables need to be normalized
further. For example, some postcodes and zip codes can be placed in a single address table, to
eliminate needs for repeating the data on every record that relates to a given address. Data
duplication is also observed in the diagnosis and treatment tables, where the veterinarian who
attends to an animal is directly recorded into the records. Creating a separate table for all
veterinarians will help normalize the tables further and eliminate duplication.
Data retrieval in the current database is likely to be slow as the design is not optimized for
querying and analytics. Enhancements to the design to enhance querying optimization would
require some sections to be changed to NoSQL design. By definition, a NoSQL database
provides a mechanism for storage and retrieval of data that is modelled in means other than
the tabular relations used in relational databases. NoSQL design provides fast, highly scalable
access to free-form data.
From the analysis of the current reports, the sections that requires modification to NoSQL
design are areas that relates to storage of data related to diagnosis and treatment. Storage of
such data in a NoSQL design will allow quick and efficient retrieval.

Among the many NoSQL database models, the most applicable model is the Wide-column
store model. With this model, storage of data is organized in form of columns instead of
rows. The approach enables fast and efficient data querying than convectional relational
models. This model has been applied in some of the most efficient database systems such as
HBase, Cassandra and Google BigTable.
Figure 1.0 Entity Relationship Diagram of the Current database structure.
The database is currently in the 3rd normal form. Denormalization of some tables will greatly
improve the reports generation as well as enhance retrieval of analytical information from the
data stored in the database. With the recommended use of Wide-column store model,
denormalization of the tables will basically create some data marts that are efficient for
information retrieval and even application of analytics on the data.

Figure 2.0 Modified ERD
The modification implemented aligns diagnosis and prescription, a feature that was lacking in
the initial database design. Details have also been moved from the diagnosis to animal table,
such as age, sex and weight. The aligning of diagnosis and treatment streamlines reports
generation.
A number of assumptions were taken in designing the proposed and the current ERD; for
tables where the primary key was not specified, it was assumed that an auto generated
surrogate key was always used to uniquely identify records in the given table. For example a
table like Taxon has Taxon_ID while Species table has Species_ID which are auto generated
and used as primary key. The keys are also indexed, speeding up searching through the data
tables.