CSI440 Aula 18 - SQL, Visões

Prévia do material em texto

CSI440 – Banco de 
Dados I
Bruno Rabello Monteiro
: Visões
Aula 18
UFOP  ICEA  DECSI
Conteúdo
• Visões
• Visões Materializadas
• Autorização do Banco de Dados
• Especificação de Restrições
• Asserções e Triggers
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 3
UFOP  ICEA  DECSI
Banco de Dados EMPRESA
(ELMASRI & NAVATHE, 2011)
UFOP  ICEA  DECSI
Visões
• São tabelas derivadas de outras tabelas
• Conhecidas por tabelas virtuais
• Possui limitações quanto a operações de
atualização
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 5
UFOP  ICEA  DECSI
Visões
• Permitem a visão do BD em 3 camadas
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 6
Física
Conceitual
Lógica Visões
Tabelas
Arquivos
UFOP  ICEA  DECSI
Visões
• Vantagens
• Escondem alguns dados dos usuários
• Simplificam a especificação de consultas
• Utilizadas como um mecanismo de segurança 
e autorização
• Modularizam o acesso ao BD
• Materializadas apenas quando especificamos 
uma consulta sobre ela
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 7
UFOP  ICEA  DECSI
Visões
• Visão = Consulta ( , , ... )
• Esquema de V é o resultado da consulta
• Conceitualmente
• Consulta envolvendo
• = Consulta ( , , ... )
• Avalie
• Na prática
• é reescrita usando ( , , ... ) ao invés de
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 8
UFOP  ICEA  DECSI
Visões: 
• A visão recebe um nome de tabela e uma
lista de nomes de atributos
• Junto à uma consulta que determina o
conteúdo da visão
• Uma vez criada, a visão pode ser usada em
consultas como uma tabela da base do BD
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 9
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
contendo o nome do funcionário,
o nome do projeto e o número de horas
trabalhadas pelo funcionário no projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 10
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
contendo o nome do funcionário,
o nome do projeto e o número de horas
trabalhadas pelo funcionário no projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 11
FUNC_PROJ
Pnome, Unome, Projnome, Horas
FUNCIONARIO, PROJETO, 
TRABALHA_EM
Cpf=Fcpf Pnr=Projnumero;
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
com o nome do departamento, a quantidade
de funcionários e o somatório de salários
dos funcionários
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 12
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
com o nome do departamento, a quantidade
de funcionários e o somatório de salários
dos funcionários
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 13
DEP_INFO (Dep_nome, Qtd_func, Total_sal)
Dnome, (*), (Salario)
DEPARTAMENTO, FUNCIONARIO
Dnumero=Dnr
Dnome;
UFOP  ICEA  DECSI
Visões
• : Recuperar o primeiro e último
nome de todos os funcionários que
trabalham no projeto ‘ProdutoX’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 14
UFOP  ICEA  DECSI
Visões
• : Recuperar o primeiro e último
nome de todos os funcionários que
trabalham no projeto ‘ProdutoX’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 15
Pnome, Unome
FUNC_PROJ
Projnome = ‘ProdutoX’;
UFOP  ICEA  DECSI
Visões
• Idealmente a ...
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 16
Pnome, Unome
FUNC_PROJ
Projnome = ‘ProdutoX’;
FUNC_PROJ
Pnome, Unome, Projnome, Horas
FUNCIONARIO, PROJETO, 
TRABALHA_EM
Cpf=Fcpf Pnr=Projnumero;
FUNC_PROJ;
UFOP  ICEA  DECSI
Visões
• Na ‘prática’...
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 17
Pnome, Unome
( Pnome, Unome, Projnome, Horas
FUNCIONARIO, PROJETO, 
TRABALHA_EM
Cpf=Fcpf
Pnr=Projnumero) FUNC_PROJ;
Projnome = ‘ProdutoX’;
UFOP  ICEA  DECSI
Visões
• Na realidade...
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 18
Pnome, Unome
FUNCIONARIO, PROJETO, 
TRABALHA_EM
Cpf=Fcpf
Pnr=Projnumero
Projnome = ‘ProdutoX’;
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
com o nome do departamento, a
quantidade de funcionários e o somatório de
salários dos funcionários e o total de projeto
controlados pelo departamento
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 19
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
com o nome do departamento, a
quantidade de funcionários e o somatório de
salários dos funcionários e o total de projeto
controlados pelo departamento
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 20
DEP_INFO2
DEP_INFO.*, (*) NumProjs
DEP_INFO, PROJETO, DEPARTAMENTO
Dnumero=Dnum Dep_nome = Dnome
Dep_nome, Qtd_func, Total_sal;
UFOP  ICEA  DECSI
Visões
• : Recuperar os nomes dos
departamentos que tenham a mesma
quantidade de funcionários e projetos
controlados
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 21
UFOP  ICEA  DECSI
Visões
• : Recuperar os nomes dos
departamentos que tenham a mesma
quantidade de funcionários e projetos
controlados
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 22
Dep_nome
DEP_INFO2
qtd_func = numprojs;
UFOP  ICEA  DECSI
Visões
• :
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 23
MEGA
F.Cpf, F.Pnome || F.Unome nome, E.Nome_Dependente,
E.parentesco, S.Pnome || S.Unome supervisor,
D.Dnome, G.Pnome || G.Unome gerente, L.dlocal, 
P.Projnome, T.horas
FUNCIONARIO F, PROJETO P, DEPARTAMENTO D, 
DEPENDENTE E, LOCALIZACAO_DEP L,
TRABALHA_EM T, FUNCIONARIO G, FUNCIONARIO S
F.Dnr = D.Dnumero F.CPF = E.FCpf
T.Fcpf = F.Cpf P.Projnumero = T.Pnr
D.Dnumero = P.Dnum L.Dnumero = D.Dnumero 
F.Supervisor = S.Cpf G.Cpf = D.Cpf_Gerente
UFOP  ICEA  DECSI
Visões
• Não faz sentido modificar uma visão
• Visão não é armazenada em disco
• Porém para alguns sistemas/usuários
• Visões são os únicos dados que eles
‘enxergam’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 24
UFOP  ICEA  DECSI
Visões
• Alterações em uma visão devem ser
reescritas para tabelas de base
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 25
consulta
Modificação
Modificação’
UFOP  ICEA  DECSI
Visões
• Uma visão é atualizável se
• For criada sobre uma única tabela
• Tiver a chave primária e todos os atributos
obrigatórios de
• Consultas aninhadas não puderem referenciar
• Não possuírem cláusulas ou funções
de agregação.
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 26
UFOP  ICEA  DECSI
Visões
• : Crie uma visão, chamada
com apenas o nome do departamento e o
número do departamento dos
departamentos com mais de 2 funcionários
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 27
DEPTO
D.Dnumero, D.Dnome
DEPARTAMENTO D
2 < ( (dnr)
FUNCIONARIO 
D.Dnumero = Dnr);
UFOP  ICEA  DECSI
Visões
• Atualização da
• Realiza com sucesso a inserção na tabela
• Nova tupla não aparece na
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 28
DEPTO
(10, ‘Ensino’);
UFOP  ICEA  DECSI
Visões
• Para corrigir o problema anterior
• Incluir na definição da visão
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 29
DEPTO_CHECK
D.Dnumero, D.Dnome
DEPARTAMENTO D
2 < ( (dnr)
FUNCIONARIO 
D.Dnumero = Dnr)
;
UFOP  ICEA  DECSI
Visões
• Atualização da visão 
• Causa um erro
• Nova tupla não possui funcionários!
• Cláusula só permite que tuplas 
sejam ‘inseridas’ se fizerem ‘parte’ da visão
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 30
DEPTO_CHECK
(50, ‘RH’);
UFOP  ICEA  DECSI
Visões Materializadas
• Possui as mesmas vantagens de uma visão
‘normal’
• Melhora o desempenho de consultas
• E tem como desvantagens
• Visão pode ser muito grande
• Problemas com modificações nas relações de
base da visão
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 31
UFOP  ICEA  DECSI
Visões Materializadas
• : Crie uma visão,
chamada contendo o nome do
funcionário, o nome do projeto e o número
de horas trabalhadas pelo funcionário no
projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 32
UFOP  ICEA  DECSI
Visões Materializadas
• : Crie uma visão,
chamada contendo o nome do
funcionário, o nome do projeto e o número
de horas trabalhadaspelo funcionário no
projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 33
FUNC_PROJ
Pnome, Unome, Projnome, Horas
FUNCIONARIO, PROJETO, 
TRABALHA_EM
Cpf=Fcpf Pnr=Projnumero;
UFOP  ICEA  DECSI
Visões Materializadas
• Modificações no BD podem deixar a visão
materializada inválida
• Atualizações nas tabelas de base não são
refletidas automaticamente na visão
materializada.
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 34
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Diz respeito a permitir que usuários
acessem apenas os dados que tenham
autorização
• Auxilia o banco de dados na questão de
segurança
• Usuários do SGBD passam a ter privilégios
• Podem acessar e realizar operações apenas
sobre os dados previamente autorizados
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 35
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Privilégios possíveis sobre uma tabela
• sobre ou ( , , ..., ) sobre
• sobre ou ( , , ..., ) sobre
• sobre ou ( , , ..., ) sobre
• sobre R
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 36
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Para executar o comando abaixo, um
usuário precisaria
• sobre
• sobre ( ) e
( )
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 37
FUNCIONARIO 
Cpf
( Fcpf
DEPENDENTE)
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Para executar o comando abaixo, um
usuário precisaria
• sobre ( , )
• sobre ( )
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 38
PROJETO
Projlocal = ‘Santo André’, Dnum = 5
Projnumero = ‘10’;
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Comando para fornecer privilégios
•
• Comando para remover privilégios
•
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 39
, TRABALHA_EM user;
PROJETO(Projnumero) user;
FUNCIONARIO user;
TRABALHA_EM user;
FUNCIONARIO user;
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• Um usuário pode dar privilégios e conceder
o direito a outro usuário também dar
privilégios
• Usuário ‘ ’ pode repassar o privilégio
para outros usuários
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 40
, TRABALHA_EM user;
;
UFOP  ICEA  DECSI
Autorização do Banco de Dados
• O comando pode, se preciso, ter um
efeito cascata
• Nesse caso, os privilégios passados
pelo usuário ‘user’ também serão
removidos!
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 41
TRABALHA_EM user;
;
UFOP  ICEA  DECSI
Referências
• Elmasri, R.; Navathe, S. B. (2011). “Mais SQL: Consultas Complexas, triggers, 
views e modificação de esquema” In: Elmasri, R.; Navathe, S. B. Sistemas de 
Banco de Dados, 6 ed. São Paulo: Pearson, p. 88-91.
• Elmasri, R.; Navathe, S. B. (2011). “Segurança de Banco de Dados” In: 
Elmasri, R.; Navathe, S. B. Sistemas de Banco de Dados, 6 ed. São Paulo: 
Pearson, p. 567-570.
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 42