Project-2

Tables (31)

admissions

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER
  • admittime — TIMESTAMP — NOT NULL
  • dischtime — TIMESTAMP
  • deathtime — TIMESTAMP
  • admission_type — VARCHAR(40) — NOT NULL
  • admit_provider_id — VARCHAR(10) — Foreign key → provider.provider_id
  • admission_location — VARCHAR(60)
  • discharge_location — VARCHAR(60)
  • insurance — VARCHAR(255)
  • language — VARCHAR(25)
  • marital_status — VARCHAR(30)
  • race — VARCHAR(80)
  • edregtime — TIMESTAMP
  • edouttime — TIMESTAMP
  • hospital_expire_flag — SMALLINT

caregiver

  • caregiver_id — INTEGER

chartevents

  • charttime — TIMESTAMP — Primary key — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id — NOT NULL
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • value — VARCHAR(200)
  • valuenum — FLOAT

d_hcpcs

  • code — CHAR(5)
  • category — SMALLINT
  • long_description — TEXT
  • short_description — VARCHAR(180)

d_icd_diagnoses

  • icd_code — CHAR(7)
  • icd_version — SMALLINT
  • long_title — VARCHAR(255)

d_icd_procedures

  • icd_code — CHAR(7)
  • icd_version — SMALLINT
  • long_title — VARCHAR(222)

d_items

  • itemid — INTEGER
  • label — VARCHAR(100) — NOT NULL
  • abbreviation — VARCHAR(50) — NOT NULL
  • linksto — VARCHAR(30) — NOT NULL
  • category — VARCHAR(50) — NOT NULL
  • unitname — VARCHAR(50)
  • param_type — VARCHAR(20) — NOT NULL
  • lownormalvalue — FLOAT
  • highnormalvalue — FLOAT

d_labitems

  • itemid — INTEGER
  • label — VARCHAR(50)
  • fluid — VARCHAR(50)
  • category — VARCHAR(50)

datetimeevents

  • charttime — TIMESTAMP — Primary key — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id — NOT NULL
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • value — TIMESTAMP — NOT NULL

diagnoses_icd

  • seq_num — INTEGER — Primary key — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • icd_code — CHAR(7)
  • icd_version — SMALLINT

drgcodes

  • drg_code — VARCHAR(10) — Primary key — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • drg_type — VARCHAR(4)
  • description — VARCHAR(195)

emar

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id
  • emar_id — VARCHAR(25)
  • emar_seq — INTEGER — NOT NULL
  • poe_id — VARCHAR(25) — Foreign key → poe.poe_id — NOT NULL
  • pharmacy_id — INTEGER — Foreign key → pharmacy.pharmacy_id
  • charttime — TIMESTAMP — NOT NULL
  • medication — TEXT
  • event_txt — VARCHAR(100)

emar_detail

  • emar_seq — INTEGER — Primary key — NOT NULL
  • emar_id — VARCHAR(25) — Foreign key → emar.emar_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • parent_field_ordinal — VARCHAR(10)
  • pharmacy_id — INTEGER

hcpcsevents

  • seq_num — INTEGER — Primary key — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • hcpcs_cd — CHAR(5) — Foreign key → d_hcpcs.code — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • short_description — VARCHAR(180)

icustays

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • stay_id — INTEGER
  • first_careunit — VARCHAR(255)
  • last_careunit — VARCHAR(255)
  • intime — TIMESTAMP
  • outtime — TIMESTAMP
  • los — FLOAT

ingredientevents

  • orderid — INTEGER — Primary key — NOT NULL
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL

inputevents

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • orderid — INTEGER
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL
  • amount — FLOAT

labevents

  • labevent_id — INTEGER
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id
  • specimen_id — INTEGER — NOT NULL
  • itemid — INTEGER — Foreign key → d_labitems.itemid — NOT NULL
  • order_provider_id — VARCHAR(10) — Foreign key → provider.provider_id
  • charttime — TIMESTAMP(0)
  • storetime — TIMESTAMP(0)
  • value — VARCHAR(200)
  • valuenum — DOUBLE
  • valueuom — VARCHAR(20)

microbiologyevents

  • microevent_id — INTEGER
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id
  • chartdate — TIMESTAMP(0) — NOT NULL
  • spec_itemid — INTEGER — NOT NULL
  • spec_type_desc — VARCHAR(100) — NOT NULL
  • test_itemid — INTEGER
  • test_name — VARCHAR(100)
  • org_itemid — INTEGER
  • org_name — VARCHAR(100)

omr

  • chartdate — DATE — Primary key — NOT NULL
  • seq_num — INTEGER — Primary key — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • result_name — VARCHAR(100) — NOT NULL
  • result_value — TEXT — NOT NULL

outputevents

  • charttime — TIMESTAMP(3) — Primary key — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id — NOT NULL
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • value — FLOAT — NOT NULL

patients

  • subject_id — INTEGER
  • gender — CHAR(1) — NOT NULL
  • anchor_age — SMALLINT
  • anchor_year — SMALLINT — NOT NULL
  • anchor_year_group — VARCHAR(20) — NOT NULL
  • dod — DATE

pharmacy

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • pharmacy_id — INTEGER
  • poe_id — VARCHAR(25) — Foreign key → poe.poe_id
  • starttime — TIMESTAMP(3)
  • stoptime — TIMESTAMP(3)
  • medication — TEXT
  • proc_type — VARCHAR(50) — NOT NULL

poe

  • poe_id — VARCHAR(25)
  • poe_seq — INTEGER — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id
  • ordertime — TIMESTAMP(0) — NOT NULL
  • order_type — VARCHAR(25) — NOT NULL
  • order_provider_id — VARCHAR(10) — Foreign key → provider.provider_id

poe_detail

  • field_name — VARCHAR(255) — Primary key — NOT NULL
  • poe_id — VARCHAR(25) — Foreign key → poe.poe_id — NOT NULL
  • field_value — TEXT

prescriptions

  • drug_type — VARCHAR(20) — Primary key — NOT NULL
  • drug — VARCHAR(255) — Primary key — NOT NULL
  • pharmacy_id — INTEGER — Foreign key → pharmacy.pharmacy_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • starttime — TIMESTAMP(3)
  • stoptime — TIMESTAMP(3)

procedureevents

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • stay_id — INTEGER — Foreign key → icustays.stay_id — NOT NULL
  • caregiver_id — INTEGER — Foreign key → caregiver.caregiver_id
  • itemid — INTEGER — Foreign key → d_items.itemid — NOT NULL
  • orderid — INTEGER
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL

procedures_icd

  • seq_num — INTEGER — Primary key — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • chartdate — DATE — NOT NULL
  • icd_code — VARCHAR(7)
  • icd_version — SMALLINT

provider

  • provider_id — VARCHAR(10)

services

  • transfertime — TIMESTAMP — Primary key — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id — NOT NULL
  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • prev_service — VARCHAR(10)
  • curr_service — VARCHAR(10)

transfers

  • subject_id — INTEGER — Foreign key → patients.subject_id — NOT NULL
  • hadm_id — INTEGER — Foreign key → admissions.hadm_id
  • transfer_id — INTEGER
  • eventtype — VARCHAR(10)
  • careunit — VARCHAR(255)
  • intime — TIMESTAMP
  • outtime — TIMESTAMP