VET Solution Database System Design and Implementation

Database Assignment
1

List of Contents
Introduction......................................................................................................................................6
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem............................................................................................................................................7
P1 Design a relational database system using appropriate design tools and techniques.................7
M1 Produce a comprehensive design for a fully functional system..............................................10
LO2 Develop a fully functional relational database system, based on an existing system
design:............................................................................................................................................18
P2 Develop and implement a fully functional database system using MS SQL Server:...............18
M2: Implement a full functional database system.........................................................................25
P3 Produce a query language, with queries across multiple tables produced earlier:...................26
M3 Produce appropriate management information.......................................................................29
LO3 Test the system against user and system requirements:........................................................30
P4 Create a test plan to test the database:......................................................................................30
M4. Assess the effectiveness of the testing...................................................................................34
LO4 User and Technical Documentation:.....................................................................................35
P5 Produce technical and user documentation..............................................................................35
M5: Produce fully functional system.............................................................................................39
Conclusion.....................................................................................................................................41
References......................................................................................................................................42
2

List of figures
Figure 1 ER diagram......................................................................................................................10
Figure 2 Treatment details table....................................................................................................16
Figure 3 staff table.........................................................................................................................17
Figure 4 presecription table...........................................................................................................17
Figure 5 animal table.....................................................................................................................17
Figure 6 owner table......................................................................................................................18
Figure 7 invoice detail table..........................................................................................................18
Figure 8 drugs table.......................................................................................................................18
Figure 9 doctor table......................................................................................................................18
Figure 10 consultation table...........................................................................................................19
Figure 11 clinic table.....................................................................................................................19
Figure 12 appointment table..........................................................................................................19
Figure 13 design data table of treatment details............................................................................20
Figure 14 design data view of staff table.......................................................................................20
Figure 15 design data view of prescription table...........................................................................20
Figure 16 design data view of pet animal table.............................................................................21
Figure 17 design data view of owner table....................................................................................21
Figure 18 design data view of invoice table..................................................................................21
Figure 19 v drug detail table..........................................................................................................22
3

Figure 20 design data view of doctor table....................................................................................22
Figure 21 v consultation table........................................................................................................22
Figure 22 design data view of clinic table.....................................................................................23
Figure 23 design data view of appointment table..........................................................................23
Figure 24 Query output 1...............................................................................................................24
Figure 25 Query output 2...............................................................................................................25
Figure 26 Query output 3...............................................................................................................25
Figure 27 Query output 4..............................................................................................................26
Figure 28 Query output 5..............................................................................................................26
Figure 29 Query output 6...............................................................................................................27
Figure 30 Query output 7...............................................................................................................28
Figure 31 Query output 8...............................................................................................................28
Figure 32Test case 1......................................................................................................................30
Figure 33 Test case 2.....................................................................................................................31
Figure 34 test case 3......................................................................................................................31
Figure 35 Test case 4.....................................................................................................................32
Figure 36 Animal table..................................................................................................................33
Figure 37 Owner table...................................................................................................................33
Figure 38 invoice detail table........................................................................................................33
Figure 39 doctor table....................................................................................................................34
Figure 40 Query report 1...............................................................................................................34
4

Figure 41 Query report 2...............................................................................................................34
Figure 42 VET solution relational diagram...................................................................................36
Figure 43 Invoice detail table design.............................................................................................37
Figure 44 Owner table design........................................................................................................37
Figure 45 Pet animal table design..................................................................................................37
Figure 46 Prescription table design...............................................................................................38
Figure 47 Treatment detail table design........................................................................................38
List of tables
Table 1 Example Of Primary Key...................................................................................................8
Table 2 Example of foreign key......................................................................................................8
Table 3 Data dictionary table.........................................................................................................11
Table 4 Un-normalized table.........................................................................................................13
Table 5 1NF table..........................................................................................................................13
Table 6 2NF table..........................................................................................................................14
Table 7 3NF table..........................................................................................................................15
Table 8Test case table....................................................................................................................29
5

Introduction
In this project assignment, the report is designed and developed for the management of
database system that can be provided by the company UKKOM LTD at Cambridge shire that
has experience of many years in the electronic database. Tshis database can be built to help
staff for updating of data and provide security and reliability to the database. To meet the
requirement of the company it can be built.
The database administration framework after execution effectively, send for testing and
approving information data utilizing different experiments and furnished all data in this report
with illustrations.
6

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 particular of VET arrangement of Database System:
A course of action of VET arrangement Database is used to administer whole data inside an
association by securing huge measures of data. Programming application is using the tables
of the relational database. It decreases data redundancy and presents data in an inducing way
that assistants incapable essential administration. It improves the execution of a structure and
gives advance worked in examination functionalities. The VET arrangement database tables
likewise give assistance to keep up information data by putting away in database stockroom
to get whenever through application level (Deque, 2017).
Tools & techniques of a relational database are:
Relational Database: It is the most generally perceived sort of DBMS. In this, data is put
away as tables. Application programming used for information arranging in data in tables is
MS SQL Server. It is generally called Standard Query Language which called SQL (spin-
off). Relational databases much of the time drive the association between fundamental and
web-enabled applications essential for gaining ground in an incredibly forceful market. It is
intended to pass on the reason for building and works with the social information parts and
enabling to make and use social databases in nature (Done, 2013).
Relational Database logical design:-
Logical data model information display is required in starting to design a VET course of
action physical database. Also, the steady data demonstrate winds up out of a sensible data
show. In addition, distinctive sort of data indicate starts with the planning of data show. The
coherent information display arranges formalizes the entities, or attributes and the objects.
Another basic endeavor of logical data modeling information demonstrating is to ensure that
model entity are balanced by ascribes that exceptionally identify with them. No attribute
7

should appear in an element unless it portrays the uniqueness identifier for the substance
which is the essential key.
ER-Diagram
These are substance relationship outlines which are utilized for pictorial portrayal or
comprehension of information data shared among various elements. An outline work permits
the administrator to see the connections between different substances known as ER chart.
Element (Entity):
An entity is a piece of substance exists in the database. It doesn't need to do anything; it
basically needs to exist. In database affiliation, a component can be an alone thing,
individual, place, or question. Information can be secured about such substance.
Attribute (Quality):
An attribute portrays the information about the element that ought to be secured. In the
database that the element is a specialist, properties could fuse name, individual ID, prosperity
plan enrollment, and work zone. An entity will have no less than zero attributes, and each one
of those ascribes applies just to that entity (ComputerScience, 2018).
Relationship:
A relationship depicts the connection between entities or tables in a database and it is spoken
to by precious diamond shape and it has numerous types.
Data Elements
It is the type of elements that contain the information of data like database attributes that can
be specified by their data types.
Data Types:
The information compose is utilized to characterize what sort of data is putting in which
database, for example, whole number, coast, character, string or more. Fundamentally, it
characterizes the sort of factor which is utilized as a part of the database.
8