SQL Normalização (1FN, 2FN, 3FN)

Na aula Modelando um Banco do Zero, o telefone do João, repetido em várias linhas do caderno, acabou com dois valores diferentes. Isso não é azar: é um defeito de projeto, e tem remédio com nome. A normalização é um conjunto de três regras, as formas normais (1FN, 2FN e 3FN), que dizem como dividir os dados em tabelas para que cada informação fique guardada num lugar só. Nesta aula você vai ver os defeitos acontecendo numa planilha de vendas montada com os dados reais da Loja Aurora, aplicar as três regras e descobrir que a Aurora já foi desenhada assim.

Os exemplos rodam de verdade no seu navegador, no SQLite 3.50 com o banco da Loja Aurora. As tabelas "mal divididas" desta aula (planilha, pedidos_lista, itens_largos, produtos_largos) não existem na Aurora: cada exemplo as monta, a partir dos dados reais, com CREATE TABLE … AS SELECT (aula Criar Tabela a partir de Consulta).

A ideia: cada coisa no seu lugar

Pense numa agenda de papel em que, a cada vez que você marca um compromisso com a tia Rosa, escreve também o endereço e o telefone dela. Um dia ela muda de casa. Você corrige o endereço na página de hoje e esquece as outras. Agora a agenda tem dois endereços da tia Rosa. A solução, que toda agenda já tem, é uma página de contatos: o endereço fica lá, uma vez só, e o compromisso diz só "tia Rosa".

Normalizar é fazer isso com método. A frase que resume as três formas normais é:

1. Os defeitos de uma planilha

Muita loja começa assim: uma planilha de vendas, uma linha por produto vendido, com tudo junto. A Ana montou essa planilha com os 220 itens vendidos pela Aurora:

CREATE TABLE planilha AS
SELECT ip.pedido_id, p.data, c.nome AS cliente, c.cidade,
       pr.nome AS produto, cat.nome AS categoria, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip
JOIN pedidos p      ON p.id = ip.pedido_id
JOIN clientes c     ON c.id = p.cliente_id
JOIN produtos pr    ON pr.id = ip.produto_id
JOIN categorias cat ON cat.id = pr.categoria_id;

SELECT * FROM planilha WHERE pedido_id <= 2;

Parece prático: tudo numa tabela, sem JOIN. Mas veja quantas vezes cada informação se repete. O José Ribamar Silva aparece em 10 linhas; "Teresina" ao lado dele, 10 vezes; a data do pedido 1, três vezes. Essas repetições causam três defeitos, chamados anomalias.

Anomalia de atualização

O José se mudou para Parnaíba. A Ana corrige a cidade dele, mas só nas linhas do pedido 1:

CREATE TABLE planilha AS
SELECT ip.pedido_id, p.data, c.nome AS cliente, c.cidade,
       pr.nome AS produto, cat.nome AS categoria, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip JOIN pedidos p ON p.id = ip.pedido_id JOIN clientes c ON c.id = p.cliente_id
JOIN produtos pr ON pr.id = ip.produto_id JOIN categorias cat ON cat.id = pr.categoria_id;

UPDATE planilha SET cidade = 'Parnaíba'
WHERE cliente = 'José Ribamar Silva' AND pedido_id = 1;

SELECT DISTINCT cliente, cidade FROM planilha WHERE cliente = 'José Ribamar Silva';

Agora o José mora em duas cidades. Para mudar uma informação, era preciso mudar dez linhas; esquecer uma deixa o banco contraditório, e nenhuma consulta avisa.

Anomalia de inserção

A Aurora tem 45 produtos e 30 clientes. Quantos aparecem na planilha?

CREATE TABLE planilha AS
SELECT ip.pedido_id, p.data, c.nome AS cliente, c.cidade,
       pr.nome AS produto, cat.nome AS categoria, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip JOIN pedidos p ON p.id = ip.pedido_id JOIN clientes c ON c.id = p.cliente_id
JOIN produtos pr ON pr.id = ip.produto_id JOIN categorias cat ON cat.id = pr.categoria_id;

SELECT COUNT(DISTINCT produto) AS produtos, COUNT(DISTINCT cliente) AS clientes
FROM planilha;

Só 42 produtos e 26 clientes. Os 3 produtos que nunca foram vendidos e os 4 clientes que nunca compraram (casos do banco: aula O Banco da Loja Aurora) não cabem na planilha: toda linha é uma venda, e sem venda não há onde escrever o produto novo ou o cliente que acabou de se cadastrar.

Anomalia de exclusão

O Pen Drive 32GB foi vendido em 4 pedidos. Se esses pedidos forem cancelados e apagados da planilha:

CREATE TABLE planilha AS
SELECT ip.pedido_id, p.data, c.nome AS cliente, c.cidade,
       pr.nome AS produto, cat.nome AS categoria, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip JOIN pedidos p ON p.id = ip.pedido_id JOIN clientes c ON c.id = p.cliente_id
JOIN produtos pr ON pr.id = ip.produto_id JOIN categorias cat ON cat.id = pr.categoria_id;

DELETE FROM planilha WHERE produto = 'Pen Drive 32GB';

SELECT COUNT(*) AS linhas_do_pen_drive FROM planilha WHERE produto = 'Pen Drive 32GB';

Apagar as vendas apagou junto o produto: a loja "esqueceu" que vende pen drive, de que categoria ele é e por quanto. Uma informação (o produto) morreu porque outra (as vendas) foi apagada.

As três formas normais existem para acabar com essas anomalias. Cada uma ataca um tipo de repetição.

2. Primeira Forma Normal (1FN): um valor por célula

A regra: cada célula guarda um valor só, e não há grupos de colunas repetidas (produto1, produto2, produto3…). Uma tabela assim está na 1FN (Primeira Forma Normal).

A violação mais comum é guardar uma lista dentro de uma coluna. Um pedido com os produtos separados por vírgula, por exemplo (a Ana montou com group_concat(), que junta textos de várias linhas):

CREATE TABLE pedidos_lista AS
SELECT p.id AS pedido_id, group_concat(pr.nome, ', ') AS produtos
FROM pedidos p
JOIN itens_pedido ip ON ip.pedido_id = p.id
JOIN produtos pr     ON pr.id = ip.produto_id
GROUP BY p.id;

SELECT * FROM pedidos_lista WHERE pedido_id <= 3;

Fica bonito de ler, e é péssimo de consultar. "Em quantos pedidos saiu o Jogo de Panelas?" A tentação é um LIKE:

CREATE TABLE pedidos_lista AS
SELECT p.id AS pedido_id, group_concat(pr.nome, ', ') AS produtos
FROM pedidos p JOIN itens_pedido ip ON ip.pedido_id = p.id JOIN produtos pr ON pr.id = ip.produto_id
GROUP BY p.id;

SELECT COUNT(*) AS com_like FROM pedidos_lista WHERE produtos LIKE '%Jogo%';

SELECT COUNT(*) AS certo FROM itens_pedido WHERE produto_id = 14;

O LIKE '%Jogo%' contou 11 pedidos; a resposta certa é 6. O LIKE também pegou o "Jogo de Tabuleiro Trilha Mística". Dá para escrever o nome inteiro no LIKE, mas aí qualquer diferença de grafia quebra a busca. E perguntas como "quantas unidades saíram?" ficam impossíveis: a lista nem guarda a quantidade, e não há como fazer JOIN com produtos nem usar índice.

O conserto: uma linha para cada item, com o produto pela chave. É exatamente a tabela itens_pedido da Aurora: (pedido_id, produto_id, quantidade, preco_unitario). Com ela, cada pergunta vira um WHERE simples.

CREATE TABLE pedidos_lista AS
SELECT p.id AS pedido_id, group_concat(pr.nome, ', ') AS produtos
FROM pedidos p JOIN itens_pedido ip ON ip.pedido_id = p.id JOIN produtos pr ON pr.id = ip.produto_id
GROUP BY p.id;

SELECT COUNT(*) FROM pedidos_lista WHERE produtos LIKE '%Jogo%';

SELECT SUM(quantidade) FROM itens_pedido WHERE produto_id = 14;

3. Segunda Forma Normal (2FN): a chave inteira

A regra: a tabela está na 1FN e, se a chave primária tem mais de uma coluna, cada coluna comum depende da chave inteira, e não só de um pedaço dela.

A chave da tabela de itens é composta: (pedido_id, produto_id). Imagine que alguém, para "facilitar", acrescentou nela a data do pedido e o nome do produto:

CREATE TABLE itens_largos AS
SELECT ip.pedido_id, ip.produto_id, p.data, pr.nome AS produto,
       ip.quantidade, ip.preco_unitario
FROM itens_pedido ip
JOIN pedidos p   ON p.id = ip.pedido_id
JOIN produtos pr ON pr.id = ip.produto_id;

SELECT * FROM itens_largos WHERE pedido_id IN (1, 29) ORDER BY pedido_id, produto_id;

Pergunte a cada coluna: "do que você depende?"

Quem depende de um pedaço da chave se repete toda vez que esse pedaço aparece: a data do pedido 1 está escrita três vezes, e o nome do Caderno, em todas as vendas dele. São as anomalias da seção 1 de volta. O conserto: cada coluna vai para a tabela do pedaço de que depende. A data já está em pedidos; o nome, em produtos. Para ver tudo junto, um JOIN remonta a informação na hora da consulta.

O preco_unitario não é repetição

Se o preço está em produtos.preco, por que a Aurora guarda outro preço em itens_pedido? Porque são informações diferentes: produtos.preco é o preço de hoje; preco_unitario é o preço cobrado naquela venda. Os dois às vezes diferem:

SELECT ip.pedido_id, pr.nome, ip.preco_unitario AS cobrado, pr.preco AS hoje
FROM itens_pedido ip
JOIN produtos pr ON pr.id = ip.produto_id
WHERE ip.preco_unitario <> pr.preco;

São as três vendas feitas em promoção. Se o preço da venda fosse lido de produtos, cada aumento de preço mudaria o valor de pedidos antigos, já pagos. O preço cobrado depende do produto e do pedido, ou seja, da chave inteira: está na 2FN e no lugar certo. Antes de chamar algo de "repetido", pergunte se é mesmo a mesma informação.

4. Terceira Forma Normal (3FN): nada além da chave

A regra: a tabela está na 2FN e nenhuma coluna comum depende de outra coluna comum. Tudo depende da chave, e só dela. Quando uma coluna depende da chave "por tabela", através de outra coluna, isso se chama dependência transitiva.

Um cadastro de produtos com os dados do fornecedor junto:

CREATE TABLE produtos_largos AS
SELECT pr.id, pr.nome, pr.preco, pr.fornecedor_id,
       f.nome AS fornecedor_nome, f.pais AS fornecedor_pais
FROM produtos pr
JOIN fornecedores f ON f.id = pr.fornecedor_id;

SELECT * FROM produtos_largos WHERE fornecedor_id = 4;

A chave é o id do produto, e a tabela está na 2FN (chave de uma coluna). Mas olhe a cadeia: o id do produto diz qual é o fornecedor_id, e o fornecedor_id diz o fornecedor_nome e o fornecedor_pais. O nome e o país são do fornecedor, não do produto: dependem de fornecedor_id, que é uma coluna comum.

id do produto ──→ fornecedor_id ──→ fornecedor_nome, fornecedor_pais
   (chave)          (coluna comum)       (dependem da coluna comum: transitiva)

Resultado: "Lusitana Comércio, Portugal" escrito cinco vezes, uma por produto. A anomalia de atualização aparece igualzinha:

CREATE TABLE produtos_largos AS
SELECT pr.id, pr.nome, pr.preco, pr.fornecedor_id,
       f.nome AS fornecedor_nome, f.pais AS fornecedor_pais
FROM produtos pr JOIN fornecedores f ON f.id = pr.fornecedor_id;

-- alguém corrigiu o país num produto só
UPDATE produtos_largos SET fornecedor_pais = 'Espanha' WHERE id = 16;

SELECT DISTINCT fornecedor_nome, fornecedor_pais
FROM produtos_largos WHERE fornecedor_id = 4;

A mesma empresa em dois países. O conserto: os dados do fornecedor vão para uma tabela fornecedores, uma linha por fornecedor, e o produto guarda só o fornecedor_id. É o que a Aurora faz. O mesmo vale para a categoria: o produto guarda categoria_id, e o nome da categoria fica em categorias.

SELECT pr.id, pr.nome, pr.preco, f.nome AS fornecedor, f.pais
FROM produtos pr
JOIN fornecedores f ON f.id = pr.fornecedor_id
WHERE f.id = 4;

5. Resumo das três formas

Existem formas normais além da terceira (a de Boyce-Codd, a 4FN, a 5FN), que tratam de casos mais raros. Na prática, um banco na 3FN já está livre das anomalias do dia a dia.

6. Desnormalizar de propósito

Normalizar não é uma lei que nunca se quebra. Às vezes um projetista repete um dado de caso pensado, sabendo do preço:

• Relatórios pesados. Uma tabela de resumo (o total de vendas de cada mês) recalculada toda noite evita somar milhões de linhas a cada consulta. É uma cópia: quem a mantém tem de refazê-la quando os dados mudam.
• Fotografias do passado. Uma nota fiscal guarda o endereço do cliente como era no dia da compra, mesmo que ele se mude depois. Como o preco_unitario, não é repetição: é outra informação (o endereço daquele dia).
• Leitura muito mais frequente que escrita, quando um JOIN a menos faz diferença medida (aula Quando o Índice Ajuda).

Antes de desnormalizar, tente uma view (aula Views): ela guarda a consulta que monta a "planilha", e não os dados. Você ganha a praticidade da tabela única sem nenhuma repetição.

CREATE VIEW vendas_planilha AS
SELECT ip.pedido_id, p.data, c.nome AS cliente, c.cidade,
       pr.nome AS produto, ip.quantidade, ip.preco_unitario
FROM itens_pedido ip
JOIN pedidos p   ON p.id = ip.pedido_id
JOIN clientes c  ON c.id = p.cliente_id
JOIN produtos pr ON pr.id = ip.produto_id;

UPDATE clientes SET cidade = 'Parnaíba' WHERE id = 1;

SELECT DISTINCT cliente, cidade FROM vendas_planilha WHERE cliente = 'José Ribamar Silva';

A mesma mudança de cidade da seção 1, agora feita em uma linha de clientes: a "planilha" (a view) mostra a cidade nova em todas as vendas do José, sem contradição.

Formas diferentes de fazer a mesma coisa

Para ver "a planilha de vendas" há três caminhos. Os três mostram os mesmos dados:

Quando dá errado

DELETE FROM produtos WHERE id = 12;

SELECT pedido_id, nome, quantidade
FROM itens_pedido
WHERE pedido_id = 1;

Referência rápida

Exercícios

Missão final: tirar os fornecedores da tabela de produtos

CREATE TABLE produtos_largos AS
SELECT pr.id, pr.nome, pr.preco, pr.fornecedor_id,
       f.nome AS fornecedor_nome, f.pais AS fornecedor_pais
FROM produtos pr JOIN fornecedores f ON f.id = pr.fornecedor_id;

SELECT fornecedor_id, fornecedor_nome, fornecedor_pais
FROM produtos_largos;

CREATE TABLE produtos_largos AS
SELECT pr.id, pr.nome, pr.preco, pr.fornecedor_id,
       f.nome AS fornecedor_nome, f.pais AS fornecedor_pais
FROM produtos pr JOIN fornecedores f ON f.id = pr.fornecedor_id;

CREATE TABLE fornecedores_novo AS
SELECT DISTINCT fornecedor_id AS id, fornecedor_nome AS nome, fornecedor_pais AS pais
FROM produtos_largos;

SELECT * FROM fornecedores_novo ORDER BY id;

De 45 linhas com fornecedor repetido para 6 linhas, uma por fornecedor. O próximo passo seria apagar as colunas fornecedor_nome e fornecedor_pais de produtos_largos (aula ALTER TABLE), deixando só o fornecedor_id, e você teria reconstruído, sozinho, as tabelas produtos e fornecedores da Aurora.

Resumo

• Dados repetidos causam três anomalias: de atualização (contradição), de inserção (não cabe) e de exclusão (some junto). Nenhuma delas dá mensagem de erro.
• 1FN: um valor por célula. Lista numa coluna vira uma tabela com uma linha por item (itens_pedido).
• 2FN: com chave composta, cada coluna depende da chave inteira. A data é do pedido; o nome é do produto; a quantidade e o preço cobrado são do item.
• 3FN: nenhuma coluna depende de outra coluna comum. Os dados do fornecedor vão para fornecedores; o produto guarda só o fornecedor_id.
• "A chave, a chave inteira e nada além da chave." A Loja Aurora está na 3FN.
• Desnormalize só de propósito (resumos, fotografias do passado); para a praticidade de "uma tabela só", prefira uma view.