Fundamentos de Banco de Dados

(1)

Fundamentos de

Banco de Dados

(2)

Normalização

• _Objetivo

– Apresentar uma abordagem de projeto de banco de dados, denominada de Normalização, a qual permite analisar a qualidade das relações, bem como elevar a sua qualidade.

• _{Principais tópicos}

– _Anomalias

– _{Tuplas espúrias}

– _{Abordagens de Projeto de Banco de Dados} – Dependências Funcionais

(3)

Normalização

• _{Principais tópicos}(Continuação)

– Formas Normais com base em Chaves Primárias

– _{Definição Geral de Formas Normais} – _{BCNF (Boyce-Codd Normal Form)} – _{Dependências Multivaloradas}

(4)

Abordagens de Projeto de BD

• _Top-down

– Iniciar com o agrupamento dos atributos obtidos a partir do projeto conceitual de mapeamento

– _{Isso é chamado de projeto por análise}

• _Bottom-up

– _{Considerar os relacionamentos entre atributos} – _{Construir as relações}

– _{Isso é chamado projeto pela síntese}

• _{Nossa Abordagem}

– _{Utilizar a abordagem Top-down para obter as relações} – _{Utilizar a abordagem Bottom-up para melhorar a}

(5)

Anomalias

• _{Cuidado com redundância de informação}

– _{Anomalias de Inserção:}

• Como inserir novo departamento sem que exista empregados?

• Inserir empregados é difícil quando informações de departamento devem ser inseridas corretamente.

– Anomalias de Remoção:

• O que acontece quando removemos CC? Perdemos o departamento 6!

– _{Anomalias de Alteração:}

• Se mudarmos o gerente do departamento 5, devemos mudá-lo em todas as tuplas com DNUMERO = 5.

NSS NOME DTANIV DNUMERO DNOME GERENTE

21 AA - 5 CV 91

22 BB - 5 CV 91

23 CC - 6 TS 93

24 DD - 7 OS 94

25 EE - 7 OS 94

(6)

Tuplas Espúrias

• _{Não quebre uma relação em relações que}

possam gerar tuplas espúrias

• _{A relação pode ser quebrada em}

• _{Quando fazemos o Join, obtemos NOVAS TUPLAS!}

DNUMERO NOME PNOME PLOCALIZAÇÃO 123 XX Compras São Paulo 123 XX Vendas Rio de Janeiro 124 YY Logística São Paulo

DNUMERO NOME PNOME 123 XX Compras 123 XX Vendas 124 YY Logística

DNUMERO PLOCALIZAÇÃO 123 São Paulo 123 Rio de Janeiro 124 São Paulo

DNUMERO NOME PNOME PLOCALIZAÇÃO 123 XX Compras São Paulo 123 XX Compras Rio de Janeiro 123 XX Vendas São Paulo 123 XX Vendas Rio de Janeiro 124 YY Logística São Paulo

Após o Join, o resultado não foi a relação original. Assim,

houve perda de informações. Conclui-se que houve uma decomposição com perdas. Após o Join, o resultado não foi a relação original. Assim,

(7)

Dependências Funcionais

• _{Dependências funcionais (DFs) são usadas}

para medir formalmente a qualidade do projeto relacional

• _{As DFs e chaves são usadas para definir}

formas normais de relações

• _{As DFs são restrições que são derivadas do}

significado dos atributos e do seus inter-relacionamentos

• Um conjunto de atributos X determina

funcionalmente um conjunto de atributos Y se o valor de X determinar um único valor Y

(8)

Dependências Funcionais

• _{X→ Y}

– _{Se duas tuplas tiverem o mesmo valor para X, elas devem}

ter o mesmo valor para Y. Ou seja:

– _{Se X→Y então, para quaisquer tuplas t1 e t2 de r(R):} – _{Se t1[X] = t2[X], então t1[Y] = t2[Y]}

• Se K é uma chave de R, então K determina funcionalmente todos os atributos de R

– Isso porque, nunca teremos duas tuplas distintas com t1[K]=t2[K]

• _Importante

– _{X→Y especifica uma restrição sobre todas as instâncias de R} – _{As DFs são derivadas das restrições do mundo real e não de}

(9)

Exemplos de

Restrições de DF

• _{O número do seguro social determina o nome}

do empregado

– _{NSS → ENOME}

• O número do projeto determina o nome do

projeto e a sua localização

– _{PNUMERO → { PNOME, PLOCALIZACAO }}

• _{O nss de empregado e o número do projeto}

determinam as horas semanais que o empregado trabalha no projeto

(10)

Regras de

Inferência para DFs

• _{Regras de inferência de Armstrong:}

– _{RI1. (Reflexiva) Se Y ⊆ X (é subconjunto de), então X→Y}

– _{(Isso também é válido quando X=Y)} – _{RI2. (Aumentativa) Se X→Y, então XZ→YZ} – _{(Notação: XZ significa X U Z)}

– RI3. (Transitiva) Se X→Y e Y→Z, então X→Z

• _{RI1, RI2 e RI3 formam um conjunto completo}

(11)

Regras de

Inferência para DFs

• _{Algumas regras de inferência úteis:}

– (Decomposição) Se X→YZ, então X→Y e X→Z

– _{(Aditiva) Se X→Y e X→Z, então X→YZ}

– _{(Pseudotransitiva) Se X→Y e WY→Z, então WX→Z}

• _{As três regras de inferência acima, bem como}

quaisquer outras regras de inferência, podem ser deduzidas a partir de RI1, RI2 e RI3

(12)

Formas Normais com

base em Chaves Primárias

• _{Normalização de Relações}

• _{Uso prático de Formas Normais}

• Definições de Chaves e de Atributos que

participam de Chaves

• _{Primeira Forma Normal}

• Segunda Forma Normal

(13)

Normalização de Relações

• Normalização

– Processo de decompor relações “ruins” dividindo seus

atributos em relações menores e “melhores”

• _{Forma Normal}

– _{Indica o nível de qualidade de uma relação}

• _1FN

– _{Definição de relação. Atributos atômicos (indivisíveis).}

• _{2FN, 3FN, BCNF}

– _{Baseiam-se em chaves e DFs de uma relação esquema}

• _{4FN e 5FN}

(14)

Uso Prático das

Formas Normais

• _{Na prática, a normalização é realizada para}

obter projetos de alta qualidade

• Os projetistas de bancos de dados não

precisam normalizar na maior forma normal possível.

• _{Desnormalização}

– _{Processo de armazenar junções de relações de}

(15)

Revisão

• _{Uma superchave, S, de uma relação esquema}

– _{R = {A1, A2, ...., An}}

• é um conjunto de atributos, subconjunto de R, com a propriedade de que t1[S] ≠ t2[S] para qualquer extensão r(R)

• _{Uma superchave, K, é uma chave se K é uma}

superchave mínima

• _{Se uma relação esquema tiver mais de uma chave,}

(16)

Atributo Primo

• _{Um atributo primo (ou primário) é membro de}

alguma chave-candidata

• _{Um atributo não-primo é um atributo que não}

é primo – isto é, não é membro de qualquer chave-candidata

NSS PNRO HORAS

TRABALHA_EM

(17)

(18)

Primeira Forma Normal

• _{Proíbe que relações tenham}

– Atributos compostos

– _{Atributos multivalorados} – _{Relações aninhadas}

Ou seja

– _{Permite apenas atributos que sejam atômicos}

• _{Considerado como parte da definição de}

(19)

Normalização na 1 FN

ENOME NSS Telefones Joaquim 305 555-444

Katarina 381 555-333 555-101 Daví 422 Carlos 489 555-376 555-222 555-376 Bárbara 533 555-101

EMPREGADO ENOME NSS Joaquim 305 Katarina 381 Daví 422 Carlos 489 Bárbara 533

EMPREGADO NSS₃₀₅ NÚMERO_555-444

(20)

Normalização de Relações com Atributos Compostos para a 1 FN

ENOME NSS PROJETOS PNUMERO HORAS Joaquim 305 1 32 Joaquim 305 2 8

Daví 422 3 40

Carlos 489 1 20 Carlos 489 3 20

EMPREGADO_PROJETO

ENOME NSS Joaquim 305 Daví 422 Carlos 489

NSS PNUMERO HORAS

305 1 32

305 2 8

422 3 40

489 1 20

489 3 20

EMPREGADO

(21)

(22)

Segunda Forma Normal

• Para entender a 2FN precisamos entender:

– Dependência Funcional

– _{Chave-primária}

– _{Atributo Não-Primo}

– _{Dependência funcional total}

• Uma DF, YZ, onde a remoção de qualquer atributo de Y invalida a DF. Exemplos:

– { NSS, PNUMERO }  HORAS é dependente totalmente de

– { NSS, PNUMERO }, uma vez que

– NSS não determina HORAS e nem PNUMERO determina HORAS

– { NSS, PNUMERO }  ENOME não é dependente totalmente de

– { NSS, PNUMERO }; ENOME é dependente parcialmente de

(23)

Segunda Forma Normal

• _{Uma relação esquema R está na 2FN se}

estiver na 1FN e todos os atributos não-primos A de R forem totalmente dependentes da chave-primária

• _{R pode ser decomposto em relações que}

(24)

Segunda Forma Normal

• _{Visa a diminuição da redundância e o}

desagrupamento de informações. Na 2FN, uma tabela representa um quantidade menor de entidades.

– Uma tabela está na 2FN se estiver na 1FN

– Todo atributo não-chave for determinado por todos os campos da chave primária. É preciso eliminar as dependências funcionais parciais.

(25)

Normalização

para a 2FN e 3FN

NSS PNUMERO HORAS ENOME PNOME PLOCALIZACAO

EMPREGADO_PROJETO

DF1 DF2 DF3

NSS PNUMERO HORAS NSS ENOME PNUMERO PNOME PLOCALIZACAO

EMPREGADO_PROJETO EMPREGADO PROJETO

Não-Primo

Depende totalmente da chave-primária

(26)

(27)

Terceira Forma Normal

• _{Para entender a 3FN precisamos entender:}

– 2FN

– _{Atributo Não-Primo}

– _{Dependência funcional transitiva}

(28)

Terceira Forma Normal

• _{Uma relação esquema R está na 3FN se ela estiver}

na 2FN e nenhum atributo não-primo, A, for transitivamente dependente da chave-primária

• _{R pode ser decomposto em relações que estejam na}

3FN via o processo de normalização

• _NOTA:

– _{Em X→Y e Y→Z, sendo X a chave-primária, pode ser}

considerado um problema se, e somente se, Y não for uma chave-candidata. Quando Y é uma chave-candidata, não existe problema com a dependência transitiva

– _{Por exemplo, considere EMP (NSS, Emp#, Salario ).}

(29)

Terceira Forma Normal

• _{Mesmo objetivo da 2FN}

– Identificar e eliminar a transitividade

– Deve estar na 2FN

– Todo atributo não chave deve ser determinado de forma não transitiva pela chave primária ou, todo atributo não chave deve ser determinado apenas pela chave primária.

(30)

Normalização para a 2FN e 3FN

ENOME NSS DTNASC ENDERECO DNUMERO DNOME PLOCALIZACAO

EMPREGADO_PROJETO

ENOME NSS DTNASC ENDERECO DNUMERO

EMPREGADO

DNUMERO DNOME PLOCALIZACAO

(31)

Definição Geral de

Formas Normais

• _As _definições _anteriores _consideravam

somente a chave-primária

• As próximas definições levarão em

(32)

Definição Geral de

Formas Normais

• Redefinição da 2FN:

– Uma relação esquema R está na 2FN se todos os

atributos não-primos, A, forem totalmente dependentes de todas as chaves de R

• _Teste:

– Verifique que EMP_PROJ não está na 2FN

(33)

(34)

BCNF

(Boyce-Codd Normal Form)

• Definição de BCNF:

– Uma relação esquema R está na BCNF se, sempre que

houver uma DF X→A em R, então X é uma superchave de R

• _{Cada FN engloba a FN anterior:}

– Toda relação em 2FN está na 1FN

– _{Toda relação em 3FN está na 2FN}

– _{Toda relação em BCNF está na 3FN}

• _{Existem relações que estão na 3FN mas não em}

BCNF

• _{A meta é alcançar a BCNF ou 3FN em todas as}

(35)

Boyce-Codd normal form

ESTUDANDE CURSO INSTRUTOR

Nair Banco de dados Marcos

Silas Banco de dados Nico

Silas Sistemas

Operacionais Altair

Silas Teoria Saulo

Wilson Banco de Dados Marcos

Wilson Sistemas

Operacionais Álvaro Wellington Banco de Dados Carlos

Zenaide Banco de Dados Nico

df1 df2

Relação em 3FN mas não em BCNF

Uma relação esquema R está na BCNF se, sempre que houver uma DF X→A, então:

• X é uma superchave de R

Uma relação esquema R está na BCNF se, sempre que houver uma DF X→A, então:

Uma relação esquema R está na 3FN se, sempre que

houver uma DF X→A, então:

ou

• A é atributo primo de R.

Uma relação esquema R está na 3FN se, sempre que

houver uma DF X→A, então:

ou

• A é atributo primo de R.

(36)

Alcançando a BCFN

pela Decomposição

• Existem duas DF em relação:

– df1: { estudante, curso } → instrutor

– _{df2: instrutor → curso}

– _{Se a relação tivesse apenas df1, a relação estaria na}

BCNF.

– _{Mas em df2, instrutor não é uma superchave, e,}

portanto, viola a BCNF, mas não a 3FN, pois curso é primo.

• _{Uma relação que não esteja na BCNF deve ser}

(37)

Alcançando a BCFN

pela Decomposição

• _{Três possíveis decomposições para relação:}

– R1(estudante, instrutor) e R2(estudante, curso)

– _{R1(curso, instrutor)} _{e R2(curso, estudante)}

– _{R1(instrutor, curso)} _{e R2(instrutor, estudante)}

• _{Todas as três decomposições perdem a df1.}

– Temos que conviver com este sacrifício, mas não podemos sacrificar a propriedade não-aditiva após a decomposição.

• _{Das três, apenas a terceira decomposição não}

(38)

Alcançando a BCFN

pela Decomposição

INSTRUTOR CURSO Marcos Banco de dados

Nico Banco de dados

Altair Sistemas Operacionais Saulo Teoria

Álvaro Sistemas Operacionais Carlos Banco de Dados INSTRUTOR ESTUDANDE Marcos Nair Nico Silas Altair Silas Saulo Silas Marcos Wilson Álvaro Wilson Carlos Wellington Nico Zenaide

ESTUDANDE CURSO INSTRUTOR Nair Banco de dados Marcos Silas Banco de dados Nico Silas Sistemas Operacionais Altair Silas Teoria Saulo Wilson Banco de Dados Marcos Wilson Sistemas Operacionais Álvaro Wellington Banco de Dados Carlos Zenaide Banco de Dados Nico

R1 R2

X (JOIN)

Relação original: R

Note que para as outras possíveis decomposições, isso não

acontece.

Note que para as outras possíveis decomposições, isso não

(39)

Decomposição sem perdas

• _{A decomposição de R em X e Y é sem perdas}

se e somente se, pelo menos uma das duas DF for válida

– _{X ∩ Y → X ou} – _{X ∩ Y → Y}

• Caso especial

– _{Se U → V, então a decomposição de R em UV e R –}

V é sem perdas.

Verifique que a

decomposição de R satisfaz esta condição!

Verifique que a

(40)

Algoritmo de

Decomposição BCNF

• _{Considere uma relação R e suas DFs}

associadas.

– _{Se X → Y violar a FNBC, decomponha R em XY e R}

− Y.

• _Aplicando _esta _idéia _{repetidamente,}

obteremos uma decomposição sem perdas de R em uma coleção de relações na BCNF.

• Em geral, mais de uma DF pode violar a

(41)

(42)

MVD e 4FN

• As dependências multivaloradas são

conseqüência da 1FN, a qual não aceita atributos multivalorados.

– _{Considere, por exemplo, a relação ACERVO abaixo:}

– _{Relação Normalizada para BCNF (note que não há DFs)}

ISBN AUTOR CÓPIAS

85-7323-169-6 Dantas 1, 2 0-13031-995-3 Molina, Ulman, Widom 1, 2

ISBN AUTOR CÓPIAS 85-7323-169-6 Dantas 1 85-7323-169-6 Dantas 2 0-13031-995-3 Molina 1 0-13031-995-3 Molina 2 0-13031-995-3 Ulman 1 0-13031-995-3 Ulman 2 0-13031-995-3 Widom 1 0-13031-995-3 Widom 2

Porque existem dependências multivaloradas!

ISBN → AUTOR ISBN → CÓPIAS

Porque existem dependências multivaloradas!

ISBN → AUTOR ISBN → CÓPIAS

Mas ainda temos

redundâncias por que?

Mas ainda temos

(43)

MVD e 4FN

• _{Sempre que X → Y ocorrer, dizemos que X}

multidetermina Y.

• Devido a semetria da definição, sempre que X

→ Y ocorrer em R, também ocorre X → Z.

• Por isso, X → Y implica X → Z; por isso, às vezes é escrito como X→Y | Z.

• _{Então, na relação ACERVO do exemplo}

MVD e 4FN

• _{Elimina redundâncias provocadas pelas}

dependências multivaloradas (MVD).

• Uma relação está na 4FN se não contiver

mais de uma MVD.

– _{Mas porque é tão ruim ter uma tabela com}

múltiplas dependências multivaloradas?

– _{Em ACERVO}

• _{Para inserir mais uma cópia do ISBN 0-13031-995-3, será}

necessário inserir 3 tuplas, uma para cada autor. ISBN AUTOR CÓPIAS

85-7323-169-6 Dantas 1 85-7323-169-6 Dantas 2 0-13031-995-3 Molina 1 0-13031-995-3 Molina 2 0-13031-995-3 Ulman 1 0-13031-995-3 Ulman 2 0-13031-995-3 Widom 1 0-13031-995-3 Widom 2

(45)

MVD e 4FN

• A solução é decompor a relação ACERVO em

duas

• _{A MVD desejável é aquele cujo determinante é}

superchave da relação

ISBN AUTOR 85-7323-169-6 Dantas 0-13031-995-3 Molina 0-13031-995-3 Ulman 0-13031-995-3 Widom ISBN CÓPIAS 85-7323-169-6 1 85-7323-169-6 2 0-13031-995-3 1 0-13031-995-3 2

De acordo com as MVD

ISBN → AUTOR

ISBN → CÓPIAS

De acordo com as MVD

ISBN → AUTOR

ISBN → CÓPIAS

Caso especial

Se R tiver as seguintes MVD

A→ B e B → C

Neste caso, R estará na 4FN se, e somente se, B e C são dependentes um do outro.

Caso especial

Se R tiver as seguintes MVD

A→ B e B → C

(46)

5FN e

Dependência de Junção

• _{Algumas vezes uma relação não pode ser}

decomposta sem perdas em duas relações, mas pode ser decomposta em três ou mais.

• _{A 5FN capta a idéia de que uma relação}

esquema deve ter alguma decomposição sem perda (dependência de junção).

(47)

5FN e

Dependência de Junção

• _{Um pequeno exemplo}

AGENTE EMPRESA PRODUTO Smith Ford Carro Smith Ford Caminhão

Smith GM Carro

Smith GM Caminhão Jones Ford Carro

AGENTES representam EMPRESAS EMPRESAS fazem PRODUTOS

AGENTES vendem PRODUTOS Regra:

Se um AGENTE vende um certo PRODUTO e este AGENTE

representa uma EMPRESA que faz este PRODUTO

então

(48)

5FN e

Dependência de Junção

• _{Um pequeno exemplo}

AGENTE EMPRESA Smith Ford Smith GM Jones Ford AGENTE PRODUTO Smith Carro Smith Caminhão Jones Carro EMPRESA PRODUTO Ford Carro Ford Caminhão GM Carro GM Caminhão

AGENTE EMPRESA PRODUTO Smith Ford Carro Smith Ford Caminhão

Smith GM Carro

Smith GM Caminhão Jones Ford Carro

AEP

AE EP AP

(49)

5FN e

Dependência de Junção

• _{Dependência de Junção}

– Uma relação R satisfaz a dependência de junção

• JD (R1, R2, ..., Rn) se

– R = R1* R2 * ...* Rn

• onde R1, R2, ..., Rn são subconjuntos dos atributos de R.

– _{Note que uma dependência multivalorada é um}

(50)

5FN e

Dependência de Junção

• _{Uma relação R está na 5FN se, e somente se,}

ela estiver na 4FN e todas as suas

dependências de junção forem determinadas pelas chaves candidatas.

• _{A descoberta de DJs em bancos de dados}

reais com centenas de atributos é

praticamente impossível. Isso poderá ser feito apenas contando com um grande grau de

intuição sobre os dados por parte do

(51)

Outras NFs

• _{Existem outras formas normais, porém elas}

(52)

Questões de Estudo

• _{Dado o DER de uma locadora de vídeo}

(próximo slide), e o mapeamento realizado

para o esquema do BD Relacional, verifique a qualidade das relações obtidas (qual forma

(53)

nome código rg cpf endereço telefone residencial salário rg código data nasc idade LOCAÇÃO nome

Sexo data nasc tipo da relação

supervisorsupervisionado 1 N logradouro cidade número endereço estado cep complemento saldo bônus telefones REALIZA 1 N FILME CONTÉM M N DIRETOR DIRIGE 1 N EFETUA N 1 SUPERVISÃO POSSUI 1 CLIENTE cpf data total desconto código nome código tipo tipo nome CONTATO CONTATO-FORNECEDOR 1 1 COMPRA FUNCIONÁRIO FORNECEDOR código nome nome fantasia cnpj descrição

data de cadastro DEPENDENTE N N M O telefones nome e-mail TLF

código da etiqueta

data do pedido

(54)