ATIVIDADE PRÁTICA

Prévia do material em texto

ATIVIDADE PRÁTICA – SGBD
Questão 1/10 - Sistema Gerenciador de Banco de Dados
O SQL é, em última análise, uma linguagem de programação criada para a recuperação de informações em bancos de dados. E como tal permite operações complexas. Duas formas interessantes encontradas para simplificar este processo se encontram nas Procedures e Functions. Uma procedure é um conjunto de comandos SQL que serão compilados e armazenados em forma binária para a execução por vários Scripts SQL. Considerando o query SQL a seguir, crie uma procedure chamada NumeroCientistas que devolva o total de cientistas registrados neste banco de dados.
Nota: 10.0
	
Resposta: DROP procedure if exists NumeroCientista; DELIMITER CREATEprocedureNumeroCientista()BEGINDECLAREquantidadeint;SELECTCOUNT(∗)intoquantidadeFROMcientistas;SELECTquantidade;END
	DELIMITER; CALL NumeroCientista;
Questão 2/10 - Sistema Gerenciador de Banco de Dados
Utilizamod procedures para automatizar processos repetitivos na gestão do nosso Banco de Dados. Suponha, por exemplo, que você precisará calcular a média em diversas tabelas, ou que fará um arredondamento qualquer em todas elas, ou ainda, que precisará fazer contas com datas. Todos estes casos, são casos onde o uso de uma procedure pode poupar trabalho e problemas. Considerando o query SQL a seguir, que cria um conjunto de tabelas em um banco de dados. Escreva o seu próprio query SQL para criar um procedure que receba o nome de um parceiro e devolva: o nome do parceiro, a organização a que ele faz parte e o código desta organização.
Nota: 10.0
	
Resposta: DROP procedure if exists pesquisa; DELIMITER CREATEprocedurepesquisa(inNParceirotext)BEGINSELECTParceiros.Nome,TipoOrg.NomeTipo,TipoOrg.OrgaoIDFROMParceirosJOINTipoOrgON(Parceiros.OrgaoID=TipoOrg.OrgaoID)WHEREParceiros.Nome=NParceiroORDERBYParceiros.Nomeasc;END
	DELIMITER; CALL pesquisa ('Márcio França');
Questão 3/10 - Sistema Gerenciador de Banco de Dados
Uma das principais razões para a popularidade do modelo relacional e da linguagem SQL é a facilidade de recuperação de informações. O desenvolvedor pode, sem muito esforço, cruzar informações em tabelas diferentes ou, criar comandos aritiméticos que premitem analisar os dados armazenados e descobrir novas informações.  
Considerando o query SQL a seguir, que cria um conjunto de tabelas em um banco de dados,  escreva o seu próprio query SQL que devolva uma lista de todos os cientistas, seus projetos e o número de horas empregado por cada um neste projeto. Não se esqueça que lista deve ser ordenada por nome de projeto e por nome de cientista sempre em ordem crescente.
Nota: 10.0
	
Resposta: SELECT cientistas.nome AS 'Cientista', projetos.nome AS 'Projetos', projetos.horas AS 'Horas' FROM cientistas JOIN atribuidoA ON (cientistas.id = atribuidoA.cientista) JOIN projetos ON (projetos.codigo = atribuidoA.projeto) ORDER BY projetos.nome, cientistas.nome ASC;
Questão 4/10 - Sistema Gerenciador de Banco de Dados
Uma estratégia eficiente de aumentar a operacionalidade de um banco de dados é a criação de views. Podemos entender uma view como sendo uma tabela especial, quase virtual, que não têm dados próprios mas que existe no banco por meio de algum tipo de relacionamento criado entre os dados das outras tabelas. Ainda que simplista, esta definição permite vislumbrar a utilidade das views. Muitas vezes, temos que criar views  para atender a necessidade de aumento de eficiência dos Bancos de Dados que Gerenciamos. Sendo assim, considerando o query SQL a seguir, escreva o seu próprio query SQL de forma a criar uma view chamada de ProjetosHora que contenha o nome dos projetos e o número de horas gasto em cada um. Observe que os dados devem estar ordenados em ordem crescente pelo nome do projeto.
Nota: 10.0
	
Resposta: CREATE VIEW ProjetosHoras AS SELECT Projetos.Nome, SUM(Projetos.Horas) FROM Projetos GROUP BY Projetos.Nome ORDER BY Projetos.Nome ASC;
Questão 5/10 - Sistema Gerenciador de Banco de Dados
A grande diferença entre uma procedure e uma function é que esta última devolve dados para o sistema e, pode ser facilmente utilizada para a recuperação de informação em um Banco de Dados. Considerando o código da function armazenada em um determinado banco de dados, escreva o query SQL que retorne uma lista de nomes de consumidores (nomeConsumidor) e seu nível de crédito sabendo que a tabela Consumidores tem o campo NomeConsumidor e campo limiteCredito. Observe também que o resultado deve ser ordenado pelo campo NomeConsumidor.
Para completar esta tarefa você deverá escrever o query SQL que atende as especificações acima usando os dados contidos na Function ou especificados no enunciado.
Nota: 10.0
	
Resposta: SELECT NomeConsumidor, NivelConsumidor(limiteCredito) FROM consumidores ORDER BY NomeCosumidor ASC;
Questão 6/10 - Sistema Gerenciador de Banco de Dados
Entre as operações interessantes que podemos fazer com os dados armazenados está a criação de pequenas tabelas auxiliares para criar novas relações entre dados diferentes. Em geral, estas tabelas aceleram o processo de recuperação da informação e tornam a criação dos Scripts SQL mais simples. Entretanto, é preciso lembrar que sempre existe um custo operacional devido ao aumento da complexidade do modelo de dados. Suponha, que você gerencia um grande Sistema de Bancos de Dados e precisa criar uma tabela auxiliar para armazenar os números de identificação dos recursos usados em uma construção. Como esta tabela deve ser o mais simples possível você terá que armazenar apenas dois campos: o ID do recurso nesta tabela, para referências e o próprio código do recurso. Observe também que estes dois campos devem armazenar números inteiros. Certifique-se, contudo, que esta tabela tenha uma chave primária em ID e um Index em código.Para completar esta tarefa digite o código do query SQL que atende as especificações deste projeto e, quando executado, criará a tabela desejada.
Nota: 10.0
	
Resposta: CREATE TABLE if not exists Auxiliar_recurso( ID int not null auto_increment, Codigo int, PRIMARY KEY (ID), INDEX idx_codigo (codigo) )
Questão 7/10 - Sistema Gerenciador de Banco de Dados
Uma das principais razões para a criação de uma view está na necessidade de aumentar a eficiência do banco de dados. Ao contrário de um query comum o resultado da view está registrado fisicamente no banco. Sendo assim, toda a complexidade da criação da view só tem impacto no processamento durante o processo de criação. Depois, todas as consultas a view têm um custo de processamento igual ao da consulta a qualquer tabela.
Considerando o query SQL a seguir, crie o seu próprio query para criar uma view chamada de CientistasHora que contenha o número total de horas de cada cientista. Observe que você deverá utilizar pelo menos um tipo de join e que os dados deverão ficar ordenados pelo nome do cientista em ordem crescente.
Nota: 10.0
	
Resposta: CREATE VIEW CientistaHora AS SELET Cientistas.Nome AS 'Cientista', SUM(Projetos.Horas) AS 'Total de horas' FROM atribuidoA JOIN Cientistas ON (Cientista.ID = atribuidoA.Cientista) JOIN Projetos ON (Projetos.Codigo = atribuidoA.Projeto) GROUP BY Cientistas.Nome ORDER BY Cientistas.Nome ASC; SELECT * FROM CientistaHora;
Questão 8/10 - Sistema Gerenciador de Banco de Dados
Para concluir um trabalho iniciado por um dos seus colegas de trabalho você terá que criar uma tabela chamada de GerenteDepartamento que fará o relacionamento estre as duas tabelas que ele já criou com pelo menos duas chaves estrangeiras com deleção em cascata. Observe, que para completar esta tarefa, ele lhe deixou o código de criação das duas tabelas. Como pode ser visto a seguir: 
Observe que também devem ser registradas a data de promoção, data em que o funcionário assumiu a função de gerente e a data final, data em que ele deixa de ser gerente. Por fim, não esqueça que esta tabela deve ter uma chave primária composta.
Para completar esta tarefa, além das datas,
1
2