Database Design for Animal Shelter

This assignment focuses on designing a relational database for an animal shelter. It outlines various entities like animals, carers, treatments, and their attributes. The design includes descriptions of fields, data types, relationships between entities (using foreign keys), and considerations for normalization to ensure data integrity and efficiency.

ICT211
Database Design
Task 2
Student ID –
Student Name-
Date -
Module Tutor -

Report
Part A: Database Design
ER Diagram
(Cinergix Pty Ltd. 2011)

Relational Schema
1. Breed
Attribute Data Type Key
FIIELDBreedType NCHAR(100) PK
FIIELDBreed NCHAR(100) PK
2. CauseOfAffliction
Attribute Data Type Key
FIIELDCauseOfAffliction NCHAR(500) PK
3. DiagnosisAetiology
Attribute Data Type Key
FIIELDDiagnosisAetiology NCHAR(500)
4. CarerGroups
Attribute Data Type Key
FIIELDPermitNumber NCHAR(20) PK
FIIELDCarerGroupName NCHAR(500)
FIIELDExpiryDate DATE
5. CarerGroupSpecialization
Attribute Data Type Key
FIIELDPermitNumber NCHAR(20) PK/FK
FIIELDSpecialization NCHAR(200) PK
6. Carers
Attribute Data Type Key
FIIELDCarerID NCHAR(20) PK
FIIELDPermitNumber NCHAR(20) FK
FIIELDPermitOptions NCHAR(200)
FIIELDFirstName NCHAR(50)
FIIELDLastName NCHAR(50)
FIIELDPhone NCHAR(20)
FIIELDMobile NCHAR(20)
FIIELDEmail NCHAR(50)
FIIELDAddress NCHAR(100)
FIIELDCity NCHAR(50)
FIIELDState NCHAR(50)
FIIELDCountry NCHAR(50)
FIIELDPostCode NCHAR(4)
7. CouncilArea

Attribute Data Type Key
FIIELDCouncilName NCHAR(200) PK
FIIELDSuburb NCHAR(50)
FIIELDState NCHAR(50)
FIIELDPostCode NCHAR(4)
8. OtherContacts
Attribute Data Type Key
FIIELDID NCHAR(20) PK
FIIELDTitle NCHAR(10)
FIIELDFirstName NCHAR(50)
FIIELDLastName NCHAR(50)
FIIELDPhone NCHAR(20)
FIIELDMobile NCHAR(20)
FIIELDEmail NCHAR(50)
FIIELDAddress NCHAR(100)
FIIELDCity NCHAR(50)
FIIELDState NCHAR(50)
FIIELDCountry NCHAR(50)
FIIELDPostCode NCHAR(4)
FIIELDContactType NCHAR(200)
9. Patient
Attribute Data Type Key
FIIELDPatientID NCHAR(20) PK
FIIELDQPWSNumber NCHAR(20)
FIIELDAnotherFacility NCHAR(200)
FIIELDTransformForm BLOB
FIIELDPatientName NCHAR(50)
FIIELDSex NCHAR(1)
FIIELDMicrochip NCHAR(50)
FIIELDKoalaTag NCHAR(50)
FIIELDTurtleTag NCHAR(50)
FIIELDLeftEarTag NCHAR(50)
FIIELDRightYearTag NCHAR(50)
FIIELDBreedType NCHAR(100) FK
FIIELDBreed NCHAR(100) FK
FIIELDGeneralNote NCHAR(500)
FIIELDStatus NCHAR(50)
FIIELDPicture MEDIUMBLOB
10. DNASamples
Attribute Data Type Key

FIIELDPatientID NCHAR(20) PK
FIIELDSampleTakenDateTime DATETIME PK
FIIELDReportDateTime DATETIME
FIIELDResult NCHAR(500)
11. Accession
Attribute Data Type Key
FIIELDAccessionNumber NCHAR(20) PK
FIIELDPatientID NCHAR(20) FK
FIIELDAdmittedDateTime DATETIME
FIIELDAdmitterID NCHAR(20) FK
FIIELDCanCallAdmitter BIT
FIIELDTransferred BIT
FIIELDCouncilName NCHAR(200) FK
FIIELDRescueLocation NCHAR(50)
FIIELDRescueSuburb NCHAR(50)
FIIELDRescueSituation NCHAR(500)
FIIELDRescueReason NCHAR(500)
FIIELDCauseOfAffliction NCHAR(500) FK
FIIELDDiagnosisAetiology NCHAR(500) FK
FIIELDTriageNurseID NCHAR(20) FK
FIIELDTriageNotes NCHAR(500)
FIIELDVetID NCHAR(20) FK
FIIELDVetNotes NCHAR(500)
FIIELDAge DECIMAL(5,2)
FIIELDMaturity NCHAR(20)
FIIELDCurrentCarerID NCHAR(20) FK
FIIELDSurgery NCHAR(100)
FIIELDOtherAcions NCHAR(100)
12. Treatment
Attribute Data Type Key
FIIELDAccessionNumber NCHAR(20) PK/FK
FIIELDMedicine NCHAR(100) PK
FIIELDStartDate DATE
FIIELDStopDate DATE
(Adrienne Watt n.d.)
Assumptions/Additional Information
Assumptions in making the database design are as follows-
- The carer group can have different types of specializations.

- The carer group can have different carers.
- The level of the patient is good to save into the database.
- The timings of the DNA reports are good to save into the database.
- The medicine should be saved into the database for all accession numbers.
- A council can admit the patient.
Normalisation
Normalization is the process to make the very effective, easy to access and easy to
handle database. Following actions are required in making the normalized database-
1. All relations are holding specific data.
2. All relations are holding primary keys.
3. All fields depend upon that primary key.
4. No transitive dependency exists in any table.
Reference
Adrienne Watt (n.d.), Chapter 11 Functional Dependencies, Retrieved from
http://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies.
Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from
http://creately.com/blog/diagrams/er-diagrams-tutorial.