VET Solution: Relational Database System Design and Implementation

This project details the design, implementation, and testing of a relational database system for VET Solutions using MS SQL Server. The project covers logical database design using ER diagrams, data normalization to 3NF, SQL query development, and thorough testing with test cases. It also includes user and technical documentation for the fully functional system. Key aspects such as data integrity, data validation, and the use of key constraints are addressed to ensure a robust and secure database. The report provides examples of SQL queries, their design, and testing procedures to validate the system's functionality and adherence to client requirements. Data dictionaries and functional dependencies are utilized to create a comprehensive and efficient database solution.

DATABASE ASSIGNMENT

Contents
List of figures........................................................................................................................................1
List of tables..........................................................................................................................................2
Introduction...........................................................................................................................................3
LO1 Use an appropriate design tool to design a relational database system for a substantial problem. .4
P1 Design a relational database system using appropriate design tools and techniques.....................4
M1 Produce a comprehensive design for a fully functional system...................................................6
LO2 Develop a fully functional relational database system, based on an existing system design........11
P2 Develop and implement a fully functional database system using MS SQL Server....................11
M2 Implement a fully functional database system...........................................................................13
P3 Produce a query language, with queries across multiple tables produced earlier........................13
M3 Produce appropriate management information..........................................................................18
LO3 Test the system against user and system requirements................................................................19
P4 Create a test plan to test the database:........................................................................................19
M4. Assess the effectiveness of the testing......................................................................................21
LO4 User and Technical Documentation:............................................................................................22
P5 Produce technical and user documentation.................................................................................22
M5: Produce fully functional system...............................................................................................30
Conclusion...........................................................................................................................................32
References...........................................................................................................................................33
List of figures
Figure 1 example of a key constraint.....................................................................................................4
Figure 2 ER diagram for VET solution..................................................................................................5
Figure 3 table of animal.........................................................................................................................8
Figure 4 table of appointment................................................................................................................8
Figure 5 table of the bill........................................................................................................................9
Figure 6 table of branch.........................................................................................................................9
Figure 7 table of owner..........................................................................................................................9
Figure 8 table of prescription...............................................................................................................10
Figure 9 table of staff...........................................................................................................................10
Figure 10 query 1 output......................................................................................................................11
Figure 11 output for query 2................................................................................................................11
Figure 12 output of query 3.................................................................................................................12
Figure 13 output for query 4................................................................................................................12
Figure 14 output of query 5.................................................................................................................13
Figure 15 output for query 6................................................................................................................14
Figure 16 output of query 7.................................................................................................................14
Figure 17 output of query 8.................................................................................................................15
1

Figure 18 test case 1............................................................................................................................16
Figure 19 test case 2............................................................................................................................17
Figure 20 test case 3............................................................................................................................17
Figure 21 test case 4............................................................................................................................17
Figure 22 test case 5............................................................................................................................17
Figure 23 test case 6............................................................................................................................18
Figure 24 test case 7............................................................................................................................18
Figure 25 test case 8............................................................................................................................18
Figure 26 animal table.........................................................................................................................19
Figure 27 appointment table................................................................................................................19
Figure 28 bill table...............................................................................................................................20
Figure 29 branch table.........................................................................................................................20
Figure 30 owner table..........................................................................................................................20
Figure 31 prescription table.................................................................................................................21
Figure 32 staff table.............................................................................................................................21
Figure 33 query 1.................................................................................................................................22
Figure 34 query 2.................................................................................................................................22
Figure 35 query 3.................................................................................................................................23
Figure 36 query 4.................................................................................................................................23
Figure 37 query 5.................................................................................................................................23
Figure 38 query 6.................................................................................................................................24
Figure 39 query 7.................................................................................................................................24
Figure 40 query 8.................................................................................................................................24
Figure 41 ERD for VET Solution........................................................................................................25
Figure 42 design of the animal table....................................................................................................25
Figure 43 design of appointment table.................................................................................................26
Figure 44 design of bill table...............................................................................................................26
Figure 45 design of branch table..........................................................................................................26
Figure 46 design of owner table..........................................................................................................27
Figure 47 design of the prescription table............................................................................................27
Figure 48 design of staff table.............................................................................................................27
Figure 49 diagram of functional dependency.......................................................................................28
List of tables
Table 1 Data Dictionary table................................................................................................................7
Table 2: First level normalized tables....................................................................................................9
Table 3- Second level normalized tables...............................................................................................9
Table 4- Third level normalized tables................................................................................................10
Table 5 test case table..........................................................................................................................19
2

Introduction
This database system is composed and produced for VET Solutions by ULKOM Ltd in which
it fulfills the all requirement of client and system is fully functional with the security of the
database. The language SQL is used to develop the queries and this document will present
some examples of queries with their design and testing part also to ensure that this database
system will work successfully. All the required validation will also prove in this report and
satisfy the requirements of the client and utilize concepts of DBMS as functional
dependencies and key constraints mainly.
3

LO1 Use an appropriate design tool to design a relational database system
for a substantial problem
P1 Design a relational database system using appropriate design tools and
techniques.
Roles and specification of VET solution Database System:
A course of action of VET arrangement in a database system is used to administer whole data inside
an association by securing enormous measures of data. The relational database is used by applications
of programming in which It decreases data redundancy and presents data in an inducing way that
assistants incapable fundamental authority. It improves the execution of a structure and gives advance
worked in functionalities of examining or analyzing. The VET arrangement for database tables
likewise gives assistance to keep up information data by putting away in database storage to get
whenever required through application level.
Relational Database tool and some techniques:
The most used database is Relational Database in which it perceives sort of DBMS. In this database,
all record is maintained in the form of tables. MS SQL Server is utilized for orchestrating data in
tables for application programming. It is generally called as Sequel or “Standard Query Language”
mainly known by SQL. The relational l databases oftentimes drive the association essential and web-
enabled applications fundamental for gaining ground in an astoundingly forceful market. It is intended
to pass on the reason for building and works with the social information parts and engaging to make
and use relational databases in the surrounding or in the technical ground (Selinger, P.G., Astrahan,
M.M., Chamberlin, D.D., Lorie, R.A. & T.G., 1988).
Logical Design for Relational Database:
To design a VET course of action, requirement of logical data model initially to design physical
database. Additionally, the predictable data indicate ending of a sensible data information. Besides,
unique sort of data demonstrate starts with the planning of data show. The attributes, entities and
objects or methods are required to make a logical database on formalizing. Another basic endeavor of
logical database is to ensure that the model elements are balanced by credits that uncommonly
identify with them by providing key constraint as primary key which defines the property of
uniqueness.
ER- Diagram
The Entity relationship diagram technique is utilized for representation of database models in a figure
which describes all the entities and their attributes with relations easily and it’ll help to the developer
for implement database by seeing the entities relations.
Entity
An entity is main classes or actors of a database system which consists of attributes or properties and
can share with other entity using key constraints property applied to attributes.
Attribute
An attribute is a property of entity or classes which are utilized in the database to develop a system for
storing data information based on attributes. According to this database system, attributes are Animal
4

Id, the Animal category of animal class, Appointment number, Appointment date, Branch name,
Prescription number and Employee id of appointment class and many more class is there with their
attributes.
Relationship
In the database, the relationship is interaction or connection among various class tables or entities and
the symbol of relationship is of diamond shape.
Data Elements
The information which will be put for specific attributes to store in the database with validating data
on the basis of its data types.
Data Types
Data types are predefined functions or variables on which the data information inserted in fields is
only based on that then it’ll accept to store in the database.
Indexing
The property of indexing is utilized for searching element in the database by providing a key to that
attribute or variable. It is used in hashing to search the value in the bucket, in programming, it can be
implemented using pointers which will help to locate the destination.
Primary/Foreign Key:
These are the part of key constraints in which primary key is utilized for providing uniqueness on
defined value, for example, the ID of the student which can’t be same, the foreign key is utilized for
providing a fetching feature from another table on which primary key is also defined which work as a
foreign key (Burroughs, T.K., Gansemer, S.J., Lee, W.D., Voldal, E.E., Roger & C.A, 1999).
Figure 1 example of a key constraint
Entity relationship diagram for VET Solution database
Entity-relationship diagram and data flow diagram are to be made for a veterinary company named as
VET Solution. This would help VET solution in designing relational database system to define the
attributes and entities with the appropriate relationship among them. Below there is ERD for the VET
solution (Briand, H., Habrias, H., Hue, J.F & Y, 1985).
5

Figure 2 ER diagram for VET solution
M1 Produce a comprehensive design for a fully functional system
Data Integrity
The data information which is stored in the database should be accurate as didn’t find corrupt or fake
and reliable too. Because these features help to provide a fully functional system for implement or
development. The data integrity is basically a term used for providing security to data information and
prevent from editing or accessing form unauthorized user.
Data Validation
The data validation is used to validating the data part on entering in the database and it is tested in
testing part with some cases. In the database, validation can be accomplished by applying key
constraints on variables or attributes as they are Not NULL. UNIQUE, PRIMARY KEY, FOREIGN
KEY, CHECK and few more utilized in a database on development. The primary key is a
combination of not null and unique key constraint and it follows the property of both, not null
constraint is utilized on an attribute for not accepting blank on that field i.e, empty, the unique key
constraint is for accepting totally different id.
Data Dictionary
The data dictionaries are utilized for providing a short name to original name, basically, it provides a
description of data elements as its type, information, key constraints and attributes of the
entity. The data dictionaries only provide a view over it as it not contain original data
information. If data dictionary is not there then accessing to database can’t be possible for the
database system.
Table 1 Data Dictionary table
Name of table Attributes Description Type of
Data
Key
constraints
Animal Animal_id Used for Varchar() Primary
6

providing id’s
Animal_category Defining
category of
animal
Varchar()
Specific_trait Provide any
special traits of
the animal
Varchar()
location Location of
animal
Varchar()
Appointment Appointment_number Provides
number of
Appointment
Varchar() Primary
Appointment_date Provide date of
Appointment
Varchar()
Branch_name Provides the
name of the
branch for
Appointment
Varchar() Foreign
Prescription_number Provides
prescription
number for
appointment
Varchar() Foreign
Employee_id Provide id of
an employee
for
appointment
Varchar() Foreign
Bill Bill_number Provides bill
number
Varchar() Primary
Bill_date Provides Date
of bill
Varchar()
Branch_name Provides
branch name
for bill
Varchar()
Appointment_number Provides
appointment
number for bill
Varchar() Foreign
Employee_id Provides id of
an employee
for the bill
Varchar() Foreign
Branch Branch_name Name of
branch
Varchar() Primary
Contact_number Provide contact
number
Varchar()
Employee_id The id of an
employee for
branch
Varchar() Foreign
Address Address of
branch
Varchar()
Owner Owner_batch batch of owner Varchar() Primary
Animal_id The id of
animal for the
owner
Varchar() Foreign
Contact_number Contact
number of
Varchar()
7

owner
location Location of
owner
Varchar()
Prescription Prescription_number Number of
prescription
Varchar() Primary
Animal_id Id of animal
for prescription
Varchar() Foreign
Prescription_date Date of
prescription
Varchar() Foreign
Owner_batch Batch of owner
for prescription
Varchar() Foreign
Staff Employee_id The id of an
employee for
staff
Varchar() primary
Contact_number Contact
number of staff
Varchar()
Staff_role Role of staff Varchar()
Salary Salary of staff Varchar()
Address Address of
staff
Varchar()
Data Normalization
The data normalization is required in any database for providing a fully functional and simple, secure,
robust database by fulfilling the requirements of clients which will store the data in the database.
Normalization has some techniques to make simpler the database like normal forms (1, 2, 3, 4, 5) but
1, 2, 3 are mainly used to reduce the database and make it simpler for developers (Bernstein & P.A.,
1976).
8

1 NF
For the database tables to be in first normal form, the attributes should not be composite or multi-
valued or derived.
Table 2: First level normalized tables
Animal Owner Staff
Animal_id Owner_batch Employee_id
Animal_category Animal_id Contact_number
Specific_trait Contact_number Staff_role
location location Salary
Prescription_number Address
Prescription_date Branch_name
2NF
There are many partial dependencies in the table which needs to be removed. The second level
normalized tables are as follows:
Table 3- Second level normalized tables
Animal Owner Branch Staff Appointment
Animal_id Owner_batch Branch_name Employee_id Appointment_numbe
r
Animal_category Animal_id Contact_numbe
r
Contact_numbe
r
Appointment_date
Specific_trait Contact_numbe
r
Employee_id Staff_role Branch_name
location location Address Salary Prescription_number
Prescription_numbe
r
Address Employee_id
Prescription_date Bill
9

3NF
The transitive dependencies need to be eliminated for the tables to be the third level of normalized.
Table 4- Third level normalized tables
Animal Owner Prescription Branch Staff Appointment Bill
Animal_id Owner_ba
tch
Prescription_
number
Branch_na
me
Employee
_id
Appointment_
number
Bill_number
Animal_ca
tegory
Animal_id Animal_id Contact_n
umber
Contact_n
umber
Appointment_
date
Bill_date
Specific_tr
ait
Contact_n
umber
Prescription_
date
Employee
_id
Staff_role Branch_name Branch_name
location location Owner_batch Address Salary Prescription_n
umber
Appointment_
number
Address Employee_id Employee_id
10

LO2 Develop a fully functional relational database system, based on an
existing system design
P2 Develop and implement a fully functional database system using MS SQL
Server
The database system utilizes the relational concept to develop a fully functional system which stores
the data information of entities and its attributes in the form of columns & rows. The database utilizes
the features of SQL language as data definition, data manipulation and more in which they consist of
some functions as select, update, alter, modify, truncate, drop, insert, create or many more. The below
figures will show the developed database system using MS SQL SERVER 2014 which generates the
database in the more secure way and robust in nature (Mistry, R & S., 2014).
Animal: The below table consist of attributes, data types, and a checkbox for null.
Figure 3 table of animal
Appointment: The below table consist of attributes, data types, and a checkbox for null.
Figure 4 table of appointment
Bill: The below table consist of attributes, data types, and a checkbox for null.
11