6조 팀 과제

테이블 (31)

admissions.csv (hosp-1)

  • hadm_id — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • admittime — TIMESTAMP
  • dischtime — TIMESTAMP
  • deathtime — TIMESTAMP
  • admission_type — VARCHAR(40) — NOT NULL
  • admit_provider_id — VARCHAR(10)
  • admission_location — VARCHAR(60)
  • discharge_location — VARCHAR(60)
  • insurance — VARCHAR(255)
  • language — VARCHAR(10)
  • marital_status — VARCHAR(30)
  • race — VARCHAR(80)
  • edregtime — TIMESTAMP
  • edouttime — TIMESTAMP
  • hospital_expire_flag — SMALLINT

caregiver.csv (icu-1)

  • caregiver_id — INTEGER — NOT NULL

chartevents.csv (icu-2)

  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • stay_id (stay_id2) — INTEGER — 외래 키 → icustays.csv.stay_id — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_items.csv.itemid — NOT NULL
  • caregiver_id — INTEGER
  • charttime — TIMESTAMP — NOT NULL
  • storetime — TIMESTAMP
  • value — VARCHAR(200)
  • valuenum — FLOAT
  • valueuom — VARCHAR(20)
  • warning — SMALLINT

d_hcps.csv (hosp-2)

  • code — CHAR(5) — 기본 키 — NOT NULL
  • category — SMALLINT
  • long_description — TEXT
  • short_description — VARCHAR(180)

d_icd_diagnoses.csv (hosp-3)

  • icd_code — CHAR(7) — 기본 키 — NOT NULL
  • icd_version — SMALLINT — 기본 키 — NOT NULL
  • long_title — VARCHAR(255)

d_icd_procedures.csv (hosp-4)

  • icd_code — CHAR(7) — 기본 키 — NOT NULL
  • icd_version — SMALLINT — 기본 키 — NOT NULL
  • long_title — VARCHAR(222)

d_items.csv (icu-3)

  • itemid — INTEGER — 기본 키 — NOT NULL
  • 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.csv (hosp-5)

  • itemid — INTEGER — 기본 키 — NOT NULL
  • label — VARCHAR(50)
  • fluid — VARCHAR(50)
  • category — VARCHAR(50)

datetimeevents.csv (icu-4)

  • charttime — TIMESTAMP — 기본 키 — NOT NULL
  • stay_id (stay_id2) — INTEGER — 외래 키 → icustays.csv.stay_id — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_items.csv.itemid — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • caregiver_id — INTEGER
  • storetime — TIMESTAMP
  • value — TIMESTAMP — NOT NULL
  • valueuom — VARCHAR(20)
  • warning — SMALLINT

diagnoses_icd.csv (hosp-6)

  • seq_num — INTEGER — 기본 키 — NOT NULL
  • icd_code — CHAR(7) — 기본 키 — NOT NULL
  • icd_version — SMALLINT — 기본 키 — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL

drgcodes.csv (hosp-7)

  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • drg_type — VARCHAR(4)
  • drg_code — VARCHAR(10) — NOT NULL
  • description — VARCHAR(195)
  • drg_severity — SMALLINT
  • drg_mortality — SMALLINT

emar.csv (hosp-8)

  • emar_id — VARCHAR(25) — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • emar_seq — INTEGER — NOT NULL
  • poe_id — VARCHAR(25) — NOT NULL
  • pharmacy_id — INTEGER
  • enter_provider_id — VARCHAR(10)
  • charttime — TIMESTAMP — NOT NULL
  • medication — TEXT
  • event_txt — VARCHAR(100)
  • scheduletime — TIMESTAMP
  • storetime — TIMESTAMP — NOT NULL

emar_detail.csv (hosp-9)

  • parent_field_ordinal — VARCHAR(10) — 기본 키 — NOT NULL
  • emar_id (emar_id2) — VARCHAR(25) — 외래 키 → emar.csv.emar_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • emar_seq — INTEGER — NOT NULL
  • administration_type — VARCHAR(50)
  • pharmacy_id — INTEGER
  • barcode_type — VARCHAR(4)
  • reason_for_no_barcode — TEXT
  • complete_dose_not_given — VARCHAR(5)
  • dose_due — VARCHAR(100)
  • dose_due_unit — VARCHAR(50)
  • dose_given — VARCHAR(255)
  • dose_given_unit — VARCHAR(50)
  • will_remainder_of_dose_be_given — VARCHAR(5)
  • product_amount_given — VARCHAR(30)
  • product_unit — VARCHAR(30)
  • product_code — VARCHAR(30)
  • product_description — VARCHAR(255)
  • product_description_other — VARCHAR(255)
  • prior_infusion_rate — VARCHAR(40)
  • infusion_rate — VARCHAR(40)
  • infusion_rate_adjustment — VARCHAR(50)
  • infusion_rate_adjustment_amount — VARCHAR(30)
  • infusion_rate_unit — VARCHAR(30)
  • route — VARCHAR(10)
  • infusion_complete — VARCHAR(1)
  • completion_interval — VARCHAR(50)
  • new_iv_bag_hung — VARCHAR(1)
  • continued_infusion_in_other_location — VARCHAR(1)
  • restart_interval — VARCHAR(2305)
  • side — VARCHAR(10)
  • site — VARCHAR(255)
  • non_formulary_visual_verification — VARCHAR(1)

hcpcsevents.csv (hosp-10)

  • hcpcs_cd — CHAR(5) — 기본 키 — NOT NULL
  • seq_num — INTEGER — 기본 키 — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • code — CHAR(5) — 외래 키 → d_hcps.csv.code — NOT NULL
  • chartdate — DATE
  • short_description — VARCHAR(180)

icustays.csv (icu-5)

  • stay_id — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • first_careunit — VARCHAR(255)
  • last_careunit — VARCHAR(255)
  • intime — TIMESTAMP
  • outtime — TIMESTAMP
  • los — FLOAT

ingredientevents.csv (icu-6)

  • subject_id — INTEGER — NOT NULL
  • hadm_id — INTEGER — NOT NULL
  • stay_id — INTEGER
  • caregiver_id — INTEGER
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL
  • storetime — TIMESTAMP
  • itemid — INTEGER — NOT NULL
  • amount — FLOAT
  • amountuom — VARCHAR(20)
  • rate — FLOAT
  • rateuom — VARCHAR(20)
  • orderid — INTEGER — NOT NULL
  • linkorderid — INTEGER
  • statusdescription — VARCHAR(20)
  • originalamount — FLOAT
  • originalrate — FLOAT

inputevents.csv (icu-7)

  • orderid — INTEGER — 기본 키 — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_items.csv.itemid — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • stay_id (stay_id2) — INTEGER — 외래 키 → icustays.csv.stay_id — NOT NULL
  • caregiver_id — INTEGER
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL
  • storetime — TIMESTAMP
  • amount — FLOAT
  • amountuom — VARCHAR(20)
  • rate — FLOAT
  • rateuom — VARCHAR(20)
  • linkorderid — INTEGER
  • ordercategoryname — VARCHAR(50)
  • secondaryordercategoryname — VARCHAR(50)
  • ordercomponenttypedescription — VARCHAR(100)
  • ordercategorydescription — VARCHAR(30)
  • patientweight — FLOAT
  • totalamount — FLOAT
  • totalamountuom — VARCHAR(50)
  • isopenbag — SMALLINT
  • continueinnextdept — SMALLINT
  • statusdescription — VARCHAR(20)
  • originalamount — FLOAT
  • originalrate — FLOAT

labevents.csv (hosp-11)

  • labevent_id — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_labitems.csv.itemid — NOT NULL
  • hadm_id — INTEGER
  • specimen_id — INTEGER — NOT NULL
  • order_provider_id — VARCHAR(10)
  • charttime — TIMESTAMP(0)
  • storetime — TIMESTAMP(0)
  • value — VARCHAR(200)
  • valuenum — DOUBLE PRECISION
  • valueuom — VARCHAR(20)
  • ref_range_lower — DOUBLE PRECISION
  • ref_range_upper — DOUBLE PRECISION
  • flag — VARCHAR(10)
  • priority — VARCHAR(7)
  • comments — TEXT

microbiologyevents.csv (hosp-12)

  • microevent_id — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • micro_specimen_id — INTEGER — NOT NULL
  • order_provider_id — VARCHAR(10)
  • chartdate — TIMESTAMP(0) — NOT NULL
  • charttime — TIMESTAMP(0)
  • spec_itemid — INTEGER — NOT NULL
  • spec_type_desc — VARCHAR(100) — NOT NULL
  • test_seq — INTEGER — NOT NULL
  • storedate — TIMESTAMP(0)
  • storetime — TIMESTAMP(0)
  • test_itemid — INTEGER
  • test_name — VARCHAR(100)
  • org_itemid — INTEGER
  • org_name — VARCHAR(100)
  • isolate_num — SMALLINT
  • quantity — VARCHAR(50)
  • ab_itemid — INTEGER
  • ab_name — VARCHAR(30)
  • dilution_text — VARCHAR(10)
  • dilution_comparison — VARCHAR(20)
  • dilution_value — DOUBLE PRECISION
  • interpretation — VARCHAR(5)
  • comments — TEXT

omr.csv (hosp-13)

  • subject_id — INTEGER — NOT NULL
  • chartdate — DATE — NOT NULL
  • seq_num — INTEGER — NOT NULL
  • result_name — VARCHAR(100) — NOT NULL
  • result_value — TEXT — NOT NULL

outputevents.csv (icu-8)

  • charttime — TIMESTAMP(3) — 기본 키 — NOT NULL
  • stay_id (stay_id2) — INTEGER — 외래 키 → icustays.csv.stay_id — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_items.csv.itemid — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • caregiver_id — INTEGER
  • storetime — TIMESTAMP(3) — NOT NULL
  • value — FLOAT — NOT NULL
  • valueuom — VARCHAR(20)

patients.csv (hosp-14)

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

pharmacy.csv (hosp-15)

  • pharmacy_id — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • poe_id — VARCHAR(25)
  • starttime — TIMESTAMP(3)
  • stoptime — TIMESTAMP(3)
  • medication — TEXT
  • proc_type — VARCHAR(50) — NOT NULL
  • status — VARCHAR(50)
  • entertime — TIMESTAMP(3) — NOT NULL
  • verifiedtime — TIMESTAMP(3)
  • route — VARCHAR(50)
  • frequency — VARCHAR(50)
  • disp_sched — VARCHAR(255)
  • infusion_type — VARCHAR(15)
  • sliding_scale — VARCHAR(1)
  • lockout_interval — VARCHAR(50)
  • basal_rate — REAL
  • one_hr_max — VARCHAR(10)
  • doses_per_24_hrs — REAL
  • duration — REAL
  • duration_interval — VARCHAR(50)
  • expiration_value — INTEGER
  • expiration_unit — VARCHAR(50)
  • expirationdate — TIMESTAMP(3)
  • dispensation — VARCHAR(50)
  • fill_quantity — VARCHAR(50)

poe.csv (hosp-17)

  • poe_id — VARCHAR(25) — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • poe_seq — INTEGER — NOT NULL
  • ordertime — TIMESTAMP(0) — NOT NULL
  • order_type — VARCHAR(25) — NOT NULL
  • order_subtype — VARCHAR(50)
  • transaction_type — VARCHAR(15)
  • discontinue_of_poe_id — VARCHAR(25)
  • discontinued_by_poe_id — VARCHAR(25)
  • order_provider_id — VARCHAR(10)
  • order_status — VARCHAR(15)

poe_detail.csv (hosp-16)

  • field_name — VARCHAR(255) — 기본 키 — NOT NULL
  • poe_id (poe_id2) — VARCHAR(25) — 외래 키 → poe.csv.poe_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • poe_seq — INTEGER — NOT NULL
  • field_value — TEXT

prescriptions.csv (hosp-18)

  • pharmacy_id — INTEGER — 기본 키 — NOT NULL
  • drug_type — VARCHAR(20) — 기본 키 — NOT NULL
  • drug — VARCHAR(255) — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • poe_id — VARCHAR(25)
  • poe_seq — INTEGER
  • order_provider_id — VARCHAR(10)
  • starttime — TIMESTAMP(3)
  • stoptime — TIMESTAMP(3)
  • formulary_drug_cd — VARCHAR(50)
  • gsn — VARCHAR(255)
  • ndc — VARCHAR(25)
  • prod_strength — VARCHAR(255)
  • form_rx — VARCHAR(25)
  • dose_val_rx — VARCHAR(100)
  • dose_unit_rx — VARCHAR(50)
  • form_val_disp — VARCHAR(50)
  • form_unit_disp — VARCHAR(50)
  • doses_per_24_hrs (Field20) — REAL
  • route — VARCHAR(50)

procedureevents.csv (icu-9)

  • orderid — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • stay_id (stay_id2) — INTEGER — 외래 키 → icustays.csv.stay_id — NOT NULL
  • itemid (itemid2) — INTEGER — 외래 키 → d_items.csv.itemid — NOT NULL
  • caregiver_id — INTEGER
  • starttime — TIMESTAMP — NOT NULL
  • endtime — TIMESTAMP — NOT NULL
  • storetime — TIMESTAMP — NOT NULL
  • value — FLOAT
  • valueuom — VARCHAR(20)
  • location — VARCHAR(100)
  • locationcategory — VARCHAR(50)
  • linkorderid — INTEGER
  • ordercategoryname — VARCHAR(50)
  • ordercategorydescription — VARCHAR(30)
  • patientweight — FLOAT
  • isopenbag — SMALLINT
  • continueinnextdept — SMALLINT
  • statusdescription — VARCHAR(20)
  • originalamount — FLOAT
  • originalrate — FLOAT

procedures_icd.csv (hosp-19)

  • seq_num — INTEGER — 기본 키 — NOT NULL
  • icd_code — VARCHAR(7) — 기본 키 — NOT NULL
  • icd_version — SMALLINT — 기본 키 — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • chartdate — DATE — NOT NULL

provider.csv (hosp-20)

  • provider_id — VARCHAR(10) — NOT NULL

services.csv (hosp-21)

  • transfertime — TIMESTAMP — 기본 키 — NOT NULL
  • curr_service — VARCHAR(10) — 기본 키 — NOT NULL
  • hadm_id (hadm_id2) — INTEGER — 외래 키 → admissions.csv.hadm_id — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • prev_service — VARCHAR(10)

transfers.csv (hosp-22)

  • transfer_id (Key) — INTEGER — 기본 키 — NOT NULL
  • subject_id (subject_id2) — INTEGER — 외래 키 → patients.csv.subject_id — NOT NULL
  • hadm_id — INTEGER
  • eventtype — VARCHAR(10)
  • careunit — VARCHAR(255)
  • intime — TIMESTAMP
  • outtime — TIMESTAMP