Veterinary Surgery Database Development Computer Science

This assignment is for the Database Design and Development faculty. In this assignment, we will hold to implement informations modeling, informations analysis and informations design techniques to plan and develop a database. First, we need to look into and seek to understand the scenario given. Harmonizing to the given scenario, we will hold to plan a database for the Petcare veterinary surgery. Petcare is a medium sized veterinary surgery with six subdivisions across London. It is given that the Petcare holds records of the animate beings they care for, prescriptions and assignments.

Each animate being has a peculiar proprietor. Each physician has specialized in their Fieldss of animate being.

There are wholly five undertakings in this assignment. The undertakings require us to use informations analysis and design methods to carry through them. We will hold to utilize such methods as entity modeling and standardization techniques. After the information has been analysed and designed, we will necessitate to put up the database and write SQL questions to recover informations from the database.

In Task 1, we are required to pull an entity relationship informations theoretical account which describes the content and construction of the informations stored by Petcare.

Undertaking 2 is the undertaking in which we will hold to bring forth the ensuing normalised tabular arraies together with their primary keys and foreign keys.

Harmonizing to Task 3, we need to put up the normalised tabular arraies from Task 2 utilizing a Database Management System ( DBMS ) and dwell the tabular arraies with trial informations. There need to be at least 5 records per tabular array. Then, we will hold to capture screen shootings of the tabular arraies we have set up.

After puting up the tabular arraies, Task 4 asks us to put up and trial questions utilizing Structured Query Language ( SQL ) to retreive certain records from the tabular arraies. We will hold to compose SQL codification for the undermentioned questions:

Display the names and references of the subdivisions of PetCare and the names of all the veterinary physicians working at each of the subdivisions. Any specialism ( s ) of the veterinary physicians should besides be shown.

Display all the assignments for the whole of the PetCare organisation. This should be ordered by day of the month. The consequence should expose the subdivision the assignment is at, the name of the veterinary physician the assignment is at, the name of the veterinary physician the assignment is with, the day of the month and clip of the assignment, the name of the animate being the assignment is for, the type of animate being and the strain of the animate being.

Then, we will hold to supply screen shootings of the ensuing end product produced when the questions are run in the database.

Undertaking 5 is the undertaking in which we will hold to explicate premises we have made during the class of the assignment analysing, planing and implementing the database, provide grounds as to why we have chosen the attack we have taken and explicate other alternate attacks we could hold taken to transport out the above undertakings from 1 to 4. We will besides necessitate to discourse alterations we could do to better our work.

Table of Contentss

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

animate being )

Remark: Description

Primary Key:

Cardinality Ratio

Owner Animals ( 1: Meter )

Owner Appointment ( 1: Meter )

Animal Appointment ( 1: Meter )

Doctor Appointment ( 1: Meter )

Animal Doctor ( 1: Meter )

Doctor Prescription ( 1: Meter )

Branch Doctor ( 1: Meter )

Entity with premise

The relationship for these entities is One-to-Many. An animate being has one proprietor and an proprietor can compose more than one animate beings.

The relationship for these entities is One-to-Many. An proprietor can compose more than one assignment and an assignment has one proprietor.

The relationship for these entities is One-to-Many. An animate being has more than one assignment and an assignment has one animate being.

Doctor and Appointment are related with One-to-Many relationship. A physician can compose more than one assignment and an assignment has one physician.

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

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

Content and construction of the informations by Pet attention

Entity Name: Animal

Primary Key: Animal ID

Foreign Key: Owner ID

Field Name

Data Type

Size

Description

Animal ID

Animal Name

Type

Breed

Sexual activity

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 animate being ID

Name of Animal

Type of animate being

Breed of animate being

Male or Female

Age of Animal

Auto no of proprietor 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 assignment ID

Auto no of proprietor ID

Auto no of physician ID

Auto no of animate being ID

Date & A ; Time of assignment

Diagnosis of assignment

Charges of assignment

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

Time 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 assignment ID

Type of drug

Timess of drug used to take

Sum 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 physician specialise

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

Time 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

Time 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

Time period

Cost

Data Model

Prescription

Owner

Appointment

Doctor

Prescription Detail

Animal

Table Design View and Datasheet View

Undertaking Table Design View

Datasheet View

Undertaking Table Design View

Datasheet View

Undertaking Table Design View

Datasheet View

Undertaking Table Design View

Datasheet View

Undertaking Table Design View

Datasheet View

Undertaking Table Design View

Datasheet View

Set-up and test all of the questions utilizing Structured Query Language ( SQL ) . Provide printouts of SQL codification for each question and the end product produced when you run the question in the database you have developed.

Display the names and references of the subdivisions of Pet Care and the names of all the veterinary physicians working at each of the subdivisions. Any specialism ( s ) of the veterinary physicians should besides be shown.

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

FROM Branch B, physician vitamin D

WHERE b.BranchID = d.BranchID

Order BY b.Name

Display all the assignments for the whole of the Pet Care organisation. This should be ordered by day of the month. The consequence should expose the subdivision the assignment is at, the name of the veterinary physician the assignment is at, the name of the veterinary physician the assignment is with, the day of the month and clip of the assignment, the name of the animate being the assignment is for, the type of animate being and the strain of the animate being.

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 vitamin 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 Premise

Undertaking 1 is the undertaking where we have to pull an entity relationship informations theoretical account for the Petcare. Therefore, to transport out Task 1, I have to first read the scenario given and seek to derive an apprehension 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 properties of the entities. When I have carried out the analysis of the informations held by Petcare, I have got the undermentioned entities: Animal, Owner, Appointment, Branch, Prescription and Doctor.

The relationship for Owner and Animal entities is One-to-Many. An animate being has one proprietor and an proprietor can compose more than one animate beings. The relationship for Owner and Appointment is One-to-Many. An proprietor can compose more than one assignment and an assignment has one proprietor. The relationship for Animal and Appointment is One-to-Many. An animate being has more than one assignment and an assignment has one animate being. Doctor and Appointment are related with One-to-Many relationship. A physician can compose more than one assignment and an assignment has one physician. Doctor and Prescription are related with One-to-Many relationship. A physician can compose more than one prescription and a prescription has one physician. Branch and Doctor are related with One-to-Many relationship. A subdivision has more than one physician and a physician has one subdivision.

I have made the premise that an proprietor can hold more than one animate being and each animate being has more than one assignment. So, proprietor can do one or more assignment. An assignment has a one animate being and physician has many assignments. Animal has more than one physician which that means physician are specialized in their field of animate being. Doctor makes a prescription for one animate being at a clip. So, physician has many prescriptions. . Petcare is a medium sized veterinary surgery with six subdivisions across London. So, there are more than one physician in each subdivision.

After placing the entities, I determined properties for them in Task 1. The entities I have produced in Task 1 will go tabular arraies in the database. When transporting out Task 2, I have to place the primary keys and foreign keys for the tabular arraies produced. Since the primary key is alone in a tabular array, I decided to hold Animal ID as primary key in Animal tabular array. Similarly, Appointment ID as primary key in Appointment tabular array, Prescription ID in Prescription tabular array, Doctor ID in Doctor Table, Branch ID in Branch tabular array, Owner ID in Owner tabular array. Owner tabular array and Animal tabular array are related with one-to-many relationship. In add-on, 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 tabular arraies and their Fieldss and the types and sizes of the Fieldss.

Since requires us to normalise the tabular arraies produced in Task 1, I have considered the signifier design of the signifiers 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 signifiers which focuses on these entities to transport out standardization. The signifiers I have used are Branch Form, Appointment Form and Prescription Form. Branch Form is the signifier which shows the item of Branch, Doctor who are specialized in what animate being. Appointment Form is the signifier which describes the item of Owner, Doctor and Animal. Prescription Form is the signifier which shows the item of Prescription gives to which animate being, given by whom, which drug are given for how many periods and how much it cost. The information theoretical accounts produced from normalizing the signifiers is the same as that of the ERD theoretical account in Task 1 when they are combined. If we carry out Task 1 foremost, there is a chance that we may happen that the normalised informations theoretical account and the ERD do non fit.

I decided to used Microsoft SQL Server 2005 to put up the database for the Petcare since I am familiar with this DBMS. I created tabular arraies in the database and inserted trial informations in the database. When dwelling the database with the trial information, I inserted meaningful informations since it would be easier to see and understand the database. I defined the field names, informations types, sizes and formats of the Fieldss of the tabular arraies in the design position. Since the undertaking requires that there should at least be 5 records in each tabular array, I inserted a lower limit of 5 records in the tabular array. I defined primary keys and foreign keys in the tabular array. After that, I created a relationship diagram demoing the relationships between the tabular arraies utilizing primary keys and foreign keys. In Task 3, I have presented the tabular arraies I have created both in design position and datasheet position. An alternate attack in puting up the database will be to utilize Microsoft SQL Server or other DBMS of pick.

When making the question exposing all six subdivisions names and references physician in each subdivision and their specialism in what animate being. Then I created another question demoing Branch Name, Address, and Appointment Date/Time, Doctor Name, Animal Name, Animal Type and Breed.

In decision, these are the premises I have made when analyzing, planing and implementing the database and the attacks I have taken.