1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
-- System
CREATE TABLE user_role (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE "user" (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
role_id INTEGER NOT NULL,
FOREIGN KEY (role_id) REFERENCES user_role (id)
);
CREATE TABLE post (
id SERIAL PRIMARY KEY,
author_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (author_id) REFERENCES "user" (id)
);
-- Students
create table desertion_risk_class (
id serial primary key,
"name" text
);
create table pucp_unit (
id serial primary key,
"name" text
);
create table administrative_division (
id serial primary key,
"name" text,
country text,
subdivision1 text,
subdivision2 text,
subdivision3 text,
subdivision10 text,
ubigeo text,
"population" integer,
poverty real
);
CREATE TABLE student (
id SERIAL PRIMARY KEY,
pucp_code INTEGER,
first_name TEXT,
last_name TEXT,
email TEXT,
-- district TEXT,
administrative_division_id integer,
pucp_unit_id integer,
tuition_bracket TEXT,
current_semester integer,
current_attendance REAL,
current_craest REAL,
current_ppne3 REAL,
current_merit REAL,
est_study_length INTEGER,
est_desertion_risk_class_id integer,
FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id),
FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id),
FOREIGN KEY (est_desertion_risk_class_id) REFERENCES desertion_risk_class (id)
);
-- alter table student
-- add constraint fk_administrative_division
-- FOREIGN KEY (administrative_division_id)
-- REFERENCES administrative_division (id)
-- ;
-- VIEWS
create view view_student as
select stu.*, adm.subdivision3 as "district", puc.name as "pucp_unit",
des.name as "est_desertion_risk_class"
from student stu
left join administrative_division adm
on stu.administrative_division_id = adm.id
left join pucp_unit as puc
on stu.pucp_unit_id = puc.id
left join desertion_risk_class as des
on stu.est_desertion_risk_class_id = des.id
;
-- SAMPLE DATA (LATER MOVE TO TESTS)
INSERT INTO user_role ("id", "name") VALUES
(1, 'admin'),
(2, 'user')
;
INSERT INTO "user" ("username","password","role_id") VALUES
('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1)
;
INSERT INTO desertion_risk_class ("name") VALUES
('Alto'),
('Bajo'),
('Medio');
INSERT INTO pucp_unit ("name") VALUES
('Facultad de Ciencias e Ingenieria'),
('Estudios Generales Ciencias');
-- sample 2019 data
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
('Peru','','','','','',29009326,28.9,'country'),
('Peru','Lima','','','','150000',8981440,15.4,'region'),
('Peru','Lima','Lima','','','150100',8095747,17.5,'province'),
('Peru','Lima','Lima','LIMA ','Lima Centro','150101',302056,12.3,'district'),
('Peru','Lima','Lima','ANCÓN ','Lima Norte','150102',36401,19.6,'district'),
('Peru','Lima','Lima','ATE ','Lima Este','150103',521692,18.9,'district'),
('Peru','Lima','Lima','BARRANCO ','Lima Centro','150104',33996,5.3,'district'),
('Peru','Lima','Lima','BREÑA ','Lima Centro','150105',82987,8.5,'district'),
('Peru','Lima','Lima','CARABAYLLO ','Lima Norte','150106',237269,26.3,'district'),
('Peru','Lima','Lima','CHACLACAYO ','Lima Este','150107',42884,10.1,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
('Peru','Lima','Lima','CHORRILLOS ','Lima Centro','150108',303913,17.2,'district'),
('Peru','Lima','Lima','CIENEGUILLA ','Lima Este','150109',31160,24.8,'district'),
('Peru','Lima','Lima','COMAS ','Lima Norte','150110',509976,22.3,'district'),
('Peru','Lima','Lima','EL AGUSTINO ','Lima Este','150111',188138,22.1,'district'),
('Peru','Lima','Lima','INDEPENDENCIA ','Lima Norte','150112',215941,21.3,'district'),
('Peru','Lima','Lima','JESÚS MARÍA ','Lima Centro','150113',71139,1.7,'district'),
('Peru','Lima','Lima','LA MOLINA ','Lima Este','150114',144491,0.7,'district'),
('Peru','Lima','Lima','LA VICTORIA ','Lima Centro','150115',193592,14.9,'district'),
('Peru','Lima','Lima','LINCE ','Lima Centro','150116',55733,4.0,'district'),
('Peru','Lima','Lima','LOS OLIVOS ','Lima Norte','150117',339028,13.4,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
('Peru','Lima','Lima','LURIGANCHO ','Lima Este','150118',184593,24.4,'district'),
('Peru','Lima','Lima','LURÍN ','Lima Sur','150119',69282,30.2,'district'),
('Peru','Lima','Lima','MAGDALENA DEL MAR ','Lima Centro','150120',54116,2.3,'district'),
('Peru','Lima','Lima','PUEBLO LIBRE','Lima Centro','150121',77892,2.0,'district'),
('Peru','Lima','Lima','MIRAFLORES ','Lima Centro','150122',86920,0.8,'district'),
('Peru','Lima','Lima','PACHACAMAC ','Lima Este','150123',81145,32.9,'district'),
('Peru','Lima','Lima','PUCUSANA ','Lima Sur','150124',12148,26.7,'district'),
('Peru','Lima','Lima','PUENTE PIEDRA ','Lima Norte','150125',263594,35.6,'district'),
('Peru','Lima','Lima','PUNTA HERMOSA ','Lima Sur','150126',6309,7.4,'district'),
('Peru','Lima','Lima','PUNTA NEGRA ','Lima Sur','150127',5951,9.5,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
('Peru','Lima','Lima','RIMAC ','Lima Centro','150128',178869,16.5,'district'),
('Peru','Lima','Lima','SAN BARTOLO ','Lima Sur','150129',6368,9.7,'district'),
('Peru','Lima','Lima','SAN BORJA ','Lima Centro','150130',111208,0.8,'district'),
('Peru','Lima','Lima','SAN ISIDRO ','Lima Centro','150131',58920,0.6,'district'),
('Peru','Lima','Lima','SAN JUAN DE LURIGANCHO ','Lima Este','150132',962554,27.0,'district'),
('Peru','Lima','Lima','SAN JUAN DE MIRAFLORES ','Lima Sur','150133',382531,19.7,'district'),
('Peru','Lima','Lima','SAN LUIS ','Lima Este','150134',57080,7.1,'district'),
('Peru','Lima','Lima','SAN MARTÍN DE PORRES ','Lima Norte','150135',620193,10.9,'district'),
('Peru','Lima','Lima','SAN MIGUEL ','Lima Centro','150136',134666,2.3,'district'),
('Peru','Lima','Lima','SANTA ANITA ','Lima Este','150137',199282,12.0,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
('Peru','Lima','Lima','SANTA MARÍA DEL MAR ','Lima Sur','150138',924,4.0,'district'),
('Peru','Lima','Lima','SANTA ROSA ','Lima Norte','150139',12641,12.0,'district'),
('Peru','Lima','Lima','SANTIAGO DE SURCO ','Lima Centro','150140',309889,3.3,'district'),
('Peru','Lima','Lima','SURQUILLO ','Lima Centro','150141',93271,5.2,'district'),
('Peru','Lima','Lima','VILLA EL SALVADOR ','Lima Sur','150142',410313,25.9,'district'),
('Peru','Lima','Lima','VILLA MARÍA DEL TRIUNFO ','Lima Sur','150143',404692,21.1,'district');
INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_desertion_risk_class_id) VALUES
(20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,1),
(20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',3,0.8,58.35,55.23,0.1532,10,3),
(20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',6,0.8,58.35,55.23,0.1532,10,3),
(20171237,'Diana','Diaz','ddiaz@pucp.edu.pe',43,2,'G4',6,0.85,60.00,57.00,0.2000,9,1),
(20171238,'Elena','Espino','eespino@pucp.edu.pe',44,2,'G3',7,0.90,62.00,59.00,0.2500,8,2),
(20171239,'Fernando','Fuentes','ffuentes@pucp.edu.pe',45,1,'G2',8,0.95,64.00,61.00,0.3000,7,1),
(20171240,'Gabriela','Gomez','ggomez@pucp.edu.pe',46,2,'G1',4,0.75,56.00,53.00,0.1000,11,3),
(20171241,'Hector','Herrera','hherrera@pucp.edu.pe',7,1,'G5',3,0.70,54.00,51.00,0.0500,12,2),
(20171242,'Irene','Ibarra','iibarra@pucp.edu.pe',8,1,'G4',2,0.65,52.00,49.00,0.0000,13,3),
(20171243,'Jorge','Juarez','jjuarez@pucp.edu.pe',9,1,'G3',1,0.60,50.00,47.00,0.0000,14,3),
(20171244,'Karla','Katz','kkatz@pucp.edu.pe',10,2,'G4',5,0.83,59.00,56.00,0.1600,10,2),
(20171245,'Luis','Lima','llima@pucp.edu.pe',11,1,'G3',6,0.86,61.00,58.00,0.1700,9,1),
(20171246,'Maria','Mora','mmora@pucp.edu.pe',12,1,'G2',7,0.89,63.00,60.00,0.1800,8,3),
(20171247,'Nestor','Nava','nnava@pucp.edu.pe',13,2,'G1',8,0.92,65.00,62.00,0.1900,7,3),
(20171248,'Olga','Ortiz','oortiz@pucp.edu.pe',14,1,'G5',4,0.78,57.00,54.00,0.1500,11,3),
(20171249,'Pablo','Perez','pperez@pucp.edu.pe',15,1,'G4',3,0.74,55.00,52.00,0.1400,12,1),
(20171250,'Quincy','Quintero','qquintero@pucp.edu.pe',16,1,'G3',2,0.71,53.00,50.00,0.1300,13,3),
(20171251,'Rita','Ramirez','rramirez@pucp.edu.pe',17,1,'G2',1,0.69,51.00,48.00,0.1200,14,2),
(20171252,'Sofia','Sanchez','ssanchez@pucp.edu.pe',18,1,'G1',5,0.85,59.00,57.00,0.2000,10,1),
(20171253,'Tomas','Torres','ttorres@pucp.edu.pe',19,2,'G5',6,0.88,61.00,59.00,0.2100,9,3),
(20171254,'Ursula','Urrutia','uurrutia@pucp.edu.pe',10,1,'G4',7,0.91,63.00,61.00,0.2200,8,3),
(20171255,'Vicente','Velasquez','vvelasquez@pucp.edu.pe',11,1,'G3',8,0.94,65.00,63.00,0.2300,7,3),
(20171256,'Wanda','Walsh','wwalsh@pucp.edu.pe',12,1,'G2',4,0.77,56.00,53.00,0.1100,11,3),
(20171257,'Ximena','Xu','xxu@pucp.edu.pe',13,1,'G1',3,0.72,54.00,51.00,0.1000,12,1),
(20171258,'Zabrina','Zoo','zzoo@pucp.edu.pe',14,1,'G9',3,0.72,54.00,51.00,0.1000,12,1),
(20201234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,1),
(20201235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',3,0.8,58.35,55.23,0.1532,10,3),
(20201236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',6,0.8,58.35,55.23,0.1532,10,3),
(20201237,'Diana','Diaz','ddiaz@pucp.edu.pe',43,2,'G4',6,0.85,60.00,57.00,0.2000,9,1),
(20201238,'Elena','Espino','eespino@pucp.edu.pe',44,2,'G3',7,0.90,62.00,59.00,0.2500,8,2),
(20201239,'Fernando','Fuentes','ffuentes@pucp.edu.pe',45,1,'G2',8,0.95,64.00,61.00,0.3000,7,1),
(20201240,'Gabriela','Gomez','ggomez@pucp.edu.pe',46,2,'G1',4,0.75,56.00,53.00,0.1000,11,3),
(20201241,'Hector','Herrera','hherrera@pucp.edu.pe',7,1,'G5',3,0.70,54.00,51.00,0.0500,12,2),
(20201242,'Irene','Ibarra','iibarra@pucp.edu.pe',8,1,'G4',2,0.65,52.00,49.00,0.0000,13,3),
(20201243,'Jorge','Juarez','jjuarez@pucp.edu.pe',9,1,'G3',1,0.60,50.00,47.00,0.0000,14,3),
(20201244,'Karla','Katz','kkatz@pucp.edu.pe',10,2,'G4',5,0.83,59.00,56.00,0.1600,10,2),
(20201245,'Luis','Lima','llima@pucp.edu.pe',11,1,'G3',6,0.86,61.00,58.00,0.1700,9,1),
(20201246,'Maria','Mora','mmora@pucp.edu.pe',12,1,'G2',7,0.89,63.00,60.00,0.1800,8,3),
(20201247,'Nestor','Nava','nnava@pucp.edu.pe',13,2,'G1',8,0.92,65.00,62.00,0.1900,7,3),
(20201248,'Olga','Ortiz','oortiz@pucp.edu.pe',14,1,'G5',4,0.78,57.00,54.00,0.1500,11,3),
(20201249,'Pablo','Perez','pperez@pucp.edu.pe',15,1,'G4',3,0.74,55.00,52.00,0.1400,12,1),
(20201250,'Quincy','Quintero','qquintero@pucp.edu.pe',16,1,'G3',2,0.71,53.00,50.00,0.1300,13,3),
(20201251,'Rita','Ramirez','rramirez@pucp.edu.pe',17,1,'G2',1,0.69,51.00,48.00,0.1200,14,2),
(20201252,'Sofia','Sanchez','ssanchez@pucp.edu.pe',18,1,'G1',5,0.85,59.00,57.00,0.2000,10,1),
(20201253,'Tomas','Torres','ttorres@pucp.edu.pe',19,2,'G5',6,0.88,61.00,59.00,0.2100,9,3),
(20201254,'Ursula','Urrutia','uurrutia@pucp.edu.pe',10,1,'G4',7,0.91,63.00,61.00,0.2200,8,3),
(20201255,'Vicente','Velasquez','vvelasquez@pucp.edu.pe',11,1,'G3',8,0.94,65.00,63.00,0.2300,7,3),
(20201256,'Wanda','Walsh','wwalsh@pucp.edu.pe',12,1,'G2',4,0.77,56.00,53.00,0.1100,11,3),
(20201257,'Ximena','Xu','xxu@pucp.edu.pe',13,1,'G1',3,0.72,54.00,51.00,0.1000,12,1),
(20201258,'Zabrina','Zoo','zzoo@pucp.edu.pe',14,1,'G9',3,0.72,54.00,51.00,0.1000,12,1)
;
-- (20171237,'Dante','Diaz','ddiaz@pucp.edu.pe',13,1,'G8',8,0.4,48.25,52.12,0.70,14,1),
-- (20171238,'Eder','Entero','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171239,'Francisco','Falconi','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171240,'Gabriel','Gonzales','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171241,'Hector','Hall','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171242,'Ignacio','Indigo','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171243,'Jose','Juanito','eentero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Karla','Katz','kkatz@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Luis','Lima','llima@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Maria','Mora','mmora@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Nestor','Nava','nnava@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Olga','Ortiz','oortiz@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Pablo','Perez','pperez@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Quincy','Quintero','qquintero@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- (20171244,'Rita','Ramirez','rramirez@pucp.edu.pe',8,2,'G4',2,0.4,50.00,50.00,0.50,12,0),
-- ;
|
