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

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Clinic(
clinicNo CHAR(5) NOT NULL, 
street VARCHAR2(40) NOT NULL, 
city VARCHAR2(15) NOT NULL, 
state VARCHAR2(15) 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)
);

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 VARCHAR2(15) NOT NULL, 
sZipCode VARCHAR2(9), 
sTelNo VARCHAR2(20) NOT NULL, 
DOB DATE NOT NULL, 
sex CHAR NOT NULL, 
SSN VARCHAR2(12) NOT NULL UNIQUE, 
position VARCHAR2(20) NOT NULL, 
salary NUMBER(8,2) NOT NULL, 
clinicNo CHAR(5) NOT NULL,
PRIMARY KEY (staffNo),
CONSTRAINT clinicnumber FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)
);

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 VARCHAR2(15) 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)
);

CREATE TABLE Pet(
petNo CHAR(6) NOT NULL, 
petName VARCHAR2(30) NOT NULL, 
petType VARCHAR2(20) NOT NULL, 
petDescription VARCHAR2(40) NOT NULL, 
pDOB DATE NOT NULL, 
dateRegistered DATE NOT NULL, 
petStatus CHAR NOT NULL, 
ownerNo CHAR(5) NOT NULL, 
clinicNo CHAR(5) NOT NULL,
PRIMARY KEY (petNo),
CONSTRAINT ownernumber FOREIGN KEY (ownerNo) REFERENCES PetOwner(ownerNo),
CONSTRAINT clinicnumber2 FOREIGN KEY (clinicNo) REFERENCES Clinic(clinicNo)
);

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)
);

CREATE TABLE Treatment(
treatNo VARCHAR2(4) NOT NULL, 
description VARCHAR2(40) NOT NULL, 
cost NUMBER(5, 2) NOT NUL,
PRIMARY KEY (treatNo)
);

CREATE TABLE Pen(
penNo CHAR(4) NOT NULL, 
penCapacity NUMBER 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)
);

CREATE TABLE PetPen(
penNo CHAR(4) NOT NULL, 
petNo CHAR(6) NOT NULL, 
dateIn DATE NOT NULL, 
dateOut DATE NOT NULL, 
comments VARCHAR2(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)
);

CREATE TABLE PetTreatment(
examNo CHAR(6) NOT NULL, 
treatNo VARCHAR2(4) NOT NULL, 
startDate DATE NOT NULL, 
endDate DATE NOT NULL, 
quantity NUMBER(4, 1) NOT NULL, 
ptComments VARCHAR2(40),
PRIMARY KEY (examNo, treatNo),
CONSTRAINT examnumber FOREIGN KEY (examNo) REFERENCES Examination(examNo),
CONSTRAINT treatnumber FOREIGN KEY (treatNo) REFERENCES Treatment(treatNo)
);

CREATE TABLE Item(
itemNo CHAR(6) NOT NULL, 
itemName VARCHAR2(20) NOT NULL, 
itemDescription VARCHAR2(20) NOT NULL, 
itemCost NUMBER(4, 2) NOT NULL,
PRIMARY KEY (itemNo)
);

CREATE TABLE Pharmacy(
drugNo CHAR(3) NOT NULL, 
drugName VARCHAR2(20) NOT NULL, 
drugDescription VARCHAR2(40) NOT NULL, 
dosage VARCHAR2(20) NOT NULL, 
methodAdmin VARCHAR2(20) NOT NULL, 
drugCost NUMBER(4, 2) NOT NULL,
PRIMARY KEY (drugNo)
);

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

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

CREATE TABLE Invoice(
invoiceNo CHAR(6) NOT NULL, 
invoiceDate DATE NOT NULL, 
datePaid DATE, 
paymentMethod VARCHAR2(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)
);

CREATE TABLE Appointment(
appNo CHAR(6) NOT NULL, 
aDate DATE NOT NULL, 
aTime NUMBER(4, 2) NOT NULL, 
petNo CHAR(6) NOT NULL,
PRIMARY KEY (appNo),
CONSTRAINT petnumber2 FOREIGN KEY (petNo) REFERENCES Pet(petNo)
);