CREATE TABLE all_prevalences
(
    ITEM                    TEXT
            primary key,
    "POPULATION TYPE"       TEXT,
    OCCURRENCES             INTEGER,
    "POPULATION COUNT"      INTEGER,
    "PREVALENCE RATE"       REAL,
    "PREVALENCE PERCENTAGE" REAL
);
all_prevalences
CREATE TABLE patients
(
    patient    TEXT
            primary key,
    birthdate  DATE,
    deathdate  DATE,
    ssn        TEXT,
    drivers    TEXT,
    passport   TEXT,
    prefix     TEXT,
    first      TEXT,
    last       TEXT,
    suffix     TEXT,
    maiden     TEXT,
    marital    TEXT,
    race       TEXT,
    ethnicity  TEXT,
    gender     TEXT,
    birthplace TEXT,
    address    TEXT
);
patients
CREATE TABLE encounters
(
    ID                TEXT
            primary key,
    DATE              DATE,
    PATIENT           TEXT,
    CODE              INTEGER,
    DESCRIPTION       TEXT,
    REASONCODE        INTEGER,
    REASONDESCRIPTION TEXT,
    foreign key (PATIENT) references patients(patient)
);
encounters
CREATE TABLE allergies
(
    START       TEXT,
    STOP        TEXT,
    PATIENT     TEXT,
    ENCOUNTER   TEXT,
    CODE        INTEGER,
    DESCRIPTION TEXT,
    primary key (PATIENT, ENCOUNTER, CODE),
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
allergies
CREATE TABLE careplans
(
    ID                TEXT,
    START             DATE,
    STOP              DATE,
    PATIENT           TEXT,
    ENCOUNTER         TEXT,
    CODE              REAL,
    DESCRIPTION       TEXT,
    REASONCODE        INTEGER,
    REASONDESCRIPTION TEXT,
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
careplans
CREATE TABLE conditions
(
    START       DATE,
    STOP        DATE,
    PATIENT     TEXT,
    ENCOUNTER   TEXT,
    CODE        INTEGER,
    DESCRIPTION TEXT,
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient),
    foreign key (DESCRIPTION) references all_prevalences(ITEM)
);
conditions
CREATE TABLE immunizations
(
    DATE        DATE,
    PATIENT     TEXT,
    ENCOUNTER   TEXT,
    CODE        INTEGER,
    DESCRIPTION TEXT,
    primary key (DATE, PATIENT, ENCOUNTER, CODE),
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
immunizations
CREATE TABLE medications
(
    START             DATE,
    STOP              DATE,
    PATIENT           TEXT,
    ENCOUNTER         TEXT,
    CODE              INTEGER,
    DESCRIPTION       TEXT,
    REASONCODE        INTEGER,
    REASONDESCRIPTION TEXT,
    primary key (START, PATIENT, ENCOUNTER, CODE),
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
medications
CREATE TABLE observations
(
    DATE        DATE,
    PATIENT     TEXT,
    ENCOUNTER   TEXT,
    CODE        TEXT,
    DESCRIPTION TEXT,
    VALUE       REAL,
    UNITS       TEXT,
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
observations
CREATE TABLE procedures
(
    DATE              DATE,
    PATIENT           TEXT,
    ENCOUNTER         TEXT,
    CODE              INTEGER,
    DESCRIPTION       TEXT,
    REASONCODE        INTEGER,
    REASONDESCRIPTION TEXT,
    foreign key (ENCOUNTER) references encounters(ID),
    foreign key (PATIENT) references patients(patient)
);
procedures
CREATE TABLE "claims"
(
    ID             TEXT
        primary key,
    PATIENT        TEXT
        references patients,
    BILLABLEPERIOD DATE,
    ORGANIZATION   TEXT,
    ENCOUNTER      TEXT
        references encounters,
    DIAGNOSIS      TEXT,
    TOTAL          INTEGER
);
claims
original_column_name,column_name,column_description,data_format,value_description
ID,,the unique id of the encounter,text,
DATE,,the date of the encounter,date,yyyy-mm-dd
PATIENT,,the patient id,text,
CODE,,the code of the care plan ,integer,
DESCRIPTION,,the description of the care plan,text,
REASONCODE,,the reason code,integer,
REASONDESCRIPTION,reason description,the description of the reason why the patient needs the care plan,text,
encounters
original_column_name,column_name,column_description,data_format,value_description
patient,,the unique id for the patient,text,
birthdate,birth date,the birth date of the patient,date,
deathdate,death date,the death date of the patient,date,"commonsense evidence: 
 the age of the patient = death year - birth year 
 if null, it means this patient is still alive"
ssn,social security number,the social security number of the patient,text,
drivers,,the driver number of the patient,text,"commonsense evidence: if not, this patient doesn't have driving license"
passport,,the passport number,text,"commonsense evidence: if not, this patient cannot go abroad, vice versa"
prefix,,the prefix,text,
first,,the first name,text,
last,,the last name,text,commonsense evidence: full name = first + last
suffix,,the suffix of the patient,text,"commonsense evidence: if suffix = PhD, JD, MD, it means this patient has doctoral degree. Otherwise, this patient is not."
maiden,,the maiden name of the patient,text,commonsense evidence: Only married women have the maiden name
marital,,the marital status of the patient,text,"commonsense evidence: 
 M: married 
 S: single"
race,,the race of the patient,text,
ethnicity,,the ethnicity of the patient,text,
gender,,the gender of the patient,text,
birthplace,birth place,the birth place,text,
address,,the specific address,text,
patients
original_column_name,column_name,column_description,data_format,value_description
ID,,the unique id of the care plan,text,
START,,the start date of the care plan,date,yyyy-mm-dd
STOP,,the stop date of the care plan,date,"yyyy-mm-dd
commonsense evidence: care plan period:
stop - start"
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the care plan ,real,
DESCRIPTION,,the description of the care plan,text,
REASONCODE,,the reason code,integer,
REASONDESCRIPTION,reason description,the description of the reason why the patient needs the care plan,text,
careplans
original_column_name,column_name,column_description,data_format,value_description
ID,,the unique id of the claim,text,
PATIENT,,the patient id,text,
BILLABLEPERIOD,billable period,the start date of the billable,date,yyyy-mm-dd
ORGANIZATION,,the claim organization,text,
ENCOUNTER,,the medical encounter id,text,
DIAGNOSIS,,the diagnosis,text,
TOTAL,,the length of the billable period,integer,
claims
original_column_name,column_name,column_description,data_format,value_description
START,,the start date of the allergy ,date,mm/dd/yy
STOP,,the stop date of the allergy,date,mm/dd/yy
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the condition ,integer,
DESCRIPTION,,the description of the patient condition,text,
conditions
original_column_name,column_name,column_description,data_format,value_description
START,,the start date of the care plan,date,yyyy-mm-dd
STOP,,the stop date of the care plan,date,"yyyy-mm-dd
commonsense evidence: Time of taking medicine"
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the medication,integer,
DESCRIPTION,,the description of the medication,text,
REASONCODE,,the reason code,integer,
REASONDESCRIPTION,reason description,the description of the reason why the patient take the medication,text,
medications
original_column_name,column_name,column_description,data_format,value_description
ITEM,,the prevalent disease,text,
POPULATION TYPE,population type,the population type - LIVING,text,
OCCURRENCES,,the number of occurrences,integer,
POPULATION COUNT,population count,the number of the counted populations ,integer,
PREVALENCE RATE,prevalence rate,the prevalence rate,real,commonsense evidence: prevalence rate = occurrences / population_count
PREVALENCE PERCENTAGE,prevalence percentage,the prevalence percentage,real,commonsense evidence: prevalence rate = occurrences / population_count * 100
all_prevalences
original_column_name,column_name,column_description,data_format,value_description
DATE,,the date of the observation,date,yyyy-mm-dd
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the observation type,text,
DESCRIPTION,,the description of the observation,text,
VALUE,,the observation value,real,
UNITS,,the units of the observation value,text,"commonsense evidence: DESCRIPTION + VALUE + UNITS could be a fact:
e.g.: body height of patient xxx is 166.03 cm:
body height is in DESCRIPTION 
166.03 is in VALUE 
cm is in UNITS"
observations
original_column_name,column_name,column_description,data_format,value_description
DATE,,the date of the immunization,date,yyyy-mm-dd
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the immunization ,integer,
DESCRIPTION,,the description of the immunization,text,
immunizations
original_column_name,column_name,column_description,data_format,value_description
START,,the start date of the allergy ,text,mm/dd/yy
STOP,,the stop date of the allergy,text,"mm/dd/yy
commonsense evidence: allergy period = stop - start"
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the allergy ,integer,
DESCRIPTION,,the description of the allergy ,text,
allergies
original_column_name,column_name,column_description,data_format,value_description
DATE,,the date of the procedure,date,yyyy-mm-dd
PATIENT,,the patient id,text,
ENCOUNTER,,the medical encounter id,text,
CODE,,the code of the procedure,integer,
DESCRIPTION,,the description of the procedure,text,
REASONCODE,reason code,the code of the reason,integer,
REASONDESCRIPTION,reason description,the description of the reason why the patient take the procedure,text,
procedures