Designing and Developing a Relational Database for Vet Solution

This project focuses on designing and developing a relational database system for VET SOLUTION, a veterinary company. The solution begins with an introduction outlining the company's needs for a computerized database to improve communication and manage paperwork across its branches. The first component, LO1, involves creating an Entity Relationship (ER) diagram to visualize the database structure, followed by the creation of tables, which includes defining fields, data types, and primary/foreign keys. A detailed data dictionary is provided, describing each table's structure. LO2 focuses on the practical implementation, involving table creation using MS SQL server and constructing SQL queries to retrieve and manipulate data from multiple tables. LO3 details a comprehensive test plan to validate the system against user and system requirements, ensuring its functionality and reliability. Finally, LO4 includes technical and user documentation to guide the implementation and usage of the database system, including queries and test plan, complete with a test plan document history, positive aspects, test strategy, and exit criteria. This project provides a complete database solution.

VET SOLUTION
Submitted by:
Date:

Table of Contents
Introduction
L01 Use an appropriate design tool to design a relational database system for a substantial problem
 ER Diagram
 User Requirement
 Data Dictionary
 Data Validation
 Data Normalization
LO2 Develop a fully functional relational database system, based on an existing system design
 Create Table using MS SQL server
 Created Queries with multiple tables for the database using sql query tools
LO3 Test the system against user and system requirements.
 Create test plan for developed Vet Solution Database
LO4 Produce technical and user documentation
 User Documentation
 Technical Documentation
Conclusion
Reference

Introduction
A veterinary company, VET SOLUTION, which started in 2005 and based in Cambridge shire
have recently opened new branches in the southeast of England. Due to the large amount of
paper work and the poor communication within the staffs and the branches. VET SOLUTION
have approach your company, ULKOM LTD, requesting for a computerized database system.
LO1
Entity Relationship diagram

Table Creation

Lo2:
Data Dictionary:

Table Name : PAT
Field Name Data type & size Primary or
Foreign Key
Note
PATID Int (11) Primary Key
PATNAME Varchar (120)
STREET Varchar (120)
CITY Varchar (120)
ZIP Varchar (120)
Table Name : MEDICINE
Field Name Data Type &
Size
Primary or
Foreign Key
Note
MEDICINE_ID Int (11) Primary Key
M_NAME Varchar (120)
PRICE Int (11)
QUANTITY Int (11)
EXP_DATE Date
ILLCODE Int (11) Foreign key
Table Name : DOCTOR
Field Name Data type & Size Primary or
Foreign Key
Note
D_SSN Int (11) Primary Key
D_NAME Varchar (120)
PHONE Varchar (120)
ADDRESS Varchar (120)
OFFICE Varchar (120)
GENDER Char(1)
CITY Varchar (120)
STATE Varchar (120)
EMAIL Varchar (120)
Table Name : PATIENT
Field Name Data Type & Size Primary or
Foreign Key
Note

MEDICAREID Int (11) Primary Key
FIRSTNAME Varchar (120)
LASTNAME Varchar (120)
GENDER Char(1)
DOB Date
PHONE Varchar (120)
ADDRESS Varchar (120)
DEPTID Int (11) Foreign key
MEDICAREID Int (11) Foreign key
Table Name : ILLNESS
Field name Data type & Size Primary key or
Foreign Key
Note
ILLCODE Varchar (120) Primary Key
DESCRIPTION Varchar (120)
Table Name : DEPARTMENT
Field name Data type & Size Primary or
Foreign key
Note
DEPTID Primary Key
DEPTNAME Varchar (120)
DEPTADMIN Varchar (120)
NUMBEROFBEDS Int (11)
Table Name : TREATMENT
Filed Name Data type & Size Primary or
Foreign key
Note
TREATMENTID Int (11) Primary Key
TREATEMENTDATE Date
DOCTORID Int (11) Foreign key
PATID Int (11) Foreign key
DESCRIPTION Varchar (120)
PATIENTID Int (11) Foreign key
ILLCODE Int (11) Foreign key
MEDICINEID Int (11) Foreign key
PAYTRANS_NUM Int (11) Foreign key
TREATMENTID Int (11) Foreign key

TREATEMENTDATE Date
DOCTORID Int (11) Foreign key
PATID Int (11) Foreign key
Table Name : EMPLOYEE
Field Name Data type & size Primary or
Foreign key
Note
EID Int (11) Primary Key
ENAME Varchar (120)
EFAMILY Varchar (120)
JOBTITLE Varchar (120)
STARTDATE Date
GENDER char (1)
DOB Date
PHONE Varchar (120)
EMAIL Varchar (120)
DEPTID
Table Name : BILL
Field Name Data type & Size Primary or
Foreign key
Note
BILL_NUM Int (11) Primary Key
AMOUNT Int (11)
BILL_DATE Date
DUE_DATE Date
Table Name : PAYMENT
Field name Data type & size Primary or
Foreign key
Note
PAYTRANS_NUM Int (11) Primary Key
FK BILL_NUM Int (11)
PAY_METHOD Varchar (120)
PAY_STATUS Varchar (120)
PAID_DATE Date
-----------------
Queries:

SELECT P.PATNAME, P.STREET, P.CITY, P.STATE, P.ZIPCODE
FROM TREATMENT T, PAT P
WHERE T.PATID = P.patid
AND T.PHYSICIANID = '123456789'
ORDER BY P.PATNAME;
SELECT *
FROM PHYSICIAN
WHERE SUPERVISOR = '123456789'
ORDER BY PHYSNAME;
SELECT PATNAME, STREET, CITY, STATE
FROM PAT
WHERE STATE = 'TX';
SELECT PATNAME, STREET, CITY, STATE
FROM PAT
WHERE STATE
IN ('TX', 'NY', 'CA');
SELECT DESCCRIPT, COST
FROM ILLNESS
ORDER BY COST DESC;
SELECT COUNT( COST ) AS TOTAL_NO, MAX( COST ) AS MAXIMUM_COST, MIN( COST ) AS MINI
MUM_COST, AVG( COST ) AS AVERAGE_COST
FROM ILLNESS;
SELECT DRUGNAME, DCOST
FROM PRESCRIPTION
ORDER BY DCOST DESC;
SELECT COUNT( DCOST ) AS 'NUMBER', MAX( DCOST ) AS 'Most
Expensive', MIN( DCOST ) AS 'Cheapest', AVG( DCOST ) AS 'Average Cost'
FROM PRESCRIPTION;
SELECT COUNT( PATID ) AS 'TREATMENTS'
FROM TREATMENT
WHERE PHYSICIANID = '221100998';

SELECT P.PATNAME, P.STREET, P.CITY, P.STATE, P.ZIPCODE
FROM TREATMENT T, PAT P, PHYSICIAN H
WHERE T.PATID = P.PATID
AND T.PHYSICIANID = H.PHYSID
AND H.PHYSNAME = 'SMITH, MARY'
ORDER BY P.PATNAME DESC;
SELECT P.PATNAME, H.PHYSNAME
FROM TREATMENT T, PAT P, PHYSICIAN H
WHERE T.PATID = P.PATID
AND T.PHYSICIANID = H.PHYSID
AND H.PHYSID = '123456789'
AND H.SUPERVISOR = '123456789'
GROUP BY H.PHYSNAME;
SELECT P.DRUGNAME, T.TREATDATE
FROM PRESCRIPTION P, TREATMENT T
WHERE P.DRUGID = T.DRUGCODE
AND T.PHYSICIANID = '123456789'
ORDER BY T.TREATDATE;
SELECT DISTINCT P.DRUGNAME
FROM PRESCRIPTION P, TREATMENT T
WHERE P.DRUGID = T.DRUGCODE
AND T.PHYSICIANID = '123456789';
SELECT DISTINCT I.DESCCRIPT
FROM ILLNESS I, TREATMENT T
WHERE I.ILLCODE = T.ILLNESSCODE
AND T.PHYSICIANID = '123456789'
ORDER BY I.DESCCRIPT;
SELECT DISTINCT PHYSNAME
FROM PHYSICIAN P, PRESCRIPTION C, TREATMENT T
WHERE P.PHYSID = T.PHYSICIANID
AND C.DRUGID = T.DRUGCODE
AND SUPERVISOR = '123456789'
AND C.DRUGNAME