Database Assessment for VET Solution Company

This report focuses on designing and implementing a relational database system for VET Solution, a veterinary organization in Cambridgeshire. The database aims to manage patient information, employee details, and branch data, enhancing communication and data sharing within the company. The report covers the design process using ER diagrams, data validation, and normalization techniques. It then details the development of the database using MS SQL Server 2014, including table creation, relationship diagrams, and query examples. Finally, the report provides comprehensive technical and user documentation, including diagrams illustrating data movement and functional dependencies.

Database Assessment

Table of Contents
Introduction............................................................................................................................ 5
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem.................................................................................................................................. 6
P1. Design a relational database system using appropriate design tools and techniques. .6
Requirement of database administration framework.......................................................6
Client Requirement.........................................................................................................6
Framework Requirement.................................................................................................7
Software Requirements...................................................................................................7
Hardware requirements...................................................................................................7
ER diagram..................................................................................................................... 7
Relationship....................................................................................................................8
M1 Produce a comprehensive design for a fully functional system.....................................9
Data Validation................................................................................................................ 9
Data Dictionary.............................................................................................................. 10
Data Normalization........................................................................................................14
LO2 Develop a fully functional relational database system...................................................19
P2. Develop the database system using MS SQL Server 2014........................................19
Relationship diagram.....................................................................................................19
Design view of tables....................................................................................................20
P3. Produce a query language, with queries across multiple tables for the database.......27
M2.................................................................................................................................... 31
LO3 Test the system against user and system requirements:..............................................32
P4 Create a test plan to test the database:.......................................................................32
M4 Create and populate tables, validation, verification, data types and referential integrity
......................................................................................................................................... 36
LO4 Produce technical and user documentation..................................................................38
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company.......................................................................................................38
1

User documentation......................................................................................................38
Technical documentation..............................................................................................45
M5 Produce technical and user documentation for a fully functional system, including
diagrams showing movement of data...............................................................................50
Conclusion........................................................................................................................... 51
References........................................................................................................................... 52
List of figures
Figure 1 ER diagram.............................................................................................................. 8
Figure 2 Data validation 1.......................................................................................................9
Figure 3 Data validation 2.....................................................................................................10
Figure 4 relationship diagram...............................................................................................19
Figure 5 owner design view..................................................................................................20
Figure 6 owner datasheet view.............................................................................................20
Figure 7 owner pet design view............................................................................................20
Figure 8 owner pet datasheet view.......................................................................................20
Figure 9 Vet appointment design view..................................................................................21
Figure 10 vet appointment datasheet view...........................................................................21
Figure 11 Vet bill design view...............................................................................................21
Figure 12 vet bill datasheet view..........................................................................................21
Figure 13 vet branch design view.........................................................................................22
Figure 14 vet branch datasheet view....................................................................................22
Figure 15 vet consultation design view.................................................................................22
Figure 16 vet consultation datasheet view............................................................................23
Figure 17 Vet department design view.................................................................................23
Figure 18 vet department datasheet view.............................................................................23
Figure 19 Vet drug design view............................................................................................23
Figure 20 vet drug datasheet view........................................................................................24
Figure 21 vet manager design view......................................................................................24
Figure 22 vet manager datasheet view.................................................................................24
Figure 23 Vet prescription design view.................................................................................24
Figure 24 vet prescription datasheet view............................................................................25
Figure 25 vet staff design view.............................................................................................25
Figure 26 vet staff datasheet view........................................................................................25
Figure 27 Vet treatment design view....................................................................................25
2

Figure 28 vet treatment datasheet view................................................................................26
Figure 29 vet bill details design view....................................................................................26
Figure 30 vet bill details datasheet view...............................................................................26
Figure 31 Query 1................................................................................................................ 27
Figure 32 query 2................................................................................................................. 27
Figure 33 query 3................................................................................................................. 28
Figure 34 query 4................................................................................................................. 28
Figure 35 query 5................................................................................................................. 29
Figure 36 query 6................................................................................................................. 29
Figure 37 query 7................................................................................................................. 30
Figure 38 Test case 1...........................................................................................................32
Figure 39 Test case 2...........................................................................................................33
Figure 40 Test case 3...........................................................................................................33
Figure 41 Test case 4...........................................................................................................34
Figure 42 Test case 5...........................................................................................................35
Figure 43 Validation 1.......................................................................................................... 36
Figure 44 validation 2........................................................................................................... 37
Figure 45 treatment table.....................................................................................................38
Figure 46 Vet staff table.......................................................................................................38
Figure 47 prescription table..................................................................................................39
Figure 48 Manager table......................................................................................................39
Figure 49 drug table............................................................................................................. 39
Figure 50 Department details...............................................................................................40
Figure 51 consultation table.................................................................................................40
Figure 52 Branch table.........................................................................................................40
Figure 53 Bill details table....................................................................................................41
Figure 54 Bill ID.................................................................................................................... 41
Figure 55 Appointment table................................................................................................41
Figure 56 pet table............................................................................................................... 41
Figure 57 owner table...........................................................................................................42
Figure 58 Query 1................................................................................................................ 43
Figure 59 Query 2................................................................................................................ 43
Figure 60 query 3................................................................................................................. 44
Figure 61 Query 4................................................................................................................ 44
Figure 62 query 5................................................................................................................. 45
Figure 63 owner design view................................................................................................45
Figure 64 owner pet design view..........................................................................................45
3

Figure 65 Vet appointment design view................................................................................45
Figure 66 Vet bill design view...............................................................................................46
Figure 67 vet branch design view.........................................................................................46
Figure 68 vet consultation design view.................................................................................46
Figure 69 Vet department design view.................................................................................46
Figure 70 Vet drug design view............................................................................................47
Figure 71 vet manager design view......................................................................................48
Figure 72 Vet prescription design view.................................................................................48
Figure 73 vet staff design view.............................................................................................48
Figure 74 Vet treatment design view....................................................................................48
Figure 75 vet bill details design view....................................................................................49
Figure 76 Relationship diagram............................................................................................49
Figure 77 functional dependency.........................................................................................50
List of tables
Table 1 Data dictionary........................................................................................................10
Table 2 1 NF........................................................................................................................ 16
Table 3 2 NF........................................................................................................................ 17
Table 4 3 NF........................................................................................................................ 18
Table 5 Test cases............................................................................................................... 32
4

Introduction
This report fundamental target is to outline and build up the VET solution database of
Cambridgeshire. The VET solution is an organization that offers veterinary services that give
the care administrations to animals. The organization requires a database for data
stockpiling, recovery, and refresh and adjust. It can be overseen by building up a database
administration framework in organization. In addition, organization requires a database for
enhancing the correspondence and data distribution amongst the Company. The designed
database for the organization will be created utilizing the software name MS SQL server
2014 which gives incredible database administration belongings to the organization. This
report will incorporate an outlining system of the VET solution database, improvement of
database and testing of the database. Lastly, a technical document and full user
documentation will be accommodated of the planned database.
5

LO1 Use an appropriate design tool to design a relational database
system for a substantial problem
P1. Design a relational database system using appropriate design tools
and techniques
For planning the VET arrangement organization's database, initially a suitable outlined will
be set up as the model like ERD chart utilizing a planning instrument. The organization VET
solution is expanding its outlet in various nations, it is important for organization to move to
the database administration framework. The clients and employee of the organization are
expanding step by step so to deal with this circumstance organization chooses to move to
the database administration framework rather than the database done manually on paper.
Organization needs a database which could include and adjust the patient points of interest
and employee detail, likewise, the distinct database must deal with the data of all the VET
solution branches. It is required to build a database for making an appropriate printed invoice
for the clients (Oracle, 2017).
Requirement of database administration framework
The vital goal of an organization to picks the database administration framework is to create
and protect the relationship that is present in the VET solution organization information
(Elmasri, and Navathe, 2010).
Client Requirement
 It is necessary for the database to have the capacity to alter the data embedded.
 Database made must record and oversee detail data of Pets (i.e. patients).
 Database must be ready to manage and add the employee record.
 Database made must record and oversee detail data of Owners to which the pet
belongs too.
 Database made ought to have the capacity to include and deal with the receipt subtle
elements to Owner of the pet for medicines of their pet.
 Database must have the capacity to manage and add the bill details of pets at all
centers of VET solution.
 Database must have the capacity to manage and include all the pet details like
treatment given, diagnosis done, drugs and more.
 Database must have the capacity to include and deal with the points of interest of
administrations (i.e. services) offer to pet.
6

Framework Requirement
For planning and dealing with the database, a framework with several requirements is
essential. There are certain software and programming necessities for building up the
database for the VET solution;
 Database needs to work proficiently and adequately.
 Database ought to have unmistakable recognizable proof and proper introduction
including name, address, contact etc.
 Database must have the capacity to perform quickly and have great ease of use
(Stonebraker, Deng and Brodie, 2017).
For database development of the VET solution some hardware and software requirements
are;
Software Requirements
a. Windows or Linux operating system.
b. MS SQL server
c. Several tools for designing like UML, draw.io.
Hardware requirements
a. Minimum size of hard disk must be 2GB.
b. Minimum 2GB of RAM.
c. Processor of either 32 or 64-bit.
d. Display resolution of 1024*468.
ER diagram
It is an abbreviation for entity relationship chart. ER graph is the best top-down methodology
for planning the database for organizations. It is the theoretical graph that is utilized to
characterize the connection among the few entities. For constructing an ER chart first the
elements are distinguished and subsequently, the relations are found among the recognized
entities. Certain more information is included for every element called traits which
characterizes the attributes of an Entity (Datanamic, 2017).
Entity
It is an accumulation of items having comparative qualities that are, distinguished by an
association as having an unrestricted presence.
Attribute:
Each identified entity has particular qualities called as attributes.
7

Relationship
There is a connection among the entity that is recognized as a connection. The relation may
be of various cardinality proportions that are coordinated, many to many and many to one.
Figure 1 ER diagram
8

M1 Produce a comprehensive design for a fully functional system
Data Validation
Validation of information is required for the information execution, confirmation, and
approach for sorted out information (Dave, 2014). There are diverse kinds of approval
 Verification of the data type
 Verification of code
 Verification of imperative
 Verification of structure.
Figure 2 Data validation 1
9
Patient ID cannot
be null

Figure 3 Data validation 2
Data Dictionary
Data dictionary is a lattice of information description. It is an arrangement of information that
is stored in the database to make them meaningful. It is utilized to extricate the important
information from the showed data. Information dictionary includes diverse components that
are;
 Table name
 Attribute
 Type of attribute and
 Attribute description (Agiledata, 2017).
Table 1 Data dictionary
Table name Attribute Data type Description PK/FK
Vet