My name is NAY LYNN AUNG. I'm studying in KMD Company (Yangon, Myanmar) for your International Advanced Diploma in Computer Studies (IADCS). I'm not attending my classes regularly because of my career job and that job requires a lot of over time work and so that I can't have regular contact with KMD Company Yangon that's why I have many difficulties in order to under what I have to do and what I've done for this assignment. In my assignment scenario, I have been subcontracted to design the database system for Petcare. Petcare is a medium sized veterinary surgery with six branches across London. They want a database system developed to handle the records of the animals they care for, prescriptions and appointments. An initial analysis of Petcare has identified the following requirements:
Petcare keeps records of the animals they treat. Animals are defined by type and also by particular breed. The other information kept about an animal is: sex and age. All animals have an owner.
Breeds of dog are: Terrier, Poodle, Beagle, Doberman, Wolfhound, Retriever, and Other Dog.
Breeds of cat are: Persian, Siamese, Tabby, Egyptian, Manx, and Other Cat.
Breeds of rabbit are: Dwarf, English Spot, Dutch, Satin and Other Rabbit.
The Information kept on owners of animals is: name, address, home telephone number and mobile telephone number.
For appointments Petcare keep the following information: the animal the appointment is for, the owner of the animal who requested the appointment, which veterinary doctor the appointment is with, the time and date of appointment, the diagnosis made and the charge made for the appointment.
Appointments are at particular branches of Petcare.
The outcome of the appointment might be a prescription with one or more drugs on it. A prescription record contains the name of the drug type (Antibiotic, Painkiller, Behavior modification, Ear medication, and Skin medication). The prescription record also shows the period, in days, that the drug must be taken for. The cost of the medication is also recorded.
Each veterinary doctor should have the following information kept about them: name, address, home telephone number and mobile telephone number. Each veterinary doctor works at one branch of Petcare only and a record should be kept of this. A veterinary doctor may specialize in the treatment of one or more type of animal (dogs, cats, rabbits).
A record is kept for each branch of Petcare. This records the name of the branch (Enfield, Islington, Hackney, Holloway, Chingford or Leyton), address of the branch, telephone number, opening hours and emergency contact telephone number.
My name is NAY LYNN AUNG and I write this assignment by myself and I already signed my statement of confirmation of own work from NCC UK. However, I can't finish this assignment solely by myself and there are a lot of people who are helping me to pass this crisis and I have to acknowledge them from my deepest position of my heart. They help me by moving aside their jobs and career opportunities. First of all I would like to thank my mother from the bottom of my heart who help me to understand what at stake when I don't finish all my assignment when the deadline passed. I have some help from my life-long friend who is from MCC Company Myanmar named Zin Ko Ko. And I also got some help from couple of software houses. I thank all the personnel and friends who help me in this matter.
TASK - 1
Entity Relationship Diagram
Fig. 1.1 Entity relationship diagram for VETERINARY SUGERY DATABASE
TASK - 2
Total Normalization Form
Doctor ID (Primary Key)
Branch ID (Foreign Key)
Owner ID (Primary Key)
Branch ID (Primary Key)
Emergency Contact Phone
Animal ID (Primary Key)
Owner ID (Foreign Key)
Diagnosis ID (Primary Key)
Drug ID (Primary Key)
Appointment ID (Primary Key)
Branch ID (Foreign Key)
Doctor ID (Foreign Key)
Animal ID (Foreign Key)
Owner ID (Foreign Key)
Diagnosis ID (Foreign Key)
Prescription ID (Primary Key)
Appointment ID (Foreign Key)
Drug ID (Foreign Key)
Instruction to take drug
TASK - 3
All tables with Sample Data and Assumptions extracted from them.
In the above table, the table name is Animal Table and this table stores the information about animals which are possessed by which owner? This information is stored by mean of Animal ID, Animal Name, Animal Type, Animal Breed, Animal Sex, Animal Age and Owner ID. Animal ID and Owner ID are automatically given by DBMS software and they stand for particular owner and animals. This table is developed to relate with Appointment Table.
In the above appointment table, appointments are sort by Appointment ID, Branch ID, Doctor ID, Animal ID, Owner ID, Diagnosis ID and Appointment Date. In these headings, all the IDs are inserted as codes which stand for each information and Appointment Date is written by MM/DD/YYY format. This table works as reminder for office clerks and doctors showing them that which appointment ID is appointed at which branch with which doctor and they're gonna show which animal and that animal is owned by whom and that animal Diagnosis ID. By searching Branch ID, the clinic can get information about particular branch from Branch Table. By searching Doctor ID, the clinic can get information about particular doctor (i.e. name of doctor and so on according to doctor table in TNF). Animal ID shows the name of the animal, breed type and other information according to Animal Table. Owner ID stands for owner name and other owner information related to Owner Table. Diagnosis ID works as Diagnosis information according to Diagnosis table. The Appointment ID of Appointment Table is reusable in Prescription Table.
The table named Branch Table works as providing information about all the branches of Petcare to customers (i.e. owners of animals). This table consists of Branch ID, Branch Name, Branch Address, Branch Phone, Branch Hour and Branch Emergency Phone. Branch Table information is relates with Appointment Table.
The Diagnosis table serves as long-term storage of diagnosis information about animals. This table relates with Appointment Table. This table shows what diagnosis is given to what animal.
The doctor table shows Doctor Name, Doctor Address, Doctor Home Phone, Doctor Mobile Phone, Doctor Specialization and Branch ID. This table actually gives information about which doctor works at which branch respectively. This table relates with Appointment Table.
This table stores information about drugs available at all branches of Petcare. This table allows doctors to know that what drug is consisting in which drug type and what drug is used for what purpose. In the above table Augmentim, B6, Dexametazone, Aerolxime Ear Drops and Silverdam Cream are available. Drug Types are Antibiotic, Painkiller, Behavior modification, Ear medication and Skin medication. This table relates to Prescription Table.
The owner table stores information about owner of particular animal and it store Owner ID, Owner Name, Owner Address, Owner Home Phone and Owner Mobile Phone. This table relates with Appointment Table.
Every animal is given particular prescription by doctor when they finished their diagnosis. The above Prescription Table stores Prescription ID, Appointment ID, Drug ID, Instruction and Charges made for the particular prescription. The Appointment ID came from Appointment Table, Drug ID came from Drug Table and each prescription has particular Prescription ID in order to easily find the respective prescription after long-term treatment of particular animal.
All SQL Script Statements wrote to developed required database system
--Delete Database if exist
Drop Database VETERINARY
Create Database VETERINARY
Create Table tblOwner
OwnerID char (10) not null Primary Key,
OwnerName char (50),
OwnerAddress char (50),
OwnerHomePhone char (15),
OwnerMobilePhone char (15)
Create Table tblBranch
BranchID Char (10) not null Primary Key,
BranchName char (50),
BranchAddress char (50),
BranchPhone char (15),
BranchHour char (50),
BranchEmergyPhone char (15)
Create Table tblDiagnosis
DiagnosisID char (10) not null Primary key,
DiagnosisName char (50),
DiagnosisType char (50),
Create Table tblDrug
DrugID char (10) not null Primary Key,
DrugName char (50),
DrugType char (50),
DrugDescription char (50)
Create Table tblDoctor
DoctorID char (10) not null Primary Key,
DoctorName char (20),
DoctorAddress char (50),
DoctorHomePhone char (15),
DoctorMobilePhone char (15),
DoctorSpecialize char (30),
BranchID char (10) not null REFERENCES tblBranch(BranchID)
Create Table tblAnimal
AnimalID char (10) not null Primary Key,
AnimalName char (30),
AnimalType char (30),
AnimalBreed char (30),
AnimalSex char (10),
OwnerID char (10) not null REFERENCES tblOwner (OwnerID)
Create Table tblAppoitment
AppoitmentID char (10) not null Primary Key,
BranchID char (10) not null REFERENCES tblBranch(BranchID),
AnimalID char (10) not null REFERENCES tblAnimal(AnimalID),
OwnerID char (10) not null REFERENCES tblOwner(OwnerID),
DiagnosisID char (10) not null REFERENCES tblDiagnosis(DiagnosisID),
Create Table tblPrescription
PrescriptionID char (10) not null Primary Key,
AppoitmentID char (10) not null REFERENCES tblAppoitment(AppoitmentID),
DrugID char (10) not null REFERENCES tblDrug(DrugID),
Instruction char (50),
Charge char (30)
Insert Into tblOwner Values ('O001','Owner One','O One Address','1-11111','1-111111')
Insert Into tblOwner Values ('O002','Owner Two','O Two Address','2-22222','2-222222')
Insert Into tblOwner Values ('O003','Owner Three','O Three Address','3-33333','3-333333')
Insert Into tblOwner Values ('O004','Owner Four','O Four Address','4-44444','4-444444')
Insert Into tblOwner Values ('O005','Owner Five','O Five Address','5-55555','5-555555')
Insert Into tblBranch Values ('B001','Enfield','Enfield Address','11111-1','8 am to 5 pm','11111')
Insert Into tblBranch Values ('B002','Islington','Islington Address','22222-2','8:30 am to 5 pm','22222')
Insert Into tblBranch Values ('B003','Hackney','Hackney Address','33333-3','8 am to 5:30 pm','33333')
Insert Into tblBranch Values ('B004','Holloway','Holloway Address','44444-4','7 am to 4:30 pm','44444')
Insert Into tblBranch Values ('B005','Chingford or Layton','Chingford or Layton Address','55555-5','8:30 am to 5:45 pm','55555')
Insert Into tblDiagnosis Values ('D001','One Diagnosis','One Type','100')
Insert Into tblDiagnosis Values ('D002','Two Diagnosis','Two Type','200')
Insert Into tblDiagnosis Values ('D003','Three Diagnosis','Three Type','300')
Insert Into tblDiagnosis Values ('D004','Four Diagnosis','Four Type','400')
Insert Into tblDiagnosis Values ('D005','Five Diagnosis','Five Type','500')
Insert Into tblDrug Values ('G001','Drug One', 'Antibiotic','Whatever')
Insert Into tblDrug Values ('G002','Drug Two', 'Painkiller','Whatever')
Insert Into tblDrug Values ('G003','Drug Three', 'Behavior modification','Whatever')
Insert Into tblDrug Values ('G004','Drug Four', 'Ear medication','Whatever')
Insert Into tblDrug Values ('G005','Drug Five', 'Skin medication','Whatever')
Insert Into tblDoctor Values ('T001','Doctor One','Doctor One address','11-11111','111-11111','DOG','B001')
Insert Into tblDoctor Values ('T002','Doctor Two','Doctor Two address','22-22222','222-22222','CAT','B002')
Insert Into tblDoctor Values ('T003','Doctor Three','Doctor Three address','33-33333','333-33333','DOG','B003')
Insert Into tblDoctor Values ('T004','Doctor Four','Doctor Four address','44-44444','444-44444','RABBIT','B004')
Insert Into tblDoctor Values ('T005','Doctor Five','Doctor Five address','55-55555','555-55555','DOG','B005')
Insert Into tblAnimal Values ('A001','One Animal','Cat','Persian','Male','1','O001')
Insert Into tblAnimal Values ('A002','Two Animal','Dog','Terrier','Male','2','O002')
Insert Into tblAnimal Values ('A003','Three Animal','Cat','Siamese','Female','3','O003')
Insert Into tblAnimal Values ('A004','Four Animal','Rabbit','Dwarf','Male','4','O004')
Insert Into tblAnimal Values ('A005','Five Animal','Dog','Doberman','Female','5','O005')
Insert Into tblAppoitment Values ('P001','B001','A001','O001','D001',GetDate())
Insert Into tblAppoitment Values ('P002','B002','A002','O002','D002',GetDate())
Insert Into tblAppoitment Values ('P003','B003','A003','O003','D003',GetDate())
Insert Into tblAppoitment Values ('P004','B004','A004','O004','D004',GetDate())
Insert Into tblAppoitment Values ('P005','B005','A005','O005','D005',GetDate())
Insert Into tblPrescription Values ('N001','P001','G001','3 times a day','$100')
Insert Into tblPrescription Values ('N002','P002','G002','2 times a day','$150')
Insert Into tblPrescription Values ('N003','P003','G003','5 times a day','$150')
Insert Into tblPrescription Values ('N004','P004','G004','1 times a day','$200')
Insert Into tblPrescription Values ('N005','P005','G005','2 times a day','$50')
TASK - 4
Structured Query Language Statement Writing
Display the names and address of the branches of Petcare and the names of all veterinary doctors working at each of the branches. Any specialism(s) of the veterinary doctors should also be shown.
Select b.BranchName, b.BranchAddress, d.DoctorName, d.DoctorSpecialize
From tblBranch as b
Join tblDoctor as d
On b.BranchID = d.BranchID
Display all the appointments for the whole of the Petcare organization. This should be ordered by date. The result should display the branch the appointment is at, the name of the veterinary doctor the appointment is with, the date and time of the appointment, the name of the animal the appointment is for, the type of animal and the breed of the animal.
Select p.AppointmentDateTime ,b.BranchName,d.DoctorName,a.AnimalName,a.AnimalType,a.AnimalBreed
From tblAppoitment as p
Inner Join tblBranch as b
On p.BranchID = b.BranchID
Inner Join tblDoctor as d
On p.DoctorID = d.DoctorID
Inner Join tblAnimal as a
On p.AnimalID = a.AnimalID
Order By p.AppointmentDateTime
TASK - 5
Assumption and Discussions
According to the scenario, I assume that, there will be only one doctor in one branch. In real life, it is not possible, one branch can handle all type of animals and doctors. Doctor, should not be one doctor in one branch. A diagnosis type should define certain cost. But in prescriptive total charge may be vary according to the amount of drag to be taken. Drug may contain one or more types. However, developing such system for handle appointments and prescriptions will gain more pros than cons.
I want to discuss some idea about this assignment. As this assignment is about Database Design and Development subject, this assignment does not include other technique. However I want to give some opinion about developing more efficient software for Petcare organization as the organization is large enough and it have branches to manage, it will be more beneficial, if all branches are linked with a software named Inter-link Veterinary Clinic Information & Appointment Handling System and by linking with Intranet system, maintaining the system will be more convenient than travelling all sites with cars, planes and other means. You should assemble an IT team in one of the branches (i.e. this branch can said to be Head Office) so that this IT team personnel can log on to remote desktop program or remote assistant program and can assist the computer clerk from other branches when certain troubles occur. The Intranet linking system can more useful, when animals are necessary to transfer to certain branch for certain care (i.e. this concern with particular paper works such as treatments and prescriptions letters can transfer from online). This can also ensure security of information and information transfer speed. You can also link with banks for online cash transfer or credit cards availability for customers (i.e. owner of animals). You can also develop web-based appointment system for customer from around the world if your organization is large enough to know from around the world.