Relational Database System Design and Implementation for VET Solutions

This report presents the design and implementation of a relational database system for VET Solutions, a company providing veterinary services. The report covers the design process using ER diagrams, data normalization, and data dictionary creation. It details the implementation using MS SQL Server, including table creation, data types, and key constraints. The report also includes a comprehensive test plan to ensure the system's functionality and data integrity. Finally, user and technical documentation are provided to guide users and developers in understanding and utilizing the system.

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.......10
P2 Develop and implement a fully functional database system using MS SQL Server.....................10
M2 Implement a fully functional database system..........................................................................12
P3 Produce a query language, with queries across multiple tables produced earlier......................12
M3 Produce appropriate management information.......................................................................15
LO3 Test the system against user and system requirements...............................................................16
P4 Create a test plan to test the database:......................................................................................16
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...............................................................................................28
Conclusion...........................................................................................................................................29
References...........................................................................................................................................30
List of figures
Figure 1 example of a key constraint.....................................................................................................5
Figure 2 ER diagram for VET solution.....................................................................................................6
Figure 3 table of animal.......................................................................................................................10
Figure 4 table of appointment.............................................................................................................10
Figure 5 table of the holder.................................................................................................................10
Figure 6 table of medication................................................................................................................11
Figure 7 table of receipt......................................................................................................................11
Figure 8 table of branch.......................................................................................................................11
Figure 9 table of staff...........................................................................................................................11
Figure 10 query 1 output.....................................................................................................................12
Figure 11 output for query 2...............................................................................................................12
Figure 12 output of query 3.................................................................................................................13
Figure 13 output for query 4...............................................................................................................13
Figure 14 output of query 5.................................................................................................................14
Figure 15 output for query 6...............................................................................................................14

Figure 16 output of query 7.................................................................................................................14
Figure 18 test case 1............................................................................................................................17
Figure 19 test case 2............................................................................................................................18
Figure 20 test case 3............................................................................................................................18
Figure 21 test case 4............................................................................................................................19
Figure 22 test case 5............................................................................................................................19
Figure 23 test case 6............................................................................................................................20
Figure 24 test case 7............................................................................................................................20
Figure 25 test case 8............................................................................................................................21
Figure 26 animal table.........................................................................................................................22
Figure 27 appointment table...............................................................................................................22
Figure 28 holder table.........................................................................................................................22
Figure 30 medication table..................................................................................................................23
Figure 29 receipt table.........................................................................................................................23
Figure 31 branch table.........................................................................................................................23
Figure 32 staff table.............................................................................................................................23
Figure 33 query 1.................................................................................................................................24
Figure 34 query 2.................................................................................................................................24
Figure 35 query 3.................................................................................................................................24
Figure 36 query 4.................................................................................................................................24
Figure 37 query 5.................................................................................................................................24
Figure 38 query 6.................................................................................................................................25
Figure 39 query 7.................................................................................................................................25
Figure 41 ERD for VET Solution............................................................................................................26
Figure 42 design of the animal table...................................................................................................26
Figure 43 design of appointment table................................................................................................26
Figure 44 design of holder table..........................................................................................................27
Figure 45 design of medication table..................................................................................................27
Figure 46 design of receipt table.........................................................................................................27
Figure 47 design of the branch 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 test case table.........................................................................................................................16

Introduction
This report is prepared for VET solutions in which a system is required to maintaining the data
information properly and it can be accomplished by the database system. So this report determines
the point of the database which are used while implementing and developing a system. This report
provides knowledge for normalization, data dictionaries and various concepts of the database which
is helpful for the user to understand and analyze the working of the system. The testing is of queries
also provide with the report to ensure that the system is developed error-free and provide all
functionalities according to the requirements of the client. At last, the report will also explain the
functional dependency which helps to recognize the relations among various entities and determine
the key constraints.

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:
VET arrangement is generally used in a database system. & moreover, VET arrangement is mainly
used for entire data in an institute or association just by providing secure measures of all the
program data. This relational program database is basically used by the applications & advantages of
programming, in which data redundancy is decreased in a kind of way, which assists for fundamental
authority. This normally improves execution part for the structure & then it gives advancements in
functions of the program. VET arrangement for the database program tables assists for keeping
information & details for keeping it away in storage for getting it, whenever it is requiring to achieve
application level.
Relational Database tool and some techniques:
The most commonly used database is a relational database, in which it is used to perceive database
management system. In a relational database, all of the records are maintained in a table form. MS
SQL server is mainly utilized for finding the program data in a tabular form for application
programming. It is normally known as Sequel or ‘Standard Query language”. It is called as SQL.
Sometimes this relational database improves the association & web-enabled web applications just to
gain ground in porch market. It is mandatory to cross all the reasons for building & works for the
social details & information & to make it engaged, we use relational database across the
surroundings & on the technical ground.
Logical Design for Relational Database:
For designing a VET course, it requires data model, which should be logical. It is mainly used to
design a physical database. In this a predictable data basically indicates the end of the sensible
details or information. Data demonstrate generally starts with a rough plan for a data show. All the
entities & objects/methods are usually required just to make a database, which should be logical.
Another motto of logical database is to make assure, all the elements of model should be balanced
by the credits. These credits are normally used to identify all the key elements as primary key, which
defines all the properties of uniqueness.
ER- Diagram
ER diagram represents entity relationship diagrams which consist of entities, attributes along with
key constraints to recognize the value and useful for designing of the database in a proper manner.
The diagram of ERD describes below in this report.
Entity
An entity is a class name or table name which consist of various attributes along with key constraint.
Basically, the entity is the part of the database to recognize the table name.

Attribute
An attribute is a property of entities which help to understand the data values which is entered in
the database and it also contains key constraints to provide unique features while entering the data
information in a database system.
Relationship
Relationships are used to providing relation among various entity tables and they are 4 types as one
to one, one to many, many to one and many to many.
Data Elements
Data elements are useful for validation g the entered values in the database system as the user
enters the value to store and system will check for validating and verifying as valid values.
Data Types
Data types are the function which is predefined in any programming used for the link with the
variable to accept values if it matches the predefined format of the variable. For example int, char,
varchar, etc.
Indexing
Indexing is useful for recognizing the address of the variable and its values and enhances the
property of searching value in the database very quickly.
Primary/Foreign Key:
The primary key is a part of key constraint in which it provides unique values which must not be null
values and should be unique. And the foreign key is used for the linking table’s through primary key
and fetching data through the foreign key. Its data cannot be modified until the table is of referential
integrity (Burroughs, Gansemer, Lee, Voldal, Rogers & Zaborowski, 1999).
Figure 1 example of a key constraint
Entity relationship diagram for VET Solution database
ER diagram is used for making relations between various entries and provide key constraints to their
attributes which help to developer database very easily as it understands the entity relationship
diagram. This diagram determines all the entities attributes align with additional features like a key
constraint, weak entity or relationships among the various entity (Briand, Habrias, Hue & Simon,
1985).

Figure 2 ER diagram for VET solution
M1 Produce a comprehensive design for a fully functional
system
Data Integrity
Data integrity is remained for robustness of data and prevent unauthorized users to access or
update the stores data. So developers design a database with such properties to attain security of
database and provide a fully functional system which stores the information accurately and provide
access to authorized users for giving any details to customers.
Data Validation

Validation of data is necessary for the database which provides surety or guarantee of actual data
and properties of security as data doesn’t contain any errors or bugs. The data validation is attained
by performing testing on various test cases to determine the problems in the developed database
which ensures that database is secured and perfect according to the requirements of the client.
Data Dictionary
Data dictionary are used for analyzing the meaning form short values which help to recognize all the
details while developing. The developer design a database in which it uses various tables
along with attributes to store the details so data dictionary works as abbreviations to
recognize each attribute and relations (Navathe & Kerschberg, 1986).
Table 1 Data Dictionary table
Name of table Attributes Description Type of Data Key
constraints
Animal AnimalID Id of animal Varchar() Primary
HolderID Id of holder fr
animal
Varchar() Foreign
AnimalName Name of
animal
Varchar()
AnimalType Type of animal Varchar()
AnimalGender Gender of
animal
Varchar()
Pet_Appointmen
t
AnimalID Id of animal for
appointment
Varchar() Foreign
DateOfAppointment Date of
appointment
Varchar()
TimeOfAppointment Time of
appointment
Varchar()
MedID Id of
medication for
appointment
Varchar() Foreign
VetID Id of vet
branch for
appoinemnt
Varchar() Foreign
Pet_Medication MedID Id of
medication
Varchar() Primary
Description Descripotion of
medication
Varchar()
Price Price of
medication
Varchar()
VetID Id of vet
branch for
medication
Varchar() Foreign
Vet_Branch VetID Id of branch Varchar() Primary
Name Branch name Varchar()
location Location of
branch
Varchar()
VetContact Contact details
of branch
Varchar()
Receipt ReceiptNo Receipt no Varchar() Primary

DateOfIssue Date of issued
receipt
Varchar()
AmountPaid Amount paid
for receipt
Varchar()
AnimalID Id of animal for
receipt
Varchar() Foreign
Pet_Holder HolderID Id of holder Varchar() Primary
HolderName Holder name Varchar()
HolderAddress Address of
holder
Varchar()
HolderContact Contact details Varchar()
Veterinary_Staff VstaffID Staff id Varchar() primary
staffName Name of staff Varchar()
salary Salary of staff Varchar()
Address Address of
staff
Varchar()
MedID Medication id
for staff
Varchar() Foreign
Data Normalization
Data normalization is necessary to make database tables simpler and easily understandable for
others. Of data is in normalized form, then it’ll be helpful for the developer to make database very
faster and much simpler. There are main 3 types of normalization form which are used commonly
and in total it has 6 types of forms. The below tables describes 1 NF, 2 NF and 3 NF form for the
database (Akehurst, Bordbar, Rodgers & Dalgliesh, 2002).
1 NF:
Person Medical
staffName DateOfAppointment
salary TimeOfAppointment
Address DateOfIssue
Animal AmountPaid
AnimalName Description
AnimalType Price
AnimalGender Name
HolderName location
HolderAddress VetContact
HolderContact
2 NF:
Staff Appointment
VstaffID AnimalID
staffName DateOfAppointment

salary TimeOfAppointment
Address
Receipt
Animal ReceiptNo
AnimalID DateOfIssue
AnimalName AmountPaid
AnimalType
AnimalGender Medication
MedID
Description
Price
Branch
VetID
Name
location
VetContact
3 NF:
Animal Pet_Appointme
nt
Pet_Medic
ation
Vet_Bran
ch
Receipt Pet_Holder Vet_Sta
ff
AnimalID AnimalID MedID VetID Receipt
No
HolderID Vstaff
ID
HolderID DateOfAppoi
ntment
Descript
ion
Name DateOfI
ssue
HolderNa
me
staffN
ame
AnimalNa
me
TimeOfAppoi
ntment
Price locati
on
AmountP
aid
HolderAd
dress
salary
AnimalTy
pe
MedID VetID VetCon
tact
AnimalI
D
HolderCo
ntact
Addres
s
AnimalGe
nder
VetID MedID

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 developed database system for VET solutions using MS SQL server provides a platform to store
the data information in the form of table and store in the system database and also allow accessing
database anytime anywhere. The SQL commands help in to develop the relational database as it
provides manipulation of data consist of select, update, alter, insert and many more commands,
data definition commands help to make the structure of table using create, truncate, drop
commands(Agrawal, Chaudhuri, Kollar, Marathe, Narasayya & Syamala, 2005).
Animal: this table provides values along with data type and allowable nulls through a checkbox.
Figure 3 table of animal
Appointment: this table provides values along with data type and allowable nulls through a
checkbox.
Figure 4 table of appointment
Holder: this table provides values in column name along with data type and allowable nulls through
a checkbox.
Figure 5 table of the holder

Medication: this table provides values in column name along with data type and allowable nulls
through a checkbox.
Figure 6 table of medication
Receipt: this table provides values in column name along with data type and allowable nulls through
a checkbox.
Figure 7 table of receipt
Branch: this table provides values in column name along with data type and allowable nulls through
a checkbox.
Figure 8 table of branch
Staff: this table provides values in column name along with data type and allowable nulls through a
checkbox.
Figure 9 table of staff
The above designs of various tables explain the database tables which is designed in a relational
database in a proper manner.

M2 Implement a fully functional database system
I achieved M2 successfully for the fully functional system as this developed database doesn’t accept
any invalid values which help to maintain security and robustness of data. On analyzing the whole
database for this system, I recommend that it provide accurate results and handle the data
information in a proper way.
P3 Produce a query language, with queries across multiple
tables produced earlier.
This database consist of various queries and some of them are described below to understand the
database which is performed in MS-ACCESS using MySQL language. The SQL language provides
various commands like data manipulation, data definition which helps to manage the details and
provide structure for storing data information.
Query 1: fetch data for top 100 percent from the animal table.
SELECT TOP (100) PERCENT dbo.Animal.AnimalID, dbo.Animal.AnimalName,
dbo.Pet_appointment.DateOfAppointment
FROM dbo.Animal INNER JOIN
dbo.Pet_appointment ON dbo.Animal.AnimalID =
dbo.Pet_appointment.AnimalID
ORDER BY dbo.Pet_appointment.DateOfAppointment
 display top 100 percent values from animal table
Figure 10 query 1 output
Query 2: fetch data from an animal table like dog values.
Select Animal.AnimalID,Animal.AnimalName From Animal
where AnimalType like 'Dog';
 display value like a dog

Figure 11 output for query 2
Query 3: fetch data for top 100 percent from appointment table.
SELECT TOP (100) PERCENT dbo.Animal.AnimalID, dbo.Animal.AnimalName,
dbo.Receipt.ReceiptNo, dbo.Receipt.AmountPaid AS Expr1
FROM dbo.Animal INNER JOIN
dbo.Receipt ON dbo.Animal.AnimalID =
dbo.Receipt.AnimalID
ORDER BY dbo.Receipt.AmountPaid DESC
 display top 100 percent values from appointment table
Figure 12 output of query 3
Query 4: fetch data for top 100 percent from medication table.
SELECT TOP (100) PERCENT dbo.Pet_Medication.MedID,
dbo.Pet_Medication.Description, dbo.Vet_Branch.Name,
dbo.Vet_Branch.VetContact
FROM dbo.Pet_Medication INNER JOIN
dbo.Vet_Branch ON dbo.Pet_Medication.VetID =
dbo.Vet_Branch.VetID
 display top 100 percent values from medication table
Figure 13 output for query 4
Query 5: fetch all details of the animal table in increasing order.
select Animal.AnimalName, Animal.AnimalType,Animal.AnimalGender

from Animal
order by AnimalID;
 display animal details in increasing order
Figure 14 output of query 5
Query 6: fetch data for top 100 percent from medication table on the basis of salary.
SELECT TOP (100) PERCENT dbo.Pet_Medication.MedID,
dbo.Pet_Medication.Description, dbo.Vet_Branch.Name,
dbo.Veterinary_Staff.salary, dbo.Veterinary_Staff.staffName
FROM dbo.Pet_Medication INNER JOIN
dbo.Vet_Branch ON dbo.Pet_Medication.VetID =
dbo.Vet_Branch.VetID INNER JOIN
dbo.Veterinary_Staff ON dbo.Pet_Medication.MedID =
dbo.Veterinary_Staff.MedID
ORDER BY dbo.Veterinary_Staff.salary
 display top 100 percent values according to salary
Figure 15 output for query 6
Query 7: fetch all data of the animal table.
select * from Animal;
 display all data from an animal table
Figure 16 output of query 7

M3 Produce appropriate management information.
I achieved M3 successfully as all queries performed well and fetch data accurately without any
problem. The database handles all the data information very well and provide accessing database
easily and very fast and also provide a simpler interface which can adapt easily to the organization to
manage all the details.

LO3 Test the system against user and system requirements
P4 Create a test plan to test the database:
Testing is a part of any project which is developed but to ensure its working, testing is used there
which provides the surety of data and system functionality working perfectly and the below table
determine in the best way as (Appelt, D., Nguyen, C.D., Briand, L.C & N., 2014):
Table 2 test case table
S. No Test case Expected output Actual output
1 Invalid value for the cell as not fit
according to data type.
It’ll show an error for
not accepting large
values or too small
values according to
data type
requirement.
It shows an error for not
accepting large values or
too small values
according to data type
requirement.
2 Update statement conflict with key
constraint as a foreign key to change
data from a table
It’ll show an error for
not updating the value
of rows as it is linked
with another table
through foreign key
constraint.
It shows an error for not
updating the value of
rows as it is linked with
another table through
foreign key constraint.
3 Invalid value for the cell as input value
does not match the format of the data
type.
It’ll show an error for
not accepting value as
didn’t match the
string format of the
data type.
It gives an error for not
accepting value as didn’t
match the string format
of the data type.
4 Thr row was not committed because
of Update statement conflict with key
constraint as a foreign key to change
data from a table
It’ll show an error for
not committing row as
confliction between
update command and
foreign key constraint.
It shows an error for not
committing row as
confliction between
update command and
foreign key constraint.
5 No row was updated as didn’t match
the format for input string because of
date method.
It’ll give an error for
not affecting row due
to invalid input string
type as required is
date format.
It gives an error for not
affecting row due to
invalid input string type
as required is date
format.
6 The row was not committed because
of invalid input string according to
date format.
It’ll give an error for
not accepting input
string which didn’t
match with the date
string format, so row
doesn’t affect or
commit.
It shows an error for not
accepting input string
which didn’t match with
the date string format,
so row doesn’t affect or
commit.
7 Not affect the row as not meet the
requirement of data type as decimal
data type need integer value in the
decimal form.
It’ll show an error for
not affecting the row
because of invalid
value in decimal data
type.
It shows an error for not
affecting the row
because of invalid value
in decimal data type.

8 Values stored an fetched properly
from the animal table.
It’ll give proper output
with values form table
animal after storing
values in data fields.
It gives accurate output
with values form table
animal after storing
values in data fields.
Figure 17 test case 1

Figure 18 test case 2
Figure 19 test case 3

Figure 20 test case 4
Figure 21 test case 5

Figure 22 test case 6
Figure 23 test case 7

Figure 24 test case 8
M4. Assess the effectiveness of the testing
I achieved M4 successfully by providing various test cases along with a description to understand the
system which works perfectly and store the information in a secure way.

LO4 User and Technical Documentation:
P5 Produce technical and user documentation
User Documentation:
The user documentation is prepared for understanding the relational database which is prepared for
a meet the client requirement and provide a fully functional system to improve the performance of
the existing system. There are various methods which help to prepare a user documentation by
gathering information from clients and it can be done with some methods such as interviews,
questionnaires, observations, analyzing of data, and focus on the objectives of the client. The
documentation also provides points on functional and functional requirements which help into
analyze the basic need of the organization and to enhance the performance by acquiring a new
system (Lee, Jeong, Yeo & Moon, 2012).
Figure 25 animal table
Figure 26 appointment table
Figure 27 holder table
Time of appointment
fetched successfully.
Holder name
entered
successfully.
price stored

Figure 28 medication table
Figure 29 receipt table
Figure 30 branch table
Figure 31 staff table
Query reports:
Amount paid
stored
successfully.
VET id associated
successfully.
Salary entered
properly.

Figure 32 query 1
Figure 33 query 2
Figure 34 query 3
Figure 35 query 4
Figure 36 query 5
Fetch date of
appointments
Query
provide
animal
name
Animal ID’s
MedID along
with description
Animal name

Figure 37 query 6
Figure 38 query 7
Provide name
Stores animal
type.

Technical Documentation:
The following database is composed of VET Solutions Company by ULKOM LTD. In which it will help
to understand the database and their relations of the organization. The below diagram represents
relational model along with attributes and key constraints to provide functional dependency among
the tables and also fulfill the requirements of the customer by developing this database design. This
database store the information of various entities and provide accurate data information when it is
required (Baker & Grosse, 1997).
Figure 39 ERD for VET Solution
The below figures are the designs of various tables used in this database system as:
Figure 40 design of the animal table
Figure 41 design of appointment table

Figure 42 design of holder table
Figure 43 design of medication table
Figure 44 design of receipt table
Figure 45 design of the branch table
Figure 46 design of staff table

M5: Produce fully functional system
Functional dependency
The functional dependency is used for determining the relation between elements or attributes.
Basically, it is used in a relational database by using ky constraints as suppose A->B it means A is
functionally dependent on B and B can use attributes of A also (Bernstein, 1976).
.
Figure 47 diagram of functional dependency

Conclusion
This report concludes that all the information related to the database is beneficial and provide
understandable knowledge for the user which is helpful in designing part of this database system for
VET solutions. This report provides ER diagram and relational diagram to understand the whole
system and their attributes along with key constraints which provide some unique properties when
user enter the value in the specific fields. The testing is also performed in this report to ensure the
validation of data values and enhance the performance of the system by providing a fully functional
system to the client. At last, the functional dependency diagram helps to understand the entries and
their relation to other entities used in this database system. The normalization and data dictionaries
table is provided in this report which helps to the developer while designing this database for
developing more accurately and perfectly and works as a fully functional system.

References
 Kroenke, D.M. and Dolan, K.A., 1988. Database processing. Science Research Associates, Inc.
 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.
 Lee, I., Jeong, S., Yeo, S. and Moon, J., 2012. A novel method for SQL injection attack
detection based on removing SQL query attribute values. Mathematical and Computer
Modelling, 55(1-2), pp.58-68.
 Bernstein, P.A., 1976. Synthesizing third normal form relations from functional
dependencies. ACM Transactions on Database Systems (TODS), 1(4), pp.277-298.
 Akehurst, D.H., Bordbar, B., Rodgers, P. and Dalgliesh, N.T.G., 2002. Automatic
normalisation via metamodelling. In ASE 2002 Workshop on Declarative Meta Programming
to Support Software Development.
 Navathe, S.B. and Kerschberg, L., 1986. Role of data dictionaries in information resource
management. Information & management, 10(1), pp.21-46.
 Baker, B.S. and Grosse, E., AT&T Corp, 1997. System and method for restricting user
access rights on the internet based on rating information stored in a relational database. U.S.
Patent 5,678,041.
 Agrawal, S., Chaudhuri, S., Kollar, L., Marathe, A., Narasayya, V. and Syamala, M., 2005,
June. Database tuning advisor for microsoft sql server 2005. In Proceedings of the 2005 ACM
SIGMOD international conference on Management of data(pp. 930-932). ACM.
 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.