Designing Database

Designing Database

Table of Contents
Introduction......................................................................................................................................2
Task 1...............................................................................................................................................2
Finalise ER diagram for data requirements by identification of all entities, relationships along
with cardinalities....................................................................................................................2
Task 2...............................................................................................................................................4
Develop relational schema that comprises of different keys along with other constraints....4
Task 3...............................................................................................................................................5
Creation of tables as well as relationship...............................................................................5
Task 4 – Query your Database.........................................................................................................7
A. Selection of records from table...................................................................................................7
1. List names as well as address of customers........................................................................7
2. List customers within branch and details of Pets...............................................................7
3 List details of drugs.............................................................................................................8
4. List of nurses and doctors within Veterinary Service........................................................8
5. List appointment details.....................................................................................................8
6. Count number of visit per each pet.....................................................................................8
7. List all available surgeons for particular time and date......................................................8
8. Produce list of treatment/diagnoses records.......................................................................8
9. List prescription details......................................................................................................8
B. Updating & Deleting Records.....................................................................................................9
1. Update stock of given drug.................................................................................................9
2. Update address details of customer....................................................................................9
3. Change appointment time...................................................................................................9
4. Delete or cancel appointment.............................................................................................9
5. Delete or remove customer from database.........................................................................9

Conclusion.......................................................................................................................................9
References......................................................................................................................................10

Introduction
Data structure which is liable for storing organised information is referred to as database.
This is an organised gathering of structured data or information which is responsible for storing
them electronically within computer system (Alaydrus and et. al, 2020). It is methodical
assortment of data. Structured Query language (SQL) refer to programming language that is
being utilised by relational database for query, manipulation as well as defining data for
providing access to data. This report is based on Veterinary practice which is present in
Wrexham and is looking forward to have computerised records. For doing so they need to
execute database for their four different branches. This report comprises of different aspects
related with entity relationship diagram, relational schema and certain queries will be illustrated.
Task 1
Finalise ER diagram for data requirements by identification of all entities, relationships along
with cardinalities.
The entity relationship diagram refers to kind of flowchart which illustrates ways in
which how entities like concepts, objects or entities relates with each other in a system. ERD
makes use of data modelling techniques that aids within defining business processes as well as
serve like a foundation for relational database (Arnbjerg-Nielsen, 2020). With respect to the case
scenario, there are four branches of Veterinary Services who deliver their functionalities. The
database comprises of details related with these branches, surgeons, nurses and many other
aspects. Basically, the ER diagram illustrates the way in which different aspects in an
organisation communicate with each other in terms of either direct or indirect relationship.