DROP TABLE PERSON;
DROP TABLE BILLING;
DROP TABLE LOCATION;
DROP TABLE ROOM;
DROP TABLE SPECIALITY;
DROP TABLE CAREPROVIDER;
DROP TABLE EPISODE;
DROP TABLE DEPTHEADS;

Create table PERSON (
PA_NUM		number (8) PRIMARY KEY,
PA_LNAME		Varchar2 (20) NOT NULL,
PA_FNAME 		Varchar2 (20) NOT NULL,
PA_ADDRESSL1 		Varchar2 (40) NOT NULL,
PA_ADDRESSCITY	 Varchar2 (20) Not NULL,
PA_ZIP			char (5) NOT NULL,
PA_AREACODE		number (6),
PA_TELEPHONE		Number (10),
PA_DOB		Date NOT NULL,
PA_TITLE		Varchar2 (4) NOT NULL
);

Create table BILLING (
BL_CODE		Number (8) PRIMARY KEY,
BL_STATUS		Varchar2 (10) NOT NULL,
BL_CONTACTDATE	Date,
BL_CLOSEDATE		Date,
BL_TOTAL		Decimal (7,2) NOT NULL,
BL_AMOUNTPAID	Decimal (7,2),
BL_OUTSTANDING	Decimal (7,2),
BL_STARTDATE		Date
);

Create table LOCATION
(
LOC_CODE		Number (5) Primary Key,
LOC_DESCRIPTION	Varchar2 (15) NOT NULL,
LOC_UNITPRICE		Decimal (7,2) NOT NULL,
LOC_TELEPHONE	Number (8) NOT NULL
);

Create table ROOM
(
RM_CODE	Number(4)PRIMARY KEY,
RM_DESCRITPION	Varchar2 (6) NOT NULL,
RM_TYPE		Varchar2 (10) NOT NULL,
LOC_CODE		Number (5) NOT NULL,
CONSTRAINT ROOM_FOREIGN_KEY FOREIGN KEY (LOC_CODE) REFERENCES LOCATION (LOC_CODE)
);

Create table SPECIALITY
(
SP_CODE		Number(6) PRIMARY KEY,
SP_DESCRIPTION	Varchar2 (20) NOT NULL
);

Create table CAREPROVIDER
(
CP_GMCNUM		Number (8) PRIMARY KEY, 
CP_TITLE		Varchar2 (6) NOT NULL,
CP_FNAME		Varchar2 (20) NOT NULL,
CP_LNAME		Varchar2 (20) NOT NULL,
CP_BLEEP_NUM	Number (4) NOT NULL,
CP_ADDRESSL1		Varchar2 (255) NOT NULL,
CP_ZIP			Number (5) NOT NULL,
CP_DATEOFEMP	DATE NOT NULL,
CP_DATETOEMP	DATE,
SP_CODE		Number NOT NULL,
CONSTRAINT CAREPROVIDER_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE)
);

Create table EPSIODE (
EP_NUM		Number (8)  PRIMARY KEY,
PA_NUM		Number (8) NOT NULL,
RM_CODE		NUMBER (4) NOT NULL,
SP_CODE		NUMBER (6) NOT NULL,
CP_GMCNUM		NUMBER (8) NOT NULL,
EP_STATUS		Varchar2 (9) NOT NULL
EP_TYPE		Varchar2 (9) NOT NULL,
BL_CODE		Number (8) NOT NULL,
EP_ADMITDATE		DATE NOT NULL,
EP_DISCHARGEDATE	DATE,
EP_DIAGNOSIS		Varchar2 (255),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (PA_NUM) REFERENCES PERSON(PA_NUM),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (RM_CODE) REFERENCES ROOM(RM_CODE),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (CP_GMCNUM) REFERENCES CAREPROVIDER(CP_GMCNUM),
CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (BL_CODE) REFERENCES BILLING(BL_CODE)
);

CREATE TABLE DEPTHEADS (
DE_NUMBER number(5) PRIMARY KEY,
CP_GMCNUM		Number (8) NOT NULL,
SP_CODE		Number (6) NOT NULL, 
CONSTRAINT DEPTHEADS_FOREIGN_KEY FOREIGN KEY (CP_GMCNUM) REFERENCES CAREPROVIDER(CP_GMCNUM),
CONSTRAINT DEPTHEADS_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE)
);