CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
);
country
CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
);
ranking_system
CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
);
ranking_criteria
CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
);
university
CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
);
university_ranking_year
CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
);
university_year
original_column_name,column_name,column_description,data_format,value_description
university_id,university id,id of the university,integer,
year,,,integer,
num_students,number of students,the total number of students for the year,integer,
student_staff_ratio,student staff ratio,,real,commonsense evidence: student_staff_ratio = number of students / number of staff
pct_international_students,pct internation student,the percentage of international students among all students,real,commonsense evidence: pct_international_student = number of interbational students / number of students
pct_female_students,pct female students,the percentage of female students,real,commonsense evidence: pct_female_students = number of female students / number of students
university_year
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying ranking criteria,integer,
ranking_system_id,ranking system id,id number identifying ranking system,integer,
criteria_name,criteria name,name of the criteria,text,
ranking_criteria
original_column_name,column_name,column_description,data_format,value_description
university_id,university id,the id of the university,integer,
ranking_criteria_id,ranking criteria id,the id of the ranking criteria,integer,
year,,ranking year,integer,
score,,ranking score,integer,
university_ranking_year
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying university,integer,
country_id, country id,the country where the university locates,text,
university_name,university name,name of the university,text,
university
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying ranking system,integer,
system_name,system name,id number identifying ranking system,text,
ranking_system
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying country,integer,
country_name,country name,the name of the country,text,
country