ICT211 Database Design Task 2 Report: ER Diagram & Schema

This report details the database design for a system, likely related to animal care or wildlife management, based on the provided relational schema and attribute data types. The design includes entities such as Breed, CauseOfAffliction, DiagnosisAetiology, CarerGroups, Carers, CouncilArea, OtherContacts, Patient, DNASamples, Accession, and Treatment, each with specific attributes and data types. Primary and foreign keys are identified to establish relationships between tables. The report also addresses database normalization, emphasizing the importance of organizing data effectively for easy access and management. Assumptions made during the design process are outlined, reflecting considerations such as carer group specializations, patient levels, and DNA report timings. References to Adrienne Watt's work on functional dependencies and Cinergix Pty Ltd.'s guide to ER diagrams are included, demonstrating a theoretical foundation for the design. This report offers a comprehensive overview of the database structure and the principles applied during its creation.

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.