Database Design and Development for VET SOLUTION

Database Design and Development
1

Table of Contents
Introduction...........................................................................................................................................5
LO1 Use an appropriate design tool to design a relational database system for a substantial problem
...............................................................................................................................................................6
P1 Design a relational database system using appropriate design tools and techniques..................6
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:......15
P2 Develop and implement a fully functional database system using MS SQL Server:....................15
M2 Implement a fully functional database system..........................................................................20
P3 Produce a query language, with queries across multiple tables produced earlier:.....................21
M3 Produce appropriate management information.......................................................................26
LO3 Test the system against user and system requirements:..............................................................27
P4 Create a test plan to test the database:......................................................................................27
M4. Assess the effectiveness of the testing.....................................................................................31
LO4 User and Technical Documentation:............................................................................................32
P5 Produce technical and user documentation...............................................................................32
M5: Produce fully functional system...............................................................................................41
Conclusion...........................................................................................................................................42
References...........................................................................................................................................43
List of figures
Figure 1 Primary Key Example...............................................................................................................7
Figure 2 Foreign Key Example................................................................................................................7
Figure 3 ER diagram...............................................................................................................................8
Figure 4 Validation Test 1......................................................................................................................9
Figure 5 Validation Test 2......................................................................................................................9
Figure 6 appointment table.................................................................................................................15
Figure 7 appointment datasheet view................................................................................................15
2

Figure 8 Clinic table.............................................................................................................................16
Figure 9 Clinic view..............................................................................................................................16
Figure 10 owner table..........................................................................................................................16
Figure 11 owner view..........................................................................................................................16
Figure 12 Invoice table........................................................................................................................17
Figure 13 Invoice datasheet view........................................................................................................17
Figure 14 Pet table...............................................................................................................................17
Figure 15 Pet datasheet view..............................................................................................................17
Figure 16 Pharmacy table....................................................................................................................18
Figure 17 Pharmacy datasheet view....................................................................................................18
Figure 18 Physician table.....................................................................................................................18
Figure 19 Physician datasheet view.....................................................................................................18
Figure 20 Staff table.............................................................................................................................19
Figure 21 Staff datasheet view............................................................................................................19
Figure 22 Treatment table...................................................................................................................19
Figure 23 Treatment datasheet view...................................................................................................19
Figure 24 Query1 output.....................................................................................................................21
Figure 25 query 2 outputs...................................................................................................................22
Figure 26 query 3 outputs...................................................................................................................23
Figure 27 query4 output......................................................................................................................24
Figure 28 query5 output......................................................................................................................25
Figure 29 Test Case 1...........................................................................................................................28
Figure 30 Test case 2...........................................................................................................................28
Figure 31 Test case 3...........................................................................................................................29
Figure 32 Test case 4...........................................................................................................................29
Figure 33 Test Case 5...........................................................................................................................30
Figure 34 Clinic table...........................................................................................................................32
Figure 35Appointment Table...............................................................................................................32
Figure 36 Invoice Table........................................................................................................................33
Figure 37 Owner Table........................................................................................................................33
Figure 38 Pet table...............................................................................................................................33
Figure 39 Pharmacy table....................................................................................................................33
Figure 40 Physician table.....................................................................................................................34
Figure 41 Staff Table............................................................................................................................34
3

Figure 42 Treatment Table..................................................................................................................34
Figure 43 query 1.................................................................................................................................35
Figure 44 query2..................................................................................................................................35
Figure 45 Query 3................................................................................................................................36
Figure 46 Query 4................................................................................................................................36
Figure 47 Query 5................................................................................................................................37
Figure 48 Relationship among tables...................................................................................................38
Figure 49 Appointment Design............................................................................................................38
Figure 50 Clinic Design.........................................................................................................................39
Figure 51 Invoice Design......................................................................................................................39
Figure 52 Owner Design......................................................................................................................39
Figure 53 Pet Design............................................................................................................................39
Figure 54 Pharmacy Design.................................................................................................................40
Figure 55 Physician Design..................................................................................................................40
Figure 56 Staff Design..........................................................................................................................40
Figure 57 Treatment Design................................................................................................................40
Figure 58 Functional dependency flowchart.......................................................................................41
List of Tables
Table 1 Data dictionary........................................................................................................................10
Table 2 First normalization..................................................................................................................12
Table 3 Second Normalization.............................................................................................................13
Table 4 Third Normalization................................................................................................................13
Table 5 Test cases................................................................................................................................27
4

Introduction
In this project, I will be working on designing and development of a database management
system for a veteran company VET SOLUTION in Cambridgeshire to create an effective
communication between all the branches of the company can be made. The design of the
database system has been made as per company’s requirements which reduce their time
consumption in manual updation and it is reliable in case of a fault.
The database management system, after its designing and development, has been properly
implemented and tested and has been discussed in this report.
5

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
Specifications of Database System and its Role: - System of Database is used to manage
whole data within a company by storing huge amounts of data in software application using
relational tables. It decreases data redundancy and presents data in a convincing manner that
helps in efficient decision making. It improves the performance of a system and provides
advance built-in analysis functionalities.
Tools and Techniques for Relational Database
Relational Database is the most common type of DBMS. In this, data is stored in tables. A
tool used for formatting data in tables is MS SQL Server. It is also known as Standard Query
Language.
Logical Design for Relational Database:-
To make a relational database, it should first be logically designed. A database consists of an
entity, its attribute, and relationship among entities. Each entity should have a primary key.
ER- Diagram
Pictorial representation of relationships shared among various entities.
Entity- An entity is an object of the database which stores a primary key. It is represented by
a rectangle.
Attribute: - Attributes are the values that define an entity. It is represented by Oval.
Relationship: - Relationship shows how two entities share information in a database. It is
represented by Diamond.
Data Elements: - Data Elements contains the attribute of an entity. It can be of any data type
and its length can be specified while developing a database.
Data Types: - Data type defines what type of variable a data can store like character, integer,
or Boolean value.
Indexes: - Indices are the location of a row of data. In the table, it acts as a pointer.
Primary/Foreign Key: - A unique column in the table is known as a primary key that is used
to separate a row in the table from any other row and when the primary key of one table is
applied in another table for reference then it becomes Foreign Key.
6

Figure 1 Primary Key Example
In the table list, Cust_Id is the labeled as Primary Key of the table.
Figure 2 Foreign Key Example
In the table, Cust_Id is labeled as Foreign key because it is used as a reference to table
present in fig 1(Singh, 2017).
Entity relationship diagram for VET Solution database
This diagram presents the nine tables that will be created during implementation time to store
the information in the database. These tables are interrelated with creating the relationship
between them. This diagram is generated according to the user and system requirements.
7

Figure 3 ER diagram
M1 Produce a comprehensive design for a fully functional system
Data Integrity: - Data Integrity is a term to define how consistent and accurate data is stored
in the data warehouse. Data integrity includes validation of data, providing quality assurance
by testing data from every perspective and coding and processing of data.
Data Validation: - Data validation is used to validate data by performing various tests. Data
is checked for its type and index and put to various conditions to check if it satisfies its type
and index.
Validation Test 1- Data type is set as money for the amount.
Validation Test 2 – physician No cannot be null.
8