PerfectPets is a practice that provides private health care for domestic pets throughout America. This service is provided through various clinics located in the main cities of America. The Director of Perfect Pets is concerned that there is a lack of communication between organizations and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralized database system to assist in the more effective and efficient running of the clinics. The relational schema for the PerfectPets database looks as follows:

1. Draw an ER diagram to illustrate the relationship among these tables.

perfectpets.sql Script (pasted)

rem +---------------------------------------------------------------------+

rem | PERFECTPETS.SQL |

rem | |

rem | Create tables for the Perfect Pets Database |

rem | |

rem | |

rem +---------------------------------------------------------------------+

rem

rem Create all the necessary tables.

rem

drop table appointment cascade constraints;

drop table invoice cascade constraints;

drop table pharmclinicstock cascade constraints;

drop table itemclinicstock cascade constraints;

drop table item cascade constraints;

drop table pharmacy cascade constraints;

drop table pettreatment cascade constraints;

drop table petpen cascade constraints;

drop table pen cascade constraints;

drop table treatment cascade constraints;

drop table examination cascade constraints;

drop table pet cascade constraints;

drop table petowner cascade constraints;

drop table staff cascade constraints;

drop table clinic cascade constraints;

CREATE TABLE Clinic(

clinicNo CHAR(5) NOT NULL,

street VARCHAR2(40) NOT NULL,

city VARCHAR2(15) NOT NULL,

state CHAR(2) NOT NULL,

zipCode VARCHAR2(9) NOT NULL UNIQUE,

telNo VARCHAR2(20) NOT NULL UNIQUE,

faxNo VARCHAR2(20) NOT NULL UNIQUE,

mgrStaffNo CHAR(4),

PRIMARY KEY (clinicNo)

);

INSERT INTO clinic values('c1111','1275 Garden Grove Blvd.','Garden Grove','CA','92345','(714) 923-9223','(714) 923-9224','s112');

INSERT INTO clinic values('c1112','20429 Yorba Linda Blvd.','Yorba Linda','CA','92845','(714) 996-7610','(714) 996-7611','s115');

INSERT INTO clinic values('c1113','15808 Imperial Hwy','Brea','CA','92635','(714) 283-5898','(714) 283-5899','s117');

INSERT INTO clinic values('c1114','15400 Computer Blvd.','Fullerton','CA','92834','(714) 278-5898','(714) 278-5899',null);

CREATE TABLE Staff(

staffNo CHAR(4) NOT NULL,

sFName VARCHAR2(30) NOT NULL,

sLName VARCHAR2(30) NOT NULL,

sStreet VARCHAR2(40) NOT NULL,

sCity VARCHAR2(15) NOT NULL,

sState CHAR(2) NOT NULL,

sZipCode VARCHAR2(9)NOT NULL,

sTelNo VARCHAR2(20) NOT NULL,

DOB DATE NOT NULL,

gender CHAR NOT NULL,

SSN VARCHAR2(12) NOT NULL UNIQUE,

position VARCHAR2(20) NOT NULL,

salary NUMBER(8,2) NOT NULL CHECK( salary > 0),

clinicNo CHAR(5) NOT NULL,

PRIMARY KEY (staffNo),

CONSTRAINT clinicnumber FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)

);

INSERT INTO staff VALUES('s111','David','Johnsson','1255 Garden Grove Blvd.','Garden Grove','CA','92345','(714) 223-2223',to_date('1-aug-1970','dd-mon-yyyy'),'M','111111111','clerk',40000,'c1111');

INSERT INTO staff VALUES('s112','Steve','John','1150 Fullerton St.','Fullerton','CA','92344','(714) 222-2113',to_date('1-jul-1960','dd-mon-yyyy'),'M','222112222','manager',50000,'c1111');

INSERT INTO staff VALUES('s113','Hess','Edward','12121 Beach Blvd.','Newport Beach','CA','99923','(996) 111-2221',to_date('1-aug-1959','dd-mon-yyyy'),'F','333113333','vet',60000,'c1112');

INSERT INTO staff VALUES('s114','Hester','Britten','8712 W Lambert Rd.','Brea','CA','92845','(714) 297-9827',to_date('1-aug-1979','dd-mon-yyyy'),'M','444114444','manager',45000,'c1112');

INSERT INTO staff VALUES('s115','Hoo','Don','3481 E Elm','Fullerton','CA','92811','(714) 113-1113',to_date('1-aug-1974','dd-mon-yyyy'),'F','555115555','manager',60000,'c1112');

INSERT INTO staff VALUES('s116','Hong','Soo','6511 W. Chapman','La Habra','CA','92732','(715) 333-7723',to_date('1-aug-1976','dd-mon-yyyy'),'M','666116666','clerk',41000,'c1113');

INSERT INTO staff VALUES('s117','Hill','Rowland','3411 Ahn Hill','Placentia','CA','92845','(714) 578-9137',to_date('1-aug-1971','dd-mon-yyyy'),'M','777117777','manager',48000,'c1113');

INSERT INTO staff VALUES('s118','Gomez','Rachel','2101 Victoria Dr.','Irvine','CA','92176','(949) 631-8888',to_date('1-aug-1969','dd-mon-yyyy'),'M','888118888','vet',70000,'c1113');

ALTER TABLE Clinic

ADD CONSTRAINT mgrstaffnumber FOREIGN KEY (mgrStaffNo) REFERENCES Staff(staffNo);

CREATE TABLE PetOwner(

ownerNo CHAR(5) NOT NULL,

oFName VARCHAR2(30) NOT NULL,

oLName VARCHAR2(30) NOT NULL,

oStreet VARCHAR2(40) NOT NULL,

oCity VARCHAR2(15) NOT NULL,

oState CHAR(2) NOT NULL,

oZipCode VARCHAR2(9),

oTelNo VARCHAR2(20) NOT NULL,

clinicNo CHAR(5) NOT NULL,

PRIMARY KEY (ownerNo),

CONSTRAINT clinicnumber1 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)

);

INSERT INTO PetOwner VALUES('o1111','Kevin','Lawrence','1262 Warren Place','Anaheim','CA','92345','(946) 921-1223','c1112');

INSERT INTO PetOwner VALUES('o1112','Gomez','Rachel','2101 Victoria Dr.','Irvine','CA','92176','(949) 631-8888','c1111');

INSERT INTO PetOwner VALUES('o1113','Charles','Lawrence','1728 W 5th SantaAna','La Palma','CA','92744','(714) 771-7878','c1111');

INSERT INTO PetOwner VALUES('o1114','Carlton','William','1212 Harbor Blvd.','Fullerton','CA','92834','(714) 279-2223','c1112');

INSERT INTO PetOwner VALUES('o1115','Bruce','Bender','201 W. La Habra Blvd.','Yorbalinda','CA','92877','(714) 566-5555','c1113');

CREATE TABLE Pet(

petNo CHAR(6) NOT NULL,

petName VARCHAR2(30) NOT NULL,

petType VARCHAR2(20) NOT NULL,

petDesc VARCHAR2(40) NOT NULL,

petDOB DATE NOT NULL,

dateRegistered DATE NOT NULL,

petStatus CHAR NOT NULL,

ownerNo CHAR(5),

clinicNo CHAR(5),

PRIMARY KEY (petNo),

CONSTRAINT ownernumber FOREIGN KEY (ownerNo) REFERENCES PetOwner(ownerNo),

CONSTRAINT clinicnumber2 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)

);

INSERT INTO Pet VALUES('pt1111','Lucky','Chihuahua','Friendly',to_date('30-nov-1994','dd-mon-yyyy'), to_date('10-aug-1995','dd-mon-yyyy'), 'Y','o1111','c1112');

INSERT INTO Pet VALUES('pt1122','Bell','Golden retriever','Active',to_date('4-mar-2001','dd-mon-yyyy'), to_date('2-may-2001','dd-mon-yyyy'), 'N',null,'c1111');

INSERT INTO Pet VALUES('pt1133','Sleepy','Labrador','Quiet',to_date('9-jan-2000','dd-mon-yyyy'), to_date('12-nov-1998','dd-mon-yyyy'), 'Y','o1113','c1111');

INSERT INTO Pet VALUES('pt1144','Gentle','Boxer','Shy',to_date('18-jan-1999','dd-mon-yyyy'), to_date('9-oct-1999','dd-mon-yyyy'), 'N','o1114','c1112');

INSERT INTO Pet VALUES('pt1155','Bark','German Shepherd','Noisy',to_date('10-feb-1998','dd-mon-yyyy'), to_date('12-nov-1998','dd-mon-yyyy'), 'Y','o1115','c1113');

INSERT INTO Pet VALUES('pt1166','Big','Pit Bull','Scary',to_date('10-feb-1999','dd-mon-yyyy'), to_date('12-nov-2000','dd-mon-yyyy'), 'Y','o1115',null);

INSERT INTO Pet VALUES('pt1177','Accent','Chihuahua','Agressive',to_date('10-feb-2001','dd-mon-yyyy'), to_date('12-aug-2001','dd-mon-yyyy'), 'Y',null,'c1112');

CREATE TABLE Examination(

examNo CHAR(6) NOT NULL,

examDate DATE NOT NULL,

examTime NUMBER(4, 2) NOT NULL,

examResults VARCHAR2(40) NOT NULL,

petNo CHAR(6) NOT NULL,

staffNo CHAR(4) NOT NULL,

PRIMARY KEY (examNo),

CONSTRAINT ExaminationAK UNIQUE (staffNo, examDate, examTime),

CONSTRAINT petnumber FOREIGN KEY (petNo) REFERENCES Pet(petNo),

CONSTRAINT staffnumber FOREIGN KEY (staffNo) REFERENCES Staff(staffNo)

);

INSERT INTO Examination VALUES('e11111',to_date('1-aug-1998','dd-mon-yyyy'),12.45,'fine','pt1111','s113');

INSERT INTO Examination VALUES('e11112',to_date('10-oct-2001','dd-mon-yyyy'),10.30,'Eye is red','pt1122','s111');

INSERT INTO Examination VALUES('e11113',to_date('25-jun-2001','dd-mon-yyyy'),14.15,'needs diet','pt1155','s118');

INSERT INTO Examination VALUES('e11114',to_date('20-nov-1999','dd-mon-yyyy'),09.25,'needs balanced nutrition','pt1144','s113');

INSERT INTO Examination VALUES('e11115',to_date('19-feb-1999','dd-mon-yyyy'),10.05,'vaccinization required','pt1155','s118');

CREATE TABLE Treatment(

AT&T 01:1 d 82% 4:31 PM description varcha240) noteut cct number52 nt petstatus char penCapacty number delault2. 1 andAtibute Type