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.
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,
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.
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Database Programming. Retrieved from https://myassignmenthelp.com/free-samples/ict320-database-programming/database-design.html.
"Database Programming." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/ict320-database-programming/database-design.html.
My Assignment Help (2021) Database Programming [Online]. Available from: https://myassignmenthelp.com/free-samples/ict320-database-programming/database-design.html
[Accessed 17 April 2021].
My Assignment Help. 'Database Programming' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/ict320-database-programming/database-design.html> accessed 17 April 2021.
My Assignment Help. Database Programming [Internet]. My Assignment Help. 2021 [cited 17 April 2021]. Available from: https://myassignmenthelp.com/free-samples/ict320-database-programming/database-design.html.
MyAssignmenthelp.com is the only dissertation service provider in Australia that provides cheap dissertation help while maintaining all quality standards. Hence, students, from various Australian cities prefer taking dissertation help from us. Sydney, Brisbane, Perth and Melbourne rank to when it comes to availing dissertation writing assistance from MyAssignmenthelp.com. Our dissertation writers, editors and experts work together to provide students highest quality dissertation help solutions. buy dissertation online from to acquire the final degree without any hassle.
Answer Introduction This report has been focused on developing a change logger script created in Ubuntu OS and managing of regular activity of user utilizing this system. Therefore, for creating shell script if the system, it is extracted from different shell command and output has been stored in the HTML file. IT would help in monitoring the system in a proper manner. The pre-installed command line tools for utilizing creation of shell scrip...
Read MoreAnswer: Introduction The continued adoption and usage of technology has led to the rise and growth of information security concerns. Controlling access to digital resources have become a critical concern to any enterprise. As such, organizations have invested heavily in ensuring that their digital resources are safe and protected to ensure that no unauthorized access is allowed. Companies have employed different authentication strategies and...
Read MoreAnswer: This particle paper is all about reviewing literature on Internet of things. The paper highlights the importance of Internet of Things along with focusing on protocol, issues of application and technologies. IOT has encountered development in various fields like RFID, communication technologies and smart sensors. The main notion of technology is all about smart sensor t...
Read MoreAnswer: A problem addressed The author is addressing the challenge of estimation of video QoE by use of QoS metrics by focusing on the understanding of ABR (adaptive bitrate) streaming. This is through evaluation of several metrics of QoE (Zhang, Wen, Chen & Khisti, 2013) by examining the rate of abandoning videos in YouTube, which happens whenever one closes the video when playing back either because of lack of importan...
Read MoreAnswer: Introduction Most of the companies are gradually shifting to newer technologies as well as information systems that greatly reduce the complexity of the tasks. The newer technologies will help in automating tasks and help managers take better and faster business decisions. In this report a company called Magellan Health, which is a healthcare company in the United States, will be studied in details and how they can come up with improv...
Read MoreJust share requirement and get customized Solution.
Orders
Overall Rating
Experts
Our writers make sure that all orders are submitted, prior to the deadline.
Using reliable plagiarism detection software, Turnitin.com.We only provide customized 100 percent original papers.
Feel free to contact our assignment writing services any time via phone, email or live chat. If you are unable to calculate word count online, ask our customer executives.
Our writers can provide you professional writing assistance on any subject at any level.
Our best price guarantee ensures that the features we offer cannot be matched by any of the competitors.
Get all your documents checked for plagiarism or duplicacy with us.
Get different kinds of essays typed in minutes with clicks.
Calculate your semester grades and cumulative GPa with our GPA Calculator.
Balance any chemical equation in minutes just by entering the formula.
Calculate the number of words and number of pages of all your academic documents.
Our Mission Client Satisfaction
It is a good source of help for any assignment. My order completed before the deadline, and rework have done for the assignment. It is quite expensive for the students. wishes for better deal regards roh
Australia
Great help and great service! I was concerned it wouldn't get to me on time, but sure enough, it was on time! This saved my rear. Thanks, you guys are awesome
Australia
Great paper. It was very detailed and everything was what was necessary for the paper.
Australia
seems ok to me, but once you forward the assignment it take 2 weeks for the feedback, then I will know if they are ok, so please do all the rest as well, if some answer are not ok they will ask to do it again
Australia