En la lección anterior cerramos el diagrama ER de BiblioRed ampliado: veintiuna entidades, sus relaciones con cardinalidad y participación, una jerarquía total y disjunta de materiales, nueve decisiones registradas y una validación contra las doce consultas del negocio. Es un modelo conceptual completo y sigue sin poderse ejecutar.
Esta lección cubre el puente. La transformación de un modelo ER a un esquema relacional es, a diferencia de casi todo lo demás en diseño, un algoritmo: un conjunto de reglas mecánicas que, aplicadas en orden, producen las tablas. Hay diez reglas. Ocho son deterministas —dado el diagrama, solo hay una respuesta correcta—, y dos (la 6, relaciones 1:1, y la 10, jerarquías) presentan alternativas reales entre las que hay que elegir con criterio. Precisamente por eso son las dos que más espacio ocupan.
El entregable es el script CREATE TABLE completo de la ampliación de BiblioRed, encajando con las siete tablas existentes, con sus claves ajenas y las acciones referenciales razonadas según lo que aprendimos en 02-06. Una advertencia desde ya, porque es el gancho de la lección siguiente: los tipos de datos que usaremos aquí son provisionales. VARCHAR(200), INTEGER, NUMERIC puestos por defecto para que el script funcione. La lección 04-04 los revisa uno a uno y añade el catálogo completo de restricciones.
Contenido
- Qué es el algoritmo de transformación y qué no resuelve
- Regla 1 — Entidad fuerte → tabla
- Regla 2 — Atributo compuesto → columnas simples
- Regla 3 — Atributo multivaluado → tabla aparte
- Regla 4 — Atributo derivado → no se almacena
- Regla 5 — Relación 1:N → clave ajena en el lado N
- Regla 6 — Relación 1:1 → tres opciones
- Regla 7 — Relación N:M → tabla de unión
- Regla 8 — Entidad débil → clave primaria compuesta
- Regla 9 — Relación ternaria → tabla con tres claves ajenas
- Regla 10 — Jerarquía de generalización → tres estrategias
- Entregable: el script completo de BiblioRed ampliado
- Revisión posterior: validar el esquema resultante
- Errores Comunes y Consejos
- Ejercicios
- Conclusión
- Qué es el algoritmo de transformación y qué no resuelve
El algoritmo se aplica en un orden concreto, y el orden importa porque cada paso depende de que el anterior haya creado las tablas a las que apuntar:
flowchart TD
A["1. Entidades fuertes → tablas con su PK"]
B["2-4. Atributos: compuestos, multivaluados, derivados"]
C["8. Entidades débiles → PK compuesta con la del propietario"]
D["5. Relaciones 1:N → FK en el lado N"]
E["6. Relaciones 1:1 → decidir entre tres opciones"]
F["7. Relaciones N:M → tabla de unión"]
G["9. Relaciones ternarias → tabla con tres FK"]
H["10. Jerarquías → elegir estrategia"]
I["Revisión: consultas, tablas sin clave, huérfanos"]
A --> B --> C --> D --> E --> F --> G --> H --> I
Lo que el algoritmo garantiza: un esquema relacional correcto, sin pérdida de información y sin relaciones inventadas. Si el diagrama era fiel al dominio, el esquema también lo será.
Lo que el algoritmo NO resuelve —y conviene tenerlo claro para no esperar magia—:
| No resuelve | Dónde se resuelve |
|---|---|
| Un diagrama mal hecho. Si la cardinalidad estaba mal, la FK acabará en el lado equivocado | Revisando el diagrama (04-02) |
| La elección de tipos de datos concretos | Lección 04-04 |
| Las reglas de negocio que no son estructurales (RN1–RN10) | Lección 04-04 |
| Las anomalías de redundancia que quedasen en el diseño | Módulo 5, normalización |
| El rendimiento | Lección 06-03, índices |
Un matiz importante: el resultado de aplicar el algoritmo a un diagrama bien hecho suele estar ya en tercera forma normal. No por casualidad: pensar en entidades y relaciones es, informalmente, aplicar los mismos principios que la normalización formaliza. En el módulo 5 lo verificaremos con instrumental.
- Regla 1 — Entidad fuerte → tabla
Regla 1. Cada entidad fuerte se convierte en una tabla. Sus atributos simples se convierten en columnas. Su identificador se convierte en la clave primaria.
Es la regla más directa y la que crea el esqueleto. Aplicada a SALAS, TIPOS_EVENTO, EVENTOS y PONENTES (las claves ajenas todavía no; llegan en la regla 5):
CREATE TABLE tipos_evento (
tipo_evento_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
codigo VARCHAR(30) NOT NULL,
nombre VARCHAR(80) NOT NULL,
descripcion VARCHAR(500),
duracion_estandar_min INTEGER,
CONSTRAINT pk_tipos_evento PRIMARY KEY (tipo_evento_id),
CONSTRAINT uq_tipos_evento_codigo UNIQUE (codigo)
);Tres decisiones ya tomadas y aplicadas aquí:
- Clave subrogada
tipo_evento_id, según la regla que fijamos en el apartado 10 de 04-01: PK subrogada en toda entidad fuerte. UNIQUEsobre la clave naturalcodigo. Esto es lo que impide que existan dos filas'taller'. Sin esteUNIQUE, la clave subrogada habría destruido una garantía que el modelo conceptual sí daba.- Restricciones nombradas (
pk_,uq_), por lo que veremos en 04-04 sobre los mensajes de error.
Y ponentes, con la misma estructura:
CREATE TABLE ponentes (
ponente_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
nombre VARCHAR(60) NOT NULL,
apellidos VARCHAR(80) NOT NULL,
email VARCHAR(120),
biografia VARCHAR(1000),
externo BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_ponentes PRIMARY KEY (ponente_id),
CONSTRAINT uq_ponentes_email UNIQUE (email)
);Observa que email no es NOT NULL pero sí UNIQUE. Es una combinación deliberada: hay ponentes externos de los que solo se tiene el teléfono, pero dos ponentes no pueden compartir dirección. Que PostgreSQL permita varios NULL en una columna UNIQUE es lo que hace viable esta combinación, y lo explicaremos a fondo en 04-04.
- Regla 2 — Atributo compuesto → columnas simples
Regla 2. Un atributo compuesto se descompone: cada componente es una columna. El atributo compuesto en sí desaparece; no queda rastro de él en el esquema.
R13 pedía descomponer la dirección de las sucursales. La tabla existente tiene:
La transformación:
ALTER TABLE sucursales ADD COLUMN dir_calle VARCHAR(120);
ALTER TABLE sucursales ADD COLUMN dir_numero VARCHAR(10);
ALTER TABLE sucursales ADD COLUMN dir_codigo_postal VARCHAR(5);
ALTER TABLE sucursales ADD COLUMN dir_ciudad VARCHAR(60);Y la migración de los datos existentes, que es la parte que nadie cuenta y siempre cuesta:
-- Los cuatro valores actuales, migrados a mano: son cuatro filas.
UPDATE sucursales SET dir_calle = 'Plaça Major', dir_numero = '3',
dir_codigo_postal = '08820', dir_ciudad = 'Vallmar'
WHERE sucursal_id = 1;
UPDATE sucursales SET dir_calle = 'Avinguda del Nord', dir_numero = '112',
dir_codigo_postal = '08821', dir_ciudad = 'Vallmar'
WHERE sucursal_id = 2;
-- ... sucursales 3 y 4
ALTER TABLE sucursales DROP COLUMN direccion;Consejo práctico: cuando el atributo compuesto ya tiene datos en producción como texto libre, la conversión automática con expresiones regulares falla más de lo que acierta. Con cuatro filas se hace a mano. Con cuarenta mil, se hace por lotes, se revisa lo que no encaja en el patrón y se conserva la columna original renombrada a
direccion_originaldurante un par de meses.
Cuándo NO descomponer
La regla tiene una excepción importante y con frecuencia se aplica mal por exceso de celo. No descompongas si nadie va a buscar, filtrar, ordenar o agregar por las partes.
| Caso | ¿Descomponer? | Motivo |
|---|---|---|
| Dirección de sucursal (R13) | Sí | Se busca por código postal, se muestra la ciudad aparte |
| Dirección postal de un ponente externo | No | Solo se imprime en una carta; un VARCHAR basta |
| Nombre y apellidos de un socio | Sí (ya lo está) | Se ordenan listados por apellidos |
| Observaciones de un informe de evento | No | Texto libre por naturaleza |
Duración hh:mm de un DVD |
No: es un solo número de minutos | Descomponer en horas y minutos complica todos los cálculos |
El coste de descomponer de más es real: cuatro columnas que hay que rellenar, validar y mantener, para un dato que solo se imprime entero. El coste de descomponer de menos es peor —hay que trocear cadenas en cada consulta—, y por eso ante la duda se descompone; pero la duda debe existir.
- Regla 3 — Atributo multivaluado → tabla aparte
Regla 3. Un atributo multivaluado se convierte en una tabla nueva con dos partes: una clave ajena a la entidad propietaria y el propio valor. La clave primaria es la combinación de ambas.
Es la regla que hace desaparecer para siempre los anti-patrones telefono1, telefono2, telefono3 y subtitulos = 'es,ca,en' que denunciamos en 04-01.
Los teléfonos de un socio (R12)
CREATE TABLE telefonos_socio (
socio_id INTEGER NOT NULL,
numero VARCHAR(20) NOT NULL,
tipo VARCHAR(10) NOT NULL,
CONSTRAINT pk_telefonos_socio PRIMARY KEY (socio_id, numero),
CONSTRAINT fk_telefonos_socio_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Análisis de las tres decisiones:
- La PK es
(socio_id, numero). El valor forma parte de la clave, y eso es lo que impide guardar el mismo teléfono dos veces para el mismo socio. Es integridad gratuita: la regla "no repitas un teléfono" no necesita código. ON DELETE CASCADE: un teléfono no tiene ninguna existencia fuera de su socio. Si el socio se borra, sus teléfonos deben irse con él. Es el caso de libro paraCASCADEsegún los criterios de 02-06.- El límite de tres teléfonos de R12 no está aquí. Ninguna clave ni restricción de tabla lo expresa. Es una regla de negocio pendiente, y en 04-04 decidiremos dónde vive.
Probémoslo:
INSERT INTO telefonos_socio (socio_id, numero, tipo) VALUES
(14, '600111222', 'movil'),
(14, '938880011', 'fijo'),
(15, '600333444', 'movil');
INSERT INTO telefonos_socio (socio_id, numero, tipo) VALUES (14, '600111222', 'trabajo');INSERT 0 3 ERROR: duplicate key value violates unique constraint "pk_telefonos_socio" DETALLE: Key (socio_id, numero)=(14, 600111222) already exists.
Exactamente el error que queríamos: el mismo número no se puede registrar dos veces para el mismo socio, ni siquiera cambiándole el tipo.
Los subtítulos de un DVD (R1)
CREATE TABLE subtitulos_dvd (
material_id INTEGER NOT NULL,
idioma VARCHAR(5) NOT NULL,
CONSTRAINT pk_subtitulos_dvd PRIMARY KEY (material_id, idioma),
CONSTRAINT fk_subtitulos_dvd_material
FOREIGN KEY (material_id) REFERENCES materiales_dvd (material_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Fíjate en el detalle: la clave ajena apunta a materiales_dvd, no a materiales. Es la subentidad la que tiene subtítulos, no cualquier material. Un audiolibro no puede tenerlos, y el esquema lo hace imposible. Esa precisión es una de las ventajas de la estrategia de jerarquía que elegiremos en la regla 10.
Y ahora la consulta C9 ("DVD con subtítulos en catalán") se responde con un JOIN normal en lugar de con un LIKE '%ca%' que devolvía basura:
SELECT m.material_id, m.titulo
FROM materiales m
JOIN subtitulos_dvd s ON s.material_id = m.material_id
WHERE s.idioma = 'ca'
ORDER BY m.titulo; material_id | titulo
-------------+-------------------------------
1204 | El bosc de les ombres
1187 | La ciutat dels prodigis
(2 filas)
- Regla 4 — Atributo derivado → no se almacena
Regla 4. Un atributo derivado no genera columna. Se calcula en el momento de consultarlo.
De los cinco derivados que anotamos en 04-02, veamos el más usado: las plazas libres de un evento (C2).
SELECT e.evento_id,
e.titulo,
e.plazas_ofertadas,
e.plazas_ofertadas - COALESCE(SUM(1 + i.acompanantes), 0) AS plazas_libres
FROM eventos e
LEFT JOIN inscripciones i
ON i.evento_id = e.evento_id
AND i.estado = 'confirmada'
WHERE e.evento_id = 47
GROUP BY e.evento_id, e.titulo, e.plazas_ofertadas; evento_id | titulo | plazas_ofertadas | plazas_libres
-----------+-------------------------------+------------------+---------------
47 | Club de lectura: novela negra | 20 | 6
(1 fila)Tres piezas que vale la pena señalar, todas ya conocidas de módulos anteriores: el LEFT JOIN conserva los eventos sin ninguna inscripción (02-04), el COALESCE convierte el NULL del SUM vacío en 0 (02-01, lógica trivalente) y 1 + acompanantes implementa la decisión de que un socio con dos acompañantes ocupa tres plazas (ambigüedad resuelta en 04-01).
La forma cómoda: una vista
Repetir esa consulta en veinte sitios es una invitación a que en alguno se escriba mal. Una vista la encapsula, y además es un ejemplo puro del nivel externo de ANSI/SPARC de 01-04:
CREATE VIEW v_eventos_ocupacion AS
SELECT e.evento_id,
e.titulo,
e.inicio,
e.plazas_ofertadas,
COALESCE(SUM(1 + i.acompanantes) FILTER (WHERE i.estado = 'confirmada'), 0) AS plazas_ocupadas,
e.plazas_ofertadas
- COALESCE(SUM(1 + i.acompanantes) FILTER (WHERE i.estado = 'confirmada'), 0) AS plazas_libres
FROM eventos e
LEFT JOIN inscripciones i ON i.evento_id = e.evento_id
GROUP BY e.evento_id, e.titulo, e.inicio, e.plazas_ofertadas;La vista no almacena nada: se recalcula en cada consulta. Sigue cumpliendo la regla 4.
Cuándo sí se almacena
Hay dos situaciones en que un derivado se acaba guardando, y las dos tienen nombre:
- Por rendimiento. Si la agenda pública (C1) muestra las plazas libres de cuarenta eventos y cada una implica agregar miles de inscripciones, puede compensar mantener una columna
plazas_ocupadasactualizada por disparador. Esto es desnormalización deliberada, tiene un coste conocido (el riesgo de que el valor almacenado y el real diverjan) y es el tema de la lección 05-04. No lo hagas sin medir antes. - Porque el valor debe congelarse. Este caso es distinto y se confunde con el anterior. El importe de una multa se calcula hoy a 0,20 €/día, pero si mañana la ordenanza sube la tarifa, las multas de ayer no deben recalcularse. Ese importe no es un derivado: es un hecho histórico que se almacena porque su valor depende de un contexto que ya no existe. La misma lógica que justifica guardar el precio de venta en una factura.
En BiblioRed, multas.importe es del segundo tipo y por eso es una columna. plazas_libres es del primero y por eso no lo es.
- Regla 5 — Relación 1:N → clave ajena en el lado N
Regla 5. En una relación 1:N, la tabla del lado N recibe una clave ajena que apunta a la clave primaria del lado 1. Los atributos propios de la relación, si los hay, van también al lado N.
Es la regla que más veces se aplica en cualquier esquema. En BiblioRed:
CREATE TABLE salas (
sala_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
sucursal_id INTEGER NOT NULL,
nombre VARCHAR(80) NOT NULL,
aforo INTEGER NOT NULL,
planta INTEGER NOT NULL DEFAULT 0,
accesible BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_salas PRIMARY KEY (sala_id),
CONSTRAINT uq_salas_sucursal_nombre UNIQUE (sucursal_id, nombre),
CONSTRAINT fk_salas_sucursal
FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);Cuatro cosas derivadas directamente del diagrama:
sucursal_idva ensalas, el lado N. Nunca al revés.NOT NULLporque la participación deSALASera total: toda sala está en una sucursal (04-02, apartado 6).UNIQUE (sucursal_id, nombre)implementa la afirmación de R3: el nombre solo es único dentro de la sucursal. Esto es lo que queda de queSALASfuera conceptualmente débil; volveremos sobre ello en la regla 8.ON DELETE RESTRICT: no se borra una sucursal que tiene salas. Coherente con la decisión ya tomada parasocios.sucursal_idyejemplares.sucursal_iden 02-06.
Por qué nunca al revés
La pregunta es legítima: ¿por qué no poner en sucursales una columna sala_id? Porque una sucursal tiene varias salas, y una columna solo cabe un valor. Las dos únicas maneras de forzarlo son los dos anti-patrones de 04-01:
| Intento | Qué es | Por qué falla |
|---|---|---|
sucursales.sala1_id, sala2_id, ... sala6_id |
Columnas numeradas | R3 dice "entre 1 y 6" hoy; la séptima llega en cuanto reformen un edificio. NULL masivo. Consultar "¿en qué sucursal está la sala 12?" requiere revisar seis columnas |
sucursales.salas = '4,7,12' |
Lista con comas | Sin clave ajena, sin tipos, sin JOIN, con LIKE que devuelve falsos positivos |
La FK va siempre en el lado que tiene como máximo un valor. Ese lado es el N.
El caso del evento sin sala
eventos.sala_id es distinto porque la decisión D4 de 04-02 hizo la sala opcional:
sala_id INTEGER NULL, -- participación parcial: eventos al aire libre
CONSTRAINT fk_eventos_sala
FOREIGN KEY (sala_id) REFERENCES salas (sala_id)
ON DELETE RESTRICT ON UPDATE CASCADELa participación del diagrama se traduce literalmente en NOT NULL o su ausencia. Es la conversión más directa de todo el algoritmo y por eso conviene haber hecho bien esa pregunta en 04-02.
- Regla 6 — Relación 1:1 → tres opciones
Regla 6. Una relación 1:1 admite tres soluciones: fusionar las dos entidades en una tabla, poner la clave ajena en uno de los dos lados con
UNIQUE, o crear una tabla intermedia. La elección depende de la participación y del patrón de acceso.
El caso de BiblioRed es EVENTOS — INFORMES_EVENTO (R9): un evento tiene como mucho un informe, un informe es de un evento.
Opción A — Fusionar en una tabla
-- Opción A: los campos del informe dentro de eventos
ALTER TABLE eventos ADD COLUMN asistentes_reales INTEGER;
ALTER TABLE eventos ADD COLUMN valoracion_media NUMERIC(3,2);
ALTER TABLE eventos ADD COLUMN observaciones VARCHAR(2000);
ALTER TABLE eventos ADD COLUMN fecha_redaccion DATE;A favor: ningún JOIN, una sola fila que leer, simplicidad máxima.
En contra: de los 1.200 eventos previstos a tres años, quizá 700 tendrán informe; los otros 500 llevarán cuatro columnas a NULL. Y hay un problema peor que el desperdicio: no se puede distinguir "informe no redactado todavía" de "informe redactado con cero asistentes". Ambos casos dan asistentes_reales IS NULL o = 0 según cómo se rellene, y la consulta C12 ("eventos sin informe") se vuelve ambigua.
Opción B — Clave ajena con UNIQUE en uno de los lados
-- Opción B, elegida para BiblioRed
CREATE TABLE informes_evento (
evento_id INTEGER NOT NULL,
asistentes_reales INTEGER NOT NULL,
valoracion_media NUMERIC(3,2),
observaciones VARCHAR(2000),
fecha_redaccion DATE NOT NULL,
CONSTRAINT pk_informes_evento PRIMARY KEY (evento_id),
CONSTRAINT fk_informes_evento_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Aquí la clave ajena es la clave primaria. Eso es lo que hace la relación 1:1: si evento_id es PK de informes_evento, no puede repetirse, y por tanto un evento no puede tener dos informes. No hace falta un UNIQUE adicional: la clave primaria ya lo es.
A favor: cero NULL, la existencia de la fila es la respuesta a "¿tiene informe?", y C12 se resuelve con un anti-join limpio:
SELECT e.evento_id, e.titulo, e.inicio
FROM eventos e
LEFT JOIN informes_evento i ON i.evento_id = e.evento_id
WHERE e.estado = 'celebrado'
AND e.inicio < CURRENT_DATE - INTERVAL '15 days'
AND i.evento_id IS NULL
ORDER BY e.inicio; evento_id | titulo | inicio
-----------+---------------------------------+------------------------
31 | Taller de escritura creativa | 2026-06-18 18:00:00+02
38 | Presentación: "Los días largos" | 2026-07-02 19:30:00+02
(2 filas)En contra: un JOIN cuando se necesitan los dos juntos. En este caso no importa: el informe se consulta en una pantalla distinta de la agenda.
Opción C — Tabla intermedia
Una tercera tabla con dos claves ajenas, ambas UNIQUE. Solo tiene sentido cuando ambos lados son de participación parcial y la relación en sí es un hecho que puede aparecer y desaparecer (por ejemplo, "qué empleado tiene asignado qué vehículo de flota"). Para el informe sería absurdo: un informe sin evento no existe.
Tabla de decisión
| Criterio | Opción A: fusionar | Opción B: FK + UNIQUE | Opción C: tabla intermedia |
|---|---|---|---|
| Participación de ambos lados | Total en los dos | Total en uno, parcial en el otro | Parcial en los dos |
| Nulos generados | Muchos si un lado es opcional | Ninguno | Ninguno |
JOIN necesarios |
Ninguno | Uno | Dos |
| Distinguir "no existe" de "vale cero" | No | Sí | Sí |
| Columnas grandes o poco consultadas | Penaliza cada lectura | Aísla el peso | Aísla el peso |
| Complejidad | Mínima | Baja | Alta |
Regla práctica: si ambos lados son de participación total y se consultan siempre juntos, fusiona (opción A) —de hecho, si es tu caso, replantéate si de verdad eran dos entidades—. Si un lado es opcional, FK en el lado opcional con la PK compartida (opción B). La opción C es rara y hay que justificarla.
Decisión de BiblioRed: opción B. El lado opcional es el informe, y ahí va la tabla.
- Regla 7 — Relación N:M → tabla de unión
Regla 7. Una relación N:M se convierte en una tabla de unión con dos claves ajenas, una a cada entidad. Los atributos propios de la relación se convierten en columnas de esa tabla. La clave primaria es, por defecto, la combinación de las dos claves ajenas.
Es la regla que más veces se aplica mal, casi siempre olvidando la segunda frase.
El caso principal: inscripciones (R6)
CREATE TABLE inscripciones (
evento_id INTEGER NOT NULL,
socio_id INTEGER NOT NULL,
fecha_inscripcion TIMESTAMPTZ NOT NULL DEFAULT now(),
estado VARCHAR(15) NOT NULL DEFAULT 'confirmada',
acompanantes INTEGER NOT NULL DEFAULT 0,
CONSTRAINT pk_inscripciones PRIMARY KEY (evento_id, socio_id),
CONSTRAINT fk_inscripciones_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_inscripciones_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);Los tres atributos propios —fecha, estado, acompañantes— viven aquí y en ningún otro sitio. Es la aplicación de la regla que enunciamos en 04-02: si un atributo necesita ambas claves para tener valor, pertenece a la relación.
La clave primaria compuesta implementa gratis la regla de R6:
INSERT INTO inscripciones (evento_id, socio_id) VALUES (47, 14);
INSERT INTO inscripciones (evento_id, socio_id, acompanantes) VALUES (47, 14, 2);INSERT 0 1 ERROR: duplicate key value violates unique constraint "pk_inscripciones" DETALLE: Key (evento_id, socio_id)=(47, 14) already exists.
"Un socio no puede inscribirse dos veces al mismo evento", dice R6. Ahí está, sin una línea de código de aplicación.
Las dos acciones referenciales, razonadas
Este es un buen momento para aplicar el criterio de 02-06, porque las dos claves ajenas de la misma tabla llevan acciones distintas y eso desconcierta a mucha gente:
| Clave ajena | Acción | Motivo |
|---|---|---|
evento_id |
ON DELETE CASCADE |
Una inscripción no significa nada sin su evento. Si un evento programado se elimina del sistema, sus inscripciones deben desaparecer. Es el mismo criterio que aplicamos a reservas.libro_id |
socio_id |
ON DELETE RESTRICT |
La inscripción es un hecho histórico con valor (asistencias, estadísticas de C5). Borrar un socio no debe borrar el registro de que asistió a doce eventos. Mismo criterio que prestamos.socio_id |
Compáralo con reservas.socio_id, que sí es CASCADE: una reserva es efímera y no tiene valor histórico; una inscripción, sí. La acción referencial se deduce del valor del dato, no de la forma de la relación.
¿Clave compuesta o clave subrogada?
Es la decisión que hay que tomar en cada tabla de unión:
PK compuesta (evento_id, socio_id) |
PK subrogada inscripcion_id + UNIQUE (evento_id, socio_id) |
|
|---|---|---|
| Impide duplicados | Sí, directamente | Sí, con el UNIQUE (si te acuerdas de ponerlo) |
| Referenciable desde otra tabla | Con FK compuesta, más pesada | Con una sola columna, más cómoda |
| Semántica | La clave es la regla de negocio | La clave no significa nada |
| Tamaño | 8 bytes | 4 bytes + el índice del UNIQUE |
| Compatible con ORM | Algunos ORM lo llevan mal | Universal |
Decisión de BiblioRed: PK compuesta en inscripciones, participaciones y eventos_materiales. Ninguna otra tabla necesita referenciarlas, y la clave compuesta expresa la regla de negocio directamente.
Los otros dos N:M
eventos_materiales (R8) es el caso más simple del esquema, N:M con un solo atributo propio:
CREATE TABLE eventos_materiales (
evento_id INTEGER NOT NULL,
material_id INTEGER NOT NULL,
papel VARCHAR(15) NOT NULL DEFAULT 'recomendado',
CONSTRAINT pk_eventos_materiales PRIMARY KEY (evento_id, material_id),
CONSTRAINT fk_eventos_materiales_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_eventos_materiales_material
FOREIGN KEY (material_id) REFERENCES materiales (material_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Y participaciones (R7) es en realidad la regla 9; llega en dos apartados.
- Regla 8 — Entidad débil → clave primaria compuesta
Regla 8. Una entidad débil se convierte en una tabla cuya clave primaria es la combinación de la clave primaria de la entidad propietaria y su identificador parcial. La clave ajena a la propietaria forma parte de la clave primaria y es, obligatoriamente,
NOT NULL.
Ya la hemos aplicado tres veces sin decirlo: telefonos_socio (regla 3), informes_evento (regla 6) e inscripciones (regla 7). El caso puro es el de los ejemplares respecto al material.
La versión ortodoxa
-- Aplicación literal de la regla 8
CREATE TABLE ejemplares (
material_id INTEGER NOT NULL,
num_ejemplar INTEGER NOT NULL, -- identificador parcial
codigo VARCHAR(15) NOT NULL,
sucursal_id INTEGER NOT NULL,
estado VARCHAR(20) NOT NULL,
fecha_adquisicion DATE,
CONSTRAINT pk_ejemplares PRIMARY KEY (material_id, num_ejemplar),
...
);Es correcta y expresa exactamente la semántica: "el ejemplar 3 de El mapa del tiempo".
Por qué BiblioRed no la usa
Hay una razón concreta y decisiva: prestamos referencia a ejemplares. Con la PK compuesta, prestamos necesitaría dos columnas y una clave ajena compuesta:
-- Lo que implicaría la versión ortodoxa
CREATE TABLE prestamos (
prestamo_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
socio_id INTEGER NOT NULL,
material_id INTEGER NOT NULL, -- dos columnas
num_ejemplar INTEGER NOT NULL, -- para identificar un ejemplar
...
CONSTRAINT fk_prestamos_ejemplar
FOREIGN KEY (material_id, num_ejemplar)
REFERENCES ejemplares (material_id, num_ejemplar)
);Y además prestamos ya existe con 4.312 filas y una FK a ejemplar_id. Cambiarlo sería una migración considerable para no ganar nada.
Decisión de BiblioRed: clave subrogada + UNIQUE sobre la clave débil. Es el patrón habitual y conserva toda la integridad:
CREATE TABLE ejemplares (
ejemplar_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
codigo VARCHAR(15) NOT NULL,
material_id INTEGER NOT NULL,
num_ejemplar INTEGER NOT NULL,
sucursal_id INTEGER NOT NULL,
estado VARCHAR(20) NOT NULL DEFAULT 'disponible',
fecha_adquisicion DATE,
CONSTRAINT pk_ejemplares PRIMARY KEY (ejemplar_id),
CONSTRAINT uq_ejemplares_codigo UNIQUE (codigo),
CONSTRAINT uq_ejemplares_material_num UNIQUE (material_id, num_ejemplar),
CONSTRAINT fk_ejemplares_material
FOREIGN KEY (material_id) REFERENCES materiales (material_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_ejemplares_sucursal
FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);La combinación es: ejemplar_id como clave primaria (cómoda para referenciar), uq_ejemplares_material_num como clave candidata natural que preserva la semántica de entidad débil, y uq_ejemplares_codigo para el código impreso en la etiqueta. Nada se ha perdido.
El principio general: en la fase lógica, una entidad débil puede llevar clave subrogada sin dejar de ser débil, siempre que su clave natural compuesta se declare
UNIQUE. Lo que no es negociable es la unicidad; la forma de la clave primaria sí.
El caso de salas es idéntico: PK sala_id, UNIQUE (sucursal_id, nombre). Y el de pagos también: PK pago_id, con la particularidad de que ahí ni siquiera hay identificador parcial natural (dos pagos de la misma multa por el mismo importe el mismo día son dos pagos distintos), lo que es un argumento adicional para la subrogada.
- Regla 9 — Relación ternaria → tabla con tres claves ajenas
Regla 9. Una relación de grado 3 se convierte en una tabla con tres claves ajenas, una a cada entidad participante, más los atributos propios de la relación. La clave primaria es, por defecto, la combinación de las tres.
Y una advertencia que es tan importante como la regla: antes de aplicarla, comprueba que la relación es ternaria de verdad.
La prueba de descomposición
Una relación ternaria R(A, B, C) es genuina si la existencia de una tripla no se puede deducir de tres relaciones binarias. La prueba consiste en preguntarse si R(A,B,C) equivale a R1(A,B) ∧ R2(B,C) ∧ R3(A,C).
Un contraejemplo clásico y erróneo: "un socio pide prestado un ejemplar en una sucursal" parece ternario, pero no lo es: la sucursal se deduce del ejemplar. Es una binaria con un derivado. Modelarla como ternaria introduce redundancia y la posibilidad de que la sucursal registrada en el préstamo contradiga la del ejemplar.
Un caso genuinamente ternario: "un proveedor suministra un componente para un proyecto concreto". Que el proveedor A suministre el componente X, que X se use en el proyecto P y que A trabaje con P no implica que A suministre X para P. La información se perdería al descomponer.
El caso de BiblioRed: participaciones (R7)
En 04-02 (decisión D5) analizamos EVENTO — PONENTE — ROL. La conclusión fue que ROL no es una entidad: es un conjunto pequeño, estable y sin atributos propios. Lo que queda es una relación binaria N:M cuyo identificador incluye un atributo:
CREATE TABLE participaciones (
evento_id INTEGER NOT NULL,
ponente_id INTEGER NOT NULL,
rol VARCHAR(25) NOT NULL,
honorarios NUMERIC(8,2) NOT NULL DEFAULT 0,
CONSTRAINT pk_participaciones PRIMARY KEY (evento_id, ponente_id, rol),
CONSTRAINT fk_participaciones_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_participaciones_ponente
FOREIGN KEY (ponente_id) REFERENCES ponentes (ponente_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);La clave primaria de tres columnas es lo que permite que Elena Roig sea a la vez moderadora y tallerista del mismo evento —R7 lo exigía— y a la vez impide que aparezca dos veces como moderadora del mismo evento.
INSERT INTO participaciones (evento_id, ponente_id, rol, honorarios) VALUES
(47, 8, 'moderador', 0),
(47, 8, 'tallerista', 180.00),
(47, 9, 'autor_invitado', 250.00);INSERT INTO participaciones (evento_id, ponente_id, rol, honorarios)
VALUES (47, 8, 'moderador', 50.00);ERROR: duplicate key value violates unique constraint "pk_participaciones" DETALLE: Key (evento_id, ponente_id, rol)=(47, 8, moderador) already exists.
ponente_id es RESTRICT y no CASCADE por un motivo muy concreto: los honorarios son un registro con implicaciones económicas. Borrar un ponente no puede borrar el rastro de lo que se le pagó.
Consejo: cuando en un diseño aparezca una relación de grado 3, dedica cinco minutos a la prueba de descomposición. En la experiencia práctica, cuatro de cada cinco resultan ser una binaria con un atributo, una binaria con un derivado, o dos binarias independientes.
- Regla 10 — Jerarquía de generalización → tres estrategias
Regla 10. Una jerarquía de generalización se lleva a tablas con una de tres estrategias: tabla única, tabla por subclase o tabla por clase concreta. La elección depende de la disyunción, la totalidad, el número de atributos específicos y el patrón de consulta.
Es la decisión más importante de toda la ampliación de BiblioRed, porque afecta al catálogo entero y a las tablas que ya existen.
Estrategia 1 — Tabla única (single table inheritance)
Una sola tabla con todos los atributos de todas las subclases, más una columna discriminante.
CREATE TABLE materiales (
material_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tipo_material VARCHAR(15) NOT NULL,
titulo VARCHAR(200) NOT NULL,
-- ... atributos comunes ...
isbn VARCHAR(13), -- solo libros
num_paginas INTEGER, -- solo libros
encuadernacion VARCHAR(20), -- solo libros
duracion_min INTEGER, -- DVD y audiolibros
formato_video VARCHAR(15), -- solo DVD
codigo_region INTEGER, -- solo DVD
issn VARCHAR(9), -- solo revistas
numero VARCHAR(20), -- solo revistas
periodicidad VARCHAR(20), -- solo revistas
narrador VARCHAR(120), -- solo audiolibros
formato_audio VARCHAR(15) -- solo audiolibros
);Rápida de consultar (ningún JOIN) y desastrosa de validar: es imposible declarar isbn NOT NULL aunque R1 lo exija para los libros, porque los DVD lo tendrían a NULL. La única salida son CHECK condicionales del estilo CHECK (tipo_material <> 'libro' OR isbn IS NOT NULL), uno por atributo obligatorio, que se multiplican rápidamente.
Estrategia 2 — Tabla por subclase (class table inheritance)
Una tabla para la superclase con lo común, y una tabla por subclase con lo específico, unidas por la clave primaria compartida.
CREATE TABLE materiales (
material_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tipo_material VARCHAR(15) NOT NULL,
titulo VARCHAR(200) NOT NULL,
autor_id INTEGER,
editorial VARCHAR(120),
anio_publicacion INTEGER,
idioma VARCHAR(5) NOT NULL,
fecha_alta DATE NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE materiales_libro (
material_id INTEGER NOT NULL PRIMARY KEY REFERENCES materiales (material_id) ON DELETE CASCADE,
isbn VARCHAR(13) NOT NULL UNIQUE, -- ahora sí se puede exigir
num_paginas INTEGER,
encuadernacion VARCHAR(20)
);Cero nulos innecesarios, restricciones específicas exigibles, y la clave ajena de ejemplares apunta a materiales, que es lo que necesitaban C10, prestamos y reservas. Su coste es un JOIN cuando se necesitan los detalles del subtipo.
Estrategia 3 — Tabla por clase concreta (concrete table inheritance)
Una tabla completa e independiente por cada subclase, sin tabla de superclase: libros, dvds, revistas, audiolibros, cada una con todos los atributos, comunes y específicos.
Es la peor opción para BiblioRed y conviene entender exactamente por qué: ejemplares no tendría a qué apuntar. Necesitaría cuatro columnas (libro_id, dvd_id, revista_id, audiolibro_id) con tres a NULL en cada fila, o una pareja (tipo, id) sin clave ajena posible —un anti-patrón conocido como referencia polimórfica, que renuncia a la integridad referencial—. Y la consulta C10 exigiría un UNION de cuatro ramas. Solo es viable cuando las subclases no se referencian desde fuera, y aquí se referencian desde tres sitios.
Tabla comparativa
| Criterio | Tabla única | Tabla por subclase | Tabla por clase concreta |
|---|---|---|---|
| Nº de tablas | 1 | 1 + n | n |
| Nulos | Muchos | Ninguno | Ninguno |
NOT NULL en atributos de subclase |
Imposible (solo CHECK condicional) |
Directo | Directo |
| Consultar todos los materiales | Trivial | Trivial (la superclase) | UNION de n ramas |
| Consultar un subtipo con sus detalles | Trivial | Un JOIN |
Trivial |
Referenciar desde fuera (ejemplares, reservas) |
Trivial | Trivial (a la superclase) | Imposible sin renunciar a la FK |
| Añadir un subtipo nuevo | ALTER TABLE sobre la tabla grande |
Una tabla nueva, nada existente cambia | Una tabla nueva + revisar todos los UNION |
| Coherencia discriminante ↔ subtipo | Automática | Hay que garantizarla | No aplica |
| Requiere jerarquía disjunta | No | No | Sí |
| Requiere jerarquía total | No | No | Sí |
| Cuándo elegirla | Pocos atributos específicos (2-3), subtipos que casi no difieren, prioridad absoluta a la lectura | Muchos atributos específicos, subtipos referenciados desde fuera, integridad importante | Subtipos casi sin nada en común y que nadie referencia |
La decisión de BiblioRed: tabla por subclase
Cuatro razones, en orden de peso:
ejemplares,prestamosyreservasnecesitan una entidad común a la que apuntar. Descarta la estrategia 3 por sí sola.- Cada subtipo tiene 3-4 atributos específicos obligatorios. Con tabla única serían once columnas mayoritariamente nulas y once
CHECKcondicionales. - R1 exige
isbn NOT NULL UNIQUEpara libros eissnpara revistas. Solo la estrategia 2 lo permite de forma directa. - Añadir "mapas" o "partituras" en el futuro es crear una tabla, sin tocar nada existente. Es exactamente el objetivo 5 de 04-01, evolución sin migraciones traumáticas.
El precio: mantener la coherencia del discriminante
La estrategia 2 tiene un agujero conocido y hay que reconocerlo: nada impide, por sí solo, que un material con tipo_material = 'libro' tenga una fila en materiales_dvd, o que no tenga ninguna fila en materiales_libro (violando la totalidad de la jerarquía).
Se cierra con una técnica elegante: incluir el discriminante en la clave ajena. Se declara UNIQUE (material_id, tipo_material) en materiales y la subtabla referencia esa pareja con su propio tipo_material fijado por un CHECK. Es una aplicación directa de las claves ajenas compuestas de 02-06:
ALTER TABLE materiales
ADD CONSTRAINT uq_materiales_id_tipo UNIQUE (material_id, tipo_material);
CREATE TABLE materiales_libro (
material_id INTEGER NOT NULL,
tipo_material VARCHAR(15) NOT NULL DEFAULT 'libro',
isbn VARCHAR(13) NOT NULL,
num_paginas INTEGER,
encuadernacion VARCHAR(20),
CONSTRAINT pk_materiales_libro PRIMARY KEY (material_id),
CONSTRAINT uq_materiales_libro_isbn UNIQUE (isbn),
CONSTRAINT chk_materiales_libro_tipo CHECK (tipo_material = 'libro'),
CONSTRAINT fk_materiales_libro_material
FOREIGN KEY (material_id, tipo_material)
REFERENCES materiales (material_id, tipo_material)
ON DELETE CASCADE ON UPDATE CASCADE
);Ahora es físicamente imposible que un DVD tenga fila en materiales_libro:
-- material 1204 es un DVD
INSERT INTO materiales_libro (material_id, isbn) VALUES (1204, '9788401339097');ERROR: insert or update on table "materiales_libro" violates foreign key constraint "fk_materiales_libro_material" DETALLE: Key (material_id, tipo_material)=(1204, libro) is not present in table "materiales".
La otra mitad de la jerarquía —que todo material tenga fila en alguna subtabla, la totalidad— no se puede garantizar con restricciones declarativas. Es la misma limitación que vimos en 04-02 con "toda sucursal tiene al menos una sala". Se resuelve con disparador, con una transacción que inserte ambas filas juntas, o asumiendo el riesgo y comprobándolo periódicamente. En 04-04 cerramos el criterio.
Compatibilidad: libros se convierte en una vista
Todo el código y todos los informes existentes consultan libros. Reescribirlos todos es innecesario:
CREATE VIEW libros AS
SELECT m.material_id AS libro_id,
l.isbn,
m.titulo,
m.autor_id,
m.editorial,
m.anio_publicacion,
m.idioma
FROM materiales m
JOIN materiales_libro l ON l.material_id = m.material_id;Las consultas antiguas siguen funcionando sin un solo cambio. Es el ejemplo más claro de independencia lógica de todo el curso: hemos cambiado el nivel conceptual y el nivel externo ha absorbido el cambio, exactamente como describía ANSI/SPARC en 01-04.
- Entregable: el script completo de BiblioRed ampliado
Aquí está el resultado de aplicar las diez reglas. Recuerda que los tipos son provisionales: VARCHAR(n) puestos a ojo, INTEGER para todo lo entero, restricciones limitadas a las estructurales. La lección 04-04 revisa cada tipo y añade el catálogo completo de CHECK, DEFAULT, dominios y columnas generadas.
-- =====================================================================
-- BiblioRed · Migración V004: ampliación eventos, materiales y multas
-- Tipos PROVISIONALES. Revisados en 04-04.
-- =====================================================================
-- ---------------------------------------------------------------------
-- BLOQUE 1 · Regla 2: atributo compuesto (R13)
-- ---------------------------------------------------------------------
ALTER TABLE sucursales ADD COLUMN dir_calle VARCHAR(120);
ALTER TABLE sucursales ADD COLUMN dir_numero VARCHAR(10);
ALTER TABLE sucursales ADD COLUMN dir_codigo_postal VARCHAR(5);
ALTER TABLE sucursales ADD COLUMN dir_ciudad VARCHAR(60);
-- (migración de datos y DROP COLUMN direccion: ver apartado 3)
-- ---------------------------------------------------------------------
-- BLOQUE 2 · Regla 10: jerarquía de materiales (R1)
-- ---------------------------------------------------------------------
CREATE TABLE materiales (
material_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
tipo_material VARCHAR(15) NOT NULL,
titulo VARCHAR(200) NOT NULL,
autor_id INTEGER,
editorial VARCHAR(120),
anio_publicacion INTEGER,
idioma VARCHAR(5) NOT NULL,
fecha_alta DATE NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT pk_materiales PRIMARY KEY (material_id),
CONSTRAINT uq_materiales_id_tipo UNIQUE (material_id, tipo_material),
CONSTRAINT chk_materiales_tipo
CHECK (tipo_material IN ('libro','dvd','revista','audiolibro')),
CONSTRAINT fk_materiales_autor
FOREIGN KEY (autor_id) REFERENCES autores (autor_id)
ON DELETE SET NULL ON UPDATE CASCADE -- igual que libros.autor_id
);
CREATE TABLE materiales_libro (
material_id INTEGER NOT NULL,
tipo_material VARCHAR(15) NOT NULL DEFAULT 'libro',
isbn VARCHAR(13) NOT NULL,
num_paginas INTEGER,
encuadernacion VARCHAR(20),
CONSTRAINT pk_materiales_libro PRIMARY KEY (material_id),
CONSTRAINT uq_materiales_libro_isbn UNIQUE (isbn),
CONSTRAINT chk_materiales_libro_tipo CHECK (tipo_material = 'libro'),
CONSTRAINT fk_materiales_libro_material
FOREIGN KEY (material_id, tipo_material)
REFERENCES materiales (material_id, tipo_material)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE materiales_dvd (
material_id INTEGER NOT NULL,
tipo_material VARCHAR(15) NOT NULL DEFAULT 'dvd',
duracion_min INTEGER NOT NULL,
formato_video VARCHAR(15),
codigo_region INTEGER,
CONSTRAINT pk_materiales_dvd PRIMARY KEY (material_id),
CONSTRAINT chk_materiales_dvd_tipo CHECK (tipo_material = 'dvd'),
CONSTRAINT fk_materiales_dvd_material
FOREIGN KEY (material_id, tipo_material)
REFERENCES materiales (material_id, tipo_material)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE materiales_revista (
material_id INTEGER NOT NULL,
tipo_material VARCHAR(15) NOT NULL DEFAULT 'revista',
issn VARCHAR(9) NOT NULL,
numero VARCHAR(20) NOT NULL,
periodicidad VARCHAR(20),
CONSTRAINT pk_materiales_revista PRIMARY KEY (material_id),
CONSTRAINT uq_materiales_revista_issn_num UNIQUE (issn, numero), -- RN9
CONSTRAINT chk_materiales_revista_tipo CHECK (tipo_material = 'revista'),
CONSTRAINT fk_materiales_revista_material
FOREIGN KEY (material_id, tipo_material)
REFERENCES materiales (material_id, tipo_material)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE materiales_audiolibro (
material_id INTEGER NOT NULL,
tipo_material VARCHAR(15) NOT NULL DEFAULT 'audiolibro',
duracion_min INTEGER NOT NULL,
narrador VARCHAR(120),
formato_audio VARCHAR(15),
CONSTRAINT pk_materiales_audiolibro PRIMARY KEY (material_id),
CONSTRAINT chk_materiales_audiolibro_tipo CHECK (tipo_material = 'audiolibro'),
CONSTRAINT fk_materiales_audiolibro_material
FOREIGN KEY (material_id, tipo_material)
REFERENCES materiales (material_id, tipo_material)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- Regla 3: atributo multivaluado (R1)
CREATE TABLE subtitulos_dvd (
material_id INTEGER NOT NULL,
idioma VARCHAR(5) NOT NULL,
CONSTRAINT pk_subtitulos_dvd PRIMARY KEY (material_id, idioma),
CONSTRAINT fk_subtitulos_dvd_material
FOREIGN KEY (material_id) REFERENCES materiales_dvd (material_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- Regla 3: atributo multivaluado (R12)
CREATE TABLE telefonos_socio (
socio_id INTEGER NOT NULL,
numero VARCHAR(20) NOT NULL,
tipo VARCHAR(10) NOT NULL DEFAULT 'movil',
CONSTRAINT pk_telefonos_socio PRIMARY KEY (socio_id, numero),
CONSTRAINT chk_telefonos_socio_tipo CHECK (tipo IN ('movil','fijo','trabajo')),
CONSTRAINT fk_telefonos_socio_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- ---------------------------------------------------------------------
-- BLOQUE 3 · Salas y eventos (R3, R4, R5)
-- ---------------------------------------------------------------------
CREATE TABLE salas (
sala_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
sucursal_id INTEGER NOT NULL,
nombre VARCHAR(80) NOT NULL,
aforo INTEGER NOT NULL,
planta INTEGER NOT NULL DEFAULT 0,
accesible BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_salas PRIMARY KEY (sala_id),
CONSTRAINT uq_salas_sucursal_nombre UNIQUE (sucursal_id, nombre), -- R3, regla 8
CONSTRAINT fk_salas_sucursal
FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE tipos_evento (
tipo_evento_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
codigo VARCHAR(30) NOT NULL,
nombre VARCHAR(80) NOT NULL,
descripcion VARCHAR(500),
duracion_estandar_min INTEGER,
CONSTRAINT pk_tipos_evento PRIMARY KEY (tipo_evento_id),
CONSTRAINT uq_tipos_evento_codigo UNIQUE (codigo)
);
CREATE TABLE eventos (
evento_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
titulo VARCHAR(200) NOT NULL,
descripcion VARCHAR(2000),
tipo_evento_id INTEGER NOT NULL,
sala_id INTEGER, -- opcional: decisión D4
inicio TIMESTAMPTZ NOT NULL,
fin TIMESTAMPTZ NOT NULL,
plazas_ofertadas INTEGER NOT NULL,
estado VARCHAR(15) NOT NULL DEFAULT 'programado',
publicado BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT pk_eventos PRIMARY KEY (evento_id),
CONSTRAINT fk_eventos_tipo
FOREIGN KEY (tipo_evento_id) REFERENCES tipos_evento (tipo_evento_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_eventos_sala
FOREIGN KEY (sala_id) REFERENCES salas (sala_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE ponentes (
ponente_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
nombre VARCHAR(60) NOT NULL,
apellidos VARCHAR(80) NOT NULL,
email VARCHAR(120),
biografia VARCHAR(1000),
externo BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_ponentes PRIMARY KEY (ponente_id),
CONSTRAINT uq_ponentes_email UNIQUE (email)
);
-- ---------------------------------------------------------------------
-- BLOQUE 4 · Reglas 6, 7 y 9: inscripciones, informes, participaciones
-- ---------------------------------------------------------------------
CREATE TABLE inscripciones ( -- Regla 7 (N:M con atributos)
evento_id INTEGER NOT NULL,
socio_id INTEGER NOT NULL,
fecha_inscripcion TIMESTAMPTZ NOT NULL DEFAULT now(),
estado VARCHAR(15) NOT NULL DEFAULT 'confirmada',
acompanantes INTEGER NOT NULL DEFAULT 0,
CONSTRAINT pk_inscripciones PRIMARY KEY (evento_id, socio_id), -- R6
CONSTRAINT fk_inscripciones_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_inscripciones_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE informes_evento ( -- Regla 6 (1:1, opción B)
evento_id INTEGER NOT NULL,
asistentes_reales INTEGER NOT NULL,
valoracion_media NUMERIC(3,2),
observaciones VARCHAR(2000),
fecha_redaccion DATE NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT pk_informes_evento PRIMARY KEY (evento_id),
CONSTRAINT fk_informes_evento_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE participaciones ( -- Regla 9 (ternaria resuelta)
evento_id INTEGER NOT NULL,
ponente_id INTEGER NOT NULL,
rol VARCHAR(25) NOT NULL,
honorarios NUMERIC(8,2) NOT NULL DEFAULT 0,
CONSTRAINT pk_participaciones PRIMARY KEY (evento_id, ponente_id, rol),
CONSTRAINT fk_participaciones_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_participaciones_ponente
FOREIGN KEY (ponente_id) REFERENCES ponentes (ponente_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE eventos_materiales ( -- Regla 7 (N:M simple)
evento_id INTEGER NOT NULL,
material_id INTEGER NOT NULL,
papel VARCHAR(15) NOT NULL DEFAULT 'recomendado',
CONSTRAINT pk_eventos_materiales PRIMARY KEY (evento_id, material_id),
CONSTRAINT fk_eventos_materiales_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_eventos_materiales_material
FOREIGN KEY (material_id) REFERENCES materiales (material_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
-- ---------------------------------------------------------------------
-- BLOQUE 5 · Multas y pagos (R10, R11)
-- ---------------------------------------------------------------------
CREATE TABLE multas (
multa_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
socio_id INTEGER NOT NULL,
prestamo_id INTEGER, -- opcional: decisión D6
motivo VARCHAR(15) NOT NULL,
importe NUMERIC(6,2) NOT NULL,
fecha_emision DATE NOT NULL DEFAULT CURRENT_DATE,
estado VARCHAR(15) NOT NULL DEFAULT 'pendiente',
CONSTRAINT pk_multas PRIMARY KEY (multa_id),
CONSTRAINT uq_multas_prestamo_motivo UNIQUE (prestamo_id, motivo), -- R10
CONSTRAINT fk_multas_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_multas_prestamo
FOREIGN KEY (prestamo_id) REFERENCES prestamos (prestamo_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE pagos ( -- Regla 8 (entidad débil)
pago_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
multa_id INTEGER NOT NULL,
fecha_pago TIMESTAMPTZ NOT NULL DEFAULT now(),
importe NUMERIC(6,2) NOT NULL,
metodo VARCHAR(15) NOT NULL,
referencia VARCHAR(50),
CONSTRAINT pk_pagos PRIMARY KEY (pago_id),
CONSTRAINT fk_pagos_multa
FOREIGN KEY (multa_id) REFERENCES multas (multa_id)
ON DELETE RESTRICT ON UPDATE CASCADE -- registro contable: no cascada
);
-- ---------------------------------------------------------------------
-- BLOQUE 6 · Adaptación de las tablas existentes a la jerarquía
-- ---------------------------------------------------------------------
ALTER TABLE ejemplares ADD COLUMN material_id INTEGER;
ALTER TABLE ejemplares ADD COLUMN num_ejemplar INTEGER;
-- (migración: materiales hereda los libros; ejemplares.material_id = antiguo libro_id)
ALTER TABLE ejemplares DROP CONSTRAINT fk_ejemplares_libro;
ALTER TABLE ejemplares DROP COLUMN libro_id;
ALTER TABLE ejemplares ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE ejemplares
ADD CONSTRAINT uq_ejemplares_material_num UNIQUE (material_id, num_ejemplar),
ADD CONSTRAINT fk_ejemplares_material
FOREIGN KEY (material_id) REFERENCES materiales (material_id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE reservas ADD COLUMN material_id INTEGER;
ALTER TABLE reservas DROP CONSTRAINT fk_reservas_libro;
ALTER TABLE reservas DROP COLUMN libro_id;
ALTER TABLE reservas ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE reservas
ADD CONSTRAINT fk_reservas_material
FOREIGN KEY (material_id) REFERENCES materiales (material_id)
ON DELETE CASCADE ON UPDATE CASCADE;
DROP TABLE libros;
CREATE VIEW libros AS
SELECT m.material_id AS libro_id, l.isbn, m.titulo, m.autor_id,
m.editorial, m.anio_publicacion, m.idioma
FROM materiales m
JOIN materiales_libro l ON l.material_id = m.material_id;Resumen de las acciones referenciales
| Clave ajena | ON DELETE |
Motivo |
|---|---|---|
salas.sucursal_id |
RESTRICT | Coherente con socios y ejemplares; no se borra una sucursal en uso |
materiales.autor_id |
SET NULL | Igual que la antigua libros.autor_id: el material sobrevive al autor |
materiales_*.material_id |
CASCADE | Las subtablas no existen sin la superclase |
subtitulos_dvd.material_id |
CASCADE | Atributo multivaluado del DVD |
telefonos_socio.socio_id |
CASCADE | Atributo multivaluado del socio |
ejemplares.material_id |
CASCADE | Igual que la antigua ejemplares.libro_id |
eventos.tipo_evento_id |
RESTRICT | No se borra un tipo con eventos históricos |
eventos.sala_id |
RESTRICT | Perder la sala de un evento pasado destruye C5 |
inscripciones.evento_id |
CASCADE | La inscripción no existe sin su evento |
inscripciones.socio_id |
RESTRICT | Hecho histórico con valor estadístico (C5) |
informes_evento.evento_id |
CASCADE | El informe no existe sin su evento |
participaciones.evento_id |
CASCADE | Igual |
participaciones.ponente_id |
RESTRICT | Registro con implicaciones económicas |
eventos_materiales.* |
CASCADE | Relación auxiliar sin valor propio |
multas.socio_id |
RESTRICT | Registro económico |
multas.prestamo_id |
RESTRICT | Registro económico |
pagos.multa_id |
RESTRICT | Entidad débil, pero contable: nunca cascada |
La última fila merece atención porque contradice la intuición: pagos es una entidad débil de multas y aun así no es CASCADE. La forma de la relación sugiere la acción; el valor del dato la decide.
- Revisión posterior: validar el esquema resultante
Aplicar diez reglas y quedarse tan tranquilo es un error. Hay tres comprobaciones que se hacen siempre.
Comprobación 1 — Toda consulta del enunciado se puede responder
Recorremos las doce consultas de 04-01 y escribimos el FROM ... JOIN de cada una. Un extracto de las tres menos evidentes:
C5 — Ocupación media de cada sala por sucursal y trimestre:
SELECT su.nombre AS sucursal,
s.nombre AS sala,
DATE_TRUNC('quarter', e.inicio) AS trimestre,
ROUND(AVG(oc.plazas_ocupadas::numeric / NULLIF(s.aforo, 0)) * 100, 1) AS ocupacion_pct
FROM eventos e
JOIN salas s ON s.sala_id = e.sala_id
JOIN sucursales su ON su.sucursal_id = s.sucursal_id
JOIN v_eventos_ocupacion oc ON oc.evento_id = e.evento_id
WHERE e.estado = 'celebrado'
GROUP BY su.nombre, s.nombre, DATE_TRUNC('quarter', e.inicio)
ORDER BY su.nombre, s.nombre, trimestre;sucursal | sala | trimestre | ocupacion_pct ----------+-------------------+------------------------+--------------- Centro | Sala Polivalente | 2026-04-01 00:00:00+02 | 72.4 Centro | Sala Polivalente | 2026-07-01 00:00:00+02 | 61.0 Norte | Sala Infantil | 2026-04-01 00:00:00+02 | 88.7 (3 filas)
C7 — Socios con deuda pendiente superior a 20 € (regla 4: el pendiente es derivado):
SELECT s.socio_id,
s.nombre || ' ' || s.apellidos AS socio,
SUM(m.importe - COALESCE(p.pagado, 0)) AS deuda
FROM socios s
JOIN multas m ON m.socio_id = s.socio_id AND m.estado = 'pendiente'
LEFT JOIN (SELECT multa_id, SUM(importe) AS pagado
FROM pagos GROUP BY multa_id) p ON p.multa_id = m.multa_id
GROUP BY s.socio_id, s.nombre, s.apellidos
HAVING SUM(m.importe - COALESCE(p.pagado, 0)) > 20
ORDER BY deuda DESC; socio_id | socio | deuda
----------+---------------+-------
15 | Iván Pereda | 27.40
16 | Nuria Bastos | 22.00
(2 filas)C10 — Los diez materiales más prestados por tipo (esta es la que la estrategia de jerarquía tenía que hacer posible):
SELECT m.tipo_material, m.titulo, COUNT(*) AS prestamos
FROM prestamos pr
JOIN ejemplares ej ON ej.ejemplar_id = pr.ejemplar_id
JOIN materiales m ON m.material_id = ej.material_id
GROUP BY m.tipo_material, m.material_id, m.titulo
ORDER BY prestamos DESC
LIMIT 10;Un solo JOIN a materiales, sin UNION de cuatro ramas. Es exactamente el beneficio que buscábamos al descartar la estrategia 3.
Comprobación 2 — Ninguna tabla sin clave primaria
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
ON c.table_name = t.table_name
AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
AND c.constraint_name IS NULL;Cero filas es la respuesta correcta. Una tabla sin clave primaria admite duplicados exactos, no se puede referenciar y no se puede actualizar fila a fila con seguridad.
Comprobación 3 — Ninguna clave ajena huérfana ni ninguna relación sin FK
Dos consultas. La primera, del catálogo, lista todas las claves ajenas declaradas para contrastarlas con el diagrama:
SELECT conrelid::regclass AS tabla,
conname AS restriccion,
confrelid::regclass AS referencia
FROM pg_constraint
WHERE contype = 'f'
ORDER BY 1, 2;La segunda busca columnas que parecen clave ajena por su nombre pero no la tienen declarada, que es el olvido más común:
SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND c.column_name LIKE '%\_id'
AND NOT EXISTS (
SELECT 1 FROM information_schema.key_column_usage k
JOIN information_schema.table_constraints t
ON t.constraint_name = k.constraint_name
WHERE k.table_name = c.table_name
AND k.column_name = c.column_name
AND t.constraint_type IN ('FOREIGN KEY','PRIMARY KEY'))
ORDER BY 1, 2;Lista de comprobación final
| # | Comprobación | ¿Cumple? |
|---|---|---|
| 1 | Las 12 consultas del enunciado tienen respuesta | Sí |
| 2 | Toda tabla tiene clave primaria | Sí |
| 3 | Toda columna *_id es PK o FK declarada |
Sí |
| 4 | Toda entidad del diagrama tiene tabla | Sí, 21 de 21 |
| 5 | Toda relación del diagrama está representada | Sí |
| 6 | Los atributos multivaluados están en tablas propias | Sí (2) |
| 7 | Los atributos derivados NO son columnas | Sí (5, en vistas) |
| 8 | Las claves naturales tienen UNIQUE |
Sí (isbn, issn+numero, codigo, email, nombre de sala) |
| 9 | Cada acción referencial está razonada | Sí (tabla del apartado 12) |
| 10 | Las reglas RN1–RN10 están asignadas | Pendiente → 04-04 |
Nueve de diez. La décima es la lección siguiente.
Errores Comunes y Consejos
Poner la clave ajena en el lado 1. El error estructural más grave y el más fácil de detectar: si la FK admitiera varios valores, está en el lado equivocado. Relee la cardinalidad del diagrama y pregúntate "¿cuántos valores necesito guardar aquí?".
Crear una tabla de unión para una relación 1:N. Funciona, pero permite estados que el negocio prohíbe: si eventos_salas fuera una tabla de unión, nada impediría dos filas para el mismo evento, es decir, un evento en dos salas. La estructura debe hacer imposible lo prohibido, no solo permitir lo correcto.
Olvidar los atributos de la relación N:M. Se crea inscripciones (evento_id, socio_id) y se da por terminado. La fecha, el estado y los acompañantes no tienen sitio, y acaban apareciendo en socios o en eventos, donde no significan nada.
Almacenar un derivado sin darse cuenta. eventos.sucursal_id "para no hacer el JOIN" crea la posibilidad de que un evento diga estar en Centro mientras su sala está en Norte. Si un valor se puede deducir del esquema, guardarlo es crear una contradicción potencial.
Aplicar CASCADE por comodidad. ON DELETE CASCADE en todas las claves ajenas evita errores al desarrollar y borra medio esquema en producción. Un DELETE FROM socios WHERE socio_id = 14 con cascadas por todas partes se lleva por delante préstamos, inscripciones y multas. Cada acción se decide por separado, con el criterio de 02-06.
Descomponer atributos compuestos que nadie va a consultar por partes. Cuatro columnas de dirección para un ponente externo cuya dirección solo se imprime en un sobre es trabajo permanente a cambio de nada.
Elegir tabla única para una jerarquía con muchos atributos específicos. Es la tentación de la simplicidad, y produce once columnas nulas y once CHECK condicionales que nadie mantiene. Cuenta los atributos específicos antes de decidir: con más de dos o tres por subtipo, casi siempre gana la tabla por subclase.
Consejo: aplica las reglas en orden y no improvises. El algoritmo funciona precisamente porque cada paso apoya sobre el anterior. Saltarse la regla 1 y empezar por las relaciones produce FK a tablas que aún no existen.
Consejo: anota junto a cada tabla qué regla la generó. El script del apartado 12 lleva esos comentarios. Cuando alguien pregunte por qué subtitulos_dvd es una tabla aparte, la respuesta está escrita: regla 3, atributo multivaluado, R1.
Consejo: crea el esquema en una base vacía y ejecútalo entero antes de darlo por bueno. Los errores de orden de creación, de nombres de restricción duplicados y de tipos incompatibles en las FK aparecen en segundos. Un script de esquema que nadie ha ejecutado es una hipótesis.
Ejercicios
Ejercicio 1 — Aplicar las reglas a un fragmento nuevo
BiblioRed incorpora en la v1.1 el siguiente fragmento:
R16 — Puntos de recogida. Además de en las cuatro sucursales, los materiales reservados se pueden recoger en puntos de recogida (taquillas automáticas) instalados en centros cívicos. Cada punto tiene un código, una dirección (calle, número, código postal), un número de taquillas y la sucursal que lo abastece. Un socio, al hacer una reserva, elige dónde recogerla: en una sucursal o en un punto de recogida. Cada punto tiene además un horario de apertura por día de la semana (lunes a domingo, con hora de apertura y de cierre; algunos días cierra).
Aplica las reglas correspondientes y escribe el SQL. Indica explícitamente qué regla aplicas en cada paso, cómo resuelves el "una sucursal o un punto de recogida" y qué acciones referenciales eliges con su motivo.
Ejercicio 2 — Elegir la estrategia de una jerarquía
BiblioRed quiere modelar las notificaciones que envía a los socios. Hay tres tipos:
- Recordatorio de devolución: lleva el préstamo asociado y los días que faltan.
- Aviso de reserva disponible: lleva la reserva asociada y la fecha límite de recogida.
- Recordatorio de evento: lleva el evento asociado y las horas que faltan.
Todas comparten: destinatario (socio), canal (email, sms, push), fecha de envío, estado (pendiente, enviada, fallida) y texto del mensaje. Se envían unas 3.000 al mes, se consultan casi siempre todas juntas ("las notificaciones de este socio, ordenadas por fecha") y se purgan a los seis meses.
- Determina si la jerarquía es disjunta/solapada y total/parcial.
- Elige una de las tres estrategias y justifícala con al menos tres argumentos de la tabla comparativa.
- Escribe el SQL de la estrategia elegida.
Ejercicio 3 — Detectar errores de transformación
Un compañero entrega esta parte del esquema. Localiza al menos cinco errores de transformación, indica qué regla se ha incumplido y escribe la versión corregida.
CREATE TABLE eventos (
evento_id SERIAL PRIMARY KEY,
titulo VARCHAR(200),
sala_id INTEGER REFERENCES salas(sala_id),
aforo_sala INTEGER,
sucursal_id INTEGER REFERENCES sucursales(sucursal_id),
ponente1_id INTEGER REFERENCES ponentes(ponente_id),
ponente2_id INTEGER REFERENCES ponentes(ponente_id),
inicio TIMESTAMPTZ,
plazas_ofertadas INTEGER,
plazas_libres INTEGER,
materiales VARCHAR(300)
);
CREATE TABLE inscripciones (
evento_id INTEGER REFERENCES eventos(evento_id) ON DELETE CASCADE,
socio_id INTEGER REFERENCES socios(socio_id) ON DELETE CASCADE
);Soluciones
Solución al Ejercicio 1
Regla 1 (entidad fuerte) + Regla 2 (atributo compuesto):
CREATE TABLE puntos_recogida (
punto_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
codigo VARCHAR(15) NOT NULL,
dir_calle VARCHAR(120) NOT NULL,
dir_numero VARCHAR(10),
dir_codigo_postal VARCHAR(5) NOT NULL,
num_taquillas INTEGER NOT NULL,
sucursal_id INTEGER NOT NULL, -- Regla 5: 1:N
CONSTRAINT pk_puntos_recogida PRIMARY KEY (punto_id),
CONSTRAINT uq_puntos_recogida_codigo UNIQUE (codigo),
CONSTRAINT fk_puntos_recogida_sucursal
FOREIGN KEY (sucursal_id) REFERENCES sucursales (sucursal_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);La dirección se descompone (regla 2) porque el enunciado la enumera troceada y porque la web ya busca por código postal (R13). sucursal_id va aquí (regla 5, lado N) y es RESTRICT: una sucursal con puntos abastecidos no se borra.
Regla 3 (atributo multivaluado): el horario. Es multivaluado —siete valores simultáneos— y compuesto —apertura y cierre—. Se convierte en tabla, con el día como identificador parcial:
CREATE TABLE horarios_punto (
punto_id INTEGER NOT NULL,
dia_semana INTEGER NOT NULL, -- 1 = lunes ... 7 = domingo
hora_apertura TIME, -- NULL = cerrado ese día
hora_cierre TIME,
CONSTRAINT pk_horarios_punto PRIMARY KEY (punto_id, dia_semana),
CONSTRAINT chk_horarios_dia CHECK (dia_semana BETWEEN 1 AND 7),
CONSTRAINT fk_horarios_punto
FOREIGN KEY (punto_id) REFERENCES puntos_recogida (punto_id)
ON DELETE CASCADE ON UPDATE CASCADE
);CASCADE porque el horario no existe fuera de su punto.
El "una sucursal o un punto": relación exclusiva. Es el caso interesante del ejercicio y admite dos soluciones:
Opción 1 — dos claves ajenas anulables con un CHECK de exclusión mutua:
ALTER TABLE reservas ADD COLUMN recogida_sucursal_id INTEGER;
ALTER TABLE reservas ADD COLUMN recogida_punto_id INTEGER;
ALTER TABLE reservas
ADD CONSTRAINT fk_reservas_recogida_sucursal
FOREIGN KEY (recogida_sucursal_id) REFERENCES sucursales (sucursal_id)
ON DELETE RESTRICT,
ADD CONSTRAINT fk_reservas_recogida_punto
FOREIGN KEY (recogida_punto_id) REFERENCES puntos_recogida (punto_id)
ON DELETE RESTRICT,
ADD CONSTRAINT chk_reservas_recogida_exclusiva
CHECK ((recogida_sucursal_id IS NOT NULL AND recogida_punto_id IS NULL)
OR (recogida_sucursal_id IS NULL AND recogida_punto_id IS NOT NULL));Opción 2 — generalizar: crear una entidad puntos_servicio de la que sucursales y puntos de recogida sean subtipos (regla 10), y que reservas referencie con una sola FK.
La opción 1 es más simple y suficiente con dos alternativas. La opción 2 es preferible si mañana aparecen más lugares de recogida (bibliobús, oficina de correos). Para la v1.1 se elige la opción 1, y se anota en el registro de decisiones que la generalización es el plan si aparece un tercer tipo.
Solución al Ejercicio 2
1. Naturaleza de la jerarquía. Disjunta: una notificación es de un solo tipo. Total: toda notificación es de uno de los tres tipos; no existen notificaciones genéricas.
2. Estrategia elegida: tabla única. Argumentos de la tabla comparativa:
- Pocos atributos específicos: cada subtipo aporta dos columnas (una FK y un número). Con tabla por subclase habría cuatro tablas para guardar seis columnas en total, y tres de esas tablas serían casi triviales.
- El patrón de consulta dominante es sobre la superclase: "las notificaciones de este socio ordenadas por fecha" no necesita los detalles del subtipo. Con tabla por subclase esa consulta, la más frecuente con diferencia, exigiría un
LEFT JOINtriple o tres consultas. - Volumen y ciclo de vida: 3.000 al mes con purga a seis meses son unas 18.000 filas vivas. El desperdicio de dos columnas nulas por fila es irrelevante, y la purga es un solo
DELETEsobre una tabla en lugar de cuatro coordinados. - Nadie referencia las notificaciones desde fuera, así que el argumento decisivo del caso de los materiales aquí no aplica.
El precio conocido es que no se puede exigir NOT NULL en las FK específicas y hay que suplirlo con CHECK condicionales. Con tres subtipos y una columna obligatoria cada uno, son tres CHECK perfectamente manejables.
3. SQL:
CREATE TABLE notificaciones (
notificacion_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
socio_id INTEGER NOT NULL,
tipo VARCHAR(20) NOT NULL,
canal VARCHAR(10) NOT NULL,
fecha_envio TIMESTAMPTZ NOT NULL DEFAULT now(),
estado VARCHAR(12) NOT NULL DEFAULT 'pendiente',
mensaje VARCHAR(500) NOT NULL,
-- atributos específicos por subtipo
prestamo_id INTEGER,
dias_restantes INTEGER,
reserva_id INTEGER,
fecha_limite DATE,
evento_id INTEGER,
horas_restantes INTEGER,
CONSTRAINT pk_notificaciones PRIMARY KEY (notificacion_id),
CONSTRAINT chk_notificaciones_tipo
CHECK (tipo IN ('recordatorio_devolucion','aviso_reserva','recordatorio_evento')),
CONSTRAINT chk_notificaciones_canal CHECK (canal IN ('email','sms','push')),
CONSTRAINT chk_notificaciones_estado
CHECK (estado IN ('pendiente','enviada','fallida')),
-- coherencia entre discriminante y atributos específicos
CONSTRAINT chk_notif_devolucion
CHECK (tipo <> 'recordatorio_devolucion' OR prestamo_id IS NOT NULL),
CONSTRAINT chk_notif_reserva
CHECK (tipo <> 'aviso_reserva' OR reserva_id IS NOT NULL),
CONSTRAINT chk_notif_evento
CHECK (tipo <> 'recordatorio_evento' OR evento_id IS NOT NULL),
CONSTRAINT fk_notificaciones_socio
FOREIGN KEY (socio_id) REFERENCES socios (socio_id) ON DELETE CASCADE,
CONSTRAINT fk_notificaciones_prestamo
FOREIGN KEY (prestamo_id) REFERENCES prestamos (prestamo_id) ON DELETE CASCADE,
CONSTRAINT fk_notificaciones_reserva
FOREIGN KEY (reserva_id) REFERENCES reservas (reserva_id) ON DELETE CASCADE,
CONSTRAINT fk_notificaciones_evento
FOREIGN KEY (evento_id) REFERENCES eventos (evento_id) ON DELETE CASCADE
);Nótese el contraste con la decisión de los materiales: la misma pregunta, distinta respuesta, y las dos correctas. La estrategia no depende de la teoría de la jerarquía, sino del número de atributos específicos, del patrón de consulta y de si alguien referencia los subtipos.
Solución al Ejercicio 3
| # | Error | Regla incumplida | Daño concreto |
|---|---|---|---|
| 1 | aforo_sala copiado en eventos |
Regla 4 (derivado) y "una cosa, un sitio" | Al reformar una sala, 300 eventos quedan con el aforo antiguo; RN1 se valida contra un dato obsoleto |
| 2 | sucursal_id en eventos |
Regla 4 (derivado) | La sucursal se deduce vía sala_id. Se puede crear un evento que diga estar en Centro con una sala de Norte: ciclo en el diagrama (04-02) |
| 3 | ponente1_id, ponente2_id |
Regla 7 (N:M) | R7 dice "varios ponentes" y "varios papeles por persona". Con dos columnas no cabe el tercero, no hay dónde poner el rol ni los honorarios, y "¿en cuántos eventos participó Elena Roig?" necesita UNION |
| 4 | plazas_libres como columna |
Regla 4 (derivado) | Se desincroniza en cuanto alguien cancela; C2 pasa a mentir |
| 5 | materiales VARCHAR(300) |
Regla 7 y anti-patrón de lista con comas | Sin FK, sin integridad, sin poder responder "¿en qué eventos se ha comentado este libro?" |
| 6 | inscripciones sin clave primaria |
Regla 7 y comprobación 2 | Un socio puede inscribirse infinitas veces al mismo evento, violando R6 |
| 7 | inscripciones sin atributos propios |
Regla 7 | No hay dónde guardar fecha, estado ni acompañantes (R6) |
| 8 | inscripciones.socio_id ON DELETE CASCADE |
Criterio de 02-06 | Borrar un socio destruye el historial de asistencia y falsea C5 |
| 9 | Faltan NOT NULL en titulo, inicio, plazas_ofertadas |
Regla 1 + participación del diagrama | Se pueden crear eventos sin título ni fecha |
Versión corregida:
CREATE TABLE eventos (
evento_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
titulo VARCHAR(200) NOT NULL,
tipo_evento_id INTEGER NOT NULL,
sala_id INTEGER,
inicio TIMESTAMPTZ NOT NULL,
fin TIMESTAMPTZ NOT NULL,
plazas_ofertadas INTEGER NOT NULL,
estado VARCHAR(15) NOT NULL DEFAULT 'programado',
publicado BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT pk_eventos PRIMARY KEY (evento_id),
CONSTRAINT fk_eventos_tipo FOREIGN KEY (tipo_evento_id)
REFERENCES tipos_evento (tipo_evento_id) ON DELETE RESTRICT,
CONSTRAINT fk_eventos_sala FOREIGN KEY (sala_id)
REFERENCES salas (sala_id) ON DELETE RESTRICT
);
-- aforo_sala, sucursal_id y plazas_libres: eliminados (derivados, vista v_eventos_ocupacion)
-- ponente1_id / ponente2_id: sustituidos por la tabla participaciones
-- materiales: sustituido por la tabla eventos_materiales
CREATE TABLE inscripciones (
evento_id INTEGER NOT NULL,
socio_id INTEGER NOT NULL,
fecha_inscripcion TIMESTAMPTZ NOT NULL DEFAULT now(),
estado VARCHAR(15) NOT NULL DEFAULT 'confirmada',
acompanantes INTEGER NOT NULL DEFAULT 0,
CONSTRAINT pk_inscripciones PRIMARY KEY (evento_id, socio_id),
CONSTRAINT fk_inscripciones_evento FOREIGN KEY (evento_id)
REFERENCES eventos (evento_id) ON DELETE CASCADE,
CONSTRAINT fk_inscripciones_socio FOREIGN KEY (socio_id)
REFERENCES socios (socio_id) ON DELETE RESTRICT
);Conclusión
Esta lección ha convertido un diagrama en un esquema ejecutable mediante un algoritmo de diez reglas.
- Reglas 1 a 4 (atributos): cada entidad fuerte es una tabla con su PK subrogada y
UNIQUEsobre la clave natural; los atributos compuestos se descomponen en columnas —salvo que nadie consulte las partes—; los multivaluados se convierten siempre en tabla aparte, lo que elimina de raíz los anti-patrones de columnas numeradas y listas con comas; y los derivados no se almacenan, con dos excepciones nombradas: el rendimiento (05-04) y los hechos históricos que deben congelarse. - Regla 5 (1:N): la clave ajena va siempre en el lado N, porque es el único que admite un solo valor. La participación del diagrama se traduce literalmente en
NOT NULLo su ausencia. - Regla 6 (1:1): tres opciones. Fusionar si ambos lados son totales; FK con PK compartida en el lado opcional si uno es parcial —lo elegido para
informes_evento, porque distingue "sin redactar" de "cero asistentes"—; tabla intermedia solo si ambos son parciales. - Regla 7 (N:M): tabla de unión con dos FK y, sobre todo, con los atributos propios de la relación, que es lo que más se olvida. La PK compuesta implementa la regla de negocio sin una línea de código.
- Regla 8 (entidad débil): PK compuesta con la del propietario. En la práctica, una entidad débil puede llevar clave subrogada siempre que su clave natural compuesta se declare
UNIQUE: lo que no es negociable es la unicidad. - Regla 9 (ternaria): tres FK, pero antes la prueba de descomposición. Cuatro de cada cinco relaciones ternarias aparentes son otra cosa; la de BiblioRed resultó ser una N:M con el rol en la clave.
- Regla 10 (jerarquía): tres estrategias con una tabla comparativa. BiblioRed eligió tabla por subclase porque
ejemplares,prestamosyreservasnecesitan una entidad común, porque cada subtipo tiene atributos obligatorios propios y porque añadir un tipo nuevo no toca nada existente. La coherencia del discriminante se cierra con una clave ajena compuesta(material_id, tipo_material). - La compatibilidad con el código existente se resolvió convirtiendo
librosen una vista: el ejemplo más limpio de independencia lógica de todo el curso. - Las acciones referenciales se razonaron una a una, y la conclusión es que la forma de la relación sugiere la acción, pero el valor del dato la decide:
inscripciones.socio_idesRESTRICTmientrasreservas.socio_idesCASCADE, ypagoses una entidad débil que jamás debe cascadear porque es un registro contable. - La revisión posterior —doce consultas respondidas, ninguna tabla sin clave primaria, ninguna columna
*_idsin FK declarada— cerró nueve de los diez puntos de la lista de comprobación.
Queda el décimo, y es el más importante: las diez reglas de negocio RN1–RN10 siguen sin estar en ninguna parte del esquema. Nada impide hoy una multa de −40 €, un evento que termina antes de empezar, un aforo de cero o un estado 'confimada' con una errata. Y los tipos de datos son provisionales: hay INTEGER donde bastaría SMALLINT, VARCHAR(15) puestos a ojo, y el importe de las multas está en NUMERIC por buenas razones que aún no hemos explicado.
En la lección siguiente, 04-04 Tipos de Datos y Restricciones, revisamos el esquema columna por columna: qué tipo entero elegir y cuándo se queda corto, por qué el dinero jamás va en coma flotante —con una demostración que sorprende—, TIMESTAMP frente a TIMESTAMPTZ y el problema de las zonas horarias, ENUM frente a tabla de catálogo frente a CHECK, la colación que decide si "Àngels" aparece antes o después de "Angel" al buscar títulos, y el catálogo completo de restricciones: NOT NULL, DEFAULT, UNIQUE con su comportamiento sorprendente ante los NULL, CHECK de una y varias columnas, columnas generadas, dominios reutilizables y cómo añadir restricciones a una tabla que ya tiene datos sin bloquearla. El resultado será la versión definitiva y blindada del esquema de BiblioRed.
Fundamentos de Bases de Datos
Módulo 1: Introducción a las Bases de Datos
- Conceptos Básicos de Bases de Datos
- Tipos de Bases de Datos
- Historia y Evolución de las Bases de Datos
- Sistemas Gestores de Bases de Datos y Arquitectura
Módulo 2: Bases de Datos Relacionales
- Modelo Relacional
- Lenguaje SQL
- Operaciones Básicas en SQL
- Consultas Multitabla: JOIN y Subconsultas
- Agregación y Agrupación de Datos
- Integridad Referencial
Módulo 3: Bases de Datos No Relacionales
- Introducción a NoSQL
- Tipos de Bases de Datos NoSQL
- Modelado de Datos en NoSQL
- Comparación entre Bases de Datos Relacionales y No Relacionales
Módulo 4: Diseño de Esquemas
- Principios de Diseño de Esquemas
- Diagramas Entidad-Relación (ER)
- Transformación de Diagramas ER a Esquemas Relacionales
- Tipos de Datos y Restricciones
Módulo 5: Normalización
Módulo 6: Transacciones, Rendimiento y Seguridad
- Transacciones y Propiedades ACID
- Concurrencia y Niveles de Aislamiento
- Índices y Optimización de Consultas
- Seguridad, Permisos y Copias de Seguridad
Módulo 7: Ejercicios Prácticos
- Ejercicios de SQL
- Ejercicios de Diseño de Esquemas
- Ejercicios de Normalización
- Ejercicios de Consultas Avanzadas y Transacciones
Módulo 8: Casos de Estudio
- Caso de Estudio: Base de Datos Relacional
- Caso de Estudio: Base de Datos No Relacional
- Caso de Estudio: Persistencia Políglota