normalização-v3-II

(1)

1

Projeto Lógico de BDs

Normalização e

Dependência Funcional

Fábio Porto DEXL Lab

LNCC Pos-graduação Banco de Dados

2

Agenda

l 

Corretude do projeto de banco de dados;

l 

Dependências Funcionais;

l 

Decomposição;

l 

Propriedades da Decomposição;

l 

Regras de Normalização;

3

Introdução

l 

Vocês viram como projetar um banco de

dados a partir do modelo ER;

(2)

DEXL Lab

4

Introdução

l  Características de qualidade de um projeto lógico

do BD:

a.  Representa os objetos do mini-mundo real M que precisam ser armazenados

b.  Obedece às restrições de integridade C do mini-mundo

M;

c.  Dados N={n1, n2,.., nk} projetos de BD de M i.  ni ocupa um espaço em disco Si ii.  ni garante as regras de integridade Ci⊆ C

iii.  Dado um cmd de atualização u sobre ni m operações são

necessárias

iv.  Dada uma consulta q, ni exige m junções

Fábio Porto

DEXL Lab

5

Característica de qualidade -

garantia de I.R.

l 

Nesta aula estudaremos técnicas de garantia

de qualidade do projeto lógico de BD frente à

atualizações de dados no BD;

Fábio Porto

DEXL Lab

Atualização e Consistência

l Objetivo de um esquema lógico: descrever um bd a

ser utilizado:

–  Carregado, acessado e atualizado

l As atualizações (inserções, exclusões e

modificações) devem manter a consistência da base de dados

–  Integridade referencial –  Todas as restrições de integridade

–  Em particular as dependências entre atributos (???)

l Pode ser + ou - simples

–  Quanto mais redundância houver na base mais dificil será manter a consistência.

(3)

Fábio Porto

DEXL Lab

7

Exemplo de anomalia de atualização

Entrega (

N°f

orn,

endF,

N°

prod, preçoP, qtd)

3 Petropolis 52 6510

22 Rio 10 15 5

22 Rio 25 10 12

3 Petropolis 25 105 3 São Paulo 10 1520

l 

Se um fornecedor muda de endereço e somente

uma tupla é atualizada -> inconsistência

l 

Uma tupla é inserida para um fornecedor

conhecido com um endereço diferente ->

inconsistência

l 

Se um fornecedor não tem entregas em curso,

perde-se seu endereço

Fábio Porto

DEXL Lab

8

Como tratar relações de baixa

qualidade ?

Uma relação apresenta baixa qualidade:

l Implica em repetições

l Apresenta problemas de atualização

l As condições para que uma relação tenha

qualidade podem ser formalmente definidas

=>

regras de normalização

Fábio Porto

DEXL Lab

9

Exemplo (cont.)

Entrega (N°forn, endF, N°prod, preçoP, qtd)

3 Petrópolis 52 65 10 22 Rio 10 15 5 22 Rio 25 10 12 3 Petropolis 25 10 5 3 Rio 10 15 20

l O endereço do fornecedor depende apenas deste;

⇒ REDUNDANCIA ⇒ Anomalias de atualização l A relação precisa ser normalizada

(4)

DEXL Lab

10

Normalização de um esquema lógico

l Processo de transformação de um esquema S1 para

um esquema S2:

– É equivalente (mesmo conteúdo) – As atualizações são simples

l Atualização simples :

– 1 atualização elementar no mundo real se traduz a atualização de uma tupla da relação;

l Exemplos de modificações elementares – Um fornecedor muda de endereço – Um produto muda de preço

Entrega (N°forn, endF, N°prod, preçoP, qtd)

Nesta relação as atualizações são complexas

Fábio Porto

DEXL Lab

11

Normalização de uma relação

l Processo de decomposição de uma relação com

atualizações complexas em várias relações com atualizações simples

l Processo sobre o esquema relacional

l Exemplo : a relação

Entrega (N°forn, endF, N°prod, preçoP, qtd) sera decomposta em:

l Entrega (N°forn, N°prod, qtd)

Fornecedor (N°forn, endF) Produto (N°prod, preçoP)

Fábio Porto

DEXL Lab

Normalização

l Avalia-se a qualidade de uma relação pelo seu grau

de normalização

l 1FN (primeira forma normal), 2FN, 3FN, FNBC

(forma normal de Boyce Codd), 4FN, etc.

2FN 3FN FNBC 4FN 1FN

(5)

Fábio Porto

DEXL Lab

13

Normalização ou tradução ER

Validação Regras TRADUÇÃO ER - R NORMALIZAÇÃO Fábio Porto DEXL Lab

14

Exemplo

VALIDAÇÃO Regras TRADUÇÃO Décomposition des relations

non satisfaisantes NORMALISA ÇÃO

0-n 0-n

Fábio Porto

DEXL Lab

15

Formalização do problema

l O endereço do fornecedor depende apenas deste,...

⇒ DEPENDÊNCIA FUNCIONAL (DF) l Notação :

–  A, B, C … atributos –  X, Y, Z … conjunto de atributos

l Seja uma relaçao R (X, Y, Z)

l Existe uma DF : XàY se, e somente se, em uma

relação R para um mesmo valor de X sempre corresponde um mesmo valor de Y

(6)

DEXL Lab

16

Formalização

l  R : X Y Z x1 y1 z1 ... x1 y1 z2 ... l X à Y : X determina Y Y depende de X l X : fonte da DF, Y : alvo da DF l exemplo :

(aluno, disciplina,ano) à nota

Existe uma DF : XàY se, e somente se, em uma relação R para um mesmo valor de X sempre corresponde um mesmo valor de Y

Fábio Porto

DEXL Lab

17

Propriedades das DF

l Transitividade : se X à Y e Y à Z então X à Z (DF inferida) l Extensão:

se X à Y então (A, X) à Y para qq A

(DF não elementar)

l Para efeito do projeto de relações, nos interessamos

em DF elementares

– Estas exprimem fatos elementares do mundo real – Permitem avaliar a qualidade de uma relação ou, caso

necessário, como as decomposer

Fábio Porto

DEXL Lab

FD como restrições sobre um

esquema

l 

Dado um esquema:

– R=(R_, Restrições), onde R_ é o conjunto de

atributos de R e restrições é um conjunto de FDs, uma instância válida de R obedece atodas as f

(7)

Fábio Porto

DEXL Lab

19

Grafo de DF

l Para cada relação identificam-se todas suas DFs

elementares

l Constrói-se um grafo orientado representativo das

dependências funcionais

grafo mínimo das DFs de uma relação

l Uma relação pode ter vários grafos mínimos

equivalentes

l Exemplo de um grafo mínimp :

R (A, B, C, D, E)

EàA EàB EàC (E ! A, B, C)

CàD

E

A B C

D

Fábio Porto

DEXL Lab

20

Utilidade do grafo de DF

l Verificar que o grafo é mínimo

l Determinar os identificadores da relação

l Verificar se a relação é de boa qualidade

(normalizada)

l Testar se a relação está normalizada

l Caso não esteja, determinar as decomposições

Exemplo de grafo não mínimo

E!D é inferida de E!C e C!D Deve-se suprimir do grafo E!D

E

A B C

X

D

Fábio Porto

DEXL Lab

21

Verificar se um grafo é mínimo

l DF inferida

X

D

X!D é inferida se existe um outro caminho X!A1… !...An!D

DF não elementar

X!D é não elementar se existe uma DF Y!D tal que Y est um sub-conjunto dos atributos de X (A,B,C)!D é não elementar

A B C

D

X

(8)

DEXL Lab

22

Exemplo de grafo de DF

Entrega (N°forn, endF, N°prod, preçoP, qtd) l N°forn à endF

– O endereço depende apenas do fornecedor

l N°prod à preçoP

– O preço do produto depende apenas deste

l (N°forn, N°prod) à qtd

– A quantidade depende do produto e do fornecedor

[falso : N°forn ! qtd, N°prod ! qtd ]

N°forn N°prod

endF qtd preçoP

Fábio Porto

DEXL Lab

23

Exemplo

l 

R (N°Aluno, sobrenome, nome, end, dataN,

Disciplina, ano, nrCreditos, prof, nota)

l 

Definição :

O estudante (N°Aluno) obteve tal nota no

ano tal, em tal Disciplina com tal professor,

obtendo um total de nrCreditos

Faça o grafo mínimo das DFs

Grafo de DF de R

N°Aluno

sobrenome

nome

endereço

dataN

nbCrédits

prof

nota

Disciplina

ano

(9)

Fábio Porto

DEXL Lab

25

DFs e identificadores

l O grafo mínimo de DF permite determinar os identificadores da

relação

l O identificador de uma relação é um conjunto(mínimo) de nós

do grafo mínimo a partir do qual pode-se chegar a todos os outros nós (através das DF)

l Prova :

– Para que seja falso, seria necessário existir duas tuplas com o mesmo valor para os atributos identificadores com valores diferentes para os outros atributos, o que seria uma contradição a DFs..

l Exemplo : R (A, B, C, D, E) E

A B C ! D

E é o identificador de R

Fábio Porto

DEXL Lab

26

DFs e identificadores - Exemplo

l Outro exemplo : R (A, B, C, D, E, F, G) F G A B C E D

(F, G) é o identificador de R

Algoritmo 2 para identificação de

chaves candidatas

l 

Considere R=(R_, FD)

l 

Seja A um atributo em R_

tal que FD implica , então existe

uma chave candidata em R_

l 

Retire um outro atributo de R_ e chamemos

do restante dos atributos de X, tal que

l 

Continue assim até que não possa mais

retirar nenhum atributo de R_ mantendo

27

X →R_

(R _− A) → R _

(10)

DEXL Lab

Normalização

28

NoForn Nome End NoProd Descrição

1 Casa X Rua 1 1 prod1

1 Casa X Rua 1 2 pod2

2 Casa Y Rua 2 1 prod1

Relação Fornecimento tem conteúdo redundante Normalização decompõe a relação :

•  eliminando às redundâncias

•  mantendo, se possível às DFs

•  sem perda de informação

Fábio Porto

DEXL Lab

29

Normalização por decomposição

l Seja uma relação R que contém redundâncias e

apresenta problemas de atualização: « Não está normalizada »

l É preciso decompô-la em relações menores de

melhor qualidade (normalizadas)

– Aplicando projeções – Seguindo as DFs

l O processo garante a obtenção de relações normalizadas.

l É preciso garantir a conservação do conteúdo,

junções sem perdas:

– A junção das novas relações reproduz R

Fábio Porto

DEXL Lab

Exemplo de Decomposição

com perdas

R (A, B, C), com A-> B

queremos decompor em R1(A,B) e R2 (B,C) R’ se decompormos em : R1=Π a,b(R) e R2=Π b,c(R) teríamos: R1= e R2= a1 b1 c1 a2 b1 c2 a1 b1 b1 c1

(11)

Fábio Porto

DEXL Lab

Exemplo de decomposição

com perdas

31

A recomposição de R a partir de R1 e R2 seria realizada através de junções:

R’’ = R1 R2 R’’= a1 b1 c1 a1 b1 c2 a2 b1 c1

a2 b1 c2 Duas tuplas adicionais foram geradas, logo R’ não equivale a R’’

Fábio Porto

DEXL Lab

Decomposição sem perda

l 

Teorema:

“Uma decomposição (R1,R2) de uma relação

R, obedecendo a um conjunto de

dependências funcionais DF é sem perdas se e

somente se, “

No exemplo anterior:

32

R1

∩ R2 → R1− R2∨ R2 − R1

R1 − R2 = A e R2 − R1 = C com A → B

R1

∩ R2 → B

33

Normalização por decomposição

R (A1, A2, … , An)

R1 = π[A1, A2,… Ai] R R2 = π[Ai, Ai+1,… Aj] R ….

Rk = π[Al, Al+1,… An] R

decomposição

junção

Se

R = R1R2 …*Rn

A decomposição é sem perda de informação

As consultas sobre R e aquelas sobre o novo BD

produzem o mesmo resultado

(12)

DEXL Lab

34

Teorema de Heath

l TEOREMA :

R (X, Y, Z) é passível de decomposição sem perdas se

R1 = π[X,Y]R R2 = π[X,Z]R

se existe a DF XàY, ou X-> Z

l R1 é necessariamente normalizada (em 3FN), pois

obedece o fato elementar XàY

l As consultas submetidas a R e aquelas sobre R1*R2

retornam o mesmo resultado

Fábio Porto

DEXL Lab

35

Exemplo : decomposição sem

perda de informação

Zoé Armand Marie Rio Petropolis São Paulo secretaria secrétaria diretor 27 32 38

R1 (NomEmp, adresse, poste) R2 (NomEmp, age)

Zoé Armand Marie Rio Petrópolis Sãp Paulo secretaria secretaria diretor Zoé Armand Marie 27 32 38 R1=π(Nome,cidade,cargo) R2=π(Nome, idade) R = R1*R2 - A decomposição é sem perda, mas inutil

NB

Nome Cidade Cargo idade

com,

Nome → cidade, cargo, idade

Nome Cidade Cargo Nome idade

Fábio Porto

DEXL Lab

Exemplo : decomposição com

perda de informação

R1' (NomEmp, adresse, poste) R2' (poste, age)

Zoé Armand Marie Rio Petropolis São Paulo secretaria secretaria diretor secretaria secretaria diretor 27 32 38

**R1' * R2'**

Zoé Zoé Armand Rio Rio Petropolis secretaria secretaria secretaria 27 32 27 32 secretaria Petropolis Armand

≠

R

(13)

Fábio Porto

DEXL Lab

37

Aplicação de Heath

l N°forn à endF => R1 (N°forn, endF) ok

Entrega' (N°forn, N°prod, preçoP, qtd) l N°prod à preçoP => R2 (N°prod, preçoP) ok

Entrega'' (N°prod, N°forn, qtd)

l (N°prod, N°forn) à qtd =>Entrega'' (N°prod, N°forn, qtd) ok

Entrega (N°forn, endF, N°prod, preçoP, qtd)

N°

forn

N°

prod

endF qtd preçoP

Fábio Porto

DEXL Lab

38

Qualidade de uma decomposição

l  Uma decomposição correta tem as propriedades:

–  Sem perda de informação –  Sem perda de DF

–  Que produz relações mais normalizadas

l  Sem perda de DFs:

– Todas as DFs devem aparecer em alguma das relações obtidas pela decomposição

– Um DF faltante => uma restrição de integridade não descrita=> o SGBD não pode verificar automaticamente – Uma DF tendo como fonte um identificador sera

automaticamente validada pelo SGBD

Fábio Porto

DEXL Lab

39

Normalização: etapas

1)  Verificar se a relação está em 1a forma normal

2)  Estabelecer o grafo mínimo de dependências

3)  Encontrar os identificadores

4)  Determinar, a partir do grafo, sua forma normal

5)  Se a relação não estiver normalizada, decompô-la

(14)

DEXL Lab

40

Formas normais : 1FN

l Uma relação está na 1FN se cada valor de cada

atributo de cada tupla é um valor simples l Exemplo:

Entrega (N°forn, endF, N°prod, preçoP, qtd) está em 1FN

l Em SGBDs relacionais, projetam-se relações

sempre em 1FN

Fábio Porto

DEXL Lab

41

2a forma normal : 2FN

Entrega mistura a descrição:

- de entrega ( N°forn, N°produto, quantidade-entregue)

- do fornecedor ( N°forn, endereço-forn) - do produto ( N°produto, preço)

• 

Permite eliminar os atributos que não descrevem o

objeto representado pela relação

Entrega (N°forn, endF, N°prod, preçoP, qtd)

N°forn N°prod

endF qtd preçoP

Fábio Porto

DEXL Lab

2a forma normal : definição

l 

Definição : uma relação está em 2FN se

- ele está em 1FN, e

- todo atributo que não faz parte do identificador

depende do identificador inteiramente ( e não de

uma parte dele)

l Existem DFs partindo de parte do identificador

Entrega (N°forn, endF, N°prod, preçoP, qtd)

N°forn N°prod

(15)

Fábio Porto

DEXL Lab

43

3FN : 3a forma normal

l Deve ser decomposta em:

F (N°forn, cidade) G (cidade, pais)

• 

Permite identificar sub-relaçoes contidas na relação

• 

Exemplo : Fornecedor (N°forn, cidade, pais)

N°forn

cidade

pais

inferida

Fábio Porto

DEXL Lab

44

3a forma normal : definição

l Definição : Uma relação encontra-se em 3FN se

- encontra-se em 1FN,e

- cada atributo que não faz parte de um identificador depende diretamente de um identificador completo

– Profundidade da arvore de DF > 1 => Fornecedor não está em 3FN

§ 

Fornecedor (N°forn, cidade, pais)

N°Forn

Cidade

país

inferida

Fábio Porto

DEXL Lab

45

Importância da 3FN

l 

Toda relação pode ser decomposta em um

conjunto de relações em 3FN

– Sem perda de informação e

– Sem perda de DF

l 

Isso não ocorre com as DFs superiores

l 

Deve-se então procurar um projeto lógico, no

(16)

DEXL Lab

46

Forma normal de Boyce-Codd

l 

Generaliza a 3FN para relações com vários

identificadores

l 

Fornecedor (N°forn, nome-forn, N°produto, preço)

com 2 identificadores :

–  (N°forn, N°produto)

–  (nome-forn, N°produto)

l 

Definição : Uma relação encontra-se na FNBC se :

- ela se encontra na 1FN, e

- se todas as fontes completas de DF são

identificadores

l 

Um projeto de BD está normalizado segundo a

FNBC se todas DFs têm chaves à esquerda

N°forn nome-forn N°produit preço

Fábio Porto

DEXL Lab

47

FNBC - exemplo : Fornecedor

l Fornecedor (N°forn, nome-forn, N°produto, preço)

N°Forn

nome-forn

N°produto

preço

Fornecedor está na 3FN mas

não em FNBC

Precisamos decopor para obter relaçoes em FNBC

Atenção : esta transformação não é sempre possível

sem que haja perda de dependencias funcionais

Identificadores: - (N°forn, N°produto) - (nome-forn, N°produto) Fábio Porto DEXL Lab

Decomposição de Fornecedor

l Fornecedor (N°forn, nome-forn, N°produto, preço)

N°forn

nome-forn

N°produto

preço

■ 

F1 (N°forn, nome-forn)

com 2 identificadores

F2 (N°forn, N°produto, preço)

ou

(17)

Fábio Porto

DEXL Lab

49

FNBC – Exemplo

l Ranking (N°Estudante, Disciplina, Rank) – 2 identificadores : (N°Estudante, Disciplina)

(Rank ,Disciplina)

l Ranking está na 3FN e na FNBC

Fábio Porto

DEXL Lab

50

FNBC contra-exemplo

l Ensino (N°Estud, Disciplina, Prof)

– Cada professor ensina uma disciplina

– Para cada Disciplina, cada estudante segue a disciplina com apenas um professor;

l 2 identificadores : (N°Estud , Disciplina)

(N°Estud, Prof) l Ensino está em 3FN mais não em FNBC

N°Estud

Disciplina

Prof

Fábio Porto

DEXL Lab

51

Decomposição de Ensino

l Ensino (N°Estud, Disciplina, Prof)

l A decomposição segundo Heath :

R1 (Prof, Disciplina) R2 (Prof, N°Estud) sem perda de informação

mas com perda de DF { N°Estud, DisciplinaàProf } l Podem-se inserir tuplas que firam a DF:

– INSERT INTO R1 : (José, BD) – INSERT INTO R1 : (Fabio, BD) – INSERT INTO R2 : (José, 12345) – INSERT INTO R2 : (Fabio, 12345)

N°Estud

Disciplina

Prof

(18)

DEXL Lab

52

Ensino : 2 soluções

l Solução 1

– Ensino (N°Estud, Prof, Disciplina) – com a R.I.: um prof ensina apenas 1 disciplina

l Solução 2 – R1 (Prof, Disciplima) – R2 (Prof, N°Estud)

– com a R.I.: um estudante se inscreve em uma disciplina com um único professor

l 

Não existe solução ideal

A solução 1 é preferível

– menor número de junções – R.I. sobre uma relação

N°Estud

Disciplima

Prof

Fábio Porto

DEXL Lab

53

Algoritmo de decomposição

baseado em Heath

l Vários algoritmos para decompor relações segundo

as DFs

l Algoritmo 1 (Heath) – R (A1, A2, A3, … An) – while exists DF elementar :

Seja AiàAj uma DF

Sejam Ak,… Al os atributos de R que dependem diretamente de Ai (AiàAj, Ak, …Al)

Substituir R por R1 (Ai, Aj, Ak, … Al)

R2 (Ai, atributos de R diferentes de Aj, Ak, …Al) – Recomeçar o algoritmo para R1 e R2

Fábio Porto

DEXL Lab

Dependência multivalorada (DM)

l Definição :

Seja uma relação R (X, Y, Z) uma DF multivalorada

X --->> Y

se para todo o valor de X existe um conjunto de valores de Y cujos elementos são independentes de Z

l Propriedade :

Se DM X-->>Y e tambem X-->>Z então anota-se: X-->>Y|Z

(19)

Fábio Porto

DEXL Lab

55

Grafo de dependência de curso

nomC prof livre

Curse (nomeCurso, prof, livro)

L'identifiant de livre est (nomC, prof, livre)

Fábio Porto

DEXL Lab

56

4a forma normal (4FN)

l Semântica : A 4FN permite separar os fatos

multivalorados independentes que teriam sido reunidos em uma mesma relação

l Definição : R encontra-se em 4FN se está em 1a FN

e se todas as DFs ou DM de R tem como determinante um identificador de R

– OBS : 4FN implica FNBC

l Outra Definição: R está em 4FN se está em FNBC e

não contém DM

Fábio Porto

DEXL Lab

57

Decomposição segundo uma DM

l Teorema de Heath no 2

– Se R(X,Y,Z) contem aDM X-->>Y|Z temos que

R1 = π[X,Y]R et R2 = π[X,Z]R

é sem perda de informação.

l Exemplo : Curso (nomc, prof, livro) FNBC

seria decomposta em:

CursoProf (nomc, prof) 4FN CursoLivre (nomC, livro)4FN