Banco de Dados I – BD I Prof. Lineu Mialaret Aula 18: Teoria da Normalização

1. Instituto Federal de Educação, Ciência e Tecnologia de São Paulo - IFSP Campus de Caraguatatuba Tecnólogo em Análise e Desenvolvimento de Sistemas 10 Semestre de 2013 Banco de Dados I – BD I Prof. Lineu Mialaret Aula 18: Teoria da Normalização

2. Desenvolvimento de Aplicativos de BD nome compra Modelo Conceitual Pessoa Produto preco nome rg Modelo Relacional Normalização

3. Introdução (1) • Normalização é uma técnica para produzir relações (tabelas) com propriedades desejáveis. É formalmente fundamentada em conceitos matemáticos. • Por meio do processo de normalização, pode-se substituir ou decompor, gradativamente, um conjunto de tabelas por um outro, mais adequado, que contenha uma menor redundância de dados. • Esta decomposição requer que nenhuma informação seja perdida e a reconstrução das tabelas originais seja possível. • O conceito de normalização foi introduzido por E. F. Codd em 1970, e desde então vem sendo muito estudado na pesquisa científica em Tecnologia de de Banco de Dados. • A normalização pode ser: • Utilizada depois da modelagem conceitual ou de forma independente. • Especialmente útil para base de dados que já tenham sido projetadas e implementadas sem o uso de técnicas formais.

4. Introdução (2) • O propósito da normalização é desenvolver esquemas relacionais que minimizem redundâncias e anomalias de atualizações. • Redundância ocorre quando o mesmo valor de dado ou informação é armazenado mais de uma vez na tabela. Redundância ocupa espaço e reduz a performance do SGBD. • Anomaly: is an undesirable consequence of a data modification. • Anomalias de Atualização surgem quando se tenta inserir, remover ou atualizar linhas numa tabela. Essa anomalias numa base de dados surgem devido a ocorrência de, por exemplo: • Grupos repetitivos de dados. • Dependências parciais de chave. • Inexatidão de representações de fatos da realidades (modelos). • Dependências transitivas entre atributos.

5. Introdução (3) • Todas essas dificuldades podem ser reduzidas ou minimizadas por meio do uso da técnica de normalização. • Esta técnica torna o Modelo Relacional que se está utilizando, bastante estável, isto é, sujeito a poucas manutenções. • Resumindo: • O objetivo da normalização é produzir um conjunto de esquemas relacionais ajustados R1,R2,...,Rm de um conjunto de atributos A1,A2,A3,...,An. • Supondo que todos os atributos estejam originalmente em uma grande relação R = {A1,A2,...,An}, a qual pode ser chamada de relação universal, a normalização vai dividir essa relação em várias relações otimizadas R1,R2,...,Rm.

6. Anomalias de Atualização (1) • Há três principais tipos de anomalias de atualização: • Anomalia de inserção, onde a inserção de uma linha numa tabela requer a inserção de informação redundante ou não pode ser realizada por atribuir valores nulos para atributos chaves. • Anomalia de remoção, em que a remoção de uma linha da tabela pode causar a perda de informação que ainda precisa ser armazenada. • Anomalia de modificação, quando a mudança de um atributo de uma linha da tabela pode exigir múltiplas mudanças desse valor em várias outras linhas da mesma.

7. Anomalias de Atualização (2) • Seja a seguinte tabela COMPANHIA que representa informações sobre uma determinada companhia. COMPANHIA • Se a companhia possui três donos, há três linhas na tabela COMPANHIA para esta empresa. COMPANHIA

8. Anomalias de Atualização (3) • Se a companhia muda para um novo endereço, o mesmo (novo endereço) deve ser atualizado consistentemente em todas as três linhas da tabela COMPANHIA: • A atualização em apenas uma ou duas linhas da tabela deixará o banco de dados num estado inconsistente (ou seja, gera uma anomalia). • Seria melhor ou mais adequado se o nome e o endereço da companhia estivessem numa tabela separada de modo que o endereço de cada companhia aparecesse em uma só linha dessa tabela. COMPANHIA

9. Anomalias de Atualização (4) • Supondo que duas pessoas criem uma nova empresa e que: • Os dois fundadores ainda não possuem cargos. • A distribuição do número de ações também não foi definida. • A nova empresa pode não ser inserida na tabela COMPANHIA pois não não há bastante informação para preencher todos aos atributos de uma linha da tabela, ou no máximo, valores nulos deverão ser usados para preencher uma linha. • Seria mais adequado que as informações sobre cargos e quantidade de ações fossem armazenadas numa tabela diferente. COMPANHIA

10. Anomalias de Atualização (5) • Supondo que um dos donos da companhia se retire do negócio mas ainda continue de posse de ações da mesma. • Se a linha da tabela COMPANHIA referente a esse dono é removida, perde-se a informação de quantas ações (#Acões) ele possui. • Se essa informação (a quantidade de ações possuída) estivesse armazenada numa tabela diferente, poderia-se manter ela armazenada, mesmo depois que a pessoa deixasse de ser dona da companhia. COMPANHIA

11. Exemplo de Anomalia • Seja a seguinte tabela exemplo EMPDEPT: • Seja a seguinte atualização nesta tabela: • Inserir um departamento D4 que não possui empregados ainda. • O que ocorre? EMPDEPT

12. Propriedades Desejáveis de BD´s • A técnica de normalização refere-se ao processo de converter um Modelo Relacional arbitrário em outro com melhores propriedades operacionais. • Projetar um Banco de Dados Relacional não é apenas uma questão de especificar um conjunto de tabelas, que contém todos os atributos requeridos. • Tabelas que são bem projetadas possuem várias importantes características: • A mais importante propriedade básica que a tabela possui é que seus atributos são relacionados logicamente, ou seja os atributos nativos de uma tabela devem se referir a uma mesma entidade ou relacionamento. • A propriedade de lossless-join garante que a informação decomposta em muitas tabelas pode ser reconstruida usando-se junções naturais. • A propriedade de preservação de dependências garante que as restrições (requsitos) na tabela original podem ser reforçadas nas relações normalizadas. • Dessa forma, a normalização tem por objetivo produzir um Modelo Relacional que garanta a integridade dos dados, uma redundância mínima e sua evolução com o mínimo de efeito colateral.

13. Formas Normais (1) • Uma tabela está numa particular Forma Normal – FN, se ela satisfaz certas propriedades de normalização, ou seja, se ela atende os requisitos de uma determinada forma normal. • Há várias formas normais definidas: Níveis de Formas Normais • 1NF – First Normal Form (Primeira Forma Normal) • 2NF – Second Normal Form (Segunda Forma Normal) • 3NF – Third Normal Form (Terceira Forma Normal) • BCNF – Boyce-Codd Normal Form (Forma Normal de Boyce-Codd) • 4NF – Fourth Normal Form (Quarta Forma Normal) • 5NF – Fifth Normal Form (Quinta Forma Normal) • DK/NF – Domain/Key Normal Form (Forma Normal de Domínio/Chave) • A teoria da normalização é tradicionalmente expressa por meio de um conjunto de formas normais, as quais progressivamente otimizam as estruturas esquemáticas das tabelas de um Banco de Dados.

14. Formas Normais (2) DK/NF • As formas normais são aninhadas (nesteds), ou seja, se uma determinada tabela R está na Terceira Forma Normal – 3FN, automaticamente ela está em 1FN e 2FN.

15. 1FN – 1a Forma Normal (1) • Domínio de um atributo: • O conjunto de possíveis valores permitidos a esse atributo. • Exemplos de domínios: X = { x   | x  -5 e x  5 } Y = { y   | y  0 } • Definição: Uma tabela está na Primeira Forma Normal - 1FN se, e somente se, todos os seus atributos são atômicos. Grupos repetidos de valores devem ser eliminados. • Uma tabela se encontra na 1FN quando todos os atributos são simples (não sendo admitidos itens estruturados ou itens repetitivos), ou seja, o valor de uma coluna da tabela é indivisível (ou único). • Quando uma tabela não está em 1FN diz-se que ela está em 0FN.

16. 1FN – 1a Forma Normal (2) • A 1FN é o primeiro passo do processo de normalização. Ela elimina os atributos multivalorados e compostos, permitindo apenas a ocorrência de atributos atômicos. •  Exemplo de uma tabela EMPREGADO na 0FN: EMPREGADO • Uma linha deve armazenar informações sobre os vários diferentes projetos nos quais um determinado empregado já trabalhou: • Caso se coloque estas informações numa tabela relacional, já se está normalizando para a 1FN. • Uma tabela no Modelo Relacional implicitamente já está em 1FN.

17. 1FN – 1a Forma Normal (3) • Há dois modos de converter uma tabela 0FN numa tabela que se encontre em 1FN. • Método da Divisão (Division Method) - dividir a tabela existente em duas tabelas, uma com os atributos não repetidos e a outra com os atributos repetidos: • Criar uma nova tabela contendo a chave primária original mais os atributos monovalorados. • Criar uma outra tabela tendo como chave primária a chave primária da tabela original concatenada com algum atributo multivalorado, e tendo como colunas os outros atributos multivalorados. • Método da Planificação (Flatenning Method) - criar novas linhas para os dados repetidos combinados com os dados que não são repetidos, e gerar uma nova chave primária (chave primária antiga concatenada com algum atributo multivalorado): • Isto introduz redundância que será removida posteriormente pela normalização.

18. 1FN – Método da Divisão (1) EMPREGADO Tabela EMPREGADO na 0FN.

19. 1FN – Método da Divisão (2) EMPREGADO

20. 1FN – Método da Divisão (3)

21. 1FN – Método da Planificação (1)

22. 1FN – Método da Planificação (2) EMPREGADO EMPREGADO

23. 1FN – 1a Forma Normal (4) • Um dos objetivos da normalização é reduzir a redundância de dados, porém com a tabela EMPREGADO apresentada anteriormente ainda há a ocorrência dessa redundância. • É necessário realizar outros passos de normalização para se ter um bom projeto (eliminando-se desse modo as redundâncias). • A 1FN ainda possui características indesejáveis. • Uma tabela na 1FN pode apresentar anomalias de: • Inclusão, Atualização e Remoção. • É necessário refinar a normalização, e para isso usa-se o conceito de Dependência Funcional - DF.

24. 1FN – 1a Forma Normal (5) EMPREGADO • Anomalia de Inserção: não se pode inserir um empregado sem que este esteja alocado a um projeto, nem inserir um projeto sem que haja um empregado trabalhando nele.

25. 1FN – 1a Forma Normal (6) EMPREGADO • Anomalia de Remoção: se for necessário remover um projeto, as informações dos empregados que estiverem trabalhando apenas naquele projeto serão perdidas.

26. 1FN – 1a Forma Normal (7) EMPREGADO • Anomalia de Atualização: se um empregado for promovido de cargo, deve-se atualizar os atributos CodCargo e NomeCargo em todas as linhas nas quais este empregado está presente.

27. Dependência Funcional (1) • O Modelo Relacional fundamentou, baseado na teoria de funções da matemática, o conceito de Dependência Funcional - DF. • Será utilizada a teoria de funções para explicar o conceito de dependência funcional do Modelo Relacional. • Considere os seguintes conjuntos X e Y: Y 11 12 13 14 X 1 2 3 4

28. Y 13 12 11 0 0 1 2 3 X Dependência Funcional (2) • Observa-se que há uma dependência entre os valores dos conjuntos, que pode ser expressa pela função f(x) = x + 10, ou seja, y é função de x, ou y = f(x) = x + 10. • Esta dependência pode também ser expressa através do gráfico apresentado abaixo:

29. Dependência Funcional (3) • Agora, sejam os conjuntos apresentados abaixo: CPF 1 2 3 4 Nome José João Rui Manoel • Observa-se que há uma dependência entre os valores dos conjuntos, que pode ser expressa pela função f(CPF) = nome. • O atributo nome é função do atributo CFP, ou seja, se houver um número de CPF, pode-se encontrar o nome da pessoa correspondente.

30. Dependência Funcional (4) • Esta dependência é expressa no Modelo Relacional da seguinte maneira: CPF  NOME • Lê-se a notação acima do seguinte modo: • Com um número de CPF pode-se encontrar o nome da pessoa, ou ainda • O nome da pessoa depende funcionalmente do CPF. • Há uma série de regras formais para se manipular e raciocinar sobre dependências funcionais. • O Axioma de Armstrong estabelece várias regras de inferência para dependências funcionais.

31. Dependência Funcional (5) • Definição: Dada uma tabela R e os atributos X e Y, um atributo Y é funcionalmente dependente do atributo X se, e somente se, para cada valor de X está associado apenas um valor de Y. • Em outras palavras, o atributo X determina univocamente Y. • Simbologia: XY, onde lê - se: X funcionalmente determina Y Y é funcionalmente dependente de X Y é função de X. • Para cada valor de X só existe um valor de Y.

32. Dependência Funcional (6) • Exemplo: • O atributo eno determina funcionalmente o atributo ename. • Ou seja, pode-se dizer que enoename.

33. Dependência Funcional (7) • Exemplo: na tabela EMPREGADO acima há três linhas com valor 121 para matrícula, com o mesmo valor no atributo Nome (Hélio). Há um relacionamento semelhante entre Matricula e Nome nas demais linhas. • O atributo Nome é funcionalmente dependente de Matricula. Os atributos CodCargo e NomeCargo também são funcionalmente dependentes do atributo Matricula.

34. Dependência Funcional (8) • Uma dependência funcional tem o lado esquerdo denominado de determinante, podendo ser um conjunto de atributos e um atributo do lado direito (que também pode ser um conjunto de atributos). • Exemplo: eno,pnohours

35. Dependência Funcional (9) • Geralmente há um só atributo do lado direito, mas pode-se combinar várias dependências funcionais em uma só. • Exemplo: eno,pnohours eno,pnoresp eno,pnohours,resp

36. Dependência Funcional (10) • Restrições (constraints) são regras que se aplicam a base de dados e limitam os valores de dados que podem ser armazenados. • Como toda restrição de integridade, dependências funcionais - DF´s são baseadas na semântica da aplicação: • Pode-se checar uma instância de uma tabela e ver se uma DF é violada ou não. Mas apenas com o exame de uma instância de uma tabela nunca se pode concluir se uma DF deve ser imposta ou não. • Uma dependência funcional - DF diz respeito a todas as possíveis instâncias de uma tabela.

37. Dependência Funcional (11) • Dependências Funcionais são direcionais: enoename não é o mesmo queenameeno • Exemplo: • Dado um nome de empregado podem existir diversos valores para o atributo eno se há empregados na base de dados com o mesmo nome. • Dessa forma, sabendo-se o valor do atributo ename não há como identificar unicamente o valor do atributo eno.

38. Dependência Funcional (12) • Uma dependência funcional é chamada de trivial se os atributos do seu lado esquerdo formam um superconjunto (superset) dos atributos do lado direito. Ou seja, um determinante (conjunto de atributos do lado esquerdo) com mais de um atributo pode determinar seus próprios membros quando isolado. • Exemplo: • enoeno • eno,enameeno • eno,pno,hourseno,hours • Dependências funcionais triviais não são de interesse devido ao fato delas não dizerem absolutamente nada. • O interesse na normalização é pelas dependências não triviais.

39. Dependência Funcional (13) • Dependências Funcionais podem ser utilizadas para se determinar as chaves candidatas e primárias de uma relação. • Por exemplo, caso se saiba que um atributo funcionalmente determina todos os outros atributos numa relação, este atributo pode ser uma chave candidata. • Exemplo: • enoeno,ename,bdate,title,salary,supereno,dno • O atributo eno é uma chave candidata para a tabela Employee. Employee

40. Dependência Funcional (14) • Definição alternativa de chaves: • Um conjunto de atributos K é uma superchave para uma tabela R se o conjunto dos atributos K funcionalmente determina todos os atributos em R. • Um conjunto de atributos K é uma chave candidata para uma tabela R se K é uma superchave mínima de R.

41. Regras para Dependências Funcionais (1) • Sejam A, B, C e D subconjuntos dos atributos de uma tabela R. • Regra 1: Reflexão Se A B então AB • Um conjunto de atributos, sempre e trivialmente, determina qualquer subconjunto de si mesmo. • Exemplo: Se {CPF, nome}  {nome} então CPF,nomenome • Lê-se o exemplo acima do seguinte modo: • Se o atributo nome é um subconjunto do conjunto formado pelos atributos CPF e nome, então os atributos CPF e nome conjuntamente permitem encontrar o nome de uma pessoa.

42. Regras para Dependências Funcionais (2) • Regra 2: Ampliação Se AB então A,CB,C • A adição de um conjunto de atributos a ambos os lados da dependência funcional resulta em outra dependência funcional válida. • Exemplo: Se CPFnome então CPF,enderecoCPF,endereco • Lê-se o exemplo acima do seguinte modo: • Se com um número de CPF encontra-se o nome de uma pessoa, então com o número de CPF e o endereço pode-se encontrar o nome e o endereço de uma pessoa.

43. Regras para Dependências Funcionais (3) • Regra 3: Transitividade   Se AB e BC então AC • Se um atributo determina um segundo atributo, e este atributo determina um terceiro atributo, então o primeiro atributo determina o terceiro atributo. • Exemplo:   Se CPFcodigo-cidade e codigo-cidadenome-cidade então CPFnome-cidade • Lê-se o exemplo acima do seguinte modo: • Se com um número de CPF pode-se encontrar o código da cidade e com o código da cidade encontra-se o nome da cidade, então com o número do CPF pode-se encontrar o nome da cidade.

44. Regras para Dependências Funcionais (4) • Há outras três regras que são derivadas das regras anteriores. • Regra 4: Decomposição Se AB,C então AB e AC • Uma dependência funcional com dois atributos no lado direito pode ser decomposta em duas dependências funcionais com um atributo no lado direito. • Exemplo: Se CPFnome,endereço então CPFnome e CPFendereco • Lê-se o exemplo acima do seguinte modo: • Se com um número de CPF encontra-se o nome e o endereço de uma pessoa, então com este mesmo CPF pode-se encontrar apenas o nome, e com este mesmo CPF pode-se encontrar apenas o endereço.

45. Regras para Dependências Funcionais (5) • Regra 5: União Se AB e AC então AB,C • É o reverso da regra anterior. • Exemplo: Se CPFnomeeCPFendereço então CPFnome, endereco • Lê-se o exemplo acima do seguinte modo: • Se com um número de CPF encontra-se o nome de uma pessoa e com o o mesmo número de CPF encontra-se seu endereço, então com este mesmo CPF pode-se encontrar o nome e o endereço da pessoa.

46. Regras para Dependências Funcionais (6) • Regra 6: Composição Se AB e CD então A,CB,D • É uma regra mais geral que a união, ou seja, ela combina duas dependências funcionais não sobrepostas em uma única dependência funcional. • Exemplo: Se CPFcódigo-funcionarioeRGendereço então CPF, RGcódigo-funcionario, endereco • Lê-se o exemplo acima do seguinte modo: • Se com um número de CPF encontra-se o código do funcionário, e com o número de RG encontra-se seu endereço, então com os números de CPF e RG pode-se determinar o código do funcionário e seu endereço.

47. Dependência Funcional Completa (1) • Conceito de Dependência Funcional Completa: • Um atributo é considerado como tendo dependência funcional completa de um outro conjunto de atributos, quando é funcionalmente dependente do conjunto inteiro, mas não de qualquer subconjunto do determinante. • A dependência funcional AB é considerada uma dependência funcional completa de B se a remoção de algum atributo de A resulta na inexistência (quebra) da dependência. • Diz-se que o atributo B é completamente dependente do atributo A. • Se há a remoção de algum atributo de A e a dependência funcional ainda existe, diz-se que o atributo B é parcialmente dependente do atributo A.

48. Dependência Funcional Completa (2) • Exemplo: • enoename (dependência funcional completa) • eno, enamesalary,title (dependência parcial, pois pode-se remover o atributo ename sem afetar a dependência) • eno, pnohours,resp (dependência funcional completa).

49. Dependência Funcional Completa (3) •  Exemplo: • O atributo Horas está em dependência funcional completa dos atributos Matricula e CodProj. • O atributo DataFim não está em dependência funcional completa de Matricula e CodProj pois DataFim é funcionalmente dependente do atributo CodProjsozinho.

50. 2FN – 2a Forma Normal (1) • Uma tabela R está na Segunda Forma Normal - 2FN se: • Ela está na 1FN. • Todo atributo não chave for totalmente funcionalmente dependente da chave primária. Isto é, não deve haver dependências parciais na chave. • Se uma tabela não está em 2FN, divide-se essa tabela em tabelas separadas, cada uma em 2FN, garantindo-se que a chave primária de cada nova tabela funcional e completamente determina todos os atributos da relação. • Por definição, qualquer tabela com uma chave primária de um único atributo sempre está na 2FN.