Australia Zoo Wildlife Hospital (AZWH) is a charity organization that exists to treat and or care...

Question

Australia Zoo Wildlife Hospital (AZWH) is a charity organization that exists to treat and or care for sick, injured or orphaned wildlife. They are brought animals from across South East Queensland, and beyond, and are re-knowned for their specialization in both Koalas and Sea Turtles. As a charity that operates separately from the main Australia Zoo company, the Zoo runs with very little funds. As part of an ongoing agreement between University of the Sunshine Coast and Australia Zoo Wildlife Hospital, we are re-developing their database systems. Stage one is the Accession (admissions) system which stores information on who brought in the wildlife, where it was found, suspected injuries, initial triage and/or vet notes and what wildlife career if any the animal is assigned to for re-habilitation or care. Animals are brought for a large variety of reasons and sometimes multiple reasons, and the database is to record these and be able to query them. The Wildlife hospital can see up to XXXXXXXXXXadmissions per year, and there is a large database of information that is maintained both for their own record keeping and for regulatory requirements set down by the State and Federal Governments. One such regulatory requirement is that all Koalas treated in Queensland are given a unique QPWS (Queensland Parks and Wildlife Service) identifier that must be maintained. A monthly report is generated to give to QPWS on these koalas and their treatment. Other wildlife such as birds can be either transferred in or out of AZWL, and as such may have more than one ID that the database needs to be able to store, recall and query. In appendices of this document you fill find an example of a blank Australia Zoo Wildlife Hospital Accession form, a list of conditions that the animals may suffer, a partially completed form. These will form the basis of your universe of discourse. Sample data will be made available in or before in week 11 of semester (this is to have you consider your design before implementation).

Robert · Accepted Answer

/* Australia Zoo Wildlife Hospital Accession Database (c) by

Australia Zoo Wildlife Hospital Accession Database is licensed under a Creative Commons Attribution 4.0
International License. You should have received a copy of the license along with this work. If not, see
.
*/
 
1) Accession Table
CREATE TABLE `accession` (
  `accessID` int(11),
  `date_admit` date,
  `time_admit` time,
  `AdmitterID` int(11),
  `AdmitterPhone` int(11),
  `transfer_facility_id` int,
  `rescue_loc` varchar(50),
  `rescue_place` varchar(20),
  `rescue_condition` varchar(50),
  `captivitytime` timestamp,
  `afflicID` int(11),
  `diag_category` varchar(50),
  `triage_ID` int(11),
  `triage_note` varchar(50),
  `vet_id` int(11),
  `veterinarian_notes` varchar(50),
  `aetiologyID` int(11),
  `fed_medicines` char(1),
  `fed_notes` varchar(50),
  `fed_timestamp` timestamp,
  `Totalweight` float,
  `age` varchar(20),
  `maturity` varchar(20),
  `carer_id` int(11)
);
ALTER TABLE `accession`
  ADD PRIMARY KEY (`accessID`);
 
ALTER TABLE `accession`
  ADD FOREIGN KEY (`rescue_place`) REFERENCES `suburb` (`rescue_place`),
  ADD FOREIGN KEY (`afflicID`) REFERENCES `affliction` (`afflicID`),
  ADD FOREIGN KEY (`aetiologyID`) REFERENCES `aetiology` (`aetiologyID`),
  ADD FOREIGN KEY (`AdmitterID`) REFERENCES `rescuer` (`AdmitterID`),
  ADD FOREIGN KEY (`carer_id`) REFERENCES `carer` (`carer_id`);

Australia Zoo Wildlife Hospital (AZWH) is a charity organization that exists to treat and or care for sick, injured or orphaned wildlife. They are brought animals from across South East Queensland,...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment