Esta é a terceira parte da série, Five Normal Forms. Os títulos das duas primeiras partes (tutoriais) são Primeira Forma Normal, seguida de Segunda Forma Normal. Nesta parte da série, a Terceira Forma Normal é explicada.
A explicação segue o enredo: Um pai morreu e deixou algum dinheiro para seu filho. O filho decidiu investir o dinheiro em uma loja de conveniência. Uma loja de conveniência, também conhecida como loja de conveniência, é uma pequena empresa de varejo que recebe itens do dia a dia de fornecedores e os vende para clientes individuais na vizinhança.
Neste momento, a loja já está abastecida, e algumas vendas já foram feitas. O filho, que é o proprietário do negócio, tem alguns funcionários, que neste tutorial são chamados de escriturários. O proprietário e qualquer funcionário podem receber suprimentos e fazer vendas após o registro dos produtos.
No entanto, antes de a loja começar, nem o proprietário nem os funcionários sabiam nada sobre formulários normais. Então, eles estavam registrando tudo como transações em uma tabela e um caderno de exercícios. Eles não tinham computador.
Você, leitor, concluiu as cinco partes desta série de tutoriais; agora você é um desenvolvedor de banco de dados. O proprietário da loja de conveniência é seu amigo. Você visitou a loja há dois dias e treinou o proprietário e os balconistas na produção de uma mesa em sua primeira forma normal. Você também visitou a loja ontem e os treinou sobre como criar uma tabela na segunda forma normal a partir da primeira forma normal.
Hoje, você acabou de chegar à loja para uma visita para treiná-los sobre como produzir uma mesa na terceira forma normal a partir da segunda forma normal. Todas as tabelas que eles têm atualmente estão na segunda forma normal. As tabelas (por nome e títulos de coluna) são:
Produtos(productID, categoryID, produto)
Categorias(categoryID, categoria)
Vendas(saleID, cliente, funcionário, data)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Orders(orderID, fornecedor, funcionário, data)
OrderDetails(orderID, productID, numberBought, costPrice)
As chaves simples ou compostas são sublinhadas.
Depois de resumir o que foi ensinado nos dois dias anteriores e antes que você pudesse fazer qualquer coisa, o proprietário pergunta:
“E os números de telefone, endereços, etc., para clientes e funcionários?
E quanto à quantidade em estoque, nível de reabastecimento, etc., para produtos?
Eles precisam de suas próprias tabelas separadas ou devem ser encaixados nas tabelas atuais?
Você, o desenvolvedor do banco de dados, responde:
“Parabéns, Proprietário! Você introduziu indiretamente a questão da Terceira Forma Normal.”
Você continua.
Outras colunas necessárias
Outras colunas necessárias são adicionadas primeiro às tabelas anteriores, que estão em 1NF e 2NF. Alguns dos nomes das colunas anteriores foram modificados.
No mínimo, a tabela Categorias deve ter as seguintes colunas:
Categorias(categoryID, categoryName, descrição)
A descrição é um parágrafo curto que descreve a categoria. Esta tabela de categorias já está em 1NF, 2NF e 3NF. A 3FN é explicada a seguir:
No mínimo, a tabela Produtos deve ter as seguintes colunas:
Produtos (ID do produto, ID da categoria, ID do fornecedor, nome do produto, preço unitário, quantidade em estoque, nível do pedido)
À medida que cada produto é vendido, um nível baixo (número) de produtos será atingido quando o produto tiver que ser reordenado, portanto, os clientes não devem vir à loja e não ter o produto. Essa ausência não é boa para os negócios. quantidadeEmEstoque é o número de um determinado produto em estoque. Isso inclui o que está na loja e o que está na prateleira.
ID da categoria e ID do fornecedor são chaves estrangeiras. É por isso que eles têm traço sublinhado em vez de sublinhado único. A chave estrangeira é explicada abaixo. Na parte anterior da série (Segunda Forma Normal), categoryID fazia parte da chave primária com um único sublinhado devido à forma como foi obtido. No entanto, a partir da explicação abaixo, ficaria claro que o categoryID deve ser uma chave estrangeira (com um traço sublinhado).
Esta tabela de produtos já está em 1NF, 2NF e 3NF. Veja porque está na 3NF abaixo:
No mínimo, a tabela SaleDetails deve ter as seguintes colunas:
SaleDetails(saleID, productID, unitSellingPrice, quantidade, desconto)
Espera-se que o valor do desconto seja zero na maioria das vezes. Um desconto é o desconto que a loja oferece a um cliente.
No mínimo, a tabela OrderDetails deve ter as seguintes colunas:
OrderDetails(orderID, productID, unitCostPrice, quantidade, desconto)
Espera-se que o valor do desconto seja zero na maioria das vezes. O desconto aqui é o desconto que o fornecedor dá à loja.
Conforme visto abaixo, a tabela Produtos pode ser considerada na 2FN ou na 3FN. As tabelas Vendas e Pedidos possuem a emissão da 3NF. Somente a tabela de vendas será usada para explicar o problema e a solução. A 3NF para Tabela de Pedidos e Tabela de Produtos seguem raciocínio semelhante e seriam apenas cotadas.
Ao adicionar colunas, a tabela Sales seria:
Sales(saleID, dateSold customerName, phone, address, city, region, postalCode, Country, employee)
Sete colunas substituíram a coluna do cliente na tabela original. Como os clientes são pessoas da vizinhança, as células das colunas cidade, região (estado), código postal e país podem ser deixadas vazias, embora não sejam deixadas vazias neste artigo.
Esta tabela de vendas ainda está na 2NF, pois as regras da 1NF e da 2NF não foram violadas. No entanto, deve-se perceber que em uma linha da tabela Sales, o cliente (nome) foi substituído por sete células de linha do cliente.
Nota: uma célula de endereço contém o número da casa, o nome da rua ou estrada e o nome da cidade, todos separados por vírgulas. Uma cidade pode ser considerada como composta por várias vilas. Embora as vírgulas separem esses componentes de string específicos, elas formam um valor de célula e não três valores de célula.
A coluna do funcionário também deve ser substituída por sete dessas colunas. No entanto, isso não é feito neste tutorial para economizar tempo e espaço de ensino. Assim, uma tabela Sales com dados pode ser:
Tabela Vendas – 2NF – Sem customerID
A coluna de tipo de dados SaleID é um número inteiro ou, melhor, auto-incremento. O tipo de dados da coluna dateSold é uma data e não um número, pois possui o caractere “/”, que não é um dígito. O tipo de dados para o restante das colunas, incluindo a coluna de telefone, é string (ou texto). O valor do telefone possui o caractere “-”, que não é um dígito.
Observe que, para cada linha, o cliente (nome), como na parte anterior da série, foi substituído por sete células, uma das quais ainda é o nome do cliente. Isso significa que os dados do cliente são uma entidade. Atualmente, o nome do cliente identifica seus outros seis dados consecutivos. Se esta tabela for programada, será conveniente identificar a entidade cliente em cada linha com um número inteiro (não auto-incremento). Nesse caso, uma coluna customerID deve preceder o customerName. A tabela anterior fica:
Tabela Vendas – 2NF – Com customerID
Existem três customerIDs: 1, 2 e 3, com 1 ocorrendo cinco vezes para John Smith, 2 ocorrendo duas vezes para James Taylor e 3 ocorrendo uma vez para Susan Wright.
Observe que alguns customerIDs e seus dependentes se repetem.
Regras para Terceira Forma Normal
Uma tabela está na Terceira Forma Normal se obedecer às seguintes regras:
- Já deve estar na Segunda Forma Normal.
- E não deve ter Dependência Transitiva.
Aí um dos escriturários (funcionários) pergunta: “O que é uma dependência transitiva?”. E você, o desenvolvedor de banco de dados, responde: “Essa é uma boa pergunta!”
Dependência Transitiva
É verdade que em uma linha, SaleID identifica todos os valores na linha; no entanto, customerID identifica seus sete valores de dados, mas não identifica o restante dos valores identificados por SaleID nessa linha. Dito de outra forma, o SaleID depende de dez valores de célula em cada linha. No entanto, o customerID depende de sete valores de célula na mesma linha, mas o customerID não depende do SaleID e dos outros valores dos quais o SaleID depende.
Essa dependência para o customerID é uma dependência transitiva. E customerID é chamado de chave estrangeira e é sublinhado com traço nesta série de tutoriais, The Five Normal Forms.
Suponha que um atributo não principal (valor de célula não primário) dependa de outros atributos não principais e o atributo não principal em questão (por exemplo, customerID e seus dependentes) não dependa da chave primária e do restante da célula valores na linha. Então isso é dependência transitiva.
A tabela Sales anterior com a chave estrangeira e seus dependentes, causaria problemas contábeis (anomalias).
Tabela de vendas de 2NF a 3NF
Para resolver o problema da chave estrangeira e seus dependentes, remova a chave estrangeira e seus dependentes, para formar uma nova tabela sem repetições. No entanto, mesmo que a chave estrangeira não dependa da chave primária, a chave primária depende da chave estrangeira. Portanto, uma cópia da chave estrangeira deve permanecer na tabela pai. A nova tabela de vendas, neste ponto, é compatível com 1NF, 2NF e 3NF; é uma tabela pai. A nova tabela filha da tabela Sales anterior também é compatível com 1NF, 2NF e 3NF. O nome da tabela filha com chave estrangeira e seus dependentes é Clientes. Se um nome adequado não puder ser encontrado, algo deu errado com a análise. A nova Tabela de Vendas na 3NF é:
Tabela Final de Vendas na 3NF
Esta tabela na 3NF tem o mesmo número de linhas que a da 2NF mas com menos colunas.
A notação da tabela para esta tabela final de vendas na 3NF é:
Sales(saleID, dateSold, customerID, employeeID)
O saleID é a chave primária com um único sublinhado. customerID é uma chave estrangeira, com um traço sublinhado. employeeID também é uma chave estrangeira com um traço sublinhado. Observe que a situação do funcionário na tabela Vendas na 2NF é a mesma que a situação do cliente. O EmployeesID e seus próprios dependentes devem ser retirados para formar outra tabela; uma cópia do employeeID permanece.
Observação: saleID, customerID e employeeID não formam uma chave composta. saleID depende de customerID e employeeID.
A relação entre saleID e customerID é de muitos para um.
A tabela de clientes na 3NF
Esta tabela tem três linhas em vez de 9 linhas na tabela 2NF Sales. Nesta tabela, customerID é uma chave primária. É igual à chave estrangeira da tabela Vendas, mas sem repetições. A chave estrangeira na tabela Sales e a chave primária na tabela Customer vinculam ambas as tabelas.
As linhas repetidas na tabela Cliente foram removidas para não violar a 1NF.
Como o leitor pode ver, colocar uma tabela na 3NF também resolveria o problema de linhas repetidas (redundância).
A notação da Tabela de clientes é:
Clientes (customerID, customerName, telefone, endereço, cidade, região, código postal, país)
A Tabela de Produtos Revisitada
A tabela de produtos fornecida acima em forma de notação é:
Produtos (ID do produto, ID da categoria, ID do fornecedor, nome do produto, preço unitário, quantidade em estoque, nível do pedido)
A chave primária aqui é productID. ID da categoria e ID do fornecedor são chaves estrangeiras. Semelhante à tabela Customer, há uma tabela Categories, onde categoryID é a chave primária, e há uma tabela Supplier, onde vendorID é a chave primária.
Se os valores das células para unitPrice, amountInStock e reorderLevel permanecerem fixos, então a tabela Products, como está, está verdadeiramente na 3NF. Se esses valores forem alterados, a tabela Produtos, como está, está na 2NF. Nesta parte da série de tutoriais, supõe-se que esses valores permaneçam fixos ao longo do tempo.
todas as mesas
Todas as tabelas agora estão na 3NF. Eles são mostrados como:
Funcionários(employeeID, nome, telefone, endereço, cidade, região, código postal, país, birthDate, rentalDate, dateReleased)
Fornecedores(ID do fornecedor, nome, telefone, endereço, cidade, região, CEP, país)
Produtos (ID do produto, ID da categoria, ID do fornecedor, nome do produto, preço unitário, quantidade em estoque, nível do pedido)
Categorias(categoryID, categoryName, descrição)
Sales(saleID, dateSold, customerID, employeeID)
SaleDetails(saleID, productID, numberSold, sellingPrice)
Clientes (customerID, customerName, telefone, endereço, cidade, região, código postal, país)
Pedidos(pedidoID, dataVendido, fornecedorID, funcionárioID)
OrderDetails(orderID, productID, numberBought, costPrice)
Até nove tabelas profissionais foram produzidas a partir de uma única tabela produzida por novatos para evitar redundância e problemas de contabilidade (anomalias na inserção, exclusão e atualização). A mesa novata por si só levaria a perdas financeiras.
Testando a equipe
Neste ponto, todos os funcionários, incluindo o proprietário, devem ter entendido 1NF, 2NF e 3NF. No entanto, eles precisam ser testados. Todos eles, inclusive o proprietário, sentarão em lugares diferentes e farão a prova. A prova composta por uma questão, terá a duração de uma hora, e é a seguinte:
Questão: Usando regras para 1NF, 2NF e 3NF, prove que todas as nove tabelas acima já estão na Primeira Forma Normal, Segunda Forma Normal e Terceira Forma Normal. Os clientes e fornecedores não precisam ser entidades reais. Os dados das tabelas devem fazer backup das notações da tabela.
Enquanto eles estão concluindo o teste, você, como desenvolvedor de banco de dados, sai para lanchar e tomar uma cerveja, para retornar após uma hora.
O futuro próximo e distante
Enquanto você, o desenvolvedor do banco de dados, está fora, você também considera que conselho dar a eles se todos passarem no teste.
Além disso, enquanto você os treinava, e agora que eles estão fazendo o teste, os clientes vêm entrando e saindo sem serem atendidos. Isso não é bom para os negócios e você, desenvolvedor de banco de dados, sabe disso. Alguns clientes podem ir às lojas concorrentes e nunca mais voltar.
Você, o desenvolvedor do banco de dados, tem 30 anos. O proprietário, como seu amigo, também tem 30 anos. Os escrivães (funcionários) têm entre 18 e 24 anos. Todas as qualidades necessárias para trabalhar para o proprietário eram: ter saúde, saber ler e escrever, saber somar, subtrair, multiplicar e dividir , e ser capaz de usar o computador e a Internet.
Quando uma tabela está em 3NF, a maioria das vulnerabilidades foram removidas do banco de dados. Muitos bancos de dados comerciais não vão além do 3NF, e as firmas ou empresas estão confortáveis.
Então, se todos passarem no teste, você pedirá aos escriturários que continuem trabalhando. Você também os aconselhará a economizar parte de seus salários para que possam ser donos de suas lojas de conveniência. Você continuará amanhã treinando apenas o proprietário em 4NF e 5NF. Com o conhecimento da 4NF e 5NF, todas as vulnerabilidades conhecidas são removidas.
Avaliação
Depois de uma hora, você, o desenvolvedor do banco de dados, volta. Você marca seus scripts. Uma excelente notícia! Todos eles, incluindo o proprietário, têm 100% cada. Viva! Isto é excelente!
Então, parabéns a todos vocês: o professor e os alunos.
Não há mais nada a fazer neste tutorial além de concluir.
Conclusão
Uma tabela está na Primeira Forma Normal, se não violar nenhuma das seguintes regras:
- Todas as colunas em uma tabela devem ter nomes de cabeçalho exclusivos.
- Cada célula deve ter apenas um único valor.
- Os valores armazenados em uma coluna devem ser do mesmo tipo.
- As linhas devem ser distintas.
- A ordem das colunas ou linhas não importa.
Uma tabela está na Segunda Forma Normal, se não violar nenhuma das seguintes regras:
- A tabela já deve estar na Primeira Forma Normal.
- Não deve haver dependência parcial.
Uma tabela está na Terceira Forma Normal, se não violar nenhuma das seguintes regras:
- Já deve estar na Segunda Forma Normal.
- E não deve ter Dependência Transitiva.
Você, o desenvolvedor do banco de dados, diga aos funcionários que eles aprenderam o suficiente. Você fornece conselhos e pede que eles voltem ao trabalho e permaneçam em suas estações por padrão.
Você marca um encontro apenas com o proprietário, para acontecer em seu escritório amanhã para treinamento em 4NF e 5NF.
