Database Design Assessment for VET Solution

Database Design Assessment

Table of Contents
Introduction................................................................................................................................2
LO1. Use an appropriate design tool to design a relational database system for a substantial
problem......................................................................................................................................3
P1. Design a relational database system using appropriate design tools and techniques.......3
M1. Produce a comprehensive design for a fully functional system......................................5
LO2. Develop a fully functional relational database system, based on an existing design.......9
P2. Develop the database system............................................................................................9
Relational Database Management System:.........................................................................9
M2. Implement a fully functional database system..............................................................12
P3. Implement a query language into the relational database system..................................14
M3. produce appropriate management information.............................................................18
LO3. Test the system against user and system requirement....................................................19
P4. Test the system against user and system requirements..................................................19
M4. Assess the effectiveness of the testing, including an explanation of the choice of test
data used...............................................................................................................................23
LO4. Produce technical and user documentation.....................................................................26
P5. Produce technical and user documentation....................................................................26
M5. Produce technical and user documentation...................................................................31
Conclusion................................................................................................................................32
References................................................................................................................................33
List of figure
Figure 1 E-R Diagram For the VET Solution............................................................................7
Figure 2 Validation Of table Animal.........................................................................................7
Figure 3 Structure of the Database...........................................................................................11
Figure 4 Table View Of the table Animal................................................................................12
1

Figure 5 Table View of the table Appointment.......................................................................12
Figure 6 Table View of the table Branch.................................................................................12
Figure 7 Table View of the table Doctor.................................................................................13
Figure 8 Table View of the table Drugs...................................................................................13
Figure 9Table View of the table Prescription..........................................................................13
Figure 10 Table View of the table Owner................................................................................14
Figure 11 Table View of the table Staff...................................................................................14
Figure 12 table View of Query 1..............................................................................................16
Figure 13 table View of Query 2..............................................................................................17
Figure 14 table View of Query 3..............................................................................................17
Figure 15 Table View of Query 4............................................................................................18
Figure 16 table View of Query 5..............................................................................................18
Figure 17 table View of Query 6..............................................................................................19
Figure 18 table View of Query 7..............................................................................................19
Figure 19 table View of Query 8..............................................................................................20
Figure 20 Test case 1...............................................................................................................21
Figure 21 Test Case 2..............................................................................................................22
Figure 22 Test case 3................................................................................................................22
Figure 23 Test case 4................................................................................................................22
Figure 24 Test case 5................................................................................................................23
Figure 25 Test case 6................................................................................................................23
Figure 26 Test case 7................................................................................................................24
Figure 27 Data Validation of Animal.......................................................................................25
Figure 28 Appointment...........................................................................................................25
Figure 29 Data Validation of Branch.......................................................................................25
Figure 30 Data Validation of Doctor.......................................................................................26
Figure 31Data Validation of Drug...........................................................................................26
Figure 32 Data Validation of Owner........................................................................................26
Figure 33 Data Validation of Prescription..............................................................................27
Figure 34 Data Validation of Staff...........................................................................................27
Figure 35 Query to create table Animal...................................................................................28
Figure 36 Query to create table Appointment..........................................................................28
Figure 37Query to create table Branch....................................................................................28
Figure 38 Query to create table Doctor....................................................................................29
2

Figure 39 Query to create table Drug.......................................................................................29
Figure 40 Query to create table Owner....................................................................................29
Figure 41 Query to create table Prescription............................................................................30
Figure 42 Query to create table Staff.......................................................................................30
Figure 43 Inset query for Animal.............................................................................................30
Figure 44 Inset query for Appointment....................................................................................30
Figure 45 Inset query for Branch.............................................................................................31
Figure 46 Inset query for Doctor..............................................................................................31
Figure 47 Inset query for Drug.................................................................................................31
Figure 48 Inset query for Owner..............................................................................................32
Figure 49Inset query for Prescription......................................................................................32
Figure 50 Inset query for Staff.................................................................................................32
Figure 51 Inset query for data validation................................................................................32
3

Introduction
This report presents the relational database system with the design the ER-diagram,
normalization, by using the appropriate tool and technology. In this report, the database
design, development and testing is presented. The VET solution is the company that provides
the services for the pet animals by registering them and stores the data or information in the
database. This designed database is fully functional, with designed the tables to store the
database and query to find the specific information from the database. This report is divided
into the four parts where the first part is VET solution database designing, the second part is
present the database development with implement the tables and queries, third part presents
the testing and fourth part shows the user and technical documentation.
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
To design and develop the relational database management system for VET Solution’s
Company, firstly a blueprint is made on an appropriate designing tool. Here, we make an E-R
diagram which may brief us with the overall architecture of the relational database system.
Database management system has become an urgent requirement in VET Solution’s
Company as the company is growing exponentially. The patients and staff are increasing, so
to keep their records this database system is a proper solution. Also, as VET Solution
Company is spreading in other countries too, the database system will help it keep a
persistent record.
Nowadays, the role of database management system has become crucial in this industry. As
the data is changing and evolving at a very fast rate, it has become important to record,
update and track this data to the patient’s satisfaction. Gathering data is helping healthcare
industry to achieve their goals in a systematic and strategic manner (IJAERD, 2017).
These database management systems are dependent on programming languages called
structured query languages. This language is used to create, update and store the data in the
database. SQL and MySQL servers allow outside program to query and get particular
information. The most important function of database management system is that they allow
different data tables to relate to one another (Allen, 2002).
User Requirements:
 A centralized database should be able to record all the information of its patients,
staff, and doctors.
 The database in making must be able to record all the information of the patient like
name, owner, type of animal etc..
 The database should also have all the information of the doctor and the owner of the
patient (animal).
 It should contain the treatment and medication given to the patient.
5

 User-friendly statistical programs should be there.
 Software and analysis program should efficiently query the databases.
 Onsite IT technical support should be provided.
 There is a need for database warehouse (Gies, 2018).
System Requirements:
 A primary live-link installation.
 A relational database server like SQL server.
 An Http Server like Apache.
 A web browser which may work as a client.
 The database should be fast and efficient.
 It should be designed in a way that the whole VET Solution can rely on it for basic
information (Gregorieff, 2010).
E-R Diagram (Entity-Relationship Diagram)
An E-R diagram is a model that shows the relationship between different entities. It illustrates
the logical structure of the database. It tells how the information system is related.
The E-R diagram consists of 5 main components:
1. Entities: They are represented by a rectangle. It is an object about which we want to
store information.
2. Actions: They are represented by a diamond. It shows the relationship two or more
entities share (Fahad, 2008).
3. Attributes: Attributes are represented by ovals. The attribute describes additional
information of an entity.
4. Lines: Solid connecting lines which connects one object to another to create a
relationship.
5. Cardinality: It shows how many instances of one activity relate to how many
instances of another activity (Chen, 2007).
6

Figure 1 E-R Diagram For the VET Solution
M1. Produce a comprehensive design for a fully functional
system
Data Validation
Data Validation is an automatic computer check that ensures the data entered in the database
is sensible. In SQL Server, the constraint check identifies acceptable column values for data
in a row within a SQL Server. It ensures that the data the program is operating on is clean and
correct (Haverkamp, 1995).
7