Modelo lógico de datos¶
Modelo físico-relacional derivado del diagrama de clases, normalizado hasta 3FN. Las decisiones de diseño principales son:
- Catálogos como tablas independientes (
CAT_*): eliminan atributos multivaluados implícitos y dependencias de texto en campos de estado. Cumplen 1FN y evitan anomalías de actualización. Personacomo entidad propia referenciada porUsuario: elimina la dependencia parcial de atributos biográficos respecto aUsuarioy soporta 1FN/2FN cuando un usuario no es persona (p.ej. cuentas de servicio).Expedienteligado aEstudiante+Edicion: cumple RN-44 (un expediente por estudiante y por edición).ProgramaVersion+ProgramaAsignatura: implementan RN-60 (versionado) y eliminan la dependencia transitiva decreditoscon respecto ahoras_totales. Los créditos se calculan a partir de las asignaturas.SolicitudHistorialEstado+DocumentoValidacion+EvaluacionFinalDetalle: soportan la trazabilidad exigida por RN-51 y permiten auditoría de cambios.DocumentoEmitido+Certificado+TituloAcademicoconcodigo_validacionUNIQUE (RN-48).
Diagrama ER (3FN)¶
erDiagram
USUARIO {
int id_usuario PK
int id_persona FK
string nombre
string apellido1
string apellido2
string correo
string contrasena_hash
int id_estado_usuario FK
datetime fecha_creacion
datetime ultimo_acceso
}
PERSONA {
int id_persona PK
string documento_identidad
int id_tipo_documento FK
date fecha_nacimiento
string sexo
string telefono
}
ROL {
int id_rol PK
string nombre
string descripcion
boolean activo
}
USUARIO_ROL {
int id_usuario PK,FK
int id_rol PK,FK
datetime fecha_asignacion
int asignado_por FK
}
PERMISO {
int id_permiso PK
string codigo
string descripcion
}
ROL_PERMISO {
int id_rol PK,FK
int id_permiso PK,FK
}
FACULTAD {
int id_facultad PK
string nombre
string codigo
}
CUM {
int id_cum PK
string nombre
string municipio
string provincia
}
PROGRAMA_POSGRADO {
int id_programa PK
int id_facultad FK
string titulo
int id_tipo_programa FK
int id_modalidad_estudio FK
int id_modalidad_dedicacion FK
int id_coordinador FK
int id_estado_programa FK
date fecha_aprobacion_inst
date fecha_resolucion_mes
}
PROGRAMA_VERSION {
int id_programa_version PK
int id_programa FK
int numero_version
date fecha_vigencia
int horas_totales
}
PROGRAMA_ASIGNATURA {
int id_programa_asignatura PK
int id_programa_version FK
string nombre
int horas
int creditos
}
EDICION_PROGRAMA {
int id_edicion PK
int id_programa FK
int numero_edicion
date fecha_inicio
date fecha_fin
int capacidad
string lugar
int id_estado_edicion FK
}
CONVOCATORIA {
int id_convocatoria PK
int id_edicion FK
date fecha_publicacion
date fecha_cierre
int id_estado_convocatoria FK
}
CONVOCATORIA_REQUISITO {
int id_convocatoria_requisito PK
int id_convocatoria FK
int id_tipo_documento FK
string descripcion
boolean obligatorio
}
NECESIDAD_SUPERACION {
int id_necesidad PK
string descripcion
int id_origen_necesidad FK
int id_prioridad FK
int id_facultad FK
int id_cum FK
int id_estado_necesidad FK
date fecha_registro
}
COMITE_ACADEMICO {
int id_comite PK
int id_programa FK
date fecha_constitucion
}
COMITE_MIEMBRO {
int id_comite PK,FK
int id_usuario PK,FK
string rol
date fecha_ingreso
}
ASPIRANTE {
int id_aspirante PK
int id_usuario FK
int id_tipo_aspirante FK
}
SOLICITUD_MATRICULA {
int id_solicitud PK
int id_convocatoria FK
int id_usuario FK
int id_aspirante FK
datetime fecha_solicitud
int id_estado_solicitud FK
}
SOLICITUD_HISTORIAL_ESTADO {
int id_historial PK
int id_solicitud FK
int id_estado_solicitud FK
datetime fecha_cambio
int id_usuario_cambio FK
string motivo
}
DOCUMENTO {
int id_documento PK
int id_solicitud FK
int id_tipo_documento FK
string nombre_archivo
string ruta_archivo
int tamano_bytes
string formato
int id_estado_validacion FK
string motivo_rechazo
datetime fecha_carga
}
DOCUMENTO_VALIDACION {
int id_documento_validacion PK
int id_documento FK
int id_usuario_validador FK
int id_estado_validacion FK
datetime fecha_validacion
string observacion
}
ESTUDIANTE {
int id_estudiante PK
int id_usuario FK
int id_persona FK
string codigo
int id_estado_academico FK
}
MATRICULA {
int id_matricula PK
int id_estudiante FK
int id_edicion FK
date fecha_matricula
int id_estado_matricula FK
}
EXPEDIENTE {
int id_expediente PK
int id_estudiante FK
int id_edicion FK
date fecha_apertura
int id_estado_expediente FK
}
ACTIVIDAD_ACADEMICA {
int id_actividad PK
int id_edicion FK
string nombre
string tipo
int horas
date fecha
}
CALIFICACION {
int id_calificacion PK
int id_expediente FK
int id_actividad FK
int id_calificacion_valor FK
date fecha
string observaciones
int registrado_por FK
}
ASISTENCIA {
int id_asistencia PK
int id_expediente FK
int id_actividad FK
date fecha
boolean presente
int registrado_por FK
}
MEMORIA_ESCRITA {
int id_memoria PK
int id_estudiante FK
int version
datetime fecha_entrega
string ruta_archivo
int paginas
int id_estado_memoria FK
}
TRIBUNAL {
int id_tribunal PK
int id_programa FK
date fecha_constitucion
}
TRIBUNAL_MIEMBRO {
int id_tribunal PK,FK
int id_usuario PK,FK
string rol
}
TUTORIA {
int id_tutoria PK
int id_usuario FK
int id_estudiante FK
int id_edicion FK
int id_memoria FK
date fecha_asignacion
}
EVALUACION_FINAL {
int id_evaluacion PK
int id_estudiante FK
int id_expediente FK
int id_tribunal FK
int id_tutor FK
int id_oponente FK
int id_memoria FK
int id_resultado_defensa FK
int convocatoria
datetime fecha_defensa
string acta_url
}
DOCUMENTO_EMITIDO {
int id_documento_emitido PK
int id_estudiante FK
int id_programa FK
int id_tipo_documento_emitido FK
int emitido_por FK
string codigo_validacion
datetime fecha_emision
string ruta_archivo
string firma_digital
}
CERTIFICADO {
int id_documento_emitido PK,FK
int id_expediente FK
int horas
int creditos
}
TITULO_ACADEMICO {
int id_documento_emitido PK,FK
int id_expediente FK
string tesis_titulo
string mencion
}
REPORTE {
int id_reporte PK
int id_usuario_generador FK
string tipo
string filtros
datetime fecha_generacion
string formato
}
AUDITORIA {
int id_auditoria PK
int id_usuario FK
int id_accion FK
string entidad
int id_entidad
datetime fecha_hora
string ip
int id_resultado FK
string detalle
}
CAT_TIPO_DOCUMENTO {
int id_tipo_documento PK
string nombre
boolean activo
}
CAT_TIPO_PROGRAMA {
int id_tipo_programa PK
string nombre
boolean activo
}
CAT_MODALIDAD_ESTUDIO {
int id_modalidad_estudio PK
string nombre
boolean activo
}
CAT_MODALIDAD_DEDICACION {
int id_modalidad_dedicacion PK
string nombre
boolean activo
}
CAT_ESTADO_USUARIO {
int id_estado_usuario PK
string nombre
}
CAT_ESTADO_PROGRAMA {
int id_estado_programa PK
string nombre
}
CAT_ESTADO_EDICION {
int id_estado_edicion PK
string nombre
}
CAT_ESTADO_CONVOCATORIA {
int id_estado_convocatoria PK
string nombre
}
CAT_ESTADO_SOLICITUD {
int id_estado_solicitud PK
string nombre
}
CAT_ESTADO_VALIDACION {
int id_estado_validacion PK
string nombre
}
CAT_ESTADO_ACADEMICO {
int id_estado_academico PK
string nombre
}
CAT_ESTADO_MATRICULA {
int id_estado_matricula PK
string nombre
}
CAT_ESTADO_EXPEDIENTE {
int id_estado_expediente PK
string nombre
}
CAT_CALIFICACION_VALOR {
int id_calificacion_valor PK
string nombre
int orden
}
CAT_ESTADO_MEMORIA {
int id_estado_memoria PK
string nombre
}
CAT_RESULTADO_DEFENSA {
int id_resultado_defensa PK
string nombre
}
CAT_ORIGEN_NECESIDAD {
int id_origen_necesidad PK
string nombre
}
CAT_PRIORIDAD {
int id_prioridad PK
string nombre
int nivel
}
CAT_ESTADO_NECESIDAD {
int id_estado_necesidad PK
string nombre
}
CAT_TIPO_ASPIRANTE {
int id_tipo_aspirante PK
string nombre
}
CAT_TIPO_DOCUMENTO_EMITIDO {
int id_tipo_documento_emitido PK
string nombre
}
CAT_ACCION_AUDITORIA {
int id_accion PK
string nombre
}
CAT_RESULTADO_AUDITORIA {
int id_resultado PK
string nombre
}
USUARIO ||--o{ USUARIO_ROL : "posee"
ROL ||--o{ USUARIO_ROL : "asignado"
ROL ||--o{ ROL_PERMISO : "tiene"
PERMISO ||--o{ ROL_PERMISO : "otorga"
USUARIO ||--o| PERSONA : "identifica"
USUARIO ||--o| ASPIRANTE : "actua_como"
USUARIO ||--o| ESTUDIANTE : "actua_como"
PERSONA ||--o| ESTUDIANTE : "caracteriza"
CAT_TIPO_DOCUMENTO ||--o{ PERSONA : "clasifica"
CAT_ESTADO_USUARIO ||--o{ USUARIO : "estado"
CAT_TIPO_ASPIRANTE ||--o{ ASPIRANTE : "tipo"
FACULTAD ||--o{ PROGRAMA_POSGRADO : "ofrece"
CAT_TIPO_PROGRAMA ||--o{ PROGRAMA_POSGRADO : "tipo"
CAT_MODALIDAD_ESTUDIO ||--o{ PROGRAMA_POSGRADO : "modalidad"
CAT_MODALIDAD_DEDICACION ||--o{ PROGRAMA_POSGRADO : "dedicacion"
CAT_ESTADO_PROGRAMA ||--o{ PROGRAMA_POSGRADO : "estado"
USUARIO ||--o{ PROGRAMA_POSGRADO : "coordina"
PROGRAMA_POSGRADO ||--o{ PROGRAMA_VERSION : "versiona"
PROGRAMA_VERSION ||--o{ PROGRAMA_ASIGNATURA : "compone"
PROGRAMA_POSGRADO ||--o{ COMITE_ACADEMICO : "tiene"
COMITE_ACADEMICO ||--o{ COMITE_MIEMBRO : "integra"
USUARIO ||--o{ COMITE_MIEMBRO : "participa"
PROGRAMA_POSGRADO ||--o{ EDICION_PROGRAMA : "ejecuta"
CAT_ESTADO_EDICION ||--o{ EDICION_PROGRAMA : "estado"
EDICION_PROGRAMA ||--o{ CONVOCATORIA : "publica"
CAT_ESTADO_CONVOCATORIA ||--o{ CONVOCATORIA : "estado"
CONVOCATORIA ||--o{ CONVOCATORIA_REQUISITO : "exige"
CAT_TIPO_DOCUMENTO ||--o{ CONVOCATORIA_REQUISITO : "requiere"
CONVOCATORIA ||--o{ SOLICITUD_MATRICULA : "recibe"
USUARIO ||--o{ SOLICITUD_MATRICULA : "realiza"
ASPIRANTE ||--o{ SOLICITUD_MATRICULA : "presenta"
CAT_ESTADO_SOLICITUD ||--o{ SOLICITUD_MATRICULA : "estado"
SOLICITUD_MATRICULA ||--o{ SOLICITUD_HISTORIAL_ESTADO : "traza"
USUARIO ||--o{ SOLICITUD_HISTORIAL_ESTADO : "cambia"
CAT_ESTADO_SOLICITUD ||--o{ SOLICITUD_HISTORIAL_ESTADO : "estado"
SOLICITUD_MATRICULA ||--o{ DOCUMENTO : "adjunta"
CAT_TIPO_DOCUMENTO ||--o{ DOCUMENTO : "tipo"
CAT_ESTADO_VALIDACION ||--o{ DOCUMENTO : "validacion"
DOCUMENTO ||--o{ DOCUMENTO_VALIDACION : "audita"
USUARIO ||--o{ DOCUMENTO_VALIDACION : "valida"
CAT_ESTADO_VALIDACION ||--o{ DOCUMENTO_VALIDACION : "estado"
ESTUDIANTE ||--o{ MATRICULA : "realiza"
EDICION_PROGRAMA ||--o{ MATRICULA : "incluye"
CAT_ESTADO_MATRICULA ||--o{ MATRICULA : "estado"
ESTUDIANTE ||--o{ EXPEDIENTE : "tiene"
EDICION_PROGRAMA ||--o{ EXPEDIENTE : "pertenece"
CAT_ESTADO_EXPEDIENTE ||--o{ EXPEDIENTE : "estado"
EDICION_PROGRAMA ||--o{ ACTIVIDAD_ACADEMICA : "planifica"
EXPEDIENTE ||--o{ CALIFICACION : "registra"
ACTIVIDAD_ACADEMICA ||--o{ CALIFICACION : "evalua"
CAT_CALIFICACION_VALOR ||--o{ CALIFICACION : "valor"
USUARIO ||--o{ CALIFICACION : "registra"
EXPEDIENTE ||--o{ ASISTENCIA : "registra"
ACTIVIDAD_ACADEMICA ||--o{ ASISTENCIA : "controla"
USUARIO ||--o{ ASISTENCIA : "registra"
ESTUDIANTE ||--o{ MEMORIA_ESCRITA : "elabora"
CAT_ESTADO_MEMORIA ||--o{ MEMORIA_ESCRITA : "estado"
PROGRAMA_POSGRADO ||--o{ TRIBUNAL : "conforma"
TRIBUNAL ||--o{ TRIBUNAL_MIEMBRO : "compone"
USUARIO ||--o{ TRIBUNAL_MIEMBRO : "integra"
USUARIO ||--o{ TUTORIA : "tutorea"
ESTUDIANTE ||--o{ TUTORIA : "recibe"
EDICION_PROGRAMA ||--o{ TUTORIA : "pertenece"
MEMORIA_ESCRITA ||--o{ TUTORIA : "asocia"
ESTUDIANTE ||--o{ EVALUACION_FINAL : "sustenta"
EXPEDIENTE ||--o{ EVALUACION_FINAL : "pertenece"
TRIBUNAL ||--o{ EVALUACION_FINAL : "evalua"
USUARIO ||--o{ EVALUACION_FINAL : "tutorea"
USUARIO ||--o{ EVALUACION_FINAL : "opone"
MEMORIA_ESCRITA ||--o{ EVALUACION_FINAL : "defiende"
CAT_RESULTADO_DEFENSA ||--o{ EVALUACION_FINAL : "resultado"
ESTUDIANTE ||--o{ DOCUMENTO_EMITIDO : "recibe"
PROGRAMA_POSGRADO ||--o{ DOCUMENTO_EMITIDO : "origina"
USUARIO ||--o{ DOCUMENTO_EMITIDO : "emite"
CAT_TIPO_DOCUMENTO_EMITIDO ||--o{ DOCUMENTO_EMITIDO : "tipo"
DOCUMENTO_EMITIDO ||--o| CERTIFICADO : "es"
DOCUMENTO_EMITIDO ||--o| TITULO_ACADEMICO : "es"
EXPEDIENTE ||--o{ CERTIFICADO : "ampara"
EXPEDIENTE ||--o{ TITULO_ACADEMICO : "ampara"
USUARIO ||--o{ REPORTE : "genera"
USUARIO ||--o{ AUDITORIA : "realiza"
CAT_ACCION_AUDITORIA ||--o{ AUDITORIA : "accion"
CAT_RESULTADO_AUDITORIA ||--o{ AUDITORIA : "resultado"
CAT_ORIGEN_NECESIDAD ||--o{ NECESIDAD_SUPERACION : "origen"
CAT_PRIORIDAD ||--o{ NECESIDAD_SUPERACION : "prioridad"
CAT_ESTADO_NECESIDAD ||--o{ NECESIDAD_SUPERACION : "estado"
FACULTAD ||--o{ NECESIDAD_SUPERACION : "solicita"
CUM ||--o{ NECESIDAD_SUPERACION : "solicita"
Resumen de normalización aplicada¶
| Forma normal | Hallazgo | Acción correctiva |
|---|---|---|
| 1FN | nombre y apellidos en una sola columna, atributos multivaluados implícitos. |
Separar en nombre, apellido1, apellido2; crear Persona con datos biográficos atómicos. |
| 1FN | Atributos derivados (creditos, horas_totales) almacenados. |
Mover a PROGRAMA_ASIGNATURA y calcular creditos por agregación. |
| 2FN | USUARIO_ROL sin PK compuesta clara y atributos de auditoría. |
PK (id_usuario, id_rol) explícita; fecha_asignacion y asignado_por dependen de la PK completa. |
| 2FN | TRIBUANL_MIEMBRO no modelado. |
Crear entidad con PK (id_tribunal, id_usuario). |
| 3FN | Dependencias transitivas por catálogos como string (estado, tipo, modalidad). |
Crear tablas CAT_* y FKs. |
| 3FN | PROGRAMA_POSGRADO con coordinador, horas_totales y creditos que dependen del programa pero también del estado/version. |
Crear PROGRAMA_VERSION + PROGRAMA_ASIGNATURA y TUTORIA para coordinador por edición. |
| 3FN | MATRICULA con atributo multivaluado estado (histórico de cambios). |
Mantener estado actual + tabla de auditoría SOLICITUD_HISTORIAL_ESTADO y MATRICULA_HISTORIAL si se requiere. |
| 3FN | SOLICITUD_MATRICULA.observaciones mezclaba datos de cambios. |
Pasar a SOLICITUD_HISTORIAL_ESTADO.motivo. |
| 3FN | DOCUMENTO.motivo_rechazo mezclaba con datos de validación. |
Pasar a DOCUMENTO_VALIDACION.observacion. |
| 3FN | EVALUACION_FINAL con datos del tribunal repetidos. |
TRIBUNAL + TRIBUNAL_MIEMBRO. |
| 3FN | Código de validación como string libre. | DOCUMENTO_EMITIDO.codigo_validacion con UNIQUE. |
| 3FN | ASISTENCIA/CALIFICACION referenciaban id_actividad inexistente. |
Crear ACTIVIDAD_ACADEMICA y FKs. |
Restricciones de integridad (constraints) recomendadas¶
-- Unicidad de correo de usuario
ALTER TABLE USUARIO ADD CONSTRAINT uq_usuario_correo UNIQUE (correo);
-- RN-45: un estudiante no puede tener dos matrículas activas en la misma edición
CREATE UNIQUE INDEX uq_matricula_activa
ON MATRICULA (id_estudiante, id_edicion)
WHERE id_estado_matricula = (SELECT id_estado_matricula FROM CAT_ESTADO_MATRICULA WHERE nombre = 'Activa');
-- RN-44: expediente único por estudiante y por edición
ALTER TABLE EXPEDIENTE ADD CONSTRAINT uq_expediente_estudiante_edicion UNIQUE (id_estudiante, id_edicion);
-- RN-48: código de validación único por documento emitido
ALTER TABLE DOCUMENTO_EMITIDO ADD CONSTRAINT uq_codigo_validacion UNIQUE (codigo_validacion);
-- RN-25: un tutor no puede atender más de cinco estudiantes por edición
-- (se modela vía aplicación, apoyado en vista materializada)
-- RN-31: tribunal de número impar
CREATE OR REPLACE FUNCTION check_tribunal_impar() RETURNS trigger AS $$
BEGIN
IF (SELECT COUNT(*) FROM TRIBUNAL_MIEMBRO WHERE id_tribunal = NEW.id_tribunal) % 2 = 0 THEN
RAISE EXCEPTION 'El tribunal debe tener número impar de miembros (RN-31)';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- CHECK sobre Asistencia: un estudiante solo un registro por actividad
ALTER TABLE ASISTENCIA ADD CONSTRAINT uq_asistencia_expediente_actividad UNIQUE (id_expediente, id_actividad);
Decisiones de denormalización controladas (opcional)¶
| Caso | Decisión | Justificación |
|---|---|---|
USUARIO.ultimo_acceso |
Redundante con AUDITORIA. |
Optimiza consultas de sesión. Mantener con TRIGGER. |
DOCUMENTO_EMITIDO.fecha_emision indexada |
— | Soporta RF-115 (archivo histórico consultable). |
EXPEDIENTE.estado redundante con MATRICULA.estado |
NO denormalizar. | Consultar por JOIN; evita inconsistencia. |