Módulo 25: Casos prácticos de diseño lógico

Objetivos del módulo

  • Aplicar de principio a fin el proceso de diseño: requisitos → E/R → esquema relacional → normalización → esquema final
  • Justificar cada decisión de transformación (claves, claves ajenas, nulos, jerarquías, entidades débiles, relaciones N:M y 1:1)
  • Comprobar la forma normal de cada tabla a partir de sus dependencias funcionales y corregir el diseño cuando haga falta
  • Expresar el esquema final con todas sus restricciones (PK, FK con su acción ante el borrado, UK, NOT NULL y CHECK) y dibujar su grafo relacional
  • Reconstruir el diseño completo del esquema EMPRESA y compararlo con su script de creación
  • Conocer los fundamentos del diseño físico: tipos de datos, índices, particionamiento, estimación de volumen, desnormalización controlada y vistas

1. Metodología de los casos

Todos los casos siguen el mismo guion, que es el que deberías seguir en un proyecto real o en un examen:

flowchart LR
    a["1. Enunciado y<br/>requisitos"] --> b["2. Diagrama E/R"]
    b --> c["3. Transformación<br/>al relacional"]
    c --> d["4. DF y forma<br/>normal de cada tabla"]
    d --> e["5. Ajustes de<br/>normalización"]
    e --> f["6. Esquema final<br/>con restricciones"]
    f --> g["7. Grafo<br/>relacional"]

Convenciones que usaremos en el esquema final:

  • Clave primaria subrayada; claves ajenas en cursiva, detalladas debajo con su acción ante el borrado.
  • NO ACTION (también llamada RESTRICT): no se puede borrar la fila padre si tiene hijas. Es el comportamiento por defecto de Oracle.
  • CASCADE: al borrar la fila padre se borran las hijas (ON DELETE CASCADE).
  • SET NULL: al borrar la fila padre, la clave ajena de las hijas pasa a nulo (ON DELETE SET NULL).
  • Los atributos que admiten nulos se marcan con (N); el resto son NOT NULL.

⚠️ Importante: Oracle solo admite acciones referenciales ante el borrado (ON DELETE CASCADE y ON DELETE SET NULL). No existe ON UPDATE CASCADE: por eso las claves primarias deben ser estables (un código interno mejor que un dato que pueda cambiar, como un correo electrónico o una matrícula de coche que se re-matricula).

💡 Consejo: Cuando un requisito no se puede expresar con PK, FK, UK, NOT NULL o CHECK (por ejemplo, “un socio no puede tener más de cinco préstamos activos”), anótalo como restricción adicional. En Oracle se implementa normalmente con disparadores, que se estudian en el Curso de Oracle PL/SQL.

2. Caso 1: Biblioteca municipal

2.1 Enunciado

La red de bibliotecas de un ayuntamiento quiere informatizar su catálogo y el préstamo:

  1. De cada libro se guarda su ISBN, título, año de publicación y número de páginas. Cada libro lo publica una editorial (código, nombre y ciudad).
  2. Un libro puede tener varios autores (código, nombre y nacionalidad) y es importante el orden en que firman. Solo se registran autores con algún libro en el catálogo.
  3. De cada libro hay uno o varios ejemplares físicos, numerados 1, 2, 3… dentro de cada libro. De cada ejemplar se guarda la fecha de adquisición, su estado (BUENO, DETERIORADO o BAJA) y su ubicación: la estantería y la sala donde está. Cada estantería tiene un código único en toda la red y está en una sola sala.
  4. De cada socio se guarda número de socio, DNI (único), nombre, apellidos, correo electrónico (opcional y único), teléfono y fecha de alta.
  5. Un socio se lleva en préstamo un ejemplar en una fecha, con una fecha prevista de devolución; la fecha real de devolución queda vacía hasta que lo devuelve. A lo largo del tiempo, un mismo socio puede llevarse el mismo ejemplar varias veces.
  6. Un socio puede reservar un libro (no un ejemplar concreto) en una fecha.
  7. Reglas de negocio: un socio no puede tener más de cinco préstamos sin devolver, y un ejemplar no puede estar prestado dos veces a la vez.

2.2 Diagrama E/R

Primer borrador del analista (la sala y la estantería se modelaron como atributos del ejemplar):

flowchart LR
    classDef debil stroke-width:4px
    classDef derivado stroke-dasharray: 5 5
    EDITORIAL[EDITORIAL] ---|"(1,1)"| publica{publica}
    publica ---|"(0,n)"| LIBRO[LIBRO]
    AUTOR[AUTOR] ---|"(1,n)"| escribe{escribe}
    escribe ---|"(1,n)"| LIBRO
    escribe --- esc_orden([orden])
    LIBRO ---|"(1,1)"| tiene{tiene}:::debil
    tiene ---|"(1,n)"| EJEMPLAR[EJEMPLAR]:::debil
    SOCIO[SOCIO] ---|"(1,1)"| realiza{realiza}
    realiza ---|"(0,n)"| PRESTAMO[PRESTAMO]
    EJEMPLAR ---|"(1,1)"| sobre{sobre}
    sobre ---|"(0,n)"| PRESTAMO
    SOCIO ---|"(0,n)"| reserva{reserva}
    reserva ---|"(0,n)"| LIBRO
    reserva --- res_fecha(((fecha_reserva)))
    LIBRO --- lib_isbn([<u>isbn</u>])
    LIBRO --- lib_titulo([titulo])
    LIBRO --- lib_anio([anio_publicacion])
    LIBRO --- lib_pag([num_paginas])
    EDITORIAL --- ed_id([<u>editorial_id</u>])
    EDITORIAL --- ed_nombre([nombre])
    EDITORIAL --- ed_ciudad([ciudad])
    AUTOR --- au_id([<u>autor_id</u>])
    AUTOR --- au_nombre([nombre])
    AUTOR --- au_nac([nacionalidad])
    EJEMPLAR --- ej_num([<u>num_ejemplar</u> parcial])
    EJEMPLAR --- ej_fecha([fecha_adquisicion])
    EJEMPLAR --- ej_estado([estado])
    EJEMPLAR --- ej_est([estanteria])
    EJEMPLAR --- ej_sala([sala])
    SOCIO --- so_num([<u>num_socio</u>])
    SOCIO --- so_dni([dni])
    SOCIO --- so_nom([nombre])
    SOCIO --- so_ape([apellidos])
    SOCIO --- so_email(["email (0,1)"])
    SOCIO --- so_tel([telefono])
    SOCIO --- so_alta([fecha_alta])
    PRESTAMO --- pr_id([<u>prestamo_id</u>])
    PRESTAMO --- pr_fp([fecha_prestamo])
    PRESTAMO --- pr_fprev([fecha_prevista])
    PRESTAMO --- pr_fd(["fecha_devolucion (0,1)"])

Decisiones de modelado:

  • EJEMPLAR es débil respecto de LIBRO: su número (1, 2, 3…) solo identifica dentro de cada libro. El identificador parcial num_ejemplar se completa con el ISBN.
  • PRESTAMO es una entidad, no una relación N:M entre SOCIO y EJEMPLAR, porque el mismo par socio-ejemplar puede repetirse en fechas distintas (requisito 5). Se le da un identificador propio (prestamo_id). La alternativa de identificar el préstamo por (ejemplar, fecha_prestamo) también sería válida.
  • reserva es N:M entre SOCIO y LIBRO; como un socio puede reservar el mismo libro en ocasiones distintas, fecha_reserva se trata como un atributo multivaluado de la relación que entrará en la clave.

2.3 Transformación al modelo relacional

Elemento E/R Regla Resultado
EDITORIAL, AUTOR, SOCIO Entidad → tabla EDITORIALES, AUTORES, SOCIOS
LIBRO + publica (1:N) Entidad → tabla; la clave del lado 1 pasa al lado N LIBROS con FK editorial_id NOT NULL (participación mínima 1)
escribe (N:M con atributo) Nueva tabla con las dos claves AUTORIAS(isbn, autor_id, orden)
EJEMPLAR (débil) Tabla con la clave del fuerte + identificador parcial EJEMPLARES(isbn, num_ejemplar, …)
PRESTAMO + realiza + sobre Entidad → tabla; dos relaciones 1:N → dos FK PRESTAMOS con FK a SOCIOS y FK compuesta a EJEMPLARES
reserva (N:M con multivaluado) Nueva tabla; la fecha entra en la clave RESERVAS(num_socio, isbn, fecha_reserva)

Primer esquema (antes de normalizar):

EJEMPLARES (*isbn*, num_ejemplar, fecha_adquisicion, estado, estanteria, sala)

(las demás tablas se muestran en el esquema final).

2.4 Dependencias funcionales y forma normal

Tabla DF (recubrimiento mínimo) Claves Forma normal
EDITORIALES editorial_id → nombre, ciudad editorial_id FNBC
LIBROS isbn → titulo, anio_publicacion, num_paginas, editorial_id isbn FNBC
AUTORES autor_id → nombre, nacionalidad autor_id FNBC
AUTORIAS isbn, autor_id → orden; isbn, orden → autor_id (isbn, autor_id), (isbn, orden) FNBC
EJEMPLARES isbn, num_ejemplar → fecha_adquisicion, estado, estanteria; estanteria → sala (isbn, num_ejemplar) 2FN
SOCIOS num_socio → todo; dni → num_socio; email → num_socio num_socio, dni, email FNBC
PRESTAMOS prestamo_id → todo; isbn, num_ejemplar, fecha_prestamo → prestamo_id prestamo_id, (isbn, num_ejemplar, fecha_prestamo) FNBC
RESERVAS (ninguna no trivial) (num_socio, isbn, fecha_reserva) FNBC

Observaciones:

  • En AUTORIAS hay dos claves candidatas porque dos autores no pueden ocupar la misma posición en un libro.
  • En PRESTAMOS se ha supuesto que un ejemplar no se presta dos veces el mismo día; eso convierte (isbn, num_ejemplar, fecha_prestamo) en clave alternativa (UK).
  • En SOCIOS, email es clave candidata aunque admita nulos: Oracle permite varios nulos en una columna UNIQUE.

2.5 Ajuste de normalización

EJEMPLARES no está en 3FN: estanteria → sala, con estanteria no superclave y sala no prima (dependencia transitiva desde la clave). La anomalía es clara: si una estantería se traslada de sala, hay que actualizar todos sus ejemplares. Es una entidad oculta: ESTANTERIA. Se separa:

ESTANTERIAS (estanteria_id, sala)

EJEMPLARES (*isbn*, num_ejemplar, fecha_adquisicion, estado, estanteria_id (N))

Descomposición sin pérdida (la intersección estanteria_id es clave de ESTANTERIAS) y con preservación.

2.6 Esquema relacional final

EDITORIALES (editorial_id, nombre, ciudad (N))

  • UK: nombre

LIBROS (isbn, titulo, anio_publicacion (N), num_paginas (N), editorial_id)

  • editorial_id → EDITORIALES(editorial_id) NO ACTION
  • CHECK: num_paginas > 0

AUTORES (autor_id, nombre, nacionalidad (N))

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

  • isbn → LIBROS(isbn) CASCADE (si se borra un libro del catálogo, sus autorías no tienen sentido)
  • autor_id → AUTORES(autor_id) NO ACTION
  • UK: (isbn, orden); CHECK: orden ≥ 1

ESTANTERIAS (estanteria_id, sala)

EJEMPLARES (*isbn*, num_ejemplar, fecha_adquisicion, estado, estanteria_id (N))

  • isbn → LIBROS(isbn) NO ACTION (un libro con ejemplares no se borra: los ejemplares se dan de baja)
  • estanteria_id → ESTANTERIAS(estanteria_id) SET NULL (si se desmonta una estantería, el ejemplar queda sin ubicar)
  • CHECK: estado IN (‘BUENO’, ‘DETERIORADO’, ‘BAJA’); num_ejemplar ≥ 1

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

  • UK: dni; UK: email

PRESTAMOS (prestamo_id, num_socio, isbn, num_ejemplar, fecha_prestamo, fecha_prevista, fecha_devolucion (N))

  • num_socio → SOCIOS(num_socio) NO ACTION (el historial de préstamos impide borrar socios)
  • (isbn, num_ejemplar) → EJEMPLARES(isbn, num_ejemplar) NO ACTION
  • UK: (isbn, num_ejemplar, fecha_prestamo)
  • CHECK: fecha_prevista ≥ fecha_prestamo; fecha_devolucion ≥ fecha_prestamo

RESERVAS (*num_socio*, *isbn*, fecha_reserva)

  • num_socio → SOCIOS(num_socio) CASCADE; isbn → LIBROS(isbn) CASCADE

Restricciones adicionales (no expresables con las anteriores): máximo cinco préstamos con fecha_devolucion nula por socio; un ejemplar no puede tener dos préstamos sin devolver; no se presta un ejemplar en estado BAJA.

2.7 Grafo relacional 

flowchart LR
    LIBROS -->|editorial_id| EDITORIALES
    AUTORIAS -->|isbn| LIBROS
    AUTORIAS -->|autor_id| AUTORES
    EJEMPLARES -->|isbn| LIBROS
    EJEMPLARES -->|estanteria_id| ESTANTERIAS
    PRESTAMOS -->|num_socio| SOCIOS
    PRESTAMOS -->|"isbn, num_ejemplar"| EJEMPLARES
    RESERVAS -->|num_socio| SOCIOS
    RESERVAS -->|isbn| LIBROS

3. Caso 2: Taller mecánico

3.1 Enunciado

Un taller quiere gestionar sus reparaciones (es una ampliación del ejercicio 1 del módulo 19):

  1. Clientes: DNI, nombre, teléfono y correo (opcional).
  2. Vehículos: matrícula, número de bastidor (único), color, año, marca y modelo. Cada vehículo pertenece a un cliente; un cliente puede tener varios. Cada modelo es de una marca, y puede haber modelos con el mismo nombre en marcas distintas. Para pedir recambios se necesita saber el tipo de combustible, que depende del modelo.
  3. Reparaciones: número (único), fecha de entrada, fecha de salida (vacía mientras está en el taller), kilómetros y descripción de la avería. Cada reparación es de un vehículo.
  4. Mecánicos: código, nombre, especialidad (MOTOR, ELECTRICIDAD, CARROCERIA, GENERAL) y precio por hora actual. En una reparación trabajan uno o varios mecánicos; se anotan las horas de cada uno y el precio por hora aplicado (el precio del mecánico puede subir con el tiempo).
  5. Piezas: referencia, descripción, precio actual y stock. En una reparación se usan varias piezas; se anota la cantidad y el precio aplicado.
  6. Facturas: cuando termina, cada reparación genera exactamente una factura, con número propio (serie legal correlativa), fecha y forma de pago (EFECTIVO, TARJETA, TRANSFERENCIA).

3.2 Diagrama E/R 

flowchart LR
    CLIENTE[CLIENTE] ---|"(1,1)"| posee{posee}
    posee ---|"(1,n)"| VEHICULO[VEHICULO]
    VEHICULO ---|"(1,1)"| sufre{sufre}
    sufre ---|"(0,n)"| REPARACION[REPARACION]
    MECANICO[MECANICO] ---|"(1,n)"| trabaja{trabaja}
    trabaja ---|"(0,n)"| REPARACION
    trabaja --- tr_horas([horas])
    trabaja --- tr_precio([precio_hora_aplicado])
    REPARACION ---|"(0,n)"| usa{usa}
    usa ---|"(0,n)"| PIEZA[PIEZA]
    usa --- us_cant([cantidad])
    usa --- us_precio([precio_aplicado])
    REPARACION ---|"(1,1)"| genera{genera}
    genera ---|"(0,1)"| FACTURA[FACTURA]
    CLIENTE --- cl_dni([<u>dni</u>])
    CLIENTE --- cl_nom([nombre])
    CLIENTE --- cl_tel([telefono])
    CLIENTE --- cl_email(["email (0,1)"])
    VEHICULO --- ve_mat([<u>matricula</u>])
    VEHICULO --- ve_bas([bastidor])
    VEHICULO --- ve_col([color])
    VEHICULO --- ve_anio([anio])
    VEHICULO --- ve_marca([marca])
    VEHICULO --- ve_modelo([modelo])
    VEHICULO --- ve_comb([combustible])
    REPARACION --- re_num([<u>num_reparacion</u>])
    REPARACION --- re_fe([fecha_entrada])
    REPARACION --- re_fs(["fecha_salida (0,1)"])
    REPARACION --- re_km([km])
    REPARACION --- re_av([averia])
    MECANICO --- me_cod([<u>cod_mecanico</u>])
    MECANICO --- me_nom([nombre])
    MECANICO --- me_esp([especialidad])
    MECANICO --- me_ph([precio_hora])
    PIEZA --- pi_ref([<u>referencia</u>])
    PIEZA --- pi_desc([descripcion])
    PIEZA --- pi_precio([precio])
    PIEZA --- pi_stock([stock])
    FACTURA --- fa_num([<u>num_factura</u>])
    FACTURA --- fa_fecha([fecha])
    FACTURA --- fa_pago([forma_pago])

En este primer diagrama, la marca, el modelo y el combustible aparecen como simples atributos de VEHICULO; la normalización mostrará que no es una buena idea. Lectura de la relación 1:1 genera: cada factura corresponde a exactamente una reparación (1,1) y cada reparación tiene como mucho una factura (0,1), porque mientras está en el taller aún no se ha facturado.

3.3 Transformación

Elemento Decisión Justificación
posee (1:N) FK dni_cliente NOT NULL en VEHICULOS Todo vehículo tiene propietario
sufre (1:N) FK matricula NOT NULL en REPARACIONES  
trabaja (N:M) Tabla TRABAJOS(num_reparacion, cod_mecanico, horas, precio_hora_aplicado) N:M con atributos
usa (N:M) Tabla CONSUMOS(num_reparacion, referencia, cantidad, precio_aplicado) N:M con atributos
genera (1:1, (1,1)-(0,1)) FK num_reparacion en FACTURAS, NOT NULL y UNIQUE La FK va al lado opcional para no tener nulos; UNIQUE garantiza el 1:1
Clave de VEHICULO matricula como PK, bastidor como UK Un vehículo puede re-matricularse; si fuera frecuente convendría una clave interna (vehiculo_id), ya que Oracle no propaga cambios de PK

La alternativa de fusionar FACTURA y REPARACION en una sola tabla se descarta: la factura tiene su propia numeración legal y sus atributos serían nulos durante toda la reparación.

3.4 Dependencias funcionales y forma normal

Primer borrador de VEHICULOS:

VEHICULOS (matricula, bastidor, color, anio, marca, modelo, combustible, dni_cliente)

matricula     → bastidor, color, anio, marca, modelo, combustible, dni_cliente
bastidor      → matricula
marca, modelo → combustible

Observa que modelo ↛ marca (hay modelos homónimos en marcas distintas): el modelo se identifica por el par (marca, modelo). Claves candidatas: matricula y bastidor. La DF marca, modelo → combustible tiene un determinante que no es superclave y un implicado no primo: es una dependencia transitiva (matricula → marca, modelo → combustible) y VEHICULOS no está en 3FN. Si un fabricante corrige el combustible de un modelo, habría que cambiar todos sus vehículos.

Ajuste: el par (marca, modelo) es una entidad oculta. Se crea MODELOS con una clave sustituta modelo_id y UK (marca_id, nombre), y de paso MARCAS, para evitar incoherencias de texto libre (“Seat” frente a “SEAT”). VEHICULOS conserva solo modelo_id.

Tabla DF Claves FN
TRABAJOS num_reparacion, cod_mecanico → horas, precio_hora_aplicado (num_reparacion, cod_mecanico) FNBC
CONSUMOS num_reparacion, referencia → cantidad, precio_aplicado (num_reparacion, referencia) FNBC
FACTURAS num_factura → todo; num_reparacion → num_factura num_factura, num_reparacion FNBC
MECANICOS cod_mecanico → nombre, especialidad, precio_hora cod_mecanico FNBC

⚠️ Importante: precio_hora_aplicado en TRABAJOS no es redundante con MECANICOS.precio_hora: el primero es el precio histórico de esa reparación y el segundo el actual. No existe la DF cod_mecanico → precio_hora_aplicado. Lo mismo ocurre con precio_aplicado en CONSUMOS. Es el mismo patrón que precio_unitario en LINEAS_PEDIDO del esquema EMPRESA.

💡 Consejo: El importe de la factura no se guarda: se calcula sumando horas × precio_hora_aplicado de TRABAJOS y cantidad × precio_aplicado de CONSUMOS. Si por rendimiento o por exigencia legal se quisiera congelar, sería una desnormalización controlada.

3.5 Esquema relacional final

CLIENTES (dni, nombre, telefono, email (N))

  • UK: email

MARCAS (marca_id, nombre)

  • UK: nombre

MODELOS (modelo_id, marca_id, nombre, combustible (N))

  • marca_id → MARCAS(marca_id) NO ACTION
  • UK: (marca_id, nombre); CHECK: combustible IN (‘GASOLINA’, ‘DIESEL’, ‘HIBRIDO’, ‘ELECTRICO’)

VEHICULOS (matricula, bastidor, color (N), anio (N), modelo_id, dni_cliente)

  • modelo_id → MODELOS(modelo_id) NO ACTION; dni_cliente → CLIENTES(dni) NO ACTION
  • UK: bastidor; CHECK: anio ≥ 1900

REPARACIONES (num_reparacion, matricula, fecha_entrada, fecha_salida (N), km, averia)

  • matricula → VEHICULOS(matricula) NO ACTION
  • CHECK: fecha_salida ≥ fecha_entrada; km ≥ 0

MECANICOS (cod_mecanico, nombre, especialidad, precio_hora)

  • CHECK: especialidad IN (‘MOTOR’, ‘ELECTRICIDAD’, ‘CARROCERIA’, ‘GENERAL’); precio_hora > 0

TRABAJOS (*num_reparacion*, *cod_mecanico*, horas, precio_hora_aplicado)

  • num_reparacion → REPARACIONES(num_reparacion) CASCADE; cod_mecanico → MECANICOS(cod_mecanico) NO ACTION
  • CHECK: horas > 0; precio_hora_aplicado > 0

PIEZAS (referencia, descripcion, precio, stock)

  • CHECK: precio ≥ 0; stock ≥ 0

CONSUMOS (*num_reparacion*, *referencia*, cantidad, precio_aplicado)

  • num_reparacion → REPARACIONES(num_reparacion) CASCADE; referencia → PIEZAS(referencia) NO ACTION
  • CHECK: cantidad > 0; precio_aplicado ≥ 0

FACTURAS (num_factura, num_reparacion, fecha, forma_pago)

  • num_reparacion → REPARACIONES(num_reparacion) NO ACTION (una reparación facturada no se puede borrar: la factura es un documento legal)
  • UK: num_reparacion; CHECK: forma_pago IN (‘EFECTIVO’, ‘TARJETA’, ‘TRANSFERENCIA’)

Observa la combinación de acciones: TRABAJOS y CONSUMOS se borran en cascada con su reparación, pero FACTURAS bloquea el borrado. Resultado: solo se pueden borrar reparaciones no facturadas (por ejemplo, un presupuesto rechazado), y entonces se llevan por delante sus trabajos y consumos.

Restricciones adicionales: solo se factura una reparación con fecha_salida no nula; la fecha de la factura no puede ser anterior a la salida; al usar una pieza debe haber stock suficiente.

3.6 Grafo relacional 

flowchart LR
    MODELOS -->|marca_id| MARCAS
    VEHICULOS -->|modelo_id| MODELOS
    VEHICULOS -->|dni_cliente| CLIENTES
    REPARACIONES -->|matricula| VEHICULOS
    TRABAJOS -->|num_reparacion| REPARACIONES
    TRABAJOS -->|cod_mecanico| MECANICOS
    CONSUMOS -->|num_reparacion| REPARACIONES
    CONSUMOS -->|referencia| PIEZAS
    FACTURAS -->|"num_reparacion (UK)"| REPARACIONES

4. Caso 3: Hospital

4.1 Enunciado

  1. Pacientes: número de historia clínica, DNI (único, pero los recién nacidos aún no tienen), nombre, apellidos, fecha de nacimiento y uno o varios teléfonos de contacto.
  2. Personal sanitario: código de empleado, nombre, apellidos y fecha de contratación. Todo el personal es médico o enfermero, nunca ambas cosas. De los médicos se guarda el número de colegiado (único) y su especialidad (código y nombre; cada médico tiene una). De los enfermeros, el turno (MAÑANA, TARDE, NOCHE) y la planta a la que están asignados.
  3. Plantas (número y nombre) y habitaciones: cada habitación se numera dentro de su planta (la 1 de la planta 3, la 2 de la planta 3…) y tiene un número de camas.
  4. Ingresos: un paciente ingresa en una habitación en una fecha, a cargo de un médico responsable; la fecha de alta se registra al salir. Un paciente puede ingresar muchas veces a lo largo de su vida.
  5. Citas de consultas externas: un médico atiende a un paciente en una fecha y hora, con un motivo. Un médico no atiende a dos pacientes a la vez, ni un paciente tiene dos citas a la vez. Cada día, cada médico pasa consulta en un único despacho.

4.2 Diagrama E/R

Primera parte: personal y jerarquía.

flowchart TB
    PERSONAL[PERSONAL] --- j1{{"es un<br/>(total, exclusiva)"}}
    j1 --- MEDICO[MEDICO]
    j1 --- ENFERMERO[ENFERMERO]
    PERSONAL --- pe_cod([<u>cod_empleado</u>])
    PERSONAL --- pe_nom([nombre])
    PERSONAL --- pe_ape([apellidos])
    PERSONAL --- pe_fc([fecha_contrato])
    MEDICO --- me_col([num_colegiado])
    ENFERMERO --- en_turno([turno])
    MEDICO ---|"(0,n)"| tiene_esp{tiene}
    tiene_esp ---|"(1,1)"| ESPECIALIDAD[ESPECIALIDAD]
    ENFERMERO ---|"(0,n)"| asignado{asignado}
    asignado ---|"(1,1)"| PLANTA[PLANTA]
    ESPECIALIDAD --- es_cod([<u>cod_especialidad</u>])
    ESPECIALIDAD --- es_nom([nombre])

⚠️ Importante: Fíjate en dónde va cada pareja. Un médico tiene exactamente una especialidad, así que el (1,1) va junto a ESPECIALIDAD; una especialidad tiene de 0 a n médicos, así que el (0,n) va junto a MEDICO. Colocarlas al revés es el fallo más frecuente con la convención del curso (módulo 06). Antes de transformar, relee cada pareja con la frase “una ocurrencia de la otra entidad se relaciona con (mín, máx) ocurrencias de esta”.

Segunda parte: pacientes, ingresos y citas.

flowchart LR
    classDef debil stroke-width:4px
    PLANTA[PLANTA] ---|"(1,1)"| contiene{contiene}:::debil
    contiene ---|"(1,n)"| HABITACION[HABITACION]:::debil
    PACIENTE[PACIENTE] ---|"(1,1)"| ingresa{ingresa}
    ingresa ---|"(0,n)"| INGRESO[INGRESO]
    HABITACION ---|"(1,1)"| aloja{aloja}
    aloja ---|"(0,n)"| INGRESO
    MEDICO[MEDICO] ---|"(1,1)"| responsable{responsable}
    responsable ---|"(0,n)"| INGRESO
    MEDICO ---|"(0,n)"| cita{cita}
    cita ---|"(0,n)"| PACIENTE
    cita --- ci_fh(((fecha_hora)))
    cita --- ci_mot([motivo])
    cita --- ci_desp([despacho])
    PLANTA --- pl_num([<u>num_planta</u>])
    PLANTA --- pl_nom([nombre])
    HABITACION --- ha_num([<u>num_habitacion</u> parcial])
    HABITACION --- ha_camas([num_camas])
    PACIENTE --- pa_nh([<u>num_historia</u>])
    PACIENTE --- pa_dni(["dni (0,1)"])
    PACIENTE --- pa_nom([nombre])
    PACIENTE --- pa_ape([apellidos])
    PACIENTE --- pa_fn([fecha_nac])
    PACIENTE --- pa_tel(((telefono)))
    INGRESO --- in_id([<u>ingreso_id</u>])
    INGRESO --- in_fi([fecha_ingreso])
    INGRESO --- in_fa(["fecha_alta (0,1)"])

4.3 Transformación

Elemento Decisión Justificación
Jerarquía PERSONAL (total, exclusiva) Tres tablas: PERSONAL, MEDICOS y ENFERMEROS con la misma PK, que en los subtipos es también FK Los subtipos tienen atributos y relaciones propias (solo MEDICO participa en citas e ingresos; solo ENFERMERO en asignado). Una sola tabla tendría muchas columnas nulas y no podría declarar las FK como NOT NULL
Teléfonos (multivaluado) Tabla TELEFONOS_PACIENTE 1FN
HABITACION (débil) PK (num_planta, num_habitacion) Identificador parcial + clave del fuerte
INGRESO Entidad con clave propia Un paciente ingresa muchas veces
cita (N:M con fecha_hora multivaluado) Tabla CITAS con (cod_medico, fecha, hora) como PK La fecha y hora diferencian las citas repetidas
dni de paciente UK que admite nulos Los recién nacidos aún no lo tienen

Con tres tablas para la jerarquía no se garantiza la cobertura total ni la exclusividad: nada impide que un código esté en MEDICOS y en ENFERMEROS a la vez, ni que esté solo en PERSONAL. Una técnica declarativa para la exclusividad es añadir a PERSONAL una columna discriminante tipo (‘M’ o ‘E’) con UK (cod_empleado, tipo), y en cada subtipo una columna tipo fija con CHECK (tipo = ‘M’) y una FK compuesta (cod_empleado, tipo) → PERSONAL(cod_empleado, tipo). La totalidad (que todo empleado tenga fila en su subtipo) requiere ya un disparador.

4.4 Dependencias funcionales y forma normal

El primer esquema de CITAS, obtenido directamente de la relación cita, es:

CITAS (*cod_medico*, fecha, hora, num_historia, motivo, despacho)

Sus DF, según el requisito 5:

cod_medico, fecha, hora   → num_historia, motivo, despacho
num_historia, fecha, hora → cod_medico
cod_medico, fecha         → despacho

Claves: {cod_medico, fecha, hora} y {num_historia, fecha, hora}. La DF cod_medico, fecha → despacho es parcial (despacho no es primo y depende de una parte de una clave): CITAS está en 1FN pero no en 2FN. El despacho se repetiría en todas las citas del médico ese día. El resto de tablas:

Tabla DF relevantes Claves FN
PACIENTES num_historia → todo; dni → num_historia num_historia, dni FNBC
PERSONAL / MEDICOS / ENFERMEROS cod_empleado → todo; num_colegiado → cod_empleado cod_empleado (y num_colegiado en MEDICOS) FNBC
HABITACIONES num_planta, num_habitacion → num_camas (num_planta, num_habitacion) FNBC
INGRESOS ingreso_id → todo ingreso_id FNBC

4.5 Ajuste de normalización

Se separa el despacho en una tabla de turnos de consulta (es el ejercicio 11 del módulo 20):

TURNOS_CONSULTA (*cod_medico*, fecha, despacho)

CITAS (*cod_medico*, fecha, hora, num_historia, motivo), con UK (num_historia, fecha, hora) y FK (cod_medico, fecha) → TURNOS_CONSULTA.

La FK aporta además una regla de negocio gratis: no se puede dar una cita a un médico un día que no pasa consulta.

4.6 Esquema relacional final

ESPECIALIDADES (cod_especialidad, nombre)

  • UK: nombre

PLANTAS (num_planta, nombre)

HABITACIONES (*num_planta*, num_habitacion, num_camas)

  • num_planta → PLANTAS(num_planta) CASCADE
  • CHECK: num_camas BETWEEN 1 AND 4

PERSONAL (cod_empleado, nombre, apellidos, fecha_contrato)

MEDICOS (*cod_empleado*, num_colegiado, cod_especialidad)

  • cod_empleado → PERSONAL(cod_empleado) CASCADE
  • cod_especialidad → ESPECIALIDADES(cod_especialidad) NO ACTION
  • UK: num_colegiado

ENFERMEROS (*cod_empleado*, turno, num_planta)

  • cod_empleado → PERSONAL(cod_empleado) CASCADE
  • num_planta → PLANTAS(num_planta) NO ACTION
  • CHECK: turno IN (‘MAÑANA’, ‘TARDE’, ‘NOCHE’)

PACIENTES (num_historia, dni (N), nombre, apellidos, fecha_nac)

  • UK: dni

TELEFONOS_PACIENTE (*num_historia*, telefono)

  • num_historia → PACIENTES(num_historia) CASCADE

INGRESOS (ingreso_id, num_historia, num_planta, num_habitacion, cod_medico, fecha_ingreso, fecha_alta (N))

  • num_historia → PACIENTES(num_historia) NO ACTION
  • (num_planta, num_habitacion) → HABITACIONES(num_planta, num_habitacion) NO ACTION
  • cod_medico → MEDICOS(cod_empleado) NO ACTION
  • CHECK: fecha_alta ≥ fecha_ingreso

TURNOS_CONSULTA (*cod_medico*, fecha, despacho)

  • cod_medico → MEDICOS(cod_empleado) CASCADE

CITAS (*cod_medico*, *fecha*, hora, num_historia, motivo (N))

  • (cod_medico, fecha) → TURNOS_CONSULTA(cod_medico, fecha) CASCADE
  • num_historia → PACIENTES(num_historia) CASCADE
  • UK: (num_historia, fecha, hora)

¿Por qué CASCADE de PERSONAL a MEDICOS? Porque el subtipo no tiene sentido sin el supertipo. Pero como MEDICOS es referenciado por INGRESOS con NO ACTION, en la práctica no se podrá borrar a un médico con ingresos a su cargo: Oracle comprueba toda la cadena.

Restricciones adicionales: un paciente no puede tener dos ingresos sin alta a la vez; el número de pacientes ingresados a la vez en una habitación no puede superar sus camas; exclusividad y totalidad de la jerarquía (ver 4.3).

4.7 Grafo relacional 

flowchart LR
    MEDICOS -->|cod_empleado| PERSONAL
    ENFERMEROS -->|cod_empleado| PERSONAL
    MEDICOS -->|cod_especialidad| ESPECIALIDADES
    ENFERMEROS -->|num_planta| PLANTAS
    HABITACIONES -->|num_planta| PLANTAS
    TELEFONOS_PACIENTE -->|num_historia| PACIENTES
    INGRESOS -->|num_historia| PACIENTES
    INGRESOS -->|"num_planta, num_habitacion"| HABITACIONES
    INGRESOS -->|cod_medico| MEDICOS
    TURNOS_CONSULTA -->|cod_medico| MEDICOS
    CITAS -->|"cod_medico, fecha"| TURNOS_CONSULTA
    CITAS -->|num_historia| PACIENTES

5. Caso 4: Liga deportiva

5.1 Enunciado

Una federación gestiona una liga de fútbol:

  1. Cada temporada (por ejemplo, “2026-27”) tiene una fecha de inicio y de fin.
  2. Equipos: código, nombre (único), ciudad, año de fundación y estadio en el que juegan como local. De cada estadio se guarda nombre, ciudad y aforo; dos equipos pueden compartir estadio.
  3. Jugadores: código, nombre, fecha de nacimiento, nacionalidad y posición. En cada temporada, un jugador juega en un único equipo con un dorsal; dos jugadores del mismo equipo no pueden tener el mismo dorsal en la misma temporada.
  4. Partidos: se disputan en jornadas; en cada jornada un equipo juega un solo partido. En cada partido hay un equipo local y otro visitante (distintos), un árbitro, una fecha y el resultado oficial (goles de cada equipo, según el acta). Los partidos se juegan siempre en el estadio del equipo local. Liga a doble vuelta: cada pareja de equipos se enfrenta una vez en cada campo.
  5. Goles: de cada gol se guarda el minuto, el jugador que lo marca y el tipo (NORMAL, PENALTI, PROPIA).
  6. Árbitros: código, nombre y colegio arbitral.

5.2 Diagrama E/R 

flowchart LR
    classDef debil stroke-width:4px
    EQUIPO[EQUIPO] ---|"(0,n)"| juega_en{juega_en}
    juega_en ---|"(1,1)"| ESTADIO[ESTADIO]
    EQUIPO ---|"(1,1) local"| es_local{local}
    es_local ---|"(0,n)"| PARTIDO[PARTIDO]
    EQUIPO ---|"(1,1) visitante"| es_visit{visitante}
    es_visit ---|"(0,n)"| PARTIDO
    ARBITRO[ARBITRO] ---|"(1,1)"| arbitra{arbitra}
    arbitra ---|"(0,n)"| PARTIDO
    TEMPORADA[TEMPORADA] ---|"(1,1)"| de_temp{pertenece}
    de_temp ---|"(0,n)"| PARTIDO
    PARTIDO ---|"(1,1)"| tiene_gol{tiene}:::debil
    tiene_gol ---|"(0,n)"| GOL[GOL]:::debil
    JUGADOR[JUGADOR] ---|"(1,1)"| marca{marca}
    marca ---|"(0,n)"| GOL
    JUGADOR ---|"(0,n)"| ficha{ficha}
    EQUIPO ---|"(0,1)"| ficha
    TEMPORADA ---|"(0,n)"| ficha
    ficha --- fi_dorsal([dorsal])
    PARTIDO --- pa_id([<u>partido_id</u>])
    PARTIDO --- pa_jor([jornada])
    PARTIDO --- pa_fecha([fecha])
    PARTIDO --- pa_gl([goles_local])
    PARTIDO --- pa_gv([goles_visitante])
    PARTIDO --- pa_est([estadio])
    GOL --- go_num([<u>num_gol</u> parcial])
    GOL --- go_min([minuto])
    GOL --- go_tipo([tipo])
    EQUIPO --- eq_id([<u>equipo_id</u>])
    EQUIPO --- eq_nom([nombre])
    JUGADOR --- ju_id([<u>jugador_id</u>])
    TEMPORADA --- te_id([<u>temporada_id</u>])

(Se omiten algunos atributos simples para no sobrecargar el diagrama.) Puntos clave:

  • Dos relaciones entre EQUIPO y PARTIDO, con papeles local y visitante.
  • ficha es una relación ternaria JUGADOR-EQUIPO-TEMPORADA. La cardinalidad (0,1) junto a EQUIPO expresa que, para un par jugador-temporada, hay como mucho un equipo: es una ternaria N:1:N.
  • GOL es débil respecto de PARTIDO (se numeran 1, 2, 3… en cada partido).
  • El borrador incluyó estadio como atributo de PARTIDO.

5.3 Transformación

Elemento Resultado Justificación
juega_en (N:1) EQUIPOS.estadio_id NOT NULL  
local, visitante Dos FK en PARTIDOS: equipo_local, equipo_visitante Una FK por papel
arbitra, pertenece FK arbitro_id, temporada_id  
ficha (ternaria N:1:N) FICHAS(jugador_id, temporada_id, equipo_id, dorsal) La clave de una ternaria con un lado 1 es la de los otros dos lados
GOL (débil) GOLES(partido_id, num_gol, minuto, tipo, jugador_id)  

5.4 Dependencias funcionales y forma normal

PARTIDOS (borrador): (partido_id, temporada_id, jornada, fecha, equipo_local, equipo_visitante, arbitro_id, goles_local, goles_visitante, estadio)

partido_id                               → todos los demás
temporada_id, jornada, equipo_local      → partido_id
temporada_id, jornada, equipo_visitante  → partido_id
temporada_id, equipo_local, equipo_visitante → partido_id   (doble vuelta)
equipo_local                             → estadio          (requisito 4)

Claves candidatas: partido_id, {temporada_id, jornada, equipo_local}, {temporada_id, jornada, equipo_visitante} y {temporada_id, equipo_local, equipo_visitante}. ¡Cuatro! La DF equipo_local → estadio tiene un determinante que no es superclave y estadio no es primo: no está en 3FN (es transitiva: partido_id → equipo_local → estadio).

FICHAS: jugador_id, temporada_id → equipo_id, dorsal y equipo_id, temporada_id, dorsal → jugador_id. Claves: {jugador_id, temporada_id} y {equipo_id, temporada_id, dorsal}. Los dos determinantes son clave: FNBC.

GOLES: partido_id, num_gol → minuto, tipo, jugador_id. FNBC.

5.5 Ajustes de normalización

  1. Eliminar estadio de PARTIDOS: se obtiene a través del equipo local (EQUIPOS.estadio_id). Si el reglamento admitiera excepciones (campo neutral en una final, cierre del estadio por sanción), la DF no existiría y la columna debería quedarse en PARTIDOS: la decisión depende exclusivamente del requisito.
  2. ¿Son derivados los goles del partido? Podrían calcularse contando GOLES y viendo el equipo de cada goleador en FICHAS (cuidado con los goles en propia puerta). Pero el requisito dice que el resultado oficial es el del acta, y el detalle de goles puede no estar completo. Se mantienen goles_local y goles_visitante como datos propios, no derivados. Si se decidiera derivarlos, guardarlos sería una desnormalización.

5.6 Esquema relacional final

TEMPORADAS (temporada_id, fecha_inicio, fecha_fin)

  • CHECK: fecha_fin > fecha_inicio

ESTADIOS (estadio_id, nombre, ciudad, aforo)

  • CHECK: aforo > 0

EQUIPOS (equipo_id, nombre, ciudad, anio_fundacion (N), estadio_id)

  • estadio_id → ESTADIOS(estadio_id) NO ACTION
  • UK: nombre

JUGADORES (jugador_id, nombre, fecha_nac, nacionalidad, posicion)

  • CHECK: posicion IN (‘PORTERO’, ‘DEFENSA’, ‘CENTROCAMPISTA’, ‘DELANTERO’)

ARBITROS (arbitro_id, nombre, colegio)

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

  • jugador_id → JUGADORES NO ACTION; temporada_id → TEMPORADAS NO ACTION; equipo_id → EQUIPOS NO ACTION
  • UK: (equipo_id, temporada_id, dorsal); CHECK: dorsal BETWEEN 1 AND 99

PARTIDOS (partido_id, temporada_id, jornada, fecha, equipo_local, equipo_visitante, arbitro_id, goles_local (N), goles_visitante (N))

  • temporada_id → TEMPORADAS NO ACTION; equipo_local → EQUIPOS(equipo_id) NO ACTION; equipo_visitante → EQUIPOS(equipo_id) NO ACTION; arbitro_id → ARBITROS NO ACTION
  • UK: (temporada_id, jornada, equipo_local); UK: (temporada_id, jornada, equipo_visitante); UK: (temporada_id, equipo_local, equipo_visitante)
  • CHECK: equipo_local <> equipo_visitante; goles_local ≥ 0; goles_visitante ≥ 0; jornada ≥ 1

GOLES (*partido_id*, num_gol, jugador_id, minuto, tipo)

  • partido_id → PARTIDOS CASCADE; jugador_id → JUGADORES NO ACTION
  • CHECK: minuto BETWEEN 1 AND 130; tipo IN (‘NORMAL’, ‘PENALTI’, ‘PROPIA’)

Los goles se dejan nulos mientras el partido no se ha jugado. Restricciones adicionales: un equipo no puede jugar como local en un partido y como visitante en otro de la misma jornada (las UK solo lo impiden dentro del mismo papel); el goleador debe tener ficha esa temporada en uno de los dos equipos; la fecha del partido debe estar dentro de la temporada.

5.7 Grafo relacional 

flowchart LR
    EQUIPOS -->|estadio_id| ESTADIOS
    FICHAS -->|jugador_id| JUGADORES
    FICHAS -->|temporada_id| TEMPORADAS
    FICHAS -->|equipo_id| EQUIPOS
    PARTIDOS -->|temporada_id| TEMPORADAS
    PARTIDOS -->|equipo_local| EQUIPOS
    PARTIDOS -->|equipo_visitante| EQUIPOS
    PARTIDOS -->|arbitro_id| ARBITROS
    GOLES -->|partido_id| PARTIDOS
    GOLES -->|jugador_id| JUGADORES

6. Caso 5: Plataforma de vídeo en streaming

6.1 Enunciado

  1. Cuentas: identificador, correo (único), hash de la contraseña, país y fecha de alta.
  2. Planes: código, nombre (único), precio mensual actual, calidad máxima (HD, FULLHD, 4K) y número de pantallas simultáneas. Una cuenta tiene a lo largo del tiempo varias suscripciones, cada una a un plan, con fecha de inicio, fecha de fin (vacía si está activa) y el precio mensual que paga (que se mantiene aunque el plan suba).
  3. Cada cuenta tiene de 1 a 5 perfiles, numerados dentro de la cuenta, con nombre y si es infantil.
  4. Contenidos: identificador, título, año y edad mínima recomendada (0, 7, 12, 16, 18). Cada contenido es una película (con duración) o una serie (con estado EN_EMISION o FINALIZADA). Las series tienen temporadas (numeradas dentro de la serie, con año) y las temporadas, episodios (numerados dentro de la temporada, con título y duración).
  5. Cada contenido tiene uno o varios géneros (código y nombre).
  6. Visualizaciones: un perfil ve una película o un episodio en un momento dado, y se guarda hasta qué segundo llegó.
  7. Valoraciones: un perfil puede valorar un contenido de 1 a 5, una sola vez (se puede cambiar).

6.2 Diagrama E/R 

flowchart TB
    classDef debil stroke-width:4px
    CUENTA[CUENTA] ---|"(1,1)"| tiene_p{tiene}:::debil
    tiene_p ---|"(1,5)"| PERFIL[PERFIL]:::debil
    CUENTA ---|"(0,n)"| suscribe{suscribe}
    suscribe ---|"(0,n)"| PLAN[PLAN]
    suscribe --- su_fi(((fecha_inicio)))
    suscribe --- su_ff(["fecha_fin (0,1)"])
    suscribe --- su_precio([precio_pagado])
    CONTENIDO[CONTENIDO] --- j1{{"es un<br/>(total, exclusiva)"}}
    j1 --- PELICULA[PELICULA]
    j1 --- SERIE[SERIE]
    SERIE ---|"(1,1)"| tiene_t{tiene}:::debil
    tiene_t ---|"(1,n)"| TEMPORADA[TEMPORADA]:::debil
    TEMPORADA ---|"(1,1)"| tiene_e{tiene}:::debil
    tiene_e ---|"(1,n)"| EPISODIO[EPISODIO]:::debil
    CONTENIDO ---|"(1,n)"| clasifica{clasifica}
    clasifica ---|"(1,n)"| GENERO[GENERO]
    PERFIL ---|"(0,n)"| valora{valora}
    valora ---|"(0,n)"| CONTENIDO
    valora --- va_punt([puntuacion])
    PERFIL ---|"(0,n)"| ve_peli{ve}
    ve_peli ---|"(0,n)"| PELICULA
    PERFIL ---|"(0,n)"| ve_epi{ve}
    ve_epi ---|"(0,n)"| EPISODIO
    PERFIL --- pf_num([<u>num_perfil</u> parcial])
    TEMPORADA --- te_num([<u>num_temporada</u> parcial])
    EPISODIO --- ep_num([<u>num_episodio</u> parcial])
    CONTENIDO --- co_id([<u>contenido_id</u>])
    CUENTA --- cu_id([<u>cuenta_id</u>])

Las dos relaciones ve comparten atributos (momento, segundo_parada), omitidos en el dibujo. Una cadena de debilidad CUENTA → PERFIL y SERIE → TEMPORADA → EPISODIO hace que la clave de EPISODIO sea (serie, num_temporada, num_episodio).

6.3 Transformación y decisiones

Elemento Decisión Justificación
Jerarquía CONTENIDO Supertipo + subtipos (tres tablas) CONTENIDO participa en clasifica y valora; PELICULA y SERIE tienen relaciones propias
suscribe (N:M con fecha multivaluada) SUSCRIPCIONES(cuenta_id, fecha_inicio, plan_id, …) Una cuenta no inicia dos suscripciones el mismo día; el plan no forma parte de la clave
PERFIL débil PK (cuenta_id, num_perfil)  
TEMPORADA, EPISODIO débiles PK (serie_id, num_temporada) y (serie_id, num_temporada, num_episodio) Cadena de identificación
Dos relaciones ve Una tabla VISUALIZACIONES con dos referencias opcionales y un CHECK de exclusividad (arco exclusivo) Comparten atributos y casi todas las consultas (“¿qué ha visto este perfil?”) las necesitan juntas

Alternativas para ve y su valoración:

Opción Ventajas Inconvenientes
Dos tablas (VER_PELICULAS, VER_EPISODIOS) FK NOT NULL, sin CHECK especial Consultas con UNION para el historial
Una tabla con arco exclusivo Historial en una sola tabla Columnas nulas y un CHECK
Supertipo VIDEO (película o episodio) Una sola FK NOT NULL Cambia la jerarquía: los episodios no son contenidos

6.4 Dependencias funcionales y forma normal

El borrador de VISUALIZACIONES copiaba, “para no tener que hacer joins”, el título y la duración:

VISUALIZACIONES (visualizacion_id, cuenta_id, num_perfil, pelicula_id, serie_id, num_temporada, num_episodio, momento, segundo_parada, titulo, duracion)

Con pelicula_id → titulo, duracion (vía PELICULAS y CONTENIDOS) y serie_id, num_temporada, num_episodio → titulo, duracion, hay dependencias transitivas: no está en 3FN. Al cambiar el título de un episodio, habría millones de filas desactualizadas. Ajuste: eliminar titulo y duracion.

Otra comprobación importante, en SUSCRIPCIONES:

cuenta_id, fecha_inicio → plan_id, fecha_fin, precio_pagado
plan_id ↛ precio_pagado      (el precio pagado es el del momento de contratar)

Al no existir plan_id → precio_pagado, no hay dependencia transitiva: FNBC. Si se hubiera copiado calidad_max del plan en la suscripción, sí la habría (plan_id → calidad_max).

Tabla Claves FN
CUENTAS cuenta_id, email FNBC
PLANES plan_id, nombre FNBC
PERFILES (cuenta_id, num_perfil) FNBC
CONTENIDOS, PELICULAS, SERIES contenido_id FNBC
TEMPORADAS, EPISODIOS sus PK compuestas FNBC
CLASIFICACIONES (contenido_id, genero_id) FNBC
VALORACIONES (cuenta_id, num_perfil, contenido_id) FNBC
VISUALIZACIONES (ajustada) visualizacion_id FNBC

6.5 Esquema relacional final

CUENTAS (cuenta_id, email, hash_password, pais, fecha_alta)

  • UK: email

PLANES (plan_id, nombre, precio_mensual, calidad_max, pantallas)

  • UK: nombre; CHECK: calidad_max IN (‘HD’, ‘FULLHD’, ‘4K’); pantallas BETWEEN 1 AND 6; precio_mensual ≥ 0

SUSCRIPCIONES (*cuenta_id*, fecha_inicio, plan_id, fecha_fin (N), precio_pagado)

  • cuenta_id → CUENTAS CASCADE; plan_id → PLANES NO ACTION
  • CHECK: fecha_fin ≥ fecha_inicio; precio_pagado ≥ 0

PERFILES (*cuenta_id*, num_perfil, nombre, infantil)

  • cuenta_id → CUENTAS CASCADE
  • CHECK: num_perfil BETWEEN 1 AND 5; infantil IN (‘S’, ‘N’)

CONTENIDOS (contenido_id, titulo, anio, edad_minima)

  • CHECK: edad_minima IN (0, 7, 12, 16, 18)

PELICULAS (*contenido_id*, duracion_min)

  • contenido_id → CONTENIDOS CASCADE; CHECK: duracion_min > 0

SERIES (*contenido_id*, estado)

  • contenido_id → CONTENIDOS CASCADE; CHECK: estado IN (‘EN_EMISION’, ‘FINALIZADA’)

TEMPORADAS (*serie_id*, num_temporada, anio)

  • serie_id → SERIES(contenido_id) CASCADE

EPISODIOS (*serie_id*, *num_temporada*, num_episodio, titulo, duracion_min)

  • (serie_id, num_temporada) → TEMPORADAS CASCADE

GENEROS (genero_id, nombre)

  • UK: nombre

CLASIFICACIONES (*contenido_id*, *genero_id*)

  • contenido_id → CONTENIDOS CASCADE; genero_id → GENEROS NO ACTION

VALORACIONES (*cuenta_id*, *num_perfil*, *contenido_id*, puntuacion)

  • (cuenta_id, num_perfil) → PERFILES CASCADE; contenido_id → CONTENIDOS CASCADE
  • CHECK: puntuacion BETWEEN 1 AND 5

VISUALIZACIONES (visualizacion_id, cuenta_id, num_perfil, pelicula_id (N), serie_id (N), num_temporada (N), num_episodio (N), momento, segundo_parada)

  • (cuenta_id, num_perfil) → PERFILES CASCADE
  • pelicula_id → PELICULAS(contenido_id) CASCADE
  • (serie_id, num_temporada, num_episodio) → EPISODIOS CASCADE
  • CHECK (arco exclusivo): (pelicula_id IS NOT NULL AND serie_id IS NULL AND num_temporada IS NULL AND num_episodio IS NULL) OR (pelicula_id IS NULL AND serie_id IS NOT NULL AND num_temporada IS NOT NULL AND num_episodio IS NOT NULL)
  • CHECK: segundo_parada ≥ 0

El atributo infantil se ha modelado como CHAR(1) con CHECK (‘S’, ‘N’) para que el diseño funcione en Oracle 19c y 21c.

⚠️ Importante: Disponible desde Oracle 23ai: el tipo de datos BOOLEAN en columnas de tabla. En versiones anteriores usa CHAR(1) con un CHECK o NUMBER(1) con valores 0 y 1.

Restricciones adicionales: como mucho una suscripción activa (fecha_fin nula) por cuenta; las suscripciones de una cuenta no se solapan; un perfil infantil no puede ver contenidos de edad mínima superior a 12.

6.6 Grafo relacional 

flowchart LR
    SUSCRIPCIONES -->|cuenta_id| CUENTAS
    SUSCRIPCIONES -->|plan_id| PLANES
    PERFILES -->|cuenta_id| CUENTAS
    PELICULAS -->|contenido_id| CONTENIDOS
    SERIES -->|contenido_id| CONTENIDOS
    TEMPORADAS -->|serie_id| SERIES
    EPISODIOS -->|"serie_id, num_temporada"| TEMPORADAS
    CLASIFICACIONES -->|contenido_id| CONTENIDOS
    CLASIFICACIONES -->|genero_id| GENEROS
    VALORACIONES -->|"cuenta_id, num_perfil"| PERFILES
    VALORACIONES -->|contenido_id| CONTENIDOS
    VISUALIZACIONES -->|"cuenta_id, num_perfil"| PERFILES
    VISUALIZACIONES -.->|"pelicula_id (N)"| PELICULAS
    VISUALIZACIONES -.->|"serie_id, num_temporada, num_episodio (N)"| EPISODIOS

Las flechas discontinuas representan las dos claves ajenas opcionales del arco exclusivo.

7. Caso 6: El esquema EMPRESA completo

Este caso reconstruye el diseño del esquema que usarás en todo el bloque de SQL, y lo compara con su script de creación, empresa_crear.sql.

7.1 Enunciado

Una empresa que vende productos informáticos quiere gestionar su organización y sus ventas:

  1. Departamentos: código, nombre (único), localidad (opcional) y presupuesto (opcional, no negativo).
  2. Empleados: código, nombre, apellidos, correo (único), teléfono (opcional), fecha de alta, oficio, salario (positivo) y comisión (opcional, no negativa). Cada empleado pertenece como mucho a un departamento (un becario recién llegado puede no tenerlo) y tiene como mucho un jefe, que es otro empleado.
  3. Clientes: código, nombre, NIF (único), correo, teléfono, ciudad y provincia (estos cuatro, opcionales), fecha de alta (por defecto, la del día) y límite de crédito (por defecto 3000, no negativo). Un cliente puede tener asignado un empleado como comercial.
  4. Categorías de productos organizadas en jerarquía: cada categoría puede tener una categoría padre. Código y nombre (único).
  5. Productos: código, nombre, descripción (opcional), precio (no negativo), stock (por defecto 0, no negativo), fecha de alta (opcional) y si está descatalogado (S/N, por defecto N). Cada producto pertenece a una categoría.
  6. Pedidos: código, fecha (por defecto, la del día), fecha de envío (opcional, no anterior a la del pedido) y estado (PENDIENTE, ENVIADO, ENTREGADO, CANCELADO; por defecto PENDIENTE). Cada pedido es de un cliente y puede haberlo gestionado un empleado (los pedidos en línea no lo tienen).
  7. Líneas de pedido: cada pedido tiene varias líneas numeradas 1, 2, 3…; cada línea es de un producto, con cantidad (positiva), precio unitario de venta y porcentaje de descuento (0 a 100, por defecto 0). Si se borra un pedido, se borran sus líneas.
  8. Proyectos internos: código, nombre (único), departamento responsable (opcional), fecha de inicio, fecha de fin (opcional, no anterior al inicio) y presupuesto (opcional, no negativo).
  9. Asignaciones: un empleado puede trabajar en varios proyectos y un proyecto tener varios empleados; se guarda la fecha de asignación (por defecto, la del día), las horas dedicadas (por defecto 0, no negativas) y la función (opcional). Si se borra un proyecto, se borran sus asignaciones.

7.2 Diagrama E/R

Por claridad, el diagrama se divide en dos partes. Organización y proyectos:

flowchart LR
    EMPLEADO[EMPLEADO] ---|"(0,n)"| pertenece{pertenece}
    pertenece ---|"(0,1)"| DEPARTAMENTO[DEPARTAMENTO]
    EMPLEADO ---|"(0,1) jefe"| dirige{dirige}
    dirige ---|"(0,n) subordinado"| EMPLEADO
    DEPARTAMENTO ---|"(0,1)"| desarrolla{desarrolla}
    desarrolla ---|"(0,n)"| PROYECTO[PROYECTO]
    EMPLEADO ---|"(0,n)"| trabaja{trabaja_en}
    trabaja ---|"(0,n)"| PROYECTO
    trabaja --- tr_fecha([fecha_asignacion])
    trabaja --- tr_horas([horas])
    trabaja --- tr_funcion(["funcion (0,1)"])
    DEPARTAMENTO --- de_id([<u>dept_id</u>])
    DEPARTAMENTO --- de_nom([nombre])
    DEPARTAMENTO --- de_loc(["localidad (0,1)"])
    DEPARTAMENTO --- de_pres(["presupuesto (0,1)"])
    EMPLEADO --- em_id([<u>emp_id</u>])
    EMPLEADO --- em_nom([nombre])
    EMPLEADO --- em_ape([apellidos])
    EMPLEADO --- em_email([email])
    EMPLEADO --- em_tel(["telefono (0,1)"])
    EMPLEADO --- em_alta([fecha_alta])
    EMPLEADO --- em_ofi([oficio])
    EMPLEADO --- em_sal([salario])
    EMPLEADO --- em_com(["comision (0,1)"])
    PROYECTO --- py_id([<u>proyecto_id</u>])
    PROYECTO --- py_nom([nombre])
    PROYECTO --- py_ini([fecha_inicio])
    PROYECTO --- py_fin(["fecha_fin (0,1)"])
    PROYECTO --- py_pres(["presupuesto (0,1)"])

Ventas:

flowchart LR
    classDef debil stroke-width:4px
    EMPLEADO[EMPLEADO] ---|"(0,1)"| atiende{atiende}
    atiende ---|"(0,n)"| CLIENTE[CLIENTE]
    CLIENTE ---|"(1,1)"| realiza{realiza}
    realiza ---|"(0,n)"| PEDIDO[PEDIDO]
    EMPLEADO ---|"(0,1)"| gestiona{gestiona}
    gestiona ---|"(0,n)"| PEDIDO
    PEDIDO ---|"(1,1)"| contiene{contiene}:::debil
    contiene ---|"(1,n)"| LINEA[LINEA_PEDIDO]:::debil
    LINEA ---|"(0,n)"| incluye{incluye}
    incluye ---|"(1,1)"| PRODUCTO[PRODUCTO]
    PRODUCTO ---|"(0,n)"| clasifica{clasifica}
    clasifica ---|"(1,1)"| CATEGORIA[CATEGORIA]
    CATEGORIA ---|"(0,1) padre"| subcat{subcategoria}
    subcat ---|"(0,n) hija"| CATEGORIA
    LINEA --- li_num([<u>num_linea</u> parcial])
    LINEA --- li_cant([cantidad])
    LINEA --- li_precio([precio_unitario])
    LINEA --- li_desc([descuento])
    PEDIDO --- pe_id([<u>pedido_id</u>])
    PEDIDO --- pe_fecha([fecha_pedido])
    PEDIDO --- pe_envio(["fecha_envio (0,1)"])
    PEDIDO --- pe_estado([estado])
    CLIENTE --- cl_id([<u>cliente_id</u>])
    CLIENTE --- cl_nif([nif])
    PRODUCTO --- pr_id([<u>producto_id</u>])
    PRODUCTO --- pr_desc([descatalogado])
    CATEGORIA --- ca_id([<u>categoria_id</u>])

(En el diagrama de ventas se omiten algunos atributos ya enumerados en el enunciado.) Observa, en la relación gestiona, el (0,1) junto a EMPLEADO: un pedido lo gestiona como mucho un empleado (los pedidos en línea 5014 y 5023 no tienen). Y en atiende: los clientes 1009 y 1013 no tienen comercial.

7.3 Transformación y comparación con el script

Elemento E/R Transformación En empresa_crear.sql
DEPARTAMENTO DEPARTAMENTOS(dept_id, nombre, localidad, presupuesto) pk_departamentos, uq_dep_nombre, nn_dep_nombre, ck_dep_presupuesto
EMPLEADO EMPLEADOS(emp_id, …, comision) pk_empleados, uq_emp_email, ck_emp_salario, ck_emp_comision, NOT NULL en todos salvo telefono, comision, jefe_id y dept_id
pertenece (N:1, mínima 0) FK dept_id nullable en EMPLEADOS fk_emp_dept (sin acción: NO ACTION)
dirige (autorrelación 1:N) FK jefe_id → EMPLEADOS en la misma tabla, nullable fk_emp_jefe
CLIENTE + atiende CLIENTES con FK comercial_id nullable fk_cli_comercial, uq_cli_nif, DEFAULT en fecha_alta y limite_credito
CATEGORIA + subcategoria FK reflexiva categoria_padre_id nullable fk_cat_padre, uq_cat_nombre
PRODUCTO + clasifica (mínima 1) FK categoria_id NOT NULL fk_pro_categoria, nn_pro_categoria, ck_pro_descatalogado
PEDIDO + realiza + gestiona FK cliente_id NOT NULL, empleado_id nullable fk_ped_cliente, fk_ped_empleado, ck_ped_estado, ck_ped_fechas
LINEA_PEDIDO (débil) PK (pedido_id, num_linea); FK a PEDIDOS con CASCADE pk_lineas_pedido, fk_lin_pedido ... ON DELETE CASCADE
incluye (N:1, mínima 1) FK producto_id NOT NULL fk_lin_producto, nn_lin_producto
PROYECTO + desarrolla (mínima 0) FK dept_id nullable fk_pry_dept, uq_pry_nombre, ck_pry_fechas
trabaja_en (N:M con atributos) ASIGNACIONES(emp_id, proyecto_id, fecha_asignacion, horas, funcion) pk_asignaciones; fk_asi_proyecto ... ON DELETE CASCADE; fk_asi_emp NO ACTION

El diseño lógico coincide exactamente con el script. Algunas decisiones que conviene entender:

  • num_linea en la clave de LINEAS_PEDIDO, en lugar de (pedido_id, producto_id): permite repetir un producto en dos líneas de un pedido (por ejemplo, con descuentos distintos). Con (pedido_id, producto_id) como clave, esa posibilidad desaparecería y la tabla seguiría estando en FNBC.
  • CASCADE asimétrico en ASIGNACIONES: borrar un proyecto borra sus asignaciones, pero un empleado con asignaciones no se puede borrar (NO ACTION). Se protege el historial de horas trabajadas.
  • descatalogado CHAR(1) con CHECK (‘S’, ‘N’) en vez de BOOLEAN, para que el script funcione en Oracle 19c y 21c.
  • ck_ped_fechas CHECK (fecha_envio >= fecha_pedido): con fecha_envio nula, la condición vale UNKNOWN y el CHECK se acepta (un CHECK solo rechaza las filas en las que la condición es FALSE). Por eso no hace falta escribir fecha_envio IS NULL OR ....

7.4 Dependencias funcionales y forma normal

Tabla DF no triviales (aparte de las de la PK) Claves candidatas FN
DEPARTAMENTOS nombre → dept_id dept_id, nombre FNBC
EMPLEADOS email → emp_id emp_id, email FNBC
CLIENTES nif → cliente_id cliente_id, nif FNBC (ver nota)
CATEGORIAS nombre → categoria_id categoria_id, nombre FNBC
PRODUCTOS — producto_id FNBC
PEDIDOS — pedido_id FNBC
LINEAS_PEDIDO — (pedido_id, num_linea) FNBC
PROYECTOS nombre → proyecto_id proyecto_id, nombre FNBC
ASIGNACIONES — (emp_id, proyecto_id) FNBC

Nota sobre CLIENTES: si se aceptara la DF ciudad → provincia, la tabla tendría una dependencia transitiva (cliente_id → ciudad → provincia) y estaría solo en 2FN. Pero, como viste en el módulo 19, el nombre de un municipio no determina la provincia (hay municipios homónimos en provincias distintas), así que no es una DF y la tabla está en FNBC. Si la empresa necesitara datos geográficos fiables, lo correcto sería una tabla MUNICIPIOS con código oficial y una FK desde CLIENTES.

Otras DF que no existen, aunque los datos de prueba no las contradigan o lo parezca:

  • oficio → salario en EMPLEADOS: refutada por los datos (módulo 19).
  • producto_id → precio_unitario en LINEAS_PEDIDO: el precio de venta se congela en la línea.
  • jefe_id → dept_id (que todos los subordinados de un jefe estén en el mismo departamento): refutada por los datos, porque los directores de los departamentos 20 a 60 dependen de la directora general (empleada 100, del departamento 10).

7.5 Lo que el esquema no garantiza

Estas reglas razonables no están en el script. Algunas se podrían añadir con un CHECK; otras no se pueden expresar con restricciones declarativas:

  • Que un empleado no sea jefe de sí mismo sí se podría: CHECK (jefe_id <> emp_id). Las cadenas de jefes circulares (A jefe de B y B jefe de A), no.
  • Que el comercial de un cliente tenga oficio VENDEDOR.
  • Que un pedido ENTREGADO o ENVIADO tenga fecha_envio: también se podría con un CHECK multicolumna (estado IN ('PENDIENTE', 'CANCELADO') OR fecha_envio IS NOT NULL). Comprueba en los datos que el pedido 5008 CANCELADO no tiene fecha de envío.
  • Que la fecha de asignación a un proyecto esté entre su inicio y su fin (abarca dos tablas).

✏️ Ejercicio: Revisa los pedidos de empresa_datos.sql y comprueba que ninguno incumpliría el CHECK sobre estado y fecha_envio propuesto arriba (pista: fíjate en los pedidos 5008 y 5020 a 5024). En el módulo 25 aprenderás a añadir restricciones a una tabla existente con ALTER TABLE.

7.6 Grafo relacional 

flowchart LR
    EMPLEADOS -->|dept_id| DEPARTAMENTOS
    EMPLEADOS -->|jefe_id| EMPLEADOS
    CLIENTES -->|comercial_id| EMPLEADOS
    CATEGORIAS -->|categoria_padre_id| CATEGORIAS
    PRODUCTOS -->|categoria_id| CATEGORIAS
    PEDIDOS -->|cliente_id| CLIENTES
    PEDIDOS -->|empleado_id| EMPLEADOS
    LINEAS_PEDIDO -->|"pedido_id (CASCADE)"| PEDIDOS
    LINEAS_PEDIDO -->|producto_id| PRODUCTOS
    PROYECTOS -->|dept_id| DEPARTAMENTOS
    ASIGNACIONES -->|emp_id| EMPLEADOS
    ASIGNACIONES -->|"proyecto_id (CASCADE)"| PROYECTOS

El grafo también indica el orden de creación y de carga: primero las tablas a las que no apunta ninguna flecha de salida (DEPARTAMENTOS, CATEGORIAS), después las que dependen de ellas… Las autorrelaciones obligan a insertar primero a los jefes (por eso empresa_datos.sql inserta los empleados “de arriba abajo”). El script de borrado, empresa_borrar.sql, sigue el orden inverso.

8. Introducción al diseño físico

8.1 Del esquema lógico al físico

El diseño lógico (lo que has hecho hasta ahora) es independiente del SGBD concreto: tablas, columnas, claves y restricciones. El diseño físico decide cómo se implementa ese esquema en un SGBD concreto para que funcione con el rendimiento, la seguridad y el coste requeridos.

Entrada del diseño físico Ejemplo
Esquema lógico normalizado Las tablas de los casos anteriores
Volumen de datos y crecimiento 2 millones de visualizaciones al día
Carga de trabajo: consultas y transacciones frecuentes “Historial de un perfil”, “préstamos activos de un socio”
Requisitos de rendimiento y disponibilidad Respuesta en menos de 200 ms
Características del SGBD Oracle: tipos de datos, índices, particiones, vistas materializadas
Decisión física Pregunta que responde
Tipos de datos ¿Cómo se almacena cada columna?
Índices ¿Cómo se accede rápido a las filas?
Particionamiento ¿Cómo se divide una tabla enorme?
Desnormalización controlada y vistas materializadas ¿Qué redundancia compensa?
Vistas ¿Qué ve cada usuario o aplicación?
Almacenamiento (tablespaces) ¿Dónde y cómo se guardan los datos?

8.2 Elección de tipos de datos en Oracle

Dato Tipo recomendado Comentario
Identificadores numéricos NUMBER(6) o columna identidad Ajusta la precisión al volumen esperado
Importes NUMBER(10,2) Nunca FLOAT/BINARY_DOUBLE para dinero (errores de redondeo)
Cantidades enteras NUMBER(5)  
Texto variable VARCHAR2(n) Longitud máxima realista; n se mide en bytes por defecto (los caracteres acentuados en UTF-8 ocupan 2)
Códigos de longitud fija CHAR(n) Por ejemplo, CHAR(1) para S/N
NIF, teléfono, código postal VARCHAR2 Son códigos, no números: no se suman y pueden tener letras o ceros a la izquierda
Fechas DATE En Oracle incluye también hora, minutos y segundos
Momentos con fracciones de segundo TIMESTAMP Por ejemplo, VISUALIZACIONES.momento
Textos largos, documentos CLOB / BLOB Sinopsis de una película, carátula
Lógicos CHAR(1) + CHECK, o BOOLEAN BOOLEAN disponible desde Oracle 23ai

⚠️ Importante: Disponible desde Oracle 23ai: el tipo BOOLEAN en columnas. Alternativa clásica: CHAR(1) con CHECK (col IN ('S', 'N')).

Claves naturales o sustitutas. Una clave natural procede del mundo real (ISBN, NIF, matrícula); una sustituta (surrogate key) es un código interno sin significado, generado por el SGBD (columna identidad o secuencia). En Oracle, como no existe ON UPDATE CASCADE, se prefieren claves sustitutas cuando la natural puede cambiar o es muy larga, manteniendo la natural como UNIQUE. Las verás en los módulos 23 y 26.

8.3 Índices

📘 Concepto: Un índice es una estructura auxiliar (normalmente un árbol B+) que permite localizar filas por el valor de una o varias columnas sin recorrer toda la tabla, igual que el índice alfabético de un libro. Acelera las búsquedas y las reuniones, pero ocupa espacio y ralentiza las inserciones, modificaciones y borrados, porque hay que mantenerlo.

Oracle crea automáticamente un índice para cada PRIMARY KEY y cada UNIQUE, pero no para las claves ajenas. Puedes comprobarlo en el diccionario de datos con el esquema EMPRESA:

SELECT table_name, index_name, column_name
FROM user_ind_columns
WHERE table_name IN ('EMPLEADOS', 'ASIGNACIONES')
ORDER BY table_name, index_name, column_position;

TABLE_NAME      INDEX_NAME      COLUMN_NAME
--------------- --------------- ------------
ASIGNACIONES    PK_ASIGNACIONES EMP_ID
ASIGNACIONES    PK_ASIGNACIONES PROYECTO_ID
EMPLEADOS       PK_EMPLEADOS    EMP_ID
EMPLEADOS       UQ_EMP_EMAIL    EMAIL

4 filas seleccionadas.

EMPLEADOS.dept_id y EMPLEADOS.jefe_id no tienen índice. Tampoco ASIGNACIONES.proyecto_id como primera columna: el índice de la PK empieza por emp_id, así que sirve para buscar “los proyectos de un empleado” pero no “los empleados de un proyecto”.

Criterios para decidir qué indexar:

Indexar No indexar (en general)
Claves ajenas: aceleran las reuniones y evitan bloqueos de la tabla hija al borrar o modificar filas padre Tablas muy pequeñas (se leen enteras más rápido)
Columnas muy usadas en WHERE, JOIN y ORDER BY Columnas que casi nunca se consultan
Columnas selectivas (muchos valores distintos: email, fecha_pedido) Columnas con pocos valores distintos en sistemas transaccionales (estado, descatalogado)
Combinaciones que se consultan juntas (índice compuesto, con la columna más filtrada primero) Tablas con muchísimas escrituras y pocas lecturas

Propuesta de índices para EMPRESA (además de los de PK y UK):

Índice Motivo
empleados(dept_id) FK; “empleados de un departamento”
empleados(jefe_id) FK; consultas jerárquicas (módulo 33)
clientes(comercial_id) FK; “clientes de un comercial”
pedidos(cliente_id) FK; “pedidos de un cliente”
pedidos(empleado_id) FK
pedidos(fecha_pedido) Búsquedas por rangos de fechas (ventas del mes)
lineas_pedido(producto_id) FK; “en qué pedidos aparece un producto”
productos(categoria_id) FK
asignaciones(proyecto_id) FK; la PK empieza por emp_id
proyectos(dept_id), categorias(categoria_padre_id) FK

Un adelanto de la sintaxis, que estudiarás en el módulo 26:

CREATE INDEX ix_emp_dept ON empleados (dept_id);

Índice creado.

Otros tipos de índice de Oracle que conviene conocer: bitmap (para columnas con pocos valores en almacenes de datos, no en sistemas con muchas actualizaciones concurrentes), basados en funciones (por ejemplo, sobre UPPER(email) para búsquedas sin distinguir mayúsculas) e índices únicos condicionales construidos sobre una expresión CASE, una técnica que permite, por ejemplo, impedir dos ingresos sin alta del mismo paciente sin escribir disparadores.

8.4 Particionamiento

📘 Concepto: El particionamiento divide una tabla (o un índice) muy grande en trozos llamados particiones, que el SGBD gestiona por separado pero que para las consultas siguen siendo una única tabla.

Tipo Criterio Ejemplo
Por rango (range) Intervalos de valores, normalmente fechas VISUALIZACIONES por mes
Por lista (list) Valores concretos CUENTAS por país
Por hash Función de dispersión, para repartir uniformemente Tablas sin un criterio natural
Compuesto Combinación de los anteriores Rango por mes y hash por cuenta

Ventajas: las consultas que filtran por la columna de partición solo leen las particiones necesarias (partition pruning); se pueden archivar o borrar datos antiguos eliminando una partición entera en lugar de con un DELETE masivo; las operaciones de mantenimiento se hacen por trozos.

💡 Consejo: El particionamiento solo compensa en tablas grandes (del orden de decenas de millones de filas o más). La disponibilidad de la opción de particionamiento depende de la edición y la licencia de Oracle; consúltalo antes de diseñar con ella.

8.5 Estimación de volumen

Antes de decidir índices y particiones hay que estimar cuántos datos habrá. Método: filas esperadas × tamaño medio de fila, proyectado a varios años.

Tabla Filas estimadas Tamaño medio de fila Volumen
Biblioteca: SOCIOS 20 000 ~150 B ~3 MB
Biblioteca: PRESTAMOS (300/día, 300 días/año, 10 años) 900 000 ~45 B ~40 MB
Streaming: PERFILES 2 000 000 ~60 B ~120 MB
Streaming: VISUALIZACIONES (3 por perfil y día) 6 000 000/día ≈ 2 190 millones/año ~50 B ~110 GB/año

Conclusiones: la biblioteca cabe holgadamente en memoria y le bastan los índices de las claves ajenas. En la plataforma de streaming, VISUALIZACIONES justifica particionar por mes, revisar con cuidado cada índice (cada uno multiplica el coste de 6 millones de inserciones diarias) y quizá mover el historial antiguo a un almacén de datos. A estas cifras hay que sumar los índices (a menudo, tanto espacio como la propia tabla) y un margen de crecimiento.

8.6 Desnormalización controlada y vistas

Lo visto en el módulo 20 se aplica en el diseño físico, nunca antes de tener el esquema lógico normalizado:

  • Vistas: consultas almacenadas que se usan como tablas. No duplican datos. Sirven para simplificar (una vista v_pedidos_importe con el importe de cada pedido), para seguridad (una vista de empleados sin el salario para el personal de recepción) y para independencia lógica (si cambia una tabla, se adapta la vista y las aplicaciones no se enteran).
  • Vistas materializadas: guardan físicamente el resultado y el SGBD lo refresca. Útiles para resúmenes costosos (ventas por mes y categoría).
  • Columnas desnormalizadas: solo cuando las anteriores no bastan, documentando el mecanismo que las mantiene.

Todo esto se estudia en el módulo 26.

8.7 De diseño a DDL: un adelanto

Así se escribe en Oracle la tabla SOCIOS del caso 1, con todas las restricciones del esquema final (la sintaxis completa la verás en los módulos 23 y 24):

CREATE TABLE socios (
    num_socio    NUMBER(6)     GENERATED ALWAYS AS IDENTITY
                               CONSTRAINT pk_socios PRIMARY KEY,
    dni          VARCHAR2(9)   CONSTRAINT nn_soc_dni NOT NULL,
    nombre       VARCHAR2(40)  CONSTRAINT nn_soc_nombre NOT NULL,
    apellidos    VARCHAR2(60)  CONSTRAINT nn_soc_apellidos NOT NULL,
    email        VARCHAR2(80),
    telefono     VARCHAR2(15),
    fecha_alta   DATE          DEFAULT SYSDATE CONSTRAINT nn_soc_fecha_alta NOT NULL,
    CONSTRAINT uq_soc_dni   UNIQUE (dni),
    CONSTRAINT uq_soc_email UNIQUE (email),
    CONSTRAINT ck_soc_email CHECK (email LIKE '%_@_%._%')
);

Tabla creada.

Correspondencia con el diseño lógico: la PK subrayada es pk_socios (con una columna identidad como clave sustituta, disponible desde Oracle 12c); cada atributo sin (N) lleva su NOT NULL con nombre; las UK son uq_soc_dni y uq_soc_email; y se ha añadido un CHECK de formato que no estaba en el diseño lógico (una decisión física de calidad de datos). Si intentas insertar un socio con el correo 'luis-sin-arroba', Oracle lo rechaza con el error ORA-02290 (violación de la restricción CK_SOC_EMAIL).

Para dejar tu esquema limpio después de la prueba:

DROP TABLE socios PURGE;

Tabla borrada.

8.8 Lista de comprobación del diseño físico

  1. ¿Cada columna tiene el tipo y la longitud adecuados? ¿Los importes son NUMBER(p,2)?
  2. ¿Las claves primarias son estables? ¿Hacen falta claves sustitutas?
  3. ¿Todas las restricciones del esquema lógico tienen su CONSTRAINT con nombre?
  4. ¿Están indexadas las claves ajenas y las columnas de búsqueda frecuente?
  5. ¿Hay tablas que por volumen necesiten particionamiento o archivado?
  6. ¿Qué vistas necesitan las aplicaciones y los distintos perfiles de usuario?
  7. ¿Hay alguna desnormalización? ¿Está medida, justificada y documentada?
  8. ¿Qué restricciones adicionales quedan para disparadores o para la aplicación?

9. Ejercicios

Los ejercicios 1 y 2 están resueltos. Los ejercicios 3 a 7 son casos completos propuestos: para cada uno, sigue las siete fases de la sección 1 y, como en los casos resueltos, termina con el esquema final y su grafo relacional.

Ejercicio 1: Índices para la biblioteca

Propón los índices (además de los de PK y UK) para el esquema final del caso 1, sabiendo que las operaciones más frecuentes son: buscar libros por título, consultar los préstamos activos de un socio y ver qué ejemplares de un libro están disponibles.

Ver solución
Índice Motivo
libros(titulo) Búsqueda por título (si se busca por palabras sueltas, mejor un índice de texto, fuera del alcance del curso)
libros(editorial_id) FK
autorias(autor_id) FK; “libros de un autor” (la PK empieza por isbn)
ejemplares(estanteria_id) FK
prestamos(num_socio, fecha_devolucion) Préstamos de un socio, filtrando los no devueltos
reservas(isbn) FK; la PK empieza por num_socio

No hace falta un índice en ejemplares(isbn): la PK (isbn, num_ejemplar) empieza por isbn y sirve también para buscar los ejemplares de un libro. Tampoco en prestamos(isbn, num_ejemplar): lo cubre el índice de la UK (isbn, num_ejemplar, fecha_prestamo).

Con el volumen estimado (menos de un millón de préstamos), no se necesita particionamiento.

Ejercicio 2: Revisar un diseño ajeno

Un compañero ha transformado el caso 4 (liga) y propone esta tabla. Encuentra los problemas:

FICHAS (jugador_id, equipo_id, temporada_id, dorsal, nombre_equipo, nombre_jugador)

Ver solución
  1. Clave incorrecta: con (jugador_id, equipo_id, temporada_id) como clave, un jugador podría tener ficha en dos equipos la misma temporada, contra el requisito 3. La clave debe ser (jugador_id, temporada_id); equipo_id pasa a ser un atributo (FK).
  2. No está en 2FN: equipo_id → nombre_equipo y jugador_id → nombre_jugador son dependencias parciales. Esas columnas sobran: están en EQUIPOS y JUGADORES.
  3. Falta la UK (equipo_id, temporada_id, dorsal), que es la otra clave candidata y garantiza dorsales únicos.

Tabla corregida: FICHAS (*jugador_id*, *temporada_id*, equipo_id, dorsal) con UK (equipo_id, temporada_id, dorsal).

Ejercicio 3: Propuesto. Agencia de viajes

Una agencia organiza viajes (código, destino, descripción, precio base y número de días) que se ofrecen en varias salidas (fecha de salida y plazas). Cada salida tiene un guía (DNI, nombre, idiomas que habla: varios). Los clientes (DNI, nombre, teléfono, correo) reservan plazas en una salida (fecha de reserva, número de plazas, precio total pagado, estado: CONFIRMADA o ANULADA). Cada viaje incluye estancias en varios hoteles (código, nombre, ciudad, categoría de 1 a 5 estrellas), con el número de noches en cada uno. Atención a los atributos multivaluados y al precio histórico.

Ejercicio 4: Propuesto. Red social

Los usuarios (identificador, nombre de usuario único, correo único, fecha de alta, biografía opcional) siguen a otros usuarios (con la fecha de inicio). Publican publicaciones (texto, fecha, ubicación opcional) que pueden ser respuesta a otra publicación. Una publicación puede llevar varias etiquetas (#hashtags) y recibir me gusta (como mucho uno por usuario, con su fecha). Los usuarios pueden enviarse mensajes privados. Presta atención a las autorrelaciones y a las restricciones que no se pueden expresar (por ejemplo, que un usuario no se siga a sí mismo sí se puede; ¿y que no dé “me gusta” a sus propias publicaciones?).

Ejercicio 5: Propuesto. Gimnasio

Un gimnasio con varios centros tiene socios con una cuota (básica, completa, familiar) cuyo precio cambia cada año. Los monitores imparten actividades (yoga, spinning…) en sesiones con día de la semana, hora, sala y aforo. Los socios se inscriben en sesiones concretas (una fecha concreta de una sesión semanal). Una sala no puede albergar dos sesiones a la misma hora y un monitor no puede impartir dos sesiones a la vez. Comprueba si alguna tabla queda en 3FN pero no en FNBC.

Ejercicio 6: Propuesto. Venta de entradas para conciertos

Una empresa vende entradas para conciertos de artistas (uno o varios por concierto, con su orden de actuación) en recintos que tienen zonas (pista, grada A…) con su aforo. El precio de la entrada depende del concierto y de la zona. Cada venta la hace un cliente, en una fecha, y puede incluir varias entradas, cada una con su zona y un código QR único. Hay que garantizar que no se vendan más entradas que el aforo de la zona (¿se puede declarativamente?).

Ejercicio 7: Propuesto. Academia de idiomas

Una academia ofrece cursos de varios idiomas y niveles (A1 a C2). Cada curso se imparte en grupos con horario, aula y un profesor. Los profesores pueden enseñar varios idiomas (es una relación con la fecha de acreditación). Los alumnos se matriculan en grupos y obtienen una calificación final; además, la academia guarda los pagos mensuales de cada matrícula. Incluye el estudio del caso “cada profesor imparte un único idioma” frente a “puede impartir varios” y su efecto en la forma normal de la tabla de grupos.

Resumen

Fase Qué se hace Errores frecuentes
Requisitos Identificar objetos, atributos, asociaciones y reglas No preguntar por los casos límite (historia, repeticiones, nulos)
E/R Entidades, relaciones, cardinalidades, débiles, jerarquías Cardinalidades al revés; atributos de otra entidad (“entidades ocultas”)
Transformación Tablas, PK, FK, UK, nulos según la participación mínima FK en el lado equivocado en 1:1; olvidar la UK que garantiza el 1:1
DF y forma normal DF de cada tabla, claves candidatas, forma normal Deducir DF de los datos; olvidar claves alternativas
Ajustes Separar dependencias parciales y transitivas; valorar FNBC Confundir datos históricos (precio aplicado) con redundancia
Esquema final PK, FK con acción ante el borrado, UK, NOT NULL, CHECK CASCADE indiscriminado; no documentar las restricciones adicionales
Grafo relacional Flechas de FK; orden de creación y de carga Ciclos de FK NOT NULL imposibles de cargar
Diseño físico Tipos, índices, particiones, volumen, vistas Indexarlo todo o nada; desnormalizar sin medir
Navegación  
← Módulo anterior: Normalización Siguiente módulo: SQL y el entorno Oracle →