ap sistema gerenciador de banco de dados

Prévia do material em texto

----------------------------------------------------------------------------------------------------------------- 
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 view s. Muitas vezes, 
temos que criar views para atender a necessidade de aumento de eficiência dos 
Bancos de Dados que Gerenciamos. 
considerando o query SQL a seguir, escreva o seu proprio query SQL de forma a criar 
uma view chamada de ProjetosHora que contenha o nome dos projetos e o numero 
de horas gasto em cada um. Observe que os dados devem estar ordenados em ordem 
crescente pelo nome do projeto. 
----------------------------------------------------------------------------------------------------------------- 
CREATE VIEW ProjetosHoras 
AS SELECT Projetos.Nome, SUM(Projetos.Horas) 
FROM Projetos GROUP BY Projetos.Nome 
ORDER BY Projetos.Nome ASC; 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
Entre as operações interessantes que podemos fazer com os dados armazenados 
esta 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 numeros de idenificação 
dos recursos usados em uma construção. Como esta tabela deve ser o mais simples 
possivel você terá que armazenar apenas dois campos: o ID do recurso nesta tabela, 
para referencias e o proprio codigo do recurso. Observe tambem que estes dois 
campos devem armazenar numeros inteiros. Certifique-se, contudo, que esta tabela 
tenha uma chave primaria em ID e um Index em codigo. 
 
Para completar esta tarefa digite o codigo do query SQL que atende as especificações 
deste projeto e, quando executado, criará a tabela desejada. 
----------------------------------------------------------------------------------------------------------------- 
CREATE TABLE Recurso ( 
ID SMALLINT UNSIGNED NOT NULL, 
codigo SMALLINT UNSIGNED NOT NULL, 
PRIMARY KEY (id), 
INDEX (codigo) 
) 
----------------------------------------------------------------------------------------------------------------- 
 
 
 
----------------------------------------------------------------------------------------------------------------- 
O SQL é, uma ultima analise, 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 Function. Um procedure é um conjunto de comandos 
SQL que serão compilados e armazenados em forma binária devolva o total de 
cientistas registrados neste banco de dados. 
----------------------------------------------------------------------------------------------------------------- 
DROP PROCEDURE IF EXISTS NumeroCientistas$$ 
CREATE PROCEDURE NumeroCientistas() 
BEGIN 
 SELECT count(ID) from Cientistas; 
END$$ 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
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, será necessário relacionar o empregado 
com o departame nto. Uma vez que tenha entendido a relação entre os dados, crie a 
tabela necessária. 
----------------------------------------------------------------------------------------------------------------- 
CREATE TABLE GerenteDepartamento ( 
emp_no INT NOT NULL, 
depat_no CHAR(4) NOT NULL, 
dataPromocao DATE NOT NULL, 
dataFinal DATE NOT NULL, 
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, 
FOREIGN KEY (depat_no) REFERENCES departments (depat_no) ON DELETE 
CASCADE, 
PRIMARY KEY (emp_no,depat_no) 
); 
----------------------------------------------------------------------------------------------------------------- 
 
 
 
 
 
 
----------------------------------------------------------------------------------------------------------------- 
O trabalho de gestão e manutenção de banco de dados, eventualmente, nos 
apresenta desafios interessantes, não é raro temos acesso apenas aos dados 
armazenados e a estrutura da tabela. E temos que desenvolver novos Scripts SQL 
para a solução de problemas inéditos. Neste caso, o primeiro passo é sempre analisar 
um fragmento da tabela para perceber a semântica contida nos dados. Sendo assim, 
considere o seguinte quadro como sendo uma amostra dos dados contidos na tabela 
Livros em um banco de dados sobre sua responsabilidade. 
 
Apesar deste fragmento ser pequeno, esta tabela contém várias dezenas de 
Gigabytes de dados tornando suas operações excessivamente lentas. O analista de 
banco de dados solicitou que você criasse uma view chamada livros_Idioma 
contendo os idiomas e o total de livros por idioma (pub_lang) acreditando que essa 
view irá ter um impacto positivo na operação diária do banco de dados.Para resolver 
esta questão você deve escrever um query SQL que atenda as especificações 
passadas e crie a view necessária: 
----------------------------------------------------------------------------------------------------------------- 
CREATE VIEW livros_idioma 
AS SELECT pub_lang, count(*) 
FROM Livros 
GROUP BY Pub_lang 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
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. 
----------------------------------------------------------------------------------------------------------------- 
SELECT S.Nome, P.Nome, P.Horas 
FROM Cientistas S 
 INNER JOIN AtribuidoA A ON S.ID=A.Cientista 
 INNER JOIN Projetos P ON A.Projeto=P.Codigo 
 ORDER BY P.Nome ASC, S.Nome ASC; 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Utilizamos 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. 
----------------------------------------------------------------------------------------------------------------- 
DELIMITER $$ 
 
DROP PROCEDURE IF EXISTS MembroDe$$ 
 
CREATE PROCEDURE MembroDe(IN quem TEXT) READS SQL DATA 
BEGIN 
Select ID, Nome, Parceiros.OrgaoID,NomeTipo 
 from Parceiros left Join TipoOrg 
 on Parceiros.OrgaoID = TipoOrg.OrgaoID 
 Where Nome = quem; 
END;$$ 
 
DELIMITER ; 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
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 vo cê deverá utilizar 
pelo menos um tipo de join e que os dados deverão ficar ordenados pelo nome do 
cientista em ordem crescente. 
----------------------------------------------------------------------------------------------------------------- 
Create view CientistasHoras as 
SELECT S.Nome, P.Horas 
FROM Cientistas S 
 INNER JOIN AtribuidoA A ON S.ID=A.Cientista 
 INNER JOIN Projetos P ON A.Projeto=P.Codigo 
ORDER BY S.Nome ASC; 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
Seu supervisor lhe solicitou a criação de um Trigg er para uma determinada tabela em 
um banco de dados SQL com as seguintes funcionalidades: antes que qualquer valor 
inteiro seja inserido em um campo de uma determinada tabela, este valor acrescido 
de 4 unidades deverá ser inserido em outra tabela. Além disso, caso exista este 
mesmo valor em alguma linha de uma terceira tabela, esta linha deverá ser deletada. 
Por fim, caso este valor exista em uma quarta tabela, este valor deverá ser atualizado 
para o resultado da soma do valor inicial com 50 unidades. 
 
Para testar o seu Trigger, ele lhe indicou o seguinte query 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 tabelas necessárias seguem a orden numérica 
apresentada no script SQL acima. 
 
----------------------------------------------------------------------------------------------------------------- 
DELIMITER $$ 
 
DROP TRIGGER TesteTrigger; 
 
CREATE TRIGGER TesteTrigger BEFORE INSERT ON TesteTrigger1 
 FOR EACH ROW BEGIN 
 INSERT INTO TesteTrigger2 SET Valor2 = NEW.Valor1 + 4; 
 DELETE FROM TesteTrigger3 WHERE Valor3 = NEW.Valor1; 
 UPDATE TesteTrigger4 SET Valor4 = Valor4 + 50 WHERE Valor4 = NEW.Valor1; 
END;$$ 
 
DELIMITER ; 
----------------------------------------------------------------------------------------------------------------- 
 
----------------------------------------------------------------------------------------------------------------- 
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. Consi derando 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. 
----------------------------------------------------------------------------------------------------------------- 
SELECT 
 NomeConsumidor, CustomerLevel(limiteCredito) 
FROM 
 Consumidores 
ORDER BY NomeConsumidor; 
-----------------------------------------------------------------------------------------------------------------