script2.sql 6.53 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21


-- ===== Course Refactor ===== --
-- Drop the FK
ALTER TABLE studentVle DROP CONSTRAINT fk_courses_studentvle;
ALTER TABLE studentVle DROP INDEX fk_courses_studentvle;

ALTER TABLE studentRegistration DROP CONSTRAINT fk_courses_registration;
ALTER TABLE studentRegistration DROP INDEX fk_courses_registration;

ALTER TABLE studentInfo DROP CONSTRAINT fk_courses_studentinfo;
ALTER TABLE studentInfo  DROP INDEX fk_courses_studentinfo;

ALTER TABLE vle DROP CONSTRAINT fk_courses_vle;
ALTER TABLE vle DROP INDEX fk_courses_vle;

ALTER TABLE assessments DROP CONSTRAINT fk_courses_assessments;
ALTER TABLE assessments  DROP INDEX fk_courses_assessments;

-- Add new PK column 
ALTER TABLE courseOfferings DROP PRIMARY KEY;
Richard's avatar
Richard committed
22
ALTER TABLE courseOfferings ADD course_offering_id int auto_increment primary key; 
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- ---------
-- TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
-- SELECT  * 
-- FROM information_schema.table_constraints
-- WHERE table_name='studentvle';
-- ------

-- Add ID to the tables
ALTER TABLE studentVle ADD course_offering_id int;
ALTER TABLE studentVle ADD CONSTRAINT fk_courseid_studentVle FOREIGN KEY (course_offering_id) REFERENCES courseOfferings (course_offering_id);
UPDATE studentVle LEFT JOIN courseOfferings ON (studentVle.code_module = courseOfferings.code_module AND studentVle.code_presentation = courseOfferings.code_presentation)
SET studentVle.course_offering_id = courseOfferings.course_offering_id;

ALTER TABLE studentRegistration ADD course_offering_id int;
ALTER TABLE studentRegistration ADD CONSTRAINT fk_courseid_studentRegistration FOREIGN KEY (course_offering_id) REFERENCES courseOfferings (course_offering_id);
UPDATE studentRegistration LEFT JOIN courseOfferings ON (studentRegistration.code_module = courseOfferings.code_module AND studentRegistration.code_presentation = courseOfferings.code_presentation)
SET studentRegistration.course_offering_id = courseOfferings.course_offering_id;

Richard's avatar
Richard committed
42
ALTER TABLE studentInfo ADD course_offering_id int;
43
44
45
46
ALTER TABLE studentInfo ADD CONSTRAINT fk_courseid_studentInfo FOREIGN KEY (course_offering_id) REFERENCES courseOfferings (course_offering_id);
UPDATE studentInfo LEFT JOIN courseOfferings ON (studentInfo.code_module = courseOfferings.code_module AND studentInfo.code_presentation = courseOfferings.code_presentation)
SET studentInfo.course_offering_id = courseOfferings.course_offering_id;

Richard's avatar
Richard committed
47
48
ALTER TABLE vle ADD course_offering_id int;
ALTER TABLE vle ADD CONSTRAINT fk_courseid_vle FOREIGN KEY (course_offering_id) REFERENCES courseOfferings (course_offering_id);
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
UPDATE vle LEFT JOIN courseOfferings ON (vle.code_module = courseOfferings.code_module AND vle.code_presentation = courseOfferings.code_presentation)
SET vle.course_offering_id = courseOfferings.course_offering_id;

ALTER TABLE assessments ADD course_offering_id int;
ALTER TABLE assessments ADD CONSTRAINT fk_courseid_assessments FOREIGN KEY (course_offering_id) REFERENCES courseOfferings (course_offering_id);
UPDATE assessments LEFT JOIN courseOfferings ON (assessments.code_module = courseOfferings.code_module AND assessments.code_presentation = courseOfferings.code_presentation)
SET assessments.course_offering_id = courseOfferings.course_offering_id;

-- Remove modules and presentations
ALTER TABLE studentVle DROP COLUMN code_module;
ALTER TABLE studentVle DROP COLUMN code_presentation;

ALTER TABLE studentRegistration DROP COLUMN code_module;
ALTER TABLE studentRegistration DROP COLUMN code_presentation;

ALTER TABLE studentInfo DROP COLUMN code_module;
ALTER TABLE studentInfo DROP COLUMN code_presentation;


ALTER TABLE vle DROP COLUMN code_module;
ALTER TABLE vle DROP COLUMN code_presentation;


ALTER TABLE assessments DROP COLUMN code_module;
ALTER TABLE assessments DROP COLUMN code_presentation;

-- ===== Moving student registration stuff into studentinfo =====--

-- ALTER TABLE studentInfo ADD date_registration datetime;
-- ALTER TABLE studentInfo ADD date_unregistration datetime;

-- INSERT INTO studentInfo (date_registration, date_unregistration) SELECT date_registration, date_unregistration
-- FROM studentRegistration WHERE studentInfo.course_offering_id == studentRegistration.course_offering_id;




-- imd_band -> imd_upperbound
ALTER TABLE studentInfo ADD age_range ENUM('0-35','35-55','55<=');
UPDATE studentInfo SET age_range = '0-35' WHERE STRCMP(age_band, '0-35');
UPDATE studentInfo SET age_range = '35-55' WHERE STRCMP(age_band, '35-55');
UPDATE studentInfo SET age_range = '55<=' WHERE STRCMP(age_band, '55<=');
ALTER TABLE studentInfo DROP COLUMN age_band;

-- move region to new table
CREATE TABLE region (
    regionid int primary key AUTO_INCREMENT,
    name varchar(20)
);

INSERT INTO region (name)
SELECT DISTINCT region FROM studentInfo;

ALTER TABLE studentInfo ADD regionid int;
ALTER TABLE studentInfo ADD CONSTRAINT fk_regionid_studentInfo FOREIGN KEY (regionid) REFERENCES region (regionid);

UPDATE studentInfo JOIN region ON (studentInfo.region = region.name) SET studentInfo.regionid = region.regionid;
ALTER TABLE studentInfo DROP COLUMN region;

-- education to own table


CREATE TABLE educationLevel (
    education_rank int primary key AUTO_INCREMENT,
    education_level varchar(40)
);

INSERT INTO educationLevel (education_level)
SELECT DISTINCT highest_education FROM studentInfo;

ALTER TABLE studentInfo ADD education_rank int;

ALTER TABLE studentInfo ADD CONSTRAINT fk_education_rank_studentInfo FOREIGN KEY (education_rank) REFERENCES educationLevel (education_rank);


UPDATE studentInfo JOIN educationLevel ON (studentInfo.highest_education = educationLevel.education_level) SET studentInfo.education_rank = educationLevel.education_rank;


-- gender to enum
ALTER TABLE studentInfo ADD gender_enum ENUM('M','F');
UPDATE studentInfo SET gender_enum = 'M' WHERE STRCMP(gender, 'M');
UPDATE studentInfo SET gender_enum = 'F' WHERE STRCMP(gender, 'F');
ALTER TABLE studentInfo DROP COLUMN gender;
ALTER TABLE studentInfo RENAME COLUMN gender_enum TO gender;

-- ===== studentAssessment Changes ===== --
-- new failed boolean column
ALTER TABLE studentAssessment ADD failed boolean;
UPDATE studentAssessment SET failed = true WHERE score < 40;
UPDATE studentAssessment SET failed = false WHERE score >= 40;

-- ===== studentVle Changes ===== --
-- rename column for better readability
ALTER TABLE studentVle RENAME COLUMN sum_click TO interactions;
-- add primary key
ALTER TABLE studentVle ADD id int auto_increment primary key; 


alter table studentAssessment add 
    primary key (id_assessment, id_student);



ALTER TABLE studentRegistration 
    ADD COLUMN id_registration int auto_increment not NULL FIRST,
    ADD PRIMARY KEY (id_registration);