Veterinary Surgery Database Development Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

This assignment is for the Database Design and Development module. In this assignment, we will have to implement data modelling, data analysis and data design techniques to design and develop a database. First, we need to investigate and try to understand the scenario given. According to the given scenario, we will have to design a database for the Petcare veterinary surgery. Petcare is a medium sized veterinary surgery with six branches across London. It is given that the Petcare holds records of the animals they care for, prescriptions and appointments. Each animal has a particular owner. Each doctor has specialized in their fields of animal.

There are altogether five tasks in this assignment. The tasks require us to apply data analysis and design methods to accomplish them. We will have to use such methods as entity modelling and normalization techniques. After the data has been analysed and designed, we will need to set up the database and write SQL queries to retrieve data from the database.

In Task 1, we are required to draw an entity relationship data model which describes the content and structure of the data stored by Petcare.

Task 2 is the task in which we will have to produce the resulting normalised tables together with their primary keys and foreign keys.

According to Task 3, we need to set up the normalised tables from Task 2 using a Database Management System (DBMS) and populate the tables with test data. There need to be at least 5 records per table. Then, we will have to capture screen shots of the tables we have set up.

After setting up the tables, Task 4 asks us to set up and test queries using Structured Query Language (SQL) to retreive certain records from the tables. We will have to write SQL code for the following queries:

Display the names and addresses of the branches of PetCare and the names of all the veterinary doctors working at each of the branches. Any specialism(s) of the veterinary doctors should also be shown.

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 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.

Then, we will have to provide screen shots of the resulting output produced when the queries are run in the database.

Task 5 is the task in which we will have to explain assumptions we have made during the course of the assignment analysing, designing and implementing the database, provide reasons as to why we have chosen the approach we have taken and explain other alternative approaches we could have taken to carry out the above tasks from 1 to 4. We will also need to discuss changes we could make to improve our work.

Table of Contents

No Description Page

1 Cover Page 1

2 Confirmation and Statement 2

3 Introduction 3-4

4 Table of contents 5

5 Task 1 6-11

6 Task 2 12-21

7 Task 3 21-28

8 Task 4 29-33

9 Task5 34-36

10 Reference 36

ERD (Entity Relationship Diagram)

Owner

Prescription

Doctor

Animal

Branch

Appointment

Relational Schema

Animal - (Animal ID, Animal Name, Animal Type, Breed, Sex, Age, Owner ID)

Owner - (Owner ID, Name, Address, Home Telephone, Mobile Telephone)

Appointment - (Appointment ID, Doctor ID, Animal ID, Owner ID, Date, Time, Diagnosis,

Charges, Branch ID)

Branch - (Branch ID, Name, Address, Opening hours, Telephone No, Emergency

Contact No)

Prescription - (Prescription No, Drug Type, Periods, Day, Cost, Appointment ID)

Doctor - (Doctor ID, Name, Address, Home Telephone, Mobile Telephone, Specialize

animal)

Remark: Description

Primary Key:

Cardinality Ratio

Owner Animals ( 1 : M )

Owner Appointment ( 1 : M )

Animal Appointment ( 1 : M )

Doctor Appointment ( 1 : M )

Animal Doctor ( 1 : M )

Doctor Prescription ( 1 : M )

Branch Doctor ( 1 : M )

Entity with assumption

The relationship for these entities is One-to-Many. An animal has one owner and an owner can write more than one animals.

The relationship for these entities is One-to-Many. An owner can write more than one appointment and an appointment has one owner.

The relationship for these entities is One-to-Many. An animal has more than one appointment and an appointment has one animal.

Doctor and Appointment are related with One-to-Many relationship. A doctor can write more than one appointment and an appointment has one doctor.

The relationship for these entities is One-to-Many. A doctor can write more than one prescription and a prescription has one doctor.

Branch and Doctor are related with one-to-many relationship. A branch has more than one doctor and a doctor has one branch.

Content and structure of the data by Pet care

Entity Name: Animal

Primary Key: Animal ID

Foreign Key: Owner ID

Field Name

Data Type

Size

Description

Animal ID

Animal Name

Type

Breed

Sex

Age

Owner ID

Var char

Var char

Var char

Var char

Var char

Var char

Var char

10

50

20

30

10

2

20

Auto no of animal ID

Name of Animal

Type of animal

Breed of animal

Male or Female

Age of Animal

Auto no of owner ID

Entity Name: Appointment

Primary Key: Appointment ID

Foreign Key: Animal ID, Owner ID, Doctor ID, Branch ID

Field Name

Data type

Size

Description

Appointment ID

Owner ID

Doctor ID

Animal ID

Date/Time

Diagnosis

Charges

Branch ID

Var char

Var char

Var char

Var char

Var char

Var char

Var char

Var char

10

10

10

10

20

20

50

10

Auto no of appointment ID

Auto no of owner ID

Auto no of doctor ID

Auto no of animal ID

Date & Time of appointment

Diagnosis of appointment

Charges of appointment

Auto no of Branch ID

Entity Name: Prescription

Primary Key: Prescription ID

Foreign Key: Appointment ID

Field Name

Data Type

Size

Description

Prescription ID

Appointment ID

Drug Type

Periods

Cost

Var char

Var char

Var char

Var char

Var char

10

10

20

10

10

Auto no of prescription ID

Auto no of appointment ID

Type of drug

Times of drug used to take

Amount of money to pay

Entity Name: Doctor

Primary Key: Doctor ID

Foreign Key: Branch ID

Field Name

Data Type

Size

Description

Doctor ID

Name

Address

Home Telephone

Mobile No

Branch ID

Specialist

Var char

Var char

Text

Var char

Var char

Var char

Var char

10

20

30

20

20

10

20

Auto no of Doctor ID

Name of Doctor

Address of Doctor

Home Telephone no of Doctor

Mobile no of Doctor

Branch which have appointed

Animal that doctor specialize

Entity Name: Branch

Primary Key: Branch ID

Foreign Key: -

Field Name

Data Type

Size

Description

Branch ID

Name

Address

Opening hours

Telephone No

Emergency Contact No

Var char

Var char

Text

Var char

Var char

Var char

10

20

30

20

20

20

Auto no of Branch ID

Name of Branch

Address of Branch

Opening hours of Branch

Telephone No of Branch

Emergency contact no of Branch

Entity Name: Owner

Primary Key: Owner ID

Foreign Key: -

Field Name

Data Type

Size

Description

Owner ID

Name

Address

Home Telephone

Mobile Telephone

Var char

Var char

Text

Var char

Var char

10

20

30

20

20

Auto no of Owner ID

Name of Owner

Address of Owner

Home Telephone of Owner

Mobile Telephone of Owner

Branch Form

Doctor ID Doctor Name Specialize Animal

Branch Form

Branch ID: _____________ Telephone : ______________

Name : _____________ Opening hours: ______________

Address : _____________ Emergency Contact No: _______

UNF 3. 1NF

Branch ID Branch ID

Name Name

Address Address

Telephone Telephone

Opening hours Opening hours

Emergency Contact No Emergency Contact No

Doctor ID

Doctor Name Branch ID

Specialize Animal Doctor ID

Doctor Name

Choose a key Specialize Animal

Branch ID

2NF 5. 3NF

Branch ID Branch ID

Name Name

Address Address

Telephone Telephone

Opening hours Opening hours

Emergency Contact No Emergency Contact No

Branch ID Branch ID

Doctor ID Doctor ID

Doctor ID Doctor ID

Specialize Animal Specialize Animal

Doctor Name Doctor Name

Optimization

(Branch) (Doctor)

Branch ID Doctor ID

Branch Name Doctor Name

Address Specialize Animal

Telephone Branch ID

Opening hours

Emergency Contact No

Data Model

Doctor

Branch

Appointment Form

Appointment Form

Appointment ID: _______ Doctor ID : _________

Owner ID : _______ Doctor Name : _________

Owner Name : _______ Animal ID : _________

Date/Time : _______ Animal Type : _________

UNF 3. 1NF

Appointment ID Appointment ID

Animal ID Animal ID

Animal Type Animal Type

Owner ID Owner ID

Owner Name Owner Name

Doctor ID Doctor ID

Doctor Name Doctor Name

Date/Time Date/Time

Choose a key

Appointment ID

4. 2NF 5. 3NF

Appointment ID Appointment ID

Animal ID Animal ID

Animal Type Owner ID

Owner ID Doctor ID

Owner Name Date/Time

Doctor ID Animal ID

Doctor Name Animal Type

Date/Time Owner ID

Owner Name

Doctor ID

Doctor Name

6. Optimization

(Appointment) (Animal) (Owner) (Doctor)

Appointment ID Animal ID Owner ID Doctor ID

Animal ID Animal Type Owner Name Doctor Name

Owner ID

Doctor ID

Date/Time

7. Data Model

Animal

Appointment

Doctor

Owner

Prescription Form

Prescription Form

Prescription ID: ________

Animal ID : ________ Animal Type : ________

Owner ID : ________ Owner Name : ________

Doctor ID : ________ Doctor Name : ________

Appointment ID Drug Type Period Cost

UNF 3. 1NF

Prescription ID Prescription ID

Appointment ID Doctor Name

Animal ID Animal ID

Animal Type Animal Type

Owner ID Owner ID

Owner Name Owner Name

Doctor ID Doctor ID

Doctor Name

Drug Type Prescription ID

Period Appointment ID

Cost Drug Type

Period

Choose a key Cost

Prescription ID

4. 2NF

Prescription ID Prescription ID

Doctor Name Appointment ID

Animal ID Cost

Animal Type Period

Owner ID Drug Type

Owner Name

Doctor ID

3NF

Prescription ID Prescription ID

Owner ID Appointment ID

Animal ID Drug Type

Doctor ID Cost

Period

Owner ID

Owner Name

Animal ID

Animal Type

Doctor ID

Doctor Name

Optimization

(Prescription) (Animal) (Owner) (Doctor)

Prescription ID Animal ID Owner ID Doctor ID

Animal ID Animal Type Owner Name Doctor Name

Owner ID

Doctor ID

(Prescription Detail) (Appointment)

Prescription ID Appointment ID

Drug Type

Period

Cost

Data Model

Prescription

Owner

Appointment

Doctor

Prescription Detail

Animal

Table Design View and Datasheet View

Project Table Design View

Datasheet View

Project Table Design View

Datasheet View

Project Table Design View

Datasheet View

Project Table Design View

Datasheet View

Project Table Design View

Datasheet View

Project Table Design View

Datasheet View

Set-up and test all of the queries using Structured Query Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed.

Display the names and addresses of the branches of Pet Care and the names of all the veterinary doctors working at each of the branches. Any specialism(s) of the veterinary doctors should also be shown.

SELECT b.NAME AS BranchName, b.Address, d.name AS DoctorName, d.Specialist

FROM Branch b, doctor d

WHERE b.BranchID = d.BranchID

ORDER BY b.Name

Display all the appointments for the whole of the Pet Care 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 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 b.[Name] AS BranchName, b.Address, an.[Date/Time], d.[Name] AS DoctorName, a.[Name] AS AnimalName, a.[Type], a.[Breed]

FROM Animal a, Appointment an, Branch b, Doctor d

WHERE b.[BranchID]=an.[Branch ID]

AND d.[Doctor ID]=an.[Doctor ID]

AND a.[Animal ID]=an.[Animal ID]

ORDER By an.[Date/Time]

Analysis and Assumption

Task 1 is the task where we have to draw an entity relationship data model for the Petcare. Thus, to carry out Task 1, I have to first read the scenario given and try to gain an understanding of it. After reading the scenario, I extracted the entities that will be in the database system of Petcare. I deduced which will be entities and which will be the attributes of the entities. When I have carried out the analysis of the data held by Petcare, I have got the following entities: Animal, Owner, Appointment, Branch, Prescription and Doctor.

The relationship for Owner and Animal entities is One-to-Many. An animal has one owner and an owner can write more than one animals. The relationship for Owner and Appointment is One-to-Many. An owner can write more than one appointment and an appointment has one owner. The relationship for Animal and Appointment is One-to-Many. An animal has more than one appointment and an appointment has one animal. Doctor and Appointment are related with One-to-Many relationship. A doctor can write more than one appointment and an appointment has one doctor. Doctor and Prescription are related with One-to-Many relationship. A doctor can write more than one prescription and a prescription has one doctor. Branch and Doctor are related with One-to-Many relationship. A branch has more than one doctor and a doctor has one branch.

I have made the assumption that an owner can have more than one animal and each animal has more than one appointment. So, owner can make one or more appointment. An appointment has a one animal and doctor has many appointments. Animal has more than one doctor which that means doctor are specialized in their field of animal. Doctor makes a prescription for one animal at a time. So, doctor has many prescriptions. . Petcare is a medium sized veterinary surgery with six branches across London. So, there are more than one doctor in each branch.

After identifying the entities, I determined attributes for them in Task 1. The entities I have produced in Task 1 will become tables in the database. When carrying out Task 2, I have to identify the primary keys and foreign keys for the tables produced. Since the primary key is unique in a table, I decided to have Animal ID as primary key in Animal table. Similarly, Appointment ID as primary key in Appointment table, Prescription ID in Prescription table, Doctor ID in Doctor Table, Branch ID in Branch table, Owner ID in Owner table. Owner table and Animal table are related with one-to-many relationship. In addition, Owner ID will be the foreign key in the Animal Table. In Appointment Table, Animal ID, Doctor ID, Branch ID and Owner ID will be the foreign keys and Appointment ID is foreign key in Prescription Table. In the Doctor Table Branch ID is foreign key. After Task 2, I have got tables and their fields and the types and sizes of the fields.

Since requires us to normalize the tables produced in Task 1, I have considered the form design of the forms likely to be used by Petcare. I assumed that the entities of Petcare are Animal, Owner, Appointment, Branch, Prescription, Doctor. Therefore, I have used the forms which focuses on these entities to carry out normalization. The forms I have used are Branch Form, Appointment Form and Prescription Form. Branch Form is the form which shows the detail of Branch, Doctor who are specialized in what animal. Appointment Form is the form which describes the detail of Owner, Doctor and Animal. Prescription Form is the form which shows the detail of Prescription gives to which animal, given by whom, which drug are given for how many periods and how much it cost. The data models produced from normalising the forms is the same as that of the ERD model in Task 1 when they are combined. If we carry out Task 1 first, there is a probability that we may find that the normalised data model and the ERD do not match.

I decided to used Microsoft SQL Server 2005 to set up the database for the Petcare since I am familiar with this DBMS. I created tables in the database and inserted test data in the database. When populating the database with the test data, I inserted meaningful data since it would be easier to see and understand the database. I defined the field names, data types, sizes and formats of the fields of the tables in the design view. Since the task requires that there should at least be 5 records in each table, I inserted a minimum of 5 records in the table. I defined primary keys and foreign keys in the table. After that, I created a relationship diagram showing the relationships between the tables using primary keys and foreign keys. In Task 3, I have presented the tables I have created both in design view and datasheet view. An alternative approach in setting up the database will be to use Microsoft SQL Server or other DBMS of choice.

When creating the query displaying all six branches names and addresses doctor in each branch and their specialism in what animal. Then I created another query showing Branch Name, Address, and Appointment Date/Time, Doctor Name, Animal Name, Animal Type and Breed.

In conclusion, these are the assumptions I have made when analysing, designing and implementing the database and the approaches I have taken.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.