Bancos de Dados

Bancos de Dados
Mestrado em Engenharia de Computação
área de concentração Geomática
UERJ
© Oscar Luiz Monteiro de Farias
1
Dependências Funcionais
e
Normalização
para Bancos de Dados Relacionais
UERJ
© Oscar Luiz Monteiro de Farias
2
Projeto de Banco de Dados...
Objetivo: gerar um conjunto de esquemas de
relações que permita:
• armazenar informações sem redundâncias
• recuperar informações com facilidade (queries)
UERJ
© Oscar Luiz Monteiro de Farias
3
Projeto de Banco de Dados...
Técnicas aplicadas até o momento:
• Senso comum na construção das relações (tabelas)
• Mapeamento de esquemas baseados no modelo ER em
esquemas do modelo relacional (também se usa o senso
comum na construção do modelo ER)
• Problema: Não possuímos medida formal alguma, de
que um esquema de relações seja melhor que outro.
Estamos completamente dependentes da intuição do
projetista.
UERJ
© Oscar Luiz Monteiro de Farias
4
Projeto de Banco de Dados...
• Há pelo menos dois níveis - conceitual e lógico - em
relação aos quais pode-se avaliar a qualidade de um
esquema.
• O nível conceitual refere-se a como os usuários
interpretam os esquemas das relações e o significado de
seus atributos.
• O nível lógico/físico preocupa-se com o representação
das tuplas, de como elas serão armazenadas e
atualizadas. Aplica-se somente às relações bases.
UERJ
© Oscar Luiz Monteiro de Farias
5
Projeto de Banco de Dados...
• As formas normais são “ferramentas” utilizadas no
projeto de um banco de dados relacional que ajudam a
eliminar uma série de efeitos não desejados, fruto da
escolha inadequada de determinados esquemas para as
relações.
• Uma relação é dita estar em forma normal se ela
satisfizer a um conjunto específico de restrições.
UERJ
© Oscar Luiz Monteiro de Farias
6
Formas Normais
Universo das Relações (normalizadas e não-normalizadas)
Relações 1NF (relações normalizadas)
Relações 2NF
Relações 3NF
Relações BCNF
Relações 4NF
Relações PJ/NF (5NF)
UERJ
© Oscar Luiz Monteiro de Farias
7
Diretrizes Informais para Projetos de BDs...
4 medidas informais de qualidade para projeto de
schemas relacionais:
Semântica dos atributos
Redução de valores redundantes nas tuplas
Redução de valores nulos nas tuplas
Evitar a geração de tuplas espúrias
UERJ
© Oscar Luiz Monteiro de Farias
8
 Semântica dos Atributos das Relações...
• Uma relação pode ser interpretada como um conjunto de
fatos ou declarações.
• O significado ou semântica especifica como interpretar
os valores dos atributos armazenados na tupla, como
estes valores estão relacionados uns aos outros.
• Quanto mais fácil a interpretação da semântica da
relação, melhor é o projeto do esquema da relação!
UERJ
© Oscar Luiz Monteiro de Farias
9
 Semântica dos Atributos das Relações - ex.
UERJ
© Oscar Luiz Monteiro de Farias
10
Projeto de Banco de Dados...
Diretriz Informal no 1:
• Projetar esquemas de relações cujos significados sejam
facilmente explicáveis.
• Não incluir atributos de diferentes tipos de entidades
(objetos) em uma mesma relação.
• Intuitivamente, se o esquema de uma relação
corresponde a um tipo de entidade ou a um tipo de
relacionamento o seu significado tende a ser claro.
UERJ
© Oscar Luiz Monteiro de Farias
11
 Informações redundantes em tuplas e
anomalias nas modificações...
• Um objetivo do projeto de um esquema é minimizar o espaço de
armazenamento das relações bases (arquivos).
• Compare o espaço ocupado pelas duas relações bases
EMPLOYEE_VS(ENAME,SSN,BDATE,ADDRESS,DNUMBER)
e DEPARTMENT_VS(DNAME,DNUMBER,DMGRSSN)
relativamente àquele ocupado pela relação EMP_DEPT, resultante
da aplicação da operação NATURAL JOIN nas duas primeiras
relações.
• Outro problema que surge é o das anomalias:
– inserção
– exclusão
– modificação
UERJ
© Oscar Luiz Monteiro de Farias
12
 Informações redundantes em tuplas e
anomalias nas modificações...
UERJ
© Oscar Luiz Monteiro de Farias
13
 Informações redundantes em tuplas e
anomalias nas modificações...
UERJ
© Oscar Luiz Monteiro de Farias
14
 Informações redundantes em tuplas e
anomalias nas modificações...
Redundância:
• Os valores dos atributos pertencentes a cada depto são repetidas
para cada empregado que trabalha em um depto.
Anomalias na inserção:
• Na inserção de um novo empregado na relação, os valores dos
atributos correspondentes ao depto tem que ser consistente com
os valores já assinalados para as outras tuplas.
• É muito difícil inserir um novo depto que ainda não possua
empregados (A alternativa seria entrar com valores nulos para os
outros campos da tupla, porém Ssn é um campo chave e,
obrigatoriamente deve ser não nulo).
UERJ
© Oscar Luiz Monteiro de Farias
15
 Informações redundantes em tuplas e
anomalias nas modificações...
Anomalias na exclusão:
• Acontece quando se exclui uma tupla, correspondente a um
empregado, de EMP_DEPT e esta é a última tupla de empregado
trabalhando em um dado depto. Com isto as informações
concernentes ao depto se perdem.
Anomalias na alteração:
• na relação EMP_DEPT, se alterarmos o valor de um dos atributos
de um dado depto, digamos o gerente do depto de no 5, deveremos
atualizar as tuplas de todos os empregados que trabalham naquele
departamento, para evitar inconsistências na base de dados.
UERJ
© Oscar Luiz Monteiro de Farias
16
Projeto de Banco de Dados...
Diretriz Informal no 2:
• Projetar os esquemas das relações bases, de tal forma
que anomalias na inserção, na alteração e na exclusão
não possam ocorrer naquelas relações.
• Se alguma anomalia estiver presente, identifique-a
claramente, de modo que os programas responsáveis
pela atualização do banco de dados realizem as
operações corretamente (sem perder informação ou sem
gerar inconsistências).
UERJ
© Oscar Luiz Monteiro de Farias
17
Projeto de Banco de Dados...
• Eventualmente pode-se violar as diretrizes de projeto,
de modo a se aumentar a performance de determinadas
consultas que sejam freqüentes.
• Em geral é aconselhável o uso de relações bases isentas
de anomalias e a especificação de visões que incluam
JOINS com a finalidade de juntar em uma mesma tupla
atributos freqüentemente referenciados em consultas.
UERJ
© Oscar Luiz Monteiro de Farias
18
 Redução de valores nulos nas tuplas...
•
Combater as relações (fat relations) que agregam atributos
correspondentes a diversas entidades (objetos).
• Vários problemas podem ocorrer:
– se os atributos não se aplicam a muitas das tuplas da relação
pode-se gerar muitos valores nulos;
– desperdício de espaço para armazenamento;
– dificuldade quanto ao significado dos atributos;
– dificuldade para se especificar operações de JOIN no nível
conceitual;
– como considerar os nulos nas diferentes operações de
agregação (COUNT, SUM, etc...)
UERJ
© Oscar Luiz Monteiro de Farias
19
Projeto de Banco de Dados...
Diretriz Informal no 3:
• Tanto quanto possível evite colocar atributos nas
relações bases cujos valores possam ser nulos.
• No caso dos valores nulos serem inevitáveis, tenha
certeza de que eles se aplicam somente em condições
excepcionais e não se aplicam à maioria das tuplas da
relação.
UERJ
© Oscar Luiz Monteiro de Farias
20
 Tuplas Espúrias...
Representação alternativa para EMP_PROJ, com 2 relações bases
UERJ
© Oscar Luiz Monteiro de Farias
21
 Tuplas Espúrias...
EMP_PROJ1 * EMP_LOCS gera várias tuplas espúrias
UERJ
© Oscar Luiz Monteiro de Farias
22
 Tuplas Espúrias...
• Um projeto de esquema fundamentado nas relações bases
EMP_PROJ1 e EMP_LOCS é particularmente ruim, pois não
podemos recuperar a informação que originalmente estava em
EMP_PROJ.
• Obtemos muitas tuplas espúrias, que representam informações
erradas, que não são válidas.
• A decomposição de EMP_PROJ em EMP_LOCS e EMP_PROJ1 é
ruim, porque quando aplicamos o NATURAL JOIN, a fim de obter a
informação original, é gerada informação incorreta.
• Motivo: PLOCATION é o atributo que relaciona EMP_LOCS e
EMP_PROJ1, todavia PLOCATION não é uma chave primária nem
uma chave estrangeira seja em EMP_LOCS ou em EMP_PROJ1.
UERJ
© Oscar Luiz Monteiro de Farias
23
Projeto de Banco de Dados...
Diretriz Informal no 4:
• Projetar esquemas de relações tal que se possa aplicar
NATURAL JOIN em atributos que sejam chaves
primárias ou chaves estrangeiras, de modo a garantir
que tuplas espúrias não sejam geradas.
• Posteriormente será apresentada uma condição formal
(nonadditive or lossless join property) que garantirá que
certos JOINS não produzirão tuplas espúrias.
UERJ
© Oscar Luiz Monteiro de Farias
24
Dependência Funcional...
• Dependência Funcional (DF): é uma restrição entre dois
conjuntos de atributos de um banco de dados.
• Esquema de Relação Universal:
R = {A1, A2, ..., An}
• Definição: Uma dependência funcional, denotada por X  Y,
entre dois conjuntos de atributos X e Y, que são subconjuntos de
R especifica uma restrição nas tuplas possíveis de formar uma
relação de instância r de R. A restrição estabelece que, para
quaisquer duas tuplas t1 e t2 em r, tal que t1[X] = t2[X], então
deve-se ter obrigatoriamente t1[Y] = t2[Y].
UERJ
© Oscar Luiz Monteiro de Farias
25
Dependência Funcional...
• Esta definição de DF significa que os valores do componente Y da
tupla em r dependem, ou são determinados, pelos valores do
componente X.
• Pode-se dizer que os valores do componente X da tupla
univocamente (funcionalmente) determinam os valores do
componente Y.
• Y é funcionalmente dependente de X.
• Se X é uma chave candidata de R, então X  Y, para qualquer
subconjunto de atributos Y de R.
• X  Y em R não implica em que Y  X em R.
• Uma DF é uma propriedade da semântica (significado) dos
atributos.
UERJ
© Oscar Luiz Monteiro de Farias
26
Dependência Funcional...
• As extensões da relação r(R) que satisfazem as restrições de
dependência funcional são chamadas extensões válidas (legal
extensions) ou estados válidos da relação (legal relation states).
• O principal uso das dependências funcionais é na descrição mais
aprofundada do esquema da relação R, através da especificação
de restrições sobre os seus atributos e que sejam válidas por todo
o tempo (i.e. em todas as extensões).
• No esquema da relação EMP_PROJ tem-se as seguintes DFs:
i) Ssn  Ename; ii) Pnumber  {Pname, Plocation}; iii) {Ssn,
Pnumber}  Hours.
UERJ
© Oscar Luiz Monteiro de Farias
27
Dependência Funcional...
• X x
Y, denota que Y não é funcionalmente dependente de X.
No exemplo abaixo TEACHER x
COURSE x
UERJ
COURSE e
TEXT
© Oscar Luiz Monteiro de Farias
28
Regras de Inferência para FDs...
• Denota-se por F o conjunto das DFs que são especificadas no
esquema relacional R.
• Uma DF X  Y é inferida (deduzida) de um conjunto de
dependências funcionais F especificada em R, se X  Y vale para
todo estado de relação r que é uma extensão válida (legal
extension) de R.
• O conjunto de todas as DFs que podem ser inferidas de F, denotase por F+ e chama-se closure de F.
• Regras de inferência podem ser usadas para inferir novas DFs a
partir de um conjunto dado de DFs.
• Usa-se a notação F |= X  Y, para denotar que a DF X  Y é
inferida do conjunto de DFs F.
UERJ
© Oscar Luiz Monteiro de Farias
29
Regras de Inferência para FDs...
• Suponha as seguintes regras de inferência associadas ao esquema
da relação EMP_DEPT:
F = {SSN  {ENAME, BDATE, ADDRESS, DNUMBER},
DNUMBER  {DNAME, DMGRSSN}}
Pode-se daí inferir que:
SSN  {DNAME, DMGRSSN}
SSN  SSN
DNUMBER  DNAME
UERJ
© Oscar Luiz Monteiro de Farias
30
Regras de Inferência para FDs...
(ri1 - Regra Reflexiva) se X  Y, então X  Y.
(ri2 - Regra Augmentation) {X  Y} |= XZ  YZ.
(ri3 - Regra Transitiva) {X  Y, Y  Z} |= X  Z.
(ri4 - Regra de Decomposição ou Projetiva) {X  YZ} |= X  Y.
(ri5 - Regra Aditiva ou União) {X  Y, Y  Z} |= X  YZ.
(ri6 - Regra Pseudotransitiva) {X  Y, WY  Z} |= WX  Z.
{ri1, ri2, ri3}  regras de inferência de Armstrong
Exemplos:
• X {A1, A2, ..., An}  {X A1, X A2, ..., X An} --- ri4
• {X A1, X A2, ..., X An}  {X A1, A2, ..., An} --- ri5
•
•
•
•
•
•
UERJ
© Oscar Luiz Monteiro de Farias
31
Regras de Inferência para FDs...
• Prova de ri1:
Supõe-se que X  Y e que duas tuplas t1 e t2 existam em alguma
relação de instância r de R, tal que t1[X] = t2[X]. Então t1[Y] =
t2[Y], porque X  Y. Portanto X  Y deve valer em r.
• Prova de ri2 (por contradição):
Assuma que X  Y é válida na relação de instância r de R, mas
que XZ  YZ não é válida. Então devem existir duas tuplas t1 e t2
em r, tal que: i) t1[X] = t2[X], ii) t1[Y] = t2[Y], iii) t1[XZ] = t2[XZ]
e iv) t1[YZ]  t2[YZ]. Isto não é possível porque de “i” e “iii”
deduz-se v) t1[Z] = t2[Z], e de “ii” e “v” deduz-se vi) t1[YZ] =
t2[YZ], o que contradiz “iv”.
UERJ
© Oscar Luiz Monteiro de Farias
32
Regras de Inferência para FDs...
• Prova de ri3:
Assuma que i) X  Y e ii) Y  Z são ambas válidas na relação r.
Então para quaisquer duas tuplas t1 e t2 em r, tal que t1[X] = t2[X],
deve-se ter também iii) t1[Y] = t2[Y] (a partir da premissa “i”) e
portanto deve-se ter iv) t1[Z] = t2[Z] (a partir de “iii” e “ii”).
Portanto X Z deve ser válida em r.
• Prova de ri4:
1. X  YZ (dado)
2. YZ  Y (usando ri1 e sabendo-se que YZ  Y)
3. X  Y (usando ri3 em “1” e “2”).
UERJ
© Oscar Luiz Monteiro de Farias
33
Regras de Inferência para FDs...
• Prova de ri5:
1. X  Y (dado)
2. X  Z (dado)
3. X  XY (usando-se ri2 em “1”, aumentando com X e notando
que XX = X)
4. XY  YZ (usando ri2 em “2”, aumentando com Y)
5. X  YZ (aplicando ri3 em “3” e “4”)
UERJ
© Oscar Luiz Monteiro de Farias
34
Regras de Inferência para FDs...
• Prova de ri6:
1. X  Y (dado)
2. WY  Z (dado)
3. WX  WY (usando-se ri2 em “1”, aumentando com W)
4. WX  Z (usando ri3 em “3” e “2”)
UERJ
© Oscar Luiz Monteiro de Farias
35
Regras de Inferência para FDs...
• As regras de inferência ri1, ri2 e ri3 são ditas sound e complete.
• Sound - significa que, dado um conjunto de DFs F, especificadas
em um esquema de relação R, qualquer DF que se puder inferir de
F pelo uso das regras ri1, ri2 e ri3 será válida em qualquer estado
de relação r de R que satisfaça as dependências em F.
• Complete - significa que, usando-se ri1, ri2 e ri3 repetidamente
para inferir dfs de F, até que não se possa gerar mais dfs, resulta
no conjunto completo (F+ = closure de F) de todas as possiveis
dfs que podem ser inferidas a partir de F.
UERJ
© Oscar Luiz Monteiro de Farias
36
Regras de Inferência para FDs...
• Uma forma sistemática de determinar estas dfs é primeiro determinar cada
conjunto de atributos X que aparece no lado esquerdo de alguma df em F e,
então, usando es regras de inferência de Armstrong, determinar o conjunto de
todos os atributos que são funcionalmente dependentes de X. Este conjunto,
X+, é chamado fechamento (closure) de X sob F.
• Algoritmo para determinar X+, fechamento (closure) de X sob F.
X+:= X;
repeat
old X+:= X+;
for each fd Y  Z in F do
if Y X then X+ := X+ U Z;
until (old X+:= X+);
UERJ
© Oscar Luiz Monteiro de Farias
37
Equivalência de Conjuntos de DFs
• Um conjunto de DFs E é coberto (covered) por um conjunto de
DFs F, ou alternativamente diz-se que F cobre E, se toda DF em
E pode ser inferida a partir de F.
• Dois conjuntos E e F de DFs são equivalentes se E+=F+.
• Pode-se determinar se F cobre E, calculando-se X+ relativamente
a F, para cada FD X  Y em E, e então, verificando se X+ inclui
os atributos em Y. Se isto acontecer para toda FD em E, então F
cobre E.
UERJ
© Oscar Luiz Monteiro de Farias
38
Conjunto Mínimo de DFs
• Um conjunto de DFs é mínimo (minimal), se satisfaz às seguintes
condições:
 Toda dependência em F possui um único atributo em seu lado
direito.
 Não se pode remover qualquer dependência de F e ainda se ter um
conjunto de dependências que seja equivalente a F.
 Não se pode substituir qualquer dependência X  A em F com a
dependência Y  A, onde Y é um subconjunto próprio de X e
ainda se ter um conjunto de DFs que seja equivalente a F.
• Pode-se pensar em um conjunto mínimo de dfs como sendo um
conjunto de dfs em forma padrão ou canônica, em que não haja
redundâncias.
UERJ
© Oscar Luiz Monteiro de Farias
39
Formas Normais...
• Inicialmente Codd propôs três formas normais que são chamadas
1NF, 2NF e 3NF.
• Posteriormente uma definição mais rigorosa de 3NF foi proposta
por Boyce e Codd, resultando na forma normal de Boyce e Codd.
• Todas estas formas normais (1NF, 2NF, 3NF e a FN de Boyce e
Codd) são baseadas no conceito de DFs entre os atributos de uma
relação.
• A 4FN é baseada no conceito de dependências multi-valoradas.
• A 5 NF, no conceito de dependências JOIN.
UERJ
© Oscar Luiz Monteiro de Farias
40
Formas Normais...
• O processo de Normalização de Dados pode ser visto como a
decomposição, através do particionamento de atributos, de
esquemas de relação indesejáveis em esquemas de relação que
possuam algumas propriedades desejadas.
• Um dos objetivos do processo de normalização é evitar as
anomalias de inserção, exclusão e atualização.
• As formas normais, consideradas isoladamente, não garantem um
bom projeto de Banco de Dados.
UERJ
© Oscar Luiz Monteiro de Farias
41
Formas Normais...
O processo de normalização, através da decomposição deve
também confirmar a existência de propriedades adicionais que os
esquemas das relações, considerados em conjunto, devem possuir:
• A propriedade não-aditiva ou JOIN sem perda (lossless join), que
garante que tuplas espúrias não serão geradas, quando de um
JOIN.
• A propriedade de preservação de dependência, que assegura que
todas as DFs estão representadas em alguma das relações
individuais resultantes.
• Recordação de conceitos: super-chave, chave, chave candidata,
chave primária.
UERJ
© Oscar Luiz Monteiro de Farias
42
Definições
• Super-chave - conjunto de um ou mais atributos que, tomados coletivamente,
nos permitem identificar de maneira unívoca uma entidade em um conjunto de
entidades (tipo-entidade).
• Uma chave K (minimal superkey) de um esquema de relação R é uma
superchave de R com a propriedade adicional de que, a remoção de qualquer
atributo A de K resulta em um conjunto de atributos K´ que não é uma
superchave de R.
• Chave candidata - é uma das chaves de um esquema de relação R.
• Chave primária - é uma chave candidata escolhida pelo projetista do BD para a
identificação de entidades em um conjunto de entidades (por convenção são
sublinhadas no esquema da relação).
• Atributo principal (prime attribute) de um esquema de relação R é um membro
de qualquer chave de R.
UERJ
© Oscar Luiz Monteiro de Farias
43
1a Forma Normal...
• A 1a Forma Normal (1a FN) não permite relações dentro de
relações, ou seja, relações como atributos de tuplas.
• A 1a FN é agora considerada como parte da definição formal de
uma relação, a qual não permite atributos multi-valorados e nem
atributos compostos.
• A 1a FN estabelece que os domínios dos atributos devem incluir
apenas valores atômicos.
• Ex.: EMP_PROJ (Ssn, Ename, {PROJS(Pnumber, Hours)})
UERJ
© Oscar Luiz Monteiro de Farias
44
1a Forma Normal...
• i) Normalização de uma “tabela” multi-valorada com
redundância.
1a NF
UERJ
© Oscar Luiz Monteiro de Farias
45
1a Forma Normal...
• ii) Normalização sem redundâncias (superior)
UERJ
© Oscar Luiz Monteiro de Farias
46
1a Forma Normal...
i)
EMP_PROJ
Ssn
Ename
Projs
Pnumber
Hours
ii)
UERJ
© Oscar Luiz Monteiro de Farias
47
1a Forma Normal...
UERJ
© Oscar Luiz Monteiro de Farias
48
1a Forma Normal...
iii)
UERJ
© Oscar Luiz Monteiro de Farias
49
1a Forma Normal...
• Pesquisas têm sido conduzidas no modelo relacional para
permitir o uso de relações aninhadas.
• EMP_PROJ(Ssn, Ename, {PROJS(Pnumber, Hours)})
• Para colocar em 1a NF relações aninhadas, primeiro remove-se
os atributos aninhados para uma nova relação, propagando-se a
chave primária na mesma; a chave primária desta nova relação
a chave parcial (da relação aninhada) com a chave primária da
relação original.
UERJ
© Oscar Luiz Monteiro de Farias
50
2a Forma Normal...
• Baseia-se no conceito de dependência total.
• Uma DF X  Y é uma DF total, se a remoção de qualquer
atributo A de X, implica em que a dependência não mais se
verifica, isto é, (X - {A}) x Y.
• Uma DF X  Y é uma DF parcial, se a remoção de algum
atributo A de X, implica em que a dependência ainda se verifica,
isto é, para algum A  X, (X - {A})  Y.
• Um esquema de relação R está na 2a Forma Normal (2a FN), se
todo atributo não principal A em R apresenta DF total da chave
primária de R.
UERJ
© Oscar Luiz Monteiro de Farias
51
2a Forma Normal...
df1
df2
df3
Normalização 2NF
EP1
EP2
EP3
UERJ
© Oscar Luiz Monteiro de Farias
52
2a Forma Normal
Um esquema de relação R que não esteja na 2a FN pode ser
normalizado em diversos esquemas de relação, nas quais os
atributos não principais estão associados apenas com a parte da
chave primária da qual apresentam dependência funcional total.
UERJ
© Oscar Luiz Monteiro de Farias
53
3a Forma Normal...
• Uma relação R está na 3a FN se e somente se, por todo o tempo,
cada tupla de R consistir de um valor de chave primária que
identifique alguma entidade, juntamente com um conjunto de
valores de atributos mutuamente independentes que descrevam
aquela entidade de alguma forma [C. J. Date].
• Dois atributos são mutuamente independentes se nenhum for
funcionalmente dependente do outro.
UERJ
© Oscar Luiz Monteiro de Farias
54
3a Forma Normal...
• A 3a FN baseia-se no conceito de dependência transitiva.
• Uma DF X  Y em um esquema de relação R é uma dependência
transitiva se existe um conjunto de atributos Z que não seja um
subconjunto de qualquer chave de R e ambos X  Z e Z  Y se
verificam.
• Ex.: a DF Ssn  Mgrssn é transitiva via Dnumber em
EMP_DEPT (slide #13), porque Ssn  Dnumber e Dnumber 
Mgrssn e Dnumber não é um subconjunto da chave de
EMP_DEPT.
UERJ
© Oscar Luiz Monteiro de Farias
55
3a Forma Normal...
• Um esquema de relação R está na 3a FN se ele está na 2a FN e
nenhum atributo não principal (no prime) for transitivamente
dependente da chave primária.
• Ex.: Pode-se normalizar (3a FN) EMP_DEPT decompondo-o nos
esquemas ED1 e ED2.
• Intuitivamente observa-se que ED1 e ED2 representam fatos
relacionados a diferentes entidades: EMPLOYEE e
DEPARTMENT.
• Um Natural JOIN (*) em ED1 e ED2 irá recuperar a relação
original EMP_DEPT, sem gerar tuplas espúrias.
UERJ
© Oscar Luiz Monteiro de Farias
56
3a Forma Normal...
Normalização 3 NF
UERJ
© Oscar Luiz Monteiro de Farias
57
Definições Gerais da 2a NF e 3a NF...
• No processo de normalização deseja-se projetar esquemas de
relações que não possuam dependências parciais e/ou transitivas.
• As definições mais gerais da 2a FN e 3a FN levam em
consideração todas as chaves candidatas (e não apenas as chaves
primárias).
• Definição Geral da 2a FN: Um esquema de relação R está na 2a
FN se todo atributo não-principal (non prime attribute) A em R
não for parcialmente dependente de qualquer chave de R.
UERJ
© Oscar Luiz Monteiro de Farias
58
Definições Gerais da 2a NF e 3a NF...
fd1
fd2
fd3
fd4
Semântica: i) Chaves candidatas: PROPERTY_ID e {COUNTY_NAME, LOT#};
ii) LOT# é único dentro de cada COUNTY;
iii) supor 2 dfs adicionais: df3 - COUNTY_NAME  TAX_RATE;
df4 - AREA  PRICE
LOTS viola a 2a FN porque TAX_RATE é parcialmente dependente da chave candidata
{COUNTY_NAME, LOT#}.
UERJ
© Oscar Luiz Monteiro de Farias
59
Definições Gerais da 2a NF e 3a NF...
• LOTS é normalizada na 2a FN, removendo-se o atributo que é
dependente da chave parcial e colocando-o em uma nova relação
juntamente com a chave parcial.
fd1
fd2
fd4
fd3
UERJ
© Oscar Luiz Monteiro de Farias
60
Definições Gerais da 2a NF e 3a NF...
• Definição Geral da 3a FN: Um esquema de relação R está na 3a
FN sempre que, se uma dependência funcional X  A valer em
R, ou i) X é uma super-chave de R, ou ii) A é um atributo
principal (prime) de R.
• LOTS2 está na 3a FN, porém a df4 em LOTS1 viola a def. de 3a
FN, pois AREA não é uma super-chave de LOTS1 e PRICE não
é um atributo principal (prime).
• LOTS2 é normalizada removendo-se o atributo não-principal da
df que viola a def. da 3a FN, juntamente com o lado esquerdo da
mesma df para uma outra relação.
UERJ
© Oscar Luiz Monteiro de Farias
61
Definições Gerais da 2a NF e 3a NF...
Normalização de LOTS1 na 3a Forma Normal
fd1
fd2
fd4
UERJ
© Oscar Luiz Monteiro de Farias
62
Definições Gerais da 2a NF e 3a NF...
• A definição de 3a FN pode ser aplicada diretamente a um esquema
de relação (não é preciso primeiro normalizar para a 2a FN).
• Poder-se-ia decompor LOTS diretamente em LOTS1A, LOTS1B
e LOTS2.
• LOTS1 viola a 3a FN porque o atributo PRICE é transitivamente
dependente de cada uma das chaves-candidatas, através do
atributo não-principal AREA.
UERJ
© Oscar Luiz Monteiro de Farias
63
Definições Gerais da 2a NF e 3a NF...
Interpretação da definição geral da 3a FN
• Um esquema de relação R viola a definição geral da 3a FN se vale
em R uma dependência funcional X  A em que ambas as
condições “i” ‘e ii” da 3a FN são violadas.
• Violar “ii” implica em que A é um atributo não-principal
(nonprime).
• Violar “i” implica em que X não é um super-conjunto de alguma
chave de R; X poderia ser não-principal ou poderia ser um
subconjunto próprio de alguma chave de R; se X é não-principal
tem-se tipicamente uma dependência transitiva que viola a 3a FN,
enquanto que se X é um subconjunto de uma chave de R teremos
uma dependência parcial que viola a 3a FN (e também a 2a FN).
UERJ
© Oscar Luiz Monteiro de Farias
64
Definições Gerais da 2a NF e 3a NF
Tem-se portanto, a seguinte definição alternativa para a 3a FN:
Um esquema de relação R está na 3a FN se todo atributo nãoprincipal de R:
 apresenta dependência funcional total em cada chave de R;
 é dependente não-transitivo de toda chave de R.
UERJ
© Oscar Luiz Monteiro de Farias
65
Forma Normal de Boyce-Codd (BCNF)...
• BCNF é mais restritiva que a 3a FN. Uma relação em BCNF
também está na 3a FN. Todavia uma relação na 3a FN não
necessariamente está em BCNF.
• Suponha, no ex. dado, que haja a df AREA  COUNTY_NAME
• LOTS1A está ainda na 3a FN, pois COUNTY_NAME é um atributo
principal.
• Decompondo LOTS1A em LOTS1AX(PROPERTY_ID#, AREA,
LOT#) e LOTS1AY(AREA, COUNTY_NAME) evitar-se-ia a
redundância de se repetir a mesma informação milhares de vezes
em LOTS1A.
UERJ
© Oscar Luiz Monteiro de Farias
66
Forma Normal de Boyce-Codd (BCNF)...
fd1
fd2
fd5
Normalização BCNF
R está na 3a FN, mas não em BCNF
fd1
fd2
UERJ
© Oscar Luiz Monteiro de Farias
67
Forma Normal de Boyce-Codd (BCNF)
• Definição da 3a BCNF [ref. Elmasri/Navathe]: Um esquema de
relação R está em BCNF sempre que, ao valer uma DF X  A em
R, então X é uma super-chave de R.
• Um atributo, possivelmente composto, de um esquema de relação
R, do qual um outro atributo é funcionalmente dependente, é
chamado de determinante (funcional).
• Definição alternativa da 3a BCNF [(ref. C. J. Date]: Um
esquema de relação R está em BCNF se e somente se cada
determinante de R for uma chave candidata.
• No exemplo fd5 viola a BCNF em LOTS1A, porque AREA não é
uma super-chave de LOTS1A.
UERJ
© Oscar Luiz Monteiro de Farias
68
4a Forma Normal...
“Relação” CTX0
CURSO PROFESSOR
TEXTO
Physics
{Prof. Green,
{Basic Mechanics,
Prof. Brown,
Principle of Optics}
Prof. Black}
Math
{Prof. White}
{Modern Algebra,
Projective Geometry}
Semântica: o curso X pode ser ministrado por qualquer dos professores
(situados na mesma linha da tabela) e o prof. pode adotar qualquer um dos
livros textos indicados (situados na mesma linha da tabela).
UERJ
© Oscar Luiz Monteiro de Farias
69
4a Forma Normal...
• CTX0 pode ser normalizada em CTX.
• CTX satisfaz à restrição: se aparecerem ambas as tuplas (c,t1, x1),
(c, t2, x2) então aparecerão também as tuplas (c,t1, x2) e (c,t2, x1).
UERJ
© Oscar Luiz Monteiro de Farias
70
4a Forma Normal...
• CTX , apesar de estar em BCNF (todos os seu atributos fazem
parte da chave e não existem outras dfs), contém redundâncias
evidentes, que causarão anomalias nas operações de atualização.
• Ex.: para adicionar-se a informação de que o curso de física
passou a usar um novo texto Advanced Mechanics, torna-se
necessário criar três novas tuplas - uma para cada professor.
• As dificuldades são causadas pelo fato dos atributos professor e
texto não serem independentes.
UERJ
© Oscar Luiz Monteiro de Farias
71
4a Forma Normal...
CTX pode ser decomposta nas relações CP e CX, porém esta decomposição não
está baseada em dependências funcionais.
Tem-se, no caso, uma dependência de múltiplos valores (DMV).
Há duas DMVs na relação CTX:
• CTX.CURSO   CTX.PROFESSOR
• CTX.CURSO   CTX.TEXTO
UERJ
© Oscar Luiz Monteiro de Farias
72
4a Forma Normal...
• Significado: cada curso possui um “conjunto bem definido de
professores”, i. e., para um curso “c” e um texto “x”, o conjunto
“t” de professores que corresponde ao par (c, x) em CTX depende
só de “c” - não faz diferença que valor de “x” escolhamos desde
que “c” e “x” apareçam juntas em alguma tupla de CTX.
• Definição: Dada uma relação R com atributos A, B e C, a
dependência de múltiplos valores R.A   R.B vale para R, se
e somente se (sss) o conjunto de valores de B que se combinam
com um dado par (valores de A, valores de C) em R depender
somente do valor de A e for independente do valor de C (A, B e C
podem ser compostos).
• Uma DMV só pode existir em relações com pelo menos 3 atributos
UERJ
© Oscar Luiz Monteiro de Farias
73
4a Forma Normal...
• Dada a relação R(A, B, C), a DMV R.A   R.B vale sss a
DMV R.A   R.C também valer.
• Expressa-se ambas as DMVs pela notação:
R.A   R.B | C
• A DF é uma DMV na qual o conjunto de valores dependentes
consiste de um único valor.
• Teorema de Fagin: Uma relação R, com atributos A, B, e C,
pode ser decomposta em suas duas projeções R1(A, B) e R2(A,
C) sss a DMV A   B | C valer em R.
UERJ
© Oscar Luiz Monteiro de Farias
74
4a Forma Normal...
• Definição da 4a FN [C.J. Date]: Uma relação R está na 4a FN
sss, sempre que existir uma DMV em R, digamos A   B,
todos os atributos de R são funcionalmente dependentes de A
(i.e., A  X para todos os atributos X de R).
• i.e., as únicas dependências (DMVs e DFs) em R são da forma
K  X (i.e., uma dependência funcional de uma chave candidata
K em algum outro atributo X).
• Uma DMV X   Y em R é denominada uma DMV trivial se i)
Y é um subconjunto de X ou ii) (X U Y) = R.
• As relações que contêm DMVs não-triviais tendem a ser relações
todas-chaves, i.e, a sua chave é composta por todos os atributos.
UERJ
© Oscar Luiz Monteiro de Farias
75
4a Forma Normal...
Fagin comprovou dois resultados importantes, que permitem
incorporar a 4a FN no procedimento global de normalização:
 A 4a FN é estritamente mais poderosa que a BCNF, i.e., qualquer
relação na 4a FN está necessariamente na BCNF.
 Qualquer relação pode ser decomposta sem perdas em uma
coleção equivalente de relações na 4a FN.
Uma relação R(A, B, C) satisfazendo as DFs A  B, B  C é
melhor decomposta em suas projeções sobre (A, B) e (B, C) do
que sobre (A, B) e (A, C).
O mesmo vale quando as DFs são substituídas pelas MVDs
A   B, B   C
UERJ
© Oscar Luiz Monteiro de Farias
76
5a Forma Normal...
• Existem relações que não podem ser decompostas sem perdas e
em duas projeções, mas que podem ser decompostas sem perdas
em três (ou mais) projeções.
UERJ
© Oscar Luiz Monteiro de Farias
77
5a Forma Normal...
JUNÇÃO
sobre P#
TUPLA
ESPÚRIA
JUNÇÃO
sobre (J#, S#)
TABELA SPJ ORIGINAL
UERJ
© Oscar Luiz Monteiro de Farias
78
5a Forma Normal...
• A afirmativa de que SPJ é igual à junção de suas três projeções SP, PJ e JS é
equivalente à afirmativa:
se o par (s1, p1) aparece em SP
e o par (p1, j1) aparece em PJ
e o par (j1, s1) aparece em JS
então a tripla (s1, p1 , j1) obviamente aparece na junção de SP, PJ e JS.
• Uma vez que (s1, p1) aparece em SP sss s1 e p1 aparecem juntos em SPJ,o
mesmo acontecendo com (p1, j1) e (j1, s1), podemos reescrever a última
afirmativa como uma restrição sobre SPJ:
se (s1, p1 , j2), (s2, p1 , j1), (s1, p2 , j1) aparecem em SPJ
então (s1, p1 , j1) também aparece em SPJ.
UERJ
© Oscar Luiz Monteiro de Farias
79
5a Forma Normal...
• A restrição anterior é satisfeita sss a relação envolvida for a
junção de certas projeções suas. Esta restrição é chamada de
Dependência de Junção.
• SPJ satisfaz a dependência de junção * (SP, PJ, JS).
• Definição: Uma Dependência de Junção (DJ), denotada por
DJ(R1, R2, ... Rn), especificada em um esquema de relação R,
determina uma restrição nas instâncias r de R. A restrição diz que
toda instância legal r de R deve ter uma decomposição de junção
sem perdas (lossless join decomposition) em R1, R2, ... Rn; i.e., *
(<R1>(r), <R2>(r), ...,<Rn>(r)) = r.
• Note que uma DMV é um caso particular de DJ, onde n=2.
UERJ
© Oscar Luiz Monteiro de Farias
80
5a Forma Normal
• Uma dependência de junção DJ(R1, R2, ... Rn), especificada em
um esquema de relação R é uma DJ trivial se um dos esquemas
de relação Ri em DJ(R1, R2, ... Rn), for igual a R.
• Tal DJ é chamada trivial porque possui a propriedade de junção
sem perdas (lossless join) para qualquer relação de instância r de
R e, portanto, náo determina qualquer tipo de restrição sobre R.
• Definição: um esquema de relação R está na 5a FN (ou forma
normal project-join - PJNF) relativamente a um conjunto F de
DFs, MVDs e DJs, se para toda dependência de junção DJ(R1,
R2, ... Rn) não trivial em F+ (i.e., implicado por F) todo Ri é uma
super-chave de R.
• Descobrir DJs em BDs reais não é uma tarefa fácil...
UERJ
© Oscar Luiz Monteiro de Farias
81
Forma Normal Domínio-Chave
• Definição: Uma relação está na Domain-Key Normal Form
(DKNF) se todas as dependências e restrições que deveriam
valer na relação podem ser determinadas (enforced)
simplesmente pela determinação de restrições de domínio e
restrições chaves especificadas na relação.
• A idéia por trás da DKNF é especificar (ao menos teoricamente)
a “forma normal definitiva”, que leve em consideração todos os
tipos de dependências e restrições.
• Utilidade prática é limitada, pois restrições complexas não
parecem poder ser incluídas em relações DKNF.
UERJ
© Oscar Luiz Monteiro de Farias
82
Finalmente...
Para complementar a formação estudar:
• capítulos 13, 14 e 21 (apenas 21.1 e 21.2) do
Fundamentals of Database Systems, Elmasri/Navathe
• cap. 14 do Introdução a Sistemas de Bancos de Dados,
C. J. Date, Editora Campus.
• 2a Prova: 23/01/2001 às 07:00 horas no LABOGEO.
UERJ
© Oscar Luiz Monteiro de Farias
83