ICT701 Relational Database Systems

ICT701 Relational Database Systems
Task 2
Student ID:
Module Tutor:
5/23/2018

Report
Part A: Database Design
ER Diagram
1Tutorialspoint.com (2015), ER Diagram Representation [online]. Available:
http://www.tutorialspoint.com/dbms/er_diagram_representation.htm [2018,
May]

Relational Schema
1. Breed
Attribute Data Type Key
BreedType NVARCHAR(100) PRIMARY KEY
Breed NVARCHAR(100) PRIMARY KEY
2. CauseOfAffliction
Attribute Data Type Key
CauseOfAffliction NVARCHAR(50) PRIMARY KEY
3. DiagnosisAetiology
Attribute Data Type Key
DiagnosisAetiology NVARCHAR(50)
4. Carers
Attribute Data Type Key
CarerID NVARCHAR(20) PRIMARY KEY
PermitNumber NVARCHAR(50) FOREIGN KEY
REFERENCES
CarerGroups
(PermitNumber)
PermitOptions NVARCHAR(200)
FirstName NVARCHAR(50)
LastName NVARCHAR(50)
Phone NVARCHAR(20)
Mobile NVARCHAR(50)
Email NVARCHAR(50)
Address NVARCHAR(50)
City NVARCHAR(50)
State NVARCHAR(50)
Country NVARCHAR(50)
PostCode NVARCHAR(50)
5. CouncilArea
Attribute Data Type Key
CouncilName NVARCHAR(200) PRIMARY KEY
Suburb NVARCHAR(50)

State NVARCHAR(50)
PostCode NVARCHAR(50)
6. OtherContacts
Attribute Data Type Key
ID INT(10) PRIMARY KEY
Title NVARCHAR(10)
FirstName NVARCHAR(50)
LastName NVARCHAR(50)
Phone NVARCHAR(20)
Mobile NVARCHAR(50)
Email NVARCHAR(50)
Address NVARCHAR(50)
City NVARCHAR(50)
State NVARCHAR(50)
Country NVARCHAR(50)
PostCode NVARCHAR(50)
ContactType NVARCHAR(200)
7. Patient
Attribute Data Type Key
PatientID INT(10) PRIMARY KEY
QPWSNumber NVARCHAR(50)
AnotherFacility NVARCHAR(200)
TransformForm BLOB
PatientName NVARCHAR(50)
Sex ENUM ('M','F','U','')
Microchip NVARCHAR(50)
KoalaTag NVARCHAR(20)
TurtleTag NVARCHAR(20)
LeftEarTag NVARCHAR(20)
RightYearTag NVARCHAR(20)
BreedType NVARCHAR(100) FOREIGN KEY
REFERENCES
Breed (BreedType)
Breed NVARCHAR(100) FOREIGN KEY
REFERENCES
Breed (Breed)
GeneralNote TEXT
Status NVARCHAR(50)
Picture MEDIUMBLOB
8. DNASamples
Attribute Data Type Key

PatientID INT(10) PRIMARY KEY
SampleTakenDateTime DATETIME PRIMARY KEY
ReportDateTime DATETIME
Result NVARCHAR(500)
9. Accession
Attribute Data Type Key
AccessionNumber NVARCHAR(50) PRIMARY KEY
PatientID INT(10) FOREIGN KEY
REFERENCES
Patient (PatientID)
AdmittedDateTime DATETIME
AdmitterID INT(10) FOREIGN KEY
REFERENCES
OtherContacts (ID)
CanCallAdmitter ENUM('Y','N','')
Transferred ENUM('Y','N','')
CouncilName NVARCHAR(200) FOREIGN KEY
REFERENCES
CouncilArea
(CouncilName)
RescueLocation NVARCHAR(255)
RescueSuburb NVARCHAR(255)
RescueSituation NVARCHAR(255)
RescueReason NVARCHAR(255)
CauseOfAffliction NVARCHAR(50) FOREIGN KEY
REFERENCES
CauseOfAffliction
(CauseOfAffliction)
DiagnosisAetiology NVARCHAR(50) FOREIGN KEY
REFERENCES
DiagnosisAetiology
(DiagnosisAetiology)
TriageNurseID NVARCHAR(20) FOREIGN KEY
REFERENCES
Carers (CarerID)
TriageNotes LONGTEXT
VetID NVARCHAR(20) FOREIGN KEY
REFERENCES
Carers (CarerID)
VetNotes TEXT
Age DECIMAL(5,2)
Maturity NVARCHAR(50)
CurrentCarerID NVARCHAR(20) FOREIGN KEY
REFERENCES
Carers (CarerID)
Surgery NVARCHAR(100)

OtherAcions NVARCHAR(100)
10. Treatment
Attribute Data Type Key
AccessionNumber NVARCHAR(50) PRIMARY KEY
FOREIGN KEY
REFERENCES
Accession
(AccessionNumber)
Medicine NVARCHAR(100) PRIMARY KEY
StartDate DATE
StopDate DATE
2 holowczak.com (2016), Converting E-R Models to Relational Models [online].
Available: http://holowczak.com/converting-e-r-models-to-relational-models/
[2018, May]
Assumptions/Additional Information
Following assumptions are used in designing the AZWH database-
1. A carer group may be specialized in more than one area.
2. A carer group may have any number of carers.
3. The level of patient is necessary to be stored into the database.
4. A council can also admit the patient.
Normalization
The database is in third normal form as the following database rules are used on the
entire database-
First Normal Form
- Remove repeated groups of data from all tables and generate new tables.
- Set primary key in all tables.
Second Normal Form
- All tables are in first normal form.
- All fields are depending upon the primary key.
Third Normal Form

- All tables are in second normal form.
- Remove transitive dependency from all tables.
Table BreedType
Table Treatment
Table CouncilArea
Table Carers
Table OtherContacts
\

Table Patient
Table DNASamples
Table Accession
Table Treatment
All tables showing above are satisfying all the above defined rules of normalization.
All the tables are satisfying the normalization rules similarly.

Reference
Tutorialspoint.com (2015), ER Diagram Representation [online]. Available:
http://www.tutorialspoint.com/dbms/er_diagram_representation.htm [2018, May]
holowczak.com (2016), Converting E-R Models to Relational Models [online].
Available: http://holowczak.com/converting-e-r-models-to-relational-models/ [2018,
May]