@danielsilva7577 / maps-conect
Code Commits Issues Pulls Sessions CI/CD
main
desvergue-de-alexandro main
sql 410 lines 20.8 KB
Raw
1 -- =====================================================================
2 -- MAPS Connect - Esquema de Base de Datos (MySQL 8.0, 3FN)
3 -- Universidad Tecmilenio - Plataforma Académica y de Mentoría MAPS
4 --
5 -- Basado en: "Documento de Especificación de Proyecto: Plataforma
6 -- Académica y de Mentoría MAPS" - Sección 6 (Catálogo de 22 Entidades)
7 -- y Sección 7 (Reglas de Integridad y Restricciones Técnicas).
8 --
9 -- Ejecutar dentro del esquema correspondiente, por ejemplo:
10 -- USE maps_conect_dev; -- perfil dev
11 -- USE maps_conect; -- perfil prod
12 -- =====================================================================
13
14 SET FOREIGN_KEY_CHECKS = 0;
15
16 -- =====================================================================
17 -- Módulo 1: Gestión Académica y Modelo MAPS
18 -- =====================================================================
19
20 -- 1. carreras: programas profesionales ofertados
21 CREATE TABLE carreras (
22 id_carrera INT AUTO_INCREMENT PRIMARY KEY,
23 nombre VARCHAR(150) NOT NULL,
24 clave VARCHAR(20) NOT NULL,
25 descripcion TEXT,
26 activa BOOLEAN NOT NULL DEFAULT TRUE,
27 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 CONSTRAINT uq_carreras_nombre UNIQUE (nombre),
29 CONSTRAINT uq_carreras_clave UNIQUE (clave)
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
31
32 -- 2. certificados: especializaciones y microcredenciales MAPS
33 CREATE TABLE certificados (
34 id_certificado INT AUTO_INCREMENT PRIMARY KEY,
35 nombre VARCHAR(150) NOT NULL,
36 descripcion TEXT,
37 area VARCHAR(100),
38 activo BOOLEAN NOT NULL DEFAULT TRUE,
39 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
40 CONSTRAINT uq_certificados_nombre UNIQUE (nombre)
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
42
43 -- 3. materias: asignaturas curriculares del plan de estudios
44 CREATE TABLE materias (
45 id_materia INT AUTO_INCREMENT PRIMARY KEY,
46 clave VARCHAR(20) NOT NULL,
47 nombre VARCHAR(150) NOT NULL,
48 creditos INT NOT NULL DEFAULT 0,
49 tipo ENUM('TRONCO_COMUN', 'DISCIPLINAR', 'CERTIFICADO', 'BIENESTAR') NOT NULL,
50 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
51 CONSTRAINT uq_materias_clave UNIQUE (clave),
52 CONSTRAINT chk_materias_creditos CHECK (creditos >= 0)
53 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
54
55 -- =====================================================================
56 -- Módulo 2: Usuarios, Perfiles y Roles
57 -- =====================================================================
58
59 -- 6. usuarios: credenciales, rol y autenticación general
60 CREATE TABLE usuarios (
61 id_usuario INT AUTO_INCREMENT PRIMARY KEY,
62 correo VARCHAR(150) NOT NULL,
63 contrasena_hash VARCHAR(255) NOT NULL,
64 nombre VARCHAR(100) NOT NULL,
65 apellido VARCHAR(100) NOT NULL,
66 rol ENUM('ESTUDIANTE', 'DOCENTE', 'EGRESADO', 'ADMIN') NOT NULL,
67 activo BOOLEAN NOT NULL DEFAULT TRUE,
68 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
69 fecha_actualizacion DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
70 CONSTRAINT uq_usuarios_correo UNIQUE (correo),
71 -- Restricción de Dominio Institucional
72 CONSTRAINT chk_usuarios_correo_institucional CHECK (
73 correo LIKE '%@tecmilenio.mx' OR correo LIKE '%@servicios.tecmilenio.mx'
74 )
75 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
76
77 -- 4. plan_estudios: relación N:M de materias por carrera y semestre sugerido
78 CREATE TABLE plan_estudios (
79 id_plan INT AUTO_INCREMENT PRIMARY KEY,
80 id_carrera INT NOT NULL,
81 id_materia INT NOT NULL,
82 semestre_sugerido INT NOT NULL,
83 CONSTRAINT fk_plan_estudios_carrera FOREIGN KEY (id_carrera) REFERENCES carreras (id_carrera) ON DELETE CASCADE,
84 CONSTRAINT fk_plan_estudios_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE CASCADE,
85 CONSTRAINT uq_plan_estudios UNIQUE (id_carrera, id_materia),
86 -- Restricción de Rango Semestral
87 CONSTRAINT chk_plan_estudios_semestre CHECK (semestre_sugerido BETWEEN 1 AND 12)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
89
90 -- 5. certificado_materias: relación N:M de asignaturas que integran cada certificado
91 CREATE TABLE certificado_materias (
92 id_certificado_materia INT AUTO_INCREMENT PRIMARY KEY,
93 id_certificado INT NOT NULL,
94 id_materia INT NOT NULL,
95 CONSTRAINT fk_certificado_materias_certificado FOREIGN KEY (id_certificado) REFERENCES certificados (id_certificado) ON DELETE CASCADE,
96 CONSTRAINT fk_certificado_materias_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE CASCADE,
97 CONSTRAINT uq_certificado_materias UNIQUE (id_certificado, id_materia)
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
99
100 -- 7. estudiantes: datos específicos del alumno (matrícula, semestre, propósito)
101 CREATE TABLE estudiantes (
102 id_estudiante INT AUTO_INCREMENT PRIMARY KEY,
103 id_usuario INT NOT NULL,
104 id_carrera INT NULL,
105 matricula VARCHAR(20) NOT NULL,
106 semestre_actual INT NOT NULL,
107 proposito_vida TEXT,
108 puntos_reputacion INT NOT NULL DEFAULT 0,
109 CONSTRAINT fk_estudiantes_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
110 CONSTRAINT fk_estudiantes_carrera FOREIGN KEY (id_carrera) REFERENCES carreras (id_carrera) ON DELETE SET NULL,
111 CONSTRAINT uq_estudiantes_usuario UNIQUE (id_usuario),
112 CONSTRAINT uq_estudiantes_matricula UNIQUE (matricula),
113 -- Restricción de Rango Semestral
114 CONSTRAINT chk_estudiantes_semestre CHECK (semestre_actual BETWEEN 1 AND 12)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
116
117 -- 8. estudiante_certificados: ruta MAPS (hasta 3 certificados elegidos por el alumno)
118 CREATE TABLE estudiante_certificados (
119 id_estudiante_certificado INT AUTO_INCREMENT PRIMARY KEY,
120 id_estudiante INT NOT NULL,
121 id_certificado INT NOT NULL,
122 fecha_eleccion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
123 CONSTRAINT fk_estudiante_certificados_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiantes (id_estudiante) ON DELETE CASCADE,
124 CONSTRAINT fk_estudiante_certificados_certificado FOREIGN KEY (id_certificado) REFERENCES certificados (id_certificado) ON DELETE CASCADE,
125 CONSTRAINT uq_estudiante_certificados UNIQUE (id_estudiante, id_certificado)
126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
127
128 -- 9. profesores: nómina, especialidad, materias asignadas, biografía y disponibilidad
129 CREATE TABLE profesores (
130 id_profesor INT AUTO_INCREMENT PRIMARY KEY,
131 id_usuario INT NOT NULL,
132 numero_nomina VARCHAR(20) NOT NULL,
133 area_especialidad VARCHAR(150),
134 biografia TEXT,
135 horario_asesorias VARCHAR(255),
136 disponible_chat BOOLEAN NOT NULL DEFAULT TRUE,
137 CONSTRAINT fk_profesores_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
138 CONSTRAINT uq_profesores_usuario UNIQUE (id_usuario),
139 CONSTRAINT uq_profesores_nomina UNIQUE (numero_nomina)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
141
142 -- 10. profesores_materias: relación N:M de materias impartidas por ciclo académico
143 CREATE TABLE profesores_materias (
144 id_profesor_materia INT AUTO_INCREMENT PRIMARY KEY,
145 id_profesor INT NOT NULL,
146 id_materia INT NOT NULL,
147 ciclo VARCHAR(20) NOT NULL,
148 CONSTRAINT fk_profesores_materias_profesor FOREIGN KEY (id_profesor) REFERENCES profesores (id_profesor) ON DELETE CASCADE,
149 CONSTRAINT fk_profesores_materias_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE CASCADE,
150 CONSTRAINT uq_profesores_materias UNIQUE (id_profesor, id_materia, ciclo)
151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
152
153 -- =====================================================================
154 -- Módulo 3: Foro de Dudas y Respuestas Colaborativas
155 -- =====================================================================
156
157 -- 11. publicaciones: dudas académicas publicadas por estudiantes
158 CREATE TABLE publicaciones (
159 id_publicacion INT AUTO_INCREMENT PRIMARY KEY,
160 id_usuario INT NOT NULL,
161 id_carrera INT NOT NULL,
162 id_materia INT NULL,
163 titulo VARCHAR(200) NOT NULL,
164 contenido TEXT NOT NULL,
165 id_respuesta_aceptada INT NULL,
166 estado ENUM('ABIERTA', 'RESUELTA', 'MODERADA') NOT NULL DEFAULT 'ABIERTA',
167 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
168 CONSTRAINT fk_publicaciones_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
169 CONSTRAINT fk_publicaciones_carrera FOREIGN KEY (id_carrera) REFERENCES carreras (id_carrera) ON DELETE CASCADE,
170 CONSTRAINT fk_publicaciones_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE SET NULL
171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
172
173 -- 12. respuestas: soluciones y comentarios de la comunidad a cada duda
174 CREATE TABLE respuestas (
175 id_respuesta INT AUTO_INCREMENT PRIMARY KEY,
176 id_publicacion INT NOT NULL,
177 id_usuario INT NOT NULL,
178 contenido TEXT NOT NULL,
179 es_solucion BOOLEAN NOT NULL DEFAULT FALSE,
180 verificado_docente BOOLEAN NOT NULL DEFAULT FALSE,
181 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
182 CONSTRAINT fk_respuestas_publicacion FOREIGN KEY (id_publicacion) REFERENCES publicaciones (id_publicacion) ON DELETE CASCADE,
183 CONSTRAINT fk_respuestas_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE
184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
185
186 -- Enlace tardío: la publicación puede marcar una respuesta como solución aceptada
187 ALTER TABLE publicaciones
188 ADD CONSTRAINT fk_publicaciones_respuesta_aceptada
189 FOREIGN KEY (id_respuesta_aceptada) REFERENCES respuestas (id_respuesta) ON DELETE SET NULL;
190
191 -- =====================================================================
192 -- Módulo 4: Tips Académicos y Sistema de Reputación
193 -- =====================================================================
194
195 -- 13. tips_academicos: recomendaciones y técnicas de estudio por materia
196 CREATE TABLE tips_academicos (
197 id_tip INT AUTO_INCREMENT PRIMARY KEY,
198 id_usuario INT NOT NULL,
199 id_materia INT NULL,
200 contenido TEXT NOT NULL,
201 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
202 CONSTRAINT fk_tips_academicos_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
203 CONSTRAINT fk_tips_academicos_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE SET NULL
204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
205
206 -- 14. votos_tips: control de votos únicos por usuario para ranking de tips
207 CREATE TABLE votos_tips (
208 id_voto INT AUTO_INCREMENT PRIMARY KEY,
209 id_tip INT NOT NULL,
210 id_usuario INT NOT NULL,
211 fecha_voto DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
212 CONSTRAINT fk_votos_tips_tip FOREIGN KEY (id_tip) REFERENCES tips_academicos (id_tip) ON DELETE CASCADE,
213 CONSTRAINT fk_votos_tips_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
214 -- Voto de utilidad único por usuario
215 CONSTRAINT uq_votos_tips UNIQUE (id_tip, id_usuario)
216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
217
218 -- =====================================================================
219 -- Módulo 5: Repositorio de Apuntes y Material de Estudio
220 -- =====================================================================
221
222 -- 15. recursos_academicos: metadatos de archivos y apuntes subidos por materia
223 CREATE TABLE recursos_academicos (
224 id_recurso INT AUTO_INCREMENT PRIMARY KEY,
225 id_usuario INT NOT NULL,
226 id_materia INT NOT NULL,
227 titulo VARCHAR(200) NOT NULL,
228 descripcion TEXT,
229 url_archivo VARCHAR(255) NOT NULL,
230 tipo_archivo VARCHAR(50),
231 descargas INT NOT NULL DEFAULT 0,
232 reportes INT NOT NULL DEFAULT 0,
233 oculto BOOLEAN NOT NULL DEFAULT FALSE,
234 fecha_subida DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
235 CONSTRAINT fk_recursos_academicos_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
236 CONSTRAINT fk_recursos_academicos_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE CASCADE,
237 CONSTRAINT chk_recursos_academicos_descargas CHECK (descargas >= 0),
238 CONSTRAINT chk_recursos_academicos_reportes CHECK (reportes >= 0)
239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
240
241 -- =====================================================================
242 -- Módulo 6: Mensajería Directa y Asesorías Privadas
243 -- =====================================================================
244
245 -- 16. conversaciones: registro del canal 1 a 1 entre dos usuarios distintos
246 CREATE TABLE conversaciones (
247 id_conversacion INT AUTO_INCREMENT PRIMARY KEY,
248 id_usuario_1 INT NOT NULL,
249 id_usuario_2 INT NOT NULL,
250 fecha_inicio DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
251 CONSTRAINT fk_conversaciones_usuario_1 FOREIGN KEY (id_usuario_1) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
252 CONSTRAINT fk_conversaciones_usuario_2 FOREIGN KEY (id_usuario_2) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
253 -- Exclusividad de Canal de Mensajería
254 CONSTRAINT uq_conversaciones_usuarios UNIQUE (id_usuario_1, id_usuario_2),
255 CONSTRAINT chk_conversaciones_usuarios_distintos CHECK (id_usuario_1 <> id_usuario_2)
256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
257
258 -- 17. mensajes: mensajes individuales enviados en una conversación
259 CREATE TABLE mensajes (
260 id_mensaje INT AUTO_INCREMENT PRIMARY KEY,
261 id_conversacion INT NOT NULL,
262 id_usuario_emisor INT NOT NULL,
263 contenido TEXT NOT NULL,
264 leido BOOLEAN NOT NULL DEFAULT FALSE,
265 fecha_envio DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
266 CONSTRAINT fk_mensajes_conversacion FOREIGN KEY (id_conversacion) REFERENCES conversaciones (id_conversacion) ON DELETE CASCADE,
267 CONSTRAINT fk_mensajes_usuario_emisor FOREIGN KEY (id_usuario_emisor) REFERENCES usuarios (id_usuario) ON DELETE CASCADE
268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
269
270 -- =====================================================================
271 -- Módulo 7: Semestre Empresarial
272 -- =====================================================================
273
274 -- 18. empresas_vinculadas: directorio de empresas receptoras de Semestre Empresarial
275 CREATE TABLE empresas_vinculadas (
276 id_empresa INT AUTO_INCREMENT PRIMARY KEY,
277 nombre VARCHAR(200) NOT NULL,
278 sector VARCHAR(100),
279 descripcion TEXT,
280 activa BOOLEAN NOT NULL DEFAULT TRUE,
281 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
282 CONSTRAINT uq_empresas_vinculadas_nombre UNIQUE (nombre)
283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
284
285 -- 19. resenas_empresarial: evaluaciones, aprendizajes y consejos de estudiantes
286 CREATE TABLE resenas_empresarial (
287 id_resena INT AUTO_INCREMENT PRIMARY KEY,
288 id_estudiante INT NOT NULL,
289 id_empresa INT NOT NULL,
290 calificacion TINYINT NOT NULL,
291 proyecto_realizado TEXT,
292 recomendaciones TEXT,
293 fecha_resena DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
294 CONSTRAINT fk_resenas_empresarial_estudiante FOREIGN KEY (id_estudiante) REFERENCES estudiantes (id_estudiante) ON DELETE CASCADE,
295 CONSTRAINT fk_resenas_empresarial_empresa FOREIGN KEY (id_empresa) REFERENCES empresas_vinculadas (id_empresa) ON DELETE CASCADE,
296 -- Calificaciones Numéricas
297 CONSTRAINT chk_resenas_empresarial_calificacion CHECK (calificacion BETWEEN 1 AND 5)
298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
299
300 -- =====================================================================
301 -- Módulo 8: Comunidades y Círculos de Estudio
302 -- =====================================================================
303
304 -- 20. comunidades_estudio: grupos de estudio por materia, certificado o carrera
305 CREATE TABLE comunidades_estudio (
306 id_comunidad INT AUTO_INCREMENT PRIMARY KEY,
307 nombre VARCHAR(150) NOT NULL,
308 id_materia INT NULL,
309 id_certificado INT NULL,
310 id_usuario_creador INT NOT NULL,
311 privacidad ENUM('PUBLICA', 'PRIVADA') NOT NULL DEFAULT 'PUBLICA',
312 enlace_sala_virtual VARCHAR(255),
313 fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
314 CONSTRAINT fk_comunidades_estudio_materia FOREIGN KEY (id_materia) REFERENCES materias (id_materia) ON DELETE SET NULL,
315 CONSTRAINT fk_comunidades_estudio_certificado FOREIGN KEY (id_certificado) REFERENCES certificados (id_certificado) ON DELETE SET NULL,
316 CONSTRAINT fk_comunidades_estudio_creador FOREIGN KEY (id_usuario_creador) REFERENCES usuarios (id_usuario) ON DELETE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
318
319 -- 21. miembros_comunidad: relación N:M de usuarios inscritos en cada círculo de estudio
320 CREATE TABLE miembros_comunidad (
321 id_miembro INT AUTO_INCREMENT PRIMARY KEY,
322 id_comunidad INT NOT NULL,
323 id_usuario INT NOT NULL,
324 rol_comunidad ENUM('LIDER', 'ASESOR', 'MIEMBRO') NOT NULL DEFAULT 'MIEMBRO',
325 fecha_union DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
326 -- Integridad en Círculos de Estudio (ON DELETE CASCADE)
327 CONSTRAINT fk_miembros_comunidad_comunidad FOREIGN KEY (id_comunidad) REFERENCES comunidades_estudio (id_comunidad) ON DELETE CASCADE,
328 CONSTRAINT fk_miembros_comunidad_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE,
329 CONSTRAINT uq_miembros_comunidad UNIQUE (id_comunidad, id_usuario)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
331
332 -- 22. mensajes_comunidad: muro de publicaciones y avisos internos de cada comunidad
333 CREATE TABLE mensajes_comunidad (
334 id_mensaje_comunidad INT AUTO_INCREMENT PRIMARY KEY,
335 id_comunidad INT NOT NULL,
336 id_usuario INT NOT NULL,
337 contenido TEXT NOT NULL,
338 fecha_publicacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
339 -- Integridad en Círculos de Estudio (ON DELETE CASCADE)
340 CONSTRAINT fk_mensajes_comunidad_comunidad FOREIGN KEY (id_comunidad) REFERENCES comunidades_estudio (id_comunidad) ON DELETE CASCADE,
341 CONSTRAINT fk_mensajes_comunidad_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios (id_usuario) ON DELETE CASCADE
342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
343
344 SET FOREIGN_KEY_CHECKS = 1;
345
346 -- =====================================================================
347 -- Índices adicionales para columnas de búsqueda frecuente
348 -- =====================================================================
349
350 CREATE INDEX idx_publicaciones_carrera ON publicaciones (id_carrera);
351 CREATE INDEX idx_publicaciones_materia ON publicaciones (id_materia);
352 CREATE INDEX idx_respuestas_publicacion ON respuestas (id_publicacion);
353 CREATE INDEX idx_tips_academicos_materia ON tips_academicos (id_materia);
354 CREATE INDEX idx_recursos_academicos_materia ON recursos_academicos (id_materia);
355 CREATE INDEX idx_mensajes_conversacion ON mensajes (id_conversacion);
356 CREATE INDEX idx_comunidades_estudio_materia ON comunidades_estudio (id_materia);
357 CREATE INDEX idx_comunidades_estudio_certificado ON comunidades_estudio (id_certificado);
358
359 -- =====================================================================
360 -- Triggers de reglas de negocio que no pueden expresarse con CHECK
361 -- =====================================================================
362
363 DELIMITER $$
364
365 -- Límite de Ruta Curricular: máximo 3 certificados simultáneos por estudiante
366 CREATE TRIGGER trg_estudiante_certificados_limite
367 BEFORE INSERT ON estudiante_certificados
368 FOR EACH ROW
369 BEGIN
370 DECLARE total_certificados INT;
371 SELECT COUNT(*) INTO total_certificados
372 FROM estudiante_certificados
373 WHERE id_estudiante = NEW.id_estudiante;
374
375 IF total_certificados >= 3 THEN
376 SIGNAL SQLSTATE '45000'
377 SET MESSAGE_TEXT = 'Un estudiante solo puede vincular un máximo de 3 certificados simultáneos.';
378 END IF;
379 END$$
380
381 -- Insignia de verificación automática para respuestas aportadas por docentes
382 CREATE TRIGGER trg_respuestas_verificacion_docente
383 BEFORE INSERT ON respuestas
384 FOR EACH ROW
385 BEGIN
386 DECLARE autor_rol VARCHAR(20);
387 SELECT rol INTO autor_rol FROM usuarios WHERE id_usuario = NEW.id_usuario;
388
389 IF autor_rol = 'DOCENTE' THEN
390 SET NEW.verificado_docente = TRUE;
391 END IF;
392 END$$
393
394 -- Reseñar Semestre Empresarial: solo estudiantes con semestre >= 6
395 CREATE TRIGGER trg_resenas_empresarial_semestre_minimo
396 BEFORE INSERT ON resenas_empresarial
397 FOR EACH ROW
398 BEGIN
399 DECLARE semestre_estudiante INT;
400 SELECT semestre_actual INTO semestre_estudiante
401 FROM estudiantes
402 WHERE id_estudiante = NEW.id_estudiante;
403
404 IF semestre_estudiante IS NULL OR semestre_estudiante < 6 THEN
405 SIGNAL SQLSTATE '45000'
406 SET MESSAGE_TEXT = 'Solo estudiantes de semestre 6 o superior pueden reseñar el Semestre Empresarial.';
407 END IF;
408 END$$
409
410 DELIMITER ;