Healthcare

CREATE TABLE PATIENT(
PATIENT_ID NUMBER(16,0) PRIMARY KEY,
PATIENT_FIRST VARCHAR2(35) NOT NULL,
PATIENT_LAST VARCHAR2(35) NOT NULL,
PATIENT_MARITAL_STATUS CHAR(2) NOT NULL,
PATIENT_RACE VARCHAR2(10) NOT NULL,
PATIENT_GENDER CHAR(2) NOT NULL,
PATIENT_DRIVERS_LICENSE VARCHAR2(15),
PATIENT_BIRTH_DATE DATE NOT NULL,
PATIENT_DEATH_DATE DATE,
PATIENT_PASSPORT VARCHAR2(15),
PATIENT_ADDRESS VARCHAR2(35) NOT NULL,
PATIENT_CITY VARCHAR2(20) NOT NULL,
PATIENT_STATE CHAR(2) NOT NULL,
PATIENT_ZIP VARCHAR2(6) NOT NULL,
PATIENT_SSN NUMBER(9,0) NOT NULL,
PATIENT_MAIDEN_NAME VARCHAR2(35)
);

CREATE TABLE ORGANIZATION(
ORGANIZATION_ID NUMBER(16,0) PRIMARY KEY,
ORGANIZATION_NAME VARCHAR2(35) NOT NULL,
ORGANIZATION_DESCRIPTION VARCHAR2(100) NOT NULL
);

CREATE TABLE PROVIDER(
PROVIDER_ID NUMBER(16,0) PRIMARY KEY,
PROVIDER_FIRST VARCHAR2(35) NOT NULL,
PROVIDER_LAST VARCHAR2(35) NOT NULL,
PROVIDER_GENDER CHAR(2) NOT NULL,
PROVIDER_SPECIALTY VARCHAR2(35),
PROVIDER_ADDRESS VARCHAR2(35) NOT NULL,
PROVIDER_CITY VARCHAR2(35) NOT NULL,
PROVIDER_STATE CHAR(2) NOT NULL,
PROVIDER_ZIP VARCHAR2(6) NOT NULL,
ORGANIZATION_ID NUMBER(16,0) NOT NULL,
FOREIGN KEY (ORGANIZATION_ID) REFERENCES ORGANIZATION
);

CREATE TABLE MEDICINE(
MEDICATION_ID NUMBER(16,0) PRIMARY KEY,
MEDICATION_NAME VARCHAR2(35) NOT NULL,
MEDICTION_DESCRIPTION VARCHAR2(100) NOT NULL,
MEDICATION_PRICE NUMBER(9,2)
);

CREATE TABLE VACCINE(
VACCINE_ID NUMBER(16,0) PRIMARY KEY,
VACCINE_NAME VARCHAR2(35) NOT NULL,
VACCINE_DESCRIPTION VARCHAR2(35) NOT NULL,
VACCINE_COST NUMBER(9,2) NOT NULL
);

CREATE TABLE PRESCRIPTION(
SCRIPT_ID NUMBER(16,0) NOT NULL,
MEDICINE_ID NUMBER(16,0) NOT NULL,
DISPENSE_AMOUNT NUMBER(16,0),
PRESCRIPTION_DESCRIPTION VARCHAR2(35),
PRIMARY KEY(SCRIPT_ID,MEDICINE_ID),
FOREIGN KEY (MEDICINE_ID) REFERENCES MEDICINE
);

CREATE TABLE BODYSITE(
BODYSITE_ID NUMBER(16,0) PRIMARY KEY,
BODYSITE_DESCRIPTION VARCHAR(30) NOT NULL,
MODALITY_DESCRIPTION VARCHAR(30) NOT NULL,
SOP_DESCRIPTION VARCHAR(30) NOT NULL
);

CREATE TABLE CONDITION(
CONDITION_ID NUMBER(16,0) PRIMARY KEY,
CONDITION_DESCRIPTION VARCHAR2(30) NOT NULL
);

CREATE TABLE VISIT(
VISIT_ID NUMBER(16,0),
PATIENT_ID NUMBER(16,0),
PROVIDER_ID NUMBER(16,0),
VISIT_START DATE,
VISIT_END DATE,
VISIT_DESCRIPTION VARCHAR2(30),
PRIMARY KEY(VISIT_ID,PATIENT_ID,PROVIDER_ID,VISIT_START),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT,
FOREIGN KEY (PROVIDER_ID) REFERENCES PROVIDER
);