script1.sql 4.5 KB
Newer Older
Richard's avatar
Richard committed
1
2
drop table if exists education_level;
drop table if exists region;
3
4
5
6
7
8
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;
9
10
drop table if exists courseOfferings;
create table courseOfferings(   
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    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),
26
    constraint fk_courses_assessments foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
27
28
29
30
31
32
33
34
35
36
);

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),
37
    constraint fk_courses_vle foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
38
39
40
41
42
43
44
45
46
47
48
49
50
51
);

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),
52
    constraint fk_courses_studentinfo foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
53
54
55
56
57
58
59
60
61
);


create table studentAssessment(
    id_assessment int,
    id_student int,
    date_submitted int,
    is_banked int,
    score int, 
Richard's avatar
Richard committed
62
63
    constraint fk_studentinfo_studentassessment foreign key (id_student)  references studentInfo(id_student),
    constraint fk_assessments_studentassessment foreign key (id_assessment) references assessments(id_assessment)
64
65
66
67
68
69
70
71
72
);


create table studentRegistration(
    code_module varchar(45),
    code_presentation varchar(45),
    id_student int,
    date_registration int,
    date_unregistration int,
Richard's avatar
Richard committed
73
    constraint fk_studentinfo_registration foreign key (id_student) references studentInfo(id_student),
74
    constraint fk_courses_registration foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation)
75
76
77
78
79
80
);


create table studentVle (
    code_module varchar(45),
    code_presentation varchar(45),
81
82
    id_student int, 
    id_site int,
83
    date int,
84
    sum_click int,
Richard's avatar
Richard committed
85
    constraint fk_studentinfo_studentvle foreign key (id_student) references studentInfo(id_student),
86
    constraint fk_courses_studentvle foreign key (code_module, code_presentation) references courseOfferings (code_module, code_presentation),
Richard's avatar
Richard committed
87
    constraint fk_vle_studentvle foreign key (id_site) references vle(id_site)
88
89
90
91
);



Stefan Vercillo's avatar
Stefan Vercillo committed
92
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/courses.csv' ignore into table courseOfferings
93
94
95
96
97
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;
     
Stefan Vercillo's avatar
Stefan Vercillo committed
98
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/assessments.csv' ignore into table assessments
99
100
101
102
103
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;

Stefan Vercillo's avatar
Stefan Vercillo committed
104
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/vle.csv' ignore into table vle
105
106
107
108
109
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;

Richard's avatar
Richard committed
110

Stefan Vercillo's avatar
Stefan Vercillo committed
111
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentInfo.csv' ignore into table studentInfo
112
113
114
115
116
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;

Stefan Vercillo's avatar
Stefan Vercillo committed
117
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentAssessment.csv' ignore into table studentAssessment
118
119
120
121
122
123
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;


Stefan Vercillo's avatar
Stefan Vercillo committed
124
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentRegistration.csv' ignore into table studentRegistration
125
126
127
128
129
130
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
     ignore 1 lines;


Stefan Vercillo's avatar
Stefan Vercillo committed
131
load data LOCAL infile '/home/stefan/stefan/3B/356/ece356/project/data_files/studentVle.csv' ignore into table studentVle
132
133
134
     fields terminated by ','
     enclosed by '"'
     lines terminated by '\r\n'
Richard's avatar
Richard committed
135
    ignore 10500000 lines;
136
137


138
139
140