Volver a la lección

Ejercicios de modelado lógico relacional de bases de datos · Ejercicio: (difícil) Garajes automatizados

Ejercicio: (difícil) Garajes automatizados

Puedes preparar tu diagrama. .

Enunciado #

Una empresa opera garajes con reconocimiento de matrículas, barreras automáticas, sensores de ocupación y cobro automático. Admite abonados con suscripción y vehículos de rotación con ticket. Debe conservar accesos, ocupaciones, pagos, tarifas e incidencias, incluso cuando cambien las condiciones del servicio.

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.

Estructura y vehículos #

  • Garaje: id_garaje (entero), nombre (texto), direccion (texto), plazas_totales (entero).
  • Planta: id_garaje (entero), num_planta (entero), plazas_disponibles (entero).
  • Plaza: id_plaza (entero), id_garaje (entero), num_planta (entero), tipo (enum), tiene_cargador (boolean).
  • Propietario: id_propietario (entero), nombre (texto), email (texto), telefono (texto).
  • Vehiculo: matricula (texto), id_propietario (entero, opcional), tipo_vehiculo (texto), marca (texto), modelo (texto).

Abonos, tickets y cobros #

  • Suscripcion: id_suscripcion (entero), matricula (texto), id_garaje (entero), fecha_inicio (fechahora), fecha_fin (fechahora), tipo_abono (texto), id_plaza_asignada (entero, opcional).
  • Ticket: id_ticket (entero), id_garaje (entero), matricula (texto, opcional), ts_entrada (fechahora), ts_salida (fechahora, opcional), importe_calculado (decimal, opcional), id_plaza (entero, opcional), id_tarifa (entero, opcional).
  • Pago: id_pago (entero), id_ticket (entero, opcional), id_suscripcion (entero, opcional), ts_pago (fechahora), metodo (enum), importe (decimal).
  • Tarifa: id_tarifa (entero), id_garaje (entero), vigencia_desde (fechahora), tipo (enum), regla (texto).

Dispositivos y actividad #

  • Dispositivo: id_dispositivo (entero), id_garaje (entero), tipo (enum), ubicacion (texto).
  • EventoAcceso: id_evento (entero), id_dispositivo (entero), matricula (texto), ts_evento (fechahora), sentido (enum), resultado (enum).
  • OcupacionPlaza: id_plaza (entero), ts_ocupa (fechahora), ts_libera (fechahora, opcional), matricula (texto, opcional), id_ticket (entero, opcional).
  • Incidencia: id_incidencia (entero), id_dispositivo (entero), ts_incidencia (fechahora), severidad (enum), descripcion (texto), resuelta (boolean).

Claves, relaciones y reglas del negocio #

  1. La PK de Planta es (id_garaje, num_planta). Las demás tablas usan como PK su primer campo, salvo Vehiculo, identificado por matricula, y OcupacionPlaza, identificada por (id_plaza, ts_ocupa).
  2. Identificación de plazas: se usa un id_plaza globalmente único. Cada plaza pertenece a una planta mediante la FK compuesta (id_garaje, num_planta) hacia Planta. Esto evita referencias ambiguas cuando varios garajes o plantas usan la misma numeración local.
  3. Cada propietario puede tener varios vehículos. Para admitir matrículas de rotación cuyo propietario no está registrado, Vehiculo.id_propietario es opcional. La matrícula detectada debe registrarse en Vehiculo antes de referenciarla desde un evento.
  4. Cada suscripción vincula una matrícula y un garaje durante el intervalo inclusivo [fecha_inicio, fecha_fin]. Su identificador permite renovaciones. Una plaza asignada es opcional, debe pertenecer al mismo garaje y no puede reservarse a matrículas incompatibles en periodos solapados. tipo_abono es texto abierto.
  5. Un ticket pertenece a un garaje y puede tener matrícula y plaza desconocidas. Su salida, importe y tarifa se informan al cerrarlo. El tipo de plaza admite normal, PMR, EV y moto.
  6. Cada pago corresponde exactamente a un ticket o a una suscripción. Se sustituye la matrícula ambigua del pago del documento por id_suscripcion, para identificar también qué abono o renovación se está pagando. La matrícula se obtiene del ticket o de la suscripción. Un ticket o suscripción puede recibir varios pagos; metodo admite tarjeta, TPV o app.
  7. Una tarifa pertenece a un garaje, tiene tipo ROTACION o ABONO y una regla de cálculo en texto. La combinación (id_garaje, tipo, vigencia_desde) debe ser única; el editor no permite marcar esa unicidad compuesta por separado. Al cerrar un ticket, selecciona la tarifa ROTACION de ese garaje con el mayor vigencia_desde que no supere ts_salida y conserva su id_tarifa. No modifiques retroactivamente una tarifa ya utilizada.
  8. Cada dispositivo pertenece a un garaje y tiene tipo CAMARA, BARRERA o SENSOR. Cada evento referencia un dispositivo y una matrícula; sentido admite ENTRADA o SALIDA y resultado, AUTORIZADO, DENEGADO o MANUAL.
  9. Adaptación de integridad: el garaje de EventoAcceso se obtiene a través del dispositivo, sin duplicar un id_garaje que pudiera ser incoherente. Tampoco se almacena la fecha por separado: se calcula desde ts_evento. Cada incidencia pertenece a un dispositivo; severidad admite baja, media, alta o critica.
  10. Cada ocupación pertenece a una plaza. Usa id_ticket para la rotación, o matricula para un abonado, de forma exclusiva. El ticket debe ser del mismo garaje y la matrícula de abonado debe tener una suscripción activa allí. Se puede volver a ocupar la misma plaza en otro instante.
  11. Una plaza no puede tener dos ocupaciones abiertas ni intervalos de ocupación solapados. ts_libera nulo deja el intervalo abierto. Estas reglas requieren índices parciales, restricciones de exclusión o validaciones transaccionales, según el motor; no marques id_plaza como único en todo el historial.
  12. La autorización de acceso requiere una suscripción activa de la matrícula en ese garaje o un ticket abierto compatible. Una plaza reservada solo puede ocuparse por su matrícula mientras el abono esté activo. Un vehículo EV puede usar una plaza sin cargador, pero no cargar en ella.
  13. Al cerrar un ticket, sus pagos deben sumar al menos importe_calculado. El importe y la tarifa deben estar informados. La coherencia entre plaza, garaje, tarifa, abono y ticket, y la suficiencia de los pagos, necesitan validación entre tablas.
  14. plazas_totales y plazas_disponibles se conservan como contadores materializados del documento: deben coincidir con las plazas y ocupaciones y actualizarse transaccionalmente. No son fuentes independientes de la capacidad y la ocupación.

Diseña las trece tablas. Además de las relaciones simples, conecta los dos campos de Plaza con la PK compuesta de Planta. Documenta las validaciones temporales y transaccionales que exceden el diagrama.

CHECK que debes configurar #

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

En Suscripcion, campo fecha_fin:

1fecha_fin >= fecha_inicio

En Ticket, campo ts_salida:

1ts_salida IS NULL OR ts_salida >= ts_entrada

En Ticket, campo importe_calculado:

1(importe_calculado IS NULL OR importe_calculado >= 0) AND (ts_salida IS NULL OR (importe_calculado IS NOT NULL AND id_tarifa IS NOT NULL))

En OcupacionPlaza, campo ts_libera:

1ts_libera IS NULL OR ts_libera >= ts_ocupa

En OcupacionPlaza, campo id_ticket:

1(id_ticket IS NOT NULL AND matricula IS NULL) OR (id_ticket IS NULL AND matricula IS NOT NULL)

En Pago, campo id_suscripcion:

1(id_ticket IS NOT NULL AND id_suscripcion IS NULL) OR (id_ticket IS NULL AND id_suscripcion IS NOT NULL)

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.

Cargando actividad al acercarte…