Entity relationship data model of Petcare

Draw an Entity Relationship Data Model that describes the content and construction of the informations held by Petcare. Stipulate the cardinality ratio and engagement restraint of each relationship type.

Entity Relationship Diagram

Owner

Animal

R1 R2

Appointment

R3 R4

Doctor

Prescription

R5

R6 R7

Drug

Branch

Specialism

Fig. 1

The Relationship between the Entities ( Fig. 1 )

R1: An proprietor has at least one Appointment.

An assignment is at most held by an proprietor.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

R2: An animate being has at least one Appointment

An assignment is at most held by an animate being.

P.S. By presuming one proprietor can hold more than one animate being, and one animate being can hold more the one proprietor. There is a many-to-many relationship between proprietor and animate being, so we resolve the relationship in to two relationships R1 and R2.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

R3: A physician has many assignments.

An assignment at most held by a physician.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

R4: One Prescription can enter many types of drug.

One type of drug at most is recorded in one Prescription.

R5: A physician has forte in much type of animate beings.

A intervention of a type of animate being is at most specialising by a physician.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

R6: A subdivision has many physicians.

A physician at most work in a subdivision

P.S. Some Doctors may fall in the organisation, but still have n’t been allocated in a Branch impermanent.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

R7: One type of drug has at most one item of drug.

One type of drug can be record in many Prescriptions.

The descriptions about the content of the Entity

The Entity contents the information of the Owners

This tabular array contains the information of the proprietor of pets. There are five properties in the tabular array, ownerID is primary key of the tabular array. The descriptions are shown in the undermentioned Fig.2.

ID card figure of the pet ‘s proprietor

Name of the pet ‘s proprietor

Owner

Address of the pet ‘s proprietor

Home Telephone figure of the pet ‘s proprietor

Mobile phone figure of the pet ‘s proprietor

Name of the Entity

Fig.2

The Entity contents the information of the Animals

This tabular array contains the information of the animate beings. There are six properties in the tabular array, animalNo is primary key of the tabular array. The descriptions are shown in the undermentioned Fig.3.

The Number for designation gave by Petcare

Name of animate beings

Animal

Type of the pets

Breed of the pets

Sexual activity of the pets

Age of the pets

Name of the Entity

Fig. 3

The Entity contents the information of the Appointments

This tabular array contains the information of the assignments. There are seven properties in the tabular array, appointmentNo is primary key of the tabular array, doctorName, ownerID and animalNo are the foreign keys for mentioning to the tabular arraies Doctor, Owner and Animal severally. The descriptions are shown in the undermentioned Fig.4.

Appointment Number gave by Petcare

The Doctor of the assignment

Appointment

Date and Time of assignment

Diagnosis of the pets

Charge of the client need to pay

Name of the Entity ID card figure of the pet ‘s proprietor

The Number of the pets gave by Petcare

Fig. 4

The Entity contents the information of the Prescription

This tabular array contains the information of the prescriptions. There are four properties in the tabular array, prescriptionID is primary key of the tabular array, it is non merely an designation figure for the tabular array. And appointmentNo is the foreign key for mentioning to the table Appointment. The descriptions are shown in the undermentioned Fig.5.

Prescription

The consecutive figure of the tabular array

The figure of the Appointment

Name of drugs

Name of the Entity Pieces of the drugs took by the client

Fig. 5

The Entity contents the item of the drugs

This tabular array contains the information of the drugs. There are three properties in the tabular array, drugName is primary key of the tabular array. The descriptions are shown in the undermentioned Fig.6.

Name of the Entity

The name of drugs

The period, in yearss, that

the drugs must be taken

Cost of the drugs per piece

Fig. 6

The Entity contents the item of the physicians

This tabular array contains the information of the physicians. There are five properties in the tabular array, doctorName is primary key of the tabular array, branchName is the foreign key for mentioning to the tabular array Branch. The descriptions are shown in the undermentioned Fig.7.

Name of physicians

Doctor

Address of physicians

Home telephone figure of physicians

Mobile telephone figure of physicians

Which subdivision is the physician in

Name of the Entity

Fig. 7

The Entity contents the item of the subdivision

This tabular array contains the information of the physicians. There are five properties in the tabular array, doctorName is primary key of the tabular array. The descriptions are shown in the undermentioned Fig.8.

Name of subdivisions

Branch

Address of subdivisions

Telephone figure of subdivisions

Opening hours of subdivisions

Emergency telephone figure of subdivisions

Name of the Entity

Fig. 8

The Entity contents the item of the Specialty File

This tabular array contains the information of the specialism of physicians. There are three properties in the tabular array, specialismNo is primary key of the tabular array, doctorName is the foreign key for mentioning to the tabular array Doctor. The descriptions are shown in the undermentioned Fig.9.

Data Number of the specialism of the physicians

Name of physicians

specialism

Type of fortes of physicians

Name of the Entity

Fig. 9

Undertaking 2 – 20 Marks

Produce the ensuing normalised tabular arraies clearly bespeaking the primary and foreign keys.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Definition of Standardization

What is Normalisation?

Adjustment procedure of the informations tabular array construction, the information in an appropriate manner the tabular array is divided into a figure of informations Information Sheet.

Standardization intents:

1 ) Data tabular array to cut down extra informations issues or conflicting instance information

2 ) Query the database to better velocity

3 ) To manage more informations with efficiency and easier care

What is Normal Form?

There are three chief normal signifiers, each with increasing degrees of standardization:

First Normal Form

A relation is in First Normal Form if, and merely if, every non-key property is functionally dependent upon primary key.

That means No Repeating Elementss or Groups of Elements.

Second Normal Form

A relation is in Second Normal Form if, and merely if, it is in First Normal Form and every non-key property is to the full functionally dependent on the primary key.

That means No Partial Dependences on a Concatenated Key.

Third Normal Form

A relation is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

That means No Dependences on Non-Key Attributes.

Normalistion and Data Definition of each tabular array

Table – Owner

Owner

ownerID

ownerName

ownerAddr

ownerTel

ownerMobile

ownerID: CHARATER ( 8 )

ownerName: VARCHAR ( 15 ) NOT NULL

ownerAddr: VARCHAR ( 50 ) NOT NULL

ownerTel: INTEGER NOT NULL

ownerMobile: Integer

Primary Key: ownerID

By sing the status of First Normal Form, there are no reiterating property in Table – Owner. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Owner are besides being meaningless, when they are non puting with the primary cardinal ownerID in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Owner is in Second Normal Form.

All of the properties in Table – Owner are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Animal

Animal

animalNo

animalName

type

strain

sex

age

animalNo: CHARATER ( 6 )

animalName: VARCHAR ( 20 ) NOT NULL

type: VARCHAR ( 10 ) NOT NULL

strain: VARCHAR ( 15 )

sex: CHARATER ( 1 ) NOT NULL

age: Integer

Primary Key: animalNo

By sing the status of First Normal Form, there are no reiterating property in Table – Animal. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Animal are besides being meaningless, when they are non puting with the primary key animalID in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Animal is in Second Normal Form.

All of the properties in Table – Animal are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Branch

Branch

branchName

branchAddr

branchTel

openHour

emergencyTel

branchName: VARCHAR ( 10 )

branchAddr: VARCHAR ( 50 ) NOT NULL

branchTel: INTEGER NOT NULL

openhour: VARCHAR ( 13 ) NOT NULL

emergencyTel: INTEGER NOT NULL

Primary Key: branchName

By sing the status of First Normal Form, there are no reiterating property in Table – Branch. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Branch are besides being meaningless, when they are non puting with the primary cardinal branchName in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Branch is in Second Normal Form.

All of the properties in Table – Branch are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Doctor

Doctor

doctorName

doctorAddr

doctorTel

doctorMobile

branchName

doctorName: VARCHAR ( 20 )

doctorAddr: VARCHAR ( 50 ) NOT NULL

doctorTel: INTEGER NOT NULL

doctorMobile: INTEGER NOT NULL

branchName: VARCHAR ( 10 )

Primary Key: doctorName

Foreign Key: branchName REFERENCES Branch

By sing the status of First Normal Form, there are no reiterating property in Table – Doctor. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Doctor are besides being meaningless, when they are non puting with the primary cardinal doctorName in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Doctor is in Second Normal Form.

All of the properties in Table – Doctor are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Appointment

Appointment

appointmentNo

doctorName

dateAndTime

diagnosing

charge

ownerID

animalNo

appointmentNo: CHARATER ( 6 )

doctorName: VARCHAR ( 20 ) NOT NULL

dateAndTime: DATETIME NOT NULL

diagnosing: VARCHAR ( 30 )

charge: Double

ownerID: CHARATER ( 8 )

animalNo: CHARATER ( 6 )

Primary Key: appointmentNo

Foreign Key: doctorName REFERENCES Doctor

ownerID REFERENCES Owner

animalNo REFERENCES Animal

By sing the status of First Normal Form, there are no reiterating property in Table – Appointment. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Appointment are besides being meaningless, when they are non puting with the primary cardinal appointmentNo in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Appointment is in Second Normal Form.

All of the properties in Table – Appointment are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Drug

Drug

drugName

period

cost

drugName: VARCHAR ( 30 )

period: VARCHAR ( 15 ) NOT NULL

cost: INTEGER NOT NULL

Primary Key: drugName

By sing the status of First Normal Form, there are no reiterating property in Table – Drug. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Drug are besides being meaningless, when they are non puting with the primary cardinal drugName in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Drug is in Second Normal Form.

All of the properties in Table – Drug are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Prescription

Prescription

prescriptionID

appointmentNo

drugName

noOfPiece

prescriptionNo: Integer

appointmentNo: CHARATER ( 6 ) NOT NULL

drugName: VARCHAR ( 20 ) NOT NULL

noOfPiece: INTEGER NOT NULL

Primary Key: prescriptionID

Foreign Key: appointmentNo REFERENCES Appointment

drugName REFERENCES Drug

By sing the status of First Normal Form, there are no reiterating property in Table – Prescription. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Prescription are besides being meaningless, when they are non puting with the primary cardinal prescriptionID in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Prescription is in Second Normal Form.

All of the properties in Table – Prescription are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

Table – Specialism

Specialism

specialismNo

doctorName

type

specialismNo: Integer

doctorName: VARCHAR ( 20 ) NOT NULL

type: VARCHAR ( 10 ) NOT NULL

Primary Key: specialismNo

Foreign Key: doctorName REFERENCES Doctor

By sing the status of First Normal Form, there are no reiterating property in Table – Specialism. So it is in First Normal Form.

And we consider the property whether is to the full functionally dependent on the primary key. All properties in Table – Specialism are besides being meaningless, when they are non puting with the primary cardinal specialismNo in the same tabular array. That means all properties are to the full functionally dependent on the primary key. So Table – Specialism is in Second Normal Form.

All of the properties in Table – Specialism are no dependences on Non-Key Attributes. So the tabular array above is in Third Normal Form if, and merely if, it is in Second Normal Form and every non-key property is non-transitively dependant on the primary key.

The consequence of Standardization

Owner

ownerID

ownerName

ownerAddr

ownerTel

ownerMobile

Animal

animalNo

animalName

type

strain

sex

age

Appointment

appointmentNo

doctorName

dateAndTime

diagnosing

charge

ownerID

animalNo

Prescription

prescriptionID

appointmentNo

drugName

noOfPiece

Drug

drugName

period

cost

Doctor

doctorName

doctorAddr

doctorTel

doctorMobile

branchName

Branch

branchName

branchAddr

branchTel

openHour

emergencyTel

Fig. 10

Specialism

specialismNo

doctorName

type

Undertaking 3 – 20 Marks

Using a Database Management System ( DBMS ) of your pick, set-up all of the above normalised tabular arraies, and dwell them with well-designed trial informations ( minimal 5 records per tabular array ) . Provide printouts of all tabular arraies.

Reasonable premises may be made with respect to informations.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

The pick of DBMS

We have pick Microsoft Access 2007 to be the DBMS for the database of Petcare. And we will make the Database Schema and the Data by utilizing SQL.

The order of making the Tables

The tabular arraies need to be created in order, because the tabular arraies have relationship. If non, the tabular arraies can non be created in Microsoft Access 2007. The order of making tabular arraies is following:

Owner

Animal

Branch

Doctor

Appointment

Drug

Prescription

Specialism

The Table created in Microsoft Access 2007 by utilizing SQL

Create Table – Owner

CREATE TABLE Owner

( ownerID CHAR ( 8 ) PRIMARY KEY,

ownerName VARCHAR ( 15 ) NOT NULL,

ownerAddr VARCHAR ( 50 ) NOT NULL,

ownerTel INT NOT NULL,

ownerMobile INT ) ;

Insert Data – Owner

INSERT INTO Owner VALUES ( ‘a1122333 ‘ , ‘Apple ‘ , ’23 Happy Road, London ‘ , 2332233, NULL ) ;

INSERT INTO Owner VALUES ( ‘b2233444 ‘ , ‘Billy ‘ , ’12 Unhappy Road, London ‘ , 44554455, 66776677 ) ;

INSERT INTO Owner VALUES ( ‘c3344555 ‘ , ‘Carol ‘ , ‘Flat 2, So Sad Building, Sad Road, London ‘ , 33443344, 85566665 ) ;

INSERT INTO Owner VALUES ( ‘d4455666 ‘ , ‘Daniel ‘ , ‘Flat 12, Oh No Building, Oh My God Road, London ‘ , 44556665, NULL ) ;

INSERT INTO Owner VALUES ( ‘e5566777 ‘ , ‘Evans ‘ , ’60 So Good Road, London ‘ , 34534534, 99935445 ) ;

Premise: 1 ) The ID card of pet ‘s proprietor is length of 8 words.

2 ) Some proprietors have n’t mobile telephone figure.

Fig. 11

Create Table – Animal

CREATE TABLE Animal

( animalNo CHAR ( 6 ) PRIMARY KEY,

animalName VARCHAR ( 20 ) NOT NULL,

type VARCHAR ( 10 ) NOT NULL,

strain VARCHAR ( 15 ) ,

sex CHAR ( 1 ) NOT NULL,

age INT ) ;

Insert Data – Animal

INSERT INTO Animal VALUES ( ‘a00001 ‘ , ‘Good Dog ‘ , ‘Dog ‘ , ‘Terrier ‘ , ‘M ‘ , 3 ) ;

INSERT INTO Animal VALUES ( ‘a00002 ‘ , ‘Good Cat ‘ , ‘Cat ‘ , ‘Persian ‘ , ‘F ‘ , 1 ) ;

INSERT INTO Animal VALUES ( ‘a00003 ‘ , ‘Good Rabbit ‘ , ‘Rabbit ‘ , ‘Dwarf ‘ , ‘F ‘ , 1 ) ;

INSERT INTO Animal VALUES ( ‘a00004 ‘ , ‘Bad Rabbit ‘ , ‘Rabbit ‘ , ‘English Spot ‘ , ‘M ‘ , 2 ) ;

INSERT INTO Animal VALUES ( ‘a00005 ‘ , ‘Bad Dog ‘ , ‘Dog ‘ , ‘Poodle ‘ , ‘M ‘ , 3 ) ;

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Fig. 12

Create Table – Branch

CREATE TABLE Branch

( branchName VARCHAR ( 10 ) PRIMARY KEY,

branchAddr VARCHAR ( 50 ) NOT NULL,

branchTel INT NOT NULL,

openhour VARCHAR ( 13 ) NOT NULL,

emergencyTel INT NOT NULL ) ;

Insert Data – Branch

INSERT INTO Branch VALUES ( ‘Chingford ‘ , ’23 Double Road, London ‘ , 99988821, ’09:00 – 18:30 ‘ , 88877343 ) ;

INSERT INTO Branch VALUES ( ‘Enfield ‘ , ‘1 Good Road, London ‘ , 99988899, ’09:00 – 19:00 ‘ , 88877677 ) ;

INSERT INTO Branch VALUES ( ‘Hackney ‘ , ’23 Bad Road, London ‘ , 99988800, ’09:00 – 19:00 ‘ , 88877644 ) ;

INSERT INTO Branch VALUES ( ‘Holloway ‘ , ’43, 1/F, God Building, 23 Smart Road, London ‘ , 99988833, ’09:00 – 19:00 ‘ , 88877661 ) ;

INSERT INTO Branch VALUES ( ‘Islington ‘ , ’34 Unhappy Road, London ‘ , 99988890, ’09:00 – 18:30 ‘ , 88877688 ) ;

INSERT INTO Branch VALUES ( ‘Leyton ‘ , ’50 Mouse Road, London ‘ , 99922332, ’09:00 – 18:30 ‘ , 88833221 ) ;

Fig. 13

Create Table – Doctor

CREATE TABLE Doctor

( doctorName VARCHAR ( 20 ) PRIMARY KEY,

doctorAddr VARCHAR ( 50 ) NOT NULL,

doctorTel INT NOT NULL,

doctorMobile INT NOT NULL,

branchName VARCHAR ( 10 ) ,

FOREIGN KEY ( branchName )

REFERENCES Branch ( BranchName )

ON DELETE CASCADE

ON UPDATE CASCADE ) ;

Insert Data – Doctor

INSERT INTO Doctor VALUES ( ‘Howard ‘ , ‘Flat 10, Doctor Building, Doctor Road, London ‘ , 55544422, 98259825, ‘Enfield ‘ ) ;

INSERT INTO Doctor VALUES ( ‘Ivan ‘ , ’45 God Road, London ‘ , 66633442, 74648394, ‘Hackney ‘ ) ;

INSERT INTO Doctor VALUES ( ‘Mary ‘ , ’60 UnHappy Road, London ‘ , 33344554, 67454456, ‘Leyton ‘ ) ;

INSERT INTO Doctor VALUES ( ‘Nicole ‘ , ‘Flat 24, Music Building, Music Road, London ‘ , 44433344, 74433565, ‘Islington ‘ ) ;

INSERT INTO Doctor VALUES ( ‘Peter ‘ , ‘2 Good Road, London ‘ , 22223333, 55643565, ‘Enfield ‘ ) ;

INSERT INTO Doctor VALUES ( ‘Ken ‘ , ’67 Mother Road, London ‘ , 22224444, 9579765, ‘Holloway ‘ ) ;

Premise: More one physician can work in a subdivision.

Fig. 14

Create Table – Appointment

CREATE TABLE Appointment

( appointmentNo CHAR ( 6 ) PRIMARY KEY,

doctorName VARCHAR ( 20 ) NOT NULL,

dateAndTime DATETIME NOT NULL,

diagnosing VARCHAR ( 30 ) ,

charge DOUBLE,

ownerID CHAR ( 8 ) NOT NULL,

animalNo CHAR ( 6 ) NOT NULL,

FOREIGN KEY ( doctorName )

REFERENCES Doctor ( doctorName )

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY ( ownerID )

REFERENCES Owner ( ownerID )

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY ( animalNo )

REFERENCES Animal ( animalNo )

ON DELETE CASCADE

ON UPDATE CASCADE ) ;

Insert Data – Appointment

INSERT INTO Appointment VALUES ( ‘ap0001 ‘ , ‘Peter ‘ , ’22/9/2010 11:00:00 ‘ , ‘Foot hurt ‘ , 300, ‘a1122333 ‘ , ‘a00001 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0002 ‘ , ‘Howard ‘ , ’22/9/2010 15:00:00 ‘ , ‘Head hurt ‘ , 350, ‘b2233444 ‘ , ‘a00002 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0003 ‘ , ‘Mary ‘ , ’23/9/2010 10:00:00 ‘ , ‘Foot hurt ‘ , 300, ‘c3344555 ‘ , ‘a00003 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0004 ‘ , ‘Ivan ‘ , ’23/9/2010 14:00:00 ‘ , ‘Sterilization ‘ , 2100, ‘d4455666 ‘ , ‘a00004 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0005 ‘ , ‘Ivan ‘ , ’24/9/2010 10:00:00 ‘ , ‘Sterilization ‘ , NULL, ‘e5566777 ‘ , ‘a00004 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0006 ‘ , ‘Nicole ‘ , ’27/9/2010 14:30:00 ‘ , ‘Inflammation ‘ , NULL, ‘a1122333 ‘ , ‘a00005 ‘ ) ;

INSERT INTO Appointment VALUES ( ‘ap0007 ‘ , ‘Nicole ‘ , ’29/9/2010 10:00:00 ‘ , ‘Inflammation ‘ , NULL, ‘b2233444 ‘ , ‘a00002 ‘ ) ;

Premise: 1 ) The charges can be NULL for some uncomplete assignments.

2 ) One physician can in charge of more than one assignment.

Fig. 15

Create Table – Drug

CREATE TABLE Drug

( drugName VARCHAR ( 30 ) PRIMARY KEY,

period VARCHAR ( 20 ) NOT NULL,

cost INT NOT NULL ) ;

Insert Data – Drug

INSERT INTO Drug VALUES ( ‘Anti-biotic ‘ , ‘1 piece per 4 hours ‘ , 5 ) ;

INSERT INTO Drug VALUES ( ‘Behaviour alteration ‘ , ‘1 piece per 6 hours ‘ , 5 ) ;

INSERT INTO Drug VALUES ( ‘Ear medicine ‘ , ‘1 piece per 4 hours ‘ , 10 ) ;

INSERT INTO Drug VALUES ( ‘Painkiller ‘ , ‘1 piece per 4 hours ‘ , 4 ) ;

INSERT INTO Drug VALUES ( ‘Skin medicine ‘ , ‘1 piece per 6 hours ‘ , 8 ) ;

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Fig. 16

Create Table – Prescription

CREATE TABLE Prescription

( prescriptionID INT PRIMARY KEY,

appointmentNo CHAR ( 6 ) NOT NULL,

drugName VARCHAR ( 20 ) NOT NULL,

noOfPiece INT NOT NULL,

FOREIGN KEY ( appointmentNo )

REFERENCES Appointment ( appointmentNo )

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY ( drugName )

REFERENCES Drug ( drugName )

ON DELETE CASCADE

ON UPDATE CASCADE ) ;

Insert Data – Prescription

INSERT INTO Prescription VALUES ( 1, ‘ap0001 ‘ , ‘Painkiller ‘ , 8 ) ;

INSERT INTO Prescription VALUES ( 2, ‘ap0002 ‘ , ‘Painkiller ‘ , 8 ) ;

INSERT INTO Prescription VALUES ( 3, ‘ap0004 ‘ , ‘Painkiller ‘ , 8 ) ;

INSERT INTO Prescription VALUES ( 4, ‘ap0006 ‘ , ‘Anti-biotic ‘ , 16 ) ;

INSERT INTO Prescription VALUES ( 5, ‘ap0007 ‘ , ‘Painkiller ‘ , 16 ) ;

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Premise: The proprietor may take more than one type of drugs after assignment.

Fig. 17

Create Table – Specialism

CREATE TABLE Specialism

( specialismNo INT PRIMARY KEY,

doctorName VARCHAR ( 20 ) NOT NULL,

type VARCHAR ( 10 ) NOT NULL,

FOREIGN KEY ( doctorName )

REFERENCES Doctor ( doctorName )

ON DELETE CASCADE

ON UPDATE CASCADE ) ;

Insert Data – Specialism

INSERT INTO Specialism VALUES ( 1, ‘Howard ‘ , ‘Dog ‘ ) ;

INSERT INTO Specialism VALUES ( 2, ‘Peter ‘ , ‘Cat ‘ ) ;

INSERT INTO Specialism VALUES ( 3, ‘Mary ‘ , ‘Rabbit ‘ ) ;

INSERT INTO Specialism VALUES ( 4, ‘Mary ‘ , ‘Dog ‘ ) ;

INSERT INTO Specialism VALUES ( 5, ‘Ivan ‘ , ‘Dog ‘ ) ;

INSERT INTO Specialism VALUES ( 6, ‘Ivan ‘ , ‘Cat ‘ ) ;

INSERT INTO Specialism VALUES ( 7, ‘Ivan ‘ , ‘Rabbit ‘ ) ;

INSERT INTO Specialism VALUES ( 8, ‘Nicole ‘ , ‘Dog ‘ ) ;

INSERT INTO specialism VALUES ( 9, ‘Ken ‘ , ‘Cat ‘ ) ;

Premise: One physician can hold more than one type specialism.

Fig. 18

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Consequence of the Relationship

Fig. 19

Undertaking 4 – 20 Marks

Set-up and test all of the undermentioned 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 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 administration. 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 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.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

The Result displayed in Access 2007 by utilizing SQL

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

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

We need to Join THREE tabular arraies to hold the consequence. The tabular arraies are Branch, Doctor and Specialism. The contents we needed to expose are Branch Name, Branch Address, Doctor Name and Specialism of Doctors.

The conditions we need to curtail are:

Branch.branchName = Doctor.branchName

Doctor.doctorName = Specialism.doctorName

By utilizing SQL in Access 2007, the consequences are shown undermentioned:

The SQL codification:

SELECT Branch.branchName, Branch.branchAddr, Doctor.doctorName, Specialism.type

FROM Branch, Doctor, Specialism

WHERE Branch.branchName = Doctor.branchName

AND Doctor.doctorName = Specialism.doctorName ;

Fig. 20

• Display all the assignments for the whole of the Petcare administration. 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 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.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

We need to Join THREE tabular arraies to hold the consequence. The tabular arraies are Branch, Doctor, Appointment, Registration and Animal. The contents we needed to expose are Branch Name, Doctor Name, Date and Time of the Appointment, Animal Name, Type of animate beings and Breed of animate beings.

The conditions we need to curtail are:

Branch.branchName = Doctor.branchName

Doctor.doctorName = Appointment.doctorName

Appointment.animalNo = Animal.animalNo

Order BY Appointment.dateAndTime in Ascending Sequence

By utilizing SQL in Access 2007, the consequences are ordered by informations and shown in following page:

The SQL codification:

SELECT B.branchName, Ap.doctorName, Ap.dateAndTime,

An.animalName, An.type, An.breed

FROM Branch B, Doctor D, Appointment Ap, Animal An

WHERE B.branchName = D.branchName

AND D.doctorName = Ap.doctorName

And Ap.animalNo = An.animalNo

Order BY Ap.dateAndTime ASC ;

Fig. 21

Undertaking 5 – 10 Marks

Explain any premises you have made when analyzing, planing and implementing the above database, warrant the attack you have taken and explicate any alternate attacks you could hold taken to any of the above undertakings. Discuss any alterations you would do to better your work.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –

Premises have made

The information gave by Petcare is accurate.

Petcare should do enrollment to the proprietors and their pets.

Explanations of premises

We have merely used our common sense to plan the Database Schema by standing on the point of view of Doctor, Client or Petcare. Because some elusive difference is merely known by the users. Their thought is the most of import information to the design.

The information gave by Petcare is accurate ; therefore we merely can plan a utile and specific Database Schema for Petcare. If non, it will impact the unity of the database and the value of the informations.

Because we have consider that some proprietors have more than one pet, and some pet is owned by more the one proprietor. If Petcare have make enrollment to them, it can set the information of their clients expeditiously and accurately. And our design can be forte to them.

Improve of our work:

If we can plan a specific interface by utilizing other programming linguistic communication, e.g. C++ , Java, that can better the efficiency of Create, Read, Update and Delete informations.

But this action needs to hold more clip for developing.

We can utilize other DBMS, e.g. MySQL, Oracle, to construct up the database of Petcare, that the users or decision makers entree the informations by cyberspace. That means they can entree the informations outside the Branch, such as Home. It is much convenient to all users or decision makers.

But it besides provided a security job. For illustration, the user forgot logout the system, and so the computing machine may be used by other individual.

After built up the database, we can give it the Petcare for proving. Then we can acquire the feedback from the users, that we can cognize some lack about the design accurately. It is of import to better our work.

But It besides needs to hold more clip to prove and the cooperation of the users.

Plagiarism Report

x

Hi!
I'm Larry

Hi there, would you like to get such a paper? How about receiving a customized one?

Check it out