Bloque 5 · Diseñar y normalizar — Curso de SQL

Lección 1 · Teoría

Señales de mal diseño

Un buen diseño es aquel que no repite datos y donde cada dato vive en un sitio. Antes de normalizar, aprende a oler una tabla mal hecha. Tu dataset del bloque, ventas_desnormalizadas, está lleno de estos olores a propósito:

Redundancia — el email del vendedor se repite en cada una de sus ventas

Campos compuestos — «Portátil, Ratón» metidos en una sola celda con comas

Dependencias raras — el teléfono de la región depende de la región, no de la venta

Sin claves — solo existe un id secuencial, nada más

Sin restricciones — cualquier valor se puede guardar de cualquier forma

la tabla problemática

DESCRIBE ventas_desnormalizadas;
SELECT * FROM ventas_desnormalizadas LIMIT 5;

1.1 · ¿Por qué molesta la redundancia?

Si María cambia de email, hay que actualizar todas sus filas. Si lo olvidas, la tabla queda con dos emails de María: inconsistencia. Y si un vendedor se va, borras sus ventas y con ellas su contacto: pérdida de datos.

💡 Regla de oro del diseño: cada dato se guarda una sola vez y el resto de tablas lo referencian por clave. Así eliminamos tanto la repetición como el riesgo de inconsistencia.

⚠️ No confundas normalizar (este bloque) con denormalizar: en informes de solo lectura a veces se acepta repetir algún campo calculado para velocidad. Se normaliza para escribir con seguridad; se denormaliza para leer deprisa. Saber cuándo cada cosa es tu madurez como DBA.

🎉 Detectas problemas. Ahora el primer estándar que los arregla: la primera forma normal.

Lección 2 · Teoría

Primera forma normal (1FN)

Una tabla está en 1FN cuando cada celda guarda UN solo valor atómico (no listas separadas por comas) y todas sus columnas son simples. La violación más clásica del dataset: la columna producto que contiene 'Portátil, Ratón'.

filas con más de un producto → violan 1FN

SELECT id, producto
FROM   ventas_desnormalizadas
WHERE  producto LIKE '%,%';

2.1 · Cómo arreglarla

Se separa cada producto en su propia fila (una zona de venta con varios productos se vuelve varias filas), o se crea una tabla relacionada. El modelo correcto separa la venta de sus líneas:

venta y líneas de venta (1FN)

-- tabla de cabecera: datos de la venta
CREATE TABLE ventas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  fecha DATE NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- tabla de líneas: un producto por fila
CREATE TABLE lineas_venta (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  venta_id INT UNSIGNED NOT NULL,
  producto VARCHAR(60) NOT NULL,
  unidades INT NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY (venta_id) REFERENCES ventas (id)
) ENGINE=InnoDB;

💡 Pregunta rápida: ¿unidades debería ir en ventas o en lineas_venta? En líneas: el dato pertenece a cada producto de la venta, no a la venta entera.

🎉 Celdas atómicas. Siguiente paso: separar las dependencias parciales y transitivas.

Lección 3 · Teoría

Segunda y tercera forma normal

3.1 · Segunda forma normal (2FN)

La 2FN aplica a tablas con clave compuesta (dos o más columnas como PK): cada columna no clave debe depender de TODA la clave, no de solo una parte. En nuestro esquema lineas_venta usa una clave simple, así que ya la cumple.

⚠️ Un ejemplo de violación: la tabla prestamos con clave (ejemplar_id, socio_id) y una columna titulo… el título depende solo del ejemplar (una parte de la clave), no del socio. Eso rompe 2FN.

3.2 · Tercera forma normal (3FN)

La 3FN elimina las dependencias transitivas: un dato no clave no debe depender de otro dato no clave. En el dataset: region_telefono depende de region, y region a su vez de la venta. El teléfono pertenece a la región, no a la venta. Solución: crear una tabla regiones y referenciarla.

Forma normal	Regla clave	Qué arregla
1FN	Un valor atómico por celda	Listas con comas, campos compuestos
2FN	Sin dependencias parciales de la clave	Columnas que se cuelgan de media clave
3FN	Sin dependencias transitivas	Datos de «otra» entidad mezclados

3.3 · El diseño normalizado (referencia)

Descarga sql/sql-bloque5_biblioteca.sql: es un modelo 3FN de una biblioteca con el que comparar tu trabajo. Observa sus claves foráneas, restricciones UNIQUE, CHECK y la tabla puente libros_generos:

extracto del modelo de referencia

CREATE TABLE libros_generos (
  libro_id  INT UNSIGNED NOT NULL,
  genero_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (libro_id, genero_id),
  FOREIGN KEY (libro_id)  REFERENCES libros  (id) ON DELETE CASCADE,
  FOREIGN KEY (genero_id) REFERENCES generos (id) ON DELETE CASCADE
) ENGINE=InnoDB;

💡 ON DELETE CASCADE: si borras un libro, se borran sus renglones de géneros. ON DELETE RESTRICT: impide borrar lo que está referenciado. Elige según el significado del dato.

🎉 Ya piensas en formas normales. Ahora código que hace cumplir las reglas: las restricciones.

Lección 4 · Teoría

Restricciones de integridad

Las restricciones son las reglas que el motor obliga a cumplir. Un buen esquema las usa en cada columna para que los datos malos ni siquiera puedan entrar.

4.1 · El kit de restricciones

una tabla protegida por completo

CREATE TABLE socios (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,   -- PK, sin nulos, correlativo
  nombre VARCHAR(80)  NOT NULL,                   -- obligatorio
  email  VARCHAR(90)  NOT NULL UNIQUE,        -- sin duplicados
  edad   TINYINT NOT NULL DEFAULT 18            -- valor por defecto
          CHECK (edad >= 0 AND edad <= 120), -- rango válido
  nivel  VARCHAR(20)  NOT NULL DEFAULT 'bronce'
          CHECK (nivel IN ('bronce', 'plata', 'oro')),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Restricción	Qué garantiza
`NOT NULL`	El campo no puede quedar vacío
`UNIQUE`	No hay dos filas con el mismo valor
`PRIMARY KEY`	NOT NULL + UNIQUE: identidad única
`FOREIGN KEY`	El valor debe existir en la tabla referenciada
`CHECK`	Se cumple una condición lógica (MariaDB/MySQL 8.0.16+)
`DEFAULT`	Valor que se usa si no se indica

⚠️ En MySQL las restricciones CHECK se validan desde 8.0.16; en MariaDB siempre. En MySQL 5.7 se «aceptan» pero no se comprueban. Verifica tu versión con SELECT VERSION();.

🎉 El esquema se protege solo. Última joya del bloque: vistas.

Lección 5 · Teoría

Vistas

Una vista es una consulta guardada como si fuera una tabla. No almacena datos de nuevo: cada vez que la consultas, ejecuta su SELECT. Sirve para que los informes difíciles se usen con una línea.

5.1 · Crear y usar una vista

un informe empaquetado

CREATE VIEW v_ingresos_por_vendedor AS
SELECT vendedor, SUM(cantidad * precio) AS ingresos
FROM   ventas
GROUP BY vendedor;

-- usarla es como consultar una tabla
SELECT * FROM v_ingresos_por_vendedor
ORDER BY ingresos DESC;

5.2 · Actualizar y borrar vistas

redefinir y eliminar

CREATE OR REPLACE VIEW v_resumen AS
SELECT region, COUNT(*) AS ventas
FROM   ventas
GROUP BY region;

DROP VIEW v_resumen;

vista con JOIN: préstamos activos (tw_biblioteca)

CREATE VIEW v_libros_en_prestamo AS
SELECT s.nombre AS socio, l.titulo, p.fecha_inicio
FROM   prestamos p
JOIN ejemplares e ON e.id = p.ejemplar_id
JOIN libros l ON l.id = e.libro_id
JOIN socios s ON s.id = p.socio_id
WHERE p.fecha_fin IS NULL;

💡 Nombra las vistas con el prefijo v_: diferencia enseguida una «tabla real» de un informe. Y no pongas ORDER BY dentro de la vista: el orden lo decide quien la consume.

🎉 Bloque 5 completado: diseñas, normalizas, proteges con restricciones y empaquetas informes en vistas. Ahora machaca los 15 ejercicios y repara el dataset desnormalizado. 🐬

Manos a la obra

✍️ Los 15 ejercicios del bloque

Primero descarga el fichero mal diseñado sql/sql-bloque5_ventas_desnormalizadas.sql, cárgalo y caza sus problemas. Luego practica creando esquemas limpios. Tienes también el modelo de referencia en sql/sql-bloque5_biblioteca.sql.

📥 Descarga el dataset desnormalizado: sql/sql-bloque5_ventas_desnormalizadas.sql · Modelo de referencia: sql/sql-bloque5_biblioteca.sql

Sección A · Cazar problemas

1 Probar la redundancia del vendedor

Sobre tw_practica: consulta que agrube por vendedor y cuente cuántas filas ocupa cada uno, para ver la redundancia de sus datos de contacto.

Ver solución

-- Ejercicio 1: la redundancia de cada vendedor
SELECT vendedor,
       COUNT(*) AS filas
FROM   ventas_desnormalizadas
GROUP BY vendedor;

2 Encontrar las celdas no atómicas

Busca las filas cuya columna producto tenga una coma (más de un producto en la celda → violación de 1FN).

Ver solución

-- Ejercicio 2: celdas con varios productos
SELECT id, producto
FROM   ventas_desnormalizadas
WHERE  producto LIKE '%,%';

3 Probar la redundancia de la región

Consulta distinta de region y region_telefono para comprobar que el teléfono está repetido por región (dependencia transitiva, 3FN).

Ver solución

-- Ejercicio 3: teléfono repetido por región
SELECT DISTINCT region, region_telefono
FROM   ventas_desnormalizadas;

Sección B · Primera forma normal

4 Tabla con celdas atómicas

Diseña una tabla lineas_venta que guarde id, venta_id y un único producto por fila, cumpliendo 1FN (con su PK).

Ver solución

-- Ejercicio 4: líneas de venta atómicas (1FN)
CREATE TABLE lineas_venta (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  venta_id INT UNSIGNED NOT NULL,
  producto VARCHAR(60) NOT NULL,
  unidades INT NOT NULL DEFAULT 1,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

5 Aislar las filas ya atómicas

Consulta las filas cuyo producto no contenga coma: esas ya cumplirían 1FN tal cual.

Ver solución

-- Ejercicio 5: filas que ya son atómicas
SELECT id, producto
FROM   ventas_desnormalizadas
WHERE  producto NOT LIKE '%,%';

Sección C · Segunda y tercera forma normal

6 Entidad vendedores (3FN)

Diseña la tabla vendedores con id, nombre, email y telefono, de modo que el contacto deje de repetirse en cada venta.

Ver solución

-- Ejercicio 6: la entidad vendedor
CREATE TABLE vendedores (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(60) NOT NULL,
  email VARCHAR(90) NOT NULL,
  telefono VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

7 Entidad regiones

Diseña la tabla regiones con id, nombre y telefono: el teléfono pasa a depender solo de la región (se corrige la 3FN).

Ver solución

-- Ejercicio 7: la entidad región
CREATE TABLE regiones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(30) NOT NULL,
  telefono VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

8 Ventas normalizadas con FK

Diseña ventas_normalizadas con id, fecha, vendedor_id y region_id, donde las dos últimas sean claves foráneas a las tablas anteriores.

Ver solución

-- Ejercicio 8: ventas que solo referencian
CREATE TABLE ventas_normalizadas (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  fecha DATE NOT NULL,
  vendedor_id INT UNSIGNED NOT NULL,
  region_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (vendedor_id) REFERENCES vendedores (id),
  FOREIGN KEY (region_id)   REFERENCES regiones   (id)
) ENGINE=InnoDB;

Sección D · Restricciones

9 Email único

Crea una tabla usuarios (id, email) donde el email sea NOT NULL UNIQUE.

Ver solución

-- Ejercicio 9: email único
CREATE TABLE usuarios (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(90) NOT NULL UNIQUE,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

10 Precio con CHECK

Tabla productos con nombre y precio DEFAULT 0.00 CHECK (precio >= 0), más su PK.

Ver solución

-- Ejercicio 10: precio no negativo
CREATE TABLE productos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(80) NOT NULL,
  precio DECIMAL(8,2) NOT NULL DEFAULT 0.00
      CHECK (precio >= 0),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

11 Estado con valores permitidos

Tabla ejemplares con estado DEFAULT 'disponible' y un CHECK que solo permita 'disponible', 'prestado', 'deteriorado'.

Ver solución

-- Ejercicio 11: estados válidos
CREATE TABLE ejemplares (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo VARCHAR(20) NOT NULL UNIQUE,
  estado VARCHAR(20) NOT NULL DEFAULT 'disponible'
    CHECK (estado IN ('disponible', 'prestado', 'deteriorado')),
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Sección E · Vistas

12 Vista de ventas por vendedor

Crea la vista v_ventas_por_vendedor que muestre vendedor y el número de COUNT(*) de ventas.

Ver solución

-- Ejercicio 12: vista de recuento
CREATE VIEW v_ventas_por_vendedor AS
SELECT vendedor, COUNT(*) AS ventas
FROM   ventas_desnormalizadas
GROUP BY vendedor;

13 Vista filtrada

Crea v_ventas_madrid: ventas de la región 'Madrid' mostrando fecha, vendedor y total.

Ver solución

-- Ejercicio 13: vista solo Madrid
CREATE OR REPLACE VIEW v_ventas_madrid AS
SELECT fecha, vendedor, total_fila
FROM   ventas_desnormalizadas
WHERE  region = 'Madrid';

14 Consultar una vista

Usa una de las vistas creadas (por ejemplo v_ventas_por_vendedor) y devuelve solo los vendedores con más de 3 ventas, ordenados por ventas descendente.

Ver solución

-- Ejercicio 14: consultar la vista
SELECT vendedor, ventas
FROM   v_ventas_por_vendedor
WHERE  ventas > 3
ORDER BY ventas DESC;

15 Vista con JOIN grande

Basándote en el modelo tw_biblioteca, crea una vista v_prestamos_socios que muestre socio, título del libro y fecha de inicio del préstamo.

Ver solución

-- Ejercicio 15: vista con joins
CREATE VIEW v_prestamos_socios AS
SELECT s.nombre AS socio, l.titulo, p.fecha_inicio
FROM   prestamos p
JOIN ejemplares e ON e.id = p.ejemplar_id
JOIN libros l ON l.id = e.libro_id
JOIN socios s ON s.id = p.socio_id;

16 🏆 Proyecto integrador: normalizar el caos

Crea el fichero esquema_normalizado.sql que convierta el dataset desnormalizado en un diseño limpio. Tu guion debe incluir:

Tabla vendedores (id, nombre, email, telefono) con su PK.
Tabla regiones (id, nombre, telefono) con su PK.
Tabla ventas_norm (id, fecha, vendedor_id, region_id) con ambas FOREIGN KEY.
Tabla lineas_venta con producto único por fila, FK a ventas, y un CHECK para que unidades >= 1.
En vendedores, el email UNIQUE.
Una vista v_ventas_completas que junte todo (hoy por hoy, con las tablas de diseño).

Ver una solución posible

-- Informe: esquema normalizado de la empresa
CREATE TABLE vendedores (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(60) NOT NULL,
  email VARCHAR(90) NOT NULL UNIQUE,
  telefono VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE regiones (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(30) NOT NULL,
  telefono VARCHAR(15) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE ventas_norm (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  fecha DATE NOT NULL,
  vendedor_id INT UNSIGNED NOT NULL,
  region_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (vendedor_id) REFERENCES vendedores (id),
  FOREIGN KEY (region_id) REFERENCES regiones (id)
) ENGINE=InnoDB;

CREATE TABLE lineas_venta (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  venta_id INT UNSIGNED NOT NULL,
  producto VARCHAR(60) NOT NULL,
  unidades INT NOT NULL DEFAULT 1 CHECK (unidades >= 1),
  PRIMARY KEY (id),
  FOREIGN KEY (venta_id) REFERENCES ventas_norm (id)
) ENGINE=InnoDB;

CREATE VIEW v_ventas_completas AS
SELECT v.fecha, ve.nombre AS vendedor, r.nombre AS region,
       l.producto, l.unidades
FROM ventas_norm v
JOIN vendedores ve ON ve.id = v.vendedor_id
JOIN regiones r ON r.id = v.region_id
JOIN lineas_venta l ON l.venta_id = v.id;

Bloque 5: Diseñar y normalizar

Señales de mal diseño

1.1 · ¿Por qué molesta la redundancia?

Primera forma normal (1FN)

2.1 · Cómo arreglarla

Segunda y tercera forma normal

3.1 · Segunda forma normal (2FN)

3.2 · Tercera forma normal (3FN)

3.3 · El diseño normalizado (referencia)

Restricciones de integridad

4.1 · El kit de restricciones

Vistas

5.1 · Crear y usar una vista

5.2 · Actualizar y borrar vistas

✍️ Los 15 ejercicios del bloque

Sección A · Cazar problemas

1 Probar la redundancia del vendedor

2 Encontrar las celdas no atómicas

3 Probar la redundancia de la región

Sección B · Primera forma normal

4 Tabla con celdas atómicas

5 Aislar las filas ya atómicas

Sección C · Segunda y tercera forma normal

6 Entidad vendedores (3FN)

7 Entidad regiones

8 Ventas normalizadas con FK

Sección D · Restricciones

9 Email único

10 Precio con CHECK

11 Estado con valores permitidos

Sección E · Vistas

12 Vista de ventas por vendedor

13 Vista filtrada

14 Consultar una vista

15 Vista con JOIN grande

16 🏆 Proyecto integrador: normalizar el caos

📝 Examen del Bloque 5

Parte A · Test teórico (aprueba con 11/15)

Parte B · Reto «esquema normalizado»