ICT211 Database Design Task 2 Report: ER Diagram and Schema

This report details a database design for the ICT211 module, focusing on an ER diagram and the relational schema. The report outlines the attributes, data types, and key constraints for various entities such as Breed, CauseOfAffliction, DiagnosisAetiology, CarerGroups, Carers, CouncilArea, OtherContacts, Patient, DNASamples, Accession, and Treatment. Assumptions are made regarding carer specializations, patient-level data, and DNA report timestamps. The database design adheres to the third normal form, ensuring data integrity and minimizing redundancy. The report references external sources for database design principles and ER diagrams. It includes detailed tables defining the attributes, data types, and key relationships for each entity within the database, providing a comprehensive overview of the database structure and its adherence to normalization principles. This assignment is available on Desklib for students to aid their studies.

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

Report
Part A: Database Design
ER Diagram
(Tutorialcup 2015)
(Ziff Davis (2017)

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

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

FIIELDPatientID INT(10) PK
FIIELDSampleTakenDateTime DATETIME PK
FIIELDReportDateTime DATETIME
FIIELDResult NVARCHAR(500)
11. Accession
Attribute Data Type Key
FIIELDAccessionNumber NVARCHAR(50) PK
FIIELDPatientID INT(10) FK
FIIELDAdmittedDateTime DATETIME
FIIELDAdmitterID INT(10) FK
FIIELDCanCallAdmitter ENUM('Y','N','')
FIIELDTransferred ENUM('Y','N','')
FIIELDCouncilName NVARCHAR(200) FK
FIIELDRescueLocation NVARCHAR(255)
FIIELDRescueSuburb NVARCHAR(255)
FIIELDRescueSituation NVARCHAR(255)
FIIELDRescueReason NVARCHAR(255)
FIIELDCauseOfAffliction NVARCHAR(50) FK
FIIELDDiagnosisAetiology NVARCHAR(50) FK
FIIELDTriageNurseID NVARCHAR(20) FK
FIIELDTriageNotes LONGTEXT
FIIELDVetID NVARCHAR(20) FK
FIIELDVetNotes TEXT
FIIELDAge DECIMAL(5,2)
FIIELDMaturity NVARCHAR(50)
FIIELDCurrentCarerID NVARCHAR(20) FK
FIIELDSurgery NVARCHAR(100)
FIIELDOtherAcions NVARCHAR(100)
12. Treatment
Attribute Data Type Key
FIIELDAccessionNumber NVARCHAR(50) PK/FK
FIIELDMedicine NVARCHAR(100) PK
FIIELDStartDate DATE
FIIELDStopDate DATE
Assumptions/Additional Information
Some assumptions are considered in the above database design-

- A carer group is not bothered for only one specialization. A carer group can
have different types of specializations.
- A carer group may have number of carers.
- Patient level is must.
- DNA report’s time is necessary to save into database.
- A patient can be admitted by the council too.
Normalisation
The database is in the third normal form because of the following rules-
- Each table has precise data with defined primary key.
- All fields depend upon the selected primary key.
- Transitive dependency is not the part of any relation.
Reference
Tutorialcup (2015), ER Data Model, Retrieved from
https://www.tutorialcup.com/dbms/er-data-model.htm.
Ziff Davis (2017), Understanding Relationships in E-R Diagrams, Retrieved from
http://it.toolbox.com/blogs/enterprise-solutions/understanding-relationships-
in-er-diagrams-14310.