CREATE TABLE person
(
    personID INTEGER    NOT NULL,
    lname VARCHAR(50)   NOT NULL,
    fname VARCHAR(50)   NOT NULL,
    birth_date DATE     NOT NULL,
    city VARCHAR(20)    ,
    zipcode INTEGER     ,
    state VARCHAR(20)   ,
    CONSTRAINT pk_person PRIMARY KEY (personID)
);

CREATE TABLE states
(
    state VARCHAR(20)   ,
    CONSTRAINT pk_state PRIMARY KEY (state)
);

CREATE TABLE phonenumbers
(
    personID integer        NOT NULL,
    phonetype VARCHAR(20)   ,
    phonenumber VARCHAR(20) ,
    CONSTRAINT pk_phonenumbers PRIMARY KEY (personID, phonetype, phonenumber)
);

CREATE TABLE emergencycontacts
(
    personID INTEGER        NOT NULL,
    emergID INTEGER         NOT NULL,
    relation VARCHAR(20)    NOT NULL,
    CONSTRAINT pk_emergencycontacts PRIMARY KEY (personID, emergID, relation)
);

CREATE TABLE relations
(
    relation VARCHAR(20)    NOT NULL,
    CONSTRAINT pk_relations PRIMARY KEY (relation)
);

CREATE TABLE employees
(
    eID INTEGER         NOT NULL,
    hireDate DATE       NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (eID)
);

CREATE TABLE physicians
(
    phyID INTEGER           NOT NULL,
    specialty VARCHAR(20)   NOT NULL,
    pager_no VARCHAR(20)    NOT NULL,
    CONSTRAINT pk_physicians PRIMARY KEY (phyID)
);

CREATE TABLE volunteers
(
    vID INTEGER         NOT NULL,
    CONSTRAINT pk_volunteers PRIMARY KEY (vID)
);

CREATE TABLE volunteer_skills
(
    vID INTEGER                 NOT NULL,
    volunteer_skill VARCHAR(20) ,
    CONSTRAINT pk_volunteers PRIMARY KEY (vID, volunteer_skill)
);

CREATE TABLE patients
(
    patID INTEGER,
    phyID INTEGER,
    contact_date DATE,
    hasInsurance BOOLEAN  ,
    CONSTRAINT pk_patients PRIMARY KEY (patID)
);

CREATE TABLE outpatient
(
    patID INTEGER,
    scheduled_date DATE,
    CONSTRAINT pk_outpatient PRIMARY KEY (patID)
);

CREATE TABLE resident
(
    patID INTEGER,
    admitted_date DATE,
    discharge_date DATE,
    CONSTRAINT pk_resident PRIMARY KEY (patID)
);

CREATE TABLE visit
(
    visitdate DATE,
    patID INTEGER,
    phyID INTEGER,
    comment VARCHAR (50),
    CONSTRAINT pk_visit PRIMARY KEY (visitdate, patID)
);

CREATE TABLE insurance
(
    patID INTEGER,
    plan VARCHAR(20),
    coverge VARCHAR(50),
    CONSTRAINT pk_insurance PRIMARY KEY (plan)
);

CREATE TABLE plans
(
    plan VARCHAR(20),
    CONSTRAINT pk_plans PRIMARY KEY (plan)
);

CREATE TABLE nurse
  (
  has_RNcert    Boolean     NOT NULL,
  eID INTEGER NOT NULL,
  cc_name VARCHAR(15) NOT NULL,
  cc_loc   VARCHAR(12)         ,
  CONSTRAINT pk_nurse PRIMARY KEY (eID)
  );
CREATE TABLE Care_Center
  (
  eID INTEGER   NOT NULL,
  cc_name VARCHAR(15) NOT NULL,
  cc_loc   VARCHAR(12)    NOT NULL,
  CONSTRAINT pk_Care_Center PRIMARY KEY (eID,cc_name,cc_loc)
  );

CREATE TABLE RN
  (
  eID INTEGER NOT NULL,
  start_date    DATE NOT NULL,
  end_date  DATE NOT NULL,
  CONSTRAINT pk_RN PRIMARY KEY (eID)
  );

CREATE TABLE room
  (
  eID INTEGER NOT NULL,
  cc_name VARCHAR(15)   NOT NULL,
  cc_loc   VARCHAR(12)  NOT NULL,
  room_no   VARCHAR(5)  NOT NULL,
  CONSTRAINT pk_nurse PRIMARY KEY (cc_name,cc_loc,room_no)
  );
CREATE TABLE bed
  (
  patID INTEGER NOT NULL,
  cc_name VARCHAR(15)   NOT NULL,
  cc_loc   VARCHAR(12)  NOT NULL,
  room_no   VARCHAR(5)  NOT NULL,
  bed_no    VARCHAR(5)  NOT NULL,
  CONSTRAINT pk_nurse PRIMARY KEY (patID,cc_name,cc_loc,room_no,bed_no)
  );

ALTER TABLE person
    ADD CONSTRAINT person_states_fk
    FOREIGN KEY (state)
    REFERENCES  states (state);

ALTER TABLE phonenumbers
    ADD CONSTRAINT person_phonenumbers_fk
    FOREIGN KEY (personID)
    REFERENCES  person (personID);

ALTER TABLE emergencycontacts
    ADD CONSTRAINT emergencycontacts_relations_fk
    FOREIGN KEY (relation)
    REFERENCES relations (relation);

ALTER TABLE emergencycontacts
    ADD CONSTRAINT emergencycontacts_person_fk
    FOREIGN KEY (personID)
    REFERENCES person (personID);

ALTER TABLE emergencycontacts
    ADD CONSTRAINT emergencycontacts_contacts_fk
    FOREIGN KEY (emergID)
    REFERENCES person (personID);

ALTER TABLE employees
    ADD CONSTRAINT person_employees_fk
    FOREIGN KEY (eID)
    REFERENCES person (personID);

ALTER TABLE physicians
    ADD CONSTRAINT person_physicians_fk
    FOREIGN KEY (phyID)
    REFERENCES person (personID);

ALTER TABLE volunteers
    ADD CONSTRAINT person_volunteers_fk
    FOREIGN KEY (vID)
    REFERENCES person (personID);

ALTER TABLE volunteer_skills
    ADD CONSTRAINT person_volunteer_skills_fk
    FOREIGN KEY (vID)
    REFERENCES volunteers (vID);

ALTER TABLE insurance
    ADD CONSTRAINT insurance_patients_fk
    FOREIGN KEY (patID)
    REFERENCES patients (patID);

ALTER TABLE insurance
    ADD CONSTRAINT insurance_plans_fk
    FOREIGN KEY (plan)
    REFERENCES plans (plan);

ALTER TABLE patients
    ADD CONSTRAINT patients_physicians_fk
    FOREIGN KEY (phyID)
    REFERENCES physician (phyID);

ALTER TABLE patients
    ADD CONSTRAINT patients_person_fk
    FOREIGN KEY (patID)
    REFERENCES  person (personID);

ALTER TABLE outpatient
    ADD CONSTRAINT outpatient_patients_fk
    FOREIGN KEY (patID)
    REFERENCES patients (patID);

ALTER TABLE visit
    ADD CONSTRAINT visit_outpatient_fk
    FOREIGN KEY (patID)
    REFERENCES outpatient (patID);

ALTER TABLE visit
    ADD CONSTRAINT visit_physicians_fk
    FOREIGN KEY (phyID)
    REFERENCES physicians (phyID);

ALTER TABLE resident
    ADD CONSTRAINT resident_patients_fk
    FOREIGN KEY (patID)
    REFERENCES patients (patID);