ICT701 Relational Database Systems Task 2: AZWH Database

This assignment provides a comprehensive relational database solution for the Australia Zoo Wildlife Hospital (AZWH), encompassing database creation, table design, data insertion, and query implementation. The solution begins by creating a database named 'AustraliaZooWildlifeHospital' and proceeds to define various tables such as BREAD_TYPE, AFFICITION, ANIMAL_INFO, PATIENT_INFO, PATIENT_DNA, RESCUER_INFO, CONTACTS, CARER_GROUP, CARER_INFO, TREATMENT, ACCESSION, and UPDATE_CONTACT, each with specific attributes and primary/foreign key relationships. The assignment further includes the insertion of sample data into these tables, followed by the implementation of SQL queries to retrieve and analyze the stored information. The queries focus on various aspects of the AZWH operations, including patient details, accession information, animal types, treatment records, carer details, and date-based filtering. This assignment is designed to demonstrate a solid understanding of relational database concepts and SQL proficiency. Desklib provides more solved assignments and resources for students.

/*--------------------------CREATE DATABASE -----------------------*/
CREATE DATABASE AustraliaZooWildlifeHospital;
/*---------------------------CREATE TABLES------------------------*/
/*----------------------BRAED_TYPE TABLES------------------------*/
CREATE TABLE BREAD_TYPE
(
BREAD_ID INT(10) NOT NULL,
BREAD_TYPE VARCHAR(150),
BREAD_NAME VARCHAR(150),
PRIMARY KEY (BREAD_ID)
)
;
/*-------------------AFFICITION TABLES------------------------*/
CREATE TABLE AFFICITION
(
AFFICITION_NO INT(10) NOT NULL,
AFFICITION_NAME VARCHAR(150),
PRIMARY KEY (AFFICITION_NO)
)
;
/*-------------------ANIMAL_INFO TABLES------------------------*/

CREATE TABLE ANIMAL_INFO
(
ANIMAL_ID VARCHAR(10) NOT NULL,
ANIMAL_TYPE VARCHAR(50) NOT NULL,
ANIMAL_NAME VARCHAR(50) NOT NULL,
RESCUER_LOCATION VARCHAR(50),
SUBURB VARCHAR(50),
LOCATION_GOV_AREA_SHIRE VARCHAR(50) NOT NULL,
ANIMAL_SITUATION VARCHAR(100),
HAPPENED VARCHAR(100),
AFFICITION_NO INT(10) NOT NULL,
FED VARCHAR(50),
MEDICATED VARCHAR(50) NOT NULL,
HOW_LONG_AGO VARCHAR(20),
POSTCODE INT(10) NOT NULL,
PRIMARY KEY (ANIMAL_ID),
FOREIGN KEY (AFFICITION_NO) REFERENCES AFFICITION(AFFICITION_NO)
)
;
/*-----------------------PATIENT_INFO TABLES------------------------*/
CREATE TABLE PATIENT_INFO
(
PATIENT_ID VARCHAR(10) NOT NULL,
ANIMAL_ID VARCHAR(10) NOT NULL,

BREAD_ID INT(10) NOT NULL,
KOALATAG VARCHAR (10),
MICROSHIP VARCHAR (10),
LEFTEARTAGE VARCHAR(10),
RIGHTEARTAG VARCHAR(10),
TURTLESTAG VARCHAR(10),
IDENTIFICATION VARCHAR (255),
PRIMARY KEY (PATIENT_ID),
FOREIGN KEY (ANIMAL_ID) REFERENCES ANIMAL_INFO(ANIMAL_ID),
FOREIGN KEY (BREAD_ID) REFERENCES BREAD_TYPE(BREAD_ID)
)
;
/*--------------------------PATIENT_DNA TABLES------------------------*/
CREATE TABLE PATIENT_DNA
(
DNA_NO VARCHAR(10) NOT NULL,
PATIENT_ID VARCHAR(10) NOT NULL,
DATETIMETAKEN DATETIME,
SYSTEMENTERDATETIME DATETIME,
RESULT VARCHAR(100),
PRIMARY KEY (DNA_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID)
)
;

/*---------------------RESCUER_INFO TABLES------------------------*/
CREATE TABLE RESCUER_INFO
(
RESCUER_ID VARCHAR(10) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50),
ADDRESS VARCHAR(200) NOT NULL,
PHONE VARCHAR(20),
MOBILE VARCHAR(20) NOT NULL,
EMAIL VARCHAR(20),
DATE DATE ,
PRIMARY KEY (RESCUER_ID)
)
;
/*------------------CONTACTS TABLES------------------------*/
CREATE TABLE CONTACTS
(
S_NO INT(10) NOT NULL,
CONTACT_TYPE VARCHAR(100) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50),
TITLE VARCHAR(100) NOT NULL,
EMAIL VARCHAR(50),

PHONE VARCHAR(20) NOT NULL,
STREET VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(200) NOT NULL,
SUBURB VARCHAR(20) NOT NULL,
STATE VARCHAR(10) NOT NULL,
COUNTRY VARCHAR(20) NOT NULL,
POSTCODE INT(10) NOT NULL,
PRIMARY KEY (S_NO)
)
;
/*-----------------CARER GROUP TABLES------------------------*/
CREATE TABLE CARER_GROUP
(
PERMIT_NUMBER INT NOT NULL,
CARERGROUP_NAME VARCHAR(100),
PERMIT_VALID VARCHAR(20),
PRIMARY KEY (PERMIT_NUMBER)
)
;
/*-------------------CARER_INFO TABLES------------------------*/
CREATE TABLE CARER_INFO
(
CARER_ID VARCHAR(10) NOT NULL,

PERMIT_NUMBER INT NOT NULL,
CARRER_NAME VARCHAR(50) NOT NULL,
RELEASE_ANIMAL VARCHAR(50),
REG_WILDLIFECARER VARCHAR(100),
BECOME_WILDLIDECARER VARCHAR(100),
SPECIES VARCHAR(100),
SEX VARCHAR(10),
WEIGHT VARCHAR(10),
AGE VARCHAR(10),
SEND_TO_CARER VARCHAR(10),
DATE DATE,
CARER VARCHAR(10),
TRIAGE_NOTES VARCHAR(255),
TRIAGE_NURSES VARCHAR(100),
PRIMARY KEY (CARER_ID),
FOREIGN KEY (PERMIT_NUMBER) REFERENCES CARER_GROUP(PERMIT_NUMBER)
)
;
/*------------------TREATMENT TABLES------------------------*/
CREATE TABLE TREATMENT
(
TREATMENT_NO INT NOT NULL,

PATIENT_ID VARCHAR(10) NOT NULL,
DIAGNOSIS_TYPE VARCHAR(100),
DIAGNOSIS_NAME VARCHAR(100),
NOTES VARCHAR(255),
ACTION_TAKEN VARCHAR(100),
START_DATE DATE,
STOP_DATE DATE,
PRESCRIBED_DATE DATE,
PRIMARY KEY (TREATMENT_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID)
)
;
/*----------------------ACCESSION TABLES------------------------*/
CREATE TABLE ACCESSION
(
ACCESSION_NO INT NOT NULL,
PATIENT_ID VARCHAR(10) NOT NULL,
RESCUER_ID VARCHAR(10) NOT NULL,
CARER_ID VARCHAR(10) NOT NULL,
S_NO INT NOT NULL,
PRIMARY KEY (ACCESSION_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID),
FOREIGN KEY(RESCUER_ID) REFERENCES RESCUER_INFO(RESCUER_ID),
FOREIGN KEY (CARER_ID) REFERENCES CARER_INFO(CARER_ID),

FOREIGN KEY (S_NO) REFERENCES CONTACTS(S_NO)
)
;
/*------------------UPDATE_CONTACT TABLES------------------------*/
CREATE TABLE UPDATE_CONTACT
(
LIST_NO INT NOT NULL,
DATE DATE NOT NULL,
ACCESSION_NO INT NOT NULL,
VET VARCHAR(100),
UPDATE_OUTCOME VARCHAR(100),
UNDER_VET_CARER VARCHAR(20),
WITH_CARER VARCHAR(20),
RELEASED VARCHAR(20),
OTHER VARCHAR(100),
PRIMARY KEY (LIST_NO, DATE),
FOREIGN KEY (ACCESSION_NO) REFERENCES ACCESSION(ACCESSION_NO)
)
;
/*-------------------------------------------------------INSERT DATA INTO
TABLES-----------------------------------------------*/

/*----------------------BREAD_TYPE TABLES-----------------------------------------------*/
INSERT INTO BREAD_TYPE VALUES (101, 'Reptile - Snake - Terrestrial', '(Blind Snake) Ramphotyphlops
sp.');
INSERT INTO BREAD_TYPE VALUES (102, 'Avian - Domestic/Feral/Exotic', 'African Grey Parrot');
INSERT INTO BREAD_TYPE VALUES (103, 'Marsupial - Macropod', 'Agile Wallaby');
INSERT INTO BREAD_TYPE VALUES (104, 'Avian - Domestic/Feral/Exotic', 'Alexandrine Parrot');
INSERT INTO BREAD_TYPE VALUES (105, 'Reptile - Lizard', 'Anomalopus Verreauxii');
/*---------------------------AFFICITION TABLES------------------------*/
INSERT INTO AFFICITION VALUES( 1, 'Adhesive/Sticky substance');
INSERT INTO AFFICITION VALUES( 3, 'Boat Strike');
INSERT INTO AFFICITION VALUES( 5, 'Cat Attack');
INSERT INTO AFFICITION VALUES( 7, 'Dog Attack');
INSERT INTO AFFICITION VALUES( 9, 'Drowning');
/*---------------------- ANIMAL_INFO TABLES------------------------*/
INSERT INTO ANIMAL_INFO VALUES ('87157', 'wallabies', 'VELI', 'US', 'US', 'LOCATION GOV
AREA','BAD','EAT SOMETHING',1,'YES','YES','1 HOUR',1234);
INSERT INTO ANIMAL_INFO VALUES ('89415', 'wallabies', 'MILI', 'US', 'US', 'SHIRE','BAD','DONT
KNOW',5,'YES','YES','10 HOUR',9807);
INSERT INTO ANIMAL_INFO VALUES ('89836', 'kangaroos', 'TIRA', 'US', 'US', 'LOCATION GOV
AREA','BAD','TRY TO KILL',3,'YES','YES','2 HOUR',7898);
INSERT INTO ANIMAL_INFO VALUES ('89927', 'kangaroos', 'VLIYA', 'US', 'US', 'LOCATION GOV
AREA','BAD','EAT SOMETHING',7,'YES','YES','3 HOUR',4567);
INSERT INTO ANIMAL_INFO VALUES ('89928', 'possums', 'YENI', 'US', 'US', 'NO','SHIRE','EAT
SOMETHING',9,'YES','YES','2 HOUR',0967);

/*------------------------------PATIENT TABLES------------------------*/
INSERT INTO PATIENT_INFO VALUES ('P101', '87157', 101, 'K101', '87989','L101', 'R101', 'T101', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P102', '89415', 102, 'K102', '89890','L102', 'R102', 'T102', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P103', '89836', 103, 'K103', '45678','L103', 'R103', 'T103', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P104', '89927', 104, 'K104', '12345','L104', 'R104', 'T104', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P105', '89928', 105, 'K105', '98978','L105', 'R105', 'T105', 'TAGES');
/*-----------------------------PATIENT_DNA TABLES------------------------*/
INSERT INTO PATIENT_DNA VALUES ('D101', 'P101', '2017-02-01 03:59:05', '2017-02-02 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D102', 'P102', '2017-02-02 03:59:05', '2017-02-03
03:59:05','MATCH');
INSERT INTO PATIENT_DNA VALUES ('D103', 'P103', '2017-02-03 03:59:05', '2017-02-04 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D104', 'P104', '2017-02-04 03:59:05', '2017-02-05 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D105', 'P105', '2017-02-05 03:59:05', '2017-02-06 03:59:05',
'MATCH');
/*----------------------------RESCUER_INFO TABLES------------------------*/
INSERT INTO RESCUER_INFO VALUES ('6033','Katherine', 'Latta', '22a Mary St', '07 1234 5678',
'7865456567', '[e-mail]', '2017-06-01');
INSERT INTO RESCUER_INFO VALUES ('5167','Jesse', 'Meldrum', '22a Mary St', '07 1234 5678',
'3567875777', '[e-mail]', '2017-06-02');
INSERT INTO RESCUER_INFO VALUES ('12901','Jesse', 'Burcher', '22a Mary St', '07 1234 5678',
'5756767878', '[e-mail]', '2017-06-03');
INSERT INTO RESCUER_INFO VALUES ('9217','Jesse', 'Hanger', '22a Mary St', '4675767678',
'7767676789', '[e-mail]', '2017-06-04');

INSERT INTO RESCUER_INFO VALUES ('12902','WANI', 'BINA', '22a Mary St', '6757566788', '5767767888',
'[e-mail]', '2017-06-05');
/*-----------------------------CONTACTS TABLES------------------------*/
INSERT INTO CONTACTS VALUES (1, 'PERSON', 'YANI', 'LINA', 'SOCIAL',
'[e-mail]','7898678979','67YHU','US','US','US','US',4567);
INSERT INTO CONTACTS VALUES (2, 'PERSON', 'VANI', 'LINA', 'SOCIAL',
'[e-mail]','6767787899','68UYU','US','US','US','US',8978);
INSERT INTO CONTACTS VALUES (3, 'PERSON', 'CANI', 'LINA', 'SOCIAL',
'[e-mail]','4565787899','56TGU','US','US','US','US',7654);
INSERT INTO CONTACTS VALUES (4, 'PERSON', 'NINA', 'LINA', 'SOCIAL',
'[e-mail]','5656676776','6JHHU','US','US','US','US',6789);
INSERT INTO CONTACTS VALUES (5, 'PERSON', 'YANI', 'LINA', 'SOCIAL',
'[e-mail]','5656656777','89UHU','US','US','US','US',0978);
/*-----------------------------CARER_GROUP TABLES------------------------*/
INSERT INTO CARER_GROUP VALUES (101, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (102, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (103, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (104, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (105, 'CR','VALID');
/*---------------------------- CARER_INFO TABLES------------------------*/
INSERT INTO CARER_INFO VALUES ('C101',101,'Jesse','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','23','YES','2018-06-1','YES','UIH','IHJK');
INSERT INTO CARER_INFO VALUES ('C102',102,'Annabelle','NO', 'YES','YES','TAKECARE
ANIMAL','F','45','24','YES','2018-06-2','YES','MKI','HJNBI');
INSERT INTO CARER_INFO VALUES ('C103',103,'Katherine','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','25','YES','2017-06-1','YES','BHI','IHJI');

INSERT INTO CARER_INFO VALUES ('C104',104,'John','NO', 'YES','YES','TAKECARE
ANIMAL','F','45','26','YES','2017-06-1','YES','NJI','IJJK');
INSERT INTO CARER_INFO VALUES ('C105',105,'MNI','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','27','YES','2018-06-1','YES','BHI','IQJK');
/*-------------------------------TREATMENT TABLES-----------------------------------------------*/
INSERT INTO TREATMENT VALUES (1, 'P101','XYZ','NMU','DMTUFJ','UI','2017-10-10','2018-01-01','2017-
12-01');
INSERT INTO TREATMENT VALUES (2, 'P102','NJH','NJK','KGFZDFCGVH','UQ','2017-10-11','2018-01-
02','2017-12-02');
INSERT INTO TREATMENT VALUES (3, 'P103','BNJ','MN','SDFGHJ','UW','2017-10-12','2018-01-03','2017-
12-03');
INSERT INTO TREATMENT VALUES (4, 'P104','VGH','VB','KJHGFGJ','UE','2017-10-13','2018-01-04','2017-
12-04');
INSERT INTO TREATMENT VALUES (5, 'P105','NBV','CVBN','DFGHJHGJ','UR','2017-10-14','2018-01-
05','2017-12-05');
/*--------------------------ACCESSION TABLES------------------------*/
INSERT INTO ACCESSION VALUES (111,'P101','6033','C101',1);
INSERT INTO ACCESSION VALUES (112,'P102','5167','C102',2);
INSERT INTO ACCESSION VALUES (113,'P103','12901','C103',3);
INSERT INTO ACCESSION VALUES (114,'P104','9217','C104',4);
INSERT INTO ACCESSION VALUES (115,'P105','12902','C105',5);
/*-----------------------UPDATE_CONTACT TABLES------------------------*/
INSERT INTO UPDATE_CONTACT VALUES (1, '2018-03-01',111,'IJ','GOOD','YES','YES','YES','UHJ');