CREATE TABLE institution_details
(
    unitid                               INTEGER
        constraint institution_details_pk
            primary key,
    chronname                            TEXT,
    city                                 TEXT,
    state                                TEXT,
    level                                TEXT,
    control                              TEXT,
    basic                                TEXT,
    hbcu                                 TEXT,
    flagship                             TEXT,
    long_x                               REAL,
    lat_y                                REAL,
    site                                 TEXT,
    student_count                        INTEGER,
    awards_per_value                     REAL,
    awards_per_state_value               REAL,
    awards_per_natl_value                REAL,
    exp_award_value                      INTEGER,
    exp_award_state_value                INTEGER,
    exp_award_natl_value                 INTEGER,
    exp_award_percentile                 INTEGER,
    ft_pct                               REAL,
    fte_value                            INTEGER,
    fte_percentile                       INTEGER,
    med_sat_value                        TEXT,
    med_sat_percentile                   TEXT,
    aid_value                            INTEGER,
    aid_percentile                       INTEGER,
    endow_value                          TEXT,
    endow_percentile                     TEXT,
    grad_100_value                       REAL,
    grad_100_percentile                  INTEGER,
    grad_150_value                       REAL,
    grad_150_percentile                  INTEGER,
    pell_value                           REAL,
    pell_percentile                      INTEGER,
    retain_value                         REAL,
    retain_percentile                    INTEGER,
    ft_fac_value                         REAL,
    ft_fac_percentile                    INTEGER,
    vsa_year                             TEXT,
    vsa_grad_after4_first                TEXT,
    vsa_grad_elsewhere_after4_first      TEXT,
    vsa_enroll_after4_first              TEXT,
    vsa_enroll_elsewhere_after4_first    TEXT,
    vsa_grad_after6_first                TEXT,
    vsa_grad_elsewhere_after6_first      TEXT,
    vsa_enroll_after6_first              TEXT,
    vsa_enroll_elsewhere_after6_first    TEXT,
    vsa_grad_after4_transfer             TEXT,
    vsa_grad_elsewhere_after4_transfer   TEXT,
    vsa_enroll_after4_transfer           TEXT,
    vsa_enroll_elsewhere_after4_transfer TEXT,
    vsa_grad_after6_transfer             TEXT,
    vsa_grad_elsewhere_after6_transfer   TEXT,
    vsa_enroll_after6_transfer           TEXT,
    vsa_enroll_elsewhere_after6_transfer TEXT,
    similar                              TEXT,
    state_sector_ct                      INTEGER,
    carnegie_ct                          INTEGER,
    counted_pct                          TEXT,
    nicknames                            TEXT,
    cohort_size                          INTEGER
);
institution_details
CREATE TABLE institution_grads
(
    unitid        INTEGER,
    year          INTEGER,
    gender        TEXT,
    race          TEXT,
    cohort        TEXT,
    grad_cohort   TEXT,
    grad_100      TEXT,
    grad_150      TEXT,
    grad_100_rate TEXT,
    grad_150_rate TEXT,
    foreign key (unitid) references institution_details(unitid)
);
institution_grads
CREATE TABLE state_sector_grads
(
    stateid        INTEGER,
    state          TEXT,
    state_abbr     TEXT,
    control        TEXT,
    level          TEXT,
    year           INTEGER,
    gender         TEXT,
    race           TEXT,
    cohort         TEXT,
    grad_cohort    TEXT,
    grad_100       TEXT,
    grad_150       TEXT,
    grad_100_rate  TEXT,
    grad_150_rate  TEXT,
    grad_cohort_ct INTEGER,
    foreign key (state) references institution_details(state),
    foreign key (stateid) references state_sector_details(stateid)
);
state_sector_grads
CREATE TABLE "state_sector_details"
(
    stateid                INTEGER,
    state                  TEXT
        references institution_details (state),
    state_post             TEXT,
    level                  TEXT,
    control                TEXT,
    schools_count          INTEGER,
    counted_pct            TEXT,
    awards_per_state_value TEXT,
    awards_per_natl_value  REAL,
    exp_award_state_value  TEXT,
    exp_award_natl_value   INTEGER,
    state_appr_value       TEXT,
    state_appr_rank        TEXT,
    grad_rate_rank         TEXT,
    awards_per_rank        TEXT,
    primary key (stateid, level, control)
);
state_sector_details
original_column_name,column_name,column_description,data_format,value_description
unitid,Unit ID number,unique Education Unit ID number,integer,
chronname,,Institution name,text,
city,,Institution city,text,
state,,Institution state,text,
level,,Level of institution,text,"commonsense evidence:
4-year: bachelor's degree
2-year: associate's degree"
control,,Control of institution,text,"Public, 
Private not-for-profit, 
Private for-profit"
basic,,Carnegie Foundation for the Advancement of Teaching Basic Classification (2010 version),text,
hbcu,Historically Black College and Universities,Denotes Historically Black College and Universities,text,
flagship,,Denotes Public flagship institutions,text,
long_x,,Institution longitude,real,
lat_y,,Institution latitude,real,"commonsense evidence:
institute Coordinate"
site,,Institution Web site address,text,
student_count,student count,Total number of undergraduates in 2010,integer,
awards_per_value,awards per value,"Completions per 100 FTE (full-time equivalency) undergraduate students (average 2011, 2012, and 2013)",real,
awards_per_state_value,awards per state value,"Completions per 100 FTE (full-time equivalency) undergraduate students, state and sector average",real,"commonsense evidence:
 if his institute's completion (or graduation) rate exceeds the average for the state and sector in which it belongs:
awards_per_value > awards_per_state_value
 if his institute's completion (or graduation) rate falls below the average for the state and sector in which it belongs:
awards_per_value < awards_per_state_value"
awards_per_natl_value,awards per national value,"Completions per 100 FTE (full-time equivalency) undergraduate students, national sector average",real,"commonsense evidence:
 if his institute's completion (or graduation) rate exceeds the average for the nationalin which it belongs:
awards_per_value > awards_per_natl_value
 if his institute's completion (or graduation) rate falls below the average for the state and industry in which it belongs:
awards_per_value < awards_per_natl_value"
exp_award_value,expected award value,Estimated educational spending per academic award in 2013. Includes all certificates and degrees,integer,"expenses related to instruction, research, public service, student services, academic support, institutional support, operations and maintenance. Includes all certificates and degrees"
exp_award_state_value,expected award state value,"Spending per completion, state and sector average",integer,
exp_award_natl_value,expected award national value,"Spending per completion, national sector average",integer,
exp_award_percentile,,,,
ft_pct,Full-time percentage,Percentage of undergraduates who attend full-time,real,
fte_value,Full-time percentage,total number of full-time equivalent undergraduates,integer,
fte_percentile,,,,
med_sat_value,median SAT value,Median estimated SAT value for incoming students,text,
med_sat_percentile,median SAT percentile,Institution's percent rank for median SAT value within sector,text,Institution's percent rank for median SAT value within sector
aid_value,aid value,The average amount of student aid going to undergraduate recipients,integer,
aid_percentile,aid percentile,Institution's percent rank for average amount of student aid going to undergraduate recipients within sector,integer,Institution's percent rank for average amount of student aid going to undergraduate recipients within sector
endow_value,endow value,End-of-year endowment value per full-time equivalent student,text,
endow_percentile,endow percentile,Institution's percent rank for endowment value per full-time equivalent student within sector,text,
grad_100_value,graduation 100 value,"percentage of first-time, full-time, degree-seeking undergraduates who complete a degree or certificate program within 100 percent of expected time (bachelor's-seeking group at 4-year institutions)",real,"%, 
commonsense evidence:
lower means harder to graduate for bachelor"
grad_100_percentile,graduation 100 percentile,Institution's percent rank for completers within 100 percent of normal time within sector,integer,
grad_150_value,graduation 150 value,"percentage of first-time, full-time, degree-seeking undergraduates who complete a degree or certificate program within 150 percent of expected time (bachelor's-seeking group at 4-year institutions)",real,"%, 
commonsense evidence:
lower means harder to graduate for bachelor"
grad_150_percentile,graduation 150 percentile,Institution's percent rank for completers within 150 percent of normal time within sector,integer,
pell_value,pell value,percentage of undergraduates receiving a Pell Grant,real,
pell_percentile,pell percentile,Institution's percent rank for undergraduate Pell recipients within sector,integer,
retain_value,retain value,share of freshman students retained for a second year,real,
retain_percentile,retain percentile,Institution's percent rank for freshman retention percentage within sector,integer,
ft_fac_value,full time faculty value,"Percentage of employees devoted to instruction, research or public service who are full-time and do not work for an associated medical school",real,
ft_fac_percentile,full time faculty percentile,Institution's percent rank for full-time faculty share within sector ,integer,
vsa_year,voluntary system of accountability year,Most recent year of Student Success and Progress Rate data available from the Voluntary System of Accountability,text,
vsa_grad_after4_first,voluntary system of accountability after 4 year first time,"First-time, full-time students who graduated from this institution within four years",text,
vsa_grad_elsewhere_after4_first,voluntary system of accountability graduation elsewhere after 4 year first time,"First-time, full-time students who graduated from another institution within four years",text,
vsa_enroll_after4_first,voluntary system of accountability enrollment after 4 year first time,"First-time, full-time students who are still enrolled at this institution after four years",text,
vsa_enroll_elsewhere_after4_first,voluntary system of accountability enrollment elsewhere after 4 year first time,"First-time, full-time students who are enrolled at another institution after four years",text,
vsa_grad_after6_first,voluntary system of accountability graduation elsewhere after 6 year first time,"First-time, full-time students who graduated from this institution within six years",text,null: not available
vsa_grad_elsewhere_after6_first,voluntary system of accountability graduation elsewhere after 6 year first time,"First-time, full-time students who graduated from another institution within six years",text,
vsa_enroll_after6_first,voluntary system of accountability enrollment after 6 year first time,"First-time, full-time students who are still enrolled at this institution after six years",text,
vsa_enroll_elsewhere_after6_first,voluntary system of accountability enrollment elsewhere after 6 year first time,"First-time, full-time students who are enrolled at another institution after six years",text,
vsa_grad_after4_transfer,voluntary system of accountability transfer after 6 year first time,Full-time transfer students who graduated from this institution within four years,text,
vsa_grad_elsewhere_after4_transfer,voluntary system of accountability graduation elsewhere after 4 year ,Full-time transfer students who graduated from this institution within four years,text,
vsa_enroll_after4_transfer,voluntary system of accountability enrollment after 4 years transfer,Full-time transfer students who are still enrolled at this institution after four years,text,
vsa_enroll_elsewhere_after4_transfer,voluntary system of accountability enrollment elsewhere after 4 years transfer,Full-time transfer students who are enrolled at another institution after four years,text,
vsa_grad_after6_transfer,voluntary system of accountability enrollment elsewhere after 6 years transfer,Full-time transfer students who graduated from this institution within six years,text,
vsa_grad_elsewhere_after6_transfer,voluntary system of accountability graduation elsewhere after 6 years transfer,Full-time transfer students who graduated from another institution within six years,text,
vsa_enroll_after6_transfer,voluntary system of accountability enrollment after 6 years transfer,Full-time transfer students who are still enrolled at this institution after six years,text,
vsa_enroll_elsewhere_after6_transfer,voluntary system of accountability enrollment elsewhere after 6 years transfer,Full-time transfer students who are enrolled at another institution after six years,text,
similar,,,,
state_sector_ct,,,,
carnegie_ct,,,,
counted_pct,,,,
nicknames,,,,
cohort_size,,,,
institution_details
original_column_name,column_name,column_description,data_format,value_description
stateid,state id,state FIPS (Federal Information Processing System) code,integer,
state,state name ,state name ,text,
state_post,,,,
level,,Level of institution,text,"commonsense evidence:
4-year: bachelor's degree
2-year: associate's degree"
control,,,text,"Public, 
Private not-for-profit, 
Private for-profit"
schools_count,,,,
counted_pct,counted percentage,"Percentage of students in the entering class (Fall 2007 at 4-year institutions, Fall 2010 at 2-year institutions) who are first-time, full-time, degree-seeking students and generally part of the official graduation rate",text,
awards_per_state_value,awards per state value,"Completions per 100 FTE (full-time equivalent) undergraduate students, state and sector average",text,
awards_per_natl_value,awards per national value,"Completions per 100 FTE (full-time equivalent) undergraduate students, national sector average",real,
exp_award_state_value,expected award state value,"Spending per completion, state and sector average ",text,
exp_award_natl_value,expected award national value,"Spending per completion, national sector average",integer,
state_appr_value,state appropriation value,State appropriations to higher education in fiscal year 2011 per resident,text,
state_appr_rank,,,,
grad_rate_rank,,,,
awards_per_rank,,,,
state_sector_details
original_column_name,column_name,column_description,data_format,value_description
unitid,unit id,Education Unit ID number,integer,
year,,year of data release,integer,
gender,,gender of students,text,'B' = both genders; 'M' = male; 'F' = female
race,,race/ethnicity of students,text,"commonsense evidence:
'X' = all students; 'Ai' = American Indian; 'A' = Asian; 'B' = Black; 'H' = Hispanic; 'W' = White"
cohort,,degree-seeking cohort type,text,"commonsense evidence:
 '4y bach' = Bachelor's/equivalent-seeking cohort at 4-year institutions; 
 '4y other' = Students seeking another type of degree or certificate at a 4-year institution; 
 '2y all' = Degree-seeking students at 2-year institutions
4-year degree is bachelor's degree
2-year degree is associate's degree"
grad_cohort,graduation cohort,"Number of first-time, full-time, degree-seeking students in the cohort being tracked, minus any exclusions",text,
grad_100,graduation 100,Number of students who graduated within 100 percent of normal/expected time,text,
grad_150,graduation 150,Number of students who graduated within 150 percent of normal/expected time,text,
grad_100_rate,grad_100_rate,Percentage of students who graduated within 100 percent of normal/expected time,text,
grad_150_rate,grad_150_rate,Percentage of students who graduated within 150 percent of normal/expected time,text,
institution_grads
original_column_name,column_name,column_description,data_format,value_description
stateid,state id,state FIPS (Federal Information Processing System) code,integer,
state,state name ,state name ,text,
state_abbr,,,,
control,,,text,"Public, 
Private not-for-profit, 
Private for-profit"
level,,Level of institution,text,"commonsense evidence:
4-year: bachelor's degree
2-year: associate's degree"
year,,year of data release,integer,
gender,,gender of students,text,B' = both genders; 'M' = male; 'F' = female
race,,race/ethnicity of students,text,"commonsense evidence:
'X' = all students; 'Ai' = American Indian; 'A' = Asian; 'B' = Black; 'H' = Hispanic; 'W' = White"
cohort,,degree-seeking cohort type,text,"commonsense evidence:
 '4y bach' = Bachelor's/equivalent-seeking cohort at 4-year institutions; 
 '4y other' = Students seeking another type of degree or certificate at a 4-year institution; 
 '2y all' = Degree-seeking students at 2-year institutions
4-year degree is bachelor's degree
2-year degree is associate's degree"
grad_cohort,graduation cohort,"Number of first-time, full-time, degree-seeking students in the cohort being tracked, minus any exclusions",text,
grad_100,graduation 100,Number of students who graduated within 100 percent of normal/expected time,text,
grad_150,graduation 150,Number of students who graduated within 150 percent of normal/expected time,text,
grad_100_rate,grad_100_rate,Percentage of students who graduated within 100 percent of normal/expected time,text,
grad_150_rate,grad_150_rate,Percentage of students who graduated within 150 percent of normal/expected time,text,
grad_cohort_ct,graduation cohort count,Number of institutions with data included in the cohort,integer,
state_sector_grads