Veterinary Surgery Database Development Management Essay

Published Date: 23 Mar 2015

This assignment is to develop a database system for Petcare. Petcare is a medium sized veterinary surgery with six branches (Enfield, Islington, Hackney, Holloway, Chingford, and Leyton) across London. Petcare want to develop a database system to handle the records of the animals they care for, prescriptions and appointments.

Their requirements are to keep records of the animals they treat, owner of that the animal, animal type such as cat, dog, rabbit, etc., animal's breed (for example, breed of dog are Terrier, Poodle, Beagle, etc.,) appointments information, owner of the animal who requested the appointment, which veterinary doctor is appointed for, date and time of the appointment, diagnosis made and the charge made for the appointment, name of the branch that is appointed for, drug type, cost of the medication, doctor's information and their specialization, and branch of each Petcare.

Other information kept about an animal is: sex and age. And for an owner of animal is: name, address, home telephone number, mobile telephone number. For each appointment, Petcare keeps the information like 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 the appointment, the diagnosis made and the charge made for the appointment. It also keeps prescription record that contains the name of the drug type, period and cost of the medication. Petcare also kept the following information for each doctor: name, address, home telephone number, mobile telephone number, and specialisation that doctor is specialised for. At Petcare, each veterinary doctor works at one branch of Petcare care only and petcare keeps this record. As Petcare has six branches, a record also is kept for each branch. This record contains the name of the branch, address of the branch, telephone number, opening hours and emergency contact telephone number.

To develop the system described above, it is analyzed and design by drawing ER diagram, normalizing the each entity and implement by My SQL, a Database Management System. The Structured Query Language (SQL) is used to get the require queries results.

Task 1 - 30 Marks

Draw an Entity Relationship Data Model that describes the content and structure of the data held by Petcare. Specify cardinality ratio and participation constraint of each relationship type.

1.1 Entity Relationship Data Model for Petcare

Fig 1.1 Entity Relationship Diagram for Petcare

1.2 Content and Structure of data held by Petcare

1.2.1 Animal

Entity Name : Animal

Entity Description : Animals that are appointed and treated by Petcare's veterinary

doctor.

Attributes : AnimalID

OwnerID

BreedID

Name

Sex

Age

1.2.2 Type

Entity Name : Type

Entity Description : Type (dog, cat, rabbit, etc) of animal that are appointed for.

Attributes : TypeID

TypeName

1.2.3 Breed

Entity Name : Breed

Entity Description : Breed of animals' (for example Breed of cat are Persian, Siamese, Tabby) that are appointed and treated by Petcare.

Attributes : BreedID

TypeID

BreedName

1.2.4 Owner

Entity Name : Owner

Entity Description : Owner of animal who requested an appointment for his/her pet.

Attributes : OwnerID

OwnerName

Address

HomeTelephoneNo

MobileNo

1.2.5 Appointment

Entity Name : Appointment

Entity Description : Appointment made by Owner of the animal at one of the Petcare's

branches.

Attributes : AppointmentID

AnimalID

DoctorID

Time

Date

Diagnosis

Charges

1.2.6 Prescription

Entity Name : Prescription

Entity Description : Prescription made by each appointment with appointed veterinary

doctor at Petcare.

Attributes : PrescriptionID

AppointmentID

Cost

1.2.7 Treatment

Entity Name : Treatment

Entity Description : Treatment for each prescription by specific veterinary doctor.

Attributes : TreatmentID

DrugID

PrecriptionID

Period

1.2.8 Drug

Entity Name : Drug

Entity Description : Drug Type that is used when treating animals.

Attributes : DrugID

DrugType

1.2.9 Doctor

Entity Name : Doctor

Entity Description : People who treat the appointed animals at Petcare.

Attributes : DoctorID

BranchID

DoctorAddress

DoctorHomeTelephoneNo

DoctorMobileNo

1.2.10 Doctor_Specialisation

Entity Name : DoctorSpecialisation

Entity Description : Specialisation of each veterinary doctor at Petcare.

Attributes : DoctorID

TypeID

1.2.11 Branch

Entity Name : Branch

Entity Description : Branch of Petcar, place where the appointments are made.

Attributes : BranchID

BranchName

BranchAddress

TelephoneNo

OpeningHour

EmergencyTelephoneNo

1.3 Cardinality ratio and Participation Constraint

1.3.1 Breed/Type

Fig1.2 Breed/Type Relationship

The cardinality ratio - Many to One (N: M)

Participation Constraint - One animal Type has many Breeds of that animal but one Breed has only one animal Type. The participation of this relationship is mandatory because all instances of Breed participate in Type and all instances of Type also participate in Breed relationship.

1.3.2 Animal/Breed

Fig1.3 Animal/Breed Relationship

The cardinality ratio - Many: One (N: M)

Participation Constraint - One Breed has many Animals but one Animal has only one Breed. The participation of this relationship is mandatory because all instances of Animal participate in Breed and all instances of Breed also participate in Animal relationship.

1.3.3 Owner/Animal

Fig1.4 Owner/Animal Relationship

The cardinality ratio - One: Many (M: N)

Participation Constraint - One Owner has many Animals but one Animal has only one Owner. The participation of this relationship is mandatory because all instances of Owner participate in Animal and all instances of Animal also participate in Owner relationship.

1.3.4 Animal/Appointment

Fig1.5 Animal/Appointment Relationship

The cardinality ratio - One: Many (M: N)

Participation Constraint - One Animal can make more than one Appointment but one Appointment has only one Animal. The participation of this relationship is mandatory because all instances of Animal participate in Appointment and all instances of Appointment also participate in Animal relationship.

1.3.5 Appointment/Prescription

Fig1.6 Appointment/Prescription

The cardinality ratio - One: One (1:1)

Participation Constraint - One Appointment has one Prescription and one Prescription has one Appointment. The participation of this relationship is mandatory because all instances of Appointment participate in Prescription and all instances of Prescription also participate in Appointment relationship.

1.3.6 Prescription/Treatment

Fig1.7 Prescription/Treatment Relationship

The cardinality ratio - One: Many (M: N)

Participation Constraint - One Prescription has one or more Treatment but one Treatment has only one Prescription. The participation of this relationship is mandatory because all instances of Prescription participate in Treatment and all instances of Treatment also participate in Prescription relationship.

1.3.7 Treatment/Drug

Fig1.8 Treatment/Drug Relationship

The cardinality ratio - Many: One (N: M)

Participation Constraint - One Treatment has at least one Drug but one Drug may have one or more Treatment. The participation of this relationship is optional because there may one instances of Drug that does not participate in Treatment. This means that there may be other drug type without treatment.

1.3.8 Doctor/Appointment

Fig1.9 Doctor/Appointment Relationship

The cardinality ratio - Many: One (N: M)

Participation Constraint - One Doctor may have one or more Appointment but one Appointment must have one Doctor. The participation of this relationship is optional because there may one instances of Doctor that does not participate in Appointment. This means that there may be Doctor who does not have any appointment.

1.3.9 Doctor/Type

Fig1.10 Doctor/Type Relationship

The cardinality ratio - Many: Many

Participation Constraint - One Doctor can specialise one or more Type of animals and one Type can be specialized by one or more Doctors. The participation of this relationship is mandatory. Here is a missing entity which describes that which Doctor specialises in which Type of animals. Therefore, a link entity 'Doctor Specialization' is added as shown in Fig1.11.

Fig1.11 Doctor/Type Relationship with Link entity 'Doctor Specialization'

1.3.11 Doctor/Branch

Fig1.12 Doctor/Branch Relationship

The cardinality ratio - One: Many (N: M)

Participation Constraint - One Doctor can work in one Branch only but one Branch has one or more Doctors. The participation of this relationship is mandatory because all instances of Doctor participate in Branch and all instances of Branch also participate in Doctor Relationship.

Task 2 - 20 Marks

Produce the resulting normalised tables clearly indicating the primary and foreign keys.

2.1 Normalisation

Unnormalised Form

First Normal Form

AnimalType

Breed

Sex

Age

OwnerName

OwnerAddress

OwnerHomeTelephone

OwnerMobileTelephone

AnimalName

Animal'sOwner

DoctorName

AppointmentTime

AppointmentDate

Diagnosis

Charge

DrugType

Period

MedicationCost

DoctorName

DoctorAddress

DoctorHomeTelephone

DoctorMobileTelephone

Specialisation

BranchName

BranchAddress

BranchTelephone

OpeningHour

EmergencyContactTelephone

Animal

AnimalID

AnimalName

AnimalType

Breed

Sex

Age

Owner

OwnerID

OwnerName

OwnerAddress

OwnerHomeTelephone

OwnerMobileTelephone

Appointment

AppointmentID

*AnimalID

*OwnerID

*DoctorID

AppointmentTime

AppointmentDate

Diagnosis

Charge

DrugType

Period

MedicationCost

Doctor

DoctorID

DoctorName

DoctorAddress

DoctorHomeTelephone

DoctorMobileTelephone

Specialisation

Branch

BranchID

BranchName

BranchAddress

BranchTelephone

OpeningHour

EmergencyContactTelephone

Second Normal Form

Animal

AnimalID

*OwnerID

*BreedID

AnimalName

Sex

Age

Type

TypeID

TypeName

Breed

BreedID

*TypeID

BreedName

Owner

OwnerID

OwnerName

OwnerAddress

OwnerHomeTelephone

OwnerMobileTelephone

Appointment

AppointmentID

*AnimalID

*DoctorID

AppointmentTime

AppointmentDate

Diagnosis

Charge

Drug

DrugID

DrugType

Treatment

TreatmentID

*AppointmentID

*DrugID

Period

Cost

Doctor

DoctorID

*BranchID

DoctorName

DoctorAddress

DoctorHomeTelephone

DoctorMobileTelephone

Specialisation

Branch

BranchID

BranchName

BranchAddress

BranchTelephone

OpeningHour

EmergencyContactTelephone

Third Normal Form

Animal

AnimalID

*OwnerID

*BreedID

AnimalName

Sex

Age

Type

TypeID

TypeName

Breed

BreedID

*TypeID

BreedName

Owner

OwnerID

OwnerName

OwnerAddress

OwnerHomeTelephone

OwnerMobileTelephone

Appointment

AppointmentID

*AnimalID

*DoctorID

AppointmentTime

AppointmentDate

Diagnosis

Charge

Drug

DrugID

DrugType

Prescription

PrescriptionID

*AppointmentID

Cost

Treatment

TreatmentID

*PrescriptionID

*DrugID

Period

Doctor_Specilisation

DoctorID

TypeID

Doctor

DoctorID

*BranchID

DoctorName

DoctorAddress

DoctorHomeTelephone

DoctorMobileTelephone

Specialisation

Branch

BranchID

BranchName

BranchAddress

BranchTelephone

OpeningHour

EmergencyContactTelephone

2.2 Normalised Tables

2.2.1 Animal

No

Field Name

Description

Status

References

1

AnimalID

Animal Identity No

Primary Key

2

AnimalName

Animal Name

3

Sex

Animal's Sex

4

Age

Animal's Age

5

BreedID

Animal's Breed Identity No

Foreign Key

Breed

6

OwnerID

Animal's Owner Identity No

Foreign Key

Owner

2.2.2 Type

No

Field Name

Description

Status

References

1

TypeID

Type Identity No

Primary Key

2

TypeName

Type Name

2.2.3 Breed

No

Field Name

Description

Status

References

1

BreedID

Breed Identity No

Primary Key

2

BreedName

Breed Name

3

TypeID

Type Identity No

Foreign Key

Type

2.3.4 Owner

No

Field Name

Description

Status

References

1

OwnerID

Owner Identity No

Primary Key

2

OwnerName

Owner Name

3

OwnerAddress

Owner Address

4

OwnerHomeTelephoneNo

Owner Home Telephone No

5

OwnerMobileNo

Owner Mobile No

2.2.5 Appointment

No

Field Name

Description

Status

References

1

AppointmentID

Appointment Identity No

Primary Key

2

AnimalID

Animal Identity No

Foreign Key

Animal

3

DoctorID

Doctor Identity No

Foreign Key

Doctor

4

AppointmentTime

Appointment Time

5

AppointmentDate

Appointment Date

6

Diagnosis

Diagnosis made

7

Charges

Charges made for the appointment

2.2.6 Prescription

No

Field Name

Description

Status

References

1

PrescriptionID

Prescription Identity No

Primary Key

2

AppointmentID

Appointment Identity No

Foreign Key

Appointment

3

Cost

Cost of the medication

2.2.7 Treatment

No

Field Name

Description

Status

References

1

TreatmentID

Treatment Identity No

Primary Key

2

DrugID

Drug Identity No

Foreign Key

Drug

3

PrescriptionID

Prescription Identity No

Foreign Key

Prescription

4

Period

Period that the drug must be taken for

2.2.8 Drug

No

Field Name

Description

Status

References

1

DrugID

Drug Type Identity No

Primary Key

2

DrugType

Drug Type Name

2.2.9 Doctor

No

Field Name

Description

Status

References

1

DoctorID

Doctor Identity No

Primary Key

2

DoctorName

Doctor Name

3

DoctorAddress

Doctor Address

4

DcotorHomeTelephoneNo

Doctor Home Telephone No

5

DoctorMobileNo

Doctor Mobile No

6

BranchID

Branch Identity No

Foreign Key

Branch

2.2.10 Doctor_Sepcialization

No

Field Name

Description

Status

References

1

DoctorID

Doctor Identity No

Foreign Key

Doctor

2

TypeID

Animal Type Identity No

Foreign Key

Type

2.11 Branch

No

Field Name

Description

Status

References

1

BranchID

Branch Identity No

Primary Key

2

BranchName

Branch Name

3

BranchAddress

Branch Address

4

BranchTelephoneNo

Branch Telephone No

5

OpeningHr

Branch Opening Hour

6

ClosingHr

Branch Closing Hour

7

EmergencyTelephoneNo

Branch Emergency Telephone No

Task 3 - 20 Marks

Using a Database Management System (DBMS) of your choice, set up all of the above normalised tables, and populate them with well-designed test data (minimum 5 records per table). Provide printouts of all tables.

3.1 Owner Table

CREATE TABLE Owner (

OwnerID VARCHAR(5) NOT NULL UNIQUE, PRIMARY KEY (OwnerID),

OwnerName VARCHAR(50) NOT NULL,

OwnerAddress VARCHAR(150) NOT NULL,

OwnerHomeTelephoneNo VARCHAR(15) NOT NULL,

OwnerMobileNo VARCHAR(15) NOT NULL);

3.1.1 Table Design

No

Field Name

Data Type

Size

Description

1

OwnerID

VARCHAR

5

Owner Identity No

2

OwnerName

VARCHAR

50

Owner Name

3

OwnerAddress

VARCHAR

150

Owner Address

4

OwnerHomeTelephoneNo

VARCHAR

15

Owner Home Telephone No

5

OwnerMobileNo

VARCHAR

15

Owner Mobile No

3.1.2 Test Data

Fig 3.1 Test Data for Owner Table

3.2 Type Table

CREATE TABLE Type (

TypeID VARCHAR(3) NOT NULL UNIQUE, PRIMARY KEY (TypeID),

TypeName VARCHAR(50) NOT NULL UNIQUE);

3.2.1 Table Design

No

Field Name

Data Type

Size

Description

1

TypeID

VARCHAR

3

Type Identity No

2

TypeName

VARCHAR

50

Type Name

3.2.2 Test Data

Fig 3.2 Test Data for Animal Type Table

3.3 Breed Table

CREATE TABLE Breed (

BreedID VARCHAR(3) NOT NULL UNIQUE, PRIMARY KEY (BreedID),

BreedName VARCHAR(50) NOT NULL,

TypeID VARCHAR(3) NOT NULL,

CONSTRAINT TypeID FOREIGN KEY TypeID (TypeID)

REFERENCES `Type` (TypeID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.3.1 Table Design

No

Field Name

Data Type

Size

Description

1

BreedID

VARCHAR

3

Breed Identity No

2

BreedName

VARCHAR

50

Breed Name

3

TypeID

VARCHAR

3

Type Identity No

3.3.2 Test Data

Fig 3.3 Test Data for Breed Table

3.4 Animal Table

CREATE TABLE animal (

AnimalID VARCHAR(5) NOT NULL UNIQUE, PRIMARY KEY (AnimalID),

AnimalName VARCHAR(50) NOT NULL,

Sex VARCHAR(1) NOT NULL,

Age SMALLINT(2) NOT NULL,

BreedID VARCHAR(3) NOT NULL,

OwnerID VARCHAR(5) NOT NULL,

CONSTRAINT BreedID FOREIGN KEY BREEDID (BREEDID)

REFERENCES breed (BreedID)

ON DELETE RESTRICT

ON UPDATE RESTRICT,

CONSTRAINT OwnerID FOREIGN KEY OwnerID (OwnerID)

REFERENCES owner (OwnerID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.4.1 Table Design

No

Field Name

Data Type

Size

Description

1

AnimalID

VARCHAR

5

Animal Identity No

2

AnimalName

VARCHAR

50

Animal Name

3

Sex

VARCHAR

1

Animal's Sex

4

Age

SMALLINT

2

Animal's Age

5

BreedID

VARCHAR

3

Animal's Breed Identity No

6

OwnerID

VARCHAR

5

Animal's Owner Identity No

3.4.2 Test Data

Fig 3.4 Test Data for Animal Table

3.5 Branch Table

CREATE TABLE branch (

BranchID VARCHAR(2) NOT NULL UNIQUE , PRIMARY KEY (BranchID),

BranchName VARCHAR(50) NOT NULL,

BranchAddress VARCHAR(150) NOT NULL,

BranchTelephoneNo VARCHAR(15) NOT NULL,

OpeningHr TIME NOT NULL,

ClosingHr TIME NOT NULL,

EmergencyTelephoneNo VARCHAR(15) NOT NULL);

3.5.1 Table Design

No

Field Name

Data Type

Size

Description

1

BranchID

VARCHAR

3

Branch Identity No

2

BranchName

VARCHAR

50

Branch Name

3

BranchAddress

VARCHAR

150

Branch Address

4

BranchTelephoneNo

VARCHAR

15

Branch Telephone No

5

OpeningHr

TIME

Branch Opening Hour

6

ClosingHr

TIME

Branch Closing Hour

7

EmergencyTelephoneNo

VARCHAR

15

Branch Emergency Telephone No

3.5.2 Test Data

Fig 3.5 Test Data for Branch Table

3.6 Doctor Table

CREATE TABLE Doctor (

DoctorID VARCHAR(3) NOT NULL UNIQUE, PRIMARY KEY (DoctorID),

BranchID VARCHAR(2) NOT NULL,

DoctorName VARCHAR(50) NOT NULL,

DoctorAddress VARCHAR(150) NOT NULL,

DoctorHomeTelephoneNo VARCHAR(15),

DocotrMobileNo VARCHAR(15),

CONSTRAINT BranchID FOREIGN KEY (BranchID)

REFERENCES branch (BranchID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.6.1 Table Design

No

Field Name

Data Type

Size

Description

1

DoctorID

VARCHAR

3

Doctor Identity No

2

DoctorName

VARCHAR

50

Doctor Name

3

DoctorAddress

VARCHAR

150

Doctor Address

4

DcotorHomeTelephoneNo

VARCHAR

15

Doctor Home Telephone No

5

DoctorMobileNo

VARCHAR

15

Doctor Mobile No

6

BranchID

VARCHAR

3

Branch Identity No

3.6.2 Test Data

Fig 3.6 Test Data for Doctor Table

3.7 Doctor Specialization Table

CREATE TABLE Doctor_Specialization (

DoctorID VARCHAR(3),

TypeID VARCHAR(3));

3.7.1 Table Design

No

Field Name

Data Type

Size

Description

1

DoctorID

VARCHAR

3

Doctor Identity No

2

TypeID

VARCHAR

3

Animal Type Identity No

3.7.2 Test Data

Fig 3.7 Test Data for Doctor_Specialization Table

3.8 Appointment Table

CREATE TABLE Appointment (

AppointmentID VARCHAR(7) NOT NULL UNIQUE, PRIMARY KEY (AppointmentID),

AnimalID VARCHAR(5) NOT NULL,

DoctorID VARCHAR(3) NOT NULL,

AppointmentTime TIME NOT NULL,

AppointmentDate DATE NOT NULL,

Diagnosis VARCHAR(100) NOT NULL,

Charges DECIMAL(10,0) NOT NULL,

CONSTRAINT AnimalID FOREIGN KEY AnimalID (AnimalID)

REFERENCES Animal (AnimalID)

ON DELETE RESTRICT

ON UPDATE RESTRICT,

CONSTRAINT DoctorID FOREIGN KEY DoctorID (DoctorID)

REFERENCES Doctor (DoctorID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.8.1 Table Design

No

Field Name

Data Type

Size

Description

1

AppointmentID

VARCHAR

7

Appointment Identity No

2

AnimalID

VARCHAR

5

Animal Identity No

3

DoctorID

VARCHAR

3

Doctor Identity No

4

AppointmentTime

TIME

Appointment Time

5

AppointmentDate

DATE

Appointment Date

6

Diagnosis

VARCHAR

100

Diagnosis made

7

Charges

DECIMAL

10,0

Charges made for the appointment

3.8.2 Test Data

Fig 3.8 Test Data for Appointment Table

3.9 Prescription Table

CREATE TABLE Prescription (

PrescriptionID varchar(6) NOT NULL UNIQUE, PRIMARY KEY (PRESCRIPTIONID),

AppointmentID VARCHAR(7) NOT NULL,

Cost DECIMAL(10,0) NOT NULL,

CONSTRAINT AppointmentID FOREIGN KEY (AppointmentID)

REFERENCES Appointment (AppointmentID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.9.1 Table Design

No

Field Name

Data Type

Size

Description

1

PrescriptionID

VARCHAR

6

Prescription Identity No

2

AppointmentID

VARCHAR

7

Appointment Identity No

3

Cost

DECIMAL

10,0

Cost of the medication

3.9.2 Test Data

Fig 3.9 Test Data for Prescription Table

3.10 Drug Table

CREATE TABLE drug (

DrugID VARCHAR(2) NOT NULL UNIQUE, PRIMARY KEY (DrugID),

DrugType VARCHAR(20) NOT NULL);

3.10.1 Table Design

No

Field Name

Data Type

Size

Description

1

DrugID

VARCHAR

4

Drug Type Identity No

2

DrugType

VARCHAR

20

Drug Type Name

3.10.2 Test Data

Fig 3.10 Test Data for Drug Table

3.11 Treatment Table

CREATE TABLE Treatment (

TreatmentID VARCHAR(7) NOT NULL UNIQUE, PRIMARY KEY (TreatmentID),

DrugID VARCHAR(4) NOT NULL,

PrescriptionID VARCHAR(6) NOT NULL,

Period DECIMAL(10,0) NOT NULL,

CONSTRAINT PrescriptionID FOREIGN KEY (PrescriptionID)

REFERENCES Prescription (PrescriptionID)

ON DELETE RESTRICT

ON UPDATE RESTRICT,

CONSTRAINT DrugID FOREIGN KEY (DrugID)

REFERENCES Drug (DrugID)

ON DELETE RESTRICT

ON UPDATE RESTRICT);

3.11.1 Table Design

No

Field Name

Data Type

Size

Description

1

TreatmentID

VARCHAR

7

Treatment Identity No

2

DrugID

VARCHAR

4

Drug Identity No

3

PrescriptionID

VARCHAR

6

Prescription Identity No

4

Period

INTEGER

5

Period that the drug must be taken for

3.11.2 Test Data

Fig 3.11 Test Data for Treatment Table

Task 4 - 20 Marks

Set-up and test all of the following 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 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 organisation. This should be ordered by date. The result should display the branch the appointment is at, the name of the veterinary doc tor 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 bred of the animal.

4.1 SQL Code for First Query

SELECT DISTINCT b.BranchName,BranchAddress, d.DoctorName ,t.TypeName as 'Specialism(s)'

FROM branch b, doctor d, `type` t, doctor_specialization ds

WHERE ds.DoctorID=d.DoctorID

AND ds.TypeID=t.TypeID

AND d.BranchID=b.BranchID

ORDER BY BranchName;

4.1.1 Expected Result table

Branch Name

Branch Address

Doctor Name

Specialism(s)

Chingford

No.5,35thStreet

D5

Rabbit

Chingford

No.5, 35thStreet

D5

Cat

Enfield

No.1, 31stStreet

D1

Cat

Enfield

No.1, 31stStreet

D1

Dog

Hackney

No.3, 33rdStreet

D3

Rabbit

Hackney

No.3, 33rdStreet

D3

Dog

Holloway

No.4, 34thStreet

D4

Dog

Holloway

No.4, 34thStreet

D4

Cat

Islington

No.2, 32ndStreet

D2

Rabbit

Islington

No.2, 32nd Street

D2

Cat

4.1.2 Actual Result Table

Fig 4.1 Result Table

4.2 SQL Code for Second Query

SELECT DISTINCT ap.AppointmentID,bh.BranchName,d.DoctorName,ap.AppointmentDate,AppointmentTime,a.AnimalName,

t.TypeName AS AnimalType,b.BreedName As Breed

FROM appointment ap, branch bh, doctor d,animal a,`type` t,breed b

WHERE ap.DoctorID=d.DoctorID

AND d.BranchID=bh.BranchID

AND ap.AnimalID=a.AnimalID

AND a.BreedID=b.BreedID

AND b.TypeID=t.TypeID

ORDER BY ap.AppointmentDate;

4.2.1 Expected Result Table

Appointment ID

Branch Name

Doctor Name

Appointment Date

Appointment Time

Animal Name

Animal Type

Breed

Ap00004

Hackney

D3

2010-08-30

09:00:00

a4

Dog

Doberman

Ap00003

Islington

D2

2010-09-05

09:00:00

a3

Rabbit

Satin

Ap00005

Holloway

D4

2010-09-12

09:00:00

a5

Dog

Retriever

Ap00001

Enfield

D1

2010-12-11

09:00:00

a1

Dog

Terrier

Ap00002

Enfield

D1

2010-12-13

09:00:00

a2

Dog

Wolfhound

4.2.2 Actual Result Table

Fig 4.2 Result Table

Task 5 - 10 Marks

Explain any assumptions you have made when analysing, designing and implementing the above database, justify the approach you have taken and explain any alternative approaches you could have taken to any of the above tasks. Discuss any changes you would make to improve your work.

5.1 Assumptions

5.1.1 Task 1

In Task 1, when analysing the ER Model, it is assumed that Petcare has 10 entities and 1 link entities. The 10 entities are animal, type, breed, owner, appointment, prescription, treatment, drug and doctor. And linked entities are branch and Doctor_Specilisation. All the animals have animal breed and all breeds have Type. All animals have one owner but all owners must have at least one animal, otherwise it does not need to make appointment at Petcare. Animals can make one or more appointments but an appointment has only one animal. If there is an appointment, then it must have a doctor who is appointed for but doctors may have or may not have one or more appointment. The relationship of 'Appointment' and 'Prescription' is One-to-One relationship as shown in Fig (5.1). According to theory, these entities can be combined to one table. The reason why separating to these tables is that to increase performance of database (for easy access). Each Doctor can work at one branch only but each branch has one or more doctor. And the prescription have one or more treatments, and one treatments have one or more drugs type but drugs type may or may not have a treatment. Each veterinary doctor specialised in the treatment of one or more type of animal. In 1.2.5 Appointment (Entity Description), attributes 'Time' and 'Date' can be used in one attribute as 'Date/Time'. Using 'Date/Time' as one attribute saves disk space but separating two attributes ('Time' and 'Date') increases user understanding than the first one. These are the assumptions made in Task 1.

Fig (5.1) Appointment/Prescription Relationship

5.1.2 Task 2

When developing the above database system in Task 2, there are four steps to gain the normalised table: unnormalised form (collecting data), 1st normal form, 2nd normal form and 3rd normal form. To get unnormalised data, data are collected form the scenario given by the assignment. From this unnormalised data, 1st normal is achieved. There are repeating column in the unnormalised data. 1st Normal Form requires that at each row-and-column intersection, there must be one and only one value and that value must be atomic. There can be no repeating groups in a table that satisfies first normal form. It means that to remove repeating column. The second normalization rule applies only in tables whose primary keys consist of multiple columns. To get 2nd Normal Form, it requires making sure that all the non-primary key columns relate to the entire primary key and not just to one of its components. Otherwise, the columns that give information about the subject but do not uniquely define it. In the 3rd Normal Form, each non-key column must be a fact about the primary key column. In this way, a fully normalised table is achieved.

5.1.3 Task 3

In Task 3, when choosing DBMS, MySQL is a reasonable. There are several reasons why MySQL is a reasonable one. Firstly, it is an open source software and extensible, open storage database engine, offering multiple variations such as Berkeley DB, InnoDB, Heap and MyISAM. It integrates with a number of programming language and web base technologies. Secondly, when considering about licensing, it has no cost as long as the associated projects are also open-source. Thirdly, in the way of performance, it is a stable DBMS and uses little disk space, memory, and CUP. Finally, although it does not support the full features of relational database, it has other great advantages described above. Moreover, when comparing with other DBMS like MS SQL, Oracle etc. MS SQL is Proprietary. With this Microsoft product, it is limited to a Sybase-derived engine through both the good and bad times. MS SQL in the way of compatibility, as the SQL Server is known to work better with other Microsoft products only. When considering how MySQL integrates seamlessly with a number of programming languages and other web-based technologies, it certainly has the advantage over MS SQL in the way of compatibility, as the SQL Server is known to work better with other Microsoft products. And for Oracle, first, OPS is very complex. This complexity does bring high availability and it can bring higher performance. But it is not an easy thing to set up and administer OPS. Second, OPS has a problem known as "disk pinging." Thus, MySQL will not be a perfect one, but it is a reasonable one to develop the database for Petcare.

When developing the above database, all the attributes that are of String type is defined as varchar (variable character) - a character string of minimum length 1 and maximum length determined by the system. When a string less than the specified length (N) is input, only the actual length is stored to save the disk space. And for attributes that are of Date/time type is defined separately such Date and Time to get a better user understanding. For other attributes like cost and charges are defined as Decimal values because it may have fraction value when calculating cash.

5.1.4 Task 4

For Task 4.1, firstly, it is joined with Branch, Doctor and Doctor_Specialisation tables. Secondly, it intersects Doctor Table and Doctor_Specialisation with DoctorID, Doctor_Specialisation table and Type Table with TypeID, and Doctor and Branch with BranchID. Finally, it is ordered by BranchName to get a better result. For Task 4.2, it is similar with Task 4.2. Firstly, it is joined with Appointment, Branch, Doctor, Animal, Type and Breed tables. Secondly, it intersects Appointment Table and Doctor Table with DoctorID, Doctor Table and Branch Table with BranchID, Appointment Table and Animal Table with AnimalID, Animal Table and Breed Table with BreedID, and Breed Table and Type Table with TypeID. Finally, it is ordered by Appointment Date.

Joins are based on the relational operator product, a direct analogue of an operator in set theory known as the Cartesian product. Product takes two relations as input and produces as output one relation composed of all the possible combinations of input rows. A natural join is designed to produce a result from two input relations R and S of only those rows in R that have matching rows in S, and vice versa. Therefore, to get a better result table, natural join is used.

Intersection is fundamentally the opposite of union. Whereas union produces the combination of two sets or tables, intersection produces a result table which contains rows common to both input tables.

5.2 Changes that would make to improve this Assignment

To improve this assignment (work), doctors should work at one or more branch at Petcare. And Doctor should specialised only one type of animal. If they specialised only type of animal then they can polish their skills. In this way, Petcare can improve its work.