Dependências Funcionais e Formas Normais

1. Dependências Funcionais eFormas Normais Pedro Sousa

2. Introdução • As dependências funcionais e as formas normais estabelecem critérios de qualidade de desenho no modelo Relacional. • Permitem detectar e prevenir a redundância e a coerência da informação. • Fundamenta-se nas dependências entre os atributos das relações • Foi extensivamente usado como critério de desenho de sistemas de informação. • Ao nível do modelo Relacional, deve ser usado para detectar eventuais problemas associados à da redundância e coerência da informação. • Ao nível do modelo Entidade Associação, deve ser usado para identifcar entidades e determinar os seus atributos. Pedro Sousa

3. O problema da Redundância dos Dados • A redundância dos dados tem implicações ao nível da coerência e da completude da informação neles contidas • São bem conhecidos os seguintes problemas, designados por anomalias: • Anomalia de inserção: quando os factos (conhecimento) independentes não podem ser inseridos independentemente na base de dados. • Anomalia de eliminação: quando se elimina uma instância de um facto, perde-se instâncias de outros factos independentes. • Anomalia de actualização: quando a actualização de um facto implica a alteração de uma série de instâncias de outros factos independentes. Pedro Sousa

4. NºEncomenda | Nº Peça | Quantidade| Preço Unitário | | | | | | Exemplo Introdutório Consideremos a seguinte Entidade /Relação Encomenda Quantidade • Anomalia de inserção: Só podemos indicar o preço de cada peça se existirem encomendas pendentes. • Anomalia de eliminação: Ao eliminar uma encomenda, perde-se a informação relativa ao preço de cada peça. • Anomalia de actualização: A alteração do preço de uma peça implica a alteração do mesmo em todas as encomendas existentes. Nº Encomenda Encomenda Nº da Peça Preço Unitário Pedro Sousa

5. Quantidade Nº Encomenda Preço Unitário Nº Peça Exemplo Introdutório Dependências Funcionais Encomenda Quantidade Nº Encomenda NºEncomenda | Nº Peça | Quantidade| Preço Unitário • O problema reside nas relações de dependências entre os atributos da Entidade/Relação: • o Nº da encomenda determina Nº da peça, a Quantidade e o Preço Unitário. • O Nº da peça determina o Preço Unitário. | | | Encomenda Nº da Peça | | | Preço Unitário Diagrama de Dependências Funcionais Pedro Sousa

6. Nome-Director Endereço-Armazém Nome-Armazém Nº-Encomenda Nome-Fornecedor Peça Nº Data-Inventário QuantidadeEntregue QuantidadeExistente Diagrama de Dependências FuncionaisOutro Exemplo Que informação está contida neste Diagrama de Dependências Funcionais? De que modo essa informação é pode ser útil no desenvolvimento de esquemas Relacionais ou mesmo Conceptuais (ER)? Pedro Sousa

7. Definições Elementares • Seja uma relação R com conjuntos de atributos X e Y: Diz-se que X determina Y se para todo o valor de X existe um só valor de Y. • Por outras palavras, sempre que dois tuplos têm um valor de X comum, têm também o valor de Y em comum. • Diz-se também que X é determinante, e representa-se graficamente por X-> Y. • A dependência funcional é um questão semântica. Não pode ser deduzida com base num conjunto de tuplos. Estes apenas podem confirmar que determinada dependência não existe. Pedro Sousa

8. Propriedades das Dependências Funcionais(regras de Armstrong) • Reflexividade • Se Y está contido em X, então X-> Y • Incremento • Se X -> Y, então X Z -> YZ • Transitividade • Se X -> Y e Y -> Z, então X-> Z Pedro Sousa

9. Regras Derivadas • Auto-Reflexividade • X -> X • Decomposição • Se X -> Y Z, então X-> Y e X -> Z • União • Se X-> Y e X -> Z então X -> YZ • Composição • Se X-> Y e A -> B então XA -> YB Pedro Sousa

10. Dependência total e atributos chaves • Seja X um atributo e Y o conjunto de atributos { y1, y2, ...yn} tal que {y1, y2, ...yn} determina X • Dizemos que X é completamente dependente de Y, se qualquer subconjunto dos elementos de Y não determina X. • Dizemos que X é um atributo chave se pertence ao identificador da Entidade ou à chave primária da Relação Pedro Sousa

11. As Formas Normais 1 Forma Normal 2 Forma Normal 3 Forma Normal Forma Normal Boyce Codd 4 Forma Normal 5 Forma Normal Pedro Sousa

12. As Formas Normais Seja uma entidade (ou associação) X com atributos {i1,..,in,a1,...,am} Seja {i1,..,in} os atributos que constituem a chave de X. • A 1ª Forma Normal prende-se com a estrutura de cada atributo de X. • A 2ª Forma Normal prende-se com a dependência entre os atributos {a1,...,am} e os atributos {i1,..,in}. • A 3ª Forma Normal prende-se com as dependências mútuas entre os atributos {a1,...,am}. • A BCNF prende-se com existência de várias chaves Pedro Sousa

13. Pessoa Pessoa Cidade Residência Data Data Cidade João Lisboa João 01-02-92 01-02-92 Lisboa Faro 11-12-94 João 11-12-94 Faro Data Cidade Zé Zé Lisboa 10-10-84 Lisboa 10-10-84 1ª Forma Normal Uma relação está na 1ª Forma normal se todos os seus atributos são valores escalares. • Todas as relações “normalizadas” estão na 1ª Forma Normal “Relação” não Normalizada Relação Normalizada Pedro Sousa

14. ID_Pessoa | Projecto | OrçamentoProjecto | TempoGastoProjecto | | | | | | ID_Pessoa TempoGastoProjecto Projecto OrçamentoProjecto Problemas com a 1ª Forma NormalExemplo • A 1ª FN não assegura a resolução do problema das anomalias! • Esta relação está na 1ª FN mas apresenta as anomalias de inserção, de actualização e de eliminação! Pedro Sousa

15. ID_Pessoa | Projecto | TempoGastoProjecto Projecto | OrçamentoProjecto | | | | | | OrçamentoProjecto Projecto Eliminação das Anomalias do Exemplo Anterior • A eliminação das anomalias implica a fragmentação da relação inicial em duas novas relações • Estas relações não têm as anomalias ! ID_Pessoa Projecto TempoGastoProjecto Pedro Sousa

16. Identificação Ano Nº Encomenda Nr-da-Peça Encomenda A 1 Forma Normal e os Modelos Conceptuais • Qual o relação entre os atributos multivalor os atributos compostos e a 1 Forma Normal ? • Em ambos os casos, os atributos Nr-da-Peça e identificação não são atributos escalares! • Esta é a razão pela qual a conversão ER para Relacional começa pela conversão dos atributos multivalor e compostos em atributos simples Pedro Sousa

17. A 2ª Forma Normal Uma Relação está na 2ª Forma normal se está na 1ª Forma Normal e cada atributo não chave é completamente dependente dos atributos chave. • Ou seja, todos os atributos que não pertencem a nenhuma chave são completamente dependentes de pelo menos uma chave. • Todos as entidades ou associações na 1FN e com uma chave simples está necessariamente na 2FN. • Nada se diz quanto às dependências mútuas entre os atributos que pertencem às chaves. • Nada se diz quanto às dependências mútuas entre os atributos que não pertencem às chaves. Pedro Sousa

18. 2ª Forma NormalExemplos Consideremos a a Relação R(A,B,C,D) e a dependência A -> D (as dependências AB ->C e AB ->D são implícitas) • Esta relação não está na 2ª FN. • A decomposição da relação R com vista à 2ª FN passa por criar as seguintes Relações: • R1(A,B,C) com a dependência AB -> C • R2(A,D) com a dependência A -> D cada atributo não chave é completamente dependente dos atributos chave Pedro Sousa

19. Nº Estudante Nº Estudante Professor Exame Exame Nota Nota Cadeira Professor Nome Cadeira Nome Exame está na 2FN Atributos da Associação Exame: Nº -Estudante,Nome-Cadeira, Professor,Nota Identificador da Associação Exame: Nº -Estudante,Nome-Cadeira Dependências da associação Exame: Nº-Estudante, Nome-Cadeira Professor,Nota Nome-Cadeira Professor 2ª Forma Normal Exemplos Exame não está na 2FN O atributo Professor não pode ser atributo de Exame! Pedro Sousa

20. Preço Total NrEncomenda Quantidade Quantidade Encomenda Nº da Peça NrEncomenda Preço Unitário Preço Unitário Nº Peça Preço Total 2ª Forma Normal Exemplos • Cada encomenda refere-se apenas a uma quantidade de uma única peça.Considere a Relação (ou Entidade) Encomenda. • Esta Entidade/Relação está na 2ª Forma Normal ? Encomenda(NrEncomenda, NºPeça,PreçoUnitário, Quantidade,PreçoTotal) Pedro Sousa

21. Preço Total Quantidade NrEncomenda Preço Unitário Nº Peça Problemas com a 2ª Forma Normal • As 2ª Forma Normal não garante a não redundância da informação, sendo portanto possível que os problemas das anomalias existam também nas relações na 2FN. • Por exemplo, a informação sobre o preço de cada peça. • A redundância surge devido às dependências entre os atributos não chave. Pedro Sousa

22. Eliminação das Anomalias • A eliminação das anomalias implica a eliminação das dependências entre os atributos não chave. • Tal implica a decomposição da Entidade/Relação original em duas. Preço Total Preço Total Quantidade NrEncomenda Quantidade Nº Peça NrEncomenda Preço Unitário Nº Peça Preço Unitário Nº Peça Estão na 2FN Está na 2FN Pedro Sousa

23. A 3ª Forma Normal Todos os atributos não chave são completamente dependentes dos atributos chave e são independentes entre si. • Ou seja, a 3FN implica a 2FN e que não existam dependências entre os atributos não chave. • Todas as entidades que apenas têm um atributo não chave estão na 3FN, se estiverem na 2FN. • Nada se diz quanto às dependências mútuas entre os atributos que pertencem às chaves. Pedro Sousa

24. 3ª Forma Normal: Exemplo 1 Consideremos a a Relação R(A,B,C) e a dependência B-> C • Esta relação não está na 3ª FN. • A decomposição da relação R com vista à 3ª FN passa por criar as seguintes Relações: • R1(B,C) com a dependência B -> C • R2(A,B) com a dependência A -> B cada atributo não chave é completamente independente dos restantes atributos não chave Pedro Sousa

25. Nº Empregado Empregado Nome Do Departamento Da Departamento Chefe Divisão Divisão Nº Empregado Nome, Departamento, Divisão,Chefe está na 3FN Departamento Divisão Divisão Chefe 3ª Forma Normal: Exemplo 2 Nº Empregado Nome Empregado v Departamento Divisão Chefe não está na 3FN Pedro Sousa

26. Nº Empregado, Nº Departamento Orçamento, Nº Horas, Nº Projecto Nº Projecto Orçamento Trabalha Trabalha 3ª Forma Normal: Exemplo 3 Nº Empregado Empregado Nome Nº Horas Nº Projecto Nº Horas Orçamento Projecto Nº Projecto Orçamento Nº Departamento Departamento Piso Trabalha não está na 3FN, porque Projecto determina Orçamento e não é chave Pedro Sousa

27. a1 a3 a3 a1 a1,a2 a3,a4 X(a1, a2, a3, a4) a3,a2 a1,a4 Problemas com a 3ª Forma Normal Seja a Entidade/Relação X com os seguintes atributos e identificadores • Os problemas da 3FN surgem quando: • Existem dependências entre os atributos dos identificadores, por exemplo: • Existem vários identificadores compostos, com pelo menos um atributo comum, por exemplo: a1 X a2 a3 a4 Pedro Sousa

28. Director Projecto | Director | Peça | Quantidade | | | Peça | | | Quantidade Projecto Problemas com a 3ª Forma Normal: Exemplo Relação Usa • O director de cada projecto é armazenado várias vezes • O director de um projecto só é armazenado quando o projecto encomendar peças • Um projecto não pode ser armazenado enquanto o seu director não for conhecido • A mudança de um director de um projecto implica a mudança de vários tuplos Problemas: • Estes problemas resultam de: • peça ser partilhada por determinantes, e • dependência entre Director e Projecto Pedro Sousa

29. A Forma Boyce Cood(BCNF) Quando todos os determinantes são chave • Não existem dependências entre os atributos não chave • Não existem dependências entre sub-conjuntos dos atributos das chaves. • A BCNF só se distingue da 3FN quando: • Existem mais do que uma chave • As chaves são formadas por vários atributos Pedro Sousa

30. Estado F# Nome Cidade Exemplo -1Chaves Disjuntas • Seja a Relação Fornecedor : Fornecedor (F#, Nome, Estado, Cidade) • Em que FN esta Relação está ? • Que anomalias são possíveis na relação Fornecedor ? • Que inconvenientes existem na existência de 2 chaves candidatas ? Pedro Sousa

31. Exemplo - 2Chaves Sobrepostas • Seja a Relação Venda : Venda (F#, P#, Fnome,Quantidade) F# Quantidade P# FNome • Em que FN esta Relação está ? • Que anomalias são possíveis na relação ? • Quais são as decomposições mais convenientes ? Pedro Sousa

32. Aluno Disciplina Exemplo -3 • Seja a Relação Aulas : Aulas (Disciplina,Professor,Aluno) Professor • Em que FN esta Relação está ? • Que anomalias são possíveis na relação ? • Quais são os inconvenientes das seguintes decomposições ? • V1 (Aluno,Professor) e V2 (Professor,Disciplina) • V1 (Aluno,Disciplina) e V2 (Professor,Disciplina) • V1 (Aluno,Disciplina) e V2 (Professor,Aluno) Pedro Sousa

33. Notas sobre a decomposição de Relações/Entidades • A decomposição de uma relação ou entidade com vista à sua normalização pode implicar a perca de informação. O Problema da Perca de Informação • Entre as decomposições que não implicam a perca de informação, importa decidir sobre a mais correcta. O Problema da Perca das Dependências Pedro Sousa

34. A | B | C A | B | C a1 | b1 | c1 B | C a1 | b1 | c1 a1 | b1 | c2 b1 | c1 a3 | b1 | c2 a3 | b1 | c1 A | B b1 | c2 a2 | b2 | c3 a3 | b1 | c2 b2 | c3 a1 | b1 a4 | b2 | c4 a2 | b2 | c3 b2 | c4 a3 | b1 a2 | b2 | c4 a2 | b2 a4 | b2 | c3 a4 | b2 a4 | b2 | c4 Decomposição com perdas de informação • Seja R1(A,B,C), e A-> B e C-> B. • Consideremos a decomposição de R1 em R2(A,B) e R3(B,C) ,e a sua posterior restituição em R´1. Existe perda de informação! R´1 R3 R1 • Decomposição R2 • União Pedro Sousa

35. Decomposição sem perdas de informação • A decomposição de uma relação diz-se sem perdas quando esta pode ser obtida a partir do join natural das relações resultantes da decomposição • A decomposição de uma relação R(X,Y,Z) (em que X, Y, Z são conjuntos de atributos) em R1(X,Y) e R2(X,Z) é sem perdas se X->Y ou se X->Z. (Teorema de Heath´s) Pedro Sousa

36. X | Y | Z X | Z x1 | y1 | z1 X | Y | Z x1 | z1 x2 | y2 | z2 x1 | y1 | z1 x2 | z2 X | Y x3 | y2 | z1 x2 | y2 | z2 x3 | z1 x1 | y1 x4 | y1 | z2 x3 | y2 | z1 x4 | z2 x2 | y2 x4 | y1 | z2 x3 | y2 x4 | y1 Decomposição sem perdas de Informação: Exemplo R1: X-> Y, YZ-> X R3: R´1 • Decomposição R2: X-> Y • União Pedro Sousa

37. Exemplos de Decomposições • Seja a Relação Fornecedor (F#, Estado, Cidade). • Decomposição 1 F1 (F#, Estado) e F2(F#,Cidade) • Decomposição 2 F1 (F#, Estado) e F2(Cidade,Estado) • A 1ª não implica perdas, enquanto na segunda perde-se a dependência F#->Cidade Pedro Sousa

38. Data NrCliente NomeCliente Exemplo • Encomenda não está na 1FN. • Porquê ? • Quais são as anomalias que se podem verificar? Codigo-Peça Preço Item NrEncomenda Encomenda Quantidade Pedro Sousa

39. Data NrCliente NomeCliente NrEncomenda NrItem PreçoUnitário 1 N Encomenda Detalhe Da Quantidade Exemplo • Este esquema está na 1FN, mas não está na 2FN. • Porquê ? • Quais são as anomalias que se podem verificar? Detalhe(NrItem, NrEncomenda,PreçoUnitário, Quantidade) Encomenda(NrEncomenda,Data,NrCliente,NomeCliente) Pedro Sousa

40. Data NrCliente NomeCliente Exemplo • Este esquema está na 2FN, mas não está na 3FN. • Porquê ? • Quais são as anomalias que se podem verificar? Quantidade NrEncomenda 1 N Encomenda Item Detalhe PreçoUnitário NrItem Detalhe(NrItem, NrEncomenda, Quantidade) Item(NrItem, PreçoUnitário) Encomenda(NrEncomenda,Data,NrCliente,NomeCliente) Pedro Sousa

41. NomeCliente Data NrCliente Exemplo Quantidade NrEncomenda • Este esquema está na 3FN 1 N Encomenda Item Detalhe N Do PreçoUnitário NrItem N Cliente Detalhe(NrItem, NrEncomenda,PreçoUnitário, Quantidade) Item(NrItem, PreçoUnitário) Encomenda(NrEncomenda,Data,NrCliente) Cliente(NrCliente,NomeCliente) Pedro Sousa

42. Formas Normais: Exercício 1 A B D Qual o Identificador ? Em que FN está ? E1 B AB C C D A AB CE Qual o Identificador ? Em que FN está ? E2 B E AB C C D D E Pedro Sousa

43. Formas Normais: Exercício 2 A B D Qual o Identificador ? Em que FN está ? E3 B D B C ABD C D A A BCD Qual o Identificador ? Em que FN está ? E4 B B ACD C CD AB D Pedro Sousa

44. Exercício 3 Considere a relação R(a,b,c,d,e), em que {a,b} é a chave primária e d->e . - Indique todas as dependências da relação R. - Qual a forma normal da relação R? - Assuma agora que a chave primária R é {a,b,d}. Em que forma normal a relação R passa a estar? Pedro Sousa

45. Exercício 4 Considere a relação R(a,b,c,d), em que {a,b} é a chave primária. - Indique uma dependência funcional entre os atributos de R de forma a que R não esteja na 2ª forma normal. - Indique uma dependência funcional entre os atributos de R de forma a que R esteja na 2ª forma normal mas não esteja na 3ª forma normal. - Indique uma dependência funcional entre os atributos de R de forma a que R esteja na 3ª forma normal mas não esteja na Boyce Codd. Pedro Sousa

46. Exercício 5 • Considere a relação R (a,b,c) e a sua decomposição nas relações R1(b,c) e R2(a,c). - Qual ou quais as dependências que têm necessariamente que existir entre os atributos a, b e c para que a decomposição de R em R1 e R2 não represente perda de informação? - Assumindo a existência da ou das dependências anteriores, escreva uma expressão em álgebra relacional que permita reconstituir R a partir de R1 e R2 ? - Pedro Sousa