Revisão de Banco de Dados Avançado
1- Explique DETALHADAMENTE cada um os códigos a seguir evitando AMBIGÜIDADES e levando em consideração que o usuário sicrano criou as tabelas DVD e cliente:
a) Usuário sicrano: grant insert, select on cliente to public
R- O usuário sicrano deu permissão de inserção e busca de dados para qualquer um usuário na tabela cliente.
b) Usuário sicrano: grant all on dvd to beltrano with grant option
R- O usuário sicrano deu todas as permissões ao usuário beltrano, podendo também repassar as mesmas permissões para outros usuários.
c) Usuário beltrano: grant update, delete on dvd to fulano with grant option
R- O usuário beltrano deu a permissão de atualizar e deletar dados da tabela DVD para fulano, podendo também repassar as mesmas permissões para outros usuários
d) Usuário fulano: grant update on dvd to josias
R- O usuário fulano deu permissão de atualizar dados na tabela dvd para josias
e) Usuário sicrano: revoke update on dvd from beltrano
R- O usuário sicrano retirou a permissão de atualizar dados na tabela dvd de beltrano, por isso o usuário fulano também perdeu essa permissão, por receber a mesma de beltrano e conseqüentemente o usuário josias também perdeu a mesma permissão por receber de fulano.
2- Tomando como base os tipos de RESTIÇÃO DE INTEGRIDADE apresentados em sala de aula (Integridade Referencial, Restrição de Chave, de Domínio e de Entidade) e os códigos abaixo, favor responder:
Codigo 1
| Create table departamentos ( codDepto integer, nome varchar(20) check (nome in ‘Vendas’, ‘Pessoal’,’Finanças’, ‘Administrativo’), andar integer check (andar between 1 and 10), orçamento numeric (20,2), constraint DeptPK primary key (codDepto)); |
Código 2
| Create table Aluno_Disciplina ( id_aluno int, id_disciplina int, constraint fkAluno foreign key (id_aluno) references Aluno(id_aluno), constraint fkDisciplina foreign key (id_disciplina) references Disciplina (id_disciplina), ); |
Código 3
| Create table Aluno_Disciplina ( id_aluno int, id_disciplina int, constraint fkAluno foreign key (id_aluno) references Aluno(id_aluno) ON DELETE CASCADE, constraint fkDisciplina foreign key (id_disciplina) references Disciplina (id_disciplina) ON DELETE CASCADE ); |
a) Quais tipos de Restrições de Integridade são respeitadas de acordo com o código 1? Justifique sua resposta apontando que partes do código asseguram cada restrição.
R – O primeiro código se refere às Restrições de Chave e de Entidade(constraint DeptPK primary key (codDepto));) onde a chave primária não pode ser nula (NOT NULL) e única (UNIQUE). Também utiliza a Restrição de Domínio (nome varchar(20) check (nome in ‘Vendas’, ‘Pessoal’,’Finanças’, ‘Administrativo’),andar integer check (andar between 1 and 10),) pois coloca um limite nos dados da tabela.
b) Que tipo de Restrição de Integridade os códigos 2 e 3 asseguram? Justifique suas respostas apontando a parte do código que assegura a mesma.
R- Ambos estão aderindo a Restrição de Integridade Referencial (constraint fkAluno foreign key (id_aluno) references Aluno(id_aluno)), ou seja, garante a integridade da base de dados quando os P.ks são referenciadas em outras relações. O terceiro código acrescenta o ON DELETE CASCADE, onde se o atributo pai for deletado, todos os filhos também serão. Com isso, no Codigo 2, se o atributo pai tentar se deletado, não será permitido, pois terá atributos dependente dele em outras tabelas.
3- A tabela abaixo respeita a segunda forma normal? Justifique suas resposta.
| ALUNOS ( CodAluno, Nome, Morada, CodDisciplina, Disciplina) |
R- Não, pois não são todos os atributos que depende das chaves primárias. O jeito certo de fazer essa tabela seria separá-los por dependência. Ou seja:
ALUNOS(CodAluno, Nome, Morada)
DISCIPLINA(CodDisciplina, Disciplina)
4- A tabela abaixo respeita a primeira forma normal? Justifique sua resposta.
| CodAluno | Nome | Morada | Disciplinas |
| 1214 | Rua Costa | Rua A | Português, Matemática, Física |
| 1250 | Ana Maria | Rua B | Latim, Português, inglês |
| 1356 | Carla Silva | Av. ABC | Economia, Matemática, Direito |
| 1456 | Hugo Leal | Bairro DEF | Português, Matemática |
R- Não, pois a Primeira Forma exige que todos os atributos sejam Monovalorados, ou seja, possuam apenas um valor. O Atributo DISCIPLINA não obedece a essa regra.
5- De acordo com a figura abaixo e tomando como base os conceitos de Modelo EER, favor assinalar a alternativa falsa:
FIGURA
( ) Um empregado pode ser apenas um docente
( ) Um empregado pode ser apenas um pesquisador
( ) O símbolo ‘o’ presente na figura representa uma restrição de especialização do tipo overlap ou sobreposição.
( ) Um empregado pode ser um pesquisado e um docente ao mesmo tempo.
(X ) Um empregado não pode exercer o cargo de docente e pesquisador ao mesmo tempo.
6- Com base nos comandos básicos de SQL, e sempre que necessário, fazendo o uso das sintaxes dadas, faça o que se pede nos itens abaixo:
| Select nome(s)_da(s)_coluna(s) from nome_da_tabela where condição |
Cliente (id, nome, sexo, data_de_nascimento, idade, cidade, estado)
DVD (id, nome, gênero, direto, duração)
Cliente_loca_dvd (id_cliente, id_dvd, data_locacao)
a) Escreva um código para visualização apenas dos campos nome e idade da tabela cliente
Select nome,idade from cliente
b) Escreva um código para visualização de todos os dados da tabela cliente
Select * from cliente
c) Escreva um código para visualização dos nomes dos clientes do sexo masculino
Select nome from cliente where sexo =’M’
d) Escreva um código para visualização dos nomes dos clientes que moram no estado de Pernambuco
Select nome from cliente where estado = ‘PE’
e) Escreva um código para visualização dos nomes dos clientes que NÃO moram no estado de Pernambuco
Select nome from cliente where estado != ‘PE’
f) Escreva um código para visualização de todos os dados da tabela DVD apresentados em ordem decrescente de nome de filme.[ORDER BY]
Select * from DVD order by nome desc
g) Escreva um código para visualização dos nomes dos DVDs que tem duração maior que 150 OU são do gênero drama. [OPERADOR LOGICO]
Select nome from DVD where duração > 150 or genero = ‘drama’
h) Escreva um código para visualização dos campos nome e estado na tabela cliente, dos clientes que moram nos estados começados com a letra “P”.[LIKE]
Select nome,estado from cliente where estado like = ‘P%’
i) Escreva um código para visualização dos clientes nascidos ente janeiro e abril
Select nome from cliente where month(data_nascimento) between 1 and 4
j) Escreva um código para visualização da data_de_locacao e dos nomes dos clientes que locaram o filme “Tropa de Elite 2”.[JUNÇÕES]
Select c.nome, cld.data_de_locacao
From cliente c, dvd d, cliente_loca_dvd cld
Where d.nome = ‘Tropa de Elite’
and c.id = cld.id_cliente
and d.id = cld.id_dvd
k) Escreve o código para criação de uma view que permita a visualização dos nomes dos clientes do sexo feminino que possuam a menor idade. [SUBCONSULTA]
Create view Nome_Cliente as
Select nome from cliente where sexo = ‘F’ and idade < (select min(idade) from cliente)
l) Escreve o código para criação de uma view que permita a visualização dos nomes do DVDs cujo tempo de duração é acima da média.[SUBCONSULTA].
Create view Nome_DVDs as
Select nome from DVD where duração > (select avg(duracao) from DVD)
m) Escreva os códigos que permitem a visualização destas views
Select * from nomedaview
n) Escreva os códigos que permitem a remoção desta view.
Drop view Nomedaview
