Relational Database Design & Implementation for VET Management

This project focuses on the design and development of a relational database system for VET (Veterinary) management, tailored to the requirements of UKKOM LTD. The project covers various aspects, including the design of the database using appropriate tools and techniques, the implementation of a fully functional system using MS SQL Server, the creation of query languages for data retrieval, and the development of a test plan to ensure system reliability. It also includes comprehensive technical and user documentation. The design incorporates key database concepts such as data integrity, normalization (1NF, 2NF, 3NF), and the creation of an Entity-Relationship (ER) diagram to visualize the relationships between different entities. The project showcases SQL queries for data manipulation and retrieval, along with test cases to validate the database functionality. The final deliverables include a fully functional database system with detailed documentation, providing a robust solution for managing VET-related data.

Database Assignment
1

List of Contents
Introduction......................................................................................................................................6
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem............................................................................................................................................7
P1 Design a relational database system using appropriate design tools and techniques.................7
M1 Produce a comprehensive design for a fully functional system..............................................10
LO2 Develop a fully functional relational database system, based on an existing system
design:............................................................................................................................................18
P2 Develop and implement a fully functional database system using MS SQL Server:...............18
M2: Implement a full functional database system.........................................................................25
P3 Produce a query language, with queries across multiple tables produced earlier:...................26
M3 Produce appropriate management information.......................................................................29
LO3 Test the system against user and system requirements:........................................................30
P4 Create a test plan to test the database:......................................................................................30
M4. Assess the effectiveness of the testing...................................................................................34
LO4 User and Technical Documentation:.....................................................................................35
P5 Produce technical and user documentation..............................................................................35
M5: Produce fully functional system.............................................................................................39
Conclusion.....................................................................................................................................41
References......................................................................................................................................42
2

List of figures
Figure 1 ER diagram......................................................................................................................10
Figure 2 Treatment details table....................................................................................................16
Figure 3 staff table.........................................................................................................................17
Figure 4 presecription table...........................................................................................................17
Figure 5 animal table.....................................................................................................................17
Figure 6 owner table......................................................................................................................18
Figure 7 invoice detail table..........................................................................................................18
Figure 8 drugs table.......................................................................................................................18
Figure 9 doctor table......................................................................................................................18
Figure 10 consultation table...........................................................................................................19
Figure 11 clinic table.....................................................................................................................19
Figure 12 appointment table..........................................................................................................19
Figure 13 design data table of treatment details............................................................................20
Figure 14 design data view of staff table.......................................................................................20
Figure 15 design data view of prescription table...........................................................................20
Figure 16 design data view of pet animal table.............................................................................21
Figure 17 design data view of owner table....................................................................................21
Figure 18 design data view of invoice table..................................................................................21
Figure 19 v drug detail table..........................................................................................................22
3

Figure 20 design data view of doctor table....................................................................................22
Figure 21 v consultation table........................................................................................................22
Figure 22 design data view of clinic table.....................................................................................23
Figure 23 design data view of appointment table..........................................................................23
Figure 24 Query output 1...............................................................................................................24
Figure 25 Query output 2...............................................................................................................25
Figure 26 Query output 3...............................................................................................................25
Figure 27 Query output 4..............................................................................................................26
Figure 28 Query output 5..............................................................................................................26
Figure 29 Query output 6...............................................................................................................27
Figure 30 Query output 7...............................................................................................................28
Figure 31 Query output 8...............................................................................................................28
Figure 32Test case 1......................................................................................................................30
Figure 33 Test case 2.....................................................................................................................31
Figure 34 test case 3......................................................................................................................31
Figure 35 Test case 4.....................................................................................................................32
Figure 36 Animal table..................................................................................................................33
Figure 37 Owner table...................................................................................................................33
Figure 38 invoice detail table........................................................................................................33
Figure 39 doctor table....................................................................................................................34
Figure 40 Query report 1...............................................................................................................34
4

Figure 41 Query report 2...............................................................................................................34
Figure 42 VET solution relational diagram...................................................................................36
Figure 43 Invoice detail table design.............................................................................................37
Figure 44 Owner table design........................................................................................................37
Figure 45 Pet animal table design..................................................................................................37
Figure 46 Prescription table design...............................................................................................38
Figure 47 Treatment detail table design........................................................................................38
List of tables
Table 1 Example Of Primary Key...................................................................................................8
Table 2 Example of foreign key......................................................................................................8
Table 3 Data dictionary table.........................................................................................................11
Table 4 Un-normalized table.........................................................................................................13
Table 5 1NF table..........................................................................................................................13
Table 6 2NF table..........................................................................................................................14
Table 7 3NF table..........................................................................................................................15
Table 8Test case table....................................................................................................................29
5

Introduction
In this project assignment, the report is designed and developed for the management of
database system that can be provided by the company UKKOM LTD at Cambridge shire that
has experience of many years in the electronic database. Tshis database can be built to help
staff for updating of data and provide security and reliability to the database. To meet the
requirement of the company it can be built.
The database administration framework after execution effectively, send for testing and
approving information data utilizing different experiments and furnished all data in this report
with illustrations.
6

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 particular of VET arrangement of Database System:
A course of action of VET arrangement Database is used to administer whole data inside an
association by securing huge measures of data. Programming application is using the tables
of the relational database. It decreases data redundancy and presents data in an inducing way
that assistants incapable essential administration. It improves the execution of a structure and
gives advance worked in examination functionalities. The VET arrangement database tables
likewise give assistance to keep up information data by putting away in database stockroom
to get whenever through application level (Deque, 2017).
Tools & techniques of a relational database are:
Relational Database: It is the most generally perceived sort of DBMS. In this, data is put
away as tables. Application programming used for information arranging in data in tables is
MS SQL Server. It is generally called Standard Query Language which called SQL (spin-
off). Relational databases much of the time drive the association between fundamental and
web-enabled applications essential for gaining ground in an incredibly forceful market. It is
intended to pass on the reason for building and works with the social information parts and
enabling to make and use social databases in nature (Done, 2013).
Relational Database logical design:-
Logical data model information display is required in starting to design a VET course of
action physical database. Also, the steady data demonstrate winds up out of a sensible data
show. In addition, distinctive sort of data indicate starts with the planning of data show. The
coherent information display arranges formalizes the entities, or attributes and the objects.
Another basic endeavor of logical data modeling information demonstrating is to ensure that
model entity are balanced by ascribes that exceptionally identify with them. No attribute
7

should appear in an element unless it portrays the uniqueness identifier for the substance
which is the essential key.
ER-Diagram
These are substance relationship outlines which are utilized for pictorial portrayal or
comprehension of information data shared among various elements. An outline work permits
the administrator to see the connections between different substances known as ER chart.
Element (Entity):
An entity is a piece of substance exists in the database. It doesn't need to do anything; it
basically needs to exist. In database affiliation, a component can be an alone thing,
individual, place, or question. Information can be secured about such substance.
Attribute (Quality):
An attribute portrays the information about the element that ought to be secured. In the
database that the element is a specialist, properties could fuse name, individual ID, prosperity
plan enrollment, and work zone. An entity will have no less than zero attributes, and each one
of those ascribes applies just to that entity (ComputerScience, 2018).
Relationship:
A relationship depicts the connection between entities or tables in a database and it is spoken
to by precious diamond shape and it has numerous types.
Data Elements
It is the type of elements that contain the information of data like database attributes that can
be specified by their data types.
Data Types:
The information compose is utilized to characterize what sort of data is putting in which
database, for example, whole number, coast, character, string or more. Fundamentally, it
characterizes the sort of factor which is utilized as a part of the database.
8

Indexing:
Indexing is utilized for finding the area of data put in the database by giving the key to them
or a few pointers or address.
Primary (Essential)/Foreign Key:
A primary key is a piece of candidate key to characterizing its property is one of a kind in the
database and remote key is utilized in the interest of the primary key in another table to give a
connection between them.
Table 1 Example Of Primary Key
Table 2 Example of foreign key
VET solution system database entity relationship diagram:
For Vet database arrangement is planned with seven tables which will be made to build up a
database for putting away data of the program in the database. These tables are interrelated
with making the association between them. The beneath figure is delivered by the engineer
and structure requirements
9

Figure 1 ER diagram
M1 Produce a comprehensive design for a fully functional system
Integrity of data:
This is the basic snippet of security of data. In the utilization of broadcast, "Information
honesty" suggests the exactness and uniformity of informational collection missing in the
database, information warehouse, information store or another frame. Data Integrity could be
10

utilized to depict a method, an express or a limit – and is reliably used as a center individual
for "information quality". Data respects are sorted out by a data show and furthermore, data
composes (Ioffe, 2015).
Validation of data:
While using SQL, approval of information data is a piece of a VET arrangement database.
The main factors of data respectability are imperatives and the erase and refresh choices. In
SQL the major necessities are checked, interesting, not an invalid, and essential limitations.
The checked restrictions are used for confirmation that a declaration almost the data is
legitimate for completely lines in a table. The novel limitation guarantees that no 2 lines have
comparative characteristics in that segment. Not valid imperative is put on the portion &
communicates that in the area where data are required (Database Management, 2018).
Data dictionary:
It is a standout among a most basic portion of a VET arrangement database. In its data word
reference, which is observed only the table’s arrangement for that gives the information about
the database? A word reference for information contains the meanings of all diagram
challenges in the database (tables, ordering, gatherings, groups, bundles, triggers, and so
forth), memory required, the example objects, column, default regards for segments,
uprightness basic data, name of clients, benefits, and parts for each client has been in all
actuality, reviewing information (Studytonight, 2018).
Table 3 Data dictionary table
Table name Attribute Description Data type Key
Pet_animal Petanimal_id Identity of animal Integer Primary
Owner_id Identity of owner Integer Foreign
Sex type of person or
gender
Char
Animal_type Type of animal Char
11

age Age of animal Integer
Appointment Appointment_id Id of appointment Integer Primary
Doctor_id Doctor id Integer Foreign
Owner_id Id of owner Integer Foreign
Petanimal_id Id of anima Integer Foreign
Appointment_date Date of
appointment
Date
Owner Owner_id Id of owner Integer Primary
Owner_fname First name of
owner
Char
Ownwe_lname Last name of
owner
Char
Owner_contact Contact details of
owner
Integer
Owner_address Address of owner Varchar
Staff Staff_id Identity of staff Integer Primary
Clinic_id Identity of clinic Integer Foreign
Staff_domain Staff are of work
Clinic Clinic_id Id of clinic Integer Primary
Clinic_name Name of clinic Char
Clinic_contact Phone no. of clinic Integer
Clinic_location Location of Varchar
12