Modelo de datos de un centro de formación con inscripciones duplicadas
El área de formación de un centro técnico gestiona inscripciones en una sola tabla llamada inscripciones con estas columnas: id, estudiante_nombre, estudiante_cedula, curso_nombre, instructor_nombre, fecha_inicio, precio y estado. Hay 18.000 filas y se han detectado 2.100 estudiantes duplicados porque la cédula se escribe con guiones en unas filas y sin guiones en otras. El mismo curso aparece con tres nombres distintos («Excel básico», «EXCEL BASICO» y «Excel Básico I»). Cuando un instructor cambia, hay que actualizar cientos de filas y algunas quedan desactualizadas. La consulta del listado por curso y fecha tarda 6 s. El centro quiere registrar históricos de precio y de instructor sin perder inscripciones antiguas, y emitir certificados que muestren el nombre del curso tal como estaba al inscribirse. Hay un fin de semana para migrar los datos y no se puede detener la inscripción más de dos horas. El equipo propone crear índices sin normalizar y agregar una columna curso_id calculada a mano.
1. Identifica las anomalías del modelo actual y propón un esquema en tercera forma normal con claves y restricciones.
Read the worked answer
La tabla actual mezcla entidades y sufre anomalías: de inserción (no se puede crear un curso sin inscripción), de actualización (cambiar instructor obliga a tocar cientos de filas) y de borrado (al eliminar la última inscripción se pierde el curso). Propuesta en 3FN:
estudiantes(id PK, cedula UNIQUE, nombre, ...); normalizar cédula sin guiones y validar conCHECKo en la aplicación.instructores(id PK, nombre, ...).cursos(id PK, nombre, ...)con nombre canónico.secciones(id PK, curso_id FK, instructor_id FK, fecha_inicio, precio_base).inscripciones(id PK, estudiante_id FK, seccion_id FK, fecha, precio_al_inscribirse, estado).precios_curso(id, curso_id, precio, vigente_desde, vigente_hasta)o histórico en sección.
Para certificados, guardar precio_al_inscribirse y una copia del nombre del curso o curso_nombre_historico en la inscripción, o usar secciones inmutables. cedula única evita duplicados; UNIQUE(estudiante_id, seccion_id) evita doble inscripción. No usar una columna curso_id calculada a mano: es una FK y debe tener integridad referencial.
2. Diseña el plan de migración para una ventana de dos horas, incluyendo validación y reversión.
Read the worked answer
Plan para dos horas: (1) ensayar en staging con copia real; (2) backup completo y verificación de restauración; (3) crear tablas nuevas y una tabla de mapeo de nombres; (4) migrar con INSERT ... SELECT limpiando cédulas y agrupando nombres con TRIM/UPPER y reglas aprobadas; (5) deduplicar con ROW_NUMBER() o GROUP BY y revisar los 2.100 casos; (6) validar conteos: 18.000 filas origen frente a inscripciones más rechazos; (7) ventana de solo lectura o doble escritura; (8) cambiar la aplicación y hacer smoke tests; (9) rollback con backup si falla.
No hacer updates manuales sin transacción. Documentar reglas de normalización y casos ambiguos. Los certificados deben generarse desde la inscripción histórica, no desde el nombre actual del curso.
3. Propón índices y reescribe la consulta del listado por curso y fecha, explicando cómo verificar la mejora.
Read the worked answer
Consulta típica: SELECT ... FROM inscripciones i JOIN secciones s ON ... WHERE s.curso_id = ? AND s.fecha_inicio BETWEEN ? AND ? ORDER BY s.fecha_inicio. Crear índice compuesto (curso_id, fecha_inicio) en secciones, y en inscripciones índices en seccion_id y estudiante_id. Evitar funciones en columnas indexadas (YEAR(fecha_inicio)=?), usar rangos. Revisar EXPLAIN: si aparece Using filesort, el orden puede requerir un índice adicional. No crear índices indiscriminadamente: cada uno cuesta escritura. La lentitud de 6 s puede venir de un escaneo completo por falta de FK indexada; medir antes y después con EXPLAIN ANALYZE o el slow query log.