Relational Database System Design and Implementation for VET Solutions

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.......10
P2 Develop and implement a fully functional database system using MS SQL Server.....................10
M2 Implement a fully functional database system..........................................................................12
P3 Produce a query language, with queries across multiple tables produced earlier......................12
M3 Produce appropriate management information.......................................................................15
LO3 Test the system against user and system requirements...............................................................16
P4 Create a test plan to test the database:......................................................................................16
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...............................................................................................28
Conclusion...........................................................................................................................................29
References...........................................................................................................................................30
List of figures
Figure 1 example of a key constraint.....................................................................................................5
Figure 2 ER diagram for VET solution.....................................................................................................6
Figure 3 table of animal.......................................................................................................................10
Figure 4 table of appointment.............................................................................................................10
Figure 5 table of the holder.................................................................................................................10
Figure 6 table of medication................................................................................................................11
Figure 7 table of receipt......................................................................................................................11
Figure 8 table of branch.......................................................................................................................11
Figure 9 table of staff...........................................................................................................................11
Figure 10 query 1 output.....................................................................................................................12
Figure 11 output for query 2...............................................................................................................12
Figure 12 output of query 3.................................................................................................................13
Figure 13 output for query 4...............................................................................................................13
Figure 14 output of query 5.................................................................................................................14
Figure 15 output for query 6...............................................................................................................14

Figure 16 output of query 7.................................................................................................................14
Figure 18 test case 1............................................................................................................................17
Figure 19 test case 2............................................................................................................................18
Figure 20 test case 3............................................................................................................................18
Figure 21 test case 4............................................................................................................................19
Figure 22 test case 5............................................................................................................................19
Figure 23 test case 6............................................................................................................................20
Figure 24 test case 7............................................................................................................................20
Figure 25 test case 8............................................................................................................................21
Figure 26 animal table.........................................................................................................................22
Figure 27 appointment table...............................................................................................................22
Figure 28 holder table.........................................................................................................................22
Figure 30 medication table..................................................................................................................23
Figure 29 receipt table.........................................................................................................................23
Figure 31 branch table.........................................................................................................................23
Figure 32 staff table.............................................................................................................................23
Figure 33 query 1.................................................................................................................................24
Figure 34 query 2.................................................................................................................................24
Figure 35 query 3.................................................................................................................................24
Figure 36 query 4.................................................................................................................................24
Figure 37 query 5.................................................................................................................................24
Figure 38 query 6.................................................................................................................................25
Figure 39 query 7.................................................................................................................................25
Figure 41 ERD for VET Solution............................................................................................................26
Figure 42 design of the animal table...................................................................................................26
Figure 43 design of appointment table................................................................................................26
Figure 44 design of holder table..........................................................................................................27
Figure 45 design of medication table..................................................................................................27
Figure 46 design of receipt table.........................................................................................................27
Figure 47 design of the branch 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 test case table.........................................................................................................................16

Introduction
This report is prepared for VET solutions in which a system is required to maintaining the data
information properly and it can be accomplished by the database system. So this report determines
the point of the database which are used while implementing and developing a system. This report
provides knowledge for normalization, data dictionaries and various concepts of the database which
is helpful for the user to understand and analyze the working of the system. The testing is of queries
also provide with the report to ensure that the system is developed error-free and provide all
functionalities according to the requirements of the client. At last, the report will also explain the
functional dependency which helps to recognize the relations among various entities and determine
the key constraints.

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:
VET arrangement is generally used in a database system. & moreover, VET arrangement is mainly
used for entire data in an institute or association just by providing secure measures of all the
program data. This relational program database is basically used by the applications & advantages of
programming, in which data redundancy is decreased in a kind of way, which assists for fundamental
authority. This normally improves execution part for the structure & then it gives advancements in
functions of the program. VET arrangement for the database program tables assists for keeping
information & details for keeping it away in storage for getting it, whenever it is requiring to achieve
application level.
Relational Database tool and some techniques:
The most commonly used database is a relational database, in which it is used to perceive database
management system. In a relational database, all of the records are maintained in a table form. MS
SQL server is mainly utilized for finding the program data in a tabular form for application
programming. It is normally known as Sequel or ‘Standard Query language”. It is called as SQL.
Sometimes this relational database improves the association & web-enabled web applications just to
gain ground in porch market. It is mandatory to cross all the reasons for building & works for the
social details & information & to make it engaged, we use relational database across the
surroundings & on the technical ground.
Logical Design for Relational Database:
For designing a VET course, it requires data model, which should be logical. It is mainly used to
design a physical database. In this a predictable data basically indicates the end of the sensible
details or information. Data demonstrate generally starts with a rough plan for a data show. All the
entities & objects/methods are usually required just to make a database, which should be logical.
Another motto of logical database is to make assure, all the elements of model should be balanced
by the credits. These credits are normally used to identify all the key elements as primary key, which
defines all the properties of uniqueness.
ER- Diagram
ER diagram represents entity relationship diagrams which consist of entities, attributes along with
key constraints to recognize the value and useful for designing of the database in a proper manner.
The diagram of ERD describes below in this report.
Entity
An entity is a class name or table name which consist of various attributes along with key constraint.
Basically, the entity is the part of the database to recognize the table name.

Attribute
An attribute is a property of entities which help to understand the data values which is entered in
the database and it also contains key constraints to provide unique features while entering the data
information in a database system.
Relationship
Relationships are used to providing relation among various entity tables and they are 4 types as one
to one, one to many, many to one and many to many.
Data Elements
Data elements are useful for validation g the entered values in the database system as the user
enters the value to store and system will check for validating and verifying as valid values.
Data Types
Data types are the function which is predefined in any programming used for the link with the
variable to accept values if it matches the predefined format of the variable. For example int, char,
varchar, etc.
Indexing
Indexing is useful for recognizing the address of the variable and its values and enhances the
property of searching value in the database very quickly.
Primary/Foreign Key:
The primary key is a part of key constraint in which it provides unique values which must not be null
values and should be unique. And the foreign key is used for the linking table’s through primary key
and fetching data through the foreign key. Its data cannot be modified until the table is of referential
integrity (Burroughs, Gansemer, Lee, Voldal, Rogers & Zaborowski, 1999).
Figure 1 example of a key constraint
Entity relationship diagram for VET Solution database
ER diagram is used for making relations between various entries and provide key constraints to their
attributes which help to developer database very easily as it understands the entity relationship
diagram. This diagram determines all the entities attributes align with additional features like a key
constraint, weak entity or relationships among the various entity (Briand, Habrias, Hue & Simon,
1985).

Figure 2 ER diagram for VET solution
M1 Produce a comprehensive design for a fully functional
system
Data Integrity
Data integrity is remained for robustness of data and prevent unauthorized users to access or
update the stores data. So developers design a database with such properties to attain security of
database and provide a fully functional system which stores the information accurately and provide
access to authorized users for giving any details to customers.
Data Validation