VET Solutions Database System: Design, Implementation, and Testing

This report details the design, implementation, and testing of a relational database system for VET Solutions, a veterinary company. The system utilizes MS SQL Server and SQL language to manage data related to animals, appointments, bills, branches, owners, prescriptions, and staff. The report includes an Entity-Relationship Diagram (ERD), data normalization, query examples, test cases, and user and technical documentation. The database is designed to be secure, robust, and fully functional, meeting the requirements of VET Solutions.

DATABASE ASSIGNMENT

Contents
List of figures........................................................................................................................................1
List of tables..........................................................................................................................................2
Introduction...........................................................................................................................................3
LO1 Use an appropriate design tool to design a relational database system for a substantial problem. .4
P1 Design a relational database system using appropriate design tools and techniques.....................4
M1 Produce a comprehensive design for a fully functional system...................................................6
LO2 Develop a fully functional relational database system, based on an existing system design........11
P2 Develop and implement a fully functional database system using MS SQL Server....................11
M2 Implement a fully functional database system...........................................................................13
P3 Produce a query language, with queries across multiple tables produced earlier........................13
M3 Produce appropriate management information..........................................................................18
LO3 Test the system against user and system requirements................................................................19
P4 Create a test plan to test the database:........................................................................................19
M4. Assess the effectiveness of the testing......................................................................................21
LO4 User and Technical Documentation:............................................................................................22
P5 Produce technical and user documentation.................................................................................22
M5: Produce fully functional system...............................................................................................30
Conclusion...........................................................................................................................................32
References...........................................................................................................................................33
List of figures
Figure 1 example of a key constraint.....................................................................................................4
Figure 2 ER diagram for VET solution..................................................................................................5
Figure 3 table of animal.........................................................................................................................8
Figure 4 table of appointment................................................................................................................8
Figure 5 table of the bill........................................................................................................................9
Figure 6 table of branch.........................................................................................................................9
Figure 7 table of owner..........................................................................................................................9
Figure 8 table of prescription...............................................................................................................10
Figure 9 table of staff...........................................................................................................................10
Figure 10 query 1 output......................................................................................................................11
Figure 11 output for query 2................................................................................................................11
Figure 12 output of query 3.................................................................................................................12
Figure 13 output for query 4................................................................................................................12
Figure 14 output of query 5.................................................................................................................13
Figure 15 output for query 6................................................................................................................14
Figure 16 output of query 7.................................................................................................................14
Figure 17 output of query 8.................................................................................................................15
1

Figure 18 test case 1............................................................................................................................16
Figure 19 test case 2............................................................................................................................17
Figure 20 test case 3............................................................................................................................17
Figure 21 test case 4............................................................................................................................17
Figure 22 test case 5............................................................................................................................17
Figure 23 test case 6............................................................................................................................18
Figure 24 test case 7............................................................................................................................18
Figure 25 test case 8............................................................................................................................18
Figure 26 animal table.........................................................................................................................19
Figure 27 appointment table................................................................................................................19
Figure 28 bill table...............................................................................................................................20
Figure 29 branch table.........................................................................................................................20
Figure 30 owner table..........................................................................................................................20
Figure 31 prescription table.................................................................................................................21
Figure 32 staff table.............................................................................................................................21
Figure 33 query 1.................................................................................................................................22
Figure 34 query 2.................................................................................................................................22
Figure 35 query 3.................................................................................................................................23
Figure 36 query 4.................................................................................................................................23
Figure 37 query 5.................................................................................................................................23
Figure 38 query 6.................................................................................................................................24
Figure 39 query 7.................................................................................................................................24
Figure 40 query 8.................................................................................................................................24
Figure 41 ERD for VET Solution........................................................................................................25
Figure 42 design of the animal table....................................................................................................25
Figure 43 design of appointment table.................................................................................................26
Figure 44 design of bill table...............................................................................................................26
Figure 45 design of branch table..........................................................................................................26
Figure 46 design of owner table..........................................................................................................27
Figure 47 design of the prescription table............................................................................................27
Figure 48 design of staff table.............................................................................................................27
Figure 49 diagram of functional dependency.......................................................................................28
List of tables
Table 1 Data Dictionary table................................................................................................................7
Table 2: First level normalized tables....................................................................................................9
Table 3- Second level normalized tables...............................................................................................9
Table 4- Third level normalized tables................................................................................................10
Table 5 test case table..........................................................................................................................19
2

Introduction
This database system is composed and produced for VET Solutions by ULKOM Ltd in which
it fulfills the all requirement of client and system is fully functional with the security of the
database. The language SQL is used to develop the queries and this document will present
some examples of queries with their design and testing part also to ensure that this database
system will work successfully. All the required validation will also prove in this report and
satisfy the requirements of the client and utilize concepts of DBMS as functional
dependencies and key constraints mainly.
3

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.
Roles and specification of VET solution Database System:
A course of action of VET arrangement in a database system is used to administer whole data inside
an association by securing enormous measures of data. The relational database is used by applications
of programming in which It decreases data redundancy and presents data in an inducing way that
assistants incapable fundamental authority. It improves the execution of a structure and gives advance
worked in functionalities of examining or analyzing. The VET arrangement for database tables
likewise gives assistance to keep up information data by putting away in database storage to get
whenever required through application level.
Relational Database tool and some techniques:
The most used database is Relational Database in which it perceives sort of DBMS. In this database,
all record is maintained in the form of tables. MS SQL Server is utilized for orchestrating data in
tables for application programming. It is generally called as Sequel or “Standard Query Language”
mainly known by SQL. The relational l databases oftentimes drive the association essential and web-
enabled applications fundamental for gaining ground in an astoundingly forceful market. It is intended
to pass on the reason for building and works with the social information parts and engaging to make
and use relational databases in the surrounding or in the technical ground (Selinger, P.G., Astrahan,
M.M., Chamberlin, D.D., Lorie, R.A. & T.G., 1988).
Logical Design for Relational Database:
To design a VET course of action, requirement of logical data model initially to design physical
database. Additionally, the predictable data indicate ending of a sensible data information. Besides,
unique sort of data demonstrate starts with the planning of data show. The attributes, entities and
objects or methods are required to make a logical database on formalizing. Another basic endeavor of
logical database is to ensure that the model elements are balanced by credits that uncommonly
identify with them by providing key constraint as primary key which defines the property of
uniqueness.
ER- Diagram
The Entity relationship diagram technique is utilized for representation of database models in a figure
which describes all the entities and their attributes with relations easily and it’ll help to the developer
for implement database by seeing the entities relations.
Entity
An entity is main classes or actors of a database system which consists of attributes or properties and
can share with other entity using key constraints property applied to attributes.
Attribute
An attribute is a property of entity or classes which are utilized in the database to develop a system for
storing data information based on attributes. According to this database system, attributes are Animal
4

Id, the Animal category of animal class, Appointment number, Appointment date, Branch name,
Prescription number and Employee id of appointment class and many more class is there with their
attributes.
Relationship
In the database, the relationship is interaction or connection among various class tables or entities and
the symbol of relationship is of diamond shape.
Data Elements
The information which will be put for specific attributes to store in the database with validating data
on the basis of its data types.
Data Types
Data types are predefined functions or variables on which the data information inserted in fields is
only based on that then it’ll accept to store in the database.
Indexing
The property of indexing is utilized for searching element in the database by providing a key to that
attribute or variable. It is used in hashing to search the value in the bucket, in programming, it can be
implemented using pointers which will help to locate the destination.
Primary/Foreign Key:
These are the part of key constraints in which primary key is utilized for providing uniqueness on
defined value, for example, the ID of the student which can’t be same, the foreign key is utilized for
providing a fetching feature from another table on which primary key is also defined which work as a
foreign key (Burroughs, T.K., Gansemer, S.J., Lee, W.D., Voldal, E.E., Roger & C.A, 1999).
Figure 1 example of a key constraint
Entity relationship diagram for VET Solution database
Entity-relationship diagram and data flow diagram are to be made for a veterinary company named as
VET Solution. This would help VET solution in designing relational database system to define the
attributes and entities with the appropriate relationship among them. Below there is ERD for the VET
solution (Briand, H., Habrias, H., Hue, J.F & Y, 1985).
5

Figure 2 ER diagram for VET solution
M1 Produce a comprehensive design for a fully functional system
Data Integrity
The data information which is stored in the database should be accurate as didn’t find corrupt or fake
and reliable too. Because these features help to provide a fully functional system for implement or
development. The data integrity is basically a term used for providing security to data information and
prevent from editing or accessing form unauthorized user.
Data Validation
The data validation is used to validating the data part on entering in the database and it is tested in
testing part with some cases. In the database, validation can be accomplished by applying key
constraints on variables or attributes as they are Not NULL. UNIQUE, PRIMARY KEY, FOREIGN
KEY, CHECK and few more utilized in a database on development. The primary key is a
combination of not null and unique key constraint and it follows the property of both, not null
constraint is utilized on an attribute for not accepting blank on that field i.e, empty, the unique key
constraint is for accepting totally different id.
Data Dictionary
The data dictionaries are utilized for providing a short name to original name, basically, it provides a
description of data elements as its type, information, key constraints and attributes of the
entity. The data dictionaries only provide a view over it as it not contain original data
information. If data dictionary is not there then accessing to database can’t be possible for the
database system.
Table 1 Data Dictionary table
Name of table Attributes Description Type of
Data
Key
constraints
Animal Animal_id Used for Varchar() Primary
6

providing id’s
Animal_category Defining
category of
animal
Varchar()
Specific_trait Provide any
special traits of
the animal
Varchar()
location Location of
animal
Varchar()
Appointment Appointment_number Provides
number of
Appointment
Varchar() Primary
Appointment_date Provide date of
Appointment
Varchar()
Branch_name Provides the
name of the
branch for
Appointment
Varchar() Foreign
Prescription_number Provides
prescription
number for
appointment
Varchar() Foreign
Employee_id Provide id of
an employee
for
appointment
Varchar() Foreign
Bill Bill_number Provides bill
number
Varchar() Primary
Bill_date Provides Date
of bill
Varchar()
Branch_name Provides
branch name
for bill
Varchar()
Appointment_number Provides
appointment
number for bill
Varchar() Foreign
Employee_id Provides id of
an employee
for the bill
Varchar() Foreign
Branch Branch_name Name of
branch
Varchar() Primary
Contact_number Provide contact
number
Varchar()
Employee_id The id of an
employee for
branch
Varchar() Foreign
Address Address of
branch
Varchar()
Owner Owner_batch batch of owner Varchar() Primary
Animal_id The id of
animal for the
owner
Varchar() Foreign
Contact_number Contact
number of
Varchar()
7

owner
location Location of
owner
Varchar()
Prescription Prescription_number Number of
prescription
Varchar() Primary
Animal_id Id of animal
for prescription
Varchar() Foreign
Prescription_date Date of
prescription
Varchar() Foreign
Owner_batch Batch of owner
for prescription
Varchar() Foreign
Staff Employee_id The id of an
employee for
staff
Varchar() primary
Contact_number Contact
number of staff
Varchar()
Staff_role Role of staff Varchar()
Salary Salary of staff Varchar()
Address Address of
staff
Varchar()
Data Normalization
The data normalization is required in any database for providing a fully functional and simple, secure,
robust database by fulfilling the requirements of clients which will store the data in the database.
Normalization has some techniques to make simpler the database like normal forms (1, 2, 3, 4, 5) but
1, 2, 3 are mainly used to reduce the database and make it simpler for developers (Bernstein & P.A.,
1976).
8

1 NF
For the database tables to be in first normal form, the attributes should not be composite or multi-
valued or derived.
Table 2: First level normalized tables
Animal Owner Staff
Animal_id Owner_batch Employee_id
Animal_category Animal_id Contact_number
Specific_trait Contact_number Staff_role
location location Salary
Prescription_number Address
Prescription_date Branch_name
2NF
There are many partial dependencies in the table which needs to be removed. The second level
normalized tables are as follows:
Table 3- Second level normalized tables
Animal Owner Branch Staff Appointment
Animal_id Owner_batch Branch_name Employee_id Appointment_numbe
r
Animal_category Animal_id Contact_numbe
r
Contact_numbe
r
Appointment_date
Specific_trait Contact_numbe
r
Employee_id Staff_role Branch_name
location location Address Salary Prescription_number
Prescription_numbe
r
Address Employee_id
Prescription_date Bill
9

3NF
The transitive dependencies need to be eliminated for the tables to be the third level of normalized.
Table 4- Third level normalized tables
Animal Owner Prescription Branch Staff Appointment Bill
Animal_id Owner_ba
tch
Prescription_
number
Branch_na
me
Employee
_id
Appointment_
number
Bill_number
Animal_ca
tegory
Animal_id Animal_id Contact_n
umber
Contact_n
umber
Appointment_
date
Bill_date
Specific_tr
ait
Contact_n
umber
Prescription_
date
Employee
_id
Staff_role Branch_name Branch_name
location location Owner_batch Address Salary Prescription_n
umber
Appointment_
number
Address Employee_id Employee_id
10

LO2 Develop a fully functional relational database system, based on an
existing system design
P2 Develop and implement a fully functional database system using MS SQL
Server
The database system utilizes the relational concept to develop a fully functional system which stores
the data information of entities and its attributes in the form of columns & rows. The database utilizes
the features of SQL language as data definition, data manipulation and more in which they consist of
some functions as select, update, alter, modify, truncate, drop, insert, create or many more. The below
figures will show the developed database system using MS SQL SERVER 2014 which generates the
database in the more secure way and robust in nature (Mistry, R & S., 2014).
Animal: The below table consist of attributes, data types, and a checkbox for null.
Figure 3 table of animal
Appointment: The below table consist of attributes, data types, and a checkbox for null.
Figure 4 table of appointment
Bill: The below table consist of attributes, data types, and a checkbox for null.
11

Figure 5 table of the bill
Branch: The below table consist of attributes, data types, and a checkbox for null.
Figure 6 table of branch
Owner: The below table consist of attributes, data types, and a checkbox for null.
Figure 7 table of owner
Prescription: The below table consist of attributes, data types, and a checkbox for null.
12

Figure 8 table of prescription
Staff: The below table consist of attributes, data types, and a checkbox for null.
Figure 9 table of staff
The above tables of database system describe their attributes and also showed what type of data they
will accept while inputting from the user.
M2 Implement a fully functional database system
The M2 is achieved successfully and satisfactorily as the database is designed and developed which
fulfills the requirements of the customer to store the information with more security and maintaining
the database by not accepting undefined values as per the data types and key constraints. The values
are entered in specific fields on the basis of its data type otherwise it’ll show an error or not accept the
value and don’t store in the database.
P3 Produce a query language, with queries across multiple tables produced
earlier.
The below queries performed on the basis of database requirement and executed successfully which
determined by seeing screenshots of the executed query and provides data information fetched from
database storage. Now see the queries which are developed using SQL language in MS SQL
SERVER for the given database.
Query 1: fetching all data of the animal table.
13

Select * from Animal;
---- display animal table details
Figure 10 query 1 output
Query 2: fetching all data from table staff where the salary is $ 12000.
Select * from Staff
WHERE Salary= $12000;
---display Employee having Salary $12000
Figure 11 output for query 2
Query 3: fetching data of maximum salary from staff table whose employee id is 202.
SELECT MAX(Salary)
FROM Staff
14

Where Employee_Id = '202'
---display Maximum salary of Employee
Figure 12 output of query 3
Query 4: fetching minimum salary form staff table whose address is Bristol.
Select MIN (Salary)As MinSalary
From Staff where Address= 'Bristol'
---display Minimum salary of Employee
Figure 13 output for query 4
Query 5: fetching data for animal id, prescription date, owner batch from different tables with the
condition of id must match.
SELECT Animal.Animal_Id, Prescription.Prescription_Date, Owner.Owner_Batch
FROM Animal, Prescription, Owner
where Prescription.Animal_Id=Owner.Animal_Id;
---display Animal Id, Prescription date, and owner batch
15

Figure 14 output of query 5
Query 6: fetching details from the prescription table and uses inner join for fetch data of another table.
SELECT Prescription.Prescription_Date, Owner.Owner_Batch, Prescription.Animal_Id
FROM Prescription
INNER JOIN Owner ON Prescription.Animal_Id=Owner.Animal_Id;
---Inner Join
16

Figure 15 output for query 6
Query 7: fetch the data from a prescription table with an inner join on for a prescription date.
SELECT Prescription.Prescription_Number, Owner.Owner_Batch, Prescription.Prescription_Date
FROM Prescription
INNER JOIN Owner ON Prescription.Animal_Id=Owner.Animal_Id;
---Inner Join
Figure 16 output of query 7
Query 8: fetch all data of animal but in order for location.
SELECT * FROM Animal
ORDER BY Location;
---Table with Preference by Location
17

Figure 17 output of query 8
M3 Produce appropriate management information.
The M3 achieved successfully for management of database queries as seen clearly in above examples
of the query in which data is fetched in order without any error occur. The information of
management for queries can be accomplished by implementing database system properly by fulfilling
required requirements of the customer and develop in a simpler way.
18

LO3 Test the system against user and system requirements
P4 Create a test plan to test the database:
The below table describes all test cases and its rectification clearly which utilizes validation of data by
executing cases as (Appelt, D., Nguyen, C.D., Briand, L.C & N., 2014)
Table 5 test case table
S. No Test case Expected output Actual output
1 Delete statement violation in
appointment table
It’ll give an error for
conflicting with
reference constraint on
deleting.
It gives an error for
not violating the delete
command on reference
constraint.
2 Foreign key conflicts in the branch
table
It’ll give an error for
terminating the
statement as conflict
with a foreign key.
It shows an error for
not accepting values
due to the conflict in
the foreign key.
3 The foreign key violation in owner
table
It’ll give an error for
terminating the
statement as conflict
with a foreign key.
It shows an error for
not accepting values
due to the conflict in
the foreign key.
4 Create a table with all data fields for
staff
It’ll accept the
command and make a
table for staff with all
attributes.
Command accepted
successfully and
created a table for staff
with all attributes.
5 The primary key violation in animal
table
It’ll produce an error
for conflicting primary
key on insertion.
It shows an error for
conflicting primary
key on insertion.
6 The primary key violation in
appointment table
It’ll produce an error
for conflicting primary
key on insertion.
It shows an error for
conflicting primary
key on insertion.
7 Insertion in staff table for salary in
staff table
It’ll accept a command
for inserting salary of
staff.
It accepted the insert
command and 1 row
affected.
8 Truncating table or dropping in staff
table
It’ll truncate the table
and produce output as
command complete
successfully.
It gives output as
command completed
successfully.
Figure 18 test case 1
19

Figure 19 test case 2
Figure 20 test case 3
Figure 21 test case 4
Figure 22 test case 5
20

Figure 23 test case 6
Figure 24 test case 7
Figure 25 test case 8
M4. Assess the effectiveness of the testing
The M4 achieved successfully after performing test cases to check whether the database is correct or
not. The above test cases presented that database is developed in a proper way and met with the
requirement of customer and not accept the invalid commands or values which give surety of secure
database system and showed effective testing parts.
21

LO4 User and Technical Documentation:
P5 Produce technical and user documentation
User Documentation:
User requirement analysis is there to develop services and products which could meet the customers
and users expectations. It provides a precise explanation of the quality, functionality, and content
demanded by the prospective users. To identify user needs, the perspective of the user must result in
Functional and Non-functional requirements. Methods for the user requirement analysis are done with
the help of interview, focus group, analysis, observation, document analysis, questionnaire or
checklists. Interviewing is done with a series of questions for end-user with scope to expand on the
responses, Focus groups bring together stakeholders and cross-section of users in discussion,
competitor analysis is the comparison of the expected product from the existing systems and scenarios
are the detailed realistic examples explaining how users will carry out tasks in specified context.
Figure 26 animal table
Figure 27 appointment table
22
All data
stored
successfully.
Data of branch fetched
successfully.

Figure 28 bill table
Figure 29 branch table
Figure 30 owner table
23
Bill date entered
successfully.
Address stored
with employee id
for branch.
Location
stored
successfully.

Figure 31 prescription table
Figure 32 staff table
Query reports:
24
Animal id associated
successfully.
Salary entered
properly.

Figure 33 query 1
Figure 34 query 2
25
Query statement
with desired
output.
Query
statement
with
desired
output.

Figure 35 query 3
Figure 36 query 4
Figure 37 query 5
26
Query statement
with desired
output.
Query
statement with
desired output.
Query statement
with desired
output.

Figure 38 query 6
Figure 39 query 7
Figure 40 query 8
27
Query
statement with
desired output.
Query statement
with desired
output.
Query statement
with desired output.

Technical Documentation:
This database system is developed for VET Solutions Company by ULKOM LTD. for assisting the
organization. This documentation consists of designs of tables with specialized knowledge and
provides a functional dependency of this database system which enables configuring of entities related
to the requirement of the client for storing the data and stores in the database.
Animal
Animal_I d
Animal_Category
Specific_Trait
Location
Appointment
Appointment_Number
Appointment_Date
Branch_Name
Prescription_Number
Employee_I d
Bill
Bill_Number
Bill_Date
Branch_Name
Appointment_Number
Employee_I d
Branch
Branch_Name
Contact_Number
Employee_I d
Address
Owner
Owner_Batch
Animal_I d
Contact_Number
Location
Prescription
Prescription_Number
Animal_I d
Prescription_Date
Owner_Batch
Staff
Employee_I d
Contact_Number
Staff_Role
Salary
Address
Figure 41 ERD for VET Solution
The below figures are the designs of various tables used in this database system as:
Figure 42 design of the animal table
28

Figure 43 design of appointment table
Figure 44 design of bill table
Figure 45 design of branch table
29

Figure 46 design of owner table
Figure 47 design of the prescription table
Figure 48 design of staff table
M5: Produce fully functional system
Functional dependency
An FD implies functional dependency which is used in relational databases for determining relations
among various tables. It is a constraint utilized to make a relation between attributes as if P is
functionally dependent on Q then, attribute Q contain same elements as attribute P (Raju, K.V.S.V.N. &
A.K., 1988).
30

Figure 49 diagram of functional dependency
31

Conclusion
This document explains that every information is tried and approved after execution of this
framework. This framework is composed and produced for VET Solutions provided by ULKOM LTD
Company in Cambridgeshire who has encountered in electronic databases. This database system
concludes that all the information arrangement will store in the database after the approval or
validation of information part which is entered in determined fields must meet with the data types and
key constraint if applicable. This report additionally gives information about word references to
comprehend the elements and its properties and furthermore having ER Diagram and functional
diagram of the database system to represent all entities and its attributes with key constraints.
32

References
 Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A. and Price, T.G., 1988. Access path
selection in a relational database management system. In Readings in Artificial Intelligence and
Databases (pp. 511-522).
 Briand, H., Habrias, H., Hue, J.F. and Simon, Y., 1985, October. Expert system for translating an ER
diagram into databases. In Proceedings of the Fourth International Conference on Entity-Relationship
Approach (pp. 199-206). IEEE Computer Society.
 Bernstein, P.A., 1976. Synthesizing third normal form relations from functional dependencies. ACM
Transactions on Database Systems (TODS), 1(4), pp.277-298.
 Appelt, D., Nguyen, C.D., Briand, L.C. and Alshahwan, N., 2014, July. Automated testing for SQL
injection vulnerabilities: an input mutation approach. In Proceedings of the 2014 International
Symposium on Software Testing and Analysis(pp. 259-269). ACM.
 Raju, K.V.S.V.N. and Majumdar, A.K., 1988. Fuzzy functional dependencies and lossless join
decomposition of fuzzy relational database systems. ACM Transactions on Database Systems
(TODS), 13(2), pp.129-166.
 Burroughs, T.K., Gansemer, S.J., Lee, W.D., Voldal, E.E., Rogers, C.A. and Zaborowski, L.J.,
International Business Machines Corp, 1999. Schema mapping to a legacy table with primary and
foreign key support. U.S. Patent 5,956,725.
 Mistry, R. and Misner, S., 2014. Introducing Microsoft SQL Server 2014. Microsoft Press.
33