Ejercicio: (difícil) The Eye

Enunciado #

En la ciudad ficticia Panopticon City, THE EYE registra accesos a domicilios, trayectos, compras e interacciones sociales. Genera alertas y sanciones y mantiene un nivel de confianza por ciudadano. Diseña el modelo relacional de este escenario narrativo con sus registros históricos.

El documento pide inventar una tabla adicional. Para concretar la solución se propone HistorialConfianza, que permite reconstruir los cambios de puntuación y su motivo.

Datos que debes almacenar #

Se usan identificadores internos salvo que se indique una clave natural. Los campos opcionales deben admitir NULL. La lista fija nombres y tipos para poder comparar tu diseño con la solución del challenge.

Ciudadanía y accesos #

• Ciudadano: id_ciudadano (entero), nombre (texto), fecha_nacimiento (fecha), nivel_confianza (entero), estado (enum), ultima_actualizacion (fechahora).
• Domicilio: id_domicilio (entero), direccion (texto), tipo (texto), id_propietario (entero).
• RegistroAcceso: id_acceso (entero), id_ciudadano (entero), id_domicilio (entero), tipo (enum), instante (fechahora).

Movilidad y actividad #

• Trayecto: id_trayecto (entero), id_ciudadano (entero), inicio (fechahora), fin (fechahora, opcional), riesgo_asociado (decimal), motivo_alerta (texto, opcional).
• PuntoTrayecto: id_punto (entero), id_trayecto (entero), latitud (decimal), longitud (decimal), instante (fechahora).
• Compra: id_compra (entero), id_ciudadano (entero), comercio (texto), categoria (texto), coste (decimal), instante (fechahora).
• InteraccionSocial: id_interaccion (entero), id_ciudadano_a (entero), id_ciudadano_b (entero), duracion_segundos (entero), zona (texto), instante (fechahora).

Alertas e historial #

• Alerta: id_alerta (entero), id_ciudadano (entero), tipo_alerta (texto), nivel_gravedad (entero), instante (fechahora), procesada (boolean).
• Sancion: id_sancion (entero), id_alerta (entero), tipo_sancion (texto), instante (fechahora).
• HistorialConfianza: id_cambio (entero), id_ciudadano (entero), nivel_anterior (entero), nivel_nuevo (entero), motivo (texto), instante (fechahora).

Claves, relaciones y reglas del negocio #

1. Todas las tablas tienen como PK su campo inicial id_.... Cada domicilio tiene un propietario, que puede poseer varios domicilios. Los accesos pueden corresponder a cualquier ciudadano, no solo al propietario.
2. Cada acceso registra una entrada o salida (tipo: entrada, salida) y pertenece a un ciudadano y a un domicilio. No se sobrescriben visitas anteriores.
3. Un ciudadano puede realizar muchos trayectos. Cada trayecto contiene muchos puntos; un punto pertenece a un único trayecto. Ordena sus puntos por instante y, si coinciden, por id_punto. fin permanece nulo mientras esté en curso.
4. Cada compra pertenece a un ciudadano; comercio y categoría se mantienen como texto según el alcance del documento. El coste no puede ser negativo.
5. Cada interacción relaciona dos ciudadanos con papeles distintos, id_ciudadano_a e id_ciudadano_b, y registra duración, zona e instante. No crees dos tablas de ciudadanos. Para evitar el mismo par invertido, usa el identificador menor en A y el mayor en B; una misma pareja puede volver a interactuar en otro momento.
6. Un ciudadano puede tener varias alertas y cada alerta puede dar lugar a varias sanciones de tipos diferentes. Una alerta solo origina sanciones cuando ha sido confirmada; procesada indica que ya se revisó, no que necesariamente se confirmó. Esa decisión se valida en el proceso que crea las sanciones.
7. Adaptación para evitar redundancia: en Sancion se conserva id_alerta y el ciudadano sancionado se obtiene de esa alerta. No añadas otro id_ciudadano que pudiera apuntar a una persona diferente.
8. estado de ciudadano admite activo, fugitivo, desaparecido, sancionado u observado. Los tipos de domicilio, compra, alerta y sanción son textos abiertos con los ejemplos del documento (vivienda, bunker, alimentos, ruta_irregular, toque_de_queda, etc.).
9. HistorialConfianza registra cada cambio con los niveles anterior y nuevo, el motivo y el instante. Ciudadano.nivel_confianza es la puntuación actual materializada que usa el sistema; actualizarla y añadir el historial debe hacerse en una misma transacción.
10. El intervalo de un trayecto debe contener las fechas de sus puntos. Las duraciones y el riesgo no pueden ser negativos. Los cambios de confianza deben encadenarse y terminar en el nivel actual. Estas condiciones requieren validación adicional a las conexiones del diagrama.

Diseña las diez tablas, sus once relaciones y los CHECK indicados. La creación de reglas algorítmicas de evaluación no forma parte del ejercicio.

CHECK que debes configurar #

Escribe cada expresión exactamente en el campo indicado; los demás CHECK se dejan vacíos.

En Ciudadano, campo nivel_confianza:

1nivel_confianza BETWEEN 0 AND 100

En Alerta, campo nivel_gravedad:

1nivel_gravedad BETWEEN 1 AND 5

En Trayecto, campo fin:

1fin IS NULL OR fin >= inicio

En InteraccionSocial, campo id_ciudadano_b:

1id_ciudadano_a < id_ciudadano_b

En HistorialConfianza, campo nivel_anterior:

1nivel_anterior BETWEEN 0 AND 100

En HistorialConfianza, campo nivel_nuevo:

1nivel_nuevo BETWEEN 0 AND 100

Criterios para el challenge #

Conserva los nombres y tipos indicados. Todos los campos son obligatorios (NN) salvo los marcados como opcionales. Marca PK y FK según las reglas y las restricciones de unicidad indicadas. Una PK compuesta identifica por la combinación; no marques cada componente como único por separado.

Dibuja cada conexión desde el campo referenciado hacia la FK. Usa extremos 1 y n, o 1 y 1 para las relaciones de uno a uno. La opcionalidad se expresa con NN desmarcado en la FK. Si una FK es compuesta, dibuja una conexión por cada componente; en SQL todas esas columnas deben formar una única FOREIGN KEY hacia la misma fila.

Deja desactivados autoincremento, clave alternativa e índice. Deja vacíos valor por defecto, ON DELETE, ON UPDATE y CHECK, salvo los CHECK indicados expresamente. Las restricciones compuestas adicionales, los índices de explotación y las reglas entre varias filas se documentan aparte: el corrector del diagrama no ejecuta operaciones del negocio ni sustituye esas validaciones.