script1.sql

drop table if exists education_level;
drop table if exists region;
drop table if exists studentVle;
drop table if exists studentRegistration;
drop table if exists studentAssessment;
drop table if exists studentInfo;
drop table if exists vle;
drop table if exists assessments;
drop table if exists courseOfferings;
create table courseOfferings(   
    code_module varchar(45),
    code_presentation varchar(45),
    module_presentation_length int(3),
    primary key (code_module, code_presentation)
);


create table assessments(
    code_module varchar(45),
    code_presentation varchar(45),
    id_assessment int,
    assessment_type varchar(45),
    date VARCHAR(10), -- ?????
    weight VARCHAR(10),
    primary key (id_assessment),
    constraint fk_courses_assessments foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
);

create table vle (
    id_site int,
    code_module varchar(45),
    code_presentation varchar(45),
    activity_type varchar(30),
    week_from int,
    week_to int,
    primary key (id_site),
    constraint fk_courses_vle foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
);

create table studentInfo(
    code_module varchar(45),
    code_presentation varchar(45),
    id_student int,
    gender varchar(1),
    region varchar(50),
    highest_education varchar(100),
    imd_band varchar(10),
    age_band varchar (10),
    num_of_prev_attempts int,
    studied_credits int, 
    primary key (id_student),
    constraint fk_courses_studentinfo foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
);


create table studentAssessment(
    id_assessment int,
    id_student int,
    date_submitted int,
    is_banked int,
    score int, 
    constraint fk_studentinfo_studentassessment foreign key (id_student)  references studentInfo(id_student),
    constraint fk_assessments_studentassessment foreign key (id_assessment) references assessments(id_assessment)
);


create table studentRegistration(
    code_module varchar(45),
    code_presentation varchar(45),
    id_student int,
    date_registration int,
    date_unregistration int,
    constraint fk_studentinfo_registration foreign key (id_student) references studentInfo(id_student),
    constraint fk_courses_registration foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
);


create table studentVle (
    code_module varchar(45),
    code_presentation varchar(45),
    id_student int, 
    id_site int,
    date int,
    sum_click int,
    constraint fk_studentinfo_studentvle foreign key (id_student) references studentInfo(id_student),
    constraint fk_courses_studentvle foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation),
    constraint fk_vle_studentvle foreign key (id_site) references vle(id_site)
);



load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/courses.csv' ignore into table courseOfferings
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;
     
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/assessments.csv' ignore into table assessments
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;

load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/vle.csv' ignore into table vle
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;


load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentInfo.csv' ignore into table studentInfo
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;

load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentAssessment.csv' ignore into table studentAssessment
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;


load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentRegistration.csv' ignore into table studentRegistration
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;


load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentVle.csv' ignore into table studentVle
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
    ignore 10500000 lines;