Database Design and Implementation for AZWH: ICT320 Report

This report provides a comprehensive analysis, redesign, and implementation of a database system for the Australia Zoo Wildlife Hospital (AZWH). It begins with an analysis of the current system, identifying flaws such as data redundancy and slow retrieval, and proposes improvements including further normalization, denormalization for report generation, and the use of NoSQL for diagnosis and treatment data. The report then presents a modified Entity Relationship Diagram (ERD) and details the implementation of the redesigned database using SQL, including Data Definition Language (DDL) for creating tables and Data Manipulation Language (DML) for populating them. The implementation includes the creation of several tables like accession, admission, animal, carer, diagnosis, medicine, and treatment, among others, with appropriate constraints and relationships. Furthermore, the report includes the creation of two stored procedures to generate reports related to new carers and carer groups, demonstrating the database's practical application. The report addresses the need for efficient data retrieval and analytical capabilities within the AZWH system, incorporating best practices in database design.

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.

PART B: DML to create the database
CREATE DATABASE zoodatabase ;
USE zoodatabase;
/*Table structure for table accession */
CREATE TABLE accession (
Accession_No VARCHAR(15) NOT NULL,
Patient_ID INT(10) NOT NULL,
DATE DATE NOT NULL,
PRIMARY KEY (Accession_No),
KEY FKAccession (Patient_ID),
CONSTRAINT FKAccession FOREIGN KEY (Patient_ID) REFERENCES animal (Patient_ID)
) ;
/*Data for the table accession */
/*Table structure for table admission */
CREATE TABLE admission (
Admission_ID INT(10) NOT NULL AUTO_INCREMENT,
Patient_ID INT(10) NOT NULL,
DATE DATE NOT NULL,
Rescuer_ID INT(10) NOT NULL,
Location_Found INT(50) NOT NULL,
Exact_Location VARCHAR(50) NOT NULL,
Situation_Found VARCHAR(100) NOT NULL,
CaptivityPeriod VARCHAR(50) NOT NULL,
Cause_of_Affliction VARCHAR(50) NOT NULL,
Triage_Notes TEXT NOT NULL,
PRIMARY KEY (Admission_ID),
KEY FKAdmission (Patient_ID),
KEY FKAdmission2349121 (Location_Found),

KEY FKAdmission (Rescuer_ID),
CONSTRAINT FKAdmission FOREIGN KEY (Location_Found) REFERENCES location (Location_ID),
CONSTRAINT FKAdmission FOREIGN KEY (Rescuer_ID) REFERENCES rescuerer (Rescuer_ID),
CONSTRAINT FKAdmission FOREIGN KEY (Patient_ID) REFERENCES animal (Patient_ID)
) ;
/*Data for the table admission */
/*Table structure for table aetiology */
CREATE TABLE aetiology (
Aetiology_ID INT(10) NOT NULL AUTO_INCREMENT,
Aetiology VARCHAR(100) NOT NULL,
PRIMARY KEY (Aetiology_ID)
) ;
/*Data for the table aetiology */
/*Table structure for table animal */
CREATE TABLE animal (
Patient_ID INT(10) NOT NULL AUTO_INCREMENT,
Species_ID INT(10) NOT NULL,
NAME VARCHAR(100) NOT NULL,
age INT(10) NOT NULL,
sex VARCHAR(10) NOT NULL,
weight FLOAT NOT NULL,
PRIMARY KEY (Patient_ID),
KEY FKAnimal (Species_ID),
CONSTRAINT FKAnimal FOREIGN KEY (Species_ID) REFERENCES species (Species_ID)
) ;
/*Data for the table animal */
/*Table structure for table care_group */

CREATE TABLE care_group (
Group_ID INT(10) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
Permit_No INT(10) DEFAULT NULL,
PRIMARY KEY (Group_ID),
KEY PermitFK (Permit_No),
CONSTRAINT PermitFK FOREIGN KEY (Permit_No) REFERENCES permit_option (Permit_No)
) ;
/*Data for the table care_group */
/*Table structure for table cared_by */
CREATE TABLE cared_by (
Carer_ID INT(10) NOT NULL,
Patient_ID INT(10) NOT NULL,
Date_Out DATE NOT NULL,
Date_Back DATE NOT NULL,
KEY FKCared_By (Carer_ID),
KEY FKCared_By (Patient_ID),
CONSTRAINT FKCared_By FOREIGN KEY (Patient_ID) REFERENCES animal (Patient_ID),
CONSTRAINT FKCared_By FOREIGN KEY (Carer_ID) REFERENCES carer (Carer_ID)
) ;
/*Data for the table cared_by */
/*Table structure for table carer */
CREATE TABLE carer (
Carer_ID INT(10) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
Address VARCHAR(100) NOT NULL,
GroupGroup_ID INT(10) NOT NULL,

PRIMARY KEY (Carer_ID),
KEY FKCarer (GroupGroup_ID),
CONSTRAINT FKCarer FOREIGN KEY (GroupGroup_ID) REFERENCES care_group (Group_ID)
) ;
/*Data for the table carer */
/*Table structure for table diagnosis */
CREATE TABLE diagnosis (
Diagnosis_ID INT(10) NOT NULL AUTO_INCREMENT,
Aetiology_ID INT(10) NOT NULL,
Patient_ID INT(10) NOT NULL,
PRIMARY KEY (Diagnosis_ID),
KEY FKDiagnosis (Aetiology_ID),
KEY FKDiagnosis (Patient_ID),
CONSTRAINT FKDiagnosis FOREIGN KEY (Aetiology_ID) REFERENCES aetiology (Aetiology_ID),
CONSTRAINT FKDiagnosis FOREIGN KEY (Patient_ID) REFERENCES animal (Patient_ID)
) ;
/*Data for the table diagnosis */
/*Table structure for table location */
CREATE TABLE location (
Location_ID INT(10) NOT NULL AUTO_INCREMENT,
Subub VARCHAR(100) NOT NULL,
Local_Gov_Area VARCHAR(100) NOT NULL,
PRIMARY KEY (Location_ID)
) ;

/*Data for the table location */
/*Table structure for table medicine */
CREATE TABLE medicine (
Med_ID INT(10) NOT NULL AUTO_INCREMENT,
Medicine VARCHAR(60) NOT NULL,
Directions VARCHAR(30) NOT NULL,
Start_Date DATE NOT NULL,
Stop_Date DATE NOT NULL,
Treatment_ID INT(10) NOT NULL,
Accession_ID INT(10) NOT NULL,
Prescription_Date INT(10) NOT NULL,
PRIMARY KEY (Med_ID),
KEY FKMedicine (Treatment_ID,Accession_ID,Prescription_Date),
CONSTRAINT FKMedicine FOREIGN KEY (Treatment_ID, Accession_ID, Prescription_Date) REFERENCES
treatment (Treatment_ID, Accession_ID, Prescription_Date)
) ;
/*Data for the table medicine */
/*Table structure for table permit_option */
CREATE TABLE permit_option (
Permit_No INT(10) NOT NULL AUTO_INCREMENT,
Species_ID INT(10) NOT NULL,
Expirely_Date DATE DEFAULT NULL,
Contact_Person VARCHAR(50) DEFAULT NULL,
Contact_No VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (Permit_No),
KEY FKPermit_Opt (Species_ID),

CONSTRAINT FKPermit_Opt FOREIGN KEY (Species_ID) REFERENCES species (Species_ID)
) ;
/*Data for the table permit_option */
/*Table structure for table preferences */
CREATE TABLE preferences (
Carer2Carer_ID INT(10) NOT NULL,
Permit_No INT(10) NOT NULL,
KEY FKPreference (Carer2Carer_ID),
KEY FKPreference (Permit_No),
CONSTRAINT FKPreference FOREIGN KEY (Permit_No) REFERENCES permit_option (Permit_No),
CONSTRAINT FKPreference FOREIGN KEY (Carer2Carer_ID) REFERENCES carer (Carer_ID)
) ;
/*Data for the table preferences */
/*Table structure for table rescuerer */
CREATE TABLE rescuerer (
Rescuer_ID INT(10) NOT NULL AUTO_INCREMENT,
First_Name VARCHAR(50) NOT NULL,
Last_Name VARCHAR(50) NOT NULL,
Title VARCHAR(60) NOT NULL,
Email VARCHAR(70) NOT NULL,
Home_Phone VARCHAR(50) NOT NULL,
Mobile VARCHAR(15) NOT NULL,
RescuerType VARCHAR(30) NOT NULL,
Street VARCHAR(50) NOT NULL,
Suburb VARCHAR(50) NOT NULL,

State VARCHAR(60) NOT NULL,
Country VARCHAR(60) NOT NULL,
Postcode VARCHAR(10) NOT NULL,
PRIMARY KEY (Rescuer_ID)
) ;
/*Data for the table rescuerer */
/*Table structure for table species */
CREATE TABLE species (
Species_ID INT(10) NOT NULL AUTO_INCREMENT,
Species VARCHAR(70) NOT NULL,
Taxon2Taxon_ID INT(10) NOT NULL,
PRIMARY KEY (Species_ID),
KEY FKSpecies2590319 (Taxon2Taxon_ID),
CONSTRAINT FKSpecies2590319 FOREIGN KEY (Taxon2Taxon_ID) REFERENCES taxon (Taxon_ID)
) ;
/*Data for the table species */
/*Table structure for table tag */
CREATE TABLE tag (
Entry_ID INT(10) NOT NULL AUTO_INCREMENT,
Patient_ID INT(10) NOT NULL,
Tag_No VARCHAR(20) NOT NULL,
Tag_Type VARCHAR(30) NOT NULL,
PRIMARY KEY (Entry_ID),
KEY FKTag2758042 (Patient_ID),
CONSTRAINT FKTag2758042 FOREIGN KEY (Patient_ID) REFERENCES animal (Patient_ID)

) ;
/*Data for the table tag */
/*Table structure for table taxon */
CREATE TABLE taxon (
Taxon_ID INT(10) NOT NULL AUTO_INCREMENT,
Taxon VARCHAR(50) NOT NULL,
PRIMARY KEY (Taxon_ID)
) ;
/*Data for the table taxon */
/*Table structure for table treatment */
CREATE TABLE treatment (
Treatment_ID INT(10) NOT NULL,
Accession_ID INT(10) NOT NULL,
Prescription_Date INT(10) NOT NULL,
Treatment VARCHAR(100) NOT NULL,
Veterinarian_ID INT(10) NOT NULL,
Diagnosis_ID INT(10) NOT NULL,
Veterinarian2Veterinarian_ID INT(10) DEFAULT NULL,
PRIMARY KEY (Treatment_ID,Accession_ID,Prescription_Date),
KEY FKTreatment2993849 (Diagnosis_ID),
KEY FKTreatment2935337 (Veterinarian2Veterinarian_ID),
CONSTRAINT FKTreatment2935337 FOREIGN KEY (Veterinarian2Veterinarian_ID) REFERENCES veterinarian
(Veterinarian_ID),
CONSTRAINT FKTreatment2993849 FOREIGN KEY (Diagnosis_ID) REFERENCES diagnosis (Diagnosis_ID)
) ;

/*Data for the table treatment */
/*Table structure for table veterinarian */
CREATE TABLE veterinarian (
Veterinarian_ID INT(10) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (Veterinarian_ID)
);
/*Data for the table veterinarian */

PART B: Procedure 1: New Carer Report
DELIMITER //
CREATE PROCEDURE NewCarerReport()
BEGIN
SELECT
accession.Accession_NO AS "Accession ID",
animal.NAME AS "Animal Name",
species.Species, taxon.Taxon, carer.NAME AS Carer,cared_by.Date_Out AS "Transfer Date",
cared_by.Date_Back AS "Return Date",
(CASE WHEN (cared_by.Date_Out != '' AND cared_by.Date_Back = '')
THEN
DATEDIFF(NOW(),cared_by.Date_Out )
ELSE
DATEDIFF(cared_by.Date_Back,cared_by.Date_Out )
END) AS "Days with Carer"
FROM
cared_by
INNER JOIN carer
ON (cared_by.Carer_ID = carer.Carer_ID)
INNER JOIN animal
ON (cared_by.Patient_ID = animal.Patient_ID)
INNER JOIN accession
ON (accession.Patient_ID = animal.Patient_ID)
INNER JOIN species
ON (animal.Species_ID = species.Species_ID)
INNER JOIN taxon
ON (species.Taxon2Taxon_ID = taxon.Taxon_ID);
END //
DELIMITER ;

Procedure 2: New Carer Group report
DELIMITER //
CREATE PROCEDURE NewCarerGroupReport()
BEGIN
SELECT
care_group.Name, care_group.Permit_No,
permit_option.Expirely_Date,
permit_option.Contact_Person, permit_option.Contact_No
FROM care_group
INNER JOIN permit_option
ON (care_group.Permit_No = permit_option.Permit_No)
WHERE permit_option.Expirely_Date < DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
END //
DELIMITER ;
The procedure queries two tables for the details, then filters the records by only showing records whose expire
date is below current date plus one month.