Database Project: ER Diagram, Relational Schema, Data Dictionary & SQL

This project showcases the design and implementation of a database system, beginning with an Entity-Relationship (ER) diagram that visually represents the entities and their relationships within the database. The ER diagram is then translated into a relational schema, which defines the tables, columns, and primary/foreign key constraints. A detailed data dictionary provides comprehensive information about each attribute, including data types, constraints, and descriptions. The project also includes SQL queries demonstrating how to retrieve and manipulate data within the database, with examples focusing on data retrieval based on status, suburb council information for recent admissions, and carer group permits. Assumptions made during the design process are explicitly stated to clarify the scope and limitations of the database model.

P a g e | 1
Table of contents
Part A.....................................................................................................................................................3
ER diagram.........................................................................................................................................3
Relational schema.............................................................................................................................4
Data dictionary..................................................................................................................................5
Assumptions:...................................................................................................................................11
Part b...................................................................................................................................................12
References...........................................................................................................................................15
List of figures
Figure 1: ER diagram..............................................................................................................................3
Figure 2: relational schema...................................................................................................................4
Figure 3: Query 1.................................................................................................................................12
Figure 4: Query 2 part A......................................................................................................................13
Figure 5: Query 2 part B.......................................................................................................................13
Figure 6: Query 3.................................................................................................................................14

P a g e | 2
Part A
ER diagram
Figure 1: ER diagram

P a g e | 3
Relational schema
Figure 2: relational schema

P a g e | 4
Data dictionary
Accession
Attributes Data types Allow null constraints Default
Accession_id Int No Primatry key
date_admitted Date No
Time Admitted Time No
admitter_id Int No
can_call_admitter Text Yes
transfer_facility_id Int No
rescue_location Varchar(100) yes 0
rescue_suburb Varchar(100) yes
rescue_situation Varchar(100) yes
rescue_situation_other Varchar(100) yes
Rescue Reason Varchar(100) yes
time_in_captivit
y
Time Yes
Affliction_id Int Yes
Diagnosis Category Varchar(100) Yes
Triage Nurse ID Int No Foreign key(trinage-
>trinage nurse id)
0
veterinarian_id Int No Foreign key(veterinary-
>veterinarian id)
0
diagnosis_aetiological_ids Int yes Foreign
key(diagnosisaetiological-
>
diagnosis_aetiological_id)
Treatment_id Int No Foreign key(treatment-
>treatment_id)
weight Decimal(5,2)
age Varchar(100)
maturity Varchar(100)
current_carer_id Varchar(100) No 0

P a g e | 5
Rescuers
Attributes Data types Allow null constraints Default
Comments
ID Int No Primatry key Stores
unque id
for each
rescurer
First Name Varchar(100) Yes
Last Name Varchar(100) No
phone Varchar(100) Yes
mobile Varchar(100) Yes
email Varchar(100) Yes
address Varchar(100) Yes
city Varchar(100) Yes
country Varchar(100) Yes
Nonwcconatcts
Attributes Data types Allow
null
constraints Default
Comments
IFnull(firstname,'')
Int No Primatry key Composite
primary
key of first
name and
last name
IFnull(lastname,'')
Varchar(100) No Primary key
IFnull(phone,'unknown')
Varchar(100) No
IFnull(mobile,'unknown') Varchar(100) Yes
IFnull(email,'unknown') Varchar(100) Yes
IFnull(address1,'unknown') Varchar(100) No unknown
IFnull(city,'unknown') Varchar(100) Yes
IFnull(country,'unknown')
Varchar(100) Yes
hospital_id Varchar(100) No Foreign key
(hospital-
>hospital_id)

P a g e | 6
status
Attributes Data types Allow null constraints Default
Comments
Status_id Int No Primatry key Stores
unque id
for each
status of
patient
status Varchar(100) No
Diagnosis_aetiology
Attributes Data types Allow
null
constraints Default
Comments
Diagnosis_aethological_id Int No Primatry key Stores
unque id
for each
diagnosis
of patient
name Varchar(100) No
Causes_of_afflication
Attributes Data types Allow null constraints Default
Comments
id Int No Primatry key Stores
unque id
for each
affliction
of patient
name Varchar(100) No
transfer
Attributes Data types Allow null constraints Default
Comments
Transfer_facility_id Int No Primatry key 0 Stores
unque id
for each
transfer of
patient
transfer Varchar(100) No

P a g e | 7
veterinary
Attributes Data types Allow null constraints Default
Comments
Veterinary_id Int No Primatry key 0
Vet notes Varchar(100) No
triage
Attributes Data types Allow null constraints Default
Comments
Triage nurse id Int No Primatry key 0
Triage notes Varchar(100) No Not
recorded
treatment
Attributes Data types Allow null constraints Default
Comments
Treatment id Int No Primatry key 0
Fed_medicated Varchar(100) Yes N
Fed_what Varchar(100) Yes
Fed_time Time Yes
patient
Attributes Data types Allow null constraints Default
Comments
animal_id Int No Primatry key Stores
unque id
for each
patient
QPWS_NUMBER Varchar(100) No
Name Varchar(100) Yes
Sex Char (100) No
Microchip Varchar(100) Yes
Koala Tag Varchar(100) Yes
Turtle Tag Varchar(100) Yes
Left Ear Tag Varchar(100) Yes
Right Ear Tag Varchar(100) no
Breed Varchar(100) Yes
General Notes Varchar(100) No
status_id Int Yes Foreign key (status-
>status_id)

P a g e | 8
Breed
Attributes Data types Allow null constraints Default
Comments
Breed Int No Primatry key Stores
unique for
each
patient
belongs to
type Varchar(100) No
hospital
Attributes Data types Allow null constraints Default
Comments
Hospital_id Int No Primatry key Stores
unique
about
each hotel
Hospital_name Varchar(100) No
Patient_accession
Attributes Data types Allow null constraints Default
Comments
Patient_id Int No Primatry key Composite
primary
key
Accession_id int No Primatry key
carers
Attributes Data types Allow null constraints Default
Comments
permit_number Int No Foreign key (permit-
>permit_number)
permit_options Varchar(100) Yes
firstname Text No
lastname Text No
phone Varchar(100) Yes
mobile Varchar(100) Yes
email Varchar(100) Yes
address1 Varchar(100) Yes
city Varchar(100) Yes
country Varchar (100) Yes
State Char (100) Yes
Postcode int Yes

P a g e | 9
Carer_group
Attributes Data types Allow null constraints Default
Comments
Permit_number Varchar(100) No Foreign key (permit-
>permit_number)
Permit_type Varchar (100) No
Permit_valid_to Date Yes
permit
Attributes Data types Allow null constraints Default
Comments
Permit_number Varchar(100) No Primary key
name Varchar (100) No
Suburb_council
Attributes Data types Allow null constraints Default
Comments
Name Varchar(100) No Primatry key
Postcode Varchar(100) Yes
State Text(100) Yes
City Varchar(100) No

P a g e | 10
Assumptions:
 Rescuer can rescue more than one patient
 One or more animals can belong to single breed.
animals may or may not have to take diagnostic aetiology
 Animals may or may not suffer from affliction
 No two cities can have same name
 Treatment or may not be prescribed to a patient.
 System maintains the contacts details only listed hospitals, wedlife sanctuaries or zoological
parks.
 Each rescue only have single location details
 Each carer belongs to only one location.

P a g e | 11
Part b
Query 1
SELECT accessions.accession_number, patient_accession.patient_id, patient.Name FROM
((((patient_accession INNER JOIN accessions ON patient_accession.accession_id =
accessions.accession_number) INNER JOIN patient ON patient.animal_id =
patient_accession.patient_id) INNER JOIN breed ON breed.Breed = patient.Breed) INNER join status
on patient.Status_id=status.status_id) where status.Status != "Transfer Out" ORDER BY
`breed`.`Typen(Taxon - Macro)`

P a g e | 12
Figure 3: Query 1
Query2
a) SELECT * from accessions inner join suburb_council ON
accessions.rescue_suburb=suburb_council.Name where
MONTH(accessions.date_admitted)=MONTH(curdate())

P a g e | 13
Figure 4: Query 2 part A
b) SELECT * from accessions inner join suburb_council ON
accessions.rescue_suburb=suburb_council.Name where
MONTH(accessions.date_admitted)=MONTH(curdate()) GROUP by
accessions.affliction_id,suburb_council.city
Figure 5: Query 2 part B

P a g e | 14
Query 3
SELECT * FROM `carer_groups` WHERE carer_groups.permit_valid_to <CURRENT_DATE
Figure 6: Query 3

P a g e | 15
References
 Plus2net.com. (2017). Converting string to date format by using str_to_date() function to
store in records of mysql table. [online] Available at:
http://www.plus2net.com/sql_tutorial/str_to_date.php [Accessed 7 Jun. 2018].
 Social.msdn.microsoft.com. (2017). Pull Records for current & previous calendar year.
[online] Available at: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f72f8c6d-
fbba-4f0e-a083-e854f85eda19/pull-records-for-current-amp-previous-calendar-year?
forum=transactsql [Accessed 7 Jun. 2018].