SISTEMA GERENCIADOR DE BANCO DE DADOS Atividade pratica

Prévia do material em texto

Questão 1/10 - Sistema Gerenciado r de Banco de Da dos 
 
Uma das principai s razões p ara a cr iação de uma view e stá na necessidade de aumentar a e ficiê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 u m cu sto de p rocessamento igual ao da consulta a qualquer tabela. Considerando o query SQL a 
seguir, crie o seu próp rio query para criar uma view chamad a de CientistasHora que co ntenha o número total de horas 
de cada cientista. Observe que você deverá utilizar pelo menos um tipo de jo in e q ue os d ados deverão ficar ord enados 
pelo nome do cientista e m ordem crescente.
Resposta: 
DROP VIEW IF EX ISTS CientistasHora ; 
CREATE VIEW Cienti stasHora AS 
SELECT C.No me, A.Projeto, P.H oras FROM Cientistas C 
LEFT JOIN Atribuido A A ON (C.ID = A.Cient ista) 
LEFT JOIN Proj etos P ON (A.Projet o = P.Codigo); 
SELECT Nome, Ho ras FROM CientistasH ora ORDER BY No me ASC;
Questão 2/10 - Sistema Gerenciado r de Banco de Da dos 
 
O trab alho de gestão e manut enção d e banco de dado s, e ventualmente, nos apresenta desafios interessantes, não é raro 
temos ace sso apenas aos dado s ar mazenados e a estrutura da tabela. E te mos que desen volver novos Scripts SQL para a 
solução d e pr oblemas inéditos . Neste caso, o primeiro passo é se mpre analisar um fra gmento da tabela p ara perceber a 
semântica contida nos dados. Sendo assim, con sidere o seg uinte quadro co mo sendo um a amostra dos dad os contidos 
na tabela Livros em um banco de dados sobre sua responsabilidad e. Apesar deste fragmento ser peq ueno, esta tabela 
contém vária s dezena s de Gigab ytes de dad os torna ndo suas operações excessivamente lentas. O analista de banco de 
dados solicitou que você criasse uma view chamada liv ros_Id ioma contendo os idiomas e o total de livros po r idio ma 
(pub_lang) acreditando q ue essa view irá ter um impacto positivo na operação diária do banco de dad o s. Para resolver 
esta questão você deve e screver um q uery SQL que ate nda as especificações pas sadas e crie a view necessária: 
Resposta: 
DROP VIEW IF EX ISTS 
livros_Idioma; 
CREATE VIEW livro s_Idioma AS 
SELECT pub_lang, COUNT(pub_lang) 
FROM Livros GROUP B Y pub_lang
Questão 3/10 - Sistema Gerenciado r de Banco de Da dos 
 
A grande diferença e ntre uma proced ure e uma function é que esta últi ma devolve d ados para o siste ma e, pode ser 
facilmente utilizada par a a recuperaç ão de informação e m um Banco de Dado s. Consid erando o có digo da fu nction 
armazenada e m um d eterminado banco de dados, escreva o query SQL que reto rne uma lista de nomes de cons umidores 
(nomeConsumidor) e seu nível de crédito sab endo que a tabela Consumidores tem o campo NomeCo nsumidor e ca mpo 
limiteCredito. Observe também que o r esultado deve ser o rdenado pelo campo NomeCon sumid or. Para completar esta 
tarefa você d everá escrever o qu ery SQL que atende a s especificações ac ima usando os d ados contidos na Fu nction o u 
especificados no enunciado . 
 
Resposta: 
 
DELIMITER $$ 
CREATE FUNCT ION nomeConsumidor( ) RETURNS 
LIST DETERM INISTIC 
BEGIN 
 DECLARE nList LI ST; 
 
 SELECT No meConsumidor, selectNivelCo nsumidor(limiteCredito ) 
FROM Consu midores 
RETURN nList; 
END; 
 
 
Questão 4/10 - Sistema Gerenciado r de Banco de Da dos 
 
Para concluir um trabalho ini ciado por um dos seus coleg as de trabalho você terá que criar uma tabela chamada de 
GerenteDepartamen to 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 p ara completar esta tarefa, ele lh e deixou o có digo de cr iação d as 
duas tabelas. Como pod e ser visto a seguir: 
Observe que também d evem ser registradas a data de pro moção, data em que o funcionário assumiu a função de gerente 
e a data final, data e m q ue ele deixa d e ser gerente. Po r fim, não esq ueça que esta tab ela deve ter u ma cha ve primária 
composta. P ara co mpletar esta tarefa, além da s datas, será necessário relacionar o e mpregado co m o departa mento. Uma 
vez que tenha entendido a relação entre os dad os, crie a tabela necessária . 
 
Questão 7/10 - Sistema Gerenciado r de Banco de Da dos 
 
Uma d as pr incipais razões para a pop ularidade do modelo relacional e da linguage m SQ L é a facilidad e d e recuperação 
de informações. O desenvolvedor pode, sem muito esforço, cruzar informações em tabelas diferentes ou, criar 
comandos aritiméticos q ue pr emitem a nalisar os dados armazenados e descobrir nova s informações. Considerando o 
query SQL a se guir, que cria um conjunt o de tabelas em um ba nco de dados, escreva o seu pró prio query SQL q ue 
devolva u ma lista de todos os cientistas, seus p rojetos e o número de horas empregado por cada um ne ste pr ojeto. Não 
se esqueça que lista deve ser ordenada por nome de projeto e por no me de cientista sempre em ordem crescente. 
Resposta: 
 
SELECT p.No me, c.Nome, a.Projet o, p.Horas 
FROM cientistas c 
LEFT JOIN Atribuido A a on (c.ID = a. Cientista) 
LEFT JOIN Proj etos p on (a.Projet o = p.Codigo ) 
ORDER BY p.No me ASC, c.No me ASC; 
 
 
Questão 8/10 - Sistema Gerenciado r de Banco de Da dos 
 
Uma estraté gia eficiente d e aumentar a operacionalidade de um banco de dad os é a criação de views. Podemos entender 
uma view co mo sendo uma tab ela especial, quase virtual, q ue não tê m dados próprios mas que existe no banco po r meio 
de algum tipo de relacionamento criado entre os dados das outras tabelas. Ainda que simplista, esta definição p ermite 
vislumbrar a utilidade das vi ews. Muitas vezes, temos q ue criar views para atender a necessidad e de aumento de 
eficiência dos Bancos de Dado s que Gere nciamos. Sendo assim, considera ndo o q uery SQL a seguir, escreva o seu 
próprio query SQL d e forma a criar uma view chamada de ProjetosHora que contenha o nome dos proj etos e o número 
de horas gasto em cada um. O bserve que os dad os devem estar ordenad os em ordem crescente pelo no me do projeto. 
Resposta: 
DROP VIEW IF EX ISTS 
 ProjetosHora ; 
CREATE VIEW Pro jetosHora AS 
SELECT p.No me, p.Horas FROM Projetos p 
LEFT JOIN Proj etos ON (Cient istas.ID = AtribuidoA.Ci entista) 
LEFT JOIN Proj etos ON (AtribuidoA.Projet o = Projetos.Codigo) ; 
SELECT Nome, Ho ras FROM Pro jetosHora ORDER BY No me ASC;
Questão 9/10 - Sistema Gerenciado r de Banco de Da dos 
 
Seu super visor lhe solicitou a criação de um Trigg er para uma deter minada tabela em um b anco de d ados SQ L co m as 
seguintes funcionalidades: a ntes que q ualquer valor inteiro s eja inserido e m um campo de uma deter minada tabela, este 
valor acrescid o de 4 unidades deverá ser inserido em outra t abela. Além disso, caso e xista este me smo valor em al guma 
linha de uma terce ira tabela, esta li nha deverá ser deletada. P or fim, ca so este valor e xista e m uma q uarta tab ela, este 
valor deverá ser atualizado para o resultado d a so ma do valor inicial com 5 0 unidades. Para testar o seu Trigger,ele lhe 
indicou o seguinte q uery SQL para a criação das quatro tabelas de teste: 
Para completar esta tarefa você terá que escrever o código que cria o Trigger especificado sabendo que a tabela 
associada a este Trigger será a tabela TesteTrigger1 e que todas as outras tabela s neces sárias se guem a or den nu mérica 
apresentada no script S QL acima 
Resposta: DELIMITER $$ CREATE TRIGGER T1 AFTER Insert TesteTrigger1 FOR EACH ROW BEGIN set @valor1 = NEW.TesteTrigger1.Valor1; set @valor2 = NEW.TesteTrigger2.Valor1; set @valor3 = NEW.TesteTrigger3.Valor1; set @valor4 = NEW.TesteTrigger4.Valor1; SET @valor = NULL; if(NEW. Valor1 IS NOT NULL) THEN
uestão 10 /10 - Sistema Gerenciador de Banco de Da dos Utilizamod proced ures para automatizar processos repetitivos na gestão do nosso Banco de Dados. Suponha, por exemplo, que você p recisará calcular a média em di versas tabelas, o u q ue far á um arred ondamento qualquer em todas elas, ou ainda, que precisará faz er contas com datas. Todos estes casos, são casos o nde o uso de uma procedure pode poupar trabalho e prob lemas. Consider ando o query SQL a seguir, que cria um co njunto de tabelas e m um banco de dados. Escreva o seu próprio query SQL p ara criar um procedu re 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 or ganização. 
Resposta: DELIMITER 
// 
DROP PROCEDUR E IF EXISTS nome_org_id; // CREATE PROCEDUR E nome_org_id(IN in_ nome varc har(60)) BEGIN SELECT p.Nome , t.No meTipo, p.Orgao ID FROM Parceiros p INNER JOIN tipoorg t on (p.OrgaoId = t.O rgaoId) WHERE p.Nome = in_no me; END // DELIMITER ; SET @in_nome = " Teste Nome"; CALL nome_o rg_id(@in_no me);