P1 · Esquema y consultas

Publicado: semana 3 · Entrega: semana 6 · En parejas

El esquema de partida de Chasqui ya tiene claves primarias, claves foráneas y algunos CHECK, pero todavía acepta datos que no pueden ocurrir. En esta entrega lo endurecen, y escriben las consultas que la gerencia de Chasqui pide cada semana. Cubre las semanas 3 a 5: subconsultas, joins internos y externos, vistas, restricciones de integridad y funciones de ventana.

1. Restricciones: schema/constraints.sql

Un script que, aplicado sobre el esquema de partida con los datos de ejemplo, agrega restricciones sin borrar ni modificar ningún dato. Cada restricción lleva un nombre explícito (CONSTRAINT shipment_delivery_needs_trip_ck ...), porque el nombre es lo que ve quien recibe el error. Como mínimo:

1. Un viaje no puede llevar más peso que la capacidad de su vehículo: la suma de weight_kg de sus envíos no supera capacity_kg. Decidan si esto se puede expresar con un CHECK o si hace falta otra cosa (por ejemplo, un trigger, que vemos en la semana 5), y justifíquenlo. La regla se puede romper desde tres tablas: al insertar o modificar un envío (su peso o su viaje), al cambiar el vehículo de un viaje y al bajar la capacidad de un vehículo. Las pruebas intentan las tres. Un viaje cargado justo hasta su capacidad es válido.
2. Un envío entregado (delivered) tiene delivered_at; uno que no fue entregado no lo tiene.
3. Un envío con trip_id no puede estar en estado registered, y uno en in_transit o delivered tiene que tener viaje.
4. El viaje de un envío recorre la ruta del envío (mismo origen y destino). Pista: una clave foránea compuesta necesita una clave candidata compuesta del otro lado. Ojo: trip no tiene columnas de origen y destino, solo route_id; piensen qué habría que agregar para usar una clave foránea, o si conviene un trigger. La regla se rompe al insertar un envío, al pasarlo a otro viaje y al cambiar la ruta de un viaje que ya lleva envíos.
5. Las acciones referenciales: decidan, para cada clave foránea, qué debe pasar al borrar la fila referenciada (RESTRICT, CASCADE, SET NULL) y escríbanlo en el README. No hay una única respuesta correcta; sí hay respuestas mal justificadas.

Los datos de ejemplo no están limpios. Por ejemplo, hay vehículos con dos viajes que salen a la misma hora, así que UNIQUE (vehicle_id, departure_at) falla al aplicarse sobre los datos. Como constraints.sql no puede borrar ni cambiar datos, una regla así queda fuera de esta entrega: si encuentran alguna, anótenla en el README con la consulta que muestra las filas que la violan.

Las pruebas públicas intentan insertar datos inválidos y esperan el error de PostgreSQL correspondiente (código 23514 para CHECK, 23503 para claves foráneas, 23505 para UNIQUE), y verifican que los datos válidos se siguen pudiendo insertar. En detalle:

• Las reglas 2 y 3 se esperan con el código 23514: son CHECK.
• Las reglas 1 y 4 aceptan cualquier error de integridad (clase 23) o de un RAISE EXCEPTION en un trigger (clase P0); RAISE EXCEPTION ... USING ERRCODE = 'check_violation' da 23514, que también sirve.
• Las pruebas no miran los nombres de las restricciones (la revisión, sí).
• Cada intento corre en una transacción que se deshace al final, con SET CONSTRAINTS ALL IMMEDIATE: una restricción DEFERRABLE se revisa en el momento, no al COMMIT.
• Las pruebas insertan y modifican filas usando solo las columnas del esquema de partida. Si agregan una columna, tiene que aceptar que no se la den (un DEFAULT, o un trigger que la llene).
• Otra prueba compara cada tabla, fila por fila, con los datos de ejemplo: constraints.sql no puede borrar ni modificar datos.

2. Consultas: queries/q11.sql … q20.sql

Como en P0, cada archivo trae en un comentario su enunciado, con los nombres de las columnas, el orden de las filas y cómo se desempatan; se escribe una sola sentencia SELECT debajo y se compara con expected/qNN.tsv. Incluyen, entre otras: cuántos envíos registró cada agencia en una semana, también las que no registraron ninguno (outer join), los clientes que enviaron a todas las regiones del sur (división relacional con NOT EXISTS), el envío más pesado de cada ruta (subconsulta correlacionada y luego DISTINCT ON o una función de ventana), el ranking mensual de agencias por ingresos (rank() OVER), y el porcentaje de envíos devueltos por región con los NULL bien tratados.

3. Vistas: schema/views.sql

• shipment_detail: un envío por fila con los nombres de las agencias, del remitente y del destinatario, y las horas de salida y llegada de su viaje. Están todos los envíos, también los que no tienen viaje (con trip_id, departure_at y arrival_at en NULL).
• agency_daily_revenue: ingresos por agencia y por día, en hora de Lima. Una fila por agencia (la de origen del envío) y por día en que registró al menos un envío, cualquiera sea su estado; los días sin envíos no aparecen. El día tiene que salir en hora de Lima aunque la sesión use otra zona horaria: las pruebas la consultan también con SET timezone = 'UTC'.

Las columnas, en este orden:

Las consultas q16–q20 deben usar estas vistas donde simplifiquen.

README

Las decisiones de ON DELETE y por qué; cuál de las restricciones del punto 1 no se pudo expresar como CHECK y qué hicieron en su lugar; y, para dos consultas, una versión alternativa equivalente (por ejemplo, con JOIN y con subconsulta) y cuál les parece más clara.

Archivos

Los archivos de P0 (queries/q01.sql … q10.sql, top-routes.mjs, p0.public.test.mjs y su expected/) se quedan en la misma carpeta.

Pruebas

El Makefile es el de P0 con un paso más. make test:

1. crea de nuevo la base de datos de pruebas con los datos de ejemplo;
2. aplica schema/constraints.sql y después schema/views.sql con psql -v ON_ERROR_STOP=1: si un script falla, make test se detiene ahí y no corre ninguna prueba;
3. corre todos los *.test.mjs: las pruebas de P0 y de P1, y las suyas.

Las consultas de P0 se corren con las restricciones ya puestas, así que deben seguir pasando. Como la prueba de P0 compara todo lo que hay en expected/, q11–q20 aparecen dos veces en la salida: una por cada archivo de pruebas.