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

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