Sessão nostalgia: formas normais

Na minha época e CEP, a "porta de entrada" para a profissión de TI era trabalhar com ERPs. Nem se chamavam assim na época; eram apenas "sistemas empresariais", geralmente desenvolvidos em uma linguagem de 4ª geração obscura vinculada a um banco de dados obscuro. Falo muito sobre isso neste texto.

Uma vez que bancos de dados relacionais eram a base de todo o nosso trabalho, os programas de treinamento das empresas e das ferramentas de desenvolvimento eram surpreendentemente bons ao abordar a questão das "formas normais" ou "normalização" de dados.

Era algo que, tipo, todo mundo e a mãe dele sabia. Era o nosso Evangelho. Era cobrado em entrevistas de emprego. Era motivo de piada, ou parafraseado como piada. É até estranho que hoje em dia seja algo desconhecido de tantos desenvolvedores.

Na verdade, as formas normais não têm nada de mais. Conforme resumiu um colega de trabalho mais velho, da minha época de programador mal-pago de ERP, elas são apenas bom senso. Um desenvolvedor ou DBA com bom senso produziria um banco de dados normalizado, pelo menos até o terceiro nível (3NF para os íntimos) mesmo que nunca tenha ouvido falar de formas normais.

Para ilustrar as formas normais, e mostrar essa maravilha à geração Z, vou usar o mesmo exemplo da época: clientes e pedidos.

# Cliente	Nome Cliente	# Pedido	# Produto	Nome Produto
1	Merposa S/A	123	54, 98	Detergente, Pneu
			77	Quinino
2	Lavou Desfiou Confecções	125	54	Detergente
2	Lavou Desfiou Confecções	125	69	Arreios
3	Meu Ovo Verdureira
4	Banco Anfíbio	126

A tabela acima está na forma normal "nula", UNF ou 0NF. Como dizíamos antigamente, esse banco de dados é um "bando de dados". A organização dos dados tem diversos problemas óbvios.

Primeira forma normal ou 1NF

A primeira forma normal prescreve que

cada célula (linha x coluna) possui apenas um valor atômico (indivisível);
a tabela não depende de uma ordem em especial para fazer sentido;
não há linhas duplicadas.

O exemplo abaixo possui os mesmos dados que o anterior, porém está 1NF.

# Cliente	Nome Cliente	# Pedido	# Produto	Nome Produto
1	Merposa S/A	123	54	Detergente
1	Merposa S/A	123	98	Pneu
1	Merposa S/A	123	77	Quinino
2	Lavou Desfiou Confecções	125	54	Detergente
2	Lavou Desfiou Confecções	125	69	Arreios
3	Meu Ovo Verdureira
4	Banco Anfíbio	126

Aqui nós já temos um arremedo de organização. A tabela acima já se parece com uma "view", ou seja, o resultado de uma consulta SQL que junta três tabelas normalizadas (cliente, pedido e detalhes do pedido).

O que significam as últimas linhas, com algumas colunas nulas? Neste exemplo, estamos presumindo que todo o banco de dados está nesta tabela, então este é o jeito de representar um cliente sem pedidos (3) ou um cliente com um pedido ainda sem itens (4).

A 1NF fala de valor "atômico", ou seja, indivisível. Alguns acham que, por conta disso, uma coluna de data deveria ser quebrada em três: dia, mês e ano. Vamos com calma... nenhum desses três valores faz sentido individualmente, o "átomo" é feito pelos três juntos.

Poderíamos argumentar ainda que a representação de uma data como dia/mês/ano é para consumo humano, que na realidade a data é um número inteiro (e.g. tantos dias desde 01/01/1900 ou desde o nascimento de Cristo).

Chaves, chaves, chaves

Agora que já temos a nossa tabela 1NF, podemos pensar em definir uma chave, ou seja, uma coluna ou conjunto de colunas que identifica e representa a linha toda, e que podemos usar para consultar o banco de dados.

Uma chave óbvia é a totalidade das colunas, já que uma tabela 1NF não tem linhas repetidas. Isto é um exemplo de superchave, ou seja, uma chave que cumpre seu papel, porém possui mais colunas que o mínimo necessário.

Estamos interessados em chaves que cumpram seu papel com o menor número possível de colunas, se possível uma só. Estas chaves "ótimas" são chamadas chaves candidatas, e a tabela pode ter mais de uma. Dentre elas, escolhemos uma, que denominamos chave primária.

Chaves candidatas não são coisas que podemos escolher a bel-prazer. Elas existem, ou não existem, a depender unicamente da tabela e das regras de negócio.

Na tabela de exemplo, uma chave candidata parece ser a chave composta '# Cliente' + '# Pedido' + '# Produto'.

Porém, o que acontece se um pedido contém duas vezes o mesmo produto? Isto pode acontecer no mundo real, mas a chave candidata acima impediria que tal pedido fosse cadastrado.

Para resolver o problema, vamos adicionar uma coluna '# Seq', que é o número sequencial do item do pedido. Este número começa em 1 a cada pedido.

# Cliente*	Nome Cliente	# Pedido*	# Seq*	# Produto	Nome Produto
1	Merposa S/A	123	1	54	Detergente
1	Merposa S/A	123	2	98	Pneu
1	Merposa S/A	123	3	77	Quinino
2	Lavou Desfiou Confecções	125	1	54	Detergente
2	Lavou Desfiou Confecções	125	2	69	Arreios
3	Meu Ovo Verdureira
4	Banco Anfíbio	126

Adotamos a chave candidata '# Cliente' + '# Pedido' + '# Seq' como chave primária, quem não tem a desvantagem da outra. As respectivas colunas estão marcadas com asteriscos no cabeçalho da tabela.

Segunda forma normal ou 2NF

A segunda forma normal exige que cada coluna que não faça parte de uma chave candidata deve depender da totalidade de qualquer chave candidata.

No nosso exemplo, o nome do cliente possui este problema. A coluna 'Nome Cliente' depende apenas de '# Cliente', não da chave primária completa. Portanto, o lugar dessa coluna é em outra tabela.

Sendo assim, teremos duas tabelas agora: Clientes e Pedidos.

# Cliente*	Nome Cliente
1	Merposa S/A
2	Lavou Desfiou Confecções
3	Meu Ovo Verdureira
4	Banco Anfíbio

# Cliente*	# Pedido*	# Seq*	# Produto	Nome Produto
1	123	1	54	Detergente
1	123	2	98	Pneu
1	123	3	77	Quinino
2	125	1	54	Detergente
2	125	2	69	Arreios
4	126

A normalização dos dados para 2NF também resolveu o problema de como representar clientes sem pedidos sem recorrer a linhas parcialmente nulas. E sempre podemos "desnormalizar" nossos dados de 2NF para 1NF recorrendo a uma view, algo como

select clientes.*, pedidos.* from clientes
left outer join pedidos
on clientes.codigo = pedidos.cliente

A query acima lista o cliente 3 com pedido nulo. Se quisermos uma view contendo apenas clientes com pedidos, basta trocar LEFT OUTER JOIN por INNER JOIN.

Ainda no contexto da 2NF, temos de considerar se o número do pedido depende ou não do código do cliente. Isso depende das regras de negócio.

Pode até ser que alguma empresa numere seus pedidos individualmente por cliente (caso em que pode haver inúmeros pedidos #123, um por cliente) porém o mais usual é que o número do pedido seja sequencial crescente (só pode haver um pedido #123 na empresa inteira).

Neste caso, a chave primária da tabela de pedidos não precisa ser '# Cliente' + '# Pedido' + '# Seq'. Basta apenas '# Pedido' + '# Seq' para identificar as linhas.

Mas agora o código do cliente não faz mais parte da chave, e depende apenas de '# Pedido', não da totalidade da chave primária. Então a coluna '# Cliente' tem de ser deslocada para outra tabela:

Clientes

# Cliente*	Nome Cliente
1	Merposa S/A
2	Lavou Desfiou Confecções
3	Meu Ovo Verdureira
4	Banco Anfíbio

Pedidos

# Cliente	# Pedido*
1	123
2	125
4	126

Detalhes do pedido

# Pedido*	# Seq*	# Produto	Nome Produto
123	54	1	Detergente
123	98	2	Pneu
123	77	3	Quinino
125	54	1	Detergente
125	69	2	Arreios

Com esta decomposição, passamos a representar de forma civilizada o pedido #126, que existe mas não tem detalhes.

Uma definição de 2NF que se encontra muito em materiais de treinamento é a seguinte: colunas que não são parte da chave primária devem depender da totalidade da chave primária.

Esta definição está incorreta, ela é mais restritiva que a original. Se uma coluna depende de apenas parte da chave primária, mas ela mesma faz parte de outra chave candidata, sua existência não viola a 2NF.

A definição incorreta também é ambígua: uma tabela pode ter várias chaves candidatas, e não fica claro se temos de testar a relação de dependência com todas as chaves ou apenas com aquela que escolhemos como primária.

(Curiosamente, vida afora eu conheci a 2NF pela definição incorreta. Só fui descobrir isso quando tive de demonstrar a diferença entre 3NF e BCNF durante a escrita deste artigo.)

Na maioria das situações práticas, não faz diferença adotar esta ou aquela definição de 2NF. Precisamos "fabricar" um exemplo para mostrar a divergência:

Estudante	Curso	Instrutor
João	Matemática	Astromar
João	Português	Zilá
Maria	Matemática	Astromar
Maria	Português	Zilá

Na tabela acima, temos duas chaves candidatas: 'Estudante' + 'Curso' e 'Estudante' + 'Instrutor'. Além disso, existe a regra de negócio que cada curso possui apenas um instrutor e cada instrutor ministra apenas um curso.

Suponha que a chave primária seja 'Estudante' + 'Curso'. À primeira vista, 'Instrutor' parece violar a 2NF, porque ele depende apenas de 'Curso'. Porém, como 'Instrutor' é parte da outra chave candidata, ele está isento desta regra.

Isto fará diferença mais adiante, quando precisarmos distinguir a terceira forma normal da forma normal de Boyle-Codd.

Terceira forma normal ou 3NF

A terceira forma normal exige que toda coluna comum (que não é parte de uma chave) deve depender diretamente de uma chave.

Numa tabela 3NF, todas as colunas atendem à relação X→C, que lemos "se X, então C". Por exemplo: "Se o código do cliente é 1, então o nome do cliente é Merposa S/A". X é uma chave candidata. C é uma coluna que não faz parte de nenhuma chave candidata.

A 3NF veta as chamadas dependências transitivas. Formalmente, X→C→D. A coluna D depende de C, que não é chave nem faz parte de uma chave. Se uma dependência assim for encontrada, a tabela não é 3NF e deve ser consertada.

Exemplo: na tabela de detalhes do pedido, a chave primária é a tupla '# Pedido' + '# Seq'. O nome do produto (D) depende apenas do código do produto (C), que não é chave, nem faz parte de uma chave.

Precisamos resolver isto deslocando o nome do produto para outra tabela:

Clientes

# Cliente*	Nome Cliente
1	Merposa S/A
2	Lavou Desfiou Confecções
3	Meu Ovo Verdureira
4	Banco Anfíbio

Pedidos

# Cliente	# Pedido*
1	123
2	125
4	126

Detalhes do pedido

# Pedido*	# Seq*	# Produto
123	1	54
123	2	98
123	3	77
125	1	54
125	2	69

Produtos

# Produto*	Nome Produto
54	Detergente
98	Pneu
77	Quinino
69	Arreios

A 3NF não trata apenas de dependências relacionais, mas também funcionais. Por exemplo, suponha as colunas X e Y, onde Y é função de X. Essa função pode ser uma operação matemática, uma manipulação de string, o que for. Pela terceira forma normal, Y é redundante e deve ser eliminada, e calculada sob demanda quando necessária.

Por questões de performance ou de rastreabilidade, nem sempre podemos seguir esta regra à risca. Por exemplo, se no detalhe do pedido temos as colunas 'Preço unitário' e 'Quantidade', a rigor não deveríamos ter a coluna 'Preço total', porque é um simples produto das outras duas. Porém, não raro o arredondamento dessa conta é uma regra de negócio, que pode mudar com o tempo. Então é prudente armazenar esse valor, pois ele pode não ser reprodutível no futuro.

Conforme prometemos antes, a segregação dos dados originais em quatro tabelas foi um exercício de senso comum. O que as formas normais fazem é codificar o senso comum, dar-lhe uma base científica.

As formas normais são cumulativas. Por exemplo, para um banco de dados ser considerado 3NF, ele deve cumprir todas as condições da UNF, 1NF e 2NF.

Forma normal de Boyle-Codd, BCNF ou 3.5NF

A BCNF é semelhante à 3NF, embora mais restritiva. Por conta disto, é apelidada de 3.5NF. Quase sempre um banco de dados 3NF já é também BCNF, o que faz muitos acreditarem que sejam a mesma coisa.

Tanto a 3NF quanto a BCNF proíbem dependências transitivas X→C→D, ou seja, coluna D depende de C que por sua vez depende de uma chave X. A diferença é a seguinte:

Na 3NF, só se considera os casos em que C e D não fazem parte de nenhuma chave candidata.
Na BCNF, C e/ou D podem fazer parte de alguma chave candidata, até mesmo de X.

Dissemos antes que alguns materiais trazem uma definição incorreta e mais restrita de 2NF. Para quem segue essa definição incorreta, a 3NF e a BCNF acabam sendo equivalentes, e um erro cancela o outro... (Eu tento provar isto no final deste texto.)

Vamos retornar ao exemplo que usamos para ilustrar essa questão:

Estudante	Curso	Instrutor
João	Matemática	Astromar
João	Português	Zilá
Maria	Matemática	Astromar
Maria	Português	Zilá

Como dito antes, a regra de negócio é que cada professor só ministra um curso e cada curso só é ministrado por um professor.

Assim, há uma redundância óbvia, porém ela não é sanada pela 2NF porque todas as colunas fazem parte de alguma chave candidata; e não é sanada pela 3NF porque não há colunas suficientes para haver uma dependência transitiva.

Esta tabela viola a BCNF porque 'Instrutor' depende apenas de 'Curso', porém 'Curso' não é uma chave candidata.

Pela definição incorreta e mais restrita de 2NF, a tabela acima não está na 2NF, porque quando a chave primária escolhida é 'Estudante' + 'Curso', a coluna 'Instrutor' depende apenas de parte da chave — ignorando o fato de 'Instrutor' ser isento por fazer parte de outra chave candidata.

Mesmo quando usamos a definição correta (mais liberal) de 2NF, são poucas as situações em que 3NF e BCNF divergem. Precisamos possuir duas ou mais chaves candidatas que compartilhem colunas. É difícil sair-se com um exemplo que não pareça forçado.

As formas normais superiores à 3NF/BCNF são menos conhecidas. Não é que elas não sejam importantes; é que as patologias que elas tratam são relativamente raras, é difícil dar exemplos práticos, e isto prejudica a didática.

Quarta forma normal (4NF)

Esta forma normal visa eliminar "mini explosões combinatoriais". Nosso exemplo de pedidos de clientes já está na 4NF, então vamos ter de fabricar um exemplo novo para explicá-la.

Imagine a seguinte tabela, que relaciona clientes a locais de entrega e condições de pagamento.

Cliente	CEP de entrega	Condição de pagamento
Merposa S/A	12345	30/60/90
Merposa S/A	54321	0/30
Merposa S/A	23456	À vista
Banco Anfíbio	23666	Pgto. adiantado
Lavou Desfiou Confecções	12345	0/30

A pergunta é a seguinte: existe alguma relação entre região de entrega e condição de pagamento, dado um mesmo cliente? Na política da sua empresa, existe o caso de um cliente poder pagar em 30/60/90 apenas quando a entrega for feita em determinado CEP?

Se sim, a tabela acima está ok. Se não, ela está violando a 4NF, e deve ser decomposta em duas tabelas: Cliente x CEP e Cliente x Condição de Pagamento.

Do contrário, haverá uma pequena mas desnecessária explosão combinatorial. Se o cliente possui 3 endereços de entrega e pode optar dentre 4 condições de pagamento, a tabela tem de ter 12 linhas por cliente para expressar todas as possibilidades.

Apesar de ser um problema óbvio de modelagem, ele não é detectado pelas formas normais até 3NF. A quarta forma normal é que finalmente veda "dependências multivaloradas independentes entre si".

No caso, CEP e condição de pagamento são multivaloradas (pode haver múltiplas regiões de entrega por cliente, bem como múltiplas condições de pagamento oferecidas a cada cliente) mas independentes entre si (a condição de pagamento não dita a região de entrega ou vice-versa).

Outro possível caso de violação da 4NF é o seguinte. Vamos rever nossa tabela de detalhes do pedido:

# Pedido	# Seq	# Produto

Suponha por um momento que a nossa regra de negócio é que cada produto conste no máximo uma vez por pedido. Neste caso, nós temos duas dependências multivaloradas: um pedido pode ter diversos números de sequência de detalhe, e pode ter diversos produtos.

Mas não existe produto cartesiano entre eles. Dentro de um mesmo pedido, tanto o sequencial quanto o produto tem de ser únicos. Então, alguma coisa está desalinhada. Ou a coluna '# Seq' é desnecessária, ou é errado forçar que um produto conste no máximo uma vez por pedido (no nível do banco de dados).

Antes de prosseguir à quinta forma normal, preciso introduzir o conceito de relacionamentos muitos-para-muitos, para o caso de não ser familiar ao leitor.

Relacionamentos n:n

A relação entre clientes e pedidos é uma relação um-para-muitos (1:n), porque um cliente pode estar relacionado a múltiplos pedidos, mas cada pedido é relacionado a apenas um cliente. Esta relação não precisa de nenhuma tabela além das citadas (clientes e pedidos), basta fazer um JOIN entre elas.

Já a relação entre pedidos e produtos é muitos-para-muitos (n:n) pois cada pedido pode conter muitos produtos, e cada produto pode constar em inúmeros pedidos. A relação n:n pode ser um produto cartesiano, mas geralmente não o é. Nem todo pedido lista todos os produtos possíveis, e é certo que nenhum produto consta em todos os pedidos.

Se a relação n:n não é um produto cartesiano, a lista das relações realmente existentes tem de ser armazenado em alguma tabela, que no nosso caso é a de detalhes do pedido. Não podemos fazer o JOIN diretamente entre pedidos e produtos; a junção entre elas tem de ser intermediada pela tabela de detalhes do pedido.

Quinta forma normal (5NF)

Esta forma normal procura detectar relacionamentos muitos-para-muitos envolvendo três tabelas (n:n:n) ou mais.

Considere a tabela abaixo, que armazena uma relação n:n:n entre clientes, vendedores e produtos:

Cliente	Vendedor	Produto
Merposa S/A	João	Detergente
Meu Ovo Verdureira	Maria	Pneu
Lavou Desfiou Confecções	José	Quinino

Esta tabela codifica relações de exclusividade entre vendedores, clientes e produtos. Por exemplo, apenas o vendedor João pode vender detergente para a empresa Merposa. A única chave candidata é composta por todas as três colunas, portanto ela já está na BCNF, e também presumimos que esteja na 4NF.

Analogamente à 4NF, temos aqui um produto cartesiano que provavelmente não reflete a realidade. Numa empresa com 10.000 clientes, 200 produtos e 30 vendedores, teríamos até 60.000.000 linhas. Será que realmente existem todas estas possibilidades? Provavelmente não.

Se tivermos como decompor esta relação tríplice em 3 tabelas, com 2 colunas cada, e ao fazer JOIN delas obtemos uma "view" idêntica à tabela original, isto significa que a tabela não está na 5NF.

Só temos como saber se a decomposição é possível conhecendo as regras de negócio. Um vendedor detém exclusividade na venda de algum produto? Existem produtos que são ofertados apenas a determinados clientes?

Como se pode ver, a 5NF já é bem mais obscura a difícil de exemplificar que as anteriores. Violações da 5NF são raras, mesmo quando o arquiteto do banco de dados não tenha atentado para esta forma normal.

O que distingue a 5NF da 4NF é que todas as três colunas do exemplo têm algum grau de dependência mútua. Se pudermos demonstrar que duas colunas são independentes entre si, e que a decomposição pode ser feita com apenas 2 tabelas em vez de 3, trata-se de um caso de aplicar a 4NF, não a 5NF.

Por exemplo, se as regras de negócio afirmarem que qualquer cliente pode comprar qualquer produto, não existe relação entre clientes e produtos. Existem duas relações multivaloradas independentes: clientes x vendedores (que vendedor atende qual cliente) e vendedores x produtos (que vendedor pode oferecer qual produto). O problema degradou para 4NF.

Sexta forma normal (6NF)

A proposta da sexta forma normal é bastante simples: cada tabela só pode possuir uma coluna além da chave primária.

Por exemplo, uma tabela de clientes típica tem dezenas de colunas: razão social, endereço, telefone, cidade, UF, etc. etc. além é claro da chave primária que pode ser um código numérico ou o CNPJ (má ideia, mas não é incomum).

Para adequar-se à 6NF, essa tabela teria de ser desmembrada em dezenas: uma com o código e a razão social, outra com o código e o endereço, outra com o código e o telefone...

Mas por que alguém faria isto?!

Uma possível utilidade é quando se usa versionamento de linhas. Neste caso, armazenamos todas as versões passadas de cada linha da tabela; a chave primária convencional é composta com um timestamp. Para obter a linha mais atual, buscamos pelo timestamp mais recente.

Numa tabela versionada que tenha muitas colunas, haverá muita repetição de informação entre versões, e não fica imediatamente claro qual coluna foi alterada entre uma versão e outra.

Se a tabela está na 6NF, essa repetição é evitada. No exemplo da tabela de clientes, se apenas o telefone foi alterado, apenas a tabela de telefones de clientes ganha uma linha nova. Além da economia de espaço, temos um registro histórico preciso do que foi alterado, e quando.

Se o cadastro do cliente precisa de um campo novo, basta criar uma tabela a mais, não é necessário mudar o "schema" das tabelas preexistentes. A existência do campo novo é sinalizada pelo fato da tabela só possuir valores para aquele campo a partir de uma certa data.

Desvantagens: consultar os dados do cliente para e.g. emitir uma nota fiscal implica em fazer JOIN com inúmeras tabelas. Além de inconveniente, pode causar problemas de performance. Por conta disso, esta forma normal é raramente usada. (A questão de administração de inúmeras tabelas eu não considero tão ruim porque isso pode ser automatizado.)

Em vez de adotar a 6NF, podemos simplesmente usar um banco de dados que faça o versionamento automático de linhas. Nunca trabalhei com banco de dados versionado num contexto de ERP, então não sei qual opção é mais interessante.

Denormalização

As formas normais visam basicamente a) reduzir o consumo de disco pela diminuição da redundância de dados, e b) reduzir o impacto de operações de escrita, reduzindo o número de linhas afetadas quando a informação precisa ser atualizada.

As formas normais presumem que o banco de dados relacional é rápido na hora de fazer JOINs de tabelas, e que a velocidade de leitura é sempre a mesma, independente do nível de normalização do banco de dados. Nem sempre isto é verdadeiro.

Um modo muito comum de melhorar tanto a performance de leitura quanto a conveniência de uso do banco de dados (permitindo consultas SQL menos complicadas) é violar de propósito alguma forma normal. Isto é chamado de "denormalização". Alguns exemplos:

(3NF) Manter informação redundante e.g. nome de produto no detalhe do pedido, evitando ter de fazer JOIN com a tabela de produtos, quiçá para garantir o nome histórico do produto (ao reemitir uma nota fiscal antiga, ela tem de sair com os nomes de produto da sua época).
(3NF) Pré-cálculo de valores, que idealmente seriam calculados sob demanda.
(3NF) Armazenar valores pré-agregados ou pré-agrupados, em vez de usar as funções SUM(), COUNT(), GROUP BY etc. do SQL sob demanda.
(2NF) Armazenar valores pré-agregados que envolvam um relacionamento 1:n, por exemplo COUNT() dos pedidos de um cliente.
(3NF/2NF/1NF) Armazenar "views" (resultados de consultas SQL com JOIN) em tabelas temporárias, quando a "view" custa muito para ser gerada, e será reutilizada muitas vezes.
(3NF) Nem sempre é prático remover todas as dependências transitivas. Cidade e UF dependem do CEP, então pela 3NF esses dados deveriam ir para outra tabela cuja chave primária é o CEP. Mas quem se dá ao trabalho? E poderia dar problema com clientes estrangeiros.

E é claro, NoSQL é uma realidade. Dados em bancos não-relacionais provavelmente não vão se encaixar nas formas normais, que foram pensadas para bancos relacionais.

É uma premissa dos bancos relacionais, e das formas normais, que leituras serão frequentes e escritas serão raras. Existem situações em que é o inverso, por exemplo coleta de logs e de informações estilo "time-series". A ingestão de dados é enorme, para de vez em nunca alguém consultá-los. Neste caso, é melhor optar por um banco NoSQL, e de um tipo que seja adequado ao caso de uso, porque há vários tipos.

Chaves e índices

Citamos antes as chaves primárias. A chave primária identifica as linhas de uma tabela. Pense no que significa "identidade". Significa que um valor de chave primária ocorre no máximo uma vez na tabela, e todas as demais informações da linha dependem dela. Uma chave primária não pode ser nula.

Chaves primárias têm de ser bem escolhidas, porque elas são a espinha dorsal do banco de dados. Na dúvida, a chave primária deve ser um número sequencial crescente. Sempre existe a tentação de usar alguma informação do mundo real que parece única. Por exemplo, o CNPJ de cliente.

Por que o CNPJ não é uma boa chave primária? Porque nem todo cliente possui CNPJ. Por exemplo, clientes estrangeiros. Um truque de época era colocar o CNPJ do despachante aduaneiro. Funcionava até o mesmo despachante representar mais outro cliente do exterior. (Nem vou entrar no mérito de armazenar CNPJs como números inteiros, em vez de strings. Agora que o CNPJ virou alfanumérico, deve estar havendo muito choro e ranger de dentes no mundo ERP.)

A chave estrangeira é uma chave primária de outra tabela. Por exemplo, na tabela de pedidos, o código do cliente é uma chave estrangeira. Em geral, é através delas que as tabelas constroem relacionamentos.

O CNPJ de cliente é uma "quase chave" ou "chave parcial". Não é uma chave, mas tem características de chave. Veja que nem todo cliente tem CNPJ, mas não pode haver dois registros de cliente com o mesmo CNPJ.

Em nossa obsessão por performance em computadores fracos, como pouco disco e pouca memória, muitas vezes confundíamos chaves com índices.

Índices são estruturas de dados, classicamente baseadas em B-Trees, que aceleram a busca por um elemento. Ao buscar uma palavra num dicionário de 100.000 palavras, teremos de ler 100.000 palavras se procuramos sequencialmente. Ou, pelo fato do dicionário estar em ordem alfabética, podemos achá-la em apenas 17 leituras (logaritmo de 100 mil na base 2).

Toda chave primária tem índice, para que os JOINs sejam rápidos. Mas pode ser necessário indexar colunas que não são chaves. E não necessariamente todo índice é uma boa chave. O CNPJ de cliente deve ser um índice, pois numa empresa é comum consultar a base de clientes pelo CNPJ. Existem tipos de índices mais adequados a textos, e.g. o nome do cliente, que é interessante poder consultar por um fragmento. Nem por isso CNPJ ou nome de cliente fazem boas chaves primárias.

É claro, se o negócio atende exclusivamente empresas e o mercado interno, CNPJ é sim uma chave candidata. Na literatura, chave candidata é uma coluna que reúne as condições para ser uma chave primária. Mesmo assim não é recomendável, porque empresas crescem e mudam suas políticas.

Seja como for, índices e chaves ocupavam espaços muitos próximos dentro das nossas cabeças. Como os índices também têm um custo de disco e processamento, procurávamos criar índices baseados em chaves compostas, tentando obter o melhor de dois mundos.

No mestrado, aprendi que, na ciência da computação, ninguém liga muito para índices. Na cabeça dos "pais fundadores" dos bancos relacionais, índices são detalhes de implementação; oportunidades de otimização que um banco de dados decente acharia sozinho.

Na ciência relacional, a preocupação é com a cardinalidade dos valores em cada coluna. Por exemplo, o CNPJ de cliente é um valor de alta cardinalidade, pois quando existe, é único por cliente.

O CEP do cliente tem média cardinalidade; tem alguma repetição, mas ainda particiona a tabela em diversos pedaços de tamanho pequeno e parecido. A cidade tem cardinalidade média-baixa, pois algumas partições ficam muito maiores que outras (e.g. clientes de São Paulo/SP x clientes de Iomerê/SC).

Já uma coluna como "Cliente Ativo" que admite apenas dois valores ("Sim" ou "Não"), ou o gênero (M/F) de um cliente pessoa física, possui baixíssima cardinalidade. Uma coluna como "Suframa (S/N)" é pior ainda, pois além de ter baixa cardinalidade, tende a particionar a tabela em dois pedaços muito diferentes. Uma empresa geralmente terá muito mais clientes fora da Zona Franca de Manaus do que dentro.

Um bom banco de dados procura detectar a cardinalidade das colunas, pois numa consulta SQL o filtro sobre uma coluna de alta cardinalidade é muito mais eficiente. Uma coluna de alta cardinalidade, poucas linhas nulas e utilizada como filtro em consultas SQL é forte candidata a um índice.

No meu tempo, os bancos de dados não faziam isso, ou tentavam fazer e erravam. Então tínhamos de usar algum truque para convencer o banco de dados a filtrar primeiro pela coluna que nós presumíamos possuir cardinalidade maior.

Hoje em dia, a ênfase em índices vai sendo diluída pelos bancos NoSQL. Por exemplo, em bancos de dados como Apache Hive simplesmente não se fala de índices. A única otimização "visível", que temos de escolher explicitamente, é a partição do banco em arquivos, segundo o valor de uma ou duas colunas.

Cada arquivo ("parquet") possui estruturas internas que são menos precisas que índices convencionais, porém aceleram as consultas quase tão bem quanto. E a melhor parte é que está tudo lá, é detalhe de implementação, é só usar.

Hoje em dia, RAM é abundante. Muitas vezes o banco de dados cabe na RAM; existem mesmo produtos que funcionam com esta premissa (Redis, MemGraph, etc.) e usam disco apenas para persistir dados. Em RAM, mesmo as buscas sequenciais são muito rápidas e não há necessidade de usar muitos índices.

Definição de 2NF versus BCNF

Dissemos antes que muita gente (inclusive eu) conhece uma definição incorreta e mais restrita de 2NF, que vou chamar de 2NF*. Uma tabela que atende à 2NF* e 3NF, é (acredito eu) automaticamente BCNF, o que induz a outro erro: que 3NF é o mesmo que BCNF.

A ideia é provar que 2NF* implica em 3NF = BCNF. Vamos começar com algumas definições de conjuntos de colunas de uma tabela:

T: conjunto de todas as colunas da tabela
C: C ⊆ T
(C é um subconjunto de colunas da tabela)
K: C que é chave candidata
R: ∃C, R = C, ∀K R ∩ K = ∅
(R é um subconjunto de colunas regulares, que não figuram em nenhuma chave)
V: ∃C, V = C, V ⊂ K
(V é fragmento de chave)
S: ∃K, ∃R, ∃C, S = C, S = K ∪ R
(S é superchave; superconjunto de uma chave)

Também precisamos trazer alguns axiomas e teoremas para dar algum rigor ao nosso raciocínio. Axiomas de Armstrong:

[ C ⊆ C₁ ] → [ C₁ → C ]
A1: axioma da trivialidade. Um conjunto de colunas sempre depende trivialmente de si mesmo ou de qualquer superconjunto de si mesmo.
[ C → C₁ ] → [ C ∪ C₂ → C₁ ∪ C₂ ]
A2: axioma da ampliação. Adicionar colunas dos dois lados de uma relação de dependência mantém sua validade.
[ C → C₁, C₁ → C₂ ] → [ C → C₂ ]
A3: axioma da transitividade.

Teoremas e corolários:

[ C → C₁, C → C₂ ] → [ C → C₁ ∪ C₂ ]
B1: teorema da união. Se C₁ e C₂ dependem separadamente de C, continuam dependendo quando unidos.
[ C → C₁, C₂ → C₃ ] → [ C ∪ C₂ → C₁ ∪ C₃ ]
B2: teorema da composição.
[ C → C₁ ∪ C₂ ] → [ C → C₁, C → C₂ ]
B3: teorema da decomposição (B1 reverso). Se um conjunto de colunas depende de C, qualquer subconjunto delas também depende de C.
[ C → C₁, C₁ ∪ C₂ → C₃ ] → [ C ∪ C₂ → C₃ ]
B4: teorema da pseudotransitividade.
[ C → C₁ ] → [ C ∪ C₂ → C₁ ]
B5: ampliação à esquerda (A2+B3). Se C₁ depende de C, também depende (trivialmente) de um superconjunto de C.
[ C ∪ C₂ → C₁, C₂ ⇏ C₁ ] → [ C → C₁ ]
B6: redução à esquerda (B5 reversa). Se C₂ faz parte do superconjunto dominante, mas sabemos que C₁ não depende de C₂, então C₂ é parte trivial e pode ser desprezada.

Teoremas e corolários envolvendo chaves candidatas:

[ C → K ] → [ C = K₁ ]
Z1: se uma chave candidata depende irredutivelmente de um grupo de colunas, esse grupo também é uma chave candidata.
[ K → C, K ⊂ S ] → [ S → C ]
Z2: se um grupo de colunas C depende da chave, também depende (trivialmente) da superchave (aplicação de B5).
C → V, K = V ∪ V₁
C ∪ V₁ → V ∪ V₁ (A2)
C ∪ V₁ → K
K₁ = C ∪ V₁ (Z1)
C = V₂
K ∩ K₁ = V₁
Z3: se um fragmento V da chave K depende irredutivelmente de um grupo de colunas C, então C é fragmento de outra chave K₁, e as chaves K e K₁ compartilham um terceiro fragmento V₁.

Definição de 2NF:

K → R
V ⇏ R

Definição de 2NF*:

K → C
V ⇏ C

A diferença é que C pode conter colunas primas, ou seja, que fazem parte de alguma chave K, enquanto R não pode.

Definição de 3NF:

K → R ⇏ R₁

Forma normal de Boyle-Codd:

K → C ⇏ C₁

A rigor a definição de BCNF usa superchave (S) em vez de chave (K), mas sempre podemos usar os axiomas para reduzir à forma acima.

Uma tabela que é 3NF mas não é BCNF satisfaz, ao mesmo tempo:

K → R ⇏ R₁
K → C → C₁

ou seja, existe algum C₁ que depende não-trivialmente de algum outro C. Porém, como a tabela está na 3NF, não existe R₁ que dependa de R.

Portanto, ou C contém um fragmento de chave V, ou C₁ contém um fragmento de chave V₁, ou ambos. Temos oito casos a considerar, e veremos que apenas o último é possível:

Caso 1: K → R → V₁
Caso 2: K → R → R₁ ∪ V₁
Caso 3: K → R ∪ V → R₁
Caso 4: K → R ∪ V → V₁
Caso 5: K → R ∪ V → R₁ ∪ V₁
Caso 6: K → V → R₁
Caso 7: K → V → R₁ ∪ V₁
Caso 8: K → V → V₁

Caso 1: K → R → V₁

Segundo a regra Z3, como R → V₁, R teria de ser um fragmento de chave, o que contradiz a premissa que R não faz parte de nenhuma chave.

Caso 2: K → R → R₁ ∪ V₁

Usando a regra B3, temos que R → R₁, que viola a 3NF. Isto contradiz a premissa que a tabela está na 3NF.

Caso 3: K → R ∪ V → R₁

Sabemos que R ⇏ R₁, então, pela regra B6, V → R₁, que viola a 2NF. Isto contradiz a premissa que a tabela está na 3NF (que implica em estar na 2NF).

Caso 4: K → R ∪ V → V₁

Segundo a regra Z3, R ∪ V teria de ser um fragmento de chave, o que contradiz a premissa que R não faz parte de nenhuma chave.

Caso 5: K → R ∪ V → R₁ ∪ V₁

Usando a regra B3, concluímos que

R ∪ V → R₁, condição que tem o mesmo defeito do caso 3.
R ∪ V → V₁, condição que tem o mesmo defeito do caso 4.

Caso 6: K → V → R₁

V → R₁ viola a 2NF.

Caso 7: K → V → R₁ ∪ V₁

Pela regra B3, V → R₁, o que viola a 2NF.

Caso 8: K → V → V₁

V → V₁ viola a 2NF*, lembrando que a 2NF* proíbe dependências na forma V→C, e V₁ é igual a algum C.

Portanto, nenhum caso pode ocorrer com uma tabela que esteja na 2NF*. Finalmente logramos provar que a adoção da forma incorreta da 2NF torna 3NF equivalente a BCNF.

Se a tabela for 2NF segundo a definição correta de 2NF, o caso 8 ainda pode ocorrer, mas apenas em circunstâncias específicas. Segundo a regra Z3, a condição V → V₁ só pode ser verdadeira se existem pelo menos duas chaves compostas que compartilhem colunas.

Esta é a condição necessária para BCNF divergir de 3NF. Apenas quando esta condição é observada é que precisamos verificar que uma tabela 3NF não viola a BCNF.

Exemplo: a tabela com as colunas 1234, com a chave 12, portanto 12→3. Suponha ainda que 3→2, relação que viola a BCNF. Neste caso, 3 pode substituir 2 na chave, e 13 também é chave.

Nem toda tabela assim viola a BCNF. Por exemplo, o nosso velho exemplo

# Pedido	# Seq	# Produto

Se admitirmos que existe apenas um produto por pedido, esta tabela tem duas chaves compostas que compartilham uma coluna: '# Pedido' + '# Seq', e '# Pedido' + '#Produto'. Isto não viola a BCNF, pois '# Produto' não é determinado unicamente por '# Seq', nem vice-versa.

Álgebra relacional x "aritmética" relacional

Os "pais fundadores" do banco de dados relacional imaginaram duas formas de expressar consultas ao banco: álgebra relacional e cálculo relacional.

Como se pode imaginar pelos nomes, cálculo relacional está num nível mais alto que álgebra relacional. Na matemática, uma fórmula de cálculo acaba sendo resolvida com álgebra (e uma fórmula algébrica acaba na aritmética). Da mesma forma, uma consulta expressa em cálculo relacional tem de ser transformada em álgebra relacional para ser executada.

SQL é uma linguagem de consulta que usa conceitos de ambas, mas é considerada mais próxima da álgebra relacional. A principal concorrente, QUEL, mais próxima do cálculo relacional, perdeu a guerra comercial, talvez porque a IBM empenhou seu peso ao promover o SQL.

Por exemplo, imagine esta consulta SQL simples:

select clientes.nome, pedidos.codigo from clientes
cross join pedidos

Alternativa equivalente:
select clientes.nome, pedidos.codigo from clientes, pedidos

Isto aqui é álgebra relacional 101: produto cartesiano entre duas tabelas. As linhas conterão as combinações de todos os clientes com todos os pedidos.

Provavelmente não é isto que queremos. Mais útil seria listar apenas as combinações em que o pedido pertence àquele cliente:

select clientes.nome, pedidos.codigo from clientes
inner join pedidos
on clientes.codigo = pedidos.cliente

As linhas produzidas por esta consulta serão um subconjunto da consulta anterior.

A pergunta é a seguinte: como o banco de dados realmente faz a busca dos pedidos e dos clilentes? Por que tabela ele deveria começar, para ser mais eficiente? Quem usa SQL normalmente não se preocupa com isso, o banco de dados que lute. Às vezes, quando uma consulta SQL fica muito lenta, temos de mexer na consulta para induzir o banco a escolher o caminho ótimo.

Eu sou do tempo da "aritmética relacional", também conhecido como "força bruta relacional": COBOL, Clipper, xBase. Estas linguagens de programação embutem suporte a "banco de dados", mas numa forma bem mais primitiva.

Elas têm tabelas, chaves e índice, mas nenhuma noção de relacionamentos entre tabelas, ou de oferecer uma "view" ordenada por um critério arbitrário, muito menos otimização automática de consultas. Ali não tem álgebra relacional. Tem "aritmética relacional" e olhe lá. A álgebra fica por conta do desenvolvedor.

Por exemplo, revisitemos esta consulta SQL simples:

select clientes.nome, pedidos.codigo from clientes
inner join pedidos
on clientes.codigo = pedidos.cliente

A implementação desta consulta em código xBase seria algo como

use clientes
set index to codigo
use pedidos

select pedidos
go top

// varre a tabela de pedidos na sua ordem natural 
do while .not. eof()
    // busca o cliente do pedido
    select clientes
    seek pedidos->cliente

    // imprime a linha da view
    ? "Cliente " + clientes->nome + " pedido #" + pedidos->codigo

    // pula para o próximo pedido
    select pedidos
    skip
enddo

No código acima, tivemos de tomar diversas decisões de otimização que podem ou não ser as melhores.

Escolhemos varrer a tabela de pedidos, e em função dela, procurar pelo cliente. Seria mais eficiente fazer o contrário? Fizemos assim porque o código do cliente não está indexado na tabela de pedidos, mas talvez seria melhor criar esse índice. Qual a cardinalidade de pedidos, clientes, e pedidos por cliente?

Se quiséssemos emular a consulta SQL com LEFT OUTER JOIN em vez de INNER JOIN, aí seríamos obrigados a começar varrendo a tabela de clientes. O que seria mudar uma palavra no SQL, significa virar nosso algoritmo do avesso.

O código acima não trata a situação em que o cliente não existe (quebra de integridade referencial).

A tabela de pedidos é varrida em "ordem natural", ou seja, provavelmente na ordem que os pedidos foram adicionados à tabela. Um relatório para consumo humano teria de usar uma ordem mais civilizada, como data do pedido, ou pelo menos o código.

Fazer algo como "ORDER BY" nessas linguagens era um pesadelo em termos de performance e de implementação. A solução era copiar os dados filtrados para outra tabela em disco, e ordená-la. Ou criar um índice que coincida com a ordem desejada. A última opção era a mais eficiente, porém o índice tem um custo recorrente. Não dava para sair sair criando índices para todas as colunas.

Nem vou discutir questões como transações e garantias ACID. Não havia garantias, era tudo implementado em código. Ou mitigado com expedientes do tipo proibir o contador de imprimir certos relatórios naquela hora do dia em que chegavam as transportadoras (sempre impacientes e com pressa) e o faturamento emitia as notas fiscais o mais rápido que podia.

Lembrando que o código acima tocava diretamente os arquivos do banco de dados. Imagine o fio da navalha: inúmeros PCs rodando MS-DOS mexendo nos mesmos arquivos, compartilhados via servidor de arquivos Novell. Óbvio que, de vez em quando, corrompia-se um índice ou uma tabela.

Quem trabalhou desse jeito por décadas desconfiou quando os bancos de dados SQL começaram a despontar. Como é que o banco conseguiria fazer a otimização fina das consultas?! E muitos bancos engoliam o caroço no primeiro LEFT OUTER JOIN. Porém melhoraram muitíssimo desde então.

SQL tem de ser usado como SQL. Esse é um dos motivos que eu não sou muito fã de ORMs. Na minha visão, quem usa ORM vai acabar produzindo código com "aritmética relacional" em vez de procurar usar SQL direito. O argumento de haver diversos dialetos de SQL não me convence; existem formas simples de tornar o código independente do dialeto SQL sem perder a essência do SQL.

Além do mais, conforme comentou um colega de profissão, quantas vezes na vida você já teve de pegar uma aplicação e trocar o banco de dados dela? Isso quase nunca acontece.

Nos anos 1990, os sistemas ERP migraram de linguagens que faziam "aritmética relacional" para o modelo "cliente-servidor", com a adoção de algum banco de dados relacional. O cliente-servidor é melhor que um sistema rodando em computadores MS-DOS com servidor Novell, mas pode ser até pior (conceitualmente) que os velhos terminais de texto ligados a um computador central.

Cliente-servidor tem uma falha fatal: o cliente tem de rodar uma cópia completa da aplicação ERP. Isto era chamado de "fat client" pelos detratores. Alguns evangelizavam mitigar o problema codificando todas as regras de negócio no banco de dados, com triggers, stored procedures, etc. Mas fazer isso com uma linguagem não-Turing-completa é dose. Parte das regras de negócio sempre acabava no cliente.

Além de inseguro, o "fat client" tem problemas de performance, conforme muitos entusiastas descobriram, não antes de já ter investido horrores portando seus sistemas ERP para cliente-servidor.

A arquitetura correta é em três camadas: banco de dados, um servidor de aplicação implementando regras de negócio, e o computador-cliente cuidando apenas da apresentação, sendo análogo a um terminal de texto. Nenhum processamento não-trivial deve ocorrer no cliente.

A rápida popularização de sistemas baseados em Web acabou forçando todo mundo a migrar para o modelo de três camadas, porque o cliente Web é tão inseguro e limitado que não há como não tratá-lo como um análogo de terminal de texto. Até existem umas gambiarras por aí que permitem a um código Javascript conversar diretamente com um banco de dados, mas obviamente não é para uso "sério".

Modelo entidade-relacionamento

Também digno de nota é o modelo entidade-relacionamento, do Dr. Chen. O modelo ER era outra figurinha fácil nos treinamentos de banco de dados relacionais da minha época. É uma ferramenta de modelagem de banco de dados, que lembra UML, na verdade o UML incorporou bastante do modelo ER.

A ideia é usar substantivos e verbos do mundo real para expressar a estrutura do banco. Por exemplo: cliente FAZ pedido. Pedido POSSUI detalhes. Detalhe de pedido REFERENCIA produto. As tabelas são objetos passivos, representados por retângulos. Os relacionamentos, que serão os JOINs das suas consultas SQL, são objetos ativos, representados por losangos. Os vértices entre uns e outros têm símbolos especiais para indicar "constraints", relacionamentos 1:1, 1:n, etc.

É uma técnica interessante, mais intuitiva que as formas normais. Dignos de nota são os relacionamentos reflexivos, que apontam para a mesma tabela (e.g. funcionário É CHEFIADO POR [outro] funcionário) e os relacionamentos n:n que são representados por losangos mas na verdade são tabelas.

Bibliografia

Date, C J. Introdução a sistemas de banco de dados. Rio de Janeiro: Elsevier, 2003.

Silberschatz et al. Sistema de Banco de Dados. São Paulo: Elsevier, 2012.