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
|
-- 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 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_dessertion_risk TEXT,
FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id),
FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id)
);
-- alter table student
-- add constraint fk_administrative_division
-- FOREIGN KEY (administrative_division_id)
-- REFERENCES administrative_division (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 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_dessertion_risk) VALUES
(20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'),
(20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,'H'),
(20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L');
|
