View
216
Download
0
Category
Preview:
Citation preview
Banco de Dados I
Aula 17 - Prof. Bruno Moreno
08/11/2011
Plano de Aula
• Visões em SQL
• Normalização
– Motivação
– Definição
– Formas Normais
15:37
VISÕES Banco de Dados I
15:37
Visões
• É uma tabela única derivada de outra(s) tabela(s)
– Pode derivada de uma tabela ou de outra visão
• Uma visão não existe de forma física
– É uma tabela virtual
– Tuplas de uma visão não são armazenadas no banco de dados
• Algumas atualizações não são possíveis em visões
15:37
Visões
• São utilizadas para simplificação de consultas complexas – Visões podem ser utilizadas para especificar uma
relação que é composta por junções de tabelas e que é consultada freqüentemente
– Em vez de formula sempre a junção, utiliza-se a visão
• São utilizadas para mecanismos de autorização e segurança
• Comando – CREATE VIEW
15:37
Visões
• Exemplo (1) CREATE VIEW TRABALHA_EM1 AS
SELECT PNOME, UNOME, PJNOME, HORAS FROM FUNCIONARIO, PROJETO, TRABALHA_EM WHERE CPF = ECPF AND PNO = PNUMERO;
• Exemplo (2) CREATE VIEW DEPT_INFO(DEPT_NOME, NO_EMPS, TOTAL_SAL) AS
SELECT DNOME, COUNT(*), SUM(SALARIO) FROM DEPARTAMENTO, FUNCIONARIO WHERE DNUMERO = DNO GROUP BY DNOME;
15:37
Visões
• Pode-se utilizar as consultas com SELECT para as visões previamente definidas.
SELECT PNOME, UNOME FROM TRABALHA_EM1 WHERE PJNOME = ‘ProjetoX’
15:37
Visões
• Visões são sempre atualizadas!
• Ao modificar-se tuplas das tabelas básicas (que compõem uma visão)
– A visão reflete essas alterações automaticamente
• Uma visão não é realizada no instante de sua definição
• Uma visão é realizada ao especificar uma consulta sobre ela
15:37
É uma responsabilidade do SGBD a garantia de que a visão é atualizada
Visões
• Exclusão de visões
DROP VIEW <nome da visão>
DROP VIEW TRABALHA_EM1;
15:37
Implementação de uma visão
• Existem duas abordagens de implementação pelo SGBD (1) Transformar a consulta de visão em uma consulta de
tabelas básicas
SELECT PNOME, UNOME FROM TRABALHA_EM1 WHERE PJNOME = ‘ProjetoX’
SELECT PNOME, UNOME FROM FUNCIONARIO, PROJETO, TRABALHA_EM WHERE CPF = ECPF AND PNO = PNUMERO AND PJNOME = ‘ProjetoX’
15:37
Ineficiente para visões formadas por consultas
complexas
Implementação de uma visão
• Existem duas abordagens de implementação pelo SGBD
– (2) Materialização da visão
• Uma tabela temporária é criada fisicamente a partir da primeira consulta utilizando-se a visão
• O SGBD deve garantir técnicas suficientes para atualização das visões materializadas
• SGBDs utilizam a variável do tempo para desconstruir tabelas temporárias que não estão sendo utilizadas
15:37
NORMALIZAÇÃO Banco de Dados I
15:37
Normalização - Motivação
• Qualidade de um projeto de BD
– Semântica de atributos bem definida
– Redução de valores redundantes nas tuplas
– Redução de valores null nas tuplas
15:37
Normalização - Motivação
(1) Semântica de atributos bem definida
– Atributos de um esquema devem produzir uma semântica fiel em relação ao mundo real
– Se o modelo conceitual for construído cuidadosamente, seguindo a sistemática do mapeamento para as relações
• A semântica correta é mantida de forma bem definida
15:37
Normalização - Motivação
15:37
Modele sempre um esquema de relação de modo que seja fácil explicar seu significado.
Normalização
Normalização - Motivação
• Exemplo de esquemas pobres semanticamente
15:37
Um esquema pobre pode gerar informações redundantes e anomalias de atualizações
Normalização - Motivação
(2) Redução de valores redundantes nas tuplas
15:37
Um esquema pobre pode gerar informações redundantes e anomalias de atualizações
Normalização - Motivação
• Qual o melhor esquema a utilizar: (1) apenas FUNC_DEP ou (2) FUNCIONARIO e DEPARTAMENTO?
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
ENOME CPF DATANASC ENDERECO DNUMERO
João 123456789 01/01/1970 Rua A 1
Maria 101010101 02/02/1975 Rua B 1
José 121212121 01/01/1971 Rua C 1
Pedro 131313131 01/01/1974 Rua D 2
Rute 141414141 01/01/1973 Rua E 2
FUNCIONARIO
DNUMERO DNOME DGERCPF
1 Pesquisa 123456789
2 Adm. 141414141
DEPARTAMENTO
Atributos são repetidos
Informações sobre cada departamento só aparecem uma vez.
Só o número do departamento é repetido.
Normalização - Motivação
• Relações como FUNC_DEP geram anomalias de atualização
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
Essas anomalias se dividem em três tipos: (1)Anomalias de inserção (2)Anomalias de exclusão (3)Anomalias de atualização
Normalização - Motivação
• Relações como FUNC_DEP geram anomalias de atualização
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
Ao inserir um funcionário (que é de um departamento), qual a garantia que os dados do departamentos serão
inseridos de forma consistente?
Anomalias de Inserção
Normalização - Motivação
• Relações como FUNC_DEP geram anomalias de atualização
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
Como inserir um novo departamento que não tem nenhum funcionário ainda?
Anomalias de Inserção
Normalização - Motivação
• Relações como FUNC_DEP geram anomalias de atualização
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
Ao excluir o último empregado de um departamento, informações sobre aquele departamento serão perdidas.
Anomalias de Exclusão
Normalização - Motivação
• Relações como FUNC_DEP geram anomalias de atualização
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
José 121212121 01/01/1971 Rua C 1 Pesquisa 123456789
Pedro 131313131 01/01/1974 Rua D 2 Adm. 141414141
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
Ao mudar o nome de um departamento, deve-se garantir que esse nome será atualizado para todos os
funcionários.
Anomalias de Atualização
Normalização - Motivação
Modele esquemas de relações básicas de forma que nenhuma anomalia possa ocorrer nas
relações
15:37
Normalização
Normalização - Motivação
(3) Redução de valores null nas tuplas
– Há relações “gordas” que possuem atributos que raramente são utilizados
– Isso causa desperdício de espaço de armazenamento
• Gera problemas de entendimento sobre o significado dos atributos
15:37
Evite, sempre que possível, colocar atributos em uma relação básica cujo valores freqüentemente
podem ser nulls
Normalização - Motivação
(3) Redução de valores null nas tuplas
– Há relações “gordas” que possuem atributos que raramente são utilizados
– Isso causa desperdício de espaço de armazenamento
• Gera problemas de entendimento sobre o significado dos atributos
15:37
Geralmente opta-se por criar uma nova relação para os atributos com nulls frequentes
(Ex. Escritório de Empregado)
Normalização
Normalização – Definição
• Técnica de analise e organização de dados que visa determinar a melhor composição para uma estrutura de dados
• Principais objetivos
– Eliminar anomalias de atualização
– Minimizar redundâncias e conseqüentes riscos de inconsistências
15:37
Decomposição Modelo Relacional Normalização
Normalização – Definição
• Converte progressivamente uma tabela em tabelas de grau e cardinalidade menores até que pouca ou nenhuma redundância de dados exista
• Se a normalização for bem sucedida
– O espaço de armazenamento dos dados diminui
– A tabela pode ser atualizada com maior eficiência
15:37
Formas Normais
• É uma regra que deve ser obedecida por uma tabela para que ela seja considerada “bem projetada”.
– 1ª FN
– 2ª FN
– 3ª FN
– FN de Boyce-Codd
– 4ª FN
15:37
Formas Normais
• É uma regra que deve ser obedecida por uma tabela para que ela seja considerada “bem projetada”.
– 1ª FN
– 2ª FN
– 3ª FN
– FN de Boyce-Codd
– 4ª FN
15:37
Principais!
1ª FORMA NORMAL Normalização
15:37
1ª Forma Normal
• Uma tabela encontra-se na 1ª FN quando ela não contém tabelas aninhadas, atributos multivalorados e compostos
15:37
ATOMICIDADE
1ª Forma Normal
• Atributos de uma relação devem conter apenas valores atômicos
• Exemplo de tabela não normalizada
15:37
CodDept Nome CPF_Ger Localização
1 Vendas 123 {João Pessoa, Rio Tinto}
2 Marketing 222 {João Pessoa, Rio Tinto,
Mamanguape}
3 Finanças 333 Rio Tinto
Atributos não atômicos
DEPARTAMENTO
1ª Forma Normal
• Atributos de uma relação devem conter apenas valores atômicos
• Exemplo de tabela não normalizada
15:37
Atributos não atômicos
Matricula Nome CPF Telefone
10 José 123 81 3222-2222
15 João 456 83 3222-2222
20 Manuel 789 83 3283-2836
25 Josué 012 85 3446-8878
ALUNO
1ª Forma Normal
• Passar para 1ª FN
– A tabela resultante do processo deve conter apenas atributos atômicos
– Tabela original possui atributos multivalorados
• Cria-se duas relações Relação 1- Possui todos atributos atômicos da relação original
Relação 2- Possui atributos multivalorados distribuídos de forma atômica. PK é composta por PK original e atributo que era multivalorado
15:37
1ª Forma Normal
• Exemplo (1): atributos multivalorados
15:37
CodDept Nome CPF_Ger Localização
1 Vendas 123 {João Pessoa, Rio Tinto}
2 Marketing 222 {João Pessoa, Rio Tinto,
Mamanguape}
3 Finanças 333 Rio Tinto
CodDept Nome CPF_Ger
1 Vendas 123
2 Marketing 222
3 Finanças 333
CodDept Localização
1 João Pessoa
1 Rio Tinto
2 João Pessoa
2 Rio Tinto
3 Mamanguape
3 Rio Tinto
DEPARTAMENTO
DEPARTAMENTO’
DEPT_LOC
1ª Forma Normal
• Passar para 1ª FN
– A tabela resultante do processo deve conter apenas atributos atômicos
– Tabela original possui atributos compostos
• Remove-se atributos compostos da relação original
• Cria-se uma nova relação composta por uma PK combinada pela chave parcial e com a PK da relação original
15:37
1ª Forma Normal
• Exemplo (2): atributos multivalorados compostos
15:37
CPF NOME PROJETOS
PNUMERO HORAS
123 João 1 32.5
2 7.5
456 Maria 3 40.0
789 José
1 20.0
2 20.0
3 10.0
111 Pedro 3 10.0
222 Paulo
1 10.0
2 20.0
3 30.0
4 40.0
EMP_PROJ
1ª Forma Normal
• Exemplo (2): atributos multivalorados compostos
15:37
CPF NOME
123 João
456 Maria
789 José
111 Pedro
222 Paulo
CPF PNUMERO HORAS
123 1 32.5
123 2 7.5
456 3 40.0
789 1 20.0
789 2 20.0
789 3 10.0
111 3 10.0
222 1 10.0
222 2 20.0
222 3 30.0
222 4 40.0
1ª Forma Normal
• Exemplo (3)
15:37
Código Carga H. Salas
inf1731 30 L512 L520
inf1732 60 L520
DISCIPLINA
Código Carga H.
inf1731 30
inf1732 60
DISCIPLINA
Não normalizada
Código Salas
inf1731 L512
inf1731 L520
inf1732 L520
DISC_SALAS
Normalizada...
1ª Forma Normal
• Exemplo (4)
15:37
Num_cad Nome CPF Salário Diplomas
0010 José 123456 5.000,00 (D1,D2)
0015 João 234567 3.000,00 (D3)
0020 Manuel 345678 8.000,00 (D1,D2,D4)
0018 José 987654 4.000,00 (D2)
PILOTO
Não normalizada
Num_cad Nome CPF Salário
0010 José 123456 5.000,00
0015 João 234567 3.000,00
0020 Manuel 345678 8.000,00
0018 José 987654 4.000,00
Num_cad Diploma
0010 D1
0010 D2
0015 D3
0020 D1
0020 D2
0020 D4
0018 D2
1ª Forma Normal
• Exemplo (5)
15:37
Matricula Nome CPF Telefone
10 José 123 81 3222-2222
15 João 456 83 3222-2222
20 Manuel 789 83 3283-2836
25 Josué 012 85 3446-8878
ALUNO
Não normalizada
Matricula Nome CPF
10 José 123
15 João 456
20 Manuel 789
25 Josué 012
Matrícula DDD Numero
10 81 3222-2222
15 83 3222-2222
20 83 3283-2836
25 85 3446-8878
2ª FORMA NORMAL Normalização
15:37
2ª Forma Normal
• Uma tabela encontra-se na 2ª FN quando, além de estar na 1ª FN, não contém dependências funcionais parciais. – Todo atributo não-chave é plenamente dependente da
chave primária
– Existe uma DF Plena entre um atributo X e um conjunto de atributos Y quando X depende de Y para sua existência
– Um atributo A (ou uma coleção de atributos) é a chave primária de uma relação R, se • Todos atributos de R são funcionalmente dependentes de A
• Nenhum atributo de R pode ser dependente apenas de um subconjunto de A
15:37
2ª Forma Normal
• Uma tabela encontra-se na 2ª FN quando, além de estar na 1ª FN, não contém dependências funcionais parciais.
• Dependência Funcional
– Uma coluna C2 depende funcionalmente de C1 quando, para qualquer tupla, para cada valor de C1, aparece o mesmo valor de C2
– Exemplo: Categoria → Salário
15:37
Nome Categoria Salário
João Vendedor 1.000
Maria Caixa 2.000
José Vendedor 1.000
Rute Caixa 2.000
2ª Forma Normal
• Uma tabela encontra-se na 2ª FN quando, além de estar na 1ª FN, não contém dependências funcionais parciais.
• Dependência Funcional Parcial
– Ocorre quando uma coluna depende apenas de parte de uma chave primária composta
15:37
Tabelas cujas chaves primárias NÃO são compostas e estão na 1FN também se
encontram na 2FN
2ª Forma Normal
• Exemplo 1 de tabela não normalizada
15:37
CodProj CodEmp Nome Categoria Salario DataIni Horas
1 1111 João 1 2.500,00 03/01/01 24
1 2222 Maria 3 2.100,00 21/11/01 18
3 3333 José 2 1.800,00 15/02/01 24
1 4444 Joana 2 1.800,00 12/02/01 18
2 5555 Pedro 1 2.500,00 08/03/01 24
3 6666 Rute 1 2.500,00 09/01/01 18
2 7777 Paulo 2 1.800,00 10/03/01 12
PROJ_EMP Dependência parcial
2ª Forma Normal
• Exemplo 2 de tabela não normalizada
15:37
Num_cad Nome CPF Salário Diploma Descrição
10 José 123 5.000,00 D1 Helicópteros
10 José 123 5.000,00 D2 Jato
15 Maria 111 3.000,00 D3 Bi-motor
20 João 333 8.000,00 D1 Helicópteros
20 João 333 8.000,00 D2 Jato
20 João 333 8.000,00 D4 Concorde
18 Paulo 222 4.000,00 D2 Jato
PILOTO
Dependência parcial Dependência parcial
2ª Forma Normal
• Como corrigir?
– Move-se os atributos não chave que dependem apenas de parte da chave primária para outra tabela.
– A PK da nova tabela é constituída pela parte da chave primária da tabela original e pelos atributos que dependem dessa chave
15:37
2ª Forma Normal
• Para passar para a 2ª FN
15:37
ProjEmp ( CodProj, CodEmp, Nome, Cat, Sal, DataIni, Horas )
ProjEmp (CodProj, CodEmp, DataIni, Horas)
Emp (CodEmp, Nome, Cat, Sal)
2ª Forma Normal
• Para passar para a 2ª FN
15:37
Piloto ( Num_cad, Nome, CPF, Salário, Diploma, Descrição)
Piloto (Num_cad, Nome, CPF, Salário)
Diploma (Cod_Diploma, Descrição)
Formação ( Num_cad, Diploma)
2ª Forma Normal
15:37
Num_cad Nome CPF Salário Diploma Descrição
10 José 123 5.000,00 D1 Helicópteros
10 José 123 5.000,00 D2 Jato
15 Maria 111 3.000,00 D3 Bi-motor
20 João 333 8.000,00 D1 Helicópteros
20 João 333 8.000,00 D2 Jato
20 João 333 8.000,00 D4 Concorde
18 Paulo 222 4.000,00 D2 Jato
PILOTO
2ª Forma Normal
15:37
Num_cad Nome CPF Salário
10 José 123 5.000,00
15 Maria 111 3.000,00
20 João 333 8.000,00
18 Paulo 222 4.000,00
PILOTO
Cod_Diploma Descrição
D1 Helicópteros
D2 Jato
D3 Bi-motor
D4 Concorde
DIPLOMA
Num_cad Diploma
10 D1
10 D2
15 D3
20 D1
20 D2
20 D4
18 D2
FORMAÇÃO
3ª FORMA NORMAL Normalização
15:37
3ª Forma Normal
• Uma relação está na 3ª Forma Normal (3NF) se ela está na 2NF e nenhum atributo não-chave é transitivamente dependente da chave primária
• Dependência Transitiva
– Ocorre quando um atributo Y depende de um atributo X e um atributo Z depende de Y
• Conseqüentemente, Z depende de X
15:37
X → Y → Z
3ª Forma Normal
• Uma relação está na 3ª Forma Normal (3NF) se ela está na 2NF e nenhum atributo não-chave é transitivamente dependente da chave primária
• Dependência Transitiva (Exemplo 1)
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
CPF → DNUMERO
DNUMERO → DGERCPF
DNUMERO não é PK nem subconjunto de PK!
3ª Forma Normal
• Uma relação está na 3ª Forma Normal (3NF) se ela está na 2NF e nenhum atributo não-chave é transitivamente dependente da chave primária
• Dependência Transitiva (Exemplo 2)
15:37
NO_AV TIPO CAPACIDADE LOCAL
101 A320 320 João Pessoa
104 B727 250 Recife
105 DC10 350 João Pessoa
103 B727 250 Natal
110 B727 250 João Pessoa
AVIÃO
NO_AV→ TIPO
TIPO → CAPACIDADE
3ª Forma Normal
Todo atributo não chave depende diretamente da chave primária
• Uma dependência funcional transitiva ou
indireta acontece quando uma coluna não chave primária depende funcionalmente de outra coluna ou combinação de colunas não chave primária.
15:37
3ª Forma Normal
• Como corrigir?
– Para cada determinante que não é PK, remover da relação os atributos que dependem dele para criar uma nova relação onde o determinante será a PK
15:37
ENOME CPF DATANASC ENDERECO DNUMERO DNOME DGERCPF
João 123456789 01/01/1970 Rua A 1 Pesquisa 123456789
Maria 101010101 02/02/1975 Rua B 1 Pesquisa 123456789
Rute 141414141 01/01/1973 Rua E 2 Adm. 141414141
FUNC_DEP
FUNC_DEP1
ENOME CPF DATANASC ENDERECO DNUMERO
João 123456789 01/01/1970 Rua A 1
Maria 101010101 02/02/1975 Rua B 1
Rute 141414141 01/01/1973 Rua E 2
DEPARTAMENTO
DNUMERO DNOME DGERCPF
1 Pesquisa 123456789
1 Pesquisa 123456789
2 Adm. 141414141
3ª Forma Normal
• Como corrigir?
– Para cada determinante que não é PK, remover da relação os atributos que dependem dele para criar uma nova relação onde o determinante será a PK
15:37
NO_AV TIPO CAPACIDADE LOCAL
101 A320 320 João Pessoa
104 B727 250 Recife
105 DC10 350 João Pessoa
103 B727 250 Natal
110 B727 250 João Pessoa
AVIÃO NO_AV TIPO LOCAL
101 A320 João Pessoa
104 B727 Recife
105 DC10 João Pessoa
103 B727 Natal
110 B727 João Pessoa
AVIÃO1
TIPO CAPACIDADE
A320 320
B727 250
DC10 350
TIPO_AV
Projeto: 4ª iteração: esquema normalizado e com consultas pré-definidas
• As consultas precisam ser descritas - SELECT, INSERT, DELETE, UPDATE - VISÕES - JUNÇÕES INTERNAS E EXTERNAS
15:37
Freqüência!
08/11/2011
15:37
Recommended