ICT701 Relational Database Systems: Database Design, ER Diagram & 3NF

This report provides a comprehensive database design solution, including an Entity-Relationship (ER) diagram and relational schema, focusing on the Australia Zoo Wildlife Hospital (AZWH). It details the database structure with tables like CarerGroups, Carers, Patients, Accession, and Treatment, outlining attributes, data types, and primary/foreign key relationships. The design incorporates various entities, including carer groups, carers, breeds, causes of affliction, diagnoses, other contacts, council areas, patients, DNA samples, accession details, and treatment information. The report also addresses database normalization, confirming that the database is in the third normal form (3NF), ensuring data integrity and minimizing redundancy. Assumptions and additional information regarding carer groups and patient management are also included, along with references to support the design principles.

Report
Part A: Database Design
ER Diagram

Relational Schema
1. CarerGroups
Attribute Data Type Key
FLLDPermitNumber NTEXT(49) PRIMARY KEY
FLLDCarerGroupName NTEXT(90)
FLLDExpiryDate DATE
2. CarerGroupSpecialization
Attribute Data Type Key
FLLDPermitNumber NTEXT(49) PRIMARY KEY
FOREIGN KEY
REFERENCES
CarerGroups
(FLLDPermitNumber)
FLLDSpecialization NTEXT(200) PRIMARY KEY
3. Carers
Attribute Data Type Key
FLLDCarerID NTEXT(20) PRIMARY KEY
FLLDPermitNumber NTEXT(49) FOREIGN KEY
REFERENCES
CarerGroups
(FLLDPermitNumber)
FLLDPermitOptions NTEXT(200)
FLLDFirstName NTEXT(49)
FLLDLastName NTEXT(49)
FLLDPhone NTEXT(20)
FLLDMobile NTEXT(49)
FLLDEmail NTEXT(49)
FLLDAddress NTEXT(49)
FLLDCity NTEXT(49)
FLLDState NTEXT(49)
FLLDCountry NTEXT(49)
FLLDPostCode NTEXT(49)
4. Breed_Type
Attribute Data Type Key
FLLDBreedTypeID INT PRIMARY KEY
FLLDBreedType NTEXT(90)
FLLDBreed NTEXT(90)

5. CauseOfAFlldliction
Attribute Data Type Key
FLLDCauseOfAFlldliction NTEXT(49) PRIMARY KEY
6. DiagnosisAetiology
Attribute Data Type Key
FLLDDiagnosisAetiology NTEXT(49) PRIMARY KEY
7. OtherContacts
Attribute Data Type Key
FLLDID INT(10) PRIMARY KEY
FLLDTitle NTEXT(10)
FLLDFirstName NTEXT(49)
FLLDLastName NTEXT(49)
FLLDPhone NTEXT(20)
FLLDMobile NTEXT(49)
FLLDEmail NTEXT(49)
FLLDAddress NTEXT(49)
FLLDCity NTEXT(49)
FLLDState NTEXT(49)
FLLDCountry NTEXT(49)
FLLDPostCode NTEXT(49)
FLLDContactType NTEXT(200)
8. CouncilArea
Attribute Data Type Key
FLLDCouncilName NTEXT(200) PRIMARY KEY
FLLDSuburb NTEXT(49)
FLLDState NTEXT(49)
FLLDPostCode NTEXT(49)
9. Patient
Attribute Data Type Key
FLLDPatientID INT(10) PRIMARY KEY
FLLDQPWSNumber NTEXT(49)
FLLDAnotherFacility NTEXT(200)
FLLDTransformForm BLOB
FLLDPatientName NTEXT(49)
FLLDSex ENUM ('M','F','U','')
FLLDMicrochip NTEXT(49)

FLLDKoalaTag NTEXT(20)
FLLDTurtleTag NTEXT(20)
FLLDLeftEarTag NTEXT(20)
FLLDRightYearTag NTEXT(20)
FLLDBreedTypeID INT FOREIGN KEY
REFERENCES
BreedType
(FLLDBreedTypeID)
FLLDGeneralNote NTEXT
FLLDStatus NTEXT(49)
FLLDPicture MEDIUMBLOB
10. DNASamples
Attribute Data Type Key
FLLDPatientID INT(10) PRIMARY KEY
FOREIGN KEY
REFERENCES
Patient
(FLLDPatientID)
FLLDSampleTakenDateTime DATETIME PRIMARY KEY
FLLDReportDateTime DATETIME
FLLDResult NTEXT(500)
11. Accession
Attribute Data Type Key
FLLDAccessionNumber NTEXT(49) PRIMARY KEY
FLLDPatientID INT(10) FOREIGN KEY
REFERENCES Patient
(FLLDPatientID)
FLLDAdmittedDateTime DATETIME
FLLDAdmitterID INT(10) FOREIGN KEY
REFERENCES Carers
(FLLDCarerID)
FLLDCanCallAdmitter ENUM('Y','N','')
FLLDTransferred ENUM('Y','N','')
FLLDCouncilName NTEXT(200) FOREIGN KEY
REFERENCES
CouncilArea
(FLLDCouncilName)
FLLDRescueLocation NTEXT(255)
FLLDRescueSuburb NTEXT(255)
FLLDRescueSituation NTEXT(255)
FLLDRescueReason NTEXT(255)
FLLDCauseOfAFlldliction NTEXT(49) FOREIGN KEY
REFERENCES
CauseOfAFlldliction

(FLLDCauseOfAFlldliction)
FLLDDiagnosisAetiology NTEXT(49) FOREIGN KEY
REFERENCES
DiagnosisAetiology
(FLLDDiagnosisAetiology)
FLLDTriageNurseID NTEXT(20) FOREIGN KEY
REFERENCES
Carers (FLLDCarerID)
FLLDTriageNotes LONGNTEXT
FLLDVetID NTEXT(20) FOREIGN KEY
REFERENCES
Carers (FLLDCarerID)
FLLDVetNotes NTEXT
FLLDAge DECIMAL(5,2)
FLLDMaturity NTEXT(49)
FLLDCurrentCarerID NTEXT(20) FOREIGN KEY
REFERENCES
Carers (FLLDCarerID)
FLLDSurgery NTEXT(90)
FLLDOtherAcions NTEXT(90)
12. Treatment
Attribute Data Type Key
FLLDAccessionNumber NTEXT(49) PRIMARY KEY
FOREIGN KEY
Accession
(FLLDAccessionNumber)
FLLDMedicine NTEXT(90) PRIMARY KEY
FLLDStartDate DATE
FLLDStopDate DATE
Assumptions/Additional Information
1. A carer group may be work as carer and take patients to the hospital.
2. Level of patient should be managed along with patient’s data.
3. Carer group or council can work as carer. They can take the patients to the
hospital.

Normalization
The database is in third normal form and each normal form is built by following rules-
1 NF
- Make separate entities by removing releated groups.
- Set primary key in all new tables.
2 NF
- All entities are in 1 NF.
- Only primary key is the dependent for other fields.
3 NF
- All entities are in 2 NF.
- There is no transitive dependency.
Below table is the councilArea table.
- There is no repeated group.
- All fields are depending upon the CouncilName only.
- There is no transitive dependency.
This table is in 3 NF. Similarly, all tables are following the same normalization rule.
That is why, the database is in third normal form.
(Chapple M. 2018)
(geeksforgeeks.org n.d)

Reference
Chapple M. (2018). Database Normalization Basics. [online]. Available from:
https://www.lifewire.com/database-normalization-basics-1019735 [Accessed: 2-June-
2018]
geeksforgeeks.org (n.d.). Database Normalization | Normal Forms. [online.] Available
from: https://www.geeksforgeeks.org/database-normalization-normal-forms/
[Accessed 2-June-2018].