Database Assignment: Designing and Implementing a Relational Database for VET Solution

Database Assignment

Table of Contents
List of Figures........................................................................................................................... 2
List of Tables.............................................................................................................................3
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
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
P3 Produce a query language, with queries across multiple tables produced earlier:.......18
LO3 Test the system against user and system requirements:................................................23
P4 Create a test plan to test the database:........................................................................23
LO4 User and Technical Documentation:...............................................................................27
P5 Produce technical and user documentation..................................................................27
Conclusion..............................................................................................................................37
References..............................................................................................................................38
1

List of Figures
Figure 1 Example of Primary Key..............................................................................................6
Figure 2 Example of Foreign Key..............................................................................................7
Figure 3 ERD Diagram...............................................................................................................7
Figure 4 Design View of Prescription Table............................................................................14
Figure 5 Design View of Owner Table.....................................................................................15
Figure 6 Design View of Drug table........................................................................................15
Figure 7 Design View of Branch Table....................................................................................16
Figure 8 Design View of Appointment Table..........................................................................16
Figure 9 Design View of Animal Table....................................................................................17
Figure 10 Design View of Staff Table......................................................................................17
Figure 11 Output of Query 1..................................................................................................18
Figure 12 Output of Query2...................................................................................................19
Figure 13 Output of Query3...................................................................................................20
Figure 14 Output of Query4...................................................................................................21
Figure 15 Output of Query5...................................................................................................22
Figure 16 Output of Query6...................................................................................................22
Figure 17 Test Case Plan 1......................................................................................................24
Figure 18 Test Case Plan 2......................................................................................................25
Figure 19 Test Case Plan 3......................................................................................................25
Figure 20 Test Case Plan 4......................................................................................................25
Figure 21 Test Case Plan 5......................................................................................................26
Figure 22 Test Case Plan 6......................................................................................................26
Figure 23 Test Case Plan 7......................................................................................................26
Figure 24 Animal Table...........................................................................................................27
Figure 25 Branch Table...........................................................................................................27
Figure 26 Appointment Table.................................................................................................28
Figure 27 Drug Table.............................................................................................................. 28
Figure 28 Owner Table........................................................................................................... 28
Figure 29 Prescription Table...................................................................................................29
Figure 30 Staff Table...............................................................................................................29
2

Figure 31 Query 1 output.......................................................................................................29
Figure 32 Query 2 Outputs.....................................................................................................30
Figure 33 Query 3 Output.......................................................................................................30
Figure 34 Query 4 Outputs....................................................................................................31
Figure 35 Query 5 Outputs....................................................................................................31
Figure 36 Query 6 Outputs....................................................................................................32
Figure 37 Relationship Diagram between the tables..............................................................33
Figure 38 Query to create animal table..................................................................................33
Figure 39 Query for creating appointment table....................................................................34
Figure 40 Query for creating branch table.............................................................................34
Figure 41 Query for creating drug table.................................................................................34
Figure 42 Query for creating owner table..............................................................................34
Figure 43 Query for creating prescription table.....................................................................34
Figure 44 Query for creating Staff table.................................................................................35
Figure 45 Insert query for animal...........................................................................................35
Figure 46 Insert query for appointment.................................................................................35
Figure 47 Insert Query for branch..........................................................................................35
Figure 48 Insert Query for Drug..............................................................................................35
Figure 49 Insert query for the owner.....................................................................................35
Figure 50 Insert query for prescription...................................................................................36
Figure 51 Insert query for staff...............................................................................................36
List of Tables
Table 1 Data Dictionary Table........................................................................................................9
Table 2 First Normal Form Table..................................................................................................11
Table 3 Second Normal Form Table.............................................................................................12
Table 4 Third Normal Form Table................................................................................................13
Table 5 Test Cases Tables.............................................................................................................26
3

Introduction
In this assignment, a database will be created for the VET solution veterinary company. For
the relational database, the data modeling will be performed which will include ER diagram
and normalization of the database using appropriate tool and techniques. The database will
be created and tested against different test cases. The organization VET solution is
responsible to provide services for pet animals by storing the details in the database. The
database will include functionalities, and the query will be executed on the database to
retrieve information from the database. There are four sections in this document in which
first part deals with database designing, the second section includes the development of the
database, the third section includes testing of database and fourth section describes user
and the 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
Specification and Role of Relational Database System:
As it is known that a database is storage area to store the vast data and to manage it in the
forms of the record in the relational tables using some specific application program. By the
help of this redundancy of data is reduced and also manage the data in such a systematic
manner which is relevant to quick decision making. In addition to this, the performance of
the system is improved and provides the advanced functionalities to built-in and for analysis
(Paredaens, 2012).
Design Tools and Techniques for the given Relational Database:
One of the most common databases is a relational database in which the data and records
are stored in the tables. To operate in the relational database, the tool required for the
formatting records and data in the form of tables is the MS SQL Server. This is also one of
the standard languages for queries.
Logical Design:
For creating any database, the logic of the database must be designed for the very first time.
The database consists of a large amount of data and it has some fields like attributes,
entities, and also the relationship between entities. Every entity must have a primary key for
the unique identification.
ER-Diagram:
It is the conceptual and a pictorial diagram which describes the relationship between all the
entities in the database (Bagui, 2011). It has basically three parts:
 Entity: It is given in a rectangular box and it is defined as the object on which some
relation is defined and has a primary key for unique identification of objects.
 Attributes: Groups of attributes defines an entity. It is basically a feature of any
entity in a database. It is given in the oval.
5

 Relationship: Given in diamond shape, and defines the relationship between two
entities in the database and their flow.
Data Elements:
It consists of the entity attributes in the database. The length and type of data are decided
at the time of creating the database.
Data Types:
It defines the type of data which can be stored for the variable. It can be integer, character,
or the Boolean value.
Indexes:
It performs as a pointer. It indicates the row location of the data.
Primary/Foreign Key:
A single attribute of the entity which identifies the entity uniquely in the database is called
as the primary key. And when one attribute is present in two different entities they are
linked and associated with each other with the help of the foreign key (Burroughs, 2012).
Figure 1 Example of Primary Key
Here, DrugID is the primary key of the drug table.
6

Figure 2 Example of Foreign Key
Here, DrugID is the foreign key from the above figure table.
VET Solution Database ERD:
ERD of the VET Solution company database has defined seven tables that having so many
attributes with the relation between them. The diagram is created by keeping in mind the
system and user requirements.
Figure 3 ERD Diagram
7