VET Solution Database: Design, Development, and Implementation Report
VerifiedAdded on  2024/05/21
|46
|4388
|345
Report
AI Summary
This report details the design and development of a database system for VET Solution, a veterinary company in Cambridgeshire, using MS SQL Server 2014. It covers the entire process, starting with the design phase, including the ER diagram, data validation, data dictionary, and data normalization. The report then discusses the development of the relational database, including table design and SQL queries for data retrieval and manipulation. Furthermore, it includes a test plan to ensure the database meets user and system requirements, along with detailed technical and user documentation. The documentation provides guidance on using and maintaining the database, making it a comprehensive resource for understanding the database implementation for VET Solution. Finally, it discusses system security and database maintenance.
Database Assignment
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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
Role of database management system..............................................................................6
User Requirement..............................................................................................................6
System Requirement..........................................................................................................7
Hardware Requirement.....................................................................................................7
Software requirement........................................................................................................7
ER diagram.........................................................................................................................7
Entities:...............................................................................................................................8
Attributes:..........................................................................................................................8
Relation:.............................................................................................................................8
M1 produce a comprehensive design for a fully functional system which includes interface
and output designs, data validations, data normalisation and data dictionary....................9
Data Validation...................................................................................................................9
Data dictionary...................................................................................................................9
Data normalization...........................................................................................................12
Un-normalization.............................................................................................................12
First Normalization...........................................................................................................13
Second Normalization......................................................................................................14
Third Normalization.........................................................................................................14
LO2 Develop a fully functional relational database system, based on an existing system
design.......................................................................................................................................16
1
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
Role of database management system..............................................................................6
User Requirement..............................................................................................................6
System Requirement..........................................................................................................7
Hardware Requirement.....................................................................................................7
Software requirement........................................................................................................7
ER diagram.........................................................................................................................7
Entities:...............................................................................................................................8
Attributes:..........................................................................................................................8
Relation:.............................................................................................................................8
M1 produce a comprehensive design for a fully functional system which includes interface
and output designs, data validations, data normalisation and data dictionary....................9
Data Validation...................................................................................................................9
Data dictionary...................................................................................................................9
Data normalization...........................................................................................................12
Un-normalization.............................................................................................................12
First Normalization...........................................................................................................13
Second Normalization......................................................................................................14
Third Normalization.........................................................................................................14
LO2 Develop a fully functional relational database system, based on an existing system
design.......................................................................................................................................16
1
P2. Once the ERD designs in P1 were accepted you have been asked to develop the
database system using MS SQL Server 2014.......................................................................16
Relational Database.........................................................................................................16
Design view of tables.......................................................................................................17
P3. Produce a query language, with queries across multiple tables for the database.......22
M2 Implement a fully functional database system which includes system security and
database maintenance.........................................................................................................27
LO3 Test the system against user and system requirements:.................................................28
P4 Create a test plan to test the database:.........................................................................28
M4 Create and populate tables, validation, verification, data types and referential
integrity against user requirements.....................................................................................32
LO4 Produce technical and user documentation.....................................................................34
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company.............................................................................................................34
User documentation........................................................................................................34
Technical documentation.................................................................................................39
M5 produce technical and user documentation for a fully functional system...................42
Conclusion................................................................................................................................43
References................................................................................................................................44
List of figures
Figure 1 ER diagram...................................................................................................................8
Figure 2 Data Validation for VET solution..................................................................................9
Figure 3 Relational Schema for VET solutions..........................................................................16
Figure 4 Design view of Doctor................................................................................................17
Figure 5 Datasheet view of Doctor..........................................................................................17
Figure 6 Design view of Clinic...................................................................................................17
Figure 7 Datasheet view of Clinic.............................................................................................18
2
database system using MS SQL Server 2014.......................................................................16
Relational Database.........................................................................................................16
Design view of tables.......................................................................................................17
P3. Produce a query language, with queries across multiple tables for the database.......22
M2 Implement a fully functional database system which includes system security and
database maintenance.........................................................................................................27
LO3 Test the system against user and system requirements:.................................................28
P4 Create a test plan to test the database:.........................................................................28
M4 Create and populate tables, validation, verification, data types and referential
integrity against user requirements.....................................................................................32
LO4 Produce technical and user documentation.....................................................................34
P5. Produce fully technical and user documentation for your designed database for VET
SOLUTION Company.............................................................................................................34
User documentation........................................................................................................34
Technical documentation.................................................................................................39
M5 produce technical and user documentation for a fully functional system...................42
Conclusion................................................................................................................................43
References................................................................................................................................44
List of figures
Figure 1 ER diagram...................................................................................................................8
Figure 2 Data Validation for VET solution..................................................................................9
Figure 3 Relational Schema for VET solutions..........................................................................16
Figure 4 Design view of Doctor................................................................................................17
Figure 5 Datasheet view of Doctor..........................................................................................17
Figure 6 Design view of Clinic...................................................................................................17
Figure 7 Datasheet view of Clinic.............................................................................................18
2
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
Figure 8 Design view of Staff....................................................................................................18
Figure 9 Datasheet view of Staff..............................................................................................18
Figure 10 Design view of Appointment....................................................................................18
Figure 11 Datasheet view of Appointment..............................................................................19
Figure 12 Design view of petOwner.........................................................................................19
Figure 13 Datasheet view of petOwner...................................................................................19
Figure 14 Design view of Patient..............................................................................................20
Figure 15 Datasheet view of Patient........................................................................................20
Figure 16 Design view of veterinary.........................................................................................20
Figure 17 datasheet view of Veterinary...................................................................................20
Figure 18 design view of Invoice..............................................................................................21
Figure 19 datasheet view of Invoice........................................................................................21
Figure 20 Query1 output..........................................................................................................22
Figure 21 Query2 output..........................................................................................................23
Figure 22 Query3 output..........................................................................................................23
Figure 23 Query4 output..........................................................................................................24
Figure 24 Query5 output..........................................................................................................25
Figure 25 Query6 output..........................................................................................................26
Figure 26 Query7 output..........................................................................................................26
Figure 27 Test case 1................................................................................................................29
Figure 28 Test case 2................................................................................................................29
Figure 29 Test case 3................................................................................................................30
Figure 30 Test case 4................................................................................................................30
Figure 31 Test case 5................................................................................................................31
Figure 32 Validation 1..............................................................................................................32
Figure 33 Validation 2..............................................................................................................33
Figure 34 Doctor table.............................................................................................................34
Figure 35 Clinic table................................................................................................................34
Figure 36 Staff table.................................................................................................................35
Figure 37 Invoice table.............................................................................................................35
Figure 38 Appointment table...................................................................................................35
Figure 39 Owner Table.............................................................................................................35
3
Figure 9 Datasheet view of Staff..............................................................................................18
Figure 10 Design view of Appointment....................................................................................18
Figure 11 Datasheet view of Appointment..............................................................................19
Figure 12 Design view of petOwner.........................................................................................19
Figure 13 Datasheet view of petOwner...................................................................................19
Figure 14 Design view of Patient..............................................................................................20
Figure 15 Datasheet view of Patient........................................................................................20
Figure 16 Design view of veterinary.........................................................................................20
Figure 17 datasheet view of Veterinary...................................................................................20
Figure 18 design view of Invoice..............................................................................................21
Figure 19 datasheet view of Invoice........................................................................................21
Figure 20 Query1 output..........................................................................................................22
Figure 21 Query2 output..........................................................................................................23
Figure 22 Query3 output..........................................................................................................23
Figure 23 Query4 output..........................................................................................................24
Figure 24 Query5 output..........................................................................................................25
Figure 25 Query6 output..........................................................................................................26
Figure 26 Query7 output..........................................................................................................26
Figure 27 Test case 1................................................................................................................29
Figure 28 Test case 2................................................................................................................29
Figure 29 Test case 3................................................................................................................30
Figure 30 Test case 4................................................................................................................30
Figure 31 Test case 5................................................................................................................31
Figure 32 Validation 1..............................................................................................................32
Figure 33 Validation 2..............................................................................................................33
Figure 34 Doctor table.............................................................................................................34
Figure 35 Clinic table................................................................................................................34
Figure 36 Staff table.................................................................................................................35
Figure 37 Invoice table.............................................................................................................35
Figure 38 Appointment table...................................................................................................35
Figure 39 Owner Table.............................................................................................................35
3
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Figure 40 Patient table.............................................................................................................36
Figure 41 Veterinary table.......................................................................................................36
Figure 42 query 1.....................................................................................................................36
Figure 43 query 2.....................................................................................................................37
Figure 44 query 3.....................................................................................................................37
Figure 45 query 4.....................................................................................................................38
Figure 46 query 5.....................................................................................................................38
Figure 47 doctor table design view..........................................................................................39
Figure 48 clinic table design view............................................................................................39
Figure 49 staff table design view.............................................................................................39
Figure 50 appointment table design view...............................................................................39
Figure 51 owner table design view..........................................................................................40
Figure 52 patient table design view.........................................................................................40
Figure 53 veterinary table design view....................................................................................40
Figure 54 invoice table design view.........................................................................................40
Figure 55 relationship diagram................................................................................................41
Figure 56 Functional dependency............................................................................................42
List of Tables
Table 1 Data dictionary for VET solution.................................................................................10
Table 2 Un-normalization.........................................................................................................12
Table 3 First normalization.......................................................................................................13
Table 4 second normalization..................................................................................................14
Table 5 third normalization......................................................................................................14
Table 6 Test Case......................................................................................................................28
4
Figure 41 Veterinary table.......................................................................................................36
Figure 42 query 1.....................................................................................................................36
Figure 43 query 2.....................................................................................................................37
Figure 44 query 3.....................................................................................................................37
Figure 45 query 4.....................................................................................................................38
Figure 46 query 5.....................................................................................................................38
Figure 47 doctor table design view..........................................................................................39
Figure 48 clinic table design view............................................................................................39
Figure 49 staff table design view.............................................................................................39
Figure 50 appointment table design view...............................................................................39
Figure 51 owner table design view..........................................................................................40
Figure 52 patient table design view.........................................................................................40
Figure 53 veterinary table design view....................................................................................40
Figure 54 invoice table design view.........................................................................................40
Figure 55 relationship diagram................................................................................................41
Figure 56 Functional dependency............................................................................................42
List of Tables
Table 1 Data dictionary for VET solution.................................................................................10
Table 2 Un-normalization.........................................................................................................12
Table 3 First normalization.......................................................................................................13
Table 4 second normalization..................................................................................................14
Table 5 third normalization......................................................................................................14
Table 6 Test Case......................................................................................................................28
4
Introduction
This assessment main objective is to design and develop a database for the VET solution of
Cambridgeshire. VET solution is a veterinary company that provides the care services for
animals. The company needs a database for information storage, retrieval, and update and
alter. It could be managed by developing a database management system in the company.
Moreover, company needs a database for improving the communication and information
sharing among the Company. The database for the company will be developed using the MS
SQL server 2014 that provides a great database management properties to the company.
This report is going to include designing procedure of database, development of database
and its testing and finally a full user and technical documentation will be provided for the
designed database.
5
This assessment main objective is to design and develop a database for the VET solution of
Cambridgeshire. VET solution is a veterinary company that provides the care services for
animals. The company needs a database for information storage, retrieval, and update and
alter. It could be managed by developing a database management system in the company.
Moreover, company needs a database for improving the communication and information
sharing among the Company. The database for the company will be developed using the MS
SQL server 2014 that provides a great database management properties to the company.
This report is going to include designing procedure of database, development of database
and its testing and finally a full user and technical documentation will be provided for the
designed database.
5
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
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
For designing the VET solution company’s database, firstly an appropriate designed will be
prepared as the prototype like ERD diagram using a design tool. As the VET solution is
increasing its branch in different countries, it is must for the company to shift on the
database management system (Pavlo, Angulo, Arulraj, 2017). The customers and staff of the
company are increasing day by day so to handle this situation company decided to go for a
database management system instead of a manual database on paper. The company
requires a database that could add and alter the patient details and staff detail, also the
single database must handle the information of all the branches of the company. The
database is needed for creating a properly printed bill for the customers (Krishnamurthy,
Thombre, Conway, et.al, 2014).
Role of database management system
The important objective for which the company chooses the database management system
is to develop and preserve the relationships that exist among the data of the VET solution
company.
User Requirement
ï‚· Database must be able to edit the information inserted.
ï‚· Database created should record and manage detail information of Patients (i.e.
Pets).
ï‚· Database should able to add manage the record of staff.
ï‚· Database created should record and manage detail information of Pet Owners.
ï‚· Database created should be able to add and manage the invoice details to pet
Owner for their pet treatments.
ï‚· Database must be able to add and manage the appointment details of the patient at
all clinics.
6
a substantial problem
P1. Design a relational database system using appropriate design tools and
techniques
For designing the VET solution company’s database, firstly an appropriate designed will be
prepared as the prototype like ERD diagram using a design tool. As the VET solution is
increasing its branch in different countries, it is must for the company to shift on the
database management system (Pavlo, Angulo, Arulraj, 2017). The customers and staff of the
company are increasing day by day so to handle this situation company decided to go for a
database management system instead of a manual database on paper. The company
requires a database that could add and alter the patient details and staff detail, also the
single database must handle the information of all the branches of the company. The
database is needed for creating a properly printed bill for the customers (Krishnamurthy,
Thombre, Conway, et.al, 2014).
Role of database management system
The important objective for which the company chooses the database management system
is to develop and preserve the relationships that exist among the data of the VET solution
company.
User Requirement
ï‚· Database must be able to edit the information inserted.
ï‚· Database created should record and manage detail information of Patients (i.e.
Pets).
ï‚· Database should able to add manage the record of staff.
ï‚· Database created should record and manage detail information of Pet Owners.
ï‚· Database created should be able to add and manage the invoice details to pet
Owner for their pet treatments.
ï‚· Database must be able to add and manage the appointment details of the patient at
all clinics.
6
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
ï‚· Database must be able to add and manage the diagnosis and treatment details given
to the Pet.
ï‚· Database must be able to add and manage the details of services (i.e. treatment)
given to pet (Coronel, and Morris, 2016).
System Requirement
For designing and managing a database a system with least specifications is required. There
are some hardware and software necessities for developing the database.
ï‚· Database must work efficiently and effectively.
ï‚· Database should have distinctive identification and appropriate presentation with
name, contact, address and more.
ï‚· Database must be able to perform fast and have high usability (Coronel, and Morris,
2016).
For designing the VET solution database some requirements are needed that are;
Hardware Requirement
1. A 64-bit processor.
2. Hard disk of minimum 2 GB is required.
3. RAM of minimum 2 GB is required.
4. Resolution display of 1024*468.
Software requirement
1. An operating system like Windows, Linux etc.
2. Design tool like Draw.io, star UML for ERD design.
3. MS SQL server 2014.
ER diagram
It is an acronym for entity relationship diagram. ER diagram is a top-down approach to
designing the database. It is a conceptual diagram that is used to define the relationships
among the several entities. For building an ER diagram first the entities are identified and
after that relation is represented among the identified entities. Some more data is added to
each entity called attributes which defines the characteristics of the Entities (Lucidchart,
2017).
7
to the Pet.
ï‚· Database must be able to add and manage the details of services (i.e. treatment)
given to pet (Coronel, and Morris, 2016).
System Requirement
For designing and managing a database a system with least specifications is required. There
are some hardware and software necessities for developing the database.
ï‚· Database must work efficiently and effectively.
ï‚· Database should have distinctive identification and appropriate presentation with
name, contact, address and more.
ï‚· Database must be able to perform fast and have high usability (Coronel, and Morris,
2016).
For designing the VET solution database some requirements are needed that are;
Hardware Requirement
1. A 64-bit processor.
2. Hard disk of minimum 2 GB is required.
3. RAM of minimum 2 GB is required.
4. Resolution display of 1024*468.
Software requirement
1. An operating system like Windows, Linux etc.
2. Design tool like Draw.io, star UML for ERD design.
3. MS SQL server 2014.
ER diagram
It is an acronym for entity relationship diagram. ER diagram is a top-down approach to
designing the database. It is a conceptual diagram that is used to define the relationships
among the several entities. For building an ER diagram first the entities are identified and
after that relation is represented among the identified entities. Some more data is added to
each entity called attributes which defines the characteristics of the Entities (Lucidchart,
2017).
7
Entities:
It is a collection of objects having similar characteristics that is, identified by an organization
as having a liberated existence.
Attributes:
Every entity has specific characteristics called an an attribute.
Relation:
There is a relation between the entities which is known as a relation. Relation can be of
different cardinality ratio that is one to one, one too many, many to many.
Figure 1 ER diagram
8
It is a collection of objects having similar characteristics that is, identified by an organization
as having a liberated existence.
Attributes:
Every entity has specific characteristics called an an attribute.
Relation:
There is a relation between the entities which is known as a relation. Relation can be of
different cardinality ratio that is one to one, one too many, many to many.
Figure 1 ER diagram
8
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
M1 produce a comprehensive design for a fully functional system which includes
interface and output designs, data validations, data normalisation and data
dictionary.
Data Validation
Data validation is needed for the data performance, assurance, policy for organized data.
There are different types of validation (Witkowski, Dorman and Greenfield, 2014);
ï‚· Authentication of data type
ï‚· Authentication of code
ï‚· Authentication of constraint
ï‚· Authentication of the structure.
Example:
Figure 2 Data Validation for VET solution
Data dictionary
It is a matrix of data definition. It is a set of data that is warehoused in the database and
have some meaning. It is used to extract the meaningful data from the displayed
information. Data dictionary has different elements in it that are;
9
Owner name
cannot be
null.
interface and output designs, data validations, data normalisation and data
dictionary.
Data Validation
Data validation is needed for the data performance, assurance, policy for organized data.
There are different types of validation (Witkowski, Dorman and Greenfield, 2014);
ï‚· Authentication of data type
ï‚· Authentication of code
ï‚· Authentication of constraint
ï‚· Authentication of the structure.
Example:
Figure 2 Data Validation for VET solution
Data dictionary
It is a matrix of data definition. It is a set of data that is warehoused in the database and
have some meaning. It is used to extract the meaningful data from the displayed
information. Data dictionary has different elements in it that are;
9
Owner name
cannot be
null.
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
ï‚· Elective
ï‚· Attribute name and
ï‚· Attribute type
Table 1 Data dictionary for VET solution
Table name Attribute Data type Description PK/FK
Doctor doctor ID Int Doctor
unique
identity
number
PK
doctorName Text Doctor’s
name
doctorContact Varchar(50) Telephone
number of
doctor
Patient patientID Int Patient
unique
identity
number
PK
petName Text Name of the
pet
petType Text Type of the
pet
petGender Text Gender of
pet
ownerID Int Unique
number of
owner
FK
clinicID int Unique
number of
clinic
FK
petOwner ownerID Int Unique
number of
owner
PK
ownerName Text Name of
owner
ownerAddress Varchar(50) Address of
10
ï‚· Attribute name and
ï‚· Attribute type
Table 1 Data dictionary for VET solution
Table name Attribute Data type Description PK/FK
Doctor doctor ID Int Doctor
unique
identity
number
PK
doctorName Text Doctor’s
name
doctorContact Varchar(50) Telephone
number of
doctor
Patient patientID Int Patient
unique
identity
number
PK
petName Text Name of the
pet
petType Text Type of the
pet
petGender Text Gender of
pet
ownerID Int Unique
number of
owner
FK
clinicID int Unique
number of
clinic
FK
petOwner ownerID Int Unique
number of
owner
PK
ownerName Text Name of
owner
ownerAddress Varchar(50) Address of
10
owner
ownerContact Varchar(50) Contact
number of
owner
clinicID Int Unique
number of
clinic
FK
appointmentNo Int Appointment
number
FK
Appointment appointmentNo Int Appointment
number
PK
appointmentDate Date Date of
appointment
.
appointmentTime Time(7) Time of the
appointment
doctorID int Unique
number of
doctor
FK
Invoice invoiceID Int Unique
number of
invoice
PK
invoiceDate Date Date of
invoice
Payment Money Total amount
to be paid
InvoiceType Varchar(50) Type of
invoice
ownerID Int Unique id of
owner
FK
veterinaryprocedureID Int Unique id of
veterinary
FK
petname text Name of pet FK
Staff staffID Int Unique
number of
staff
PK
11
ownerContact Varchar(50) Contact
number of
owner
clinicID Int Unique
number of
clinic
FK
appointmentNo Int Appointment
number
FK
Appointment appointmentNo Int Appointment
number
PK
appointmentDate Date Date of
appointment
.
appointmentTime Time(7) Time of the
appointment
doctorID int Unique
number of
doctor
FK
Invoice invoiceID Int Unique
number of
invoice
PK
invoiceDate Date Date of
invoice
Payment Money Total amount
to be paid
InvoiceType Varchar(50) Type of
invoice
ownerID Int Unique id of
owner
FK
veterinaryprocedureID Int Unique id of
veterinary
FK
petname text Name of pet FK
Staff staffID Int Unique
number of
staff
PK
11
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
1 out of 46
Related Documents
Your All-in-One AI-Powered Toolkit for Academic Success.
 +13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.