The Australia Zoo Wildlife Hospital (AZWH) requirements are analyzed and the Australia Zoo Wildlife Hospital (AZWH) ER diagram is expressed. The ER diagram is provided below. The ER diagram is expressed in Chen’s notation.
ER Diagram:
The Relational Schema for the AZWH is provided below:
Contacts (ContactID, ContactType, First Name, Last Name, Title Salutation , Email , Phone Number, Address , Suburb , State , Country , Postcode );
Diagnosis Aetiology (Aetiology ID, Aetiology Category Name);
Local Council Area (Area ID, Area Name, Post Code);
Carer Group (Carer Group ID, Permit Number, Name, Expiry Date, Specialisations);
Carer (Carer ID, Carer Group ID , Name , Address , City , Post Code, Phone Number );
Wild Life Type (Wild Life Type ID , Macro Taxon);
Breed (Breed ID , Breed Name, Wild Life Type ID);
Patient ( Patient ID , Name , Picture, Koala Tag , Microchip No , Ear Tag Number , Alternate Tag ID , Breed ID);
Cause Of Affliction (Cause Of Affliction ID, Reason );
Accessions (Accession ID , Patient ID, Date Of Admission, Notes, Admitter Contact ID, Found Regional Area , Found Postal Code , Carer ID, Cause Of Affliction ID, Status );
Patient Aetiology List (Accession ID, Aetiology ID, Notes );
Medicine (Medicine ID , Medicine Name , Manufacturer , Cost float);
Patient Medicine(Accession ID, Medicine ID, Start Date, Stop Date);
Patient Treatment (Accession ID, Treatment Name , Notes );
DNA Results (Accession ID, Date Sample Taken, Date Result Entered, Sample Analysis Results );
The type of data stored by the attributes of the table is provided below
Contacts Table
|
Attribute Name |
Data Type |
|
Contact ID Contact Type First Name Last Name Title Salutation Email Phone Number Address Suburb State Country Postcode |
int VARCHAR(30) VARCHAR(30) VARCHAR(30) VARCHAR(30) VARCHAR(30) int(10) VARCHAR(30) VARCHAR(30) VARCHAR(30) int(10) |
DiagnosisAetiology Table
|
Attribute Name |
Data Type |
|
Aetiology ID AetiologyCategoryName |
int VARCHAR(30) |
LocalCouncilArea Table
|
Attribute Name |
Data Type |
|
Area ID AreaName PostCode |
int VARCHAR(30 int(10) |
CarerGroup Table
|
Attribute Name |
Data Type |
|
Carer Group ID Permit Number Name Expiry Date Specialisations |
int VARCHAR(10) VARCHAR(30) Date VARCHAR(100) |
Carer Table
|
Attribute Name |
Data Type |
|
CarerID Carer Group ID Name Address City PostCodePhone Number |
int int VARCHAR(30) VARCHAR(30) VARCHAR(30) Int int(10) |
WildLifeType Table:
|
Attribute Name |
Data Type |
|
Wild Life Type ID Macro Taxon |
Int VARCHAR(50) |
Breed Table:
|
Attribute Name |
Data Type |
|
BreedID BreedName WildLifeTypeID |
int VARCHAR(30) int |
Patient Table:
|
Attribute Name |
Data Type |
|
PatientID Name Picture KoalaTag MicrochipNo EarTagNumber AlternateTagID BreedID |
int VARCHAR(30) BLOB VARCHAR(30) VARCHAR(30) VARCHAR(30) VARCHAR(30) int |
CauseOfAffliction Table:
|
Attribute Name |
Data Type |
|
CauseOfAfflictionID Reason |
int VARCHAR(30) |
Accessions Table:
|
Attribute Name |
Data Type |
|
AccessionID PatientID DateOfAdmission Notes AdmitterContactID FoundRegionalArea FoundPostalCode CarerID CauseOfAfflictionID Status |
int int, date VARCHAR(200), int VARCHAR(30), int(10) int int VARCHAR(30) |
PatientAetiologyList Table:
|
Attribute Name |
Data Type |
|
AccessionID AetiologyID Notes |
int int VARCHAR(100) |
Medicine Table:
|
Attribute Name |
Data Type |
|
MedicineID MedicineName Manufacturer Cost |
int VARCHAR(30), VARCHAR(30), float |
PatientMedicine Table:
|
Attribute Name |
Data Type |
|
AccessionID MedicineID StartDate StopDate |
int int date date |
PatientTreatment Table:
|
Attribute Name |
Data Type |
|
AccessionID TreatmentName Notes |
int, VARCHAR(30) VARCHAR(30) |
DNAResults Table:
|
Attribute Name |
Data Type |
|
AccessionID DateSampleTaken DateResultEntered SampleAnalysisResults |
int date date VARCHAR(100) |
drop database hospital;
create database hospital;
use hospital;
Table Creation:
create table Contacts(ContactID int PRIMARY KEY, ContactType VARCHAR(30), FirstName VARCHAR(30), LastName VARCHAR(30), TitleSalutation VARCHAR(30), Email VARCHAR(30), PhoneNumber int(10), Address VARCHAR(30), Suburb VARCHAR(30), State VARCHAR(30), Country VARCHAR(30), Postcode int(10));
create table DiagnosisAetiology(AetiologyID int PRIMARY KEY, AetiologyCategoryName VARCHAR(30));
create table LocalCouncilArea(AreaID int PRIMARY KEY, AreaName VARCHAR(30), PostCode int(10));
create table CarerGroup( CarerGroupID int PRIMARY KEY, PermitNumber VARCHAR(10) , Name VARCHAR(30), ExpiryDate Date, Specialisations VARCHAR(100));
create table Carer( CarerID int PRIMARY KEY, CarerGroupID int, Name VARCHAR(30), Address VARCHAR(30), City VARCHAR(30), PostCode int, PhoneNumber int(10));
create table WildLifeType(WildLifeTypeID int PRIMARY KEY, MacroTaxon VARCHAR(50));
create table Breed( BreedID int PRIMARY KEY, BreedName VARCHAR(30), WildLifeTypeID int);
create table Patient( PatientID int PRIMARY KEY, Name VARCHAR(30), Picture BLOB, KoalaTag VARCHAR(30), MicrochipNo VARCHAR(30), EarTagNumber VARCHAR(30), AlternateTagID VARCHAR(30), BreedID int);
create table CauseOfAffliction(CauseOfAfflictionID int PRIMARY KEY, Reason VARCHAR(30));
create table Accessions( AccessionID int PRIMARY KEY, PatientID int, DateOfAdmission date, Notes VARCHAR(200), AdmitterContactID int, FoundRegionalArea VARCHAR(30), FoundPostalCode int(10), CarerID int, CauseOfAfflictionID int, Status VARCHAR(30));
create table PatientAetiologyList (AccessionID int, AetiologyID int, Notes VARCHAR(100));
create table Medicine(MedicineID int PRIMARY KEY, MedicineName VARCHAR(30), Manufacturer VARCHAR(30), Cost float);
create table PatientMedicine(AccessionID int, MedicineID int, StartDate date, StopDate date);
create table PatientTreatment(AccessionID int, TreatmentName VARCHAR(30), Notes VARCHAR(30));
create table DNAResults(AccessionID int, DateSampleTaken date, DateResultEntered date, SampleAnalysisResults VARCHAR(100));
insert into Contacts values(1,”Person”,”Lary”,”Willian”,””,”[email protected]“,23564646,”21 Kate Road”,”Greenwills”,”QLD”,”US”,424244);
insert into Contacts values(2,”Hospital”,””,””,”australian hospital”, “[email protected]“, 3423523, “21 William Road”,”Greentrend”,”California”,”AUS”,444242);
insert into DiagnosisAetiology values(1,”Abcess”);
insert into DiagnosisAetiology values(3,”AIDS”);
insert into DiagnosisAetiology values(5,”Alopecia”);
insert into DiagnosisAetiology values(7,”Amputation”);
insert into DiagnosisAetiology values(9,”Anaemia – babesiosis”);
insert into DiagnosisAetiology values(11,”Anaemia – chronic disease”);
insert into DiagnosisAetiology values(13,”Anaemia – other”);
insert into DiagnosisAetiology values(15,”Anaemia – parasitism”);
insert into DiagnosisAetiology values(17,”Anaemia – Rodenticide toxicity”);
insert into DiagnosisAetiology values(19,”Anaemia – trypanosomiasis”);
insert into LocalCouncilArea values (1,”ACKLAND”, 3214);
insert into LocalCouncilArea values(2,”ACLAND”,23423);
insert into LocalCouncilArea values(3,”ALDERSHOT”,4353);
insert into LocalCouncilArea values(4,”ALSTONVILLE”,4234);
insert into LocalCouncilArea values(5,”BOKARINA”,2342);
insert into CarerGroup values(1,”WIRP01762310″,”Special Permit – Koalas”,2013-05-16,”Ipswich Koala Protection Society Inc”);
insert into CarerGroup values(2,”WIRP01762310″,”Special Permit – Monotremes”,2013-05-16,”Ipswich Koala Protection Society Inc”);
insert into CarerGroup values(3,”WIRP02854014″,”General Carers Permit”,2017-03-23,”Fauna Rescue Whitsundays Association”);
insert into CarerGroup values(4,”WIRP02854014″,”Special Permit – Koalas”,2017-03-23,”Fauna Rescue Whitsundays Association”);
insert into CarerGroup values(5,”WIRP05472111″,”General Carers Permit”,2014-07-19,”Wildcare”);
insert into CarerGroup values(6,”WIRP05472111″,”Special Permit – Koalas”,2014-07-19,”Wildcare”);
insert into CarerGroup values(7,”WIRP05472111″,”Special Permit – Monotremes”,2014-07-19,”Wildcare”);
insert into CarerGroup values(8,”WIRP07193413″,”General Carers Permit”,2015-06-30,”FAU”);
insert into CarerGroup values(9,”NAWIRP07193413″,”Special Permit – Koalas”,2015-06-30,”FAU”);
insert into CarerGroup values(10,”NAWIRP07471713″,”General Carers Permit”,2016-07-22,”Pine Rivers Koala Care Assoc Inc”);
insert into CarerGroup values(11,”WIRP10066711″,”General Carers Permit”,2014-10-16,”ONARR”);
insert into CarerGroup values(12,”WIRP10821212″,”General Carers Permit”,2015-02-22,”Gladstone & District Wildlife Carers Assoc”);
insert into CarerGroup values(13,”WIRP11720012″,”Special Permit – Koalas”,2015-08-23,”Ipswich Koala Protection Society Inc(Koalas)” );
insert into Carer values(1,2,”Jesse”,”2a Mary St”,”BATTERY HILL”,1234,988873423);
insert into Carer values(2,3,”Lewis”,”23a Mary St”,”NANANGO”,43453,42345552);
insert into Carer values(3,4,”Macropods”,”12a Mary St”,”BATTERY HILL”,53424,23252525);
insert into Carer values(4,6,”Mary”,”4a Mary St” ,”NANANGO”,3423,2525252);
insert into Carer values(5,11,”Katherine”,”34d Mary St”,”BATTERY HILL”,4324,632433242);
insert into Carer values(6,10,”Jesse”,”32a Mary St”,”MOUNT MELLUM”,34123,2563534534);
insert into Carer values(7, 13, “Cleminson”, “10a Mary St”, “NANANGO”, 54532, 3523424325);
insert into WildLifeType values(1,”Reptile – Snake – Terrestrial”);
insert into WildLifeType values(2,”Avian”);
insert into WildLifeType values(3,”Domestic/Feral/Exotic”);
insert into WildLifeType values(4,”Agile Wallaby,Marsupial – Macropod”);
insert into WildLifeType values(5,”Alexandrine Parrot,Avian”);
insert into WildLifeType values(6,”Domestic/Feral/Exotic”);
insert into WildLifeType values(7,”Anomalopus Verreauxii,Reptile – Lizard”);
insert into Breed values(1,”African Grey Parrot”,4);
insert into Breed values(2,”Agile Wallaby”,3);
insert into Breed values(3,”Alexandrine Parrot”,2);
insert into Breed values(4,”Anomalopus Verreauxii”,5);
insert into Breed values(5,”Antarctic Prion”,6);
insert into Breed values(6,”Arctic Jaeger”,6);
insert into Breed values(7,”Asian House Gecko”,1);
insert into Breed values(8,”Asian Painted Frog”,2);
insert into Patient values(1,”Kate”,””,”UYIY7567″,”65567345″,”JGJ6363″,”FGD4535″,3);
insert into Patient values (2,”William”,””,”UTU6574″,”65342567345″,”JGHDG63″,”RWER4535″,2);
insert into Patient values(3,”John”,””,”UYIY234″,”6556734635″,”DASJGJ6363″,”FEFS35″,6);
insert into Patient values(4,”Tom”,””,”YGJG7567″,”653345″,”JGJ53535″,”FRGF435567″,8);
insert into CauseOfAffliction values(1,”Adhesive/Sticky substance”);
insert into CauseOfAffliction values(2,”Boat Strike”);
insert into CauseOfAffliction values(3,”Cat Attack”);
insert into CauseOfAffliction values(4,”Dog Attack”);
insert into CauseOfAffliction values(5,”Drowning”);
insert into CauseOfAffliction values(6,”Fell out of tree”);
insert into Accessions values(1,5,2014-02-01, “”, 1, “California”,2342,1,4,”Sent to Carer”);
insert into Accessions values(2,2,2015-03-01, “Treatment to be provided”, 1, “New Jersy”,2342,2,4,”In hospital”);
insert into Accessions values(3,1,2015-04-01, “Bone mistake”, 2, “London”,4234,6,2,”Sent to Wild”);
insert into Accessions values(4,3,2016-09-01, “”, 2, “California”,2342,3,4,”Sent to Wild”);
insert into Accessions values(5,4,2016-10-01, “”, 1, “London”,4353,2,6,”Sent to Carer”);
insert into PatientAetiologyList values(1,13,””);
insert into PatientAetiologyList values(2,1,”Treatment to be provided”);
insert into PatientAetiologyList values(3,3,””);
insert into PatientAetiologyList values(4,11,””);
insert into PatientAetiologyList values(5,15,””);
insert into Medicine values(1,”Vicks”,”Carnick Limited”,23);
insert into Medicine values(2,”Crocin”,”Kate Limited”,34);
insert into Medicine values(3,”Pexomox”,”Tacker Limited”,45);
insert into Medicine values(4,”Hasper”,”Hasperk Limited”,67);
insert into PatientMedicine values(1,3,2012-08-12,2017-07-12);
insert into PatientMedicine values(2,4,2015-06-12,2015-08-12);
insert into PatientMedicine values(3,2,2015-06-12,2016-06-02);
insert into PatientMedicine values(4,1,2016-06-12,2017-06-22);
insert into PatientTreatment values(1,”Surgery”,”Successful Treatment”);
insert into PatientTreatment values(2,”Treatment “,””);
insert into PatientTreatment values(3,”Surgery”,””);
insert into PatientTreatment values(4,”Surgery”,”Treatment”);
insert into DNAResults values(1,2012-02-22,2012-04-12,”Correct Result is published”);
insert into DNAResults values(2,2013-04-12,2014-04-12,”Result is published”);
insert into DNAResults values(3,2015-02-22,2016-04-12,””);
insert into DNAResults values(3,2015-08-22,2015-09-16,”Result is published”);
insert into DNAResults values(4,2016-02-22,2016-04-22,””);
User Reports:
You should include the query used on your database design to get that data.
1. List the patient id, accession number, animal name, and breed for all animals, sorted by animal type, that are currently being treated (where they have not been released, or sent to a carer or other facility).
Query:
select Patient.PatientID, Accessions.AccessionID , Patient.Name, Breed.BreedName from Patient inner join Accessions on Patient.PatientID= Accessions.PatientID inner join Breed on Breed.BreedID=Patient.BreedID where Accessions.Status like “In hospital” order by
Breed.WildLifeTypeID;
2. Monthly report (this is multiple queries):
a. list the total for all in-coming accessions in the previous calendar month grouped by
i. Local government area
Query:
select FoundRegionalArea, count(*) AS AccessionCount from Accessions where Month(DateOfAdmission)= MONTH(CURRENT_DATE – INTERVAL 1 MONTH) and YEAR(DateOfAdmission)= YEAR(CURRENT_DATE – INTERVAL 1 MONTH) group by FoundRegionalArea;
Query:
select CauseOfAffliction.CauseOfAfflictionID,Reason, count(*) AS AccessionCount from Accessions inner join CauseOfAffliction on CauseOfAffliction.CauseOfAfflictionID=Accessions.CauseOfAfflictionID where Month(DateOfAdmission)= MONTH(CURRENT_DATE – INTERVAL 1 MONTH) and YEAR(DateOfAdmission)= YEAR(CURRENT_DATE – INTERVAL 1 MONTH) group by CauseOfAffliction.CauseOfAfflictionID,Reason;
Query:
select count(*) from Accessions where Month(DateOfAdmission) = Month(NOW()) and year(DateOfAdmission) < year(NOW());
3. List all details for Carer Groups with an expired permit
Query:
Select * from CarerGroup where ExpiryDate < DATE(NOW());
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download