Database Design, Development, and Testing for VET Solution: Report

This report presents a comprehensive analysis of a relational database system designed for VET Solution, encompassing the design, development, and testing phases. The report begins with an introduction to the problem, followed by the design phase, which includes the creation of an Entity-Relationship (E-R) diagram and data normalization to ensure data integrity and reduce redundancy. The development phase focuses on the implementation of the database system, including table creation and the use of SQL queries to retrieve and manipulate data. The report then details the testing phase, where the system is tested against user and system requirements, and concludes with the production of technical and user documentation. This report is a complete guide to relational database design and implementation, including all necessary components, such as data validation, data dictionaries, and comprehensive documentation.

Database Design Assessment

Table of Contents
Introduction................................................................................................................................2
LO1. Use an appropriate design tool to design a relational database system for a substantial
problem......................................................................................................................................3
P1. Design a relational database system using appropriate design tools and techniques.......3
M1. Produce a comprehensive design for a fully functional system......................................5
LO2. Develop a fully functional relational database system, based on an existing design.......9
P2. Develop the database system............................................................................................9
Relational Database Management System:.........................................................................9
M2. Implement a fully functional database system..............................................................12
P3. Implement a query language into the relational database system..................................14
M3. produce appropriate management information.............................................................18
LO3. Test the system against user and system requirement....................................................19
P4. Test the system against user and system requirements..................................................19
M4. Assess the effectiveness of the testing, including an explanation of the choice of test
data used...............................................................................................................................23
LO4. Produce technical and user documentation.....................................................................26
P5. Produce technical and user documentation....................................................................26
M5. Produce technical and user documentation...................................................................31
Conclusion................................................................................................................................32
References................................................................................................................................33
List of figure
Figure 1 E-R Diagram For the VET Solution............................................................................7
Figure 2 Validation Of table Animal.........................................................................................7
Figure 3 Structure of the Database...........................................................................................11
Figure 4 Table View Of the table Animal................................................................................12
1

Figure 5 Table View of the table Appointment.......................................................................12
Figure 6 Table View of the table Branch.................................................................................12
Figure 7 Table View of the table Doctor.................................................................................13
Figure 8 Table View of the table Drugs...................................................................................13
Figure 9Table View of the table Prescription..........................................................................13
Figure 10 Table View of the table Owner................................................................................14
Figure 11 Table View of the table Staff...................................................................................14
Figure 12 table View of Query 1..............................................................................................16
Figure 13 table View of Query 2..............................................................................................17
Figure 14 table View of Query 3..............................................................................................17
Figure 15 Table View of Query 4............................................................................................18
Figure 16 table View of Query 5..............................................................................................18
Figure 17 table View of Query 6..............................................................................................19
Figure 18 table View of Query 7..............................................................................................19
Figure 19 table View of Query 8..............................................................................................20
Figure 20 Test case 1...............................................................................................................21
Figure 21 Test Case 2..............................................................................................................22
Figure 22 Test case 3................................................................................................................22
Figure 23 Test case 4................................................................................................................22
Figure 24 Test case 5................................................................................................................23
Figure 25 Test case 6................................................................................................................23
Figure 26 Test case 7................................................................................................................24
Figure 27 Data Validation of Animal.......................................................................................25
Figure 28 Appointment...........................................................................................................25
Figure 29 Data Validation of Branch.......................................................................................25
Figure 30 Data Validation of Doctor.......................................................................................26
Figure 31Data Validation of Drug...........................................................................................26
Figure 32 Data Validation of Owner........................................................................................26
Figure 33 Data Validation of Prescription..............................................................................27
Figure 34 Data Validation of Staff...........................................................................................27
Figure 35 Query to create table Animal...................................................................................28
Figure 36 Query to create table Appointment..........................................................................28
Figure 37Query to create table Branch....................................................................................28
Figure 38 Query to create table Doctor....................................................................................29
2

Figure 39 Query to create table Drug.......................................................................................29
Figure 40 Query to create table Owner....................................................................................29
Figure 41 Query to create table Prescription............................................................................30
Figure 42 Query to create table Staff.......................................................................................30
Figure 43 Inset query for Animal.............................................................................................30
Figure 44 Inset query for Appointment....................................................................................30
Figure 45 Inset query for Branch.............................................................................................31
Figure 46 Inset query for Doctor..............................................................................................31
Figure 47 Inset query for Drug.................................................................................................31
Figure 48 Inset query for Owner..............................................................................................32
Figure 49Inset query for Prescription......................................................................................32
Figure 50 Inset query for Staff.................................................................................................32
Figure 51 Inset query for data validation................................................................................32
3

Introduction
This report presents the relational database system with the design the ER-diagram,
normalization, by using the appropriate tool and technology. In this report, the database
design, development and testing is presented. The VET solution is the company that provides
the services for the pet animals by registering them and stores the data or information in the
database. This designed database is fully functional, with designed the tables to store the
database and query to find the specific information from the database. This report is divided
into the four parts where the first part is VET solution database designing, the second part is
present the database development with implement the tables and queries, third part presents
the testing and fourth part shows the user and technical documentation.
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 design and develop the relational database management system for VET Solution’s
Company, firstly a blueprint is made on an appropriate designing tool. Here, we make an E-R
diagram which may brief us with the overall architecture of the relational database system.
Database management system has become an urgent requirement in VET Solution’s
Company as the company is growing exponentially. The patients and staff are increasing, so
to keep their records this database system is a proper solution. Also, as VET Solution
Company is spreading in other countries too, the database system will help it keep a
persistent record.
Nowadays, the role of database management system has become crucial in this industry. As
the data is changing and evolving at a very fast rate, it has become important to record,
update and track this data to the patient’s satisfaction. Gathering data is helping healthcare
industry to achieve their goals in a systematic and strategic manner (IJAERD, 2017).
These database management systems are dependent on programming languages called
structured query languages. This language is used to create, update and store the data in the
database. SQL and MySQL servers allow outside program to query and get particular
information. The most important function of database management system is that they allow
different data tables to relate to one another (Allen, 2002).
User Requirements:
 A centralized database should be able to record all the information of its patients,
staff, and doctors.
 The database in making must be able to record all the information of the patient like
name, owner, type of animal etc..
 The database should also have all the information of the doctor and the owner of the
patient (animal).
 It should contain the treatment and medication given to the patient.
5

 User-friendly statistical programs should be there.
 Software and analysis program should efficiently query the databases.
 Onsite IT technical support should be provided.
 There is a need for database warehouse (Gies, 2018).
System Requirements:
 A primary live-link installation.
 A relational database server like SQL server.
 An Http Server like Apache.
 A web browser which may work as a client.
 The database should be fast and efficient.
 It should be designed in a way that the whole VET Solution can rely on it for basic
information (Gregorieff, 2010).
E-R Diagram (Entity-Relationship Diagram)
An E-R diagram is a model that shows the relationship between different entities. It illustrates
the logical structure of the database. It tells how the information system is related.
The E-R diagram consists of 5 main components:
1. Entities: They are represented by a rectangle. It is an object about which we want to
store information.
2. Actions: They are represented by a diamond. It shows the relationship two or more
entities share (Fahad, 2008).
3. Attributes: Attributes are represented by ovals. The attribute describes additional
information of an entity.
4. Lines: Solid connecting lines which connects one object to another to create a
relationship.
5. Cardinality: It shows how many instances of one activity relate to how many
instances of another activity (Chen, 2007).
6

Figure 1 E-R Diagram For the VET Solution
M1. Produce a comprehensive design for a fully functional
system
Data Validation
Data Validation is an automatic computer check that ensures the data entered in the database
is sensible. In SQL Server, the constraint check identifies acceptable column values for data
in a row within a SQL Server. It ensures that the data the program is operating on is clean and
correct (Haverkamp, 1995).
7

Figure 2 Validation Of table Animal
We do not need an incorrect data as it makes decision making difficult for the company.
Good analytic can only happen with a good quality and clean data. Incorrect data is of no use.
Validation also ensures accuracy and reliability (Ioffe, 2015).
Data Dictionary
A Data Dictionary in a database management system is the set of tables which used to store
information about a database. It contains information about the database objects like tables,
index, columns, datatypes etc.. Basically, it contains the metadata about the database. In SQL
Server, it is used to fire queries and it updates itself whenever there is any change in the
database (Jain, 2005).
Table Name Attributes Data Type Description PK/FK
Animal AnimalId Int Unique id of the animal PK
AnimalName Varchar(20) Name of the animal.
specificTrait Varchar(20) Speciality like color
location Varchar(20) Where it resides at present
Appointmen
t
AppointmentId Int Aunique id of appointment PK
DoctorId Int Unique id of a doctor. FK
OwnerId Int Unique id of Owner. FK
AnimalId Int Unique id of animal FK
Branch BranchId Int Unique id of branch PK
BranchLocation Varchar(50) Location of the branch
BranchContact Long Contact number of the branch
8

StaffId Int Unique id of staff FK
Doctor DoctorID Int Unique id of the doctor PK
DoctorName Varchar(20) Name of the doctor
DoctorSalary Long Salary of the doctor
DoctorContact Long Contact number of the doctor
BranchId int Unique id of the branch FK
Drug DrugId Int Unique id of the drug PK
DrugName Varchar(50) Name of the drug
DrugCategory Varchar(50) Category of the drug
PrescriptionId Int Unique id of the prescription FK
DoctorId int Unique id of the doctor. FK
Owner OwnerId Int Unique id of the owner. PK
OwnerName Varchar(50) Name of the owner.
OwnerContact Long Contact number of the owner.
AnimalId Int Unique id of the animal. PK
Prescription PrescriptionId Int Unique id of the prescription. FK
OwnerId Intt Unique id of the owner. FK
AnimalId Int Unique id of the animal. FK
DoctorId Int Unique id of the doctor. Fk
Cost Int Cost of the prescription.
Date Date Date on which the prescription was
written.
Staff StaffId Int Unique id of the owner. PK
StaffName Varchar(50) Name of the staff members.
StaffAge Int Age of the staff members.
StaffNumber Long Contact number of the staff member.
StaffAddress Varchar(100
)
Address of the staff member.
BranchId int Unique id of the branch. FK
Data Normalization
9

Data Normalization is a technique to organize tables in a manner as to reduce redundancy and
dependency of the data. Using this technique, we break down big tables into smaller chunks
of tables and define the relationship between these tables (Halvey, 2006).
Database Normal Forms as of now are:
1st Normal Form
2nd Normal Form
3rd Normal Form
BCNF
4th Normal Form
5th Normal Form
Un- Normalization
AnimalId
AnimalName
specificTrait
Location
AppointmentId
BranchId
BranchLocation
BranchContact
DoctorId
DoctorName
DoctorContact
DoctorSalary
DrugId
DrugCategory
DrugName
OwnerId
OwnerName
OwnerContact
PrescriptionId
10

Cost
Date
StaffId
StaffName
StaffAge
StaffContact
StaffAddress
1st Normal Form
Doctor Animal Staff
DoctorId AnimalId StaffId
DoctorName AnimalName StaffName
DoctorContact Specifictrait StaffContact
DoctorSalary Location StaffAge
Cost DrugId Staffaddress
Date AppointmetId OwnerName
prescriptionIid Drugcategory BranchLocation
DrugName BranchId
BranchContact
OwnerId
OwnerContact
2nd Normal Form
Doctor Animal Prescription Drug Branch
DoctorId AnimalId PrescriptionId DrugId BranchId
Doctorname AnimalName Cost Drugname Branchname
DoctorContact specificTrait Date DrugCategory BranchContact
Doctorsalary location StaffId DoctorId StaffAddress
Cost Staffname BranchId
Date StaffNumber
11