Database Assignment: Designing and Implementing a Relational Database for VET Solution

This assignment focuses on designing and implementing a relational database for VET Solution, a veterinary company. The project involves creating an Entity-Relationship Diagram (ERD), normalizing the database, and developing a fully functional database using MS SQL Server. The database is tested against user and system requirements, and both user and technical documentation are provided. The assignment demonstrates the application of database design principles and techniques in a real-world scenario.

Database Assignment

Table of Contents
List of Figures.............................................................................................................................2
List of Tables...............................................................................................................................3
Introduction...............................................................................................................................4
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem......................................................................................................................................5
P1 Design a relational database system using appropriate design tools and techniques.....5
LO2 Develop a fully functional relational database system, based on an existing system
design:......................................................................................................................................14
P2 Develop and implement a fully functional database system using MS SQL Server:......14
P3 Produce a query language, with queries across multiple tables produced earlier:.......18
LO3 Test the system against user and system requirements:.................................................23
P4 Create a test plan to test the database:.........................................................................23
LO4 User and Technical Documentation:................................................................................27
P5 Produce technical and user documentation...................................................................27
Conclusion................................................................................................................................37
References................................................................................................................................38
1

List of Figures
Figure 1 Example of Primary Key...............................................................................................6
Figure 2 Example of Foreign Key................................................................................................7
Figure 3 ERD Diagram.................................................................................................................7
Figure 4 Design View of Prescription Table.............................................................................14
Figure 5 Design View of Owner Table......................................................................................15
Figure 6 Design View of Drug table..........................................................................................15
Figure 7 Design View of Branch Table......................................................................................16
Figure 8 Design View of Appointment Table...........................................................................16
Figure 9 Design View of Animal Table......................................................................................17
Figure 10 Design View of Staff Table........................................................................................17
Figure 11 Output of Query 1....................................................................................................18
Figure 12 Output of Query2.....................................................................................................19
Figure 13 Output of Query3.....................................................................................................20
Figure 14 Output of Query4.....................................................................................................21
Figure 15 Output of Query5.....................................................................................................22
Figure 16 Output of Query6.....................................................................................................22
Figure 17 Test Case Plan 1........................................................................................................24
Figure 18 Test Case Plan 2........................................................................................................25
Figure 19 Test Case Plan 3........................................................................................................25
Figure 20 Test Case Plan 4........................................................................................................25
Figure 21 Test Case Plan 5........................................................................................................26
Figure 22 Test Case Plan 6........................................................................................................26
Figure 23 Test Case Plan 7........................................................................................................26
Figure 24 Animal Table.............................................................................................................27
Figure 25 Branch Table.............................................................................................................27
Figure 26 Appointment Table..................................................................................................28
Figure 27 Drug Table................................................................................................................28
Figure 28 Owner Table.............................................................................................................28
Figure 29 Prescription Table....................................................................................................29
Figure 30 Staff Table.................................................................................................................29
2

Figure 31 Query 1 output.........................................................................................................29
Figure 32 Query 2 Outputs.......................................................................................................30
Figure 33 Query 3 Output........................................................................................................30
Figure 34 Query 4 Outputs......................................................................................................31
Figure 35 Query 5 Outputs......................................................................................................31
Figure 36 Query 6 Outputs......................................................................................................32
Figure 37 Relationship Diagram between the tables...............................................................33
Figure 38 Query to create animal table...................................................................................33
Figure 39 Query for creating appointment table.....................................................................34
Figure 40 Query for creating branch table...............................................................................34
Figure 41 Query for creating drug table..................................................................................34
Figure 42 Query for creating owner table...............................................................................34
Figure 43 Query for creating prescription table......................................................................34
Figure 44 Query for creating Staff table...................................................................................35
Figure 45 Insert query for animal.............................................................................................35
Figure 46 Insert query for appointment..................................................................................35
Figure 47 Insert Query for branch............................................................................................35
Figure 48 Insert Query for Drug...............................................................................................35
Figure 49 Insert query for the owner.......................................................................................35
Figure 50 Insert query for prescription....................................................................................36
Figure 51 Insert query for staff................................................................................................36
List of Tables
Table 1 Data Dictionary Table..........................................................................................................9
Table 2 First Normal Form Table...................................................................................................11
Table 3 Second Normal Form Table...............................................................................................12
Table 4 Third Normal Form Table..................................................................................................13
Table 5 Test Cases Tables..............................................................................................................26
3

Introduction
In this assignment, a database will be created for the VET solution veterinary company. For
the relational database, the data modeling will be performed which will include ER diagram
and normalization of the database using appropriate tool and techniques. The database will
be created and tested against different test cases. The organization VET solution is
responsible to provide services for pet animals by storing the details in the database. The
database will include functionalities, and the query will be executed on the database to
retrieve information from the database. There are four sections in this document in which
first part deals with database designing, the second section includes the development of the
database, the third section includes testing of database and fourth section describes user
and the 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
Specification and Role of Relational Database System:
As it is known that a database is storage area to store the vast data and to manage it in the
forms of the record in the relational tables using some specific application program. By the
help of this redundancy of data is reduced and also manage the data in such a systematic
manner which is relevant to quick decision making. In addition to this, the performance of
the system is improved and provides the advanced functionalities to built-in and for analysis
(Paredaens, 2012).
Design Tools and Techniques for the given Relational Database:
One of the most common databases is a relational database in which the data and records
are stored in the tables. To operate in the relational database, the tool required for the
formatting records and data in the form of tables is the MS SQL Server. This is also one of
the standard languages for queries.
Logical Design:
For creating any database, the logic of the database must be designed for the very first time.
The database consists of a large amount of data and it has some fields like attributes,
entities, and also the relationship between entities. Every entity must have a primary key for
the unique identification.
ER-Diagram:
It is the conceptual and a pictorial diagram which describes the relationship between all the
entities in the database (Bagui, 2011). It has basically three parts:
 Entity: It is given in a rectangular box and it is defined as the object on which some
relation is defined and has a primary key for unique identification of objects.
 Attributes: Groups of attributes defines an entity. It is basically a feature of any
entity in a database. It is given in the oval.
5

 Relationship: Given in diamond shape, and defines the relationship between two
entities in the database and their flow.
Data Elements:
It consists of the entity attributes in the database. The length and type of data are decided
at the time of creating the database.
Data Types:
It defines the type of data which can be stored for the variable. It can be integer, character,
or the Boolean value.
Indexes:
It performs as a pointer. It indicates the row location of the data.
Primary/Foreign Key:
A single attribute of the entity which identifies the entity uniquely in the database is called
as the primary key. And when one attribute is present in two different entities they are
linked and associated with each other with the help of the foreign key (Burroughs, 2012).
Figure 1 Example of Primary Key
Here, DrugID is the primary key of the drug table.
6

Figure 2 Example of Foreign Key
Here, DrugID is the foreign key from the above figure table.
VET Solution Database ERD:
ERD of the VET Solution company database has defined seven tables that having so many
attributes with the relation between them. The diagram is created by keeping in mind the
system and user requirements.
Figure 3 ERD Diagram
7

Data Integrity:
It is defined as the data accuracy and consistency data in the warehouse. It includes
processing and coding of the data, data validation, and provides assurance of the quality by
testing the data from every point of view.
Data Validation:
By performing some tests, validation of the data can be applied. Also, the index and type of
the data are checked and various conditions are used to check (Xian, 2012).
Data Dictionary:
It describes the brief about the attributes and the entities in the database. It contains all
information about each entity and attributes and also their types.
Table 1 Data Dictionary Table
Name of Table Attribute Name Description Data
type
Primary
Key/Foreign
Key
Appointment ID appointment ID Integer Primary
Date It represents
appointment date.
Date
AnimalID It represents the
animal ID.
Integer
OwnerID It describes the
owner ID who has
taken the
appointment
Integer Foreign
Branch ID It describes ID of the
branch.
Integer Primary
Location It describes the
location of the
branch
Varchar
Name It describes the name Varchar
8

of the Branch
Contact It describes the
contact number of
that particular
branch.
Numeric
StaffID It describes the staff
ID.
Integer Foreign
Owner ID It describes the ID of
the owner.
Integer Primary
Name It describes the name
of the owner.
Varchar
Address It describes the
owner address.
Varchar
Contact It describes the
contact information
of the owner.
Numeric
Age It describes the age
of the owner.
Integer
AnimalID It describes the ID of
the animal.
Integer Foreign
Animal ID It describes the
animal ID.
Integer Primary
Name It describes the name
of the animal.
Varchar
Address It describes the
address.
Varchar
Animal Type It describes the
animal type
Numeric
Drug DrugID It describes the ID of
the drug.
Integer Primary
Name It describes the name Varchar
9

of the drug.
StaffID It describes the ID of
the staff.
Integer
Cost It describes the cost
of the treatment.
Integer
Prescription ID It describes the
prescription ID.
Integer Primary
StaffID It describes the ID of
the staff.
Text Foreign
AnimalID It describes the ID of
the animal.
Integer Foreign
DrugID It describes the ID of
the drug.
Integer Foreign
OwnerID It describes the ID of
the owner.
Integer Foreign
Staff ID It describes the ID of
the staff.
Integer Primary
Name It describes the name
of the staff.
Text
Address It describes the
address of the staff.
varchar
Age It describes the age
of the staff.
Date
Role It describes the role
of the staff.
Text
Data Normalization:
It a technique to rearranged the data elements in a normalized form to reduce the
redundancy of the data. It is required to optimize the data at various different levels
(Gouhar, 2017).
10

1NF:
Table 2 First Normal Form Table
First Normalization
Appointment Animal
ID ID
Date Name
AnimalID Address
OwnerID Aanimal Type
BRANCH DRUG
ID DrugID
Location Name
Name StaffID
Contact Cost
StaffID PRESCRIPTION
OWNER ID
ID StaffID
Name AnimalID
Address DrugID
Contact OwnerID
Age STAFF
AnimalID ID
Name
Address
Age
Role
11

2NF:
Table 3 Second Normal Form Table
Second Normalization
Appointment Animal Prescription
ID ID ID
Date Name StaffID
AnimalID Address AnimalID
OwnerID Aanimal Type DrugID
BRANCH DRUG OwnerID
ID DrugID Staff
Location Name ID
Name StaffID Name
Contact Cost Address
StaffID Age
OWNER Role
ID
Name
Address
Contact
Age
AnimalID
3NF:
Table 4 Third Normal Form Table
Third Normalization
Appointment Animal Prescription Owner
ID ID ID ID
Date Name StaffID Name
AnimalID Address AnimalID Address
OwnerID Aanimal Type DrugID Contact
12

Branch Drug OwnerID Age
ID DrugID Staff AnimalID
Location Name ID
Name StaffID Name
Contact Cost Address
StaffID Age
Role
13

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:
When talking about the relational database, it is the most famous and common schema to
store the data into rows and columns or into the format of the table. Tabular format of data
is then used to define and generate the relationship. Various common operations can be
performed on the data elements like insertion, selection, deletion, and update. It also
defines the functions on storing the data. For creating the database of the company VET
SOLUTION, I am using the MS SQL Server 2014 (Harrigngton, 2016).
Here, the design views of various tables are given:
Prescription Table Design View: This table is designed to store the information of
prescription that is given by the doctor to the animal. Here, I have present the table design
view which is designed by using the SQL server.
Figure 4 Design View of Prescription Table
Owner Table Design View: This table is designed to store the information of the owner of
the animal. Here, I have present the table design view which is designed by using the SQL
server.
14

Figure 5 Design View of Owner Table
Drug Table Design View: This table is designed to store the information of drug that is given
by the doctor to the animal according to their requirement. Here, I have present the table
design view which is designed by using the SQL server.
Figure 6 Design View of Drug table
Branch Table Design View: This table is designed to store the information of branches that
are located in the UK states. Here, I have present the table design view which is designed by
using the SQL server.
15

Figure 7 Design View of Branch Table
Appointment Table Design View:
Figure 8 Design View of Appointment Table
Animal Table Design View: This table is designed to store the information of animal with
their problem, address, and owner id. Here, I have present the table design view which is
designed by using the SQL server.
Figure 9 Design View of Animal Table
16

Staff Table Design View: This table is designed to store the information of staff who are
working in the VET solution branches and this table contains the particular information like
address, role, skills, contact, age etc. Here, I have present the table design view which is
designed by using the SQL server.
Figure 10 Design View of Staff Table
17

P3 Produce a query language, with queries across multiple tables produced earlier:
For any database, the query is accustomed to executing capacities on tables like the update,
insert, select, and delete on the database table. It is required to fetch out the useful and
relevant data from the database server storage with the help of the structured query. An
inquiry can be controlled by the client to recover information according to their necessity
(Staszewski, 2016). Questions are composed in SQL dialect for the social database. Let us
discuss some queries and their output:
Query1:
To fetch the animal ID, prescription ID as Expr1, Appointment date, and the drug name from
the database.
Solution:
SELECT dbo.Animal.ID, dbo.Prescription.ID AS Expr1, dbo.Appointment.Date,
dbo.Drug.Name FROM dbo.Animal INNER JOIN
dbo.Appointment ON dbo.Animal.ID = dbo.Appointment.AnimalID INNER JOIN
dbo.Prescription ON dbo.Animal.ID = dbo.Prescription.AnimalID INNER JOIN
dbo.Drug ON dbo.Prescription.DrugID = dbo.Drug.DrugID
Output:
Figure 11 Output of Query 1
18

Query2:
To fetch the animal id, animal name, owner id as expr1, owner name as expr2, prescription
table staff id, prescription table drug id from the database.
Solution:
SELECT dbo.Animal.ID, dbo.Animal.Name, dbo.Owner.ID AS Expr1, dbo.Owner.Name AS
Expr2, dbo.Prescription.StaffID, dbo.Prescription.DrugID
FROM dbo.Animal INNER JOIN
dbo.Owner ON dbo.Animal.ID = dbo.Owner.AnimalID INNER JOIN
dbo.Prescription ON dbo.Animal.ID = dbo.Prescription.AnimalID AND
dbo.Owner.ID = dbo.Prescription.OwnerID
Output:
Figure 12 Output of Query2
Query3:
To display all the filed in the prescription table like Id, staff id, animal id, drug id, and the
owner id from the database.
Solution:
Select * from Prescription;
19

Output:
Figure 13 Output of Query3
Query4:
To display the minimum cost from the drug table in the database.
Solution:
SELECT MIN (Cost) AS SmallestPrice
FROM Drug;
Output:
20

Figure 14 Output of Query4
Query5:
To display the maximum cost from the drug table in the database.
Solution:
SELECT MAX(cost)
FROM Drug
Where DrugID = '52'
Output:
21

Figure 15 Output of Query5
Query6:
To display and fetch the animal id, animal name, prescription table staff id from the animal
and appointment table where animal.id will be equal to the appointment animal id from the
database.
Solution:
SELECT dbo.Animal.ID, dbo.Animal. Name, dbo.Prescription.StaffID FROM dbo.Animal
INNER JOIN dbo.Appointment ON dbo.Animal.ID = dbo.Appointment.AnimalID INNER JOIN
dbo.Prescription ON dbo.Animal.ID = dbo.Prescription.AnimalID
Output:
Figure 16 Output of Query6
22

LO3 Test the system against user and system requirements:
P4 Create a test plan to test the database:
For the VET SOLUTION company database, various test cases are taken in the care to
validate and verification of the stored data and check the results. The implementation and
execution of the database are perfectly working for all the test cases. If there is any fault in
the database it will be identified very easily. It is done to check the performance of the
database, Here, various test cases and plan are discussed (Eckstein, 2018).
Table 5 Test Cases Tables
Serial
No.
Test Case Plan Expected
Outcome
Actual
Outcome
Comment
1. To create the drug
table in the
database
The drug table must
be created with each
entity and the code
should run
successfully.
The command is
executed and the
table is created
successfully.
Execution
Successful.
2. To convert the data
type varchar to the
numeric in the
branch table.
The error must be
shown when
converting the
varchar to the
numeric.
The error is shown
in converting the
type from varchar
to numeric data.
Execution
Successful.
3. To convert the date
or time from the
character string in
the table of
appointment.
There must be shown
an error of conversion
from character string.
Error is shown in
the conversion of
the date to a
character string.
Execution
Successful.
4. To check the
foreign key in the
other table and
giving the value in
that table instead
An error must be
shown of a confliction
of the foreign key in
the branch and staff.
Error is shown of
the confliction of
the foreign key.
Execution
Successful.
23

of the original
table.
5. When inserting the
value in the drug
table field
Entry must be added
to the table when it is
made.
All the entry is
added to the drug
table.
Execution
Successful.
6. Creating the
violation in the
primary key of the
in the staff table.
The error must be
shown in violation of
the primary key
constraints.
Error is shown for
the violation of
primary key.
Execution
Successful.
7. Reference
Constraints
confliction with the
table of staff.
The reference
constraints error
must be shown in the
table of the staff.
The error is shown
in reference
constraint
confliction.
Execution
Successful.
Figure 17 Test Case Plan 1
24

Figure 18 Test Case Plan 2
Figure 19 Test Case Plan 3
Figure 20 Test Case Plan 4
25

Figure 21 Test Case Plan 5
Figure 22 Test Case Plan 6
Figure 23 Test Case Plan 7
26

LO4 User and Technical Documentation:
P5 Produce technical and user documentation
User Documentation:
In the user documentation, the final database documentation of the system is provided to
the user to access it at their end. It allows the user to understand the functionalities of the
database at the user's end. The design view, query representation output, interface and so
many more things are visible to the user's end. The script tables are shown in the user part.
Also in addition to this, queries are also given to fetch the output from the database (Larson,
2016).
Figure 24 Animal Table
Figure 25 Branch Table
27
Display the information
of the animal with their
name, address and id.
Display the branch
information with their
name and location.

Figure 26 Appointment Table
Figure 27 Drug Table
Figure 28 Owner Table
28
Display the Appointment
information.
Display the drug
information.

Figure 29 Prescription Table
Figure 30 Staff Table
In addition to this, there are some query and its output which are visible to the user. Let us
see what user can see in this (Hogan, 2018).
Figure 31 Query 1 output
29
Store the information of
prescription that is given by the
doctor for the animal.

Figure 32 Query 2 Outputs
Figure 33 Query 3 Output
30
Display the smallest price of drug
from the stored values.
Display the prescription table.

Figure 34 Query 4 Outputs
Figure 35 Query 5 Outputs
31
Display the Appointment date
and name of the owner

Figure 36 Query 6 Outputs
Technical Documentation:
As clear from the name technical documentation, all the technical aspects are represented
related to the database of the VET Solutions. It helps is proper assisting and management of
the company. In this design and prototype and the relation between the entity, tables are
described. Also, in the technical documentation, the functional dependency is also given
(Linares, 2016).
32

Animal
ID
Name
Address
Contact
Appointment
ID
Date
AnimalID
OwnerID
Branch
ID
Name
Location
contact
StaffID
Drug
DrugID
Name
StaffID
cost
Owner
ID
Name
Address
Contact
Age
AnimalID
Prescription
ID
StaffID
AnimalID
DrugID
OwnerID
Staff
ID
Name
Address
Contact
Age
role
Figure 37 Relationship Diagram between the tables
Query is made at the server database which is given for each table.
Figure 38 Query to create animal table
33
Create query to generate the
Animal table to store the
information of animals.

Figure 39 Query for creating appointment table
Figure 40 Query for creating branch table
Figure 41 Query for creating drug table
Figure 42 Query for creating owner table
Figure 43 Query for creating prescription table
34
Create query to generate the
branch table to store the
information of animals.
Create query to generate the
drug table to store the
information of drugs

Figure 44 Query for creating Staff table
In addition to this, there are some queries to insert the data into the database tables.
Figure 45 Insert query for animal
Figure 46 Insert query for appointment
Figure 47 Insert Query for branch
Figure 48 Insert Query for Drug
Figure 49 Insert query for the owner
35
Insertion query to insert the
information in animal table.

Figure 50 Insert query for prescription
Figure 51 Insert query for staff
Functional Dependency:
Also, between all the entities tables there is the functional dependency through which they
are connected. There is the dependency diagram of the company VET solution is given
(Foster, 2016).
36

Conclusion
The database for Vet solution organization is successfully created. The database stores the
details of pet. The database is successfully created using the system and user requirements.
The database modeling diagram is created for the database. The data models include ER
diagram, normalization, and functional dependency diagram. The database is tested against
various test cases and documentation is successfully created for this database.
37

References
 Bagui, S. and Earp, R., 2011. Database design using entity-relationship diagrams. CRC
Press.
 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.
 Eckstein, J. and Schultz, B.R., 2018. Basic Relational Database Theory. Introductory
Relational Database Design for Business, with Microsoft Access, pp.53-94.
 Foster, E.C., and Godbole, S., 2016. Database Modeling and Design. In Database Systems
(pp. 101-137). Apress, Berkeley, CA.
 Gouhar, A., 2017. Database Management System. International Journal of Engineering
Science, 11766.
 Harrington, J.L., 2016. Relational database design and implementation. Morgan
Kaufmann.
 Hogan, R., 2018. A Practical Guide to Database Design. Chapman and Hall/CRC.
 Larson, B., English, D. and Purington, P., 2016. Microsoft SQL Server 2016 Reporting
Services. McGraw-Hill Education.
 Linares-Vásquez, M., Li, B., Vendome, C. and Poshyvanyk, D., 2016, July. Documenting
database usages and schema constraints in database-centric applications. In Proceedings
of the 25th International Symposium on Software Testing and Analysis (pp. 270-281).
ACM.
 Paredaens, J., De Bra, P., Gyssens, M. and Van Gucht, D., 2012. The structure of the
relational database model (Vol. 17). Springer Science & Business Media.
 Staszewski, P., Woldan, P., Korytkowski, M., Scherer, R. and Wang, L., 2016, June. Query-
by-example image retrieval in Microsoft SQL server. In International Conference on
Artificial Intelligence and Soft Computing (pp. 746-754). Springer, Cham.
 Xian, Y., Fonarow, G.C., Reeves, M.J., Webb, L.E., Blevins, J., Demyanenko, V.S., Zhao,
X., Olson, D.M., Hernandez, A.F., Peterson, E.D. and Schwamm, L.H., 2012. Data
quality in the American Heart Association Gets With The Guidelines-Stroke (GWTG-
Stroke): results from a national data validation audit. American heart journal, 163(3),
pp.392-398.
38