ICT211 Task 2: Relational Database Design Report - WebFinance

This report provides a comprehensive analysis of relational database design, focusing on the WebFinance system. It includes an ER diagram illustrating the relationships between various entities such as Breed, CauseOfAffliction, DiagnosisAetiology, CarerGroups, Carers, CouncilArea, OtherContacts, Patient, DNASamples, Accession, and Treatment. The report details the relational schema, specifying attributes, data types, and primary/foreign keys for each table. Assumptions are outlined, and the importance of normalization is emphasized, with adherence to third normal form principles. The report references sources like WebFinance, Techtarget, and Pearson Education to support the design choices and methodologies.

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

Report
Part A: Database Design
ER Diagram
(WebFinance 2017)
(Techtarget 2017)

Relational Schema
1. Breed
Attribute Data Type Key
FIELDBreedType NVARCHAR(100) PK
FIELDBreed NVARCHAR(100) PK
2. CauseOfAffliction
Attribute Data Type Key
FIELDCauseOfAffliction NVARCHAR(500) PK
3. DiagnosisAetiology
Attribute Data Type Key
FIELDDiagnosisAetiology NVARCHAR(500)
4. CarerGroups
Attribute Data Type Key
FIELDPermitNumber NVARCHAR(20) PK
FIELDCarerGroupName NVARCHAR(500)
FIELDExpiryDate DATE
5. CarerGroupSpecialization
Attribute Data Type Key
FIELDPermitNumber NVARCHAR(20) PK/FK
FIELDSpecialization NVARCHAR(200) PK
6. Carers
Attribute Data Type Key
FIELDCarerID NVARCHAR(20) PK
FIELDPermitNumber NVARCHAR(20) FK
FIELDPermitOptions NVARCHAR(200)
FIELDFirstName NVARCHAR(50)
FIELDLastName NVARCHAR(50)
FIELDPhone NVARCHAR(20)
FIELDMobile NVARCHAR(20)
FIELDEmail NVARCHAR(50)
FIELDAddress NVARCHAR(100)
FIELDCity NVARCHAR(50)
FIELDState NVARCHAR(50)
FIELDCountry NVARCHAR(50)
FIELDPostCode NVARCHAR(4)
7. CouncilArea

Attribute Data Type Key
FIELDCouncilName NVARCHAR(200) PK
FIELDSuburb NVARCHAR(50)
FIELDState NVARCHAR(50)
FIELDPostCode NVARCHAR(4)
8. OtherContacts
Attribute Data Type Key
FIELDID NVARCHAR(20) PK
FIELDTitle NVARCHAR(10)
FIELDFirstName NVARCHAR(50)
FIELDLastName NVARCHAR(50)
FIELDPhone NVARCHAR(20)
FIELDMobile NVARCHAR(20)
FIELDEmail NVARCHAR(50)
FIELDAddress NVARCHAR(100)
FIELDCity NVARCHAR(50)
FIELDState NVARCHAR(50)
FIELDCountry NVARCHAR(50)
FIELDPostCode NVARCHAR(4)
FIELDContactType NVARCHAR(200)
9. Patient
Attribute Data Type Key
FIELDPatientID NVARCHAR(20) PK
FIELDQPWSNumber NVARCHAR(20)
FIELDAnotherFacility NVARCHAR(200)
FIELDTransformForm BLOB
FIELDPatientName NVARCHAR(50)
FIELDSex NVARCHAR(1)
FIELDMicrochip NVARCHAR(50)
FIELDKoalaTag NVARCHAR(50)
FIELDTurtleTag NVARCHAR(50)
FIELDLeftEarTag NVARCHAR(50)
FIELDRightYearTag NVARCHAR(50)
FIELDBreedType NVARCHAR(100) FK
FIELDBreed NVARCHAR(100) FK
FIELDGeneralNote NVARCHAR(500)
FIELDStatus NVARCHAR(50)
FIELDPicture MEDIUMBLOB
10. DNASamples
Attribute Data Type Key

FIELDPatientID NVARCHAR(20) PK
FIELDSampleTakenDateTime DATETIME PK
FIELDReportDateTime DATETIME
FIELDResult NVARCHAR(500)
11. Accession
Attribute Data Type Key
FIELDAccessionNumber NVARCHAR(20) PK
FIELDPatientID NVARCHAR(20) FK
FIELDAdmittedDateTime DATETIME
FIELDAdmitterID NVARCHAR(20) FK
FIELDCanCallAdmitter BIT
FIELDTransferred BIT
FIELDCouncilName NVARCHAR(200) FK
FIELDRescueLocation NVARCHAR(50)
FIELDRescueSuburb NVARCHAR(50)
FIELDRescueSituation NVARCHAR(500)
FIELDRescueReason NVARCHAR(500)
FIELDCauseOfAffliction NVARCHAR(500) FK
FIELDDiagnosisAetiology NVARCHAR(500) FK
FIELDTriageNurseID NVARCHAR(20) FK
FIELDTriageNotes NVARCHAR(500)
FIELDVetID NVARCHAR(20) FK
FIELDVetNotes NVARCHAR(500)
FIELDAge DECIMAL(5,2)
FIELDMaturity NVARCHAR(20)
FIELDCurrentCarerID NVARCHAR(20) FK
FIELDSurgery NVARCHAR(100)
FIELDOtherAcions NVARCHAR(100)
12. Treatment
Attribute Data Type Key
FIELDAccessionNumber NVARCHAR(20) PK/FK
FIELDMedicine NVARCHAR(100) PK
FIELDStartDate DATE
FIELDStopDate DATE
(Pearson Education 2017)
Assumptions/Additional Information
1. A one carer group may contain so many specializations.
2. A one carer group may contain so many carers.
3. The patient’s level like Adult should be saved into the database.

4. A patient can be admitted by the council too.
5. The date and time of DNA sample report should be saved into the database.
6. All accession numbers should be saved along with medicines.
Normalisation
Normalization is the necessary activity for each database. The normalized database
helps the user to fetch the data very easily. Following rules are applied in making the
database in third normal form-
1. Create different tables for different data with primary key.
2. Other fields should depend upon the primary key.
3. Transitive dependency is not shown in any table.
Reference
WebFinance (2017), Relational Database, Retrieved from
http://www.businessdictionary.com/definition/relational-database.html
Techtarget (2017), Relational Database, Retrieved from
http://searchsqlserver.techtarget.com/definition/relational-database
Pearson Education (2017), Data Types in MySQL, Retrieved from
http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2