Ejercicio: (avanzado) Paquetería

Enunciado #

Una empresa de logística necesita gestionar los paquetes enviados por sus clientes, sus estaciones de tránsito y los empleados que los manejan. Cada paquete tiene un remitente y un destinatario, un origen y un destino, y un historial de pasos por estaciones.

Datos que debes almacenar #

• Cliente: id_cliente (entero, identificador), nombre (texto), apellido (texto), direccion (texto), telefono (texto) y email (texto).
• Paquete: id_paquete (entero, identificador), peso (decimal; kg), largo (decimal; cm), ancho (decimal; cm), alto (decimal; cm), fecha_envio (fechahora), id_remitente (entero), id_destinatario (entero), direccion_origen (texto) y direccion_destino (texto).
• Estacion: id_estacion (entero, identificador), nombre (texto), ciudad (texto), estado (texto) y tipo (texto; centro de distribución, punto de tránsito, etc.).
• Empleado: id_empleado (entero, identificador), nombre (texto), apellido (texto), posicion (texto) e id_estacion (entero; estación de trabajo).
• Transito: id_paquete (entero), id_estacion (entero), fecha_llegada (fechahora), fecha_salida (fechahora, opcional mientras siga en la estación) e id_empleado (entero; responsable de ese paso).

Reglas del negocio #

1. Cada paquete tiene un remitente y un destinatario. Ambos son clientes: crea dos relaciones distintas desde Cliente hacia Paquete, una por cada papel.
2. Un cliente puede enviar y recibir muchos paquetes. No crees tablas separadas para remitentes y destinatarios ni marques sus FK como únicas.
3. Guarda las dimensiones como tres valores atómicos. Las direcciones de origen y destino se conservan en el paquete tal como eran al enviarlo, aunque el cliente cambie después su dirección habitual.
4. Cada empleado trabaja en una sola estación y una estación puede tener varios empleados. No se pide un historial de cambios de estación del personal.
5. Usa (id_paquete, id_estacion, fecha_llegada) como PK compuesta de Transito. Así se puede registrar que un paquete vuelva a una estación en otro momento.
6. Cada tránsito corresponde a un paquete, a una estación y a un único empleado responsable. Ese empleado puede manejar muchos tránsitos.
7. El responsable debe trabajar en la estación de ese tránsito. Las FK simples hacia Empleado y Estacion no garantizan esa coincidencia: al implementar la base de datos habría que añadir una referencia compuesta adecuada o una validación adicional.
8. La salida, cuando exista, no puede ser anterior a la llegada. El peso y las dimensiones deben ser positivos; estas reglas se validarían al implementar la base de datos.

Aclaración del alcance: se añaden las direcciones históricas de origen y destino para concretar esos datos del contexto. El historial permite pasos repetidos por una estación y cada paso tiene un solo responsable.

Diseña las cinco tablas y sus seis relaciones, diferenciando los papeles de remitente y destinatario.

Criterios para el challenge #

Conserva los nombres de tablas y campos indicados; los nombres de campos están escritos sin tildes y con guiones bajos. Los tipos entre paréntesis corresponden a las opciones del editor. Todos los campos son obligatorios (NN), salvo los marcados como opcionales; marca las claves primarias (PK), foráneas (FK) y las restricciones de unicidad que se deducen de las reglas.

Deja desactivados autoincremento, clave alternativa e índice. Deja vacíos el valor por defecto, las acciones ON DELETE y ON UPDATE y, salvo que se indique expresamente, CHECK. Usa los extremos 1 y n, o 1 y 1, en las conexiones; una FK opcional tiene NN desmarcado.

Una PK compuesta identifica por la combinación de sus campos: no marques cada componente como único por separado. Si una FK referencia una PK compuesta, dibuja una conexión por cada pareja de campos; todas juntas representan una única referencia a la misma fila. En una implementación SQL se debe declarar una sola FOREIGN KEY con todas esas columnas, no varias referencias independientes. El editor no comprueba por sí solo las reglas sobre varias filas ni todas las restricciones compuestas.