Database Assignment: Designing and Implementing a Relational Database System for VET Solutions

This report details the design, implementation, and testing of a relational database system for VET Solutions, a company in Cambridgeshire specializing in electronic database systems. The database is designed to meet the company's specific requirements, providing a secure and efficient platform for storing and managing critical data. The report covers the logical design process, including entity relationship diagrams (ERDs), data normalization, and data integrity considerations. It then delves into the implementation using MS SQL Server, showcasing the creation of tables, defining data types, and implementing primary and foreign keys. The report also includes a comprehensive test plan with various test cases to ensure the database's functionality and data integrity. Finally, the report provides user and technical documentation, including table designs, functional dependency diagrams, and sample queries, to guide users and developers in interacting with the database.

Database Assignment

Contents
Introduction...............................................................................................................................4
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem......................................................................................................................................5
P1 Design a relational database system using appropriate design tools and techniques.....5
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:......................................................................................................................................14
P2 Develop and implement a fully functional database system using MS SQL Server:.......14
M2 Implement a fully functional database system..............................................................19
P3 Produce a query language, with queries across multiple tables produced earlier:........20
M3 Produce appropriate management information...........................................................23
LO3 Test the system against user and system requirements:.................................................24
P4 Create a test plan to test the database:..........................................................................24
M4. Assess the effectiveness of the testing.........................................................................26
LO4 User and Technical Documentation:................................................................................27
P5 Produce technical and user documentation...................................................................27
M5: Produce fully functional system....................................................................................35
Conclusion................................................................................................................................36
References................................................................................................................................37
1

List of figures
Figure 1 ER Diagram...................................................................................................................8
Figure 2 Unormalised Form......................................................................................................11
Figure 3 1 NF............................................................................................................................12
Figure 4 2 NF............................................................................................................................12
Figure 5 3 NF............................................................................................................................13
Figure 6 animal table................................................................................................................14
Figure 7 animal data information view....................................................................................15
Figure 8 Appointment table.....................................................................................................15
Figure 9 appointment data information view..........................................................................15
Figure 10 branch table.............................................................................................................16
Figure 11 branch data information view..................................................................................16
Figure 12 owner table..............................................................................................................16
Figure 13 owner data information view..................................................................................17
Figure 14 staff table.................................................................................................................17
Figure 15 staff data information view......................................................................................17
Figure 16 prescription table.....................................................................................................18
Figure 17 prescription data information view.........................................................................18
Figure 18 bill table....................................................................................................................18
Figure 19 bill data information view........................................................................................19
Figure 20 query output 1.........................................................................................................20
Figure 21 query output 2.........................................................................................................21
Figure 22 query output 3.........................................................................................................22
Figure 23 query output 4.........................................................................................................22
Figure 24query output 5..........................................................................................................23
Figure 25 query output 6.........................................................................................................23
Figure 26 test case 1.................................................................................................................25
Figure 27 test case 2.................................................................................................................25
Figure 28 test case 3.................................................................................................................25
Figure 29 test case 4.................................................................................................................26
Figure 30 test case 5.................................................................................................................26
2

Figure 31 test case 6.................................................................................................................26
Figure 32 staff table.................................................................................................................27
Figure 33 branch table.............................................................................................................27
Figure 34 animal table..............................................................................................................28
Figure 35 owner table..............................................................................................................28
Figure 36 appointment table...................................................................................................28
Figure 37 prescription table.....................................................................................................29
Figure 38 bill table....................................................................................................................29
Figure 39 query 1.....................................................................................................................29
Figure 40 query 2.....................................................................................................................30
Figure 41 query 3.....................................................................................................................30
Figure 42 query 4.....................................................................................................................30
Figure 43 query 5.....................................................................................................................31
Figure 44 query 6.....................................................................................................................31
Figure 45 Relation diagram for VET solution...........................................................................32
Figure 46 animal table design..................................................................................................32
Figure 47 appointment table design........................................................................................33
Figure 48 bill table design........................................................................................................33
Figure 49 staff table design......................................................................................................33
Figure 50 owner table design...................................................................................................34
Figure 51 branch table design..................................................................................................34
Figure 52 prescription table design..........................................................................................34
Figure 53 functional dependency diagram..............................................................................35
List of tables
Table 1 data dictionary table.....................................................................................................7
Table 2 test case table..............................................................................................................18
3

Introduction
In the project report, it is developed and designed for the database management for a
system which is provided by a company ULKOM LTD in Cambridgeshire who has many years
of experience in an electronic database system. This database is designed to meet company
requirements and helps to their staff for ease of system data updation and provide the
reliable and secure database.
The database management system after implementation successfully, send for testing and
validating data information using various test cases and provided all information in this
report with examples.
4

LO1 Use an appropriate design tool to design a relational database system for
a substantial problem
P1 Design a relational database system using appropriate design tools and
techniques.
Roles and specification of VET solution Database System:
An arrangement of VET solution Database is utilized to oversee entire information inside an
organization by putting away tremendous measures of information. The programming
application is utilizing relational database tables. It diminishes information repetition and
presents information in a persuading way that aides in proficient basic leadership. It
enhances the execution of a framework and gives progress worked in examination
functionalities. The VET solution database tables also provide help to maintain data
information by storing in database warehouse to fetch anytime through application level.
Relational Database tool and some techniques:
Relational Database is the most widely recognized kind of DBMS. In this, information is
stored in the form of tables. Application software utilized for arranging information in tables
is MS SQL Server. It is otherwise called Standard Query Language which is known as SQL
(sequel). Relational databases frequently drive the organization basic and web-empowered
applications basic for making progress in an exceptionally aggressive market. It is designed
to convey the basis for building and works with the relational data parts and empowering to
create and utilize relational databases in the environment.
Logical Design for Relational Database:-
A logical data model is needed in beginning to layout a VET arrangement physical database.
What's more, the consistent information show ends up out of a reasonable information
display. Moreover, different kind of information show begins with the preparation of
information display. The logical data model stage formalizes the entities, or attributes, and
their objects. Another essential undertaking of logical data modeling is to guarantee that the
model entities are adjusted by attributes that extraordinarily relate to them. No attribute
5

ought to show up in an element unless it depicts the uniqueness identifier for the entity
which is the primary key.
ER- Diagram
These are entity relationship diagrams which are used for pictorial representation or
understanding of data information shared among different entities. A design function allows
admin to see the relationships between various entities known as ER diagram.
Entity
An object is a part of entity exists in the database. It doesn't have to do anything; it
essentially needs to exist. In database association, an element can be an alone thing,
individual, place, or question. Data can be secured about such substance.
Attribute
An attribute characterizes the data about the entity that should be put away. In the
database that the entity is a worker, attributes could incorporate name, person ID,
wellbeing design enlistment, and work area. An entity will have at least zero attributes, and
every one of those attributes applies just to that entity.
Relationship
A relationship describes the relation between tables or entities in a database and it is
represented by diamond shape and it has many types.
Data Elements
Data elements are the data information of entities such as its attributes in a database which
are specified by its data types.
Data Types:
The data type is used to define what type of information is storing in entities such as
integer, float, character, string or more. Basically, it defines the type of variable which is
used in the database.
Indexing
6

Indexing is used for finding the location of data stored in the database by providing key to
them or some pointers or address.
Primary/Foreign Key:
A primary key is a part of candidate key to defining its property is unique in the database
and foreign key is used on behalf of the primary key in another table to provide a relation
between them.
Table 1 Primary key example
Table 2 foreign key example
Entity relationship diagram for VET Solution database
The entity relationship diagram for Vet database solution is designed with seven tables
which will be created to develop a database for storing information of the program in the
database. These tables are interrelated with making the connection between them. The
below figure is produced by the developer and framework prerequisites (Wu, 2017).
7

Figure 1 ER Diagram
M1 Produce a comprehensive design for a fully functional system
Data Integrity
Data integrity is an essential piece of information security. In its broadest use, "Data
integrity" implies the precision and consistency of data set away in a database, data
stockroom, data store or another form. The term – Data Integrity - can be used to portray
an express, a procedure or a capacity – and is consistently used as a middle person for "data
8

quality". Data with "integrity" is said to have an aggregate or whole structure. Data regards
are organized by an information display and additionally information writes.
Data Validation
When utilizing SQL, validation of data information is the part of a VET solution database that
keeps information steady. The key factors in data integrity are constraints, referential
integrity and the delete and update-alternatives. The fundamental sorts of requirements in
SQL are check, unique, not null, and primary constraints. Check limitations are utilized to
verify that an announcement about the information is valid for all lines in a table. The
unique constraint guarantees that no two lines have similar qualities in their sections. The
not null constraint is put on a segment and expresses that information is required in that
section.
Data Dictionary
A standout amongst the most critical parts of a VET solution database is its data dictionary,
which is a perused just arrangement of tables that gives data about the database. A
dictionary for data contains the definitions of all outline protests in the database (tables,
indexing, groups, clusters, packages, triggers, etc), memory required, the pattern objects,
default esteems for columns, integrity imperative information, name of users, privileges,
and parts for every user has been granted, auditing data.
Table 3 data dictionary table
Table Name Attribute Description Data type Key
Animal Animal id Identity of
animal
integer primary
Animal add Add new animal varchar
Animal name Add name varchar
Animal contact Add contact
details
integer
Animal identity Identity of
animal
integer
Appointment Appointment id Id of
appointment
integer primary
Appointment
date
Date of
appointment
date
Branch id Id of the branch
for appointment
integer foreign
9

Animal id Id of animal for
appointment
integer foreign
Owner id Id of owner for
appointment
integer foreign
Branch Branch id Id of branch integer primary
branch name Name of branch varchar
Branch location Location of
branch
varchar
Branch contact Contact details
of branch
integer
Staff id Identity of staff
for branch
integer foreign
Staff Staff id Identity of staff integer primary
Staff name Name of staffs varchar
Staff add Add new staff varchar
Staff role Role of staff varchar
Staff contact Contact details
of staff
integer
Bill Bill id Id of bill integer primary
Staff id Id of staff for bill integer foreign
Owner id Id of owner for
bill
integer foreign
Animal id Id of animal for
bill
integer foreign
cost Amount of bill varchar
owner Owner id Id of owner integer primary
Owner name Name of owner varchar
Owner contact Contact details
of owner
integer
Owner add Add new owner varchar
Animal id Id of animal for
owner
integer foreign
Prescription Prescription id Id of
prescription
integer primary
Staff id Id of staff for
prescription
integer foreign
Appointment id Identity of
appointment for
prescription
integer foreign
Animal id Identity of
animal for
prescription
integer foreign
Owner id Id of owner for
prescription
integer foreign
Drug name Drug name varchar
10

Data Normalization
Data normalization is used for arranging data in the proper format of relations by following
the properties of 1NF, 2NF and 3NF which are types of normalization to reduce data
redundancy and provides data integrity and it also provides best results in optimized way for
the database.
Now, first gather the data to develop the database which is in unnormalised form:
11

Figure 2 Unormalised Form
After collect the data now, normalised to developed the good database,
First perform the 1 NF that has no dependencies and repeated data:
12

Figure 3 1 NF
2 NF: Normalised the 1 NF into 2 NF to reduce the data redundancy and complexity. 2 NF
should follow conditions such as
Figure 4 2 NF
13

3 NF: 3 NF should be in 2 NF and 1 NF with deleting the anomalies, and with the 3 NF the
users will easily insert the values in the table and delete the values from the table (Loffe,
2015).
Figure 5 3 NF
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 the most basic blueprint to store information and it stores
information in table arrangement utilizing of rows and columns. Relationships are produced
by the forbidden information. It enables us to play out an assortment of activities on the
information like Update, deletion, insertion, and selection. It gives information with
usefulness on the best way to be put away. For setting up the relational database, the user
has utilized MS SQL Server 2014. This database is secure and robust with required data
fields and stored specified information.
The below figures describes the design of tables in which it consists of table name,
attributes, data type and allows its functionalities.
 Animal
Figure 6 animal table
15

Figure 7 animal data information view
 Appointment
Figure 8 Appointment table
Figure 9 appointment data information view
 Branch
16

Figure 10 branch table
Figure 11 branch data information view
 Owner
Figure 12 owner table
17

Figure 13 owner data information view
 Staff
Figure 14 staff table
Figure 15 staff data information view
18

 Prescription
Figure 16 prescription table
Figure 17 prescription data information view
 Bill
Figure 18 bill table
19

Figure 19 bill data information view
M2 Implement a fully functional database system
The execution of database scrutinize above has been intended to accomplish upkeep
objectives as depicted in customer necessities. The particular information writes for each
attribute of entities will make it more secure and keep garbage value information from
putting away into the database. For instance, if a developer enters a character in int data
type field then the information won't be added to the database. Likewise, the primary key
given to every element in a substance set will be utilized to recognize a specific question or
element from the various items and these fields can't be left clear. The security of the
framework will be checked time to time by the utilization of a primary key as no
unauthorized or not valid person will ever get admittance to the database just similar to
intruders.
20

P3 Produce a query language, with queries across multiple tables produced earlier:
The query is accustomed to actualizing capacities on tables like select, update, delete and
insert in the table. The database query language is utilized to separate information from the
database by utilizing a structured query. A query can be controlled by the user to recover
information according to their prerequisite. Queries are composed in SQL language for the
relational database.
Query1 fetch the animal id and animal name with owner id and prescription id from the
three different tables.
Solution:
SELECT dbo.Animal.Animal_ID, dbo.Animal.Animal_Name, dbo.Owner.Owner_ID,
dbo.Prescription.Prescription_ID FROM dbo.Animal INNER JOIN dbo.Owner ON
dbo.Animal.Animal_ID = dbo.Owner.Animal_ID INNER JOIN dbo.Prescription ON
dbo.Animal.Animal_ID = dbo.Prescription.Animal_ID AND dbo.Owner.Owner_ID =
dbo.Prescription.Owner_ID.
Figure 20 query output 1
Query 2 fetch appointment id, branch id, branch name, staff name, staff id, and staff role.
21

Solution:
SELECT dbo.Appointment.Appointment_ID, dbo.Branch.Branch_ID,
dbo.Branch.Branch_Name, dbo.Staff.Staff_Name, dbo.Staff.Staff_ID, dbo.Staff.Staff_role
FROM dbo.Appointment INNER JOIN
dbo.Branch ON dbo.Appointment.BranchID = dbo.Branch.Branch_ID INNER JOIN dbo.Staff
ON dbo.Branch.Staff_ID = dbo.Staff.Staff_ID.
Figure 21 query output 2
Query 3 fetches animal id, name, identity, and id of prescription, appointment, and branch.
Solution:
SELECT dbo.Animal.Animal_ID, dbo.Animal.Animal_Name, dbo.Animal.Animal_Identity,
dbo.Prescription.Prescription_ID, dbo.Appointment.Appointment_ID, dbo.Branch.Branch_ID
FROM dbo.Animal INNER JOIN dbo.Appointment ON dbo.Animal.Animal_ID =
dbo.Appointment.AnimalID INNER JOIN dbo.Branch ON dbo.Appointment.BranchID =
dbo.Branch.Branch_ID INNER JOIN
dbo.Prescription ON dbo.Animal.Animal_ID = dbo.Prescription.Animal_ID AND
dbo.Appointment.Appointment_ID = dbo.Prescription.Appointment_ID
22

Figure 22 query output 3
Query 4 fetches all the information of staff table.
Solution: Select * from Staff;
Figure 23 query output 4
Query 5 fetches minimum costs as smallest price from bill table.
Solution: SELECT MIN (Cost) AS Smallest Price FROM Bill;
23

Figure 24query output 5
Query 6 fetch maximum costs from bill table with the condition of it.
Solution: SELECT MAX (cost) FROM Bill Where Bill_ID = '702'
Figure 25 query output 6
M3 Produce appropriate management information.
In this report, the management of data information takes place as the implementation of
queries like above-defined queries which is important to a developer to access it and make
required changes with various queries that already implemented in a program to separate
an assortment of particular information implementation of various queries. It’ll help for a
developer for implementing queries to make it simple to recover information and
furthermore data administration turns out to be simple.
24

LO3 Test the system against user and system requirements:
P4 Create a test plan to test the database:
A procedure of validation and verification of the put away information and checks the
working of the outline and usage of database executes as wanted is known as testing.
Testing is performed to test different conditions and discover an error in the current
framework. Testing is performed to ensure that the execution is appropriate or not.
Verification is a strategy of surveying the go-between work after effects of an item change
lifecycle to check if we are in the right track of making the last thing. Approval is the method
for evaluating the last thing to check whether the item meets the business needs (MJ, RIVA,
2006).
Table 4 test case table
Sr. No. Test cases Expected Result Actual result
1. Changed branch
contact field value by a
varchar data type.
Data should not be updated
as the data type of contact
is not varchar.
Data is not updated.
2. Int type is not
compatible with a date
for an appointment.
The data should not be
inserted into the database
as the date does not accept
an integer value.
Shows Error in
inserting integer value
in appointment date.
3. Violation of primary key
by adding duplicate
value in the animal
table.
The data should not be
inserted into the database
as a violation of unique
value is not accepted.
Data not accepted and
shows error.
4. Violation of primary key
by adding duplicate
value in branch table.
The data should not be
inserted into the database
as a violation of unique
value is not accepted with
duplicate value.
Data not accepted and
shows error with the
terminate statement.
25

5. Data changed without
updating foreign key
not accepted.
Data will not be accepted
and show the error of
duplication.
Data not accepted and
display error.
6. Deletion of referenced
data shows error and
statement will
terminate.
Error message while
executing the query.
Query didn’t execute.
Figure 26 test case 1
Figure 27 test case 2
26

Figure 28 test case 3
Figure 29 test case 4
Figure 30 test case 5
Figure 31 test case 6
27

M4. Assess the effectiveness of the testing
Different experiments have been utilized to test security and openness for my database. In
these test cases, I have performed different approvals like an value will just acknowledge
the value characterized by its information compose. If the data information composes
entered isn't in an indistinguishable organization from its pre-characterized information
write then mistake message is produced and the framework does not execute the asked for
the query. Various testing of different cases shows different errors of not accepting wrong
data in the statements shows that the SQL queries are developed with robustness which
provides the correct database.
28

LO4 User and Technical Documentation:
P5 Produce technical and user documentation
User Documentation:
Documentation of developer file is the last documentation of a framework which is given to
the user toward the conclusion to get to it. It gives a simple comprehension of information
to the user. The information and its yield utilizing queries will a piece of user's
documentation and give an interface to the user to cooperate with the inward framework.
In this database, I have given some datasheet which also includes queries to remove some
significant information from the database (Jin, 2016).
Figure 32 staff table
Figure 33 branch table
29
Data of staff is
stored successfully
Data of branch is
stored successfully

Figure 34 animal table
Figure 35 owner table
Figure 36 appointment table
30
Data of animal is
stored successfully
Data of owner is stored
successfully
Branch id is referenced
here

Figure 37 prescription table
Figure 38 bill table
Query reports:
Figure 39 query 1
31
Drung name is
inserted here
Cost entered and
stored here
Query statement with
desired output

Figure 40 query 2
Figure 41 query 3
Figure 42 query 4
32
Query statement
with desired output
Query statement with
desired output
Query statement with
desired output

Figure 43 query 5
Figure 44 query 6
Technical Documentation:
In specialized documentation, all the specialized data related to VET Solutions Company is
reported legitimately to assist the administration of the organization. It incorporates the
model or configuration perspectives of the table and the relationship among the tables are
recorded. Additionally, the functional dependency is incorporated into the specialized
documentation. The technical document describes the implementation of data tables with
specified data type using null values (Papenbrock, 2015).
33
Query statement with
desired output
Query statement with
desired output

Figure 45 Relation diagram for VET solution
Figure 46 animal table design
34

Figure 47 appointment table design
Figure 48 bill table design
Figure 49 staff table design
35

Figure 50 owner table design
Figure 51 branch table design
Figure 52 prescription table design
36

M5: Produce fully functional system
Functional dependency
A functional dependency (FD) is an association between two characteristics, usually
between the Primary Key and other non-key qualities inside a table. For any connection,
characteristic Z is essentially dependent on property A (the Primary Key), if for each
generous instance of A, that estimation of A strikingly chooses the estimation of Z (Yang,
1986).
Figure 53 functional dependency diagram
37

Conclusion
This report concludes that all the data information is tested and validated after
implementation of this system. This system is designed and developed for ULKOM LTD
Company in Cambridgeshire who has experienced in electronic databases. Using this
database management system, the provided solution will store the validated data in
specified fields related to them and can utilize in various ways. This report also provides
data dictionaries to understand the entities and its attributes and also having ER diagram of
this database system with functionality dependent diagram.
38

References
 Database Management. 2018. Data Validation. [online] Available at:
http://databasemanagement.wikia.com/wiki/Data_Validation [Accessed 16 May
2018].
 Docs.oracle.com. 2018. The Data Dictionary. [online] Available at:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/datadict.htm [Accessed
16 May 2018].
 Ioffe, S. and Szegedy, C., 2015. Batch normalization: Accelerating deep network
training by reducing internal covariate shift. arXiv preprint arXiv:1502.03167.
 Jin, C., Lall, A., Xu, J.J., Zhang, Z. and Zhou, A., 2016. Distributed error estimation of
functional dependency. Information Sciences, 345, pp.156-176.
 Opentextbc.ca. 2018. Chapter 11 Functional Dependencies – Database Design – 2nd
Edition. [online] Available at: https://opentextbc.ca/dbdesign01/chapter/chapter-11-
functional-dependencies/ [Accessed 16 May 2018].
 Papenbrock, T., Ehrlich, J., Marten, J., Neubert, T., Rudolph, J.P., Schönberg, M.,
Zwiener, J. and Naumann, F., 2015. Functional dependency discovery: An
experimental evaluation of seven algorithms. Proceedings of the VLDB
Endowment, 8(10), pp.1082-1093.
 Pipitone, A. and Pirrone, R., 2014, June. A hidden Markov model for an automatic
generation of er diagrams from owl ontology. In Semantic Computing (ICSC), 2014
IEEE International Conference on (pp. 135-142).
 Teorey, T.J., Yang, D. and Fry, J.P., 1986. A logical design methodology for relational
databases using the extended entity-relationship model. ACM Computing Surveys
(CSUR), 18(2), pp.197-222.
 Tuya, J., Suarez-Cabal, M.J. and De La Riva, C., 2006, November. SQLMutation: A tool
to generate mutants of SQL database queries. In Mutation Analysis, 2006. Second
Workshop on (pp. 1-1).
 Whatisdbms.com. 2016. E-R Diagrams in DBMS: Components, Symbols, and
Notations. [online] Available at: http://whatisdbms.com/e-r-diagrams-in-dbms-
components-symbols-and-notations/ [Accessed 16 May 2018].
39

 Wu, P.Y., Igoche, D.A. and Drauss, P.J., 2017. Media versus Message: Choosing The
ER Diagram To Teach ER Modeling. In Proceedings of the EDSIG Conference ISSN (Vol.
2473, p. 3857).
40