Veterinary Surgery Database
Development
October, 2010
M. D. Shirima
i
TABLE OF CONTENTS
TASK 1 ........................................................................................................... 1
ENTITY RELATIONSHIP DIAGRAM ............................................................... 1
RELATIONSHIP ............................................................................................. 1
TASK 2 ........................................................................................................... 2
NORMALIZED TABLES WITH CLEARLY INDICATION OF PRIMARY AND
FOREIGN KEYS.............................................................................................. 2
FIRST NORMAL FORM ................................................................................... 3
SECOND NORMAL FORM ............................................................................... 4
THIRD NORMAL FORM .................................................................................. 5
TASK 3 ........................................................................................................... 6
NORMALIZED TABLES SET UP IN ACCESS DATABASE MANAGEMENT
SYSTEM ......................................................................................................... 6
TASK 4 ......................................................................................................... 11
STRUCTURED QUERY LANGUAGE (SQL) ..................................................... 11
TASK 5 ......................................................................................................... 13
ASSUMPTIONS ............................................................................................ 13
REFERENCE ................................................................................................. 14
ii
TASK 1
ENTITY RELATIONSHIP DIAGRAM
Owner
Contacts
Breeds Animal Type
Veterinary
Branch Appointment Contacts
Diagnosis Charges
Prescription
RELATIONSHIP
This is a connection between entities. There are two rules needed when you want
to add relationship. These rules can be referred as cardinality and participation.
1
TASK 2
NORMALIZED TABLES WITH CLEARLY INDICATION OF PRIMARY AND
FOREIGN KEYS
UN-NORMALIZED DATA-SET
Data which are not normalized.
VETERINARYINFO
VetDrNo
VetDrName
VetDrAddress
VetDrHomTel
VetDrMobTel
BranchID
BranchName
BranchAddress
BranchTel
BranchOpnHrs
BranchEmergenceContact
AnimalID
AnimalName
AnimalAge
OwnerName
OwnerHomTel
OwnerMobTel
OwnerAddress
( AppointmentNo
AppointmentTime
AppointmentDate
DrugName
DrugType
DrugPeriod
AnimalTypeNo Repeating
AnimalTypeName Group
BreedTypeNo
BreedTypeName
VetSpecNo
VetSpecName
PrescriptionName
PrescriptionPeriod )
CostID
DrugCost
AppointmentCost
2
FIRST NORMAL FORM
“First Normal Form involves to get rid of repeating groups of data”.
First Normal Form
VETERINARYINFO APPOINTMENTINFO
VetDrNo VetDrNo*
VetDrName AppointmentNo
VetDrAddress AppointmentTime
VetDrHomTel AppointmentDate
VetDrMobTel DrugName
BranchID DrugType
BranchName DrugPeriod
BranchAddress AnimalTypeNo
BranchTel AnimalTypeName
BranchOpnHrs BreedTypeNo
BranchEmergenceContact BreedTypeName
AnimalID VetSpecNo
AnimalName VetSpecName
AnimalAge PrescriptionName
OwnerName PrescriptionPeriod
OwnerHomTel
OwnerMobTel
OwnerAddress
CostID
DrugCost
AppointmentCost
3
SECOND NORMAL FORM
“Second Normal Form is working with relation which was in First Normal Form its
affects only relations with more than one attribute in the key”.
Second Normal Form
VETERINARYINFO APPOINTMENTINFO
VetDrNo VetDrNo*
VetDrName AppointmentNo
VetDrAddress AppointmentTime
VetDrHomTel AppointmentDate
VetDrMobTel
BranchID DRUGDETAILS
BranchName DrugName
BranchAddress DrugType
BranchTel DrugPeriod
BranchOpnHrs
BranchEmergenceContact ANIMALTYPEINFO
AnimalID AnimalTypeNo
AnimalName AnimalTypeName
AnimalAge
OwnerName BREEDINFO
OwnerHomTel BreedTypeNo
OwnerMobTel BreedTypeName
OwnerAddress
CostID SPECIALIZATIONINFO
DrugCost AnimalTypeNo*
AppointmentCost VetSpecNo
VetSpecName
PRESCRIPTIONINFO
PrescriptionName
PrescriptionPeriod
4
THIRD NORMAL FORM
Third Normal Form is carry out for relations which is previously in second normal
form and considers dependencies among non-key.
Third Normal Form
VETERINARYINFO APPOINTMENTINFO
BranchID* VetDrNo*
AppointmentNo* CostID*
PrescriptionName* AppointmentNo
VetSpecNo* AppointmentTime
VetDrNo AppointmentDate
VetDrName
VetDrAddress DRUGDETAILS
VetDrHomTel CostID*
VetDrMobTel DrugName
DrugType
BRANCHINFO DrugPeriod
BranchID
BranchName ANIMALTYPEINFO
BranchAddress AnimalTypeNo
BranchTel AnimalTypeName
BranchOpnHrs
BranchEmergenceContact BREEDINFO
BreedTypeNo
ANIMAILINFO BreedTypeName
AnimalTypeNo*
BreedTypeNo* SPECIALIZATIONINFO
AnimalID AnimalTypeNo*
AnimalName VetSpecNo
AnimalAge VetSpecName
OWNERINFO PRESCRIPTIONINFO
AppointmentNo* PrescriptionName
AnimalID* PrescriptionPeriod
OwnerName
OwnerHomTel
OwnerMobTel
OwnerAddress
COSTINFO
CostID
DrugCost
AppointmentCost
5
TASK 3
NORMALIZED TABLES SET UP IN ACCESS DATABASE MANAGEMENT
SYSTEM
The following below figure 1 to figure 10 are tables for Petcare database.
Figure 1
Figure 2
6
Figure 3
Figure 4
7
Figure 5
Figure 6
8
Figure 7
Figure 8
Figure 9
9
Figure 10
DATABASE RELATIONSHIP
10
TASK 4
STRUCTURED QUERY LANGUAGE (SQL)
“SQL is a standard database sublanguage for relation database, management
systems.
This query displays the show branches name and branch address, veterinary doctors
name and their specialism(s).
SELECT BranchName, BranchAddress, VetDocName, VetSpecName
FROM BranchInfo, VeterinaryInfo, SpecializationInfo
WHERE [Link]=[Link]
AND [Link]=[Link]
After run the query above the output produced is as shown below.
The Output of Query
11
This query displays the all appointment of the Petcare which shows the branch
name, veterinary doctor, appointment date and time, animal name, animal type and
breed of animal which appointment made for.
SELECT BranchName, VetDocName, AnimalName, AnimalTypeName,
BreedTypeName, AppointmentDate, AppointmentTime
FROM BranchInfo, VeterinaryInfo, AnimalInfo, AnimalTypeInfo,
BreedInfo, AppointmentInfo
WHERE [Link]=[Link]
AND [Link]=[Link]
AND [Link]=[Link]
AND
[Link]=[Link]
ORDER BY [Link];
The Output of Query
12
TASK 5
ASSUMPTIONS
The Petcare organization database had been created through Microsoft Access all
tables have been analyzed and created to meet the purpose required for the
database and the assumptions made that for each table primary key have been
identified by entities ID, also the cost table will act as cost center for appointment
charges which we assume will be fixed price and drug charges which will be
identified by CostID as per task three shows the well-designed data printouts.
The implementation of the Petcare organization database was successful and
performance of the database meets the requirement and rules it’s easy to update,
delete, and add data in the database.
13
REFERENCE
1. NCC Database Development and Design – (IADCS) 2008; ISBN 0954307101
14