Módulo 21: Del modelo E/R al relacional

Objetivos del módulo

Entender en qué consiste el diseño lógico y qué información se pierde al pasar del modelo E/R al relacional
Transformar entidades fuertes y todos los tipos de atributos (simples, identificadores, compuestos, multivaluados, derivados y opcionales)
Transformar relaciones 1:N y 1:1 con todas las combinaciones de participación, y decidir dónde va la clave ajena
Transformar relaciones N:M, atributos de relación y relaciones reflexivas
Calcular la clave primaria de una relación ternaria o n-aria según sus cardinalidades
Transformar entidades débiles en existencia y en identificación
Elegir la mejor de las tres opciones para transformar una generalización
Transformar agregaciones y categorías (uniones)
Identificar las restricciones que se pierden y recuperarlas con NOT NULL, UNIQUE, CHECK, aserciones o disparadores
Aplicar todo el proceso a casos completos: el esquema EMPRESA, una biblioteca y un hospital

1. El diseño lógico

1.1. De dónde partimos y adónde llegamos

En el bloque 2 (módulos 04 a 15) aprendiste a construir el esquema conceptual con el modelo E/R extendido. Ese esquema es independiente del SGBD: habla de entidades, relaciones y jerarquías, conceptos que ningún SGBD relacional entiende directamente. El diseño lógico traduce ese esquema al único concepto que sí entiende un SGBD relacional: la relación (tabla), con sus claves y restricciones.

flowchart LR
    ER["Esquema conceptual<br/>(E/R extendido)"] --> T["Reglas de<br/>transformación"]
    T --> ESQ["Esquema relacional<br/>(relaciones, PK, FK, UK, CHECK)"]
    ESQ --> N["Normalización<br/>(módulo 20)"]
    N --> DDL["Script DDL<br/>(CREATE TABLE)"]

📘 Concepto: La transformación E/R → relacional es un conjunto de reglas sistemáticas que, aplicadas a cada elemento del diagrama (entidad, atributo, relación, jerarquía…), producen un esquema relacional que representa la misma información. Bien aplicadas, dan un esquema que normalmente ya está en tercera forma normal.

1.2. Pérdida semántica

El modelo relacional tiene menos capacidad expresiva que el E/R extendido. Algunas restricciones del diagrama se traducen limpiamente a claves y NOT NULL, pero otras (cardinalidades mínimas de 1 en el lado “muchos”, máximos concretos como “como mucho 3”, coberturas totales de una jerarquía…) no tienen equivalente directo y hay que recuperarlas con CHECK, aserciones, disparadores o lógica de la aplicación. Lo estudiarás en la sección 13.

⚠️ Importante: Una transformación no termina cuando tienes las tablas: termina cuando has documentado todas las restricciones del E/R que el esquema relacional no recoge y has decidido cómo se van a garantizar.

1.3. Orden recomendado

Entidades fuertes (y sus atributos).
Entidades débiles.
Relaciones 1:1 y 1:N (claves ajenas).
Relaciones N:M, ternarias y n-arias (relaciones nuevas).
Generalizaciones.
Agregaciones y categorías.
Restricciones perdidas.

Seguir este orden evita que una clave ajena apunte a una relación que aún no has definido.

1.4. Notación

Se usa la notación del curso. En los diagramas E/R, la pareja (mín, máx) escrita junto a la entidad A indica con cuántas ocurrencias de A se relaciona una ocurrencia de la otra entidad. En el esquema relacional: clave primaria subrayada, claves ajenas en cursiva (detalladas debajo con →), (N) en los atributos que admiten nulos, UK: para claves alternativas y CHECK: para restricciones de verificación.

2. Transformación de entidades fuertes

📘 Concepto: Regla 1. Cada entidad fuerte se transforma en una relación con el mismo nombre (en plural), cuyos atributos son los atributos de la entidad. El identificador principal de la entidad pasa a ser la clave primaria.

💻 Ejemplo: Biblioteca municipal. Un socio se identifica por su número de socio; también tiene un DNI único, nombre, fecha de alta y, opcionalmente, email.

flowchart LR
    SOCIO[SOCIO]
    SOCIO --- s_num([<u>num_socio</u>])
    SOCIO --- s_dni([dni])
    SOCIO --- s_nom([nombre])
    SOCIO --- s_alta([fecha_alta])
    SOCIO --- s_mail(["email (0,1)"])

Esquema relacional:

SOCIOS (num_socio, dni, nombre, fecha_alta, email (N))

UK: dni

Ejemplo de extensión:

num_socio	dni	nombre	fecha_alta	email
1	11111111H	Rosa Vidal	12/03/2024	rosa@correo.es
2	22222222J	Marcos Gil	05/09/2025	NULL
3	33333333P	Inés Llorens	20/01/2026	ines@correo.es

3. Transformación de atributos

3.1. Atributos simples e identificadores

Atributo E/R	Se transforma en	Restricción
Simple obligatorio	Atributo de la relación	`NOT NULL`
Simple opcional (0,1)	Atributo de la relación	Admite nulos `(N)`
Identificador principal	Clave primaria	`PRIMARY KEY`
Identificador alternativo	Clave alternativa	`UNIQUE` (+ `NOT NULL` si es obligatorio)
Atributo con dominio restringido	Atributo de la relación	`CHECK` o dominio SQL

3.2. Atributos compuestos

Un atributo compuesto (por ejemplo, direccion formada por calle, numero, cp y ciudad) tiene dos transformaciones posibles:

Opción A (recomendada): descomponerlo en sus atributos simples, que pasan directamente a la relación. Permite buscar, ordenar y agrupar por cualquiera de ellos.
Opción B: tratarlo como un único atributo que concatena los componentes. Solo es aceptable si el dato nunca se va a usar por partes.

💻 Ejemplo: Agencia de viajes. Un cliente tiene un código, un nombre y una dirección compuesta.

flowchart LR
    CLIENTE[CLIENTE]
    CLIENTE --- c_cod([<u>cod_cliente</u>])
    CLIENTE --- c_nom([nombre])
    CLIENTE --- c_dir([direccion])
    c_dir --- c_calle([calle])
    c_dir --- c_num([numero])
    c_dir --- c_cp([cp])
    c_dir --- c_ciu([ciudad])

Opción A:

CLIENTES_VIAJES (cod_cliente, nombre, calle, numero, cp, ciudad)

Opción B:

CLIENTES_VIAJES (cod_cliente, nombre, direccion)

💡 Consejo: Si el atributo compuesto es opcional como un todo (el cliente puede no tener dirección) pero sus componentes son obligatorios cuando existe, la opción A deja cuatro columnas (N), y puedes añadir un CHECK del tipo “o todas nulas o todas no nulas”.

3.3. Atributos multivaluados

📘 Concepto: Regla. Un atributo multivaluado se transforma en una nueva relación formada por la clave primaria de la entidad (como clave ajena) y el atributo multivaluado. La clave primaria de la nueva relación es la combinación de ambos.

💻 Ejemplo: Un socio de la biblioteca puede tener varios teléfonos.

flowchart LR
    SOCIO[SOCIO]
    SOCIO --- s_num([<u>num_socio</u>])
    SOCIO --- s_nom([nombre])
    SOCIO --- s_tel(((telefono)))

SOCIOS (num_socio, nombre, …)

TELEFONOS_SOCIO (*num_socio*, telefono)

num_socio → SOCIOS(num_socio) [ON DELETE CASCADE]

num_socio	telefono
1	600111222
1	963000111
3	655444333

El socio 1 tiene dos teléfonos (dos tuplas), el 2 ninguno (ninguna tupla, sin nulos) y el 3 uno.

Variantes:

Multivaluado compuesto (cada teléfono tiene un tipo: móvil, fijo, trabajo): la nueva relación incluye todos los componentes. La PK sigue siendo (num_socio, telefono); tipo es un atributo más.
Cardinalidad mínima 1 (todo socio debe tener al menos un teléfono): no se puede garantizar con claves; es una restricción perdida (sección 13).
Máximo pequeño y fijo (como mucho 2 teléfonos): a veces se diseña telefono1, telefono2 (N) en la propia relación. Es una desnormalización que complica las consultas (“¿quién tiene el teléfono X?” obliga a mirar dos columnas); úsala solo con una justificación clara.

⚠️ Importante: Nunca guardes un multivaluado como lista en una sola columna ('600111222, 963000111'): incumple la atomicidad de las relaciones (1FN) y hace imposible indexar, validar o buscar ese dato.

3.4. Atributos derivados

Un atributo derivado se calcula a partir de otros (la edad a partir de la fecha de nacimiento, el número de ejemplares de un libro contando sus ejemplares, el importe de una línea a partir de cantidad, precio y descuento). Opciones:

Opción	Cómo	Cuándo
No almacenarlo (por defecto)	Se calcula en las consultas o en una vista	Casi siempre; evita inconsistencias
Columna virtual	`GENERATED ALWAYS AS (expresión) VIRTUAL` en Oracle	Si la expresión usa solo columnas de la misma fila y es determinista
Almacenarlo	Columna normal, mantenida por la aplicación o por un disparador	Si calcularlo es muy costoso y se consulta mucho (desnormalización controlada)

💻 Ejemplo: En LINEAS_PEDIDO de EMPRESA el importe de una línea es cantidad * precio_unitario * (1 - descuento / 100). No se almacena; se calcula en las consultas. Podría definirse como columna virtual porque solo depende de columnas de la misma fila. En cambio, la edad de una persona no puede ser columna virtual en Oracle porque depende de la fecha actual (SYSDATE), que no es determinista: se calcula en una vista o en la consulta.

⚠️ Importante: No confundas un derivado con un dato histórico. LINEAS_PEDIDO.precio_unitario parece derivable de PRODUCTOS.precio, pero no lo es: guarda el precio en el momento de la venta, que no cambia aunque mañana el producto suba de precio. Ese atributo debe almacenarse siempre.

3.5. Atributos opcionales

Un atributo con cardinalidad (0,1) se transforma en un atributo que admite nulos. Si en una entidad hay muchos atributos opcionales que solo tienen valor para un grupo de ocurrencias (por ejemplo, comision, zona y objetivo_ventas, que solo tienen los vendedores), es una pista de que falta una especialización en el E/R (sección 11).

4. Transformación de relaciones 1:N

4.1. Regla general

📘 Concepto: Regla. Una relación binaria 1:N se transforma propagando la clave primaria de la entidad del lado 1 a la relación de la entidad del lado N, donde pasa a ser clave ajena. Los atributos de la relación también se propagan al lado N. No se crea ninguna relación nueva.

Con la notación del curso, el lado 1 es la entidad junto a la que está escrito el máximo 1: la clave ajena va a parar a la otra entidad.

💻 Ejemplo: Liga deportiva. Cada jugador pertenece a un único equipo; un equipo tiene de 0 a n jugadores. El dorsal del jugador se registra en la relación.

flowchart LR
    EQUIPO[EQUIPO] ---|"(1,1)"| pertenece{pertenece}
    pertenece ---|"(0,n)"| JUGADOR[JUGADOR]
    pertenece --- p_dorsal([dorsal])
    EQUIPO --- e_id([<u>equipo_id</u>])
    EQUIPO --- e_nom([nombre])
    JUGADOR --- j_id([<u>jugador_id</u>])
    JUGADOR --- j_nom([nombre])

(1,1) junto a EQUIPO: cada jugador pertenece a un equipo como mínimo y como máximo. (0,n) junto a JUGADOR: cada equipo tiene de 0 a n jugadores. Correspondencia 1:N.

EQUIPOS (equipo_id, nombre)

UK: nombre

JUGADORES (jugador_id, nombre, equipo_id, dorsal)

equipo_id → EQUIPOS(equipo_id)

EQUIPOS

equipo_id	nombre
1	Atlético Ribera
2	CD Montaña

JUGADORES

jugador_id	nombre	equipo_id	dorsal
10	Iker Sola	1	9
11	Dani Ortí	1	4
12	Pau Serra	2	1

4.2. La participación decide los nulos

Cardinalidad junto a la entidad del lado 1	Significado	Clave ajena en el lado N
(1,1)	Toda ocurrencia del lado N está relacionada obligatoriamente	`NOT NULL`
(0,1)	Puede haber ocurrencias del lado N sin relacionar	Admite nulos `(N)`

Si en el ejemplo los jugadores pudieran estar sin equipo (agentes libres), la cardinalidad junto a EQUIPO sería (0,1) y el esquema quedaría:

JUGADORES (jugador_id, nombre, equipo_id (N), dorsal (N))

equipo_id → EQUIPOS(equipo_id)

Cardinalidad junto a la entidad del lado N	Significado	¿Se refleja en el esquema?
(0,n)	Un equipo puede no tener jugadores	Sí, automáticamente
(1,n)	Todo equipo tiene al menos un jugador	No: restricción perdida (aserción, disparador o aplicación)

💡 Consejo: La cardinalidad mínima junto a la entidad del lado 1 se traduce en NOT NULL. La cardinalidad mínima junto a la entidad del lado N casi nunca se puede declarar. Memoriza esta asimetría: aparece en todos los exámenes.

4.3. Opción alternativa: relación intermedia

Una relación 1:N también puede transformarse como si fuera N:M, creando una relación nueva cuya clave primaria es solo la clave del lado N:

JUGADORES (jugador_id, nombre)

PERTENENCIAS (*jugador_id*, equipo_id, dorsal)

jugador_id → JUGADORES(jugador_id)
equipo_id → EQUIPOS(equipo_id)

Al ser jugador_id la PK, cada jugador aparece como mucho una vez: se conserva el 1:N. Esta opción es preferible cuando:

La participación del lado N es muy opcional (pocas ocurrencias relacionadas): evita una columna llena de nulos, y los atributos de la relación (dorsal) solo existen cuando hay relación.
La relación tiene muchos atributos propios.
Se prevé que en el futuro la relación pueda convertirse en N:M (por ejemplo, guardar el histórico de equipos de cada jugador): bastará con ampliar la PK.

En los demás casos, la propagación de clave es la opción preferida: menos tablas y menos combinaciones en las consultas.

5. Transformación de relaciones 1:1

En una relación 1:1 la clave puede propagarse en cualquiera de los dos sentidos, o incluso fusionarse las dos entidades en una sola relación. La decisión depende de las cardinalidades mínimas. Hay cuatro combinaciones, de las cuales dos son simétricas.

5.1. Caso (1,1) — (1,1): ambas obligatorias

Cada ocurrencia de A se relaciona con exactamente una de B y viceversa.

💻 Ejemplo: En una liga, cada equipo juega en un estadio propio y cada estadio pertenece a un único equipo.

flowchart LR
    EQUIPO[EQUIPO] ---|"(1,1)"| juega{juega_en}
    juega ---|"(1,1)"| ESTADIO[ESTADIO]
    EQUIPO --- e_id([<u>equipo_id</u>])
    EQUIPO --- e_nom([nombre])
    ESTADIO --- s_id([<u>estadio_id</u>])
    ESTADIO --- s_nom([nombre_estadio])
    ESTADIO --- s_afo([aforo])

Opción preferida: fusionar ambas entidades en una sola relación, eligiendo una de las claves como PK y la otra como clave alternativa:

EQUIPOS (equipo_id, nombre, estadio_id, nombre_estadio, aforo)

UK: nombre; UK: estadio_id

equipo_id	nombre	estadio_id	nombre_estadio	aforo
1	Atlético Ribera	501	Camp de la Ribera	8000
2	CD Montaña	502	Estadio Pico Alto	5500

Alternativa: dos relaciones, propagando la clave en cualquier sentido con NOT NULL y UNIQUE (por ejemplo, si las entidades tienen muchos atributos o se consultan por separado):

EQUIPOS (equipo_id, nombre, estadio_id)

estadio_id → ESTADIOS(estadio_id)
UK: estadio_id

ESTADIOS (estadio_id, nombre_estadio, aforo)

⚠️ Importante: Con dos relaciones, NOT NULL + UNIQUE en EQUIPOS.estadio_id garantiza que cada equipo tenga un estadio distinto, pero no que todo estadio tenga equipo. Esa mitad de la restricción se pierde; con la fusión no se pierde nada.

5.2. Caso (0,1) — (1,1): una obligatoria y otra opcional

💻 Ejemplo: Universidad. Cada departamento está dirigido por exactamente un profesor; un profesor dirige como mucho un departamento (la mayoría no dirige ninguno).

flowchart LR
    PROFESOR[PROFESOR] ---|"(1,1)"| dirige{dirige}
    dirige ---|"(0,1)"| DEPARTAMENTO[DEPARTAMENTO]
    dirige --- d_desde([fecha_inicio])
    PROFESOR --- p_id([<u>prof_id</u>])
    PROFESOR --- p_nom([nombre])
    DEPARTAMENTO --- d_id([<u>dep_id</u>])
    DEPARTAMENTO --- d_nom([nombre])

(1,1) junto a PROFESOR: cada departamento tiene exactamente un director. (0,1) junto a DEPARTAMENTO: cada profesor dirige 0 o 1 departamentos.

📘 Concepto: Regla. La clave se propaga hacia la entidad con participación obligatoria (aquella cuyas ocurrencias están todas relacionadas: aquí DEPARTAMENTO). La clave ajena es NOT NULL y UNIQUE. Así no aparece ningún nulo.

PROFESORES (prof_id, nombre)

DEPARTAMENTOS_UNI (dep_id, nombre, director_id, fecha_inicio)

director_id → PROFESORES(prof_id)
UK: director_id

dep_id	nombre	director_id	fecha_inicio
D1	Matemáticas	7	01/09/2024
D2	Informática	3	15/02/2025

Si la clave se hubiera propagado al revés (PROFESORES.dep_dirigido_id), la columna sería nula para casi todos los profesores y no se garantizaría que todo departamento tenga director.

El caso (1,1) — (0,1) es el mismo con los papeles intercambiados: la clave siempre va a la entidad cuyas ocurrencias participan todas.

5.3. Caso (0,1) — (0,1): ambas opcionales

💻 Ejemplo: Hospital. Un paciente ingresado ocupa una cama; una cama está ocupada por un paciente o libre; la mayoría de los pacientes de la base de datos no están ingresados ahora mismo.

flowchart LR
    PACIENTE[PACIENTE] ---|"(0,1)"| ocupa{ocupa}
    ocupa ---|"(0,1)"| CAMA[CAMA]
    PACIENTE --- pa_id([<u>nhc</u>])
    CAMA --- ca_id([<u>cama_id</u>])

Tres opciones:

Opción	Esquema	Valoración
a) Propagar a CAMA	CAMAS (cama_id, nhc (N)) con UK: nhc	Pocos nulos si casi todas las camas están ocupadas. Buena opción
b) Propagar a PACIENTE	PACIENTES (nhc, …, cama_id (N)) con UK: cama_id	Muchísimos nulos (casi ningún paciente está ingresado). Peor
c) Relación nueva	OCUPACIONES (cama_id, nhc) con UK: nhc	Ningún nulo; una tabla más. Recomendable si la relación tiene atributos (fecha de ocupación)

💡 Consejo: En el caso (0,1)-(0,1), propaga hacia el lado donde haya menos nulos (el que participa más), o crea una relación nueva. Recuerda que en Oracle una columna UNIQUE admite varias filas con NULL, así que la opción a) funciona aunque haya varias camas libres.

5.4. Resumen de las relaciones 1:1

Cardinalidades (junto a A, junto a B)	Transformación recomendada
(1,1), (1,1)	Fusionar A y B en una relación (la otra clave queda como `UNIQUE`)
(1,1), (0,1)	Propagar la clave de A a B, `NOT NULL UNIQUE` (todo B tiene su A)
(0,1), (1,1)	Propagar la clave de B a A, `NOT NULL UNIQUE` (todo A tiene su B)
(0,1), (0,1)	Propagar hacia el lado con menos nulos, `UNIQUE` y admite nulos, o crear relación nueva

⚠️ Importante: Lo que diferencia una 1:1 de una 1:N en el esquema relacional es la restricción UNIQUE sobre la clave ajena. Si la olvidas, el esquema permitirá que un profesor dirija dos departamentos.

6. Transformación de relaciones N:M

📘 Concepto: Regla. Una relación N:M se transforma en una relación nueva cuyos atributos son las claves primarias de las entidades participantes (como claves ajenas) más los atributos propios de la relación. La clave primaria de la nueva relación es, en principio, la combinación de las claves ajenas.

💻 Ejemplo: Universidad. Un alumno se matricula en varias asignaturas y una asignatura tiene muchos alumnos. De cada matrícula se guarda la nota (que se conoce al final del curso).

flowchart LR
    ALUMNO[ALUMNO] ---|"(0,n)"| matricula{se_matricula}
    matricula ---|"(1,n)"| ASIGNATURA[ASIGNATURA]
    matricula --- m_nota(["nota (0,1)"])
    ALUMNO --- a_id([<u>expediente</u>])
    ALUMNO --- a_nom([nombre])
    ASIGNATURA --- s_id([<u>cod_asig</u>])
    ASIGNATURA --- s_nom([nombre])
    ASIGNATURA --- s_cred([creditos])

(1,n) junto a ASIGNATURA: cada alumno está matriculado en al menos una. (0,n) junto a ALUMNO: una asignatura puede no tener alumnos (optativa sin demanda).

ALUMNOS (expediente, nombre)

ASIGNATURAS (cod_asig, nombre, creditos)

MATRICULAS (*expediente*, *cod_asig*, nota (N))

expediente → ALUMNOS(expediente)
cod_asig → ASIGNATURAS(cod_asig)
CHECK: nota BETWEEN 0 AND 10

expediente	cod_asig	nota
A001	BD	8.5
A001	PROG	6.0
A002	BD	NULL
A003	SIS	7.25

6.1. Cuando la clave combinada no basta

Si una misma pareja puede relacionarse varias veces (un alumno que suspende y se vuelve a matricular de BD al curso siguiente), la combinación (expediente, cod_asig) ya no es única. Hay que añadir a la clave primaria el atributo que distingue las repeticiones, normalmente uno temporal:

MATRICULAS (*expediente*, *cod_asig*, curso_academico, nota (N))

expediente	cod_asig	curso_academico	nota
A001	BD	2024-25	3.5
A001	BD	2025-26	8.5

💡 Consejo: Pregunta siempre: “¿puede repetirse la misma pareja?”. Si la respuesta es sí, la PK necesita un atributo más (fecha, curso, número de orden). Es el error más común al transformar relaciones N:M de datos históricos (módulo 13).

6.2. Restricciones de la N:M

Las cardinalidades mínimas 0 se cumplen solas.
Una cardinalidad mínima 1 (cada alumno matriculado en al menos una asignatura) se pierde: nada impide insertar un alumno sin matrículas.
Una cardinalidad máxima concreta (como mucho 10 asignaturas por alumno) se pierde.
Acciones referenciales típicas: cascada si la relación no tiene sentido sin las entidades (ON DELETE CASCADE hacia el alumno si se permite borrar alumnos), restringida si es un histórico que debe conservarse.

7. Atributos de las relaciones

Resumen de adónde va un atributo propio de una relación según su tipo:

Tipo de relación	Destino del atributo	Ejemplo
1:1 con propagación	La relación que recibe la clave ajena	`fecha_inicio` en `DEPARTAMENTOS_UNI`
1:1 fusionada	La relación fusionada	—
1:N con propagación	La relación del lado N	`dorsal` en `JUGADORES`
1:N con relación intermedia	La relación intermedia	`dorsal` en `PERTENENCIAS`
N:M, ternaria, n-aria	La relación nueva	`nota` en `MATRICULAS`

Si el atributo de la relación es multivaluado (por ejemplo, las fechas de los exámenes de cada matrícula), se crea otra relación más cuya clave es la clave de la relación de origen más el atributo.

8. Relaciones reflexivas

Una relación reflexiva (o recursiva) relaciona una entidad consigo misma, con dos papeles distintos. Se transforma con las mismas reglas que una binaria normal; la diferencia es que la clave ajena referencia a la misma relación y debe llevar un nombre que indique el papel.

8.1. Reflexiva 1:N

💻 Ejemplo: EMPRESA. Cada empleado tiene como mucho un jefe; un jefe dirige a muchos empleados.

flowchart LR
    EMPLEADO[EMPLEADO] ---|"jefe (0,1)"| dirige{dirige}
    dirige ---|"subordinado (0,n)"| EMPLEADO
    EMPLEADO --- e_id([<u>emp_id</u>])
    EMPLEADO --- e_nom([nombre])

EMPLEADOS (emp_id, nombre, …, jefe_id (N))

jefe_id → EMPLEADOS(emp_id)
CHECK: jefe_id <> emp_id

emp_id	nombre	jefe_id
100	Ana	NULL
102	Marta	100
120	Pablo	102
121	Raquel	120

La clave ajena debe admitir nulos si hay un “raíz” (la directora general no tiene jefe). Si fuese NOT NULL, nunca podrías insertar el primer empleado. El CHECK evita que alguien sea su propio jefe; los ciclos más largos (A jefe de B y B jefe de A) no se pueden impedir con un CHECK y requieren un disparador.

8.2. Reflexiva 1:1

💻 Ejemplo: Red social. Un usuario puede indicar su pareja dentro de la red (como mucho una).

flowchart LR
    USUARIO[USUARIO] ---|"(0,1)"| pareja{es_pareja_de}
    pareja ---|"(0,1)"| USUARIO
    USUARIO --- u_id([<u>usuario_id</u>])
    USUARIO --- u_nick([nick])

USUARIOS_RED (usuario_id, nick, pareja_id (N))

pareja_id → USUARIOS_RED(usuario_id)
UK: pareja_id
CHECK: pareja_id <> usuario_id

⚠️ Importante: Si la relación es simétrica (si 1 es pareja de 2, 2 es pareja de 1), esta solución guarda el dato dos veces y se puede volver inconsistente (1 → 2 pero 2 → 3). Alternativas: guardarlo solo una vez con la convención usuario_id < pareja_id, o mantener la simetría con un disparador.

8.3. Reflexiva N:M

💻 Ejemplo: Red social. Un usuario sigue a muchos usuarios y es seguido por muchos. Se guarda la fecha en la que empezó a seguirlo.

flowchart LR
    USUARIO[USUARIO] ---|"seguidor (0,n)"| sigue{sigue_a}
    sigue ---|"seguido (0,n)"| USUARIO
    sigue --- s_fecha([fecha])
    USUARIO --- u_id([<u>usuario_id</u>])

SEGUIMIENTOS (*seguidor_id*, *seguido_id*, fecha)

seguidor_id → USUARIOS_RED(usuario_id) [ON DELETE CASCADE]
seguido_id → USUARIOS_RED(usuario_id) [ON DELETE CASCADE]
CHECK: seguidor_id <> seguido_id

seguidor_id	seguido_id	fecha
1	3	02/02/2026
2	3	14/02/2026
3	1	15/02/2026

Aquí la relación no es simétrica (1 sigue a 3 no implica lo contrario), así que las dos tuplas (1,3) y (3,1) son hechos distintos. Otro ejemplo clásico de reflexiva N:M: las asignaturas prerrequisito de otras asignaturas.

9. Relaciones ternarias y n-arias

9.1. Regla general

📘 Concepto: Regla. Una relación de grado 3 o superior se transforma siempre en una relación nueva que contiene las claves primarias de todas las entidades participantes (como claves ajenas) y los atributos de la relación. La clave primaria se calcula a partir de las cardinalidades máximas.

Recuerda cómo se leen las cardinalidades en una ternaria (módulo 06): la pareja junto a la entidad X indica con cuántas ocurrencias de X se relaciona una combinación de ocurrencias de las otras dos entidades.

Si el máximo junto a X es n, la combinación de las otras dos no determina X: la clave de X debe estar en la PK.
Si el máximo junto a X es 1, la combinación de las otras dos determina X: la clave de X no hace falta en la PK (las otras dos ya identifican la tupla).

Tipo (máximos)	Clave primaria de la relación nueva	Claves candidatas
N:M:P (n, n, n)	(a, b, c)	Una
1:N:M (1 junto a A)	(b, c)	Una
1:1:N (1 junto a A y junto a B)	(b, c) o (a, c)	Dos: elige una como PK y la otra `UNIQUE`
1:1:1	(a, b), (a, c) o (b, c)	Tres

9.2. Ternaria N:M:P

💻 Ejemplo: Hospital. Un médico prescribe medicamentos a pacientes. Cualquier médico puede prescribir cualquier medicamento a cualquier paciente, y se guarda la dosis.

flowchart TB
    MEDICO[MEDICO] ---|"(1,n)"| prescribe{prescribe}
    prescribe ---|"(0,n)"| PACIENTE[PACIENTE]
    prescribe ---|"(1,n)"| MEDICAMENTO[MEDICAMENTO]
    prescribe --- pr_dosis([dosis])

PRESCRIPCIONES (*num_colegiado*, *nhc*, *cod_medicamento*, dosis)

num_colegiado → MEDICOS(num_colegiado)
nhc → PACIENTES(nhc)
cod_medicamento → MEDICAMENTOS(cod_medicamento)

num_colegiado	nhc	cod_medicamento	dosis
4601	50231	IBU600	1 cada 8 h
4601	50231	OME20	1 al día
4602	50231	IBU600	1 cada 12 h

💡 Consejo: Como en la N:M, si el mismo médico puede prescribir el mismo medicamento al mismo paciente en ocasiones distintas, añade fecha a la clave primaria.

9.3. Ternaria 1:N:M

💻 Ejemplo: Liga deportiva. En cada temporada, un jugador juega en un único equipo; un equipo tiene muchos jugadores cada temporada; un jugador juega muchas temporadas.

flowchart TB
    JUGADOR[JUGADOR] ---|"(1,n)"| juega{juega}
    juega ---|"(1,1)"| EQUIPO[EQUIPO]
    juega ---|"(1,n)"| TEMPORADA[TEMPORADA]
    juega --- jg_dorsal([dorsal])

(1,1) junto a EQUIPO: para una pareja (jugador, temporada) hay exactamente un equipo. Por tanto (jugador, temporada) determina el equipo y la PK es (jugador_id, temporada_id):

PLANTILLAS (*jugador_id*, *temporada_id*, equipo_id, dorsal)

jugador_id → JUGADORES(jugador_id)
temporada_id → TEMPORADAS(temporada_id)
equipo_id → EQUIPOS(equipo_id)

jugador_id	temporada_id	equipo_id	dorsal
10	2024-25	1	9
10	2025-26	2	11
11	2025-26	2	4

Si hubieras puesto (jugador_id, temporada_id, equipo_id) como PK, el esquema permitiría que el jugador 10 jugara en dos equipos la misma temporada: la PK debe ser mínima para que recoja la restricción.

9.4. Ternaria 1:1:N

💻 Ejemplo: Liga deportiva. En cada temporada, cada equipo tiene un único entrenador y cada entrenador entrena a un único equipo.

flowchart TB
    ENTRENADOR[ENTRENADOR] ---|"(1,1)"| entrena{entrena}
    entrena ---|"(0,1)"| EQUIPO[EQUIPO]
    entrena ---|"(1,n)"| TEMPORADA[TEMPORADA]

Para un par (equipo, temporada) hay un entrenador; para un par (entrenador, temporada) hay un equipo. Hay dos claves candidatas: (equipo_id, temporada_id) y (entrenador_id, temporada_id).

BANQUILLOS (*equipo_id*, *temporada_id*, entrenador_id)

equipo_id → EQUIPOS(equipo_id)
temporada_id → TEMPORADAS(temporada_id)
entrenador_id → ENTRENADORES(entrenador_id)
UK: (entrenador_id, temporada_id)

equipo_id	temporada_id	entrenador_id
1	2025-26	70
2	2025-26	71
1	2026-27	71

⚠️ Importante: No olvides la clave alternativa. Sin UK: (entrenador_id, temporada_id), nada impediría que el entrenador 71 dirigiera a los equipos 1 y 2 en la temporada 2026-27.

9.5. Relaciones n-arias

El razonamiento se generaliza a cualquier grado: la clave primaria está formada por las claves de las entidades cuyo máximo es n; si hay entidades con máximo 1, sus claves son atributos no clave (o forman parte de claves candidatas alternativas). Si todas las entidades tienen máximo 1, cada combinación de n−1 claves es clave candidata.

💻 Ejemplo: Agencia de viajes, relación cuaternaria reserva entre CLIENTE, VIAJE, FECHA_SALIDA y HABITACION_HOTEL, donde para un cliente, un viaje y una fecha de salida se asigna una única habitación (máximo 1 junto a HABITACION_HOTEL): PK = (cod_cliente, cod_viaje, fecha_salida), y la habitación es una clave ajena no clave.

💡 Consejo: Antes de transformar una ternaria, comprueba en el E/R (módulo 13) que es realmente ternaria y no la suma de dos binarias. Una ternaria mal modelada produce una relación con redundancias que luego la normalización (4FN, 5FN) tendrá que descomponer.

10. Entidades débiles

10.1. Débil en existencia

📘 Concepto: Una entidad débil en existencia tiene identificador propio, pero sus ocurrencias no pueden existir sin la ocurrencia de la entidad fuerte con la que se relacionan (relación 1:N con participación obligatoria).

Regla. Se transforma como una entidad normal más una relación 1:N: la clave de la fuerte se propaga como clave ajena NOT NULL, y normalmente con ON DELETE CASCADE (si desaparece la fuerte, desaparecen sus débiles).

💻 Ejemplo: Taller mecánico. Cada reparación tiene un número propio de orden de trabajo, pero no existe sin el vehículo reparado.

flowchart LR
    classDef debil stroke-width:4px
    VEHICULO[VEHICULO] ---|"(1,1)"| sufre{sufre}:::debil
    sufre ---|"(0,n)"| REPARACION[REPARACION]:::debil
    VEHICULO --- v_mat([<u>matricula</u>])
    VEHICULO --- v_mod([modelo])
    REPARACION --- r_id([<u>num_orden</u>])
    REPARACION --- r_fec([fecha_entrada])
    REPARACION --- r_imp([importe])

VEHICULOS (matricula, modelo)

REPARACIONES (num_orden, matricula, fecha_entrada, importe)

matricula → VEHICULOS(matricula) [ON DELETE CASCADE]

10.2. Débil en identificación

📘 Concepto: Una entidad débil en identificación no tiene identificador propio suficiente: se identifica por la clave de la entidad fuerte más un identificador parcial (discriminador). Toda débil en identificación lo es también en existencia.

Regla. Se crea una relación cuya clave primaria es la clave primaria de la entidad fuerte + el identificador parcial. La parte heredada es también clave ajena hacia la fuerte, casi siempre con ON DELETE CASCADE.

💻 Ejemplo: Biblioteca. Cada libro (ISBN) tiene varios ejemplares físicos numerados 1, 2, 3… dentro de ese libro.

flowchart LR
    classDef debil stroke-width:4px
    LIBRO[LIBRO] ---|"(1,1)"| tiene{tiene}:::debil
    tiene ---|"(1,n)"| EJEMPLAR[EJEMPLAR]:::debil
    LIBRO --- l_isbn([<u>isbn</u>])
    LIBRO --- l_tit([titulo])
    EJEMPLAR --- ej_num([<u>num_ejemplar</u> parcial])
    EJEMPLAR --- ej_est([estado])

LIBROS (isbn, titulo)

EJEMPLARES (*isbn*, num_ejemplar, estado)

isbn → LIBROS(isbn) [ON DELETE CASCADE]
CHECK: estado IN (‘DISPONIBLE’, ‘PRESTADO’, ‘BAJA’)

isbn	num_ejemplar	estado
9788420412146	1	DISPONIBLE
9788420412146	2	PRESTADO
9788497592208	1	DISPONIBLE

El ejemplar 1 existe para dos libros distintos: num_ejemplar solo identifica dentro de cada libro. Es exactamente el caso de LINEAS_PEDIDO en EMPRESA: (*pedido_id*, num_linea).

10.3. Cadenas de debilidad

Si una entidad débil depende a su vez de otra débil, la clave primaria se acumula a lo largo de la cadena.

💻 Ejemplo: Universidad: EDIFICIO (cod_edificio) → AULA (numero dentro del edificio) → PUESTO (numero de puesto dentro del aula).

EDIFICIOS (cod_edificio, nombre)

AULAS (*cod_edificio*, num_aula, capacidad)

cod_edificio → EDIFICIOS(cod_edificio) [ON DELETE CASCADE]

PUESTOS (*cod_edificio*, *num_aula*, num_puesto, tiene_ordenador)

(cod_edificio, num_aula) → AULAS(cod_edificio, num_aula) [ON DELETE CASCADE]

💡 Consejo: Cuando la clave acumulada crece mucho (tres o más atributos que se propagan a otras tablas), muchos diseñadores introducen una clave subrogada en la débil (puesto_id) y dejan la clave natural como UNIQUE. Es una decisión de diseño físico perfectamente válida, siempre que no se pierda la restricción de unicidad.

11. Generalizaciones (jerarquías)

11.1. El ejemplo

💻 Ejemplo: Plataforma de streaming. Todo contenido es una película o una serie, nunca ambas (jerarquía total y exclusiva). Todos los contenidos tienen título, año y clasificación por edades; las películas tienen duración y director; las series, número de temporadas y si siguen en emisión. Los usuarios valoran contenidos (de cualquier tipo).

flowchart TB
    CONTENIDO[CONTENIDO] --- j{{"es un<br/>(total, exclusiva)"}}
    j --- PELICULA[PELICULA]
    j --- SERIE[SERIE]
    CONTENIDO --- c_id([<u>contenido_id</u>])
    CONTENIDO --- c_tit([titulo])
    CONTENIDO --- c_anio([anio])
    CONTENIDO --- c_edad([clasificacion_edad])
    PELICULA --- p_dur([duracion_min])
    PELICULA --- p_dir([director])
    SERIE --- s_temp([num_temporadas])
    SERIE --- s_emi([en_emision])

Hay tres opciones de transformación.

11.2. Opción 1: una sola relación para toda la jerarquía

Se crea una relación con los atributos del supertipo, los de todos los subtipos (que admiten nulos) y un atributo discriminante que indica el subtipo de cada tupla.

CONTENIDOS (contenido_id, titulo, anio, clasificacion_edad, tipo, duracion_min (N), director (N), num_temporadas (N), en_emision (N))

CHECK: tipo IN (‘PELICULA’, ‘SERIE’)
CHECK: (tipo = ‘PELICULA’ AND duracion_min IS NOT NULL AND num_temporadas IS NULL AND en_emision IS NULL) OR (tipo = ‘SERIE’ AND num_temporadas IS NOT NULL AND duracion_min IS NULL AND director IS NULL)

contenido_id	titulo	anio	clasificacion_edad	tipo	duracion_min	director	num_temporadas	en_emision
1	El faro	2024	12	PELICULA	112	L. Ibarra	NULL	NULL
2	Código Delta	2023	16	SERIE	NULL	NULL	3	S
3	La huerta	2025	7	PELICULA	95	M. Puig	NULL	NULL

Ventajas: una sola tabla, consultas sencillas y rápidas (sin combinaciones), la jerarquía total se garantiza con tipo NOT NULL, la exclusiva con un único discriminante.
Inconvenientes: muchos nulos; los atributos obligatorios de cada subtipo no se pueden declarar NOT NULL (hace falta el CHECK); las relaciones en las que participa solo un subtipo no se pueden restringir con una FK (cualquier contenido sería referenciable).
Variantes: si la jerarquía es parcial, el discriminante admite un valor más (o nulo) para las ocurrencias que no pertenecen a ningún subtipo. Si es solapada, en lugar de un único tipo se usan indicadores (es_pelicula, es_serie, con CHECK en ('S','N')).

11.3. Opción 2: una relación por subtipo

Se crea una relación para cada subtipo, que incluye los atributos heredados del supertipo y los propios. El supertipo no tiene relación.

PELICULAS (contenido_id, titulo, anio, clasificacion_edad, duracion_min, director)

SERIES (contenido_id, titulo, anio, clasificacion_edad, num_temporadas, en_emision)

Ventajas: sin nulos; los atributos de cada subtipo pueden ser NOT NULL; las consultas sobre un solo subtipo son directas.
Inconvenientes: solo sirve para jerarquías totales (una ocurrencia que no sea de ningún subtipo no tiene dónde guardarse); con jerarquías solapadas los datos comunes se duplican; nada impide repetir el mismo contenido_id en las dos tablas; y lo más grave: una relación con el supertipo (VALORACIONES de cualquier contenido) no puede tener una única clave ajena, porque no hay una tabla de contenidos.

11.4. Opción 3: una relación para el supertipo y otra para cada subtipo

Se crea una relación para el supertipo (con los atributos comunes y, opcionalmente, el discriminante) y una para cada subtipo con sus atributos propios. La clave primaria de cada subtipo es la misma que la del supertipo y es a la vez clave ajena hacia él.

CONTENIDOS (contenido_id, titulo, anio, clasificacion_edad, tipo)

CHECK: tipo IN (‘PELICULA’, ‘SERIE’)

PELICULAS (*contenido_id*, duracion_min, director)

contenido_id → CONTENIDOS(contenido_id) [ON DELETE CASCADE]

SERIES (*contenido_id*, num_temporadas, en_emision)

contenido_id → CONTENIDOS(contenido_id) [ON DELETE CASCADE]

CONTENIDOS

contenido_id	titulo	anio	clasificacion_edad	tipo
1	El faro	2024	12	PELICULA
2	Código Delta	2023	16	SERIE
3	La huerta	2025	7	PELICULA

PELICULAS

contenido_id	duracion_min	director
1	112	L. Ibarra
3	95	M. Puig

SERIES

contenido_id	num_temporadas	en_emision
2	3	S

Ventajas: es la transformación más fiel: sin nulos, cada subtipo con sus NOT NULL, y tanto el supertipo como cada subtipo pueden participar en relaciones con su propia FK (VALORACIONES.contenido_id → CONTENIDOS, EPISODIOS.contenido_id → SERIES). Sirve para cualquier cobertura.
Inconvenientes: más tablas y más combinaciones para reconstruir una ocurrencia completa. Además se pierden la cobertura total (puede haber un contenido sin fila en ningún subtipo) y la exclusividad (podría haber una fila en PELICULAS y en SERIES para el mismo contenido).

💡 Consejo: Truco profesional para garantizar la exclusividad en la opción 3 sin disparadores: añade tipo también a cada subtipo con un CHECK de valor fijo, declara UNIQUE (contenido_id, tipo) en el supertipo y haz que la clave ajena del subtipo sea compuesta:

PELICULAS (*contenido_id*, tipo, duracion_min, director) con CHECK: tipo = ‘PELICULA’ y (contenido_id, tipo) → CONTENIDOS(contenido_id, tipo).

Así, el contenido 2 (tipo SERIE) no puede tener fila en PELICULAS. La cobertura total, en cambio, solo se puede garantizar con una aserción o un disparador.

11.5. ¿Qué opción elegir?

Criterio	Opción 1: una relación	Opción 2: solo subtipos	Opción 3: supertipo + subtipos
Cobertura total	Sí (discriminante `NOT NULL`)	Sí, es la única que admite	Sí (cobertura con aserción/disparador)
Cobertura parcial	Sí (discriminante con valor “ninguno”)	No	Sí
Exclusiva	Sí (un discriminante)	Sí (riesgo de clave repetida)	Sí (con el truco del tipo)
Solapada	Sí (indicadores S/N)	Mal (duplica datos comunes)	Sí, de forma natural
Subtipos con pocos atributos propios	Recomendada	Posible	Posible
Subtipos con muchos atributos propios	Muchos nulos	Posible	Recomendada
Subtipos con relaciones propias	No se pueden restringir	Posible	Recomendada
Supertipo con relaciones propias	Sí	No (no hay tabla del supertipo)	Sí
Consultas sobre todo el conjunto	Muy rápidas	Requieren `UNION`	Requieren combinación
Consultas sobre un subtipo	Requieren filtro	Muy rápidas	Requieren combinación

💡 Consejo: Regla práctica: opción 1 si los subtipos casi no se diferencian (un par de atributos); opción 3 si los subtipos tienen muchos atributos o relaciones propias, o si la jerarquía es parcial o solapada; opción 2 solo si la jerarquía es total y exclusiva, el supertipo no participa en ninguna relación y casi siempre se consulta cada subtipo por separado.

11.6. Jerarquías de varios niveles

Con varios niveles se aplica la regla nivel a nivel, y se pueden combinar opciones: por ejemplo, la opción 3 entre PERSONA y sus subtipos EMPLEADO y CLIENTE, y la opción 1 dentro de EMPLEADO para los subtipos VENDEDOR y TÉCNICO (que solo se diferencian en un par de atributos). Lo importante es que la clave del supertipo raíz se mantiene como clave de todos los niveles.

12. Agregaciones y categorías

12.1. Agregaciones

📘 Concepto: Una agregación trata una relación (con sus entidades) como si fuera una entidad de nivel superior, para que pueda participar en otra relación. Regla. La relación agregada se transforma según su tipo (normalmente N:M, en una relación nueva con su clave primaria) y esa clave primaria se usa como clave ajena en la transformación de la relación externa.

💻 Ejemplo: Consultora. Los consultores participan en proyectos (N:M). En cada participación se usan licencias de software (N:M entre la participación y LICENCIA): no basta con saber qué licencias usa un consultor, ni cuáles usa un proyecto, sino cuáles usa ese consultor en ese proyecto.

flowchart LR
    subgraph AG["PARTICIPACION"]
        CONSULTOR[CONSULTOR] ---|"(1,n)"| participa{participa}
        participa ---|"(1,n)"| PROYECTO[PROYECTO]
    end
    AG ---|"(0,n)"| usa{usa}
    usa ---|"(0,n)"| LICENCIA[LICENCIA]

CONSULTORES (consultor_id, nombre)

PROYECTOS_CONS (proyecto_id, nombre)

PARTICIPACIONES (*consultor_id*, *proyecto_id*, rol)

consultor_id → CONSULTORES(consultor_id)
proyecto_id → PROYECTOS_CONS(proyecto_id)

LICENCIAS (licencia_id, producto)

USOS_LICENCIA (*consultor_id*, *proyecto_id*, *licencia_id*)

(consultor_id, proyecto_id) → PARTICIPACIONES(consultor_id, proyecto_id) [ON DELETE CASCADE]
licencia_id → LICENCIAS(licencia_id)

consultor_id	proyecto_id	licencia_id
1	P1	MATLAB-07
1	P1	AUTOCAD-02
2	P1	MATLAB-07

⚠️ Importante: La clave ajena de USOS_LICENCIA hacia la agregación es compuesta y apunta a PARTICIPACIONES, no a CONSULTORES y PROYECTOS_CONS por separado. Así se garantiza que solo se registran licencias de participaciones que existen: el consultor 3 no puede usar licencias en el proyecto P1 si no participa en él. Esa es la diferencia con una ternaria.

12.2. Categorías (tipos unión)

📘 Concepto: Una categoría (o tipo unión) es un subconjunto de la unión de varias entidades distintas, que pueden tener claves diferentes. Por ejemplo, el PROPIETARIO de un vehículo puede ser un PARTICULAR (identificado por DNI) o una EMPRESA (identificada por CIF).

flowchart TB
    PARTICULAR[PARTICULAR] --- u{{"U<br/>(categoría)"}}
    EMPRESA_C[EMPRESA] --- u
    u --- PROPIETARIO[PROPIETARIO]
    PROPIETARIO ---|"(1,1)"| posee{posee}
    posee ---|"(0,n)"| VEHICULO[VEHICULO]

Opción A: clave subrogada para la categoría. Se crea una relación para la categoría con una clave artificial, y cada superclase la referencia:

PROPIETARIOS (propietario_id, tipo)

CHECK: tipo IN (‘PARTICULAR’, ‘EMPRESA’)

PARTICULARES (dni, nombre, propietario_id (N))

propietario_id → PROPIETARIOS(propietario_id)
UK: propietario_id

EMPRESAS_TALLER (cif, razon_social, propietario_id (N))

propietario_id → PROPIETARIOS(propietario_id)
UK: propietario_id

VEHICULOS (matricula, modelo, propietario_id)

propietario_id → PROPIETARIOS(propietario_id)

Opción B: arco exclusivo. Si la categoría no tiene atributos ni más relaciones que la de VEHICULO, se puede omitir y poner dos claves ajenas opcionales en VEHICULOS, con un CHECK que obligue a que exactamente una tenga valor:

VEHICULOS (matricula, modelo, dni_propietario (N), cif_propietario (N))

dni_propietario → PARTICULARES(dni)
cif_propietario → EMPRESAS_TALLER(cif)
CHECK: (dni_propietario IS NOT NULL AND cif_propietario IS NULL) OR (dni_propietario IS NULL AND cif_propietario IS NOT NULL)

13. Restricciones que se pierden y cómo recuperarlas

Restricción del E/R	¿La recoge el esquema con PK/FK?	Cómo recuperarla
Identificador alternativo	No por sí solo	`UNIQUE` (+ `NOT NULL`)
Atributo obligatorio	No por sí solo	`NOT NULL`
Dominio de un atributo	Parcialmente (tipo de datos)	`CHECK`, dominio SQL (desde 23ai)
Mínimo 1 junto al lado 1 de una 1:N	Sí, con la FK	`NOT NULL` en la FK
Máximo 1 en ambos lados (1:1)	No por sí solo	`UNIQUE` en la FK
Mínimo 1 junto al lado N de una 1:N (todo pedido tiene al menos una línea)	No	Aserción (Oracle 26ai), disparador o control en la aplicación
Mínimo 1 en una N:M	No	Aserción, disparador o aplicación
Máximo concreto (como mucho 3 préstamos a la vez)	No	Aserción, disparador o aplicación
Atributo derivado	—	Vista, columna virtual o disparador si se almacena
Reflexiva irreflexiva (nadie es su propio jefe)	No	`CHECK (jefe_id <> emp_id)`
Ausencia de ciclos en una jerarquía reflexiva	No	Disparador
Exclusividad entre relaciones (arco)	No	`CHECK` sobre los nulos de las FK
Cobertura total (opción 3)	No	Aserción o disparador
Exclusividad (opción 3)	No	Discriminante + FK compuesta + `CHECK` (11.4)
Restricciones de inclusión y exclusión entre relaciones (módulo 13)	No	Aserción o disparador
Restricciones entre atributos de una misma tupla	No	`CHECK` (por ejemplo, `fecha_fin >= fecha_inicio`)

⚠️ Importante: En este curso no se escribe código de disparadores (son PL/SQL). Cuando una restricción solo pueda garantizarse con uno, documéntala en el diseño (“RI-3: todo pedido debe tener al menos una línea; se implementará con un disparador”) y consulta el Curso de Oracle PL/SQL para implementarla.

💡 Consejo: Para el mínimo 1 del lado N existe un recurso habitual sin disparadores: insertar el padre y sus primeras hijas en la misma transacción desde la aplicación, y no ofrecer ninguna operación que deje un padre sin hijas. Es menos robusto que una aserción, pero muy común.

14. Caso completo: el esquema EMPRESA

En este caso se recorre el camino inverso al que siguió el autor del curso: partir de los requisitos, construir el E/R y transformarlo hasta obtener exactamente las 9 tablas de ../recursos/empresa_crear.sql.

14.1. Requisitos

La empresa se organiza en departamentos (código, nombre único, localidad y presupuesto opcionales).
Cada empleado (código, nombre, apellidos, email único, teléfono opcional, fecha de alta, oficio, salario y comisión opcional) pertenece como mucho a un departamento (los becarios pueden no tener). Un departamento puede no tener empleados.
Cada empleado tiene como mucho un jefe, que es otro empleado.
Cada cliente (código, nombre, NIF único, email, teléfono, ciudad y provincia opcionales, fecha de alta y límite de crédito) puede tener asignado un empleado comercial.
Los productos (código, nombre, descripción opcional, precio, stock, fecha de alta y si está descatalogado) pertenecen a una única categoría. Las categorías (código, nombre único) se organizan en un árbol: cada una tiene como mucho una categoría padre.
Un cliente hace pedidos (código, fecha, fecha de envío opcional y estado). Cada pedido es de un cliente y puede estar gestionado por un empleado (los pedidos online no).
Cada pedido tiene una o varias líneas, numeradas 1, 2, 3… dentro del pedido; cada línea es de un producto, con cantidad, precio unitario en el momento de la venta y porcentaje de descuento.
Los departamentos organizan proyectos (código, nombre único, fechas de inicio y fin, presupuesto). Un proyecto pertenece como mucho a un departamento.
Los empleados se asignan a proyectos: un empleado puede estar en varios proyectos y un proyecto tener varios empleados. De cada asignación se guarda la fecha, las horas y la función.

14.2. Diagrama E/R

Para que sea legible, el diagrama muestra entidades y relaciones; los atributos se detallan en la tabla siguiente.

flowchart TB
    classDef debil stroke-width:4px
    DEPARTAMENTO[DEPARTAMENTO]
    EMPLEADO[EMPLEADO]
    CLIENTE[CLIENTE]
    PEDIDO[PEDIDO]
    LINEA[LÍNEA DE PEDIDO]:::debil
    PRODUCTO[PRODUCTO]
    CATEGORIA[CATEGORÍA]
    PROYECTO[PROYECTO]

    DEPARTAMENTO ---|"(0,1)"| pertenece{pertenece}
    pertenece ---|"(0,n)"| EMPLEADO
    EMPLEADO ---|"jefe (0,1)"| dirige{dirige}
    dirige ---|"subordinado (0,n)"| EMPLEADO
    EMPLEADO ---|"(0,1)"| atiende{atiende}
    atiende ---|"(0,n)"| CLIENTE
    CLIENTE ---|"(1,1)"| realiza{realiza}
    realiza ---|"(0,n)"| PEDIDO
    EMPLEADO ---|"(0,1)"| gestiona{gestiona}
    gestiona ---|"(0,n)"| PEDIDO
    PEDIDO ---|"(1,1)"| contiene{contiene}:::debil
    contiene ---|"(1,n)"| LINEA
    PRODUCTO ---|"(1,1)"| referencia{referencia}
    referencia ---|"(0,n)"| LINEA
    CATEGORIA ---|"(1,1)"| clasifica{clasifica}
    clasifica ---|"(0,n)"| PRODUCTO
    CATEGORIA ---|"padre (0,1)"| subcat{es_subcategoria}
    subcat ---|"hija (0,n)"| CATEGORIA
    DEPARTAMENTO ---|"(0,1)"| organiza{organiza}
    organiza ---|"(0,n)"| PROYECTO
    EMPLEADO ---|"(0,n)"| asignado{asignado}
    asignado ---|"(0,n)"| PROYECTO
    asignado --- as_fecha([fecha_asignacion])
    asignado --- as_horas(["horas (0,1)"])
    asignado --- as_func(["funcion (0,1)"])

Entidad	Atributos (identificador subrayado)
DEPARTAMENTO	dept_id, nombre (identificador alternativo), localidad (0,1), presupuesto (0,1)
EMPLEADO	emp_id, nombre, apellidos, email (identificador alternativo), telefono (0,1), fecha_alta, oficio, salario, comision (0,1)
CLIENTE	cliente_id, nombre, nif (identificador alternativo), email (0,1), telefono (0,1), ciudad (0,1), provincia (0,1), fecha_alta, limite_credito
CATEGORÍA	categoria_id, nombre (identificador alternativo)
PRODUCTO	producto_id, nombre, descripcion (0,1), precio, stock, fecha_alta (0,1), descatalogado
PEDIDO	pedido_id, fecha_pedido, fecha_envio (0,1), estado
LÍNEA DE PEDIDO (débil)	num_linea (parcial), cantidad, precio_unitario, descuento
PROYECTO	proyecto_id, nombre (identificador alternativo), fecha_inicio, fecha_fin (0,1), presupuesto (0,1)

14.3. Transformación paso a paso

Paso	Elemento E/R	Regla aplicada	Resultado
1	Entidades fuertes DEPARTAMENTO, EMPLEADO, CLIENTE, CATEGORÍA, PRODUCTO, PEDIDO, PROYECTO	Una relación por entidad; identificadores alternativos → `UNIQUE`	7 relaciones
2	LÍNEA DE PEDIDO, débil en identificación de PEDIDO	PK = pedido_id + num_linea; FK con cascada	`LINEAS_PEDIDO (pedido_id, num_linea, ...)`, `fk_lin_pedido ... ON DELETE CASCADE`
3	pertenece (1:N, (0,1) junto a DEPARTAMENTO)	FK en el lado N, admite nulos	`EMPLEADOS.dept_id (N)`
4	dirige (reflexiva 1:N, jefe (0,1))	FK reflexiva con nombre de papel, admite nulos	`EMPLEADOS.jefe_id (N)`
5	atiende (1:N, (0,1) junto a EMPLEADO)	FK con nombre de papel, admite nulos	`CLIENTES.comercial_id (N)`
6	realiza (1:N, (1,1) junto a CLIENTE)	FK obligatoria	`PEDIDOS.cliente_id` `NOT NULL`
7	gestiona (1:N, (0,1) junto a EMPLEADO)	FK con nombre de papel, admite nulos	`PEDIDOS.empleado_id (N)`
8	referencia (1:N, (1,1) junto a PRODUCTO)	FK obligatoria	`LINEAS_PEDIDO.producto_id` `NOT NULL`
9	clasifica (1:N, (1,1) junto a CATEGORÍA)	FK obligatoria	`PRODUCTOS.categoria_id` `NOT NULL`
10	es_subcategoria (reflexiva 1:N, padre (0,1))	FK reflexiva, admite nulos	`CATEGORIAS.categoria_padre_id (N)`
11	organiza (1:N, (0,1) junto a DEPARTAMENTO)	FK, admite nulos	`PROYECTOS.dept_id (N)`
12	asignado (N:M con atributos)	Relación nueva; PK = emp_id + proyecto_id	`ASIGNACIONES (emp_id, proyecto_id, fecha_asignacion, horas, funcion)`
13	Dominios	`CHECK`	`ck_emp_salario`, `ck_ped_estado`, `ck_pro_descatalogado`, `ck_lin_descuento`…
14	Restricciones entre atributos de la misma tupla	`CHECK`	`ck_ped_fechas`, `ck_pry_fechas`

14.4. Esquema relacional resultante

DEPARTAMENTOS (dept_id, nombre, localidad (N), presupuesto (N))

UK: nombre

EMPLEADOS (emp_id, nombre, apellidos, email, telefono (N), fecha_alta, oficio, salario, comision (N), jefe_id (N), dept_id (N))

jefe_id → EMPLEADOS(emp_id)
dept_id → DEPARTAMENTOS(dept_id)
UK: email

CLIENTES (cliente_id, nombre, nif, email (N), telefono (N), ciudad (N), provincia (N), fecha_alta, limite_credito (N), comercial_id (N))

comercial_id → EMPLEADOS(emp_id)
UK: nif

CATEGORIAS (categoria_id, nombre, categoria_padre_id (N))

categoria_padre_id → CATEGORIAS(categoria_id)
UK: nombre

PRODUCTOS (producto_id, nombre, descripcion (N), categoria_id, precio, stock, fecha_alta (N), descatalogado)

categoria_id → CATEGORIAS(categoria_id)

PEDIDOS (pedido_id, cliente_id, empleado_id (N), fecha_pedido, fecha_envio (N), estado)

cliente_id → CLIENTES(cliente_id)
empleado_id → EMPLEADOS(emp_id)

LINEAS_PEDIDO (*pedido_id*, num_linea, producto_id, cantidad, precio_unitario, descuento)

pedido_id → PEDIDOS(pedido_id) [ON DELETE CASCADE]
producto_id → PRODUCTOS(producto_id)

PROYECTOS (proyecto_id, nombre, dept_id (N), fecha_inicio, fecha_fin (N), presupuesto (N))

dept_id → DEPARTAMENTOS(dept_id)
UK: nombre

ASIGNACIONES (*emp_id*, *proyecto_id*, fecha_asignacion, horas (N), funcion (N))

emp_id → EMPLEADOS(emp_id)
proyecto_id → PROYECTOS(proyecto_id) [ON DELETE CASCADE]

Las restricciones CHECK y los valores por defecto se detallan en el módulo 16 (sección 12.1).

14.5. Del esquema al script

La correspondencia con ../recursos/empresa_crear.sql es directa. Por ejemplo, así queda la entidad débil LÍNEA DE PEDIDO (la N:M asignaciones sigue el mismo patrón, con PK (emp_id, proyecto_id) y dos claves ajenas):

-- Entidad débil en identificación: PK = PK de PEDIDOS + identificador parcial
CREATE TABLE lineas_pedido (
    pedido_id        NUMBER(8),
    num_linea        NUMBER(3),
    producto_id      NUMBER(6)    CONSTRAINT nn_lin_producto NOT NULL,   -- referencia: (1,1) junto a PRODUCTO
    cantidad         NUMBER(5)    CONSTRAINT nn_lin_cantidad NOT NULL,
    precio_unitario  NUMBER(8,2)  CONSTRAINT nn_lin_precio NOT NULL,     -- dato histórico, no derivado
    descuento        NUMBER(4,2)  DEFAULT 0 CONSTRAINT nn_lin_descuento NOT NULL,
    CONSTRAINT pk_lineas_pedido PRIMARY KEY (pedido_id, num_linea),
    CONSTRAINT ck_lin_cantidad  CHECK (cantidad > 0),
    CONSTRAINT ck_lin_precio    CHECK (precio_unitario >= 0),
    CONSTRAINT ck_lin_descuento CHECK (descuento BETWEEN 0 AND 100),
    CONSTRAINT fk_lin_pedido    FOREIGN KEY (pedido_id)   REFERENCES pedidos (pedido_id) ON DELETE CASCADE,
    CONSTRAINT fk_lin_producto  FOREIGN KEY (producto_id) REFERENCES productos (producto_id)
);

14.6. Decisiones de diseño comentadas

¿Por qué LÍNEA DE PEDIDO es débil y no una N:M entre PEDIDO y PRODUCTO? Con una N:M la PK sería (pedido_id, producto_id) y no se podría repetir un producto en el mismo pedido (por ejemplo, dos líneas del mismo producto con descuentos distintos). Con la entidad débil y el número de línea, el pedido se ve como un documento con sus líneas numeradas, igual que una factura en papel.
¿Por qué no hay ON DELETE CASCADE en PEDIDOS.cliente_id? Los pedidos son documentos con valor contable: no deben desaparecer al borrar un cliente. La FK restringida obliga a tratar antes sus pedidos.
¿Por qué ASIGNACIONES tiene cascada hacia PROYECTOS pero no hacia EMPLEADOS? Si se elimina un proyecto, sus asignaciones pierden sentido. En cambio, borrar un empleado con historial de horas en proyectos sería perder información: se prefiere impedirlo.
¿Por qué la PK de ASIGNACIONES no incluye la fecha? Porque el requisito dice que un empleado está o no está asignado a un proyecto; no se guarda un historial de asignaciones repetidas. Si se quisiera, habría que añadir fecha_asignacion a la PK (sección 6.1).
Restricciones perdidas que el script no recoge (y que se documentan): todo pedido debe tener al menos una línea ((1,n) junto a LÍNEA); un empleado no puede ser su propio jefe y no puede haber ciclos en la jerarquía de mando ni en la de categorías; el comercial de un cliente debería ser un empleado con oficio VENDEDOR.

✏️ Ejercicio: ¿Qué CHECK añadirías a EMPLEADOS y a CATEGORIAS para impedir que un empleado sea su propio jefe y que una categoría sea su propia madre? ¿Bastan para impedir cualquier ciclo?

15. Caso 2: biblioteca municipal

15.1. Requisitos

Cada socio tiene número de socio, DNI único, nombre, dirección (calle, código postal y ciudad), fecha de nacimiento y uno o varios teléfonos. Se necesita conocer su edad.
Cada libro tiene ISBN, título y año de publicación. Lo publica una única editorial (código y nombre único). Un libro tiene uno o varios autores (código, nombre, nacionalidad opcional) y de cada autoría se guarda el orden en el que aparece el autor en la portada.
Cada libro pertenece como mucho a un género; los géneros se organizan en jerarquía (Novela → Novela negra).
De cada libro hay uno o varios ejemplares, numerados dentro de cada libro, con su estado.
Un socio toma prestados ejemplares. De cada préstamo se guarda la fecha de préstamo y la fecha de devolución (vacía mientras no se devuelve). Un ejemplar puede prestarse muchas veces a lo largo del tiempo, pero no dos veces el mismo día. Un socio puede tener como mucho 3 préstamos sin devolver.

15.2. Diagrama E/R

flowchart TB
    classDef debil stroke-width:4px
    classDef derivado stroke-dasharray: 5 5
    SOCIO[SOCIO]
    LIBRO[LIBRO]
    EJEMPLAR[EJEMPLAR]:::debil
    AUTOR[AUTOR]
    EDITORIAL[EDITORIAL]
    GENERO[GÉNERO]

    SOCIO --- so_num([<u>num_socio</u>])
    SOCIO --- so_tel(((telefono)))
    SOCIO --- so_dir([direccion])
    so_dir --- so_calle([calle])
    so_dir --- so_cp([cp])
    so_dir --- so_ciu([ciudad])
    SOCIO --- so_fnac([fecha_nacimiento])
    SOCIO --- so_edad([edad]):::derivado

    EDITORIAL ---|"(1,1)"| publica{publica}
    publica ---|"(1,n)"| LIBRO
    AUTOR ---|"(1,n)"| escribe{escribe}
    escribe ---|"(0,n)"| LIBRO
    escribe --- es_orden([orden])
    GENERO ---|"(0,1)"| clasif{clasifica}
    clasif ---|"(0,n)"| LIBRO
    GENERO ---|"padre (0,1)"| subg{es_subgenero}
    subg ---|"hijo (0,n)"| GENERO
    LIBRO ---|"(1,1)"| tiene{tiene}:::debil
    tiene ---|"(1,n)"| EJEMPLAR
    SOCIO ---|"(0,n)"| presta{toma_prestado}
    presta ---|"(0,n)"| EJEMPLAR
    presta --- pr_fp([fecha_prestamo])
    presta --- pr_fd(["fecha_devolucion (0,1)"])

15.3. Transformación

SOCIOS_BIB (num_socio, dni, nombre, calle, cp, ciudad, fecha_nacimiento)

UK: dni

TELEFONOS_SOCIO (*num_socio*, telefono)

num_socio → SOCIOS_BIB(num_socio) [ON DELETE CASCADE]

EDITORIALES (editorial_id, nombre)

UK: nombre

GENEROS (genero_id, nombre, genero_padre_id (N))

genero_padre_id → GENEROS(genero_id)
UK: nombre

LIBROS_BIB (isbn, titulo, anio, editorial_id, genero_id (N))

editorial_id → EDITORIALES(editorial_id)
genero_id → GENEROS(genero_id) [ON DELETE SET NULL]

AUTORES (autor_id, nombre, nacionalidad (N))

AUTORIAS (*isbn*, *autor_id*, orden)

isbn → LIBROS_BIB(isbn) [ON DELETE CASCADE]
autor_id → AUTORES(autor_id)
UK: (isbn, orden)
CHECK: orden > 0

EJEMPLARES_BIB (*isbn*, num_ejemplar, estado)

isbn → LIBROS_BIB(isbn) [ON DELETE CASCADE]

PRESTAMOS (*isbn*, *num_ejemplar*, fecha_prestamo, num_socio, fecha_devolucion (N))

(isbn, num_ejemplar) → EJEMPLARES_BIB(isbn, num_ejemplar)
num_socio → SOCIOS_BIB(num_socio)
CHECK: fecha_devolucion >= fecha_prestamo

flowchart RL
    TELEFONOS_SOCIO -->|num_socio| SOCIOS_BIB
    LIBROS_BIB -->|editorial_id| EDITORIALES
    LIBROS_BIB -->|genero_id| GENEROS
    GENEROS -->|genero_padre_id| GENEROS
    AUTORIAS -->|isbn| LIBROS_BIB
    AUTORIAS -->|autor_id| AUTORES
    EJEMPLARES_BIB -->|isbn| LIBROS_BIB
    PRESTAMOS -->|"isbn, num_ejemplar"| EJEMPLARES_BIB
    PRESTAMOS -->|num_socio| SOCIOS_BIB

Ejemplo de extensión de PRESTAMOS:

isbn	num_ejemplar	fecha_prestamo	num_socio	fecha_devolucion
9788420412146	1	03/02/2026	1	17/02/2026
9788420412146	1	20/02/2026	3	NULL
9788497592208	1	05/03/2026	1	NULL

15.4. Decisiones comentadas

Dirección compuesta: se descompone (calle, cp, ciudad) para poder buscar socios por ciudad o código postal.
Teléfonos: multivaluado → relación propia. El mínimo de un teléfono por socio se pierde (restricción documentada).
Edad: derivado que depende de la fecha actual → no se almacena; se calcula en una vista.
PK de PRESTAMOS: la N:M entre SOCIO y EJEMPLAR tiene repeticiones a lo largo del tiempo, así que se añade fecha_prestamo. Pero, como un ejemplar no puede prestarse dos veces el mismo día, (isbn, num_ejemplar, fecha_prestamo) ya es única y no hace falta num_socio en la PK. Incluirlo sería un error: permitiría prestar el mismo ejemplar el mismo día a dos socios.
Orden de autoría: UK: (isbn, orden) impide que dos autores ocupen la misma posición en la portada de un libro.
Restricciones perdidas: todo libro tiene al menos un autor y un ejemplar; todo socio tiene al menos un teléfono; como mucho 3 préstamos sin devolver por socio; un ejemplar no puede tener dos préstamos sin devolver a la vez. Todas requieren aserción o disparador.

16. Caso 3: hospital

16.1. Requisitos

El personal del hospital (código, NIF único, nombre, fecha de incorporación) puede ser médico (número de colegiado único, especialidad), enfermero (turno: mañana, tarde o noche) o ninguno de los dos (personal administrativo). Nadie es a la vez médico y enfermero.
Cada médico tiene una única especialidad (código, nombre único).
El hospital tiene plantas (número, nombre). Cada planta está supervisada por exactamente un enfermero y un enfermero supervisa como mucho una planta.
Cada planta tiene habitaciones numeradas dentro de la planta, con su número de camas.
Los pacientes (número de historia clínica, NIF opcional y único, nombre, fecha de nacimiento) pueden tener varias alergias.
Un paciente puede tener muchos ingresos. Cada ingreso tiene un código propio, fecha de ingreso, fecha de alta opcional, una habitación y un médico responsable.
Los médicos prescriben medicamentos a los pacientes, con una dosis y una fecha.

16.2. Diagrama E/R

flowchart TB
    classDef debil stroke-width:4px
    PERSONAL[PERSONAL] --- jh{{"es un<br/>(parcial, exclusiva)"}}
    jh --- MEDICO[MÉDICO]
    jh --- ENFERMERO[ENFERMERO]
    ESPECIALIDAD[ESPECIALIDAD] ---|"(1,1)"| tiene_esp{tiene}
    tiene_esp ---|"(0,n)"| MEDICO
    ENFERMERO ---|"(1,1)"| supervisa{supervisa}
    supervisa ---|"(0,1)"| PLANTA[PLANTA]
    PLANTA ---|"(1,1)"| consta{consta_de}:::debil
    consta ---|"(1,n)"| HABITACION[HABITACIÓN]:::debil
    PACIENTE[PACIENTE] ---|"(1,1)"| ingresa{ingresa}
    ingresa ---|"(0,n)"| INGRESO[INGRESO]
    HABITACION ---|"(1,1)"| aloja{aloja}
    aloja ---|"(0,n)"| INGRESO
    MEDICO ---|"(1,1)"| responsable{es_responsable}
    responsable ---|"(0,n)"| INGRESO
    MEDICO ---|"(0,n)"| prescribe{prescribe}
    prescribe ---|"(0,n)"| PACIENTE
    prescribe ---|"(0,n)"| MEDICAMENTO[MEDICAMENTO]
    PACIENTE --- pa_alerg(((alergia)))

16.3. Transformación

Jerarquía (parcial y exclusiva, subtipos con atributos y relaciones propias: el médico tiene especialidad y es responsable de ingresos; el enfermero supervisa plantas) → opción 3, con el discriminante admitiendo un valor para el personal que no es de ningún subtipo:

PERSONAL (personal_id, nif, nombre, fecha_incorporacion, tipo)

UK: nif
CHECK: tipo IN (‘MEDICO’, ‘ENFERMERO’, ‘OTRO’)

ESPECIALIDADES (esp_id, nombre)

UK: nombre

MEDICOS (*personal_id*, num_colegiado, esp_id)

personal_id → PERSONAL(personal_id) [ON DELETE CASCADE]
esp_id → ESPECIALIDADES(esp_id)
UK: num_colegiado

ENFERMEROS (*personal_id*, turno)

personal_id → PERSONAL(personal_id) [ON DELETE CASCADE]
CHECK: turno IN (‘MAÑANA’, ‘TARDE’, ‘NOCHE’)

1:1 supervisa ((1,1) junto a ENFERMERO, (0,1) junto a PLANTA: toda planta tiene supervisor) → FK NOT NULL UNIQUE en PLANTAS:

PLANTAS (num_planta, nombre, supervisor_id)

supervisor_id → ENFERMEROS(personal_id)
UK: supervisor_id

Débil en identificación HABITACIÓN:

HABITACIONES (*num_planta*, num_habitacion, num_camas)

num_planta → PLANTAS(num_planta) [ON DELETE CASCADE]
CHECK: num_camas BETWEEN 1 AND 4

Paciente con multivaluado:

PACIENTES_H (nhc, nif (N), nombre, fecha_nacimiento)

UK: nif

ALERGIAS (*nhc*, alergia)

nhc → PACIENTES_H(nhc) [ON DELETE CASCADE]

INGRESO (entidad con identificador propio y tres 1:N obligatorias; la FK hacia la habitación es compuesta):

INGRESOS (ingreso_id, nhc, num_planta, num_habitacion, medico_id, fecha_ingreso, fecha_alta (N))

nhc → PACIENTES_H(nhc)
(num_planta, num_habitacion) → HABITACIONES(num_planta, num_habitacion)
medico_id → MEDICOS(personal_id)
CHECK: fecha_alta >= fecha_ingreso

Ternaria N:M:P prescribe con fecha en la clave (el mismo médico puede volver a prescribir el mismo medicamento al mismo paciente otro día):

MEDICAMENTOS (cod_medicamento, nombre)

PRESCRIPCIONES_H (*medico_id*, *nhc*, *cod_medicamento*, fecha, dosis)

medico_id → MEDICOS(personal_id)
nhc → PACIENTES_H(nhc)
cod_medicamento → MEDICAMENTOS(cod_medicamento)

flowchart RL
    MEDICOS -->|personal_id| PERSONAL
    ENFERMEROS -->|personal_id| PERSONAL
    MEDICOS -->|esp_id| ESPECIALIDADES
    PLANTAS -->|supervisor_id| ENFERMEROS
    HABITACIONES -->|num_planta| PLANTAS
    ALERGIAS -->|nhc| PACIENTES_H
    INGRESOS -->|nhc| PACIENTES_H
    INGRESOS -->|"num_planta, num_habitacion"| HABITACIONES
    INGRESOS -->|medico_id| MEDICOS
    PRESCRIPCIONES_H -->|medico_id| MEDICOS
    PRESCRIPCIONES_H -->|nhc| PACIENTES_H
    PRESCRIPCIONES_H -->|cod_medicamento| MEDICAMENTOS

16.4. Decisiones comentadas

PLANTAS.supervisor_id referencia a ENFERMEROS, no a PERSONAL: así la FK garantiza que el supervisor sea enfermero. Es la gran ventaja de la opción 3 frente a la 1.
La exclusividad (nadie es médico y enfermero) puede reforzarse con el truco del discriminante en los subtipos (sección 11.4).
INGRESOS tiene identificador propio (ingreso_id) porque un paciente puede ingresar varias veces en la misma habitación; usar (nhc, fecha_ingreso) como clave también sería válido.
Restricciones perdidas: que no haya más pacientes ingresados a la vez en una habitación que camas tiene; que un médico no prescriba a un paciente alérgico; que el tipo de PERSONAL coincida con la tabla de subtipo en la que aparece; que toda planta tenga al menos una habitación.

17. Ejercicios

Ejercicio 1: Atributos de todo tipo

Una agencia de viajes registra guías con: código (identificador), DNI (único), nombre completo formado por nombre, primer apellido y segundo apellido (opcional), idiomas que habla (varios), fecha de nacimiento, edad y email opcional. Transforma la entidad al modelo relacional.

Ver solución

GUIAS (cod_guia, dni, nombre, apellido1, apellido2 (N), fecha_nacimiento, email (N))

UK: dni

IDIOMAS_GUIA (*cod_guia*, idioma)

cod_guia → GUIAS(cod_guia) [ON DELETE CASCADE]

Atributo	Tipo	Decisión
cod_guia	Identificador	PK
dni	Identificador alternativo	`UNIQUE` (+ `NOT NULL`)
nombre completo	Compuesto	Descompuesto en tres atributos; `apellido2` opcional
idiomas	Multivaluado	Relación nueva con PK (cod_guia, idioma)
edad	Derivado	No se almacena (se calcula a partir de `fecha_nacimiento`)
email	Opcional	Admite nulos

Ejercicio 2: Dónde va la clave ajena

Transforma cada relación binaria e indica si la clave ajena admite nulos y si lleva UNIQUE:

a) PAÍS — tiene_capital — CIUDAD: cada país tiene exactamente una capital; una ciudad es capital como mucho de un país. Cardinalidades: (1,1) junto a CIUDAD, (0,1) junto a PAÍS. b) CLIENTE — tiene — TARJETA_FIDELIDAD: un cliente tiene como mucho una tarjeta y una tarjeta es de exactamente un cliente. (1,1) junto a CLIENTE, (0,1) junto a TARJETA. c) MECÁNICO — repara — VEHÍCULO: un vehículo en el taller está asignado como mucho a un mecánico; un mecánico repara muchos vehículos. (0,1) junto a MECÁNICO, (0,n) junto a VEHÍCULO. d) EMPLEADO — usa — PLAZA_PARKING: un empleado usa como mucho una plaza y una plaza la usa como mucho un empleado. (0,1) en ambos lados; hay 40 plazas y 300 empleados.

Ver solución

a) Todo país tiene capital → la clave va a PAÍS: PAISES (cod_pais, nombre, capital_id), capital_id → CIUDADES(ciudad_id), NOT NULL y UNIQUE.

b) Toda tarjeta tiene cliente → la clave va a TARJETA: TARJETAS (num_tarjeta, puntos, cliente_id), cliente_id → CLIENTES(...), NOT NULL y UNIQUE. (Si casi todos los clientes tuvieran tarjeta, también sería razonable fusionar.)

c) 1:N, la FK va al lado N (VEHÍCULO): VEHICULOS (matricula, …, mecanico_id (N)). Admite nulos ((0,1) junto a MECÁNICO); sin UNIQUE (un mecánico repara muchos).

d) (0,1)-(0,1). Hay menos plazas que empleados, así que propagar a PLAZA deja menos nulos: PLAZAS_PARKING (num_plaza, emp_id (N)) con UNIQUE en emp_id, que admite nulos (plazas libres). Alternativa: relación nueva USOS_PLAZA (*num_plaza*, emp_id) con UK: emp_id.

Ejercicio 3: Ternarias

Calcula la clave primaria (y las claves alternativas, si las hay) de la relación resultante:

a) Universidad: PROFESOR imparte ASIGNATURA a GRUPO. Una asignatura en un grupo la imparte un solo profesor; un profesor puede impartir la misma asignatura a varios grupos y varias asignaturas a un mismo grupo. Máximos: 1 junto a PROFESOR, n junto a ASIGNATURA, n junto a GRUPO. b) Agencia de viajes: GUIA acompaña a GRUPO_TURISTICO en una FECHA. En una fecha, un grupo lleva un único guía y un guía acompaña a un único grupo. Máximos: 1 junto a GUIA, 1 junto a GRUPO_TURISTICO, n junto a FECHA. c) Taller: MECÁNICO usa HERRAMIENTA en REPARACIÓN, sin ninguna restricción.

Ver solución

a) IMPARTICIONES (*cod_asig*, *grupo_id*, prof_id). PK = (cod_asig, grupo_id), porque la pareja determina el profesor.

b) ACOMPANAMIENTOS (*grupo_id*, fecha, guia_id) con UK: (guia_id, fecha). Hay dos claves candidatas: (grupo_id, fecha) y (guia_id, fecha). Si FECHA no es una entidad con más atributos, basta con un atributo fecha (no hace falta una tabla FECHAS).

c) N:M:P → USOS_HERRAMIENTA (*mecanico_id*, *herramienta_id*, *num_orden*), con la PK formada por las tres claves ajenas.

Ejercicio 4: Elegir la opción para una jerarquía

Un taller mecánico registra vehículos (matrícula, marca, modelo, año). Los vehículos pueden ser turismos (número de puertas), motocicletas (cilindrada) o furgonetas (carga máxima, y un permiso especial que se relaciona con la entidad PERMISO). Además, el taller repara vehículos de otros tipos que no se especializan (caravanas, quads…). Ningún vehículo pertenece a dos subtipos. Elige la opción de transformación, justifícala y escribe el esquema.

Ver solución

La jerarquía es parcial (hay vehículos sin subtipo) y exclusiva. Eso descarta la opción 2. Los subtipos tienen un único atributo cada uno, lo que favorecería la opción 1, pero FURGONETA tiene una relación propia con PERMISO, que con la opción 1 no se podría restringir solo a furgonetas. Hay dos soluciones razonables:

Opción 3 (la más fiel):

VEHICULOS_T (matricula, marca, modelo, anio, tipo (N))

CHECK: tipo IN (‘TURISMO’, ‘MOTO’, ‘FURGONETA’)

TURISMOS (*matricula*, num_puertas)

MOTOCICLETAS (*matricula*, cilindrada)

FURGONETAS (*matricula*, carga_max, permiso_id)

en las tres: matricula → VEHICULOS_T(matricula) [ON DELETE CASCADE]
permiso_id → PERMISOS(permiso_id)

(tipo nulo para los vehículos que no son de ningún subtipo.)

Opción mixta (también válida): una sola tabla para turismos y motos (opción 1, con num_puertas (N) y cilindrada (N) y un CHECK por tipo) y una tabla FURGONETAS aparte que referencia a la general. Lo que no sería correcto es la opción 1 pura con permiso_id (N) en VEHICULOS_T sin ningún CHECK, porque permitiría asignar un permiso a una moto.

Ejercicio 5: Restricciones perdidas

En el caso de la biblioteca (sección 15), indica para cada restricción si el esquema relacional la garantiza o no y, en caso negativo, cómo se recuperaría: a) Un libro tiene una única editorial. b) Todo libro tiene al menos un ejemplar. c) Un socio no puede tener más de 3 préstamos sin devolver. d) La fecha de devolución no puede ser anterior a la del préstamo. e) No puede haber dos socios con el mismo DNI. f) Un género no puede ser subgénero de sí mismo.

Ver solución

a) Sí: LIBROS_BIB.editorial_id es un único atributo (NOT NULL).

b) No: mínimo 1 del lado N de una relación de identificación. Aserción (Oracle 26ai), disparador o alta conjunta del libro y su primer ejemplar en la misma transacción.

c) No: máximo concreto sobre varias filas. Aserción o disparador.

d) Sí, con CHECK (fecha_devolucion >= fecha_prestamo); si fecha_devolucion es nula, el CHECK da desconocido y la fila se acepta (correcto).

e) Sí, con UNIQUE (dni).

f) Sí para el caso directo con CHECK (genero_padre_id <> genero_id); los ciclos más largos necesitan un disparador.

Una red social guarda usuarios (id, nick único, email único, fecha de registro), publicaciones (cada una de un usuario, con id propio, texto y fecha), comentarios (numerados dentro de cada publicación, con texto, fecha y el usuario que comenta), “me gusta” de usuarios a publicaciones (con la fecha; un usuario solo puede dar un “me gusta” a cada publicación) y relaciones de seguimiento entre usuarios. Las cuentas pueden ser personales o de empresa (estas tienen CIF y sector); la jerarquía es total y exclusiva y solo se diferencia en esos dos atributos. Haz la transformación completa con esquema y grafo relacional.

Ver solución

Jerarquía total y exclusiva con subtipos de muy pocos atributos y sin relaciones propias → opción 1:

USUARIOS_RS (usuario_id, nick, email, fecha_registro, tipo_cuenta, cif (N), sector (N))

UK: nick; UK: email
CHECK: tipo_cuenta IN (‘PERSONAL’, ‘EMPRESA’)
CHECK: (tipo_cuenta = ‘PERSONAL’ AND cif IS NULL AND sector IS NULL) OR (tipo_cuenta = ‘EMPRESA’ AND cif IS NOT NULL)

PUBLICACIONES (pub_id, autor_id, texto, fecha)

autor_id → USUARIOS_RS(usuario_id) [ON DELETE CASCADE]

COMENTARIOS (*pub_id*, num_comentario, usuario_id, texto, fecha)

pub_id → PUBLICACIONES(pub_id) [ON DELETE CASCADE]
usuario_id → USUARIOS_RS(usuario_id) [ON DELETE CASCADE]

ME_GUSTA (*usuario_id*, *pub_id*, fecha)

usuario_id → USUARIOS_RS(usuario_id) [ON DELETE CASCADE]
pub_id → PUBLICACIONES(pub_id) [ON DELETE CASCADE]

SEGUIMIENTOS_RS (*seguidor_id*, *seguido_id*, fecha)

seguidor_id → USUARIOS_RS(usuario_id) [ON DELETE CASCADE]
seguido_id → USUARIOS_RS(usuario_id) [ON DELETE CASCADE]
CHECK: seguidor_id <> seguido_id

Observa que COMENTARIOS es débil en identificación de PUBLICACIÓN y, además, tiene una relación 1:N con USUARIO (quién comenta). El borrado de un usuario arrastra en cascada sus publicaciones, comentarios, “me gusta” y seguimientos, que en una red social es lo que suele exigir la normativa de protección de datos.

Ejercicios propuestos

✏️ Ejercicio: Transforma una relación N:M entre ACTOR y PELICULA con el atributo personaje, teniendo en cuenta que un actor puede interpretar varios personajes en la misma película.

✏️ Ejercicio: Una ternaria RESERVA entre CLIENTE, VUELO y ASIENTO: en un vuelo, cada asiento lo ocupa un solo cliente, y un cliente puede reservar varios asientos en el mismo vuelo. Calcula la clave primaria.

✏️ Ejercicio: Transforma la jerarquía PERSONA → ALUMNO, PROFESOR de una universidad, sabiendo que es parcial y solapada (hay profesores que son alumnos de doctorado), con las tres opciones, y compáralas.

✏️ Ejercicio: Diseña el E/R y transforma al relacional un videoclub online con películas, copias digitales en distintas calidades (HD, 4K) dependientes de la película, alquileres con fecha y precio, y clientes con varias direcciones de facturación.

✏️ Ejercicio: Toma el diagrama E/R de EMPRESA (sección 14.2) y añade: a) una especialización de EMPLEADO en VENDEDOR (con comision y zona) y RESTO; b) un histórico de los departamentos por los que ha pasado cada empleado. Transforma los cambios y razona cómo afectarían a las tablas actuales.

Resumen

Concepto	Regla clave
Entidad fuerte	Relación con PK = identificador
Atributo compuesto	Descomponer en simples
Atributo multivaluado	Relación nueva (PK de la entidad + atributo)
Atributo derivado	No almacenar; vista o columna virtual (si es determinista)
1:N	FK en el lado N; `NOT NULL` si el mínimo junto al lado 1 es 1
1:1	Fusionar (1,1)-(1,1); FK `NOT NULL UNIQUE` en el lado obligatorio; (0,1)-(0,1): FK `UNIQUE` nula o relación nueva
N:M	Relación nueva; PK = claves de ambas (+ fecha si hay repeticiones)
Reflexiva	FK hacia la propia relación con nombre de papel
Ternaria / n-aria	Relación nueva; PK = claves de las entidades con máximo n
Débil en existencia	FK `NOT NULL` + `ON DELETE CASCADE`
Débil en identificación	PK = PK de la fuerte + identificador parcial
Generalización	Opción 1 (una tabla + discriminante), 2 (solo subtipos, solo si total) o 3 (supertipo + subtipos, la más fiel)
Agregación	FK compuesta hacia la relación agregada
Categoría	Clave subrogada de la categoría o arco exclusivo con `CHECK`
Restricciones perdidas	Mínimos 1 del lado N, máximos concretos, coberturas: aserción, disparador o aplicación

Navegación
← Módulo anterior: El modelo relacional	Siguiente módulo: Álgebra relacional →

Módulo 21: Del modelo E/R al relacional

Objetivos del módulo

1. El diseño lógico

1.1. De dónde partimos y adónde llegamos

1.2. Pérdida semántica

1.3. Orden recomendado

1.4. Notación

2. Transformación de entidades fuertes

3. Transformación de atributos

3.1. Atributos simples e identificadores

3.2. Atributos compuestos

3.3. Atributos multivaluados

3.4. Atributos derivados

3.5. Atributos opcionales

4. Transformación de relaciones 1:N

4.1. Regla general

4.2. La participación decide los nulos

4.3. Opción alternativa: relación intermedia

5. Transformación de relaciones 1:1

5.1. Caso (1,1) — (1,1): ambas obligatorias

5.2. Caso (0,1) — (1,1): una obligatoria y otra opcional

5.3. Caso (0,1) — (0,1): ambas opcionales

5.4. Resumen de las relaciones 1:1

6. Transformación de relaciones N:M

6.1. Cuando la clave combinada no basta

6.2. Restricciones de la N:M

7. Atributos de las relaciones

8. Relaciones reflexivas

8.1. Reflexiva 1:N

8.2. Reflexiva 1:1

8.3. Reflexiva N:M

9. Relaciones ternarias y n-arias

9.1. Regla general

9.2. Ternaria N:M:P

9.3. Ternaria 1:N:M

9.4. Ternaria 1:1:N

9.5. Relaciones n-arias

10. Entidades débiles

10.1. Débil en existencia

10.2. Débil en identificación

10.3. Cadenas de debilidad

11. Generalizaciones (jerarquías)

11.1. El ejemplo

11.2. Opción 1: una sola relación para toda la jerarquía

11.3. Opción 2: una relación por subtipo

11.4. Opción 3: una relación para el supertipo y otra para cada subtipo

11.5. ¿Qué opción elegir?

11.6. Jerarquías de varios niveles

12. Agregaciones y categorías

12.1. Agregaciones

12.2. Categorías (tipos unión)

13. Restricciones que se pierden y cómo recuperarlas

14. Caso completo: el esquema EMPRESA

14.1. Requisitos

14.2. Diagrama E/R

14.3. Transformación paso a paso

14.4. Esquema relacional resultante

14.5. Del esquema al script

14.6. Decisiones de diseño comentadas

15. Caso 2: biblioteca municipal

15.1. Requisitos

15.2. Diagrama E/R

15.3. Transformación

15.4. Decisiones comentadas

16. Caso 3: hospital

16.1. Requisitos

16.2. Diagrama E/R

16.3. Transformación

16.4. Decisiones comentadas

17. Ejercicios

Ejercicio 1: Atributos de todo tipo

Ejercicio 2: Dónde va la clave ajena

Ejercicio 3: Ternarias

Ejercicio 4: Elegir la opción para una jerarquía

Ejercicio 5: Restricciones perdidas

Ejercicio 6: Caso completo de una red social

Ejercicios propuestos

Resumen