Normalización

En resumen: La normalización divide los datos en tablas para que cada hecho se guarde en un solo lugar, lo que elimina las anomalías de actualización, inserción y borrado.

Problema

Excel llegó a la base de datos

¿Recuerdas la tabla de pedidos de la primera lección del curso? Alguien la ha pasado a la base de datos tal como estaba: una fila por cada producto de un pedido, y al lado la fecha, el cliente, su correo y su ciudad.

flat_orders

• order_idinteger *
• ordered_attext *
• customertext *
• emailtext *
• citytext
• producttext *
• pricereal *
• quantityinteger *

SQLbase de datos vacía

SELECT * FROM flat_orders;

SELECT * FROM flat_orders;

▶ EjecutarRestaurar la consultaBase nueva

11 filas, y solo tres clientes. El nombre y el correo de María están escritos 6 veces. Mira también su ciudad en el pedido 6.

La idea

Anomalías

Cuando un hecho está escrito en muchos sitios, cada cambio es un riesgo. Estos problemas se llaman anomalías. Hay tres tipos.

De actualización. María cambia su correo. Un compañero lo corrige solo en su último pedido:

SQLbase de datos vacía

UPDATE flat_orders SET email = 'maria.i@example.com'
WHERE order_id = 6;

SELECT DISTINCT customer, email FROM flat_orders
WHERE customer = 'Maria Ivanova';

UPDATE flat_orders SET email = 'maria.i@example.com' WHERE order_id = 6; SELECT DISTINCT customer, email FROM flat_orders WHERE customer = 'Maria Ivanova';

▶ EjecutarRestaurar la consultaBase nueva

Ahora la base de datos dice dos cosas a la vez. ¿Cuál es el correo correcto?

Míralo

Inserción y borrado

De inserción. La tienda empieza a vender copos de avena. Nadie los ha pedido todavía. ¿Dónde anotas su precio?

SQLbase de datos vacía

INSERT INTO flat_orders (product, price)
VALUES ('Copos de avena', 3.2);

INSERT INTO flat_orders (product, price) VALUES ('Copos de avena', 3.2);

▶ EjecutarRestaurar la consultaBase nueva

Una fila sin pedido no puede entrar. El producto existe solo cuando alguien lo compra.

De borrado. Elena cancela el pedido 4, y se borra. Era el único pedido con la sartén:

SQLbase de datos vacía

DELETE FROM flat_orders WHERE order_id = 4;

SELECT product, price FROM flat_orders
WHERE product = 'Sartén de 28 cm';

DELETE FROM flat_orders WHERE order_id = 4; SELECT product, price FROM flat_orders WHERE product = 'Sartén de 28 cm';

▶ EjecutarRestaurar la consultaBase nueva

Junto con el pedido desaparece el hecho de que la tienda vende una sartén por 24,90 €.

La idea

Formas normales

Organizar las tablas para que cada hecho esté en un solo lugar se llama normalización. Las reglas vienen por pasos, las formas normales. Cada una incluye la anterior.

Primera (1FN): un valor por celda. Sin listas como «vegano, ecológico», y sin columnas tag1, tag2, tag3.

SQLbase de datos vacía

SELECT name, tags FROM products_sheet
WHERE tags = 'vegano';

SELECT name, tags FROM products_sheet WHERE tags = 'vegano';

▶ EjecutarRestaurar la consultaBase nueva

Solo el pan blanco. El pan integral es vegano, pero su celda dice «vegano, ecológico». Con LIKE '%vegano%' lo encontrarás, pero no puedes contar productos por etiqueta, corregir «ecológico» en todas partes ni evitar una falta de ortografía. Las etiquetas van en una tabla de enlace, como en la primera lección de este módulo.

Segunda (2FN): cada columna depende de toda la clave. Esto importa cuando la clave tiene varias columnas:

order_lines

• PKorder_idinteger
• PKproduct_idinteger
• product_nametext *
• quantityinteger *

El nombre del producto depende solo de product_id, no del pedido. Se repetirá en cada pedido con ese producto. Va en products.

Tercera (3FN): las columnas dependen solo de la clave, no de otra columna:

products

• PKidinteger
• nametext *
• category_idinteger *
• category_nametext *
• pricereal *

category_name lo decide category_id, no el producto. Si se cambia el nombre de la categoría, hay que corregir cada producto que está en ella. El nombre va en categories.

Más precisamente, para curiosos

La fórmula corta de las tres formas: cada columna depende de «la clave, toda la clave y nada más que la clave».

También hay formas superiores (Boyce-Codd, cuarta, quinta), pero en la práctica una tabla en tercera forma normal suele dejar de tener estas anomalías.

flat_orders rompe la segunda forma normal. La clave de una fila es el pedido y el producto, mientras que la fecha y el cliente dependen solo del pedido. También rompe la tercera: la ciudad y el correo dependen del cliente, no del pedido.

Pruébalo

¿Qué forma?

1. En order_lines la clave es (order_id, product_id), y product_name depende solo de product_id. ¿Qué forma se rompe?

La terceraLa primeraLa segunda

2. products tiene category_id y category_name. ¿Qué forma se rompe?

La primeraLa terceraLa segunda

Comprobar

La idea

La división

Divides flat_orders en las tablas de la tienda: customers, products, orders y order_items. Empiezas por las que no apuntan a nada.

INSERT puede tomar sus filas de un SELECT en lugar de VALUES. Con DISTINCT, cada producto entra una vez:

products

• PKidinteger
• nametext *
• pricereal *

SQLbase de datos vacía

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  price REAL NOT NULL
);
INSERT INTO products (name, price)
SELECT DISTINCT product, price FROM flat_orders;

SELECT * FROM products;

CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, price REAL NOT NULL ); INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders; SELECT * FROM products;

▶ EjecutarRestaurar la consultaBase nueva

De 11 filas salen 6 productos, cada uno con un id. El precio también se queda en las filas del pedido, porque es el precio al que se compró. Por qué eso no es una repetición lo verás en la próxima lección.

Míralo

Excel está sucio

Lo mismo para los clientes:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext

SQLbase de datos vacía

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders;

▶ EjecutarRestaurar la consultaBase nueva

Un error. En el pedido 6, la ciudad de María es «Sofia», sin tilde. DISTINCT compara la fila entera, así que ve dos clientes con un correo, y UNIQUE no los deja entrar. Es bueno: la regla atrapó el error en lugar de llevarlo a la base nueva.

Primero corriges los datos, luego divides:

SQLbase de datos vacía

UPDATE flat_orders SET city = 'Sofía' WHERE city = 'Sofia';

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

SELECT * FROM customers;

UPDATE flat_orders SET city = 'Sofía' WHERE city = 'Sofia'; CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders; SELECT * FROM customers;

▶ EjecutarRestaurar la consultaBase nueva

Casi toda mudanza desde Excel empieza con una limpieza como esta.

Pruébalo

Los pedidos

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *

Los productos y los clientes ya están divididos. Rellena orders (id, customer_id, ordered_at) a partir de flat_orders. El número de pedido es order_id, y el cliente se encuentra por el correo.

SQLbase de datos vacía

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at TEXT NOT NULL
);

CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), ordered_at TEXT NOT NULL );

Comprobar▶ Ejecutar

Ejercicio

Dos tareas

1. Lo que queda es la tabla de enlace entre pedidos y productos.

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

Rellena order_items a partir de flat_orders: el número de pedido, el id del producto, la cantidad y el precio.

SQLbase de datos vacía

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price REAL NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price REAL NOT NULL, PRIMARY KEY (order_id, product_id) );

Comprobar▶ Ejecutar

2. Ahora el informe de la primera lección del curso se hace con una sola consulta. ¿Cuánto ha gastado cada cliente?

Muestra el nombre de cada cliente y el total de sus compras (cantidad por precio):

SQLbase de datos vacía

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. María cambió su correo, y está en 40 filas de la tabla. Un compañero corrigió 38. ¿Cómo se llama esto?

Una anomalía de actualizaciónUna anomalía de borradoUna anomalía de inserción

2. ¿Qué rompe la primera forma normal?

Una columna tags con el texto «vegano, ecológico»Una tabla sin columna idUna columna city que a veces es NULL

3. ¿Qué hace INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders?

Añade una fila por cada par distinto de producto y precioNada, INSERT no puede usar SELECTAñade una fila a products por cada pedido

4. ¿Por qué la división de los pedidos de María se detuvo con un error de UNIQUE?

Porque su ciudad está escrita de forma distinta en dos filas, y DISTINCT la vio como dos clientesPorque María tiene muchos pedidosPorque un correo no puede ser UNIQUE

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Restricciones: reglas para los datos · Claves foráneas: vínculos entre tablas · Tipos de relación: 1:1, 1:N, M:N · Diagrama ER: de la descripción al esquema
• Continúa en: Claves naturales y desnormalización · Bases de datos documentales · La panadería, etapa 1: la base de datos y las tablas
• Ejercicios sobre el tema: Diseñar una base de datos