Saltar a contenido

Modelo de datos

Este es el modelo real de la base del sistema: Oracle 23ai, tal como lo dejan creado los scripts de la carpeta sql/ en el orden de la Guía de ejecución (S12, S14, S15, S16, S17, S19, S20, S21 y S22). Coincide columna por columna con las entidades JPA del backend, que arranca con ddl-auto: validate: si una tabla o columna no existiera, la aplicación no levantaría.

1. Esquemas

Esquema Módulo del backend Tablas
BOM_CAMPANIAS campanias CATEGORIAS_HABITO, HABITOS, CAMPANIAS, CAMPANIA_HABITOS, CAMPANIA_SEDES, CUPOS_DIARIOS
BOM_PARTICIPACION participacion, cuentas y sedes ESTUDIANTES, REGISTROS, DETALLE_EVIDENCIAS, SOLICITUDES_CUENTA, SEDES_USUARIO
BOM_PREMIOS premios CATEGORIAS_PREMIO, PREMIOS, ACTAS_PREMIACION, DETALLES_PREMIACION
BOM_AUDITORIA auditoria EVENTOS

Son 16 tablas en 4 esquemas. La aplicación se conecta como BOMERP_APP, que no es dueño de ningún esquema: solo tiene CREATE SESSION y permisos por tabla.

BOM_CATALOGO y BOM_VENTAS también existen en la base, pero son el ejemplo guiado del docente (catalogo y ventas) y no forman parte del dominio del proyecto.

2. Diagrama

Línea continua: llave foránea real en Oracle. Línea punteada: referencia por ID entre esquemas distintos, sin llave foránea, por la decisión ADR-02 (cada módulo es dueño de sus tablas).

erDiagram
  CATEGORIAS_HABITO["BOM_CAMPANIAS.CATEGORIAS_HABITO"] {
    NUMBER ID PK
    VARCHAR2 NOMBRE UK
    VARCHAR2 DESCRIPCION
  }
  HABITOS["BOM_CAMPANIAS.HABITOS"] {
    NUMBER ID PK
    NUMBER ID_CATEGORIA FK
    VARCHAR2 NOMBRE
    VARCHAR2 DESCRIPCION
    NUMBER PUNTAJE_BASE "CHECK > 0"
    NUMBER ACTIVO
  }
  CAMPANIAS["BOM_CAMPANIAS.CAMPANIAS"] {
    NUMBER ID PK
    VARCHAR2 NOMBRE
    DATE FECHA_INICIO
    DATE FECHA_FIN
    NUMBER MAX_EVIDENCIAS_DIA "CHECK > 0"
    VARCHAR2 ESTADO
  }
  CAMPANIA_HABITOS["BOM_CAMPANIAS.CAMPANIA_HABITOS"] {
    NUMBER ID PK
    NUMBER ID_CAMPANIA FK,UK
    NUMBER ID_HABITO FK,UK
  }
  CAMPANIA_SEDES["BOM_CAMPANIAS.CAMPANIA_SEDES"] {
    NUMBER ID PK
    NUMBER ID_CAMPANIA FK,UK
    VARCHAR2 SEDE UK
  }
  CUPOS_DIARIOS["BOM_CAMPANIAS.CUPOS_DIARIOS"] {
    NUMBER ID PK
    NUMBER ID_CAMPANIA FK,UK
    NUMBER ID_ESTUDIANTE UK
    DATE FECHA UK
    NUMBER EVIDENCIAS_USADAS "CHECK >= 0"
  }
  ESTUDIANTES["BOM_PARTICIPACION.ESTUDIANTES"] {
    NUMBER ID PK
    VARCHAR2 KEYCLOAK_ID UK
    VARCHAR2 USUARIO UK
    VARCHAR2 NOMBRE
    VARCHAR2 CORREO
    VARCHAR2 CODIGO_UNIVERSITARIO
    VARCHAR2 DNI
    VARCHAR2 ESCUELA
    NUMBER CICLO
    VARCHAR2 CELULAR
    VARCHAR2 SEDE
  }
  REGISTROS["BOM_PARTICIPACION.REGISTROS"] {
    NUMBER ID PK
    NUMBER ID_ESTUDIANTE FK
    NUMBER ID_CAMPANIA
    TIMESTAMP FECHA
    VARCHAR2 ESTADO
    NUMBER TOTAL_EVIDENCIAS
    NUMBER PUNTAJE_TOTAL
    VARCHAR2 MOTIVO_RECHAZO
    VARCHAR2 REVISADO_POR
    TIMESTAMP FECHA_REVISION
    VARCHAR2 SEDE
  }
  DETALLE_EVIDENCIAS["BOM_PARTICIPACION.DETALLE_EVIDENCIAS"] {
    NUMBER ID PK
    NUMBER ID_REGISTRO FK
    NUMBER ID_HABITO
    VARCHAR2 NOMBRE_HABITO
    VARCHAR2 DESCRIPCION
    VARCHAR2 URL_IMAGEN
    NUMBER PUNTAJE_UNITARIO
    NUMBER VECES "CHECK > 0"
    NUMBER SUBTOTAL
    VARCHAR2 ESTADO
    VARCHAR2 MOTIVO_RECHAZO
    VARCHAR2 ARCHIVO_IMAGEN
  }
  SEDES_USUARIO["BOM_PARTICIPACION.SEDES_USUARIO"] {
    VARCHAR2 USUARIO PK
    VARCHAR2 SEDE
  }
  SOLICITUDES_CUENTA["BOM_PARTICIPACION.SOLICITUDES_CUENTA"] {
    NUMBER ID PK
    NUMBER ODOO_LEAD_ID UK
    VARCHAR2 SEDE
    VARCHAR2 NOMBRES
    VARCHAR2 APELLIDOS
    VARCHAR2 CORREO
    VARCHAR2 CODIGO_UNIVERSITARIO
    VARCHAR2 DNI
    VARCHAR2 ESCUELA
    NUMBER CICLO "CHECK 1-14"
    VARCHAR2 CELULAR
    VARCHAR2 ARCHIVO_CARNE
    VARCHAR2 ESTADO "CHECK"
    VARCHAR2 MOTIVO_RECHAZO
    TIMESTAMP FECHA_SOLICITUD
    TIMESTAMP FECHA_REVISION
    VARCHAR2 REVISADO_POR
    VARCHAR2 USUARIO_CREADO
  }
  CATEGORIAS_PREMIO["BOM_PREMIOS.CATEGORIAS_PREMIO"] {
    NUMBER ID PK
    VARCHAR2 NOMBRE UK
    VARCHAR2 DESCRIPCION
  }
  PREMIOS["BOM_PREMIOS.PREMIOS"] {
    NUMBER ID PK
    NUMBER CATEGORIA_ID FK
    VARCHAR2 NOMBRE UK
    VARCHAR2 DESCRIPCION
    NUMBER STOCK "CHECK >= 0"
    BOOLEAN ACTIVO
  }
  ACTAS_PREMIACION["BOM_PREMIOS.ACTAS_PREMIACION"] {
    NUMBER ID PK
    NUMBER CAMPANIA_ID UK
    VARCHAR2 SEDE UK
    DATE FECHA_INICIO_PERIODO UK
    DATE FECHA_FIN_PERIODO UK
    TIMESTAMP FECHA_EMISION
    VARCHAR2 ESTADO
    NUMBER TOTAL_GANADORES
    NUMBER TOTAL_PREMIOS_ENTREGADOS
  }
  DETALLES_PREMIACION["BOM_PREMIOS.DETALLES_PREMIACION"] {
    NUMBER ID PK
    NUMBER ACTA_ID FK
    NUMBER PREMIO_ID FK
    NUMBER ESTUDIANTE_ID
    NUMBER POSICION
    NUMBER CANTIDAD "CHECK > 0"
    NUMBER PUNTAJE_ALCANZADO
  }
  EVENTOS["BOM_AUDITORIA.EVENTOS"] {
    NUMBER ID PK
    TIMESTAMP FECHA
    VARCHAR2 USUARIO
    VARCHAR2 NOMBRE
    VARCHAR2 ACCION
    VARCHAR2 ENTIDAD
    VARCHAR2 ENTIDAD_ID
    VARCHAR2 DETALLE
    VARCHAR2 SEDE
    VARCHAR2 TRACE_ID
  }
  CATEGORIAS_HABITO ||--o{ HABITOS : "FK"
  CAMPANIAS ||--o{ CAMPANIA_HABITOS : "FK"
  HABITOS ||--o{ CAMPANIA_HABITOS : "FK"
  CAMPANIAS ||--o{ CAMPANIA_SEDES : "FK"
  CAMPANIAS ||--o{ CUPOS_DIARIOS : "FK"
  ESTUDIANTES ||--o{ REGISTROS : "FK"
  REGISTROS ||--|{ DETALLE_EVIDENCIAS : "FK"
  CATEGORIAS_PREMIO ||--o{ PREMIOS : "FK"
  ACTAS_PREMIACION ||--|{ DETALLES_PREMIACION : "FK"
  PREMIOS ||--o{ DETALLES_PREMIACION : "FK"
  CAMPANIAS ||..o{ REGISTROS : "por ID"
  HABITOS ||..o{ DETALLE_EVIDENCIAS : "por ID"
  ESTUDIANTES ||..o{ CUPOS_DIARIOS : "por ID"
  CAMPANIAS ||..o{ ACTAS_PREMIACION : "por ID"
  ESTUDIANTES ||..o{ DETALLES_PREMIACION : "por ID"

3. Restricciones

Tabla Restricción Qué protege
CUPOS_DIARIOS UK_CUPO_DIA (campaña, estudiante, fecha), CK_CUPO_USADAS Una sola fila de cupo por estudiante y día; las evidencias usadas nunca son negativas
CAMPANIAS CK_CAMPANIA_MAX El máximo de evidencias por día es mayor que cero
CAMPANIA_HABITOS UK_CAMPANIA_HABITO Un hábito no se repite dentro de una campaña
CAMPANIA_SEDES UK_CAMPANIA_SEDE, CK_CAMPANIA_SEDE Una campaña no se repite en una sede; solo Lima, Juliaca o Tarapoto
CATEGORIAS_HABITO UK_CATEGORIA_HABITO_NOMBRE No hay dos categorías con el mismo nombre
HABITOS CK_HABITO_PUNTAJE El puntaje base es mayor que cero
REGISTROS CK_REGISTRO_PUNTAJE, CK_REGISTRO_TOTALEV, CK_REGISTRO_SEDE Puntaje no negativo, al menos una evidencia y sede válida
DETALLE_EVIDENCIAS CK_EVID_VECES, CK_EVID_SUBTOTAL, CK_DETALLE_ESTADO Veces mayor que cero, subtotal no negativo y uno de BORRADOR, ENVIADO, VALIDADO, RECHAZADO o ANULADO
ESTUDIANTES UK_ESTUDIANTE_KEYCLOAK, UK_ESTUDIANTE_USUARIO, CK_ESTUDIANTE_SEDE Un estudiante por cuenta de Keycloak y por usuario
SOLICITUDES_CUENTA CK_SOLICITUD_ESTADO, CK_SOLICITUD_CICLO y el índice único UX_SOLICITUD_LEAD Estado válido, ciclo de 1 a 14 y un solo registro por lead de Odoo
SEDES_USUARIO CK_SEDE_USUARIO Solo Lima, Juliaca o Tarapoto
CATEGORIAS_PREMIO UK_CATEGORIA_PREMIO_NOMBRE No hay dos categorías de premio con el mismo nombre
PREMIOS UK_PREMIO_NOMBRE, CK_PREMIO_STOCK Nombre único y stock nunca negativo
ACTAS_PREMIACION UK_ACTA_CAMPANIA_PERIODO (campaña, sede, inicio, fin), CK_ACTA_ESTADO, CK_ACTA_PERIODO, CK_ACTA_SEDE No hay dos actas del mismo periodo, campaña y sede; estado EMITIDA o ANULADA; el fin no es antes del inicio
DETALLES_PREMIACION CK_DETALLE_CANTIDAD, CK_DETALLE_POSICION Cantidad y posición mayores que cero

Llaves foráneas

Llave De A
FK_HABITO_CATEGORIA HABITOS.ID_CATEGORIA CATEGORIAS_HABITO.ID
FK_CH_CAMPANIA, FK_CH_HABITO CAMPANIA_HABITOS CAMPANIAS, HABITOS
FK_CS_CAMPANIA CAMPANIA_SEDES.ID_CAMPANIA CAMPANIAS.ID
FK_CUPO_CAMPANIA CUPOS_DIARIOS.ID_CAMPANIA CAMPANIAS.ID
FK_REGISTRO_ESTUDIANTE REGISTROS.ID_ESTUDIANTE ESTUDIANTES.ID
FK_EVIDENCIA_REGISTRO DETALLE_EVIDENCIAS.ID_REGISTRO REGISTROS.ID
FK_PREMIO_CATEGORIA PREMIOS.CATEGORIA_ID CATEGORIAS_PREMIO.ID
FK_DETALLE_ACTA DETALLES_PREMIACION.ACTA_ID ACTAS_PREMIACION.ID
FK_DETALLE_PREMIO DETALLES_PREMIACION.PREMIO_ID PREMIOS.ID

Referencias por ID entre esquemas (sin llave foránea)

Columna Apunta a Por qué no hay FK
REGISTROS.ID_CAMPANIA BOM_CAMPANIAS.CAMPANIAS Otro esquema; el módulo participacion consulta la campaña por CampaniaService
DETALLE_EVIDENCIAS.ID_HABITO BOM_CAMPANIAS.HABITOS Otro esquema; además el detalle copia NOMBRE_HABITO y PUNTAJE_UNITARIO como dato histórico
CUPOS_DIARIOS.ID_ESTUDIANTE BOM_PARTICIPACION.ESTUDIANTES Otro esquema
ACTAS_PREMIACION.CAMPANIA_ID BOM_CAMPANIAS.CAMPANIAS Otro esquema
DETALLES_PREMIACION.ESTUDIANTE_ID BOM_PARTICIPACION.ESTUDIANTES Otro esquema

4. Índices

Índice Columnas Para qué consulta
IX_REGISTRO_SEDE REGISTROS (SEDE, ESTADO, FECHA) Bandeja de validación y reporte de cada sede
IX_REGISTRO_ESTUDIANTE REGISTROS (ID_ESTUDIANTE) "Mis registros" y la llave foránea al estudiante
IX_EVIDENCIA_REGISTRO DETALLE_EVIDENCIAS (ID_REGISTRO) Cargar las evidencias de un registro
IX_DETALLE_ACTA, IX_DETALLE_PREMIO DETALLES_PREMIACION Detalle de un acta y premios ya entregados
IX_SOLICITUD_ESTADO SOLICITUDES_CUENTA (ESTADO, FECHA_SOLICITUD) Solicitudes por revisar
IX_EVENTOS_FECHA, IX_EVENTOS_ACCION EVENTOS Consulta de la auditoría por fecha y por acción

Las llaves primarias y las restricciones UNIQUE crean además su propio índice.

5. Particionamiento

REGISTROS está particionada por rango de FECHA con INTERVAL mensual (S22). La primera partición, P_INICIAL, guarda todo lo anterior a 2026; desde ahí Oracle crea sola una partición nueva cada vez que llega un registro de un mes que todavía no tiene la suya.

La tabla sigue siendo una sola para la aplicación: las consultas y las entidades JPA no cambiaron.

6. PL/SQL

Objeto Esquema Qué hace
OBTENER_PUNTAJE_ESTUDIANTE (estudiante, campaña) BOM_PARTICIPACION Suma solo los puntos de evidencias VALIDADO, igual que el ranking. Si el estudiante no existe lanza ORA-20001
HABITO_MAS_REGISTRADO (campaña, sede) BOM_PARTICIPACION Devuelve el hábito con más veces registradas; si no hay registros devuelve "Sin registros" (NO_DATA_FOUND)
TRG_EVENTOS_INMUTABLE BOM_AUDITORIA Trigger BEFORE UPDATE OR DELETE sobre EVENTOS: lanza ORA-20010 con el ID y el usuario del evento (:OLD). Ni el dueño del esquema puede alterar la auditoría sin deshabilitarlo

7. Privilegios

Usuario Permisos
BOMERP_APP CREATE SESSION; SELECT, INSERT, UPDATE, DELETE sobre las tablas de negocio
BOMERP_APP sobre BOM_AUDITORIA.EVENTOS Solo SELECT e INSERT: un evento de auditoría no se puede modificar ni borrar desde la aplicación
ROL_REPORTES Solo SELECT sobre registros, evidencias, estudiantes y eventos, y EXECUTE sobre las dos funciones: para quien solo necesita consultar
Dueños de esquema (BOM_CAMPANIAS, BOM_PARTICIPACION, BOM_PREMIOS, BOM_AUDITORIA) CREATE SESSION, CREATE TABLE, CREATE VIEW; la aplicación nunca se conecta con ellos