-- ============================================================================================================================================================ -- Guia de normalização de tabelas usando SQL como instrumento de ação. -- ============================================================================================================================================================ -- Resolvendo um problema de normalização. -- A Normalização foi desenvolvida para resolver os problemas decorrentes de falhas de modelagem que são 'permitidas' no Modelo Relacional. -- Estas 'Anomalias' foram percebidas algum tempo depois que se iniciou o uso massivo deste modelo de banco de dados. -- O estudo destas 'Anomalias' resultou em um 'procedimento' que elimina estes problemas tornando a tabela mais adequada aos princípios da Teoria dos Conjuntos -- que fundamenta o modelo relacional. Este 'procedimento' foi denominado de Normalização de Tabelas. -- Com este 'procedimento' é possível classificar as tabelas em 1ª Forma Normal, 2ª Forma Normal e 3ª Forma Normal. -- Na Forma NÃO Normal a tabela pode conter as anomalias sobrepostas. As anomalias são: multivaloração, dependência funcional parcial -- (em tabelas com chaves primárias compostas) e dependência funcional transitiva ou campo derivado de cálculo. -- Então, este procedimento leva a tabela excluindo as anomalias em fases ou passos. -- Da Forma Não Normal para 1ª Forma Normal ==> Eliminando a multivaloração -- Da 1ª Forma Normal para 2ª Forma Normal ==> Eliminando a Dependência Funcional Parcial -- Da 2ª Forma Normal para 3ª Forma Normal ==> Eliminando a Dependência Funcional Transitiva e campos de cálculo. -- -- Neste Guia de Normalização foi usada uma nomenclatura visando informar a forma normal que tabela está nas etapas do procedimento. -- Deste modo, usamos as terminações de nomes 'fnn', '1fn', '2fn' e '3fn' para indicar as formas normais que a tabela foi alcançando. -- -- Neste Guia de Normalização o objetivo é indicar os comandos SQL que executam a 'transformação' de tabelas para mudanças do 'Status' (de Normalização). -- Em cada situação CRIA-SE uma tabela (designada como 'origem') na situação que deve ser resolvida. A seguir se escrevem os comandos SQL que eliminam da -- tabela 'origem' a 'anomalia' apresentada. -- Os exemplos de comandos SQL foram desenvolvidos para o SGBD PostgreSQL e em alguns casos sitou-se os comandos na mesma etapa para o SGBD MariaDB. -- É essencial que o estudante tenha um equipamento com estes SGBDs instalados para executar os comandos propostos. -- ============================================================================================================================================================ -- Transformação de tabelas da forma NÃO NORMAL para a 1ª Forma Normal (Eliminando da Multivaloração). -- -- Em uma base de dados crie uma tabela executando o comando a seguir: CREATE TABLE IF NOT EXISTS public.clientesfnn ( cdcli smallint NOT NULL, txnomecliente character varying(60) COLLATE pg_catalog."default", nutelcliente character(13) COLLATE pg_catalog."default", dtcadcliente date, CONSTRAINT pkcliente PRIMARY KEY (cdcli)) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.clientesfnn OWNER to postgres; -- Para este exercício, o campo nutelcliente apresentará múltiplos valores. Cada valor será separado do outro pelo caractere '/'. -- Para isso executa-se o comando: INSERT INTO clientesfnn VALUES (1,'Osvaldo', '478/852/846/856','2023-05-19'), (2,'Ortavio', '741/490/104', '2023-05-19'), (3,'Oduvaldo', '146/592/490/212','2023-05-19'), (4,'Olavo', '951/703/526/325','2023-05-19'), (5,'Oscar', '952/713/416', '2023-05-19'), (6,'Orlando', '953/723/126/532','2023-05-19'), (7,'Osmar', '954/733/226/645','2023-05-19'), (8,'Olimpio', '955/743/326/728','2023-05-19'), (9,'Onofre', '956/743/426/828','2023-05-19'), (10,'Orfeu', '957/753/526/948','2023-05-19'), (11,'Osni', '958/763/626/105','2023-05-19'), (12,'Othon', '959/773/726/168','2023-05-19'), (13,'Otacílio','960/783/826/154','2023-05-19'), (14,'Orestes', '961/793/926/179','2023-05-19'); -- -- Note: existem clientes que possuem 2, 3 ou 4 telefones apresentados no campo nutelcliente. -- este é o campo multivalorado em relação ao campo cdcli (que é a chave primária da tabela). -- -- Os SGBDs mais diversos oferecem funções de processamento dos valores de campo. Estes comando podem viabilizar -- o tratamento de dados nas tabelas pela execução de scripts. -- -- Consulte o catálogo de funções de processamento de dados do PostgreSQL em: https://www.postgresqltutorial.com ou https://www.postgresql.org -- -- Pesquisando as funções do PostgreSQL (e testando cada uma delas), encontra-se uma adequada para o processamento de dados de clientes (com os dados depois -- da execução do comando INSERT acima indicado). É a função: regexp_split_to_table(), que pode ser usada na forma de uma projeção -- SELECT cdcli, regexp_split_to_table(nutelcliente, '\/+') AS nutel, CURRENT_DATE FROM clientesfnn; -- -- Então, dada uma tabela A(C1, C2,...,Cn) sendo C1 sua chave primária e tendo um campo Ci com multivaloração; para se eliminar da tabela multivaloração, -- o procedimento é: -- 1) CRIAR uma outra tabela (com nome A1, por exemplo) com a chave primária e o campo multivalorado (executando o comando SQL estudado) -- 2) Projetando de A todos os campos EXCETO o campo multivalorado. -- -- Note: Em A a CP continua sendo C1. Em A1 a CP PODE SER (C1, Ci), sendo que Ci já apresenta os valores 'desmembrados'. -- Em A1 C1 passa a ser uma Chave Estrangeira. Portanto entre A e A1 existe um relacionamento. -- -- Para criar a tabela 'desmembrada' de A, pode-se escrever um comando semelhante à: create table clientestels as (SELECT cdcli, regexp_split_to_table(nutelcliente, '\/+') AS nutel, CURRENT_DATE FROM clientesfnn); -- -- Note: Esta nova tabela 'carrega' o campo cdcli como uma chave estrangeira. -- A criação da tabela clientestels ainda não removeu da tabela clientesfnn a multivaloração. Como o campo nutelcliente SUGERE que dado é um número de telefones -- o nome da tabela criada foi 'clientestels' e naturalmente o campo denominou-se 'nutel'. -- -- Para realizar a operação de REDEFINIÇÃO da estrutura da tabela clientes, realiza-se a operação de PROJEÇÃO, retirando de clientes o campo multivalorado. create table clientes1fn as (select cdcli, nome, dtcadcliente from clientes); -- Note: O nome da tabela sugere o 'status' da tabela estando na 1ª Formma Normal, seguindo a nomenclatura proposta neste Guia. -- ============================================================================================================================================================ -- A sequência acima executa a transformação de uma tabela da forma não normal para a 1ª forma normal (havendo multivaloração). -- Se houver na tabela original, mais de 1 campo multivalorado o procedimento não elimina a multivaloração na tabela desmembrada. -- Os comandos e as situações apresentadas para o SGBD PostgreSQL podem ser reproduzidas para o SGBD MariaDb. -- A seguir faremos a apresentação da mesma situação (e dados) para o SGBD MariaDB. -- -- Criando a tabela clientes: CREATE TABLE IF NOT EXISTS clientesfnn ( cdcli smallint NOT NULL, txnomecliente character varying(60), nutelcliente CHARACTER(30), dtcadcliente date, CONSTRAINT pkcliente PRIMARY KEY (cdcli)); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Acrescentando linhas em 'clientes' (já com a ocorrencia de multiplos valores no campi nutelcliente). insert into clientesfnn VALUES (1,'Osvaldo', '478/852/846/856','2023-05-19'), (2,'Ortavio', '741/490/104', '2023-05-19'), (3,'Oduvaldo', '146/592/490/212','2023-05-19'), (4,'Olavo', '951/703/526/325','2023-05-19'), (5,'Oscar', '952/713/416', '2023-05-19'), (6,'Orlando', '953/723/126/532','2023-05-19'), (7,'Osmar', '954/733/226/645','2023-05-19'), (8,'Olimpio', '955/743/326/728','2023-05-19'), (9,'Onofre', '956/743/426/828','2023-05-19'), (10,'Orfeu', '957/753/526/948','2023-05-19'), (11,'Osni', '958/763/626/105','2023-05-19'), (12,'Othon', '959/773/726/168','2023-05-19'), (13,'Otacílio','960/783/826/154','2023-05-19'), (14,'Orestes', '961/793/926/179','2023-05-19'); -- Esta tabela foi exportada para um Script .SQL e está disponível para download no acesso: -- ( https://www.fatecourinhos.edu.br/disciplinas/ibd012/anotacoes/2023.1-Normalizacao-clientesfnn.sql ) -- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -- O estudo dos comandos SQL para análise de multivaloração em campos da tabela clientes pode ser iniciado com o comando: SELECT cdcli, nutelcliente, substring_index(substring_index(nutelcliente, '/', 1), '/', -1) AS nutel1, substring_index(substring_index(nutelcliente, '/', 2), '/', -1) AS nutel2, substring_index(substring_index(nutelcliente, '/', 3), '/', -1) AS nutel3, substring_index(substring_index(nutelcliente, '/', 4), '/', -1) AS nutel4 FROM clientesfnn; -- Este comando exibe o campo nutelcliente e os números escritos em campos separados considerando o caractere '/' como separador. -- Note: em campos de nutelcliente que tenha dois separadores o valor do último número de telefone aparece repetido. -- Esse 'efeito' acontece por conta que substring_index(substring_index(nutelcliente, '/', 3), '/', -1) e / -- substring_index(substring_index(nutelcliente, '/', 3), '/', -1) tem o mesmo retorno para a entrada '741/490/104'. -- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Derivado do comando anterior, cada um dos campos pode formar uma tabela com o campo cdcli e a partir das tabelas formadas se faz a operação de UNIÃO, -- na forma: SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 1), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 2), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 3), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 4), '/', -1) AS nutel1 FROM clientesfnn))); -- Nesse comando, o efeito de repetição de valores é omitido. -- -- Partindo da execução deste comando, o restante do procedimento segue como especificado para o SGBD PostgreSQL, com a eliminação do campo multivalorado da -- tabela clientes e com o estabelecimento da ligação entre as tabelas com a comparação entre as chaves primária da tabela clientes com a correspondente -- chave estrangeira da tabela 'desmembrada'. Portanto para detalhar em comando: -- Criando a tabela 'desmembrada': CREATE TABLE clientestels as (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 1), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 2), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 3), '/', -1) AS nutel1 FROM clientesfnn UNION (SELECT cdcli, substring_index(substring_index(nutelcliente, '/', 4), '/', -1) AS nutel1 FROM clientesfnn)))); CREATE TABLE clientes1fn as (select cdcli, nome,dtcadcliente from clientesfnn); -- para ajustar as chaves primárias das tabelas: ALTER TABLE clientes1fn ADD PRIMARY KEY (cdcli); ALTER TABLE clientestels ADD PRIMARY KEY (cdcli,nutel1); ALTER TABLE clientestels ADD INDEX iclientestelsnutel1 (nutel1) USING BTREE; ALTER TABLE clientestels ADD CONSTRAINT clientestelsFK1 FOREIGN KEY (cdcli) REFERENCES clientes (cdcli) ON UPDATE CASCADE ON DELETE CASCADE; -- -- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Existem alguns comandos que ainda podem ser estudados e desenvolvidos. -- Minha sugestão é que sejam executados, seus resultados analisados e então o comando seja estudado e eventualmente alterado (SE for percebido que o estudo -- indique uma evolução no sentido de resolver o problema apresentado. -- -- São os comandos: WITH RECURSIVE data AS (SELECT cdcli, concat(nutelcliente, '/') nutelcliente FROM clientesfnn), cte AS (SELECT cdcli, substring(nutelcliente, 1, LOCATE('/', nutelcliente) - 1) word, substring(nutelcliente, LOCATE('/', nutelcliente) + 2) nutelcliente FROM data UNION ALL SELECT cdcli, substring(nutelcliente, 1, locate('/', nutelcliente) - 1) word, substring(nutelcliente, locate('/', nutelcliente) + 2) nutelcliente FROM cte WHERE locate(', ', nutelcliente) > 0 ) SELECT cdcli, word, nutelcliente FROM cte; -- SELECT nutelcliente, LOCATE('/',nutelcliente,1) AS pos1, SUBSTRING(nutelcliente,1,LOCATE('/',nutelcliente,1)-1) AS nutel1 FROM clientesfnn; -- SELECT nutelcliente, LOCATE('/',nutelcliente,(LOCATE('/',nutelcliente,1)+1)) AS pos2, SUBSTRING(nutelcliente, (LOCATE('/',nutelcliente,1)+1), (LOCATE('/',nutelcliente, (LOCATE('/',nutelcliente,1)+1)-1)-1) ) AS nutel2 FROM clientesfnn; -- SELECT nutelcliente, LOCATE('/',nutelcliente,1) AS pos1, SUBSTRING(nutelcliente,1,LOCATE('/',nutelcliente,1)-1) AS nutel1 FROM clientesfnn; -- SELECT cdcli, nutelcliente, LOCATE('/',nutelcliente,1) AS pos1, SUBSTRING(nutelcliente,1,LOCATE('/',nutelcliente,1)-1) AS nutel1, LOCATE('/',nutelcliente,(LOCATE('/',nutelcliente,1)+1)) AS pos2, SUBSTRING(nutelcliente, (LOCATE('/',nutelcliente,1)+1), (LOCATE('/',nutelcliente, (LOCATE('/',nutelcliente,1)+1)-1)-1) ) AS nutel2, LOCATE('/',nutelcliente,(LOCATE('/',nutelcliente,LOCATE('/',nutelcliente,1))+1)) AS pos3, SUBSTRING(nutelcliente, (LOCATE('/',nutelcliente,LOCATE('/',nutelcliente,1))+1), (LOCATE('/',nutelcliente, (LOCATE('/',nutelcliente,LOCATE('/',nutelcliente,1))+1)-1)-1) ) AS nutel3 FROM clientesfnn; -- SELECT nutelcliente, LOCATE('/',nutelcliente,1), LOCATE('/',nutelcliente,5), LOCATE('/',nutelcliente,9), LOCATE('/',nutelcliente,13) FROM clientesfnn; -- SELECT SUBSTRING_INDEX('www.mari.adb.org', '.', 3); -- ============================================================================================================================================================ -- Transformação da tabela da 1ª Forma Normal para 2ª Forma Normal (Eliminando da Dependência Parcial) -- Vamos trabalhar com a seguinte tabela: CREATE TABLE IF NOT EXISTS public.vendas1fn ( fkcliente integer, txnomecliente character varying(250) COLLATE pg_catalog."default", fkfuncionario integer, txnomecompfnc text COLLATE pg_catalog."default", fkarmazem smallint, txnomearmazem character varying(50) COLLATE pg_catalog."default", vltotalnfvenda double precision, dtvenda date, dtemissao date, dtcadnfvenda date, fknunfvenda integer, fkproduto character(12) COLLATE pg_catalog."default", txnomeprod character varying(128) COLLATE pg_catalog."default", vlunit numeric(12,2), qtvendida integer, vlunititem double precision, vltotalitem numeric) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.vendas1fn OWNER to postgres; -- -- Esta tabela foi exportada para um Script .SQL (com a estrutura e os dados da tabela) e está disponível para download no acesso abaixo: -- ( https://www.fatecourinhos.edu.br/disciplinas/ibd012/anotacoes/2023.1-Normalizacao-vendas1fn.sql ) -- -- Os comandos que se desenvolvem nesta etapa deste Guia trabalham sobre os dados desta tabela. Como na tabela origem temos 498 linhas. -- Esta tabela está adequada ao modelo relacional: -- - não tem linhas repetidas - execute SELECT DISTINCT * FROM vendas1fn e o sistema conta 498 linhas -- - não tem campos com nomes repetidos -- -- Nesta etapa do Guia estamos partindo do princípio que a tabela está na 1FN e pode apresentar Dep. Parcial. Sendo assim a tabela deve ter uma CP Composta. -- Seria já um exercício interessante determinar a CP desta tabela... -- Bem, uma candidata à CP pode ser detectada SE o comando 'select distinct campo from tabela;'. Teste na base onde você criar esta tabela o seguinte comando: select distinct fkcliente from vendas1fn; -- Este comando retorna 8 linhas. PORTANTO o campo fkcliente NÃO é candidato a ser uma CP. select distinct concat(fkcliente,fkproduto) from vendas1fn; -- Retorna 33 linhas. E neste comando testamos se a concatenação de valores de dois campos podem ser candidatos a ser uma CP. -- Bem, você pode testar outras várias combinações. Mas já apontamos a CP como sendo (fknunfvenda,fkproduto) select distinct concat(fknunfvenda,fkproduto) from vendas1fn; -- Este comando retorna 498 linhas (sem repetir os valores de [fknunfvenda,fkproduto] combinados). -- -- Bem, temos que indicar se a tabela possui a 'anomalia' de dependência parcial. Para isso tomamos cada parte da CP como determinante de Dep Fincuonal, -- na forma: select distinct fknunfvenda, dtcadnfvenda from vendas1fn order by fknunfvenda; select distinct fknunfvenda from vendas1fn order by fknunfvenda; -- Estes DOIS comandos retornam 146 linhas. -- SE os dois comandos retornarem a mesma quantidade de linhas significa que para todos os valores de fknunfvenda teremos associados -- (sempre) os mesmos valores de dtcadnfvenda. -- Para titulo de estudo... execute select distinct fknunfvenda, qtvendida from vendas1fn order by fknunfvenda; -- E veja que este comando retorna 148 linhas!! Significa que devem existir valores de fknunfvenda que 'apontam' para DIFERENTES valores do campo qtvendida. -- -- Seguindo a analise de dependência funcional parcial, deve-se 'testar' a dependência parcial considerando como determinante cada parte da CP e identificando -- todos os seus dependentes. -- -- fknunfvenda -> fkcliente,txnomecliente,fkfuncionario,txnomecompfnc,fkarmazem,txnomearmazem,vltotalnfvenda,dtvenda,dtemissao,dtcadnfvenda -- fkproduto -> txnomeprod,vlunit -- os campos: qtvendida,vlunititem,vltotalitem são dependentes de toda a chave primária (esta é a situação desejada: Dependência TOTAL). -- Outra forma de escrever: fknunfvenda,fkproduto -> qtvendida,vlunititem,vltotalitem -- -- o Procedimento de eliminação da dependência parcial indica: -- - devem ser criadas tabelas, uma para cada parte determinante da dependência parcial. -- Para estas tabelas copia-se o campo determinante e todos os seus dependentes. -- - Da tabela 'origem' deve-se REMOVER os campos dependentes. -- -- Sendo assim: create table desmem1fnn as (select fknunfvenda,fkcliente,txnomecliente,fkfuncionario,txnomecompfnc,fkarmazem,txnomearmazem,vltotalnfvenda,dtvenda,dtemissao,dtcadnfvenda from vendas1fn); create table desmem2fnn as (select fkproduto,txnomeprod,vlunit from vendas1fn); create table vendas2fn as (select fknunfvenda,fkproduto,qtvendida,vlunititem,vltotalitem from vendas1fn); -- A tabela vendas2fn não tem mais a dependência parcial, entretanto ainda temos que avaliar a ocorrência da dependência transativa - quando ocorre a -- relação de dependência entre os campos 'fora da chave primária'. Os campos podem também apresentar uma relação de cálculo matemático, nesta situação -- diz-se que existe uma Dependência Funcional de cálculo. -- Na tabela vendas2fn ainda apresenta campos que podem ser definidos como campos de cálculo: -- qtvendida * vlunititem = vltotalitem -- Em muitos gerenciadores de bancos de dados existe um recurso que permite exibir os dados combinados. É o conceito da visão de dados. -- Podemos colocar a definição do valor vltotalitem na estrutura de definição da visão e retirar o campo da tabela vendas2fn -- Então definiremos: CREATE OR REPLACE VIEW public.vendas3fn AS SELECT vendas2fn.fknunfvenda, vendas2fn.fkproduto, vendas2fn.qtvendida, vendas2fn.vlunititem, vendas2fn.qtvendida::double precision * vendas2fn.vlunititem AS vltotalitem FROM vendas2fn; ALTER TABLE public.vendas3fn OWNER TO postgres; -- -- A visão vendas3fn segue a nomenclatura proposta neste Guia. -- Lembre-se que as tabelas desmembradas no procedimento DEVEM tratadas como tabelas na forma "Não Normal". -- Sendo assim, as tabelas desmem1 e desmem2 foram denominadas desmem1fnn e desmem2fnn, respectivamente. -- ------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Embora este Guia vise o estudo do procedimento de Normalização, o desenvolvimento de SQL foi percebido. SQL é uma linguagem. -- Para se ter a proficiência deve-se praticá-la. -- -- Deixamos a seguir comandos SQL que foram pesquisados e estudados durante a elaboração deste Guia. -- Analise-os, estude suas partes, procure entender o que faz cada segmento de comando. Comente e converse à respeito com seus pares. create table vendas1fn as ( select nfv.pknunfvenda, nfv.fkcliente, clt.txnomecliente, nfv.fkfuncionario, concat(fnc.txprenomes,' ',fnc.txsobrenome) as txnomecompfnc, nfv.fkarmazem, arz.txnomearmazem, nfv.vltotalnfvenda, nfv.dtvenda, nfv.dtemissao, nfv.dtcadnfvenda, nfvit.fknunfvenda, nfvit.fkproduto, nfvit.vlunitario as vlunititem, prd.txnome as txnomeprod, prd.vlpreco, nfvit.qtvendida, nfvit.vlunitario, (nfvit.qtvendida*nfvit.vlunitario) as vltotalitem from nfvendas as nfv inner join nfvendasitens nfvit on nfv.pknunfvenda = nfvit.fknunfvenda inner join produtos as prd on nfvit.fkproduto = prd.pkproduto inner join clientes as clt on nfv.fkcliente = clt.pkcliente inner join funcionarios fnc on nfv.fkfuncionario = fnc.pkfuncionario inner join armazens as arz on nfv.fkarmazem = arz.pkarmazem ); -- update nfvendas set vltotalnfvenda=(select round(cast((sum(nfit.qtvendida*nfit.vlunitario)) as numeric),2) as vtotitem from nfvendasitens where nfvendas.pknunfvenda=nfvendasitens.fknunfvenda group by nfvendasitens.fknunfvenda order by nfvendasitens.fknunfvenda); select * from nfvendas;