VET Solutions Database: Design, Implementation & Documentation

This project details the design, development, and testing of a relational database system for VET Solutions, a veterinary company. The project begins with the design phase, utilizing ER diagrams and data normalization techniques to create a robust database structure. MS SQL Server 2014 is used for the implementation, with comprehensive table designs and relationships established. The project includes the creation of SQL queries for data retrieval and manipulation, along with considerations for system security and database maintenance. A thorough test plan is developed and executed to validate the database against user and system requirements. Finally, both technical and user documentation are produced to ensure the system is well-understood and maintainable. This database aims to improve data management and communication for VET Solutions, enabling efficient handling of staff details, patient records, and appointment scheduling.

Database Assignment

Table of Contents
Introduction...........................................................................................................................................4
LO1 Use an appropriate design tool to design a relational database system for a substantial problem
...............................................................................................................................................................5
P1. Design a relational database system using appropriate design tools and techniques.................5
M1 produce a comprehensive design for a fully functional system which includes interface and
output designs, data validations, data normalization and data dictionary........................................7
LO2 Develop a fully functional relational database system, based on an existing system design.......14
P2. Once the ERD designs in P1 were accepted you have been asked to develop the database
system using MS SQL Server 2014...................................................................................................14
P3. Produce a query language, with queries across multiple tables for the database.....................19
M2 Implement a fully functional database system which includes system security and database
maintenance....................................................................................................................................24
M3: Assess whether meaningful data has been extracted from the database to produce
appropriate management system...................................................................................................25
LO3 Test the system against user and system requirements:..............................................................27
P4 Create a test plan to test the database:......................................................................................27
M4 Assess the validation of testing and the choices made for the testing purpose........................30
LO4 Produce technical and user documentation.................................................................................31
P5. Produce fully technical and user documentation for your designed database for VET SOLUTION
Company..........................................................................................................................................31
M5 you should produce technical and user documentation for a fully functional system..............37
Conclusion:..........................................................................................................................................38
References...........................................................................................................................................39
List of tables
Table 1 Data dictionary........................................................................................................................10
Table 2 un-normalized.........................................................................................................................11
1

Table 3 1 NF.........................................................................................................................................11
Table 4 2 NF.........................................................................................................................................12
Table 5 3NF..........................................................................................................................................12
Table 6 Test cases................................................................................................................................27
List of figures
Figure 1 E-R diagram.............................................................................................................................7
Figure 2 prescription table design view.................................................................................................8
Figure 3 owner table design view..........................................................................................................8
Figure 4 branch table design view.........................................................................................................8
Figure 5 appointment table design view...............................................................................................9
Figure 6 animal table design view.........................................................................................................9
Figure 7 Staff table design view.............................................................................................................9
Figure 8 relational database for VET solutions...................................................................................14
Figure 9 Prescription table...................................................................................................................15
Figure 10 Owner Table........................................................................................................................15
Figure 11 Branch Table........................................................................................................................15
Figure 12 Appointment table...............................................................................................................16
Figure 13 animal Table........................................................................................................................16
Figure 14 Staff Table...........................................................................................................................16
Figure 15 Prescription table.................................................................................................................17
Figure 16 Owner table.........................................................................................................................17
Figure 17 Branch Table........................................................................................................................17
Figure 18 Appointment table...............................................................................................................18
Figure 19 animal Table........................................................................................................................18
Figure 20 Staff Table............................................................................................................................18
Figure 21 query 1 output.....................................................................................................................19
Figure 22 query 2 output.....................................................................................................................20
Figure 23 query 3 output.....................................................................................................................20
Figure 24 query 4 output.....................................................................................................................21
Figure 25 query 5 output.....................................................................................................................22
Figure 26 query 6 output.....................................................................................................................22
Figure 27 query 7 output.....................................................................................................................23
2

Figure 28 query 1 output.....................................................................................................................25
Figure 29 query 2 output.....................................................................................................................26
Figure 30 test case 1............................................................................................................................27
Figure 31 test case 2............................................................................................................................28
Figure 32 test case 3............................................................................................................................28
Figure 33 test case 4............................................................................................................................29
Figure 34 test case 5............................................................................................................................29
Figure 35 test case 6............................................................................................................................29
Figure 36 prescription table design view.............................................................................................31
Figure 37 owner table design view......................................................................................................31
Figure 38 branch table design view.....................................................................................................32
Figure 39 appointment table design view...........................................................................................32
Figure 40 animal table design view.....................................................................................................32
Figure 41 Staff table design view.........................................................................................................32
Figure 42 Prescription table.................................................................................................................33
Figure 43 Owner table.........................................................................................................................33
Figure 44 Branch Table........................................................................................................................33
Figure 45 Appointment table...............................................................................................................34
Figure 46 animal Table........................................................................................................................34
Figure 47 Staff Table............................................................................................................................34
Figure 48 relationship diagram............................................................................................................35
Figure 49 query 5 output.....................................................................................................................35
Figure 50 query 6 output.....................................................................................................................36
Figure 51 query 7 output.....................................................................................................................36
Figure 52 functional dependency........................................................................................................37
3

Introduction
Here we will design a database system for the VET solution of Cambridge. It is the veterinary
company that provides services and care for the animals. As the company is growing and has more
information to handle and update they require a much stronger database system for storing
information and altering or updating it on daily basis. The database would also help them to
communicate the information in a much better way. Therefore they asked to build a database for
the same, and it will be done using MS SQL 2014, that gives the best management authority to the
company. In this report, we will design the procedure of database, development and a proper
testing so they can update it on daily basis.
4

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
To create a database for VET Solution Company we first have to design it appropriately with all the
minute details included. This will be done using the designing tools which help in making the ER
diagram of the entities present in the company. With the growing branches of the company, it
becomes more necessary for them to have a proper database management system. With the day by
day increase in the staff and customers, creates a problem for handling data manually so they
decided to go for database management. They require adding and altering the staff details, patient
details etc for all the branches of a single database system (Coronel, and Morris, 2016).
What is the role of database management systems?
These are the software that allows the storage, retrieval, adding and deleting data. They are helpful
in performing modifying functions, organizing the given data in the user desired format. This makes
the task easier and maintenance of information and usage becomes easier. This also helps in
accounting and calculation processes. The records are easily maintained and transferred from one
place to other. In addition to this the problem of managing files is reduced (Witkowski, Dorman, and
Greenfield, 2014).
What are the user requirements?
 The database should be able to alter the information added to it.
 The database should record all the details of the pets and manage them as needed.
 It should not only cover the details of the pet but also the pet owner.
 The database should have all the information regarding the staff working in the company.
 It should be helpful in managing the invoices given for the treatment of pets to the pet
owners.
 It should have a section for all the prior appointments taken in all the clinics.
 It should also add the details of the treatment and diagnosis done to treat the patient and
what medicines or prescription was given and by whom it was treated (lechaamwe.weebly,
2017).
5

To fulfill all the above requirements we need to build a fast performing management system that
has appropriate data and works effectively and is user-friendly. For better efficiency, it should have
distinctive identities for the name, contact address, color and other details. For designing such a
system we require a 64-bit processor, storage or hard disk of at least 2 GB, high RAM speed, and a
good resolution display. Also, we require a user-friendly operating system such as Windows, A MS
SQL server, and tools like draw.io for making ER diagrams (Charron, Gilbertson, Konik, et.al, 2017).
ER diagrams:
It stands for entity relationship diagrams. It is basically a connection between several different
entities represented using flowcharts and shapes. For this, we need to find all the entities from the
given data first and then the connection between them. Also, there are several attributes for a single
entity, which defines the characteristics of that entity (1keydata, 2017).
The entity is a group of objects having similar characteristics that is, identified by an organization as
having a liberated existence. They can be said as the main headings of any given data under which
we have several subheadings known by attributes. Every entity has specific characteristics called an
attribute.
There is a relation among the entities which is known as a relation. The relation can be of different
cardinality ratio that is one to one, one too many, many to many (Pejathaya, Talluri, and Bhide,
2017).
6

Figure 1 E-R diagram.
M1 produce a comprehensive design for a fully functional system which includes
interface and output designs, data validations, data normalization and data
dictionary.
Data validations:
It is a process for ensuring that the data inserted is correct and no unrequired stuff is added to it, this
is a data cleansing process to ensure a good quality of data. It improves the performance of the
database system. It is of different types:
 Authentication of data type
 Authentication of code
 Authentication of constraint
 Authentication of the structure.
Design view of tables:
Comprehensive designs of the database made using SQL are as follows:
7

Figure 2 prescription table design view
Figure 3 owner table design view
Figure 4 branch table design view
8

Figure 5 appointment table design view
Figure 6 animal table design view
Figure 7 Staff table design view
Data dictionary:
It is a document that contains all the database or useful information with some exact meanings. It
can be defined with a specific structure or matrix definition. Data dictionary has different elements
in it such as elective, attribute name and attribute type.
9

Table 1 Data dictionary
Table Name Attributes Data Type Description PK/FK
Animal AnimalId Int Animal distinct identity PK
AnimalName Varchar(20) Animal name
Address Varchar(20) Address where animal live
Contact Numeric(18, 0) Animal owner contact
detail
Appointment AppointmentId Int Appointment distinct
identity
PK
Date date Date of the appointment
OwnerId Int Owner distinct identity. FK
AnimalId Int Animal distinct identity FK
Branch BranchId Int Branch distinct identity PK
BranchName Varchar(50) Branch name
BranchLocation Varchar(50) Branch address
Contact Long Branch contact details
StaffId Int Staff distinct identity FK
Owner OwnerId Int Owner distinct identity. PK
OwnerName Varchar(50) Owner name to which
animal belongs too.
OwnerAddress Varchar(50) Owner contact details.
AnimalID int Animal distinct identity FK
Prescription PrescriptionId Int Prescription number. PK
staffId Int Staff distinct identity FK
AnimalID Int Animal distinct identity FK
BranchID Int Branch distinct identity. Fk
Cost Int Prescription drug total
amount.
DrugName Varchar(50) Name of the drug in
prescription.
Staff ID Int Staff distinct identity PK
StaffName Varchar(50) Staff member name
contact Numeric(18,0) Staff contact details
Address Varchar(50) Staff location address
StaffRole Varchar(50) Role of the staff
Data normalization:
10

In order to increase the integrity of data and reduce data repetition or redundancy, we organize the
data in form of rows and columns or a table format so that they can be easily viewed and searched.
This can be done by sorting the un-normalized data (1keydata, 2017).
Un- normalized data:
Table 2 un-normalized
AnimalId
AnimalName
Address
AppointmentId
BranchId
BranchLocation
BranchName
Contact
OwnerId
OwnerName
OwnerAddress
PrescriptionId
Cost
Date
Drug name
Id
StaffName
StffRole
1 NF
Table 3 1 NF
Owner Staff
Branch
OwnerId ID AppointmentId
OwnerName StaffName Date
OwnerAddress contact OwnerId
AnimalID Address DrugName
AnimalName BranchId
Address BranchName
Contact BranchLocation
Contact
11