Revisão - Banco de Dados Avançado (FAFICA)

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