--  +---------------------------------------------------------------------+
--  |  PERFECTPETS.SQL 						   |
--  |                                                                     |
--  |  Create tables for the Perfect Pets Database	   		   |
--  |                                                                     |
--  |  								   |
--  +---------------------------------------------------------------------+
--
--  Create all the necessary tables.

-- Note: The first time you run this script comment out the following line
--	SQL Server does not like dropping contraints that do not yet exist!
ALTER TABLE Clinic DROP CONSTRAINT mgrstaffnumber

drop table appointment;
drop table invoice;
drop table pharmclinicstock;
drop table itemclinicstock;
drop table item;
drop table pharmacy;
drop table pettreatment;
drop table petpen;
drop table pen;
drop table treatment;
drop table examination;
drop table pet;
drop table petowner;
drop table staff;
drop table clinic;

CREATE TABLE Clinic(
clinicNo CHAR(5) NOT NULL, 
street VARCHAR(40) NOT NULL, 
city VARCHAR(15) NOT NULL, 
state CHAR(2) NOT NULL, 
zipCode VARCHAR(9) NOT NULL UNIQUE, 
telNo VARCHAR(20) NOT NULL UNIQUE, 
faxNo VARCHAR(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 VARCHAR(30) NOT NULL, 
sLName VARCHAR(30) NOT NULL, 
sStreet VARCHAR(40) NOT NULL, 
sCity VARCHAR(15) NOT NULL, 
sState CHAR(2) NOT NULL, 
sZipCode VARCHAR(9)NOT NULL, 
sTelNo VARCHAR(20) NOT NULL, 
DOB DATETIME NOT NULL, 
gender CHAR NOT NULL, 
SSN VARCHAR(12) NOT NULL UNIQUE, 
position VARCHAR(20) NOT NULL, 
salary DECIMAL(8,2) NOT NULL, 
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',CAST('1-aug-1970' AS DATETIME),'M','111111111','clerk',40000,'c1111');
INSERT INTO staff VALUES('s112','Steve','John','1150 Fullerton St.','Fullerton','CA','92344','(714) 222-2113',CAST('1-jul-1960' AS DATETIME),'M','222112222','manager',50000,'c1111');
INSERT INTO staff VALUES('s113','Hess','Edward','12121 Beach Blvd.','Newport Beach','CA','99923','(996) 111-2221',CAST('1-aug-1959' AS DATETIME),'F','333113333','vet',60000,'c1112');
INSERT INTO staff VALUES('s114','Hester','Britten','8712 W Lambert Rd.','Brea','CA','92845','(714) 297-9827',CAST('1-aug-1979' AS DATETIME),'M','444114444','manager',45000,'c1112');
INSERT INTO staff VALUES('s115','Hoo','Don','3481 E Elm','Fullerton','CA','92811','(714) 113-1113',CAST('1-aug-1974' AS DATETIME),'F','555115555','manager',60000,'c1112');
INSERT INTO staff VALUES('s116','Hong','Soo','6511 W. Chapman','La Habra','CA','92732','(715) 333-7723',CAST('1-aug-1976' AS DATETIME),'M','666116666','clerk',41000,'c1113');
INSERT INTO staff VALUES('s117','Hill','Rowland','3411 Ahn Hill','Placentia','CA','92845','(714) 578-9137',CAST('1-aug-1971' AS DATETIME),'M','777117777','manager',48000,'c1113');
INSERT INTO staff VALUES('s118','Gomez','Rachel','2101 Victoria Dr.','Irvine','CA','92176','(949) 631-8888',CAST('1-aug-1969' AS DATETIME),'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 VARCHAR(30) NOT NULL, 
oLName VARCHAR(30) NOT NULL, 
oStreet VARCHAR(40) NOT NULL, 
oCity VARCHAR(15) NOT NULL,
oState CHAR(2) NOT NULL, 
oZipCode VARCHAR(9), 
oTelNo VARCHAR(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 VARCHAR(30) NOT NULL, 
petType VARCHAR(20) NOT NULL, 
petDesc VARCHAR(40) NOT NULL, 
petDOB DATETIME NOT NULL, 
dateRegistered DATETIME 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',CAST('30-nov-1994' AS DATETIME), CAST('10-aug-1995' AS DATETIME), 'Y','o1111','c1112');
INSERT INTO Pet VALUES('pt1122','Bell','Golden retriever','Active',CAST('4-mar-2001' AS DATETIME), CAST('2-may-2001' AS DATETIME), 'N',null,'c1111');
INSERT INTO Pet VALUES('pt1133','Sleepy','Labrador','Quiet',CAST('9-jan-2000' AS DATETIME), CAST('12-nov-1998' AS DATETIME), 'Y','o1113','c1111');
INSERT INTO Pet VALUES('pt1144','Gentle','Boxer','Shy',CAST('18-jan-1999' AS DATETIME), CAST('9-oct-1999' AS DATETIME), 'N','o1114','c1112');
INSERT INTO Pet VALUES('pt1155','Bark','German Shepherd','Noisy',CAST('10-feb-1998' AS DATETIME), CAST('12-nov-1998' AS DATETIME), 'Y','o1115','c1113');
INSERT INTO Pet VALUES('pt1166','Big','Pit Bull','Scary',CAST('10-feb-1999' AS DATETIME), CAST('12-nov-2000' AS DATETIME), 'Y','o1115',null);
INSERT INTO Pet VALUES('pt1177','Accent','Chihuahua','Agressive',CAST('10-feb-2001' AS DATETIME), CAST('12-aug-2001' AS DATETIME), 'Y',null,'c1112');

CREATE TABLE Examination(
examNo CHAR(6) NOT NULL, 
examDate DATETIME NOT NULL, 
examTime DECIMAL(4, 2) NOT NULL, 
examResults VARCHAR(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',CAST('1-aug-1998' AS DATETIME),12.45,'fine','pt1111','s113');
INSERT INTO Examination VALUES('e11112',CAST('10-oct-2001' AS DATETIME),10.30,'Eye is red','pt1122','s111');
INSERT INTO Examination VALUES('e11113',CAST('25-jun-2001' AS DATETIME),14.15,'needs diet','pt1155','s118');
INSERT INTO Examination VALUES('e11114',CAST('20-nov-1999' AS DATETIME),09.25,'needs balanced nutrition','pt1144','s113');
INSERT INTO Examination VALUES('e11115',CAST('19-feb-1999' AS DATETIME),10.05,'vaccinization required','pt1155','s118');

CREATE TABLE Treatment(
treatNo CHAR(4) NOT NULL, 
description VARCHAR(40) NOT NULL, 
cost DECIMAL(5, 2) NOT NULL,
PRIMARY KEY (treatNo)
);

INSERT INTO Treatment VALUES('t001','Bellyache',15);
INSERT INTO Treatment VALUES('t002','Tail Docking',40);
INSERT INTO Treatment VALUES('t003','Diabetes',25);
INSERT INTO Treatment VALUES('t004','Spray',10);
INSERT INTO Treatment VALUES('t006','Neuter',100);
INSERT INTO Treatment VALUES('t007','penicillin anitibiotic course',50);
INSERT INTO Treatment VALUES('t008','feline hysterectomy',200);
INSERT INTO Treatment VALUES('t009','vaccination course against feline flu',70);
INSERT INTO Treatment VALUES('t010','small dog-stay in pen per day',20);

CREATE TABLE Pen(
penNo CHAR(4) NOT NULL, 
penCapacity DECIMAL DEFAULT 2 NOT NULL CHECK (penCapacity BETWEEN 1 AND 4), 
penStatus CHAR DEFAULT 'A' NOT NULL CHECK (penStatus = 'A' OR penStatus = 'N'),
clinicNo CHAR(5) NOT NULL,
PRIMARY KEY (penNo),
CONSTRAINT clinicnumber3 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)
);

INSERT INTO Pen VALUES('p001',2,'A','c1111');
INSERT INTO Pen VALUES('p002',2,'A','c1111');
INSERT INTO Pen VALUES('p003',4,'A','c1112');
INSERT INTO Pen VALUES('p004',2,'N','c1113');
INSERT INTO Pen VALUES('p005',3,'A','c1111');
INSERT INTO Pen VALUES('p006',4,'N','c1112');
INSERT INTO Pen VALUES('p007',2,'A','c1113');
INSERT INTO Pen VALUES('p008',2,'A','c1111');

CREATE TABLE PetPen(
penNo CHAR(4) NOT NULL, 
petNo CHAR(6) NOT NULL, 
dateIn DATETIME NOT NULL, 
dateOut DATETIME, 
comments VARCHAR(40),
PRIMARY KEY (penNo, petNo, dateIn),
CONSTRAINT PetPenAK UNIQUE (penNo, petNo, dateOut),
CONSTRAINT pennumber FOREIGN KEY (penNo) REFERENCES Pen(penNo),
CONSTRAINT petnumber1 FOREIGN KEY (petNo) REFERENCES Pet(petNo)
);

INSERT INTO PetPen VALUES('p006','pt1111',CAST('4-may-2000' AS DATETIME),CAST('5-may-2000' AS DATETIME),'ok');
INSERT INTO PetPen VALUES('p001','pt1122',CAST('10-feb-2002' AS DATETIME),CAST('15-feb-2002' AS DATETIME),'sad');
INSERT INTO PetPen VALUES('p002','pt1133',CAST('21-dec-2000' AS DATETIME),CAST('23-dec-2000' AS DATETIME),null);
INSERT INTO PetPen VALUES('p003','pt1144',CAST('11-mar-2000' AS DATETIME),null,'no');
INSERT INTO PetPen VALUES('p001','pt1155',CAST('1-apr-1999' AS DATETIME),CAST('1-apr-1999' AS DATETIME),'ok');
INSERT INTO PetPen VALUES('p006','pt1177',CAST('1-may-2001' AS DATETIME),CAST('12-may-2001' AS DATETIME),'ok');

CREATE TABLE PetTreatment(
examNo CHAR(6) NOT NULL, 
treatNo CHAR(4) NOT NULL, 
startDate DATETIME NOT NULL, 
endDate DATETIME NOT NULL, 
quantity DECIMAL(4, 1) NOT NULL, 
petComments VARCHAR(40),
PRIMARY KEY (examNo, treatNo),
CONSTRAINT examnumber FOREIGN KEY (examNo) REFERENCES Examination(examNo),
CONSTRAINT treatnumber FOREIGN KEY (treatNo) REFERENCES Treatment(treatNo)
);

INSERT INTO PetTreatment VALUES('e11111','t001',CAST('1-jan-1999' AS DATETIME),CAST('1-jan-1999' AS DATETIME),2,null);
INSERT INTO PetTreatment VALUES('e11112','t003',CAST('3-feb-2002' AS DATETIME),CAST('4-feb-2002' AS DATETIME),1,null);
INSERT INTO PetTreatment VALUES('e11113','t004',CAST('15-oct-2001' AS DATETIME),CAST('19-oct-2001' AS DATETIME),3,'complicated');
INSERT INTO PetTreatment VALUES('e11111','t002',CAST('20-nov-1998' AS DATETIME),CAST('21-nov-1998' AS DATETIME),1,'minor');
INSERT INTO PetTreatment VALUES('e11114','t009',CAST('7-mar-2001' AS DATETIME),CAST('7-mar-2001' AS DATETIME),1,null);
INSERT INTO PetTreatment VALUES('e11114','t010',CAST('19-apr-2001' AS DATETIME),CAST('19-apr-2001' AS DATETIME),2,null);
INSERT INTO PetTreatment VALUES('e11115','t007',CAST('10-may-1999' AS DATETIME),CAST('10-may-1999' AS DATETIME),1,null);
INSERT INTO PetTreatment VALUES('e11115','t008',CAST('10-may-1999' AS DATETIME),CAST('10-may-1999' AS DATETIME),1,'Perfectly done');

CREATE TABLE Item(
itemNo CHAR(6) NOT NULL, 
itemName VARCHAR(20) NOT NULL, 
itemDesc VARCHAR(40) NOT NULL, 
itemCost DECIMAL(4, 2) NOT NULL,
PRIMARY KEY (itemNo)
);

--set escape \
INSERT INTO Item VALUES('s00111','petfood','puppy food',5);
INSERT INTO Item VALUES('s00112','petcan','canned food',10);
INSERT INTO Item VALUES('s00113','slip collar','adjustable collar',15);
INSERT INTO Item VALUES('s00114','fetch \& flash','dog''s toy',25);
INSERT INTO Item VALUES('s00115','eco-flyer','toy',9);
INSERT INTO Item VALUES('s00116','fold-a-bowl','foldable bowl',20);
INSERT INTO Item VALUES('s00117','dog manual','training manual',45);
INSERT INTO Item VALUES('s00118','flint river','treat',19);
INSERT INTO Item VALUES('s00119','natural bone','treat',13);
INSERT INTO Item VALUES('s00120','long haul','dog packs',30);

CREATE TABLE Pharmacy(
drugNo CHAR(3) NOT NULL, 
drugName VARCHAR(20) NOT NULL, 
drugDesc VARCHAR(40) NOT NULL, 
dosage VARCHAR(20) NOT NULL, 
methodAdmin VARCHAR(20) NOT NULL, 
drugCost DECIMAL(4, 2) NOT NULL,
PRIMARY KEY (drugNo)
);

INSERT INTO Pharmacy VALUES('d01','petbelly','For bellyache','200 tablets','2 tablet daily',9);
INSERT INTO Pharmacy VALUES('d02','flea spray','to wash out flea','bottle','daily',5);
INSERT INTO Pharmacy VALUES('d03','bellyworm','to remove threaded worms','50 tablets','1 tablet daily',10);
INSERT INTO Pharmacy VALUES('d04','DTPD','rabies','one shot','yearly',15);
INSERT INTO Pharmacy VALUES('d05','HATPA','ear care','bottle','1 spoon/week',30);
INSERT INTO Pharmacy VALUES('d06','appetizer','edible tablets','10 tablets','1 tablet daily',50);

CREATE TABLE ItemClinicStock(
itemNo CHAR(6) NOT NULL, 
clinicNo CHAR(5) NOT NULL, 
inStock DECIMAL(6) NOT NULL, 
reorderLevel DECIMAL(6) NOT NULL, 
reorderQty DECIMAL(6) NOT NULL,
PRIMARY KEY (itemNo, clinicNo),
CONSTRAINT itemnumber FOREIGN KEY (itemNo) REFERENCES Item(itemNo),
CONSTRAINT clinicnumber4 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)
);

INSERT INTO ItemClinicStock VALUES('s00111','c1111',1,1,5);
INSERT INTO ItemClinicStock VALUES('s00111','c1112',1,1,5);
INSERT INTO ItemClinicStock VALUES('s00111','c1113',1,1,5);
INSERT INTO ItemClinicStock VALUES('s00112','c1111',3,3,5);
INSERT INTO ItemClinicStock VALUES('s00112','c1112',3,3,5);
INSERT INTO ItemClinicStock VALUES('s00112','c1113',3,3,5);
INSERT INTO ItemClinicStock VALUES('s00113','c1111',20,5,50);
INSERT INTO ItemClinicStock VALUES('s00113','c1113',10,5,20);
INSERT INTO ItemClinicStock VALUES('s00114','c1111',20,2,10);
INSERT INTO ItemClinicStock VALUES('s00114','c1112',20,2,10);
INSERT INTO ItemClinicStock VALUES('s00114','c1113',20,2,10);
INSERT INTO ItemClinicStock VALUES('s00115','c1113',5,3,25);

CREATE TABLE PharmClinicStock(
drugNo CHAR(3) NOT NULL, 
clinicNo CHAR(5) NOT NULL, 
inStock DECIMAL(3) NOT NULL, 
reorderLevel DECIMAL(3) NOT NULL, 
reorderQty DECIMAL(3) NOT NULL,
PRIMARY KEY (drugNo, clinicNo),
CONSTRAINT drugnumber FOREIGN KEY (drugNo) REFERENCES Pharmacy(drugNo),
CONSTRAINT clinicnumber5 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)
);

INSERT INTO PharmClinicStock VALUES('d01','c1111',5,2,10);
INSERT INTO PharmClinicStock VALUES('d02','c1113',1,1,20);
INSERT INTO PharmClinicStock VALUES('d03','c1112',9,5,50);
INSERT INTO PharmClinicStock VALUES('d04','c1113',50,10,0);
INSERT INTO PharmClinicStock VALUES('d05','c1112',20,5,0);
INSERT INTO PharmClinicStock VALUES('d06','c1112',4,3,12);
INSERT INTO PharmClinicStock VALUES('d01','c1112',2,3,23);
INSERT INTO PharmClinicStock VALUES('d02','c1112',5,3,100);
INSERT INTO PharmClinicStock VALUES('d04','c1112',3,3,15);

CREATE TABLE Invoice(
invoiceNo CHAR(6) NOT NULL, 
invoiceDate DATETIME NOT NULL, 
datePaid DATETIME, 
paymentMethod VARCHAR(15) NOT NULL, 
ownerNo CHAR(5) NOT NULL, 
examNo CHAR(6) NOT NULL,
PRIMARY KEY (invoiceNo),
CONSTRAINT ownernumber1 FOREIGN KEY (ownerNo) REFERENCES PetOwner(ownerNo),
CONSTRAINT examnumber1 FOREIGN KEY (examNo) REFERENCES Examination(examNo)
);

INSERT INTO Invoice VALUES('i00001',CAST('10-mar-1999' AS DATETIME),CAST('15-mar-1999' AS DATETIME),'check','o1111','e11111');
INSERT INTO Invoice VALUES('i00002',CAST('20-dec-2001' AS DATETIME),CAST('25-dec-2001' AS DATETIME),'credit','o1112','e11112');
INSERT INTO Invoice VALUES('i00003',CAST('18-nov-2001' AS DATETIME),CAST('5-dec-2001' AS DATETIME),'cash','o1113','e11113');
INSERT INTO Invoice VALUES('i00004',CAST('10-jul-2000' AS DATETIME),CAST('25-jul-2000' AS DATETIME),'cash','o1114','e11114');
INSERT INTO Invoice VALUES('i00005',CAST('25-mar-1999' AS DATETIME),CAST('29-mar-1999' AS DATETIME),'credit','o1115','e11115');

CREATE TABLE Appointment(
appNo CHAR(6) NOT NULL, 
appDate DATETIME NOT NULL, 
appTime DECIMAL(4, 2) NOT NULL, 
petNo CHAR(6) NOT NULL,
PRIMARY KEY (appNo),
CONSTRAINT petnumber2 FOREIGN KEY (petNo) REFERENCES Pet(petNo)
);

INSERT INTO Appointment VALUES('a00001',CAST('11-apr-1999' AS DATETIME),15.20,'pt1111');
INSERT INTO Appointment VALUES('a00002',CAST('21-nov-2001' AS DATETIME),10.10,'pt1122');
INSERT INTO Appointment VALUES('a00003',CAST('1-oct-2001' AS DATETIME),9.00,'pt1133');
INSERT INTO Appointment VALUES('a00004',CAST('7-jul-2000' AS DATETIME),16.30,'pt1144');
INSERT INTO Appointment VALUES('a00005',CAST('10-mar-2002' AS DATETIME),12.40,'pt1155');

select * from clinic;
select * from staff;
select * from petowner;
select * from pet;
select * from examination;
select * from treatment;
select * from pen;
select * from petpen;
select * from pettreatment;
select * from item;
select * from pharmacy;
select * from itemclinicstock;
select * from pharmclinicstock;
select * from invoice;
select * from appointment;




