En la lección anterior consultabas un esquema que ya existía. Aquí no hay esquema: hay un cliente que te cuenta lo que necesita, en el desorden con el que lo cuentan los clientes, y tu trabajo es convertir ese relato en tablas, claves y restricciones que no se puedan romper.

Cómo trabajar esta lección. Cada ejercicio es un caso completo, no una pregunta de un minuto. Lee el enunciado entero, coge papel —o un editor de texto— y haz los cuatro pasos por tu cuenta antes de mirar nada:

1. Identificar entidades y relaciones: subraya los sustantivos del enunciado, decide cuáles son entidades y cuáles atributos, y anota la cardinalidad de cada relación.
2. Dibujar el diagrama ER con notación de pata de gallo (puedes usar mermaid, papel o cualquier herramienta).
3. Escribir el CREATE TABLE aplicando las diez reglas de transformación de la lección 04-03, con sus claves ajenas y sus acciones ON DELETE / ON UPDATE.
4. Añadir las restricciones —NOT NULL, UNIQUE, CHECK, DEFAULT, dominios, EXCLUDE— que codifican las reglas de negocio del enunciado.

Solo después compara con la solución propuesta. Es normal que tu esquema no sea idéntico: en diseño casi nunca hay una única respuesta correcta, sino respuestas defendibles y respuestas que rompen un requisito. Por eso cada solución termina con un apartado de decisiones discutibles, donde se explica qué alternativas también serían válidas y qué se gana y se pierde con cada una. Y al final de la lección hay una rúbrica de autoevaluación con la que puedes puntuar tu propio diseño.

En esta lección no se hace análisis formal de dependencias funcionales ni se citan formas normales: eso es exactamente la materia de 07-03. Aquí diseñamos bien desde el principio; allí diagnosticamos y arreglamos diseños que ya han salido mal.

No necesitas ningún juego de datos cargado: cuatro de los cinco casos son dominios nuevos, y el quinto se apoya en el esquema de BiblioRed que ya conoces. Sí necesitas tener a mano:

• Las diez reglas de transformación ER → relacional (lección 04-03), en especial las de relaciones N:M, entidades débiles, jerarquías de generalización y relaciones ternarias.
• El catálogo de restricciones de la lección 04-04: CHECK, UNIQUE sobre varias columnas, UNIQUE parcial mediante índice, dominios (CREATE DOMAIN), columnas generadas y EXCLUDE con btree_gist.
• Una sesión psql para ejecutar tus CREATE TABLE. No des por bueno un esquema que no has ejecutado: la mitad de los errores de diseño los detecta el propio motor al crear las tablas.

Para el ejercicio 2 conviene tener presente el esquema de BiblioRed, en particular sucursales, materiales, ejemplares, socios y prestamos.

Si vas a usar restricciones EXCLUDE, activa la extensión una sola vez por base de datos:

SQLite. No soporta CREATE DOMAIN, ni EXCLUDE, ni ALTER TABLE ADD CONSTRAINT, y solo aplica las claves ajenas si activas PRAGMA foreign_keys = ON. Los CHECK sí funcionan. Donde una solución use algo exclusivo de PostgreSQL, se indica la alternativa portable.

1. Ejercicio 1 — Básico: videoclub de barrio
2. Ejercicio 2 — Intermedio: préstamo interbibliotecario en BiblioRed
3. Ejercicio 3 — Intermedio: plataforma de cursos en línea
4. Ejercicio 4 — Avanzado: taller mecánico con jerarquía y relación ternaria
5. Ejercicio 5 — Avanzado: tarifas con vigencia temporal
6. Errores comunes y consejos
7. Ejercicios de refuerzo
8. Rúbrica de autoevaluación

Dificultad: Básico

Enunciado. «Cinema Vallmar» es un videoclub que sobrevive alquilando películas en formato físico. Su dueño te cuenta lo siguiente:

«Tengo unas 3.000 películas. De cada una guardo el título, el año, la duración en minutos y la clasificación por edades. Cada película es de un género —drama, comedia, documental...— aunque hay algunas que son de dos, y me gustaría poder buscarlas por los dos. De cada película tengo entre una y seis copias físicas; cada copia tiene una etiqueta pegada con un código, un formato (DVD o Blu-ray) y un estado, porque algunas están rayadas y no las alquilo. Los clientes se hacen socios con nombre, teléfono y correo; el correo no se puede repetir. Cuando alguien alquila una copia apunto la fecha, la fecha de devolución prevista y, cuando la trae, la real. Un mismo socio puede tener varias copias alquiladas a la vez, pero una copia solo puede estar alquilada a una persona. También me gustaría poder buscar por actor: cada película tiene varios actores y cada actor sale en varias películas, y me interesa saber qué personaje interpretaba.»

Necesita poder responder: qué copias de una película están disponibles ahora mismo, qué tiene alquilado un socio, qué películas hay de un género, en qué películas ha trabajado un actor y qué alquileres están vencidos.

Pista. Hay dos relaciones N:M en el enunciado, y una de ellas tiene un atributo propio.

Paso 1 — Entidades y relaciones

Relaciones:

• peliculas N:M generos → tabla intermedia peliculas_generos.
• peliculas N:M actores, con atributo personaje → tabla intermedia reparto.
• peliculas 1:N copias (una película tiene entre 1 y 6 copias).
• socios 1:N alquileres, copias 1:N alquileres.

La distinción clave del caso es película frente a copia. La película es la obra; la copia es el disco de plástico con una etiqueta. El socio no alquila «Casablanca»: alquila la copia CV-0412. Confundir ambas es el error de diseño más frecuente en este dominio, y es exactamente la misma distinción que hay en BiblioRed entre materiales y ejemplares.

Paso 2 — Diagrama ER

Paso 3 — CREATE TABLE

Paso 4 — Restricciones que codifican las reglas de negocio

El esquema debe responder las cinco consultas del enunciado. Comprobación rápida:

Por qué generos es una tabla y no una columna de texto. El dueño dijo «hay algunas que son de dos géneros». Eso descarta de plano una columna genero VARCHAR(40) y descarta con más fuerza aún el anti-patrón de la lista con comas ('drama,comedia'), que ya vimos en 04-01: hace imposible el índice, imposible el JOIN e imposible garantizar que el texto esté bien escrito. Una tabla generos con UNIQUE (nombre) impide además que convivan «Documental», «documental» y «documentales».

La clave primaria de reparto incluye personaje. Es una decisión discutible y merece la pena entender por qué. Con PRIMARY KEY (pelicula_id, actor_id), un actor que interpreta dos papeles en la misma película —gemelos, dobles papeles— sería imposible de registrar. Incluir personaje en la clave lo permite. La alternativa igualmente válida es poner una clave sustituta reparto_id SERIAL y un UNIQUE (pelicula_id, actor_id, personaje); el efecto es el mismo y las claves ajenas hacia reparto quedan más cortas si algún día hacen falta.

Las acciones referenciales no son todas iguales, y eso es deliberado:

El índice único parcial es la pieza más interesante del diseño. La regla «una copia solo puede estar alquilada a una persona» no se puede expresar con un UNIQUE (copia_id) sobre alquileres, porque entonces una copia solo podría alquilarse una vez en toda su historia. Lo que hay que restringir es que haya como mucho un alquiler abierto por copia, y eso es exactamente lo que hace CREATE UNIQUE INDEX ... WHERE fecha_devolucion IS NULL. Es una restricción de PostgreSQL; en SQLite existe el mismo índice parcial, en MySQL 8 no, y allí habría que resolverlo con un disparador o confiando en la aplicación (peor).

Alternativa razonable que no he tomado: una columna copias.estado redundante con la existencia de un alquiler abierto. La he mantenido porque el dueño distingue estados que no dependen del alquiler (dañada, retirada), pero eso introduce una posibilidad de incoherencia: una copia disponible con un alquiler abierto. Si el volumen fuera mayor, valdría la pena mantenerla con un disparador; con 3.000 películas, la aplicación puede encargarse.

Dificultad: Intermedio

Enunciado. BiblioRed quiere lanzar un servicio de préstamo interbibliotecario. La dirección lo describe así:

«Si un socio de Sur quiere un libro que solo está en Norte, ahora tiene que desplazarse. Queremos que pueda pedirlo desde su sucursal y que el ejemplar viaje. También queremos poder pedir a bibliotecas de otras ciudades con las que tenemos convenio —tienen nombre, ciudad, persona de contacto y correo—, y prestarles a ellas. Cada petición la hace un socio en una sucursal, sobre un material concreto (no sobre un ejemplar: nos da igual cuál venga). La petición pasa por estados: solicitada, aceptada, en tránsito, disponible para recoger, prestada, devuelta, rechazada o cancelada, y de cada cambio de estado queremos saber cuándo ocurrió y quién lo hizo. Cuando la petición se acepta, se le asigna un ejemplar concreto. El envío tiene un coste que paga la biblioteca solicitante, y las peticiones a bibliotecas externas tienen un plazo máximo distinto del interno.»

Restricciones adicionales: una petición se dirige o bien a otra sucursal de BiblioRed o bien a una biblioteca externa, nunca a las dos ni a ninguna. Un socio no puede tener más de tres peticiones activas simultáneamente (esta la puedes dejar para la aplicación, pero indícalo).

Pista. El historial de cambios de estado es una entidad débil que depende de la petición.

Paso 1 — Entidades y relaciones

Entidades nuevas (las existentes de BiblioRed no se tocan):

Relaciones:

• socios 1:N peticiones_interbib (quién pide).
• sucursales 1:N peticiones_interbib como origen (dónde lo recoge).
• sucursales 1:N peticiones_interbib como destino opcional (a quién se pide).
• bibliotecas_externas 1:N peticiones_interbib como destino opcional.
• materiales 1:N peticiones_interbib (qué se pide).
• ejemplares 1:N peticiones_interbib (qué ejemplar se asignó, nulo hasta la aceptación).
• peticiones_interbib 1:N peticiones_estados (identificadora, entidad débil).

Paso 2 — Diagrama ER

Paso 3 — CREATE TABLE

Paso 4 — Restricciones

Tres tablas nuevas, cero modificaciones en las tablas existentes de BiblioRed. Ejemplo de carga coherente:

Y la comprobación de que la regla del destino exclusivo funciona:

Diseñar sobre un esquema existente cambia las reglas del juego. El requisito implícito más fuerte de este ejercicio es que no puedes romper nada. Por eso la solución no añade columnas a prestamos ni a ejemplares: cualquier consulta, informe o índice que ya existiera en BiblioRed sigue funcionando exactamente igual después de instalar el servicio.

La petición apunta a materiales y no a ejemplares, porque el socio dijo «me da igual cuál venga». ejemplar_id es nulo al principio y se rellena al aceptar. Ese NULL no es una carencia del diseño: es información («todavía no se ha asignado»), y el CHECK ck_pet_ejemplar_estado la ata al estado para que no pueda haber una petición «en tránsito» sin ejemplar.

Dos claves ajenas hacia la misma tabla. sucursal_origen_id y sucursal_destino_id apuntan las dos a sucursales. Es perfectamente legal y muy habitual; lo único que exige es nombres de columna que digan su papel, porque sucursal_id a secas sería ambiguo. Es el mismo patrón que ya aparece en BiblioRed entre socios.sucursal_id y ejemplares.sucursal_id.

El destino exclusivo: tres alternativas.

La tercera es la que suele proponer quien viene de un ORM y es la única claramente incorrecta.

El historial como entidad débil. peticiones_estados tiene clave primaria (peticion_id, secuencia): el número de secuencia solo tiene sentido dentro de su petición. Es el caso de libro de entidad débil con relación identificadora, y por eso lleva ON DELETE CASCADE: si la petición desaparece, su historial no significa nada.

Aquí hay una redundancia deliberada: estado_actual en la cabecera duplica el último estado del historial. Se podría calcular siempre con ORDER BY secuencia DESC LIMIT 1, pero es la consulta más frecuente del sistema (el panel de bandeja de entrada de cada sucursal) y la columna permite indexarla. Es la desnormalización controlada de la lección 05-04: se acepta a cambio de tener que mantener las dos cosas sincronizadas, idealmente con un disparador.

El límite de tres peticiones activas por socio. No se puede expresar con un CHECK, porque un CHECK solo ve la fila que se está insertando y esta regla implica contar filas de la tabla. Las opciones reales son un disparador BEFORE INSERT que cuente y lance excepción, o la lógica de aplicación dentro de la transacción con un SELECT ... FOR UPDATE sobre el socio. Lo importante es decirlo en el diseño, no dejarlo implícito: una regla de negocio sin restricción es una regla que algún día se incumplirá.

Dificultad: Intermedio

Enunciado. Una plataforma de formación quiere su base de datos:

«Tenemos cursos, y cada curso está dividido en módulos, y cada módulo en lecciones. Las lecciones van numeradas dentro de su módulo y los módulos dentro de su curso. Una lección tiene título, tipo (vídeo, texto o cuestionario) y duración estimada. Los cursos pueden tener requisitos previos: para hacer «SQL avanzado» hay que haber hecho antes «SQL básico», y un curso puede tener varios requisitos. Los alumnos se matriculan en cursos; de la matrícula guardamos la fecha, el precio pagado y si está activa, completada o abandonada. Queremos saber, de cada alumno y cada lección, si la ha terminado y cuándo; también el porcentaje de avance del curso. Las lecciones de tipo cuestionario tienen preguntas con varias opciones, de las que una es la correcta, y cada intento de un alumno guarda la nota y la fecha. Un alumno puede intentar un cuestionario varias veces.»

Pista. «Requisitos previos» es una relación N:M de una tabla consigo misma.

Paso 1 — Entidades y relaciones

Jerarquía de contenidos: cursos 1:N modulos 1:N lecciones. Los tres son entidades fuertes con clave sustituta, pero modulos y lecciones llevan además una clave alternativa que refleja su numeración dentro del padre.

Autorreferencia: cursos N:M cursos a través de requisitos_curso (curso_id, requisito_id).

N:M con atributos: alumnos N:M cursos a través de matriculas, que tiene fecha, precio y estado propios.

Progreso: matriculas N:M lecciones a través de progreso_leccion. Nota que el progreso se cuelga de la matrícula, no del alumno: si alguien se matricula dos veces en el mismo curso, cada matrícula tiene su propio avance.

Cuestionarios: lecciones 1:N preguntas 1:N opciones; matriculas 1:N intentos (sobre una lección de tipo cuestionario).

Paso 2 — Diagrama ER

Paso 3 — CREATE TABLE

Paso 4 — Restricciones

Once tablas. La consulta del porcentaje de avance, que es la razón de ser de media plataforma, sale directa:

El progreso cuelga de la matrícula, no del alumno. Es la decisión más importante del diseño y la más fácil de errar. Si progreso_leccion tuviera (alumno_id, leccion_id), un alumno que abandona un curso y se vuelve a matricular arrastraría todo su avance anterior, y no habría forma de saber a qué matrícula pertenecía cada lección completada. Con (matricula_id, leccion_id), cada intento de hacer el curso tiene su propia historia. El precio es un JOIN más para llegar del alumno al progreso; es un precio bajo.

La numeración dentro del padre: clave sustituta + UNIQUE compuesto. modulos tiene modulo_id como clave primaria y UNIQUE (curso_id, orden) como clave alternativa. La alternativa —clave primaria compuesta (curso_id, orden)— también es defendible y es más «pura», pero tiene dos inconvenientes prácticos: reordenar los módulos obliga a actualizar la clave primaria (y en cascada todas las lecciones), y las claves ajenas hacia lecciones acabarían siendo de tres columnas. Con clave sustituta, reordenar es un UPDATE de la columna orden y nada más.

La autorreferencia N:M. requisitos_curso es una tabla de reunión cuyas dos claves ajenas apuntan a la misma tabla. El CHECK (curso_id <> requisito_id) impide el caso trivial de que un curso sea requisito de sí mismo. Lo que no puede impedir ninguna restricción declarativa es un ciclo más largo: A exige B, B exige C, C exige A. Detectar eso requiere recorrer el grafo, y se hace con una CTE recursiva —la verás en 07-04— o con un disparador que la ejecute antes de insertar.

uq_opcion_correcta garantiza como mucho una correcta, no exactamente una. El índice único parcial impide dos opciones marcadas como correctas en la misma pregunta, pero no impide que no haya ninguna. Esa segunda mitad de la regla —«al menos una»— es una restricción de conjunto que las bases de datos relacionales no expresan bien de forma declarativa; se resuelve con un disparador AFTER o con una comprobación al publicar el curso. Reconocer el límite es parte del diseño.

Alternativa razonable que no he tomado: guardar en intentos las respuestas concretas de cada pregunta (intentos_respuestas). El enunciado solo pedía la nota, y añadir esa tabla sin que nadie la pida es sobrediseño. Pero si mañana quieren estadísticas de «qué pregunta falla más gente», haría falta, y la extensión sería limpia: (intento_id, pregunta_id, opcion_id).

Dificultad: Avanzado

Enunciado. Un taller mecánico de Vallmar:

«Atendemos vehículos: coches, motos y furgonetas. De todos guardamos matrícula, marca, modelo, año y el cliente propietario. De los coches nos interesa además el número de plazas y el tipo de combustible; de las motos, la cilindrada; y de las furgonetas, la carga máxima en kilos y si tiene tacógrafo. Un cliente puede tener varios vehículos y un vehículo tiene un único propietario. Cuando entra un vehículo abrimos una orden de reparación con la fecha de entrada, el kilometraje y una descripción del problema. En una orden se hacen varias intervenciones; cada intervención la realiza un mecánico concreto sobre la orden, aplicando un tipo de trabajo del catálogo (cambio de aceite, alineación, diagnosis...), y anotamos las horas dedicadas. El mismo mecánico puede hacer varios tipos de trabajo en la misma orden, y el mismo tipo de trabajo lo pueden hacer mecánicos distintos en la misma orden en días distintos. También registramos las piezas que se usan en cada intervención, con la cantidad y el precio unitario aplicado ese día.»

Pista. «Intervención» relaciona tres entidades a la vez. Y una jerarquía de generalización se puede transformar de tres formas distintas: elige y justifica.

Paso 1 — Entidades y relaciones

La jerarquía: vehiculos es la superentidad, con coches, motos y furgonetas como subentidades. La generalización es total (todo vehículo es de uno de los tres tipos) y disjunta (ninguno es dos cosas a la vez).

La relación ternaria: intervenciones relaciona ordenes × mecanicos × tipos_trabajo. Como el enunciado dice explícitamente que el mismo mecánico puede repetir tipo de trabajo en la misma orden en días distintos, la ternaria no se puede identificar por la terna: necesita clave sustituta o incluir la fecha.

piezas N:M intervenciones con atributos (cantidad, precio_unitario).

Paso 2 — Diagrama ER

Paso 3 — CREATE TABLE

Paso 4 — La restricción que faltaba

Doce tablas. Comprobación de que la jerarquía está bien cerrada:

Las tres formas de transformar una jerarquía, y por qué he elegido esta. La lección 04-03 daba tres opciones:

La opción tercera es la que rompe el diseño en cuanto aparece ordenes: hay que poder referenciar «un vehículo» sin saber su tipo.

El truco de la clave ajena compuesta (vehiculo_id, tipo) merece atención porque es elegante y poco conocido. El problema que resuelve: con una clave ajena normal coches.vehiculo_id → vehiculos.vehiculo_id, nada impide meter en coches un vehículo cuyo tipo sea 'moto'. La solución tiene tres piezas que solo funcionan juntas:

1. UNIQUE (vehiculo_id, tipo) en vehiculos —redundante con la clave primaria, pero necesario para que la FK compuesta tenga a qué apuntar.
2. Una columna tipo en la subtabla, con CHECK (tipo = 'coche') y DEFAULT.
3. La clave ajena compuesta de las dos columnas.

El resultado es que el motor impide meter una moto en la tabla de coches. Sin el truco, esa regla quedaría en manos de la aplicación. Lo que sigue sin quedar garantizado es la totalidad de la generalización (que todo vehículo tenga fila en alguna subtabla): eso requiere restricciones diferidas o un disparador.

La ternaria: por qué no basta con la terna como clave. La tentación es PRIMARY KEY (orden_id, mecanico_id, tipo_trabajo_id). El enunciado la desmonta: «el mismo tipo de trabajo lo pueden hacer mecánicos distintos en la misma orden en días distintos». Y de hecho el mismo mecánico puede repetir el mismo trabajo dos días seguidos. Por eso la solución usa clave sustituta y añade UNIQUE (orden_id, mecanico_id, tipo_trabajo_id, fecha), que sí es la clave alternativa real. Si mañana admiten dos intervenciones iguales el mismo día (mañana y tarde), habría que sustituir fecha por momento TIMESTAMPTZ o eliminar el UNIQUE.

La clave sustituta tiene además una ventaja decisiva: intervencion_piezas necesita apuntar a la intervención, y con clave compuesta de cuatro columnas la tabla de piezas tendría seis columnas de clave.

precio_unitario duplica piezas.precio_actual, y está bien. Es la desnormalización más justificada que existe: el precio de una pieza cambia con el tiempo y la orden ya facturada debe conservar el precio que se aplicó. piezas.precio_actual es el precio de hoy, intervencion_piezas.precio_unitario es el precio de aquel día. No son el mismo dato, aunque coincidan en el momento de insertar. El ejercicio 5 lleva esta idea hasta el final.

Alternativa razonable: modelar intervenciones sin tipos_trabajo, poniendo el nombre del trabajo como texto libre. Sería más simple y sería un error: el enunciado habla de un «catálogo», y el precio por hora vive en él.

Dificultad: Avanzado

Enunciado. Una empresa municipal de aparcamientos de Vallmar:

«Tenemos cuatro aparcamientos y cada uno aplica tarifas que cambian con el tiempo. Una tarifa dice: para este aparcamiento y este tipo de usuario (residente, general, comercial), el precio de la primera hora, el de cada hora adicional y el máximo diario. Las tarifas se aprueban en pleno y entran en vigor una fecha concreta; la anterior deja de aplicarse ese mismo día. Necesitamos conservar todas las tarifas históricas, porque hay reclamaciones de hace tres años y tenemos que poder decir qué precio estaba vigente el 14 de marzo de 2024. A veces se aprueba una tarifa con meses de antelación, así que puede haber tarifas futuras cargadas. Nunca puede haber dos tarifas vigentes a la vez para el mismo aparcamiento y tipo de usuario. También registramos las estancias: entrada, salida, matrícula y el importe cobrado.»

Pista. Piensa en la clave como «qué + desde cuándo», y busca la restricción de PostgreSQL que impide que dos intervalos se solapen.

Paso 1 — Entidades y relaciones

• aparcamientos: entidad fuerte, estable.
• tipos_usuario: catálogo pequeño.
• tarifas: la entidad temporal. Su identidad no es «el aparcamiento y el tipo», sino «el aparcamiento, el tipo y el periodo de vigencia».
• estancias: los hechos. Cada estancia se cobra con la tarifa vigente en su momento.

La relación clave: aparcamientos × tipos_usuario 1:N tarifas, donde el discriminante que completa la identidad es el intervalo de vigencia.

Paso 2 — Diagrama ER

Paso 3 — CREATE TABLE

Paso 4 — La restricción de no solapamiento

Carga de ejemplo con dos tramos históricos y uno futuro:

Consulta de reclamación: «¿qué precio se aplicaba el 14 de marzo de 2024?»

Y la comprobación de que el motor bloquea un solapamiento:

La decisión central: no hay UPDATE de precios, hay filas nuevas. El instinto de un principiante es UPDATE tarifas SET precio_primera_hora = 2.20 WHERE .... Ese UPDATE destruye la información que el enunciado pide conservar: en cuanto se ejecuta, ya no hay forma de responder a la reclamación de 2024. Cuando un requisito dice «histórico», la operación de negocio «cambiar el precio» se traduce en un INSERT, no en un UPDATE.

Intervalo cerrado-abierto [desde, hasta). El enunciado dice que la nueva tarifa entra en vigor «una fecha concreta» y la anterior deja de aplicarse «ese mismo día». Eso es exactamente un intervalo cerrado por la izquierda y abierto por la derecha: la tarifa antigua vale hasta el 30 de junio incluido y la nueva desde el 1 de julio, y ambas se escriben con 2024-07-01 como frontera. La alternativa —vigente_hasta = '2024-06-30' con intervalo cerrado por ambos lados— también funciona, pero obliga a hacer aritmética de fechas cada vez que se encadena un tramo y falla en cuanto la granularidad pasa de días a horas. Cerrado-abierto es la convención que hay que adoptar por defecto en datos temporales.

vigente_hasta IS NULL significa «todavía vigente». Es un uso legítimo del nulo y encaja con daterange(desde, hasta), que interpreta el NULL superior como infinito. La alternativa es poner '9999-12-31'; simplifica las consultas (BETWEEN funciona sin OR ... IS NULL) a cambio de meter una fecha mágica que algún día alguien mostrará en pantalla.

Tres formas de garantizar el no solapamiento:

La primera es superior y por eso es la de la solución; la segunda es un buen segundo premio y merece la pena conocerla porque cubre el 90 % de los casos con sintaxis estándar.

estancias.tarifa_id guarda la tarifa aplicada, y es imprescindible. Se podría deducir a partir de entrada y la tabla de tarifas, pero fijarla en la estancia tiene dos ventajas: la factura queda inmutable aunque alguien corrija a posteriori una fecha de vigencia mal cargada, y la consulta de facturación no necesita el JOIN por rango, que es mucho más caro que un JOIN por igualdad. Es la misma lógica que intervencion_piezas.precio_unitario en el ejercicio 4.

Alternativa razonable que no he tomado: dos tablas, tarifas_vigentes y tarifas_historico. Es un patrón muy extendido y tiene una ventaja clara (la tabla caliente se mantiene diminuta) y dos inconvenientes serios: las consultas que cruzan la frontera necesitan un UNION, y el paso de una tabla a otra es una operación que hay que escribir bien y que puede fallar. Con una sola tabla y un índice adecuado, PostgreSQL aguanta millones de tramos sin despeinarse.

1. Confundir la obra con el objeto físico. Película/copia, material/ejemplar, modelo/unidad. Si dos cosas pueden estar en sitios distintos y en estados distintos, son dos entidades.

2. Atributos multivaluados escondidos. «A veces son dos géneros», «los teléfonos», «los idiomas de los subtítulos». Cada vez que el cliente diga «a veces son varios», ahí hay una tabla.

3. Claves ajenas polimórficas. Una columna objeto_tipo + objeto_id que apunta a tablas distintas según el tipo. El motor no puede declarar esa clave ajena, así que la integridad queda sin proteger. Usa columnas nulables excluyentes con CHECK, o una jerarquía.

4. Poner todas las claves ajenas con ON DELETE CASCADE «por si acaso». CASCADE está bien para lo que no existe sin su padre (líneas de un pedido, historial de una petición). Para el histórico contable —préstamos, alquileres, facturas— la acción correcta casi siempre es RESTRICT, y la baja se hace con una columna activo.

5. Olvidar que un CHECK solo ve su propia fila. «Máximo tres peticiones activas», «al menos una opción correcta», «la suma de las líneas debe cuadrar con el total»: ninguna es expresable con CHECK. Reconócelo en el diseño y decide dónde va (disparador, aplicación o restricción diferida).

6. Clave primaria compuesta en entidades que se van a referenciar mucho. Es técnicamente correcto y prácticamente incómodo: cada tabla hija arrastra todas las columnas. Clave sustituta + UNIQUE sobre la clave natural es casi siempre mejor compromiso.

7. Precios y tarifas sin congelar. Un importe facturado que se lee de la tabla de precios actual cambia solo cuando cambian los precios. Copia el precio aplicado en la línea del documento.

8. Nomenclatura inconsistente. peliculaID, id_genero, SocioId en el mismo esquema. Elige una convención (snake_case, singular o plural, sufijo _id) y no la rompas. Es lo primero que ve quien herede tu esquema.

9. Diseñar para consultas que nadie ha pedido. Cada tabla que añades es una tabla que hay que mantener. Si el enunciado no menciona una necesidad, apúntala como posible extensión, no la construyas.

Consejo de método. Antes de escribir el primer CREATE TABLE, escribe la lista de preguntas que el esquema debe responder y déjala a la vista. Al terminar, comprueba una por una que puedes escribir la consulta. Ese es el criterio de «terminado»; no lo es que el CREATE TABLE se ejecute sin error.

Sin pistas. Cada uno pide los cuatro pasos completos.

Diseña el esquema de un gimnasio: socios con cuota mensual, clases dirigidas con horario semanal fijo (día de la semana y hora), monitores, salas con aforo, y reservas de plaza de los socios para una sesión concreta de una clase en una fecha concreta. Un socio no puede reservar dos veces la misma sesión, y una sesión no puede admitir más reservas que el aforo de su sala. Además, dos clases no pueden ocupar la misma sala a la misma hora.

Amplía BiblioRed para gestionar el origen y el final de vida de los ejemplares: de dónde vino cada ejemplar (compra a un proveedor con factura, donación de un particular o de una entidad, o intercambio con otra biblioteca) y, cuando se retira, por qué motivo, con qué fecha y qué destino tuvo (venta benéfica, reciclaje, cesión). No puedes modificar la tabla ejemplares salvo para añadir columnas, y ninguna consulta existente puede romperse.

Solución A — Gimnasio

Entidades: socios, salas, monitores, clases (la definición: nombre, nivel, duración), sesiones (una ocurrencia concreta: clase + fecha + hora + sala + monitor), reservas (socio × sesión).

La distinción clave es clase frente a sesión: «Pilates de los martes a las 19:00» es la clase; «el Pilates del martes 4 de agosto de 2026 a las 19:00 en la Sala 2 con Aitor» es la sesión. Las reservas se hacen sobre sesiones.

Dos comentarios sobre las decisiones:

• PRIMARY KEY (sesion_id, socio_id) resuelve gratis la regla «un socio no puede reservar dos veces la misma sesión». Es el caso ideal: una regla de negocio que se convierte en la clave primaria.
• El aforo no es un CHECK. «No más reservas que plazas» exige contar filas, y un CHECK no puede. sesiones.plazas copia el aforo de la sala en el momento de programarla (para poder ofrecer menos plazas que el aforo real), y el control se hace en la transacción de reserva con SELECT ... FOR UPDATE sobre la sesión —exactamente el problema de la última plaza que resolverás en 07-04.
• Las dos restricciones EXCLUDE son el mismo patrón del ejercicio 5 aplicado a intervalos de tiempo en lugar de a vigencias.

Solución B — Donaciones y expurgo en BiblioRed

Las decisiones que hay que saber defender:

• UNIQUE (ejemplar_id) en las dos tablas convierte la relación en 1:1 opcional: un ejemplar tiene como mucho un origen registrado y como mucho un expurgo. Sin ese UNIQUE sería 1:N y un ejemplar podría aparecer donado dos veces.
• El CHECK de coherencia por vía es la pieza de diseño más valiosa: hace imposible una compra sin factura o una donación con proveedor. La alternativa —tres tablas hijas adquisiciones_compra, adquisiciones_donacion, adquisiciones_intercambio con el patrón de jerarquía del ejercicio 4— es más limpia conceptualmente y más pesada de consultar. Con tres subtipos de dos o tres columnas cada uno, el CHECK gana.
• expurgos → ejemplares es RESTRICT y adquisiciones → ejemplares es CASCADE. No es una incoherencia: si se borra el ejemplar de la base de datos, su procedencia deja de importar, pero un expurgo es un acto administrativo que debe sobrevivir e impedir el borrado.
• No se modifica ejemplares. El estado retirado ya existía; lo único que aporta el expurgo es la documentación de por qué. Cualquier consulta anterior sigue funcionando palabra por palabra.

Puntúa tu diseño de cada ejercicio con esta lista. Un diseño correcto cumple los ocho primeros puntos; los dos últimos separan un diseño correcto de un buen diseño.

Cómo puntuar. Si fallas el punto 1, vuelve al enunciado: el diseño no sirve. Si fallas el 2, el 3 o el 4, tienes un problema estructural. Los puntos 5 a 8 son los que separan un esquema de estudiante de uno de producción. Los puntos 9 y 10 solo se aplican a los enunciados que los mencionan.

Has diseñado cinco esquemas completos desde cero: un videoclub con dos relaciones N:M —una de ellas con atributo propio—, una ampliación de BiblioRed que tuvo que encajar con un esquema en producción sin tocarlo, una plataforma de cursos con jerarquía de contenidos y autorreferencia, un taller mecánico con jerarquía de generalización y relación ternaria, y un sistema de tarifas donde la clave incluye el tiempo. En los cinco, el trabajo real no estuvo en el CREATE TABLE sino en las dos decisiones que lo preceden: qué es una entidad y qué es un atributo, y qué regla de negocio puede garantizar el motor y cuál no.

Repasa la lista de herramientas que has usado para codificar reglas: CHECK de una columna y de varias, UNIQUE compuesto, índice único parcial (una copia alquilada, una matrícula activa, una opción correcta), clave ajena compuesta con discriminante para cerrar una jerarquía, EXCLUDE USING gist para intervalos que no se solapan, y dominios para conjuntos de valores reutilizables. Y recuerda las tres reglas que ninguna de ellas pudo expresar —el máximo de peticiones activas, el aforo de una sesión y la totalidad de una jerarquía—, porque saber dónde acaba lo declarativo es tan importante como saber usarlo.

En 07-03, Ejercicios de Normalización, el trabajo se invierte. En lugar de partir de requisitos y llegar a tablas, partirás de tablas que ya existen y que están mal: listados planos con datos repetidos, históricos con el nombre del socio copiado en cada fila, tablas donde cambiar un teléfono obliga a tocar catorce registros. Tendrás que escribir sus dependencias funcionales, calcular cierres, encontrar todas las claves candidatas, decir en qué forma normal están y por qué, y descomponerlas paso a paso con el SQL que hace la migración. Nada de requisitos: solo tablas, datos y el análisis formal que revela lo que esconden.