For Part A you are to include an analysis of the current sub-system’s design in a word document or PDF. You should include:
Identification of major implementation flaws in the existing system, and/or areas for improvement, including (but not limited to)
o Removing repeated data,
o De-normalization for optimization
o changing some areas to NoSQL (if so include what type of NoSQL database).
o Rational for the creation of each specific constraint (Foreign Key and Unique) and/or index.
Identification of the current Normal Form of the system.
An ER Diagram for the current design of the system, and an ER Diagram for the redesigned system.
For animals still with a carer: it should show when they were transferred to the carer and total time with the carer to date
For animals that returned to the hospital in the last 7days: the date returned and the total time with carer.
Current System Analysis
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 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.
Implementation of Modified Database Structure
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
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,
Data Duplication and Normalization
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 */
Querying and Analytics
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.
