Database Design and Development for VET SOLUTION

This project focuses on designing and developing a relational database management system for VET SOLUTION, a veterinary company in Cambridgeshire. The aim is to create an effective communication system between all branches of the company, reducing manual data entry and ensuring reliability. The report details the design process, implementation using MS SQL Server, testing against user and system requirements, and documentation for both users and technical staff. The database is designed to improve data integrity, reduce redundancy, and facilitate efficient decision-making.

Database Design and Development
1

Table of Contents
Introduction...........................................................................................................................................5
LO1 Use an appropriate design tool to design a relational database system for a substantial problem
...............................................................................................................................................................6
P1 Design a relational database system using appropriate design tools and techniques..................6
M1 Produce a comprehensive design for a fully functional system...................................................8
LO2 Develop a fully functional relational database system, based on an existing system design:......15
P2 Develop and implement a fully functional database system using MS SQL Server:....................15
M2 Implement a fully functional database system..........................................................................20
P3 Produce a query language, with queries across multiple tables produced earlier:.....................21
M3 Produce appropriate management information.......................................................................26
LO3 Test the system against user and system requirements:..............................................................27
P4 Create a test plan to test the database:......................................................................................27
M4. Assess the effectiveness of the testing.....................................................................................31
LO4 User and Technical Documentation:............................................................................................32
P5 Produce technical and user documentation...............................................................................32
M5: Produce fully functional system...............................................................................................41
Conclusion...........................................................................................................................................42
References...........................................................................................................................................43
List of figures
Figure 1 Primary Key Example...............................................................................................................7
Figure 2 Foreign Key Example................................................................................................................7
Figure 3 ER diagram...............................................................................................................................8
Figure 4 Validation Test 1......................................................................................................................9
Figure 5 Validation Test 2......................................................................................................................9
Figure 6 appointment table.................................................................................................................15
Figure 7 appointment datasheet view................................................................................................15
2

Figure 8 Clinic table.............................................................................................................................16
Figure 9 Clinic view..............................................................................................................................16
Figure 10 owner table..........................................................................................................................16
Figure 11 owner view..........................................................................................................................16
Figure 12 Invoice table........................................................................................................................17
Figure 13 Invoice datasheet view........................................................................................................17
Figure 14 Pet table...............................................................................................................................17
Figure 15 Pet datasheet view..............................................................................................................17
Figure 16 Pharmacy table....................................................................................................................18
Figure 17 Pharmacy datasheet view....................................................................................................18
Figure 18 Physician table.....................................................................................................................18
Figure 19 Physician datasheet view.....................................................................................................18
Figure 20 Staff table.............................................................................................................................19
Figure 21 Staff datasheet view............................................................................................................19
Figure 22 Treatment table...................................................................................................................19
Figure 23 Treatment datasheet view...................................................................................................19
Figure 24 Query1 output.....................................................................................................................21
Figure 25 query 2 outputs...................................................................................................................22
Figure 26 query 3 outputs...................................................................................................................23
Figure 27 query4 output......................................................................................................................24
Figure 28 query5 output......................................................................................................................25
Figure 29 Test Case 1...........................................................................................................................28
Figure 30 Test case 2...........................................................................................................................28
Figure 31 Test case 3...........................................................................................................................29
Figure 32 Test case 4...........................................................................................................................29
Figure 33 Test Case 5...........................................................................................................................30
Figure 34 Clinic table...........................................................................................................................32
Figure 35Appointment Table...............................................................................................................32
Figure 36 Invoice Table........................................................................................................................33
Figure 37 Owner Table........................................................................................................................33
Figure 38 Pet table...............................................................................................................................33
Figure 39 Pharmacy table....................................................................................................................33
Figure 40 Physician table.....................................................................................................................34
Figure 41 Staff Table............................................................................................................................34
3

Figure 42 Treatment Table..................................................................................................................34
Figure 43 query 1.................................................................................................................................35
Figure 44 query2..................................................................................................................................35
Figure 45 Query 3................................................................................................................................36
Figure 46 Query 4................................................................................................................................36
Figure 47 Query 5................................................................................................................................37
Figure 48 Relationship among tables...................................................................................................38
Figure 49 Appointment Design............................................................................................................38
Figure 50 Clinic Design.........................................................................................................................39
Figure 51 Invoice Design......................................................................................................................39
Figure 52 Owner Design......................................................................................................................39
Figure 53 Pet Design............................................................................................................................39
Figure 54 Pharmacy Design.................................................................................................................40
Figure 55 Physician Design..................................................................................................................40
Figure 56 Staff Design..........................................................................................................................40
Figure 57 Treatment Design................................................................................................................40
Figure 58 Functional dependency flowchart.......................................................................................41
List of Tables
Table 1 Data dictionary........................................................................................................................10
Table 2 First normalization..................................................................................................................12
Table 3 Second Normalization.............................................................................................................13
Table 4 Third Normalization................................................................................................................13
Table 5 Test cases................................................................................................................................27
4

Introduction
In this project, I will be working on designing and development of a database management
system for a veteran company VET SOLUTION in Cambridgeshire to create an effective
communication between all the branches of the company can be made. The design of the
database system has been made as per company’s requirements which reduce their time
consumption in manual updation and it is reliable in case of a fault.
The database management system, after its designing and development, has been properly
implemented and tested and has been discussed in this report.
5

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
Specifications of Database System and its Role: - System of Database is used to manage
whole data within a company by storing huge amounts of data in software application using
relational tables. It decreases data redundancy and presents data in a convincing manner that
helps in efficient decision making. It improves the performance of a system and provides
advance built-in analysis functionalities.
Tools and Techniques for Relational Database
Relational Database is the most common type of DBMS. In this, data is stored in tables. A
tool used for formatting data in tables is MS SQL Server. It is also known as Standard Query
Language.
Logical Design for Relational Database:-
To make a relational database, it should first be logically designed. A database consists of an
entity, its attribute, and relationship among entities. Each entity should have a primary key.
ER- Diagram
Pictorial representation of relationships shared among various entities.
Entity- An entity is an object of the database which stores a primary key. It is represented by
a rectangle.
Attribute: - Attributes are the values that define an entity. It is represented by Oval.
Relationship: - Relationship shows how two entities share information in a database. It is
represented by Diamond.
Data Elements: - Data Elements contains the attribute of an entity. It can be of any data type
and its length can be specified while developing a database.
Data Types: - Data type defines what type of variable a data can store like character, integer,
or Boolean value.
Indexes: - Indices are the location of a row of data. In the table, it acts as a pointer.
Primary/Foreign Key: - A unique column in the table is known as a primary key that is used
to separate a row in the table from any other row and when the primary key of one table is
applied in another table for reference then it becomes Foreign Key.
6

Figure 1 Primary Key Example
In the table list, Cust_Id is the labeled as Primary Key of the table.
Figure 2 Foreign Key Example
In the table, Cust_Id is labeled as Foreign key because it is used as a reference to table
present in fig 1(Singh, 2017).
Entity relationship diagram for VET Solution database
This diagram presents the nine tables that will be created during implementation time to store
the information in the database. These tables are interrelated with creating the relationship
between them. This diagram is generated according to the user and system requirements.
7

Figure 3 ER diagram
M1 Produce a comprehensive design for a fully functional system
Data Integrity: - Data Integrity is a term to define how consistent and accurate data is stored
in the data warehouse. Data integrity includes validation of data, providing quality assurance
by testing data from every perspective and coding and processing of data.
Data Validation: - Data validation is used to validate data by performing various tests. Data
is checked for its type and index and put to various conditions to check if it satisfies its type
and index.
Validation Test 1- Data type is set as money for the amount.
Validation Test 2 – physician No cannot be null.
8

Figure 4 Validation Test 1
Figure 5 Validation Test 2
Data Dictionary
Data Dictionary gives brief about the entities and its attribute that has to be reserved in the
database. It comprises of the entity or name of the table, its attributes, its description and its
type.
9

Table 1 Data dictionary
Table Name Attribute Description Data type Key
Appointment appoint Id of an appointment Integer Primary
appointDate Date of the
appointment
Date
appointTime Time of the
appointment
Time
ownerNo Id of the owner who
took the appointment
Integer Foreign
Clinic clinicNo Id of the clinic Integer Primary
clinicAddress Address of the clinic Varchar
clinicCity City in which the clinic
is located
Text
clinicTelNo Telephone Number of
the clinic
Integer
Invoice billNo Id of the bill Integer Primary
billDate Date on which the bill
is issued
Date
paymentMethod Payment method used
for paying the bill
Text
totalAmount Total amount of the bill money
billType Type of the bill Text
ownerNo Id of the owner of the
bill.
Integer Foreign
Owner ownerNo Id of an owner Integer Primary
ownerName Name of the owner Text
ownerAddress Address of the owner varchar
ownerCity City of the owner Text
ownerTelNo Telephone Number of
the owner
Integer
clinicNo Clinic Id of the owner Integer Foreign
10

Pet petNo Id of the pet Integer Primary
petName Name of the pet Text
petType Type of the pet Text
petGender Gender of the pet Text
ownerNo Id of pet owner Integer Foreign
clinicNo Id of the clinic Integer Foreign
physicianNo Id of the physician Integer Foreign
Pharmacy drugNo Id gave to a drug Integer Primary
drugName Name of the drug Text
drugPrice Price of the drug Money
clinicNo Clinic Id in which drug
is available
Integer Foreign
Physician physicianNo Id of the physician Integer Primary
pysicianName Name of the physician Text
pysicianTelNo Telephone Number of
the physician
Integer
clinicNo Clinic Id of the
physician
Integer Foreign
appointNo Appointment Id is
represented
Integer Foreign
Staff staffNo Id of the staff Integer Primary
staffName Name of the staff Text
staffAddress Address of the staff varchar
staffDOB DOB of the staff Date Foreign
staffCity City of staff Text
staffTelNo Telephone Number of
the staff
Integer
clinicNo Clinic Id of the staff Integer Foreign
Treatment treatNo Id gave to a treatment Integer Primary
treatmentName Name of the treatment Text
treatmentCost Cost of the treatment money
petNo Pet Id of the pet Integer Foreign
11

Data Normalization
Data normalization is used to rearrange data in useful series called normal forms which
reduces data redundancy and provides data integrity. It optimizes the results of a database at
different levels.
1NF
Table 2 First normalization
First normalization
OWNER PET CLINIC
ownerNo physicianNo clinicNo
ownerName physicianName clinicAddress
ownerAddress physicianTelNo clinicCity
ownerCity petNo clinicTelNo
ownerTelNo petName staffNo
billNo petType staffName
billDate petGender staffAddress
paymentMethod ownerNo staffDOB
totalAmount treatNo staffCity
billType treatmentName staffTelNo
appointNo treatmentCost drugNo
appointDate drugName
appointTime drugPrice
clinicNo
2NF
12

Table 3 Second Normalization
Second Normalization
Owner Pet Clinic Physician
ownerNo petNo clinicNo physicianNo
ownerName petName clinicAddress physicianName
ownerAddress petType clinicCity physicianTelNo
ownerCity petGender clinicTelNo appointNo
ownerTelNo ownerNo staffNo appointDate
billNo treatNo staffName appointTime
billDate treatmentName staffAddress clinicNo
paymentMethod treatmentCost staffDOB
totalAmount staffCity
billType staffTelNo
drugNo
drugName
drugPrice
3NF
Table 4 Third Normalization
Third normalization
APPOINTMENT CLINIC INVOICE OWNER PET
appointNo clinicNo billNo ownerNo PetNo
AppointDate clinicAddress billDate ownerName petName
appointTime clinicCity paymentMethod ownerAddress petType
ownerNo clinicTelNo totalAmount ownerCity petGender
billType ownerTelNo ownerNo
ownerNo clinicNo clinicNo
physicianNo
PHYSICIAN STAFF TREATMENT
physicianNo staffNo treatNo
13

physicianName staffName treatmentName
physicianTelNo staffAddress treatmentCost
clinicNo staffDOB petNo
appointNo staffCity
staffTelNo
clinicNo
14

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:
A relational database is most common schema to store data and it stores data in table format
using rows and columns. Relationships are generated by the tabular data. It allows us to
perform a variety of operations on the data like Update, deletion, insertion, and selection. It
provides data with functionality on how to be stored. For preparing the relational database, I
have used MS SQL Server 2014.
Design view of tables comprises of table name, its attributes and the type of data of each
attribute.
 Appointment
Figure 6 appointment table
Figure 7 appointment datasheet view
 Clinic
15

Figure 8 Clinic table
Figure 9 Clinic view
 Owner
Figure 10 owner table
Figure 11 owner view
 Invoice
16

Figure 12 Invoice table
Figure 13 Invoice datasheet view
 Pet
Figure 14 Pet table
Figure 15 Pet datasheet view
 Pharmacy
17

Figure 16 Pharmacy table
Figure 17 Pharmacy datasheet view
 Physician
Figure 18 Physician table
Figure 19 Physician datasheet view
18

 Staff
Figure 20 Staff table
Figure 21 Staff datasheet view
 Treatment
Figure 22 Treatment table
Figure 23 Treatment datasheet view
19

M2 Implement a fully functional database system
The implementation of database discussed above has been designed to achieve maintenance
goals as described in client requirements. The specific data type for each attribute will make
it more secure and prevent garbage data from storing into the database. For example, if a user
enters a character in integer field then the data will not be added to the database. Also, the
primary id given to each entity in an entity set will be used to distinguish a particular object
or entity from all the other objects and these fields can’t be left blank. The security of the
system will be checked time to time by the use of a primary key as no unauthorized user will
ever get access to the database.
20

P3 Produce a query language, with queries across multiple tables produced
earlier:
The query is used to implementing functions on tables like select, update, delete and insert in
the table. Query language is used to extract data from database by using a structured query. A
query can be manipulated by the user to retrieve data as per their requirement. Queries are
written in SQL language for the relational database.
Query1 Fetch the petNo, petName, petGender from “Pet” where petType is a horse.
Solution:
SELECT petNo, petName, petGender FROM Pet Where petType like ‘horse’;
Output:
Figure 24 Query1 output
Query2 fetch staffNo, staffName, clinicAddress from Staff and Clinic where clinicNo of
staff is equaled to clinicNo of clinic table.
Solution:
SELECT Staff.staffNo, Staff.staffName, clinic.clinicAddress FROM Staff inner join clinic
ON Staff.clinicNo=clinic.clinicNo;
21

Output:
Figure 25 query 2 outputs
Query3 fetch ownerName, ownerCity, billNo, billDate, totalAmount from tables Owner and
Invoice where ownerNo of Owner equals ownerNo of Invoice in ascending order of its
totalAmount.
Solution
SELECT Owner.ownerName, Owner.ownerCity, Invoice.billNo, Invoice.billDate,
Invoice.totalAmount FROM Owner inner join Invoice ON Owner.ownerNo =
Invoice.ownerNo ORDER BY Invoice.totalAmount;
Output
22

Figure 26 query 3 outputs
Query4 write a query to find the physicianName, clinicAddress, and petType from table
Physician, Clinic, and Pet where clinicNo of all three tables are equal and physicianName has
R within it.
Solution
SELECT Physician.physicianName, Clinic.clinicAddress, Pet.petType FROM Physician
inner join Clinic ON Physician.clinicNo = Clinic.clinicNo Inner join Pet ON Clinic.clinicNo
= Pet.clinicNo WHERE physicianName like ‘%R%’;
Output
23

Figure 27 query4 output
Query5 write a query to find the physicianName, clinicAddress, and petType from table
Physician, Clinic, and Pet where clinicNo of all three tables are same and physicianName has
‘e’ to it.
Solution
SELECT Physician.physicianName, Clinic.clinicAddress, Pet.petType FROM Physician
inner join Clinic ON Physician.clinicNo = Clinic.clinicNo Inner join Pet ON Clinic.clinicNo
= Pet.clinicNo WHERE physicianName like ‘%e%’;
Output
24

Figure 28 query5 output
25

M3 Produce appropriate management information.
This above implemented the queries made it access data important to a user, many different
queries have been implemented to extract a variety of specific data. These queries made it
easy to retrieve data and also information management becomes easy.
26

LO3 Test the system against user and system requirements:
P4 Create a test plan to test the database:
A process of validation and verification of the stored data and checks the working of the
design and implementation of database executes as desired is known as testing. Testing is
performed to test various conditions and find any fault in the existing system. Testing is
performed to make sure that the performance is proper or not.
Table 5 Test cases
Sr. No. Test cases Expected Result Actual result
1. Null value entered in
clinic TelNo.
The data should not be
inserted into the database as
clinic TelNo does not accept
a null value.
Shows Error in
inserting null value in
clinicTelNo.
2. Null value entered in
clinicCity.
The data should not be
inserted into the database as
clinicCity does not accept a
null value.
Shows Error in
inserting null value in
clinicCity.
3. Changed totalAmount
field value by a
character value.
Data should not be updated
as the data type of
totalAmount is money, it
can’t be a character.
Data is not updated.
4. Changed clinicNo field
value by a character
value.
Data should not be updated
as the data type of clinicNo
is an integer, it can’t be a
character.
Data is not updated.
5. Query with
inappropriate syntax is
fired.
Error message while
executing query.
Query didn’t execute.
27

Figure 29 Test Case 1
Figure 30 Test case 2
28

Figure 31 Test case 3
Figure 32 Test case 4
29

Figure 33 Test Case 5
30

M4. Assess the effectiveness of the testing
Various test cases have been used to test security and accessibility for my database. I have
performed various validations like a value will only accept the value defined by its data type.
If the data type entered is not in the same format as its pre-defined data type then error
message is generated and the system does not execute the requested query.
31

LO4 User and Technical Documentation:
P5 Produce technical and user documentation
User Documentation:
Documentation of user is the final documentation of a system which is provided to the user at
the end to access it. It gives an easy understanding of data to the user. The data and its output
using queries will a part of user documentation and provides an interface to the user to
interact with the inner system. I have provided some datasheet view including queries to
extract some meaningful data from the database.
Figure 34 Clinic table
Figure 35Appointment Table
32
Data of the clinic is
stored
Appointment details
of patient is stored
here

Figure 36 Invoice Table
Figure 37 Owner Table
Figure 38 Pet table
Figure 39 Pharmacy table
33
Invoice details are
stored here
Pet Owner details are stored here
Pet details are
stored
Pharmacy details
are stored

Figure 40 Physician table
Figure 41 Staff Table
Figure 42 Treatment Table
34
Data of the staff is
stored
Physician details
are stored
Treatment
details are
stored

Query reports:
Figure 43 query 1
Figure 44 query2
35
Query and its output
Query and its output

Figure 45 Query 3
Figure 46 Query 4
36
Query and its output
Query and its output

Figure 47 Query 5
37
Query and its output

Technical Documentation:
In technical documentation, all the technical information associated with VET Solutions Company is
documented properly for assisting the management of the company. It includes the prototype or
design views of table and the relationship among the tables are documented. Also, the functional
dependency is included in the technical documentation (Graubner, 2017).
Figure 48 Relationship among tables
Figure 49 Appointment Design
38

Figure 50 Clinic Design
Figure 51 Invoice Design
Figure 52 Owner Design
Figure 53 Pet Design
39

Figure 54 Pharmacy Design
Figure 55 Physician Design
Figure 56 Staff Design
Figure 57 Treatment Design
40

M5: Produce fully functional system
Functional dependency
A functional dependency is used to define the relationship among entities that uniquely
identify each other. The functional dependency of my project has been shown below.
Figure 58 Functional dependency flowchart
41

Conclusion
The database is successfully designed for the VET solution veterinary company according to
the requirements set by user and client by using the tool MS SQL server 2014. The designed
database will reduce data redundancy, time, and cost of the database. Using this database
VET solution can easily store the data and extract the meaningful data whenever necessary
without any manual work. It will help the company in better and quick decision making. The
database designer is flexible and easily adaptable and is efficient to use.
42

References
 1keydata. 2017. Logical Data Model. [Online] Available at
https://www.1keydata.com/datawarehousing/logical-data-model.html [Accessed 25 April
2018].
 1keydata, n.d. Third Normalisation form. [online] Database Normalization Available at:
https://www.1keydata.com/database-normalization/third-normal-form-3nf.php
[Accessed 25 April2018].
 Chapple, M. 2018. Ensure Accurate Data Using Functional Dependencies. [online]
ThoughtCo. Available at: https://www.thoughtco.com/functional-dependency-definition-
1019257 [Accessed 25 Jan. 2018].
 Cs.tsu 2017. Functional Dependency and Normalization for Relational Databases.
[ebook] cs.tsu, pp.1-20. Available at:
http://cs.tsu.edu/ghemri/CS346/ClassNotes/Normalization.pdf [Accessed 24 Jan. 2018].
 Elmasri, R. and Navathe, S. 2016. FUNDAMENTALS OF Database Systems.6th ed.
[ebook] PEARSON, pp.59-75. Available at:
https://pdfs.semanticscholar.org/74b6/cd75916b3a6d1dbc0284582968d87c941db7.pdf
[Accessed 24 Jan. 2018].
 Graubner, J. 2017. What is "technical documentation"?[online] Transcom. Available at:
http://www.transcom.de/transcom/en/technische-dokumentation.htm [Accessed 25 Jan.
2018].
 Krishnamurthy, S., Thombre, N., Conway, N., Li, W.H. and Hoyer, M., Cisco
Technology Inc, 2014. Addition and processing of continuous SQL queries in a
streaming relational database management system. U.S. Patent 8,745,070.
 Lucidchart 2017. What is an Entity Relationship Diagram? [Online] Lucidchart.
Available at: https://www.lucidchart.com/pages/er-diagrams [Accessed 25 April2018].
 Parker, J. 2012.Business, User, and System Requirements. [Blog] Enfocus Solutions Inc.
Available at: http://enfocussolutions.com/business-user-and-system-requirements/
[Accessed 24 Jan. 2018].
 Singh, C. 2017. Primary key in DBMS. [online] beginners book.. Available at:
https://beginnersbook.com/2015/04/primary-key-in-dbms/ [Accessed 24 Jan. 2018].
43

 Support.office 2017. Create a simple select query. [online] Support.office. Available at:
https://support.office.com/en-us/article/Create-a-simple-select-query-de8b1c8d-14e9-
4b25-8e22-70888d54de59 [Accessed 25 Jan. 2018].
44