Database System: Design, Implementation, and Testing for VET

This report details the design and implementation of a relational database system for VET solutions using MS SQL Server. It covers the design phase, including ER diagrams, data dictionaries, and normalization to ensure data integrity and efficiency. The implementation phase involves creating database tables and relationships using SQL commands. The report also includes various SQL queries for data retrieval and management information generation. A comprehensive test plan is presented to validate the system against user and system requirements, followed by user and technical documentation for system understanding and maintenance. Finally, the report discusses functional dependencies within the database schema.

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