Plano de Desenvolvimento TDE Banco de Dados II - Junções, Funções e Procedimentos SQL

FACULDADE SANTA TEREZINHA CEST COORDENAÇÃO DO CURSO DE SISTEMAS DE INFORMAÇÃO PLANO DE DESENVOLVIMENTO DO TDE CEST MISSÃO E VISÃO Missão Promover educação superior de excelência formando profissionais competentes e éticos cidadãos comprometidos com o desenvolvimento da sociedade e com o segmento das pessoas com deficiência conscientes de sua função transformadora Visão Ser referência regional em educação superior reconhecida pela excelência de sua atuação e compromisso social DADOS DA DISCIPLINA CÓDIGO NOME CH TOTAL PERÍODO 1168 BANCO DE DADOS II 80h 5º TRABALHO DISCENTES EFETIVOS TDE OBJETIVO GERAL Capacitarse nos conceitos de junções funções e procedimentos em SQL para trabalhar e manipular dados de tabelas de forma eficiente ESPECÍFICOS Compreender e aplicar as junções INNER JOIN LEFT JOIN e RIGHT JOIN para combinar dados de duas ou mais tabelas Criar funções escalar e de tabela para executar cálculos e operações em colunas ou conjuntos de colunas Criar procedimentos para automatizar tarefas e processos no banco de dados aumentando a eficiência e reduzindo erros Aplicar as técnicas aprendidas em exercícios práticos utilizando bancos de dados reais e simulados NOÇÕES TEÓRICAS NECESSÁRIAS PARA O TDE Junções Views Restrições de Integridade Tipos de Dados Funções Procedimentos Gatilhos Views Recursivas PostgreSQL PLPGSQL PROCEDIMENTOS METODOLÓGICOS E CRONOGRAMA DAS ATIVIDADES Desenvolver a solução das atividades realizadas em sala de aula em linguagem SQL compatíveis com o banco de dados PostgreSQL e linguagem PLPGSQL Entregar os comandos em documento de texto de preferência Google Docs pelo Classroom até a data de 18 de Abril de 2023 RECURSOS DIDÁTICOS E TECNOLOGIA EDUCACIONAL APLICADA FACULDADE SANTA TEREZINHA CEST COORDENAÇÃO DO CURSO DE SISTEMAS DE INFORMAÇÃO PLANO DE DESENVOLVIMENTO DO TDE Computador Internet Ferramentas da Plataforma Google Educacional Aplicativos PostgreSQL processadores de texto PONTUAÇÃO DO TDE 20 A atividade será desenvolvida individualmente e a pontuação será mensurada de acordo com os seguintes critérios Estruturação 2 Corretude 8 O TDE descrito será pontuado em até 20 da AV1 PONTUAÇÃO MÍNIMA 000 PONTUAÇÃO MÁXIMA 100 REFERÊNCIA PARA O ESTUDO ELMASRI R NAVATHE E Sistemas de banco de dados 7 ed Rio de Janeiro Pearson 2019 SUDARSHAN S SILBERSCHATZ K Sistema de banco de dados 7 ed Rio de Janeiro GEN LTC 2020 São Luís 10 de abril de 2023 Profª Me José Nunes de Oliveira Neto FACULDADE SANTA TEREZINHA CEST COORDENAÇÃO DO CURSO DE SISTEMAS DE INFORMAÇÃO PLANO DE DESENVOLVIMENTO DO TDE ANEXO Slide 426 Aula de Junções 1 Escreva as seguintes consultas em SQL a Exibir uma lista de todos os professores exibindo id nome e o número de ofertas que eles tiveram Certifiquese de mostrar o número de ofertas como 0 para professores que não possuem nenhuma oferta Sua consulta deve usar uma junção externa e não deve usar subconsultas escalares b Escreva a mesma consulta acima mas usando uma subconsulta escalar sem junção externa c Exiba a lista de todas as ofertas de disciplinas oferecidas na primavera de 2018 juntamente com os nomes dos professores que as ofertaram Se uma oferta tiver mais FACULDADE SANTA TEREZINHA CEST COORDENAÇÃO DO CURSO DE SISTEMAS DE INFORMAÇÃO PLANO DE DESENVOLVIMENTO DO TDE de um professor ela deve aparecer tantas vezes no resultado quanto tiver professores Se ela não tiver nenhum professor ela ainda deverá aparecer no resultado com o nome do professor definido como d Exiba a lista de todos os departamentos com o número total de professores em cada departamento sem usar subconsultas escalares Certifiquese de lidar corretamente com departamentos sem instrutores Slide 442 Aula de Views 1 Escreva as seguintes declarações em SQL a Defina uma view credtotal dando o total de créditos adquiridos por estudantes em cada ano b Defina uma view departdisciplinasano que informe a quantidade de disciplinas ofertadas por ano para cada departamento c Defina uma view disciplinasprofessores2018 que exiba todas as disciplinas ofertadas por cada professor no ano de 2018 Slide 523 Aula de Funções e Procedimentos 1 Crie um procedimento aumentosalarioporcent numeric30 nomedepart varchar25 para aumentar o salario de cada professor em porcentagem ex 10 de um determinado departamento 2 Crie uma função convertenotanota varchar2 que fornece uma conversão de notas de letras para pontuações numéricas por exemplo uma nota A corresponde a 8 pontos um A a 75 pontos um B a 7 pontos um B a 65 pontos e assim por diante Os pontos de nota ganhos por um aluno para uma oferta de disciplina são definidos como o número de créditos para a disciplina somado com os pontos numéricos para a nota que o estudante recebeu Slide 541 Aula de Gatilhos 1 Escreva uma trigger para restringir que um professor não oferte disciplinas diferentes em um semestre no mesmo slot de tempo 2 Para manter o atributo totcred da relação estudante faça o seguinte a Modifique o gatilho nas atualizações de faz para lidar com todas as atualizações que podem afetar o valor do totcred b Escreva um gatilho para lidar com inserções na relação faz c Sob quais suposições é razoável não criar gatilhos na relação disciplina 3 Escreva gatilhos para impor a restrição de integridade referencial de oferta à slottempo nas atualizações de oferta e slottempo Slide 553 Aula de Views Recursivas Agregação Avançada 1 Considere o seguinte esquema relacional para uma rede social Usuarioid nome idade Amizadeidusuario1 idusuario2 FACULDADE SANTA TEREZINHA CEST COORDENAÇÃO DO CURSO DE SISTEMAS DE INFORMAÇÃO PLANO DE DESENVOLVIMENTO DO TDE Cada registro na tabela Amizade indica que os usuários com idusuario1 e idusuario2 são amigos Implemente uma view recursiva em SQL que retorne todas as amizades de um determinado usuário incluindo amigos de amigos amigos de amigos de amigos e assim por diante 2 Modifique a consulta recursiva para definir uma relação preqreqprofundidadeiddisciplina idprereq profundidade em que a profundidade do atributo indica quantos níveis de prérequisitos intermediários existem entre a disciplina e o prérequisito Os prérequisitos diretos têm uma profundidade de 0 with recursive recprereqiddisciplina idprereq as select iddisciplina idprereq from prereq union select recprereqiddisciplina prereqidprereq from recprereq prereq where recprereqidprereq prereqiddisciplina select from recprereq Slide 563 Aula de Views Recursivas Agregação Avançada 1 Considere a tabela vendasdatavenda valorvenda idvendedor Escreva uma consulta SQL que classifique os vendedores de acordo com o valor total de vendas realizadas atribuindo a cada vendedor uma posição no ranking Slide 426 1 a SELECT Pid Pnome COUNT AS numeroofertas FROM Professor AS P LEFT JOIN Ministra AS M ON Pid Midprofessor GROUP BY Pid b SELECT Pid Pnome SELECT COUNT FROM Ministras as M WHERE Midprofessor Pid AS numeroofertas FROM Professor AS P c SELECT Oidoferta Osemestre Oano Opredio Onumerosala Pnome FROM Ministra AS M Professor AS P Ofertas AS O WHERE Midoferta Oidoferta AND Midprofessor Pid d SELECT Pnomedepart COUNT AS quantidadeprofessores FROM Professor AS P GROUP BY Pnomedepart Slide 442 1 a create view credtotal as SELECT Enome Fano SUMDcreditos as totalcred from estudante AS E faz AS F disciplina AS D WHERE Fidestudante Eid AND Fiddisciplina Ddisciplina GROUP BY Enome Fano b create view departdisciplinasano as SELECT Pnomedepart Mano COUNT as quantidadedisciplinas FROM professor AS P ministra AS M WHERE Midprofessor Pidprofessor GROUP BY Pnomedepart Mano c create view disciplinasprofessores2018 as SELECT Pnome Dnome as quantidadedisciplinas FROM professor AS P ministra AS M disciplina AS D WHERE Midprofessor Pidprofessor AND Mano 2018 AND Middisciplina Diddisciplina Slide 523 1 create procedure aumentosalario in porcent numeric30 in nomedepart varchar25 begin UPDATE professor AS P SET Psalario salario salario porcent 100 WHERE Pnomedepart nomedepart end 2 create function convertenotanota varchar2 returns numeric21 begin declare notaconvertida numeric21 if nota A then notaconvertida 85 elsif nota A then notaconvertida 8 elsif nota A then notaconvertida 75 elsif nota B then notaconvertida 7 elsif nota B then notaconvertida 65 elsif nota B then notaconvertida 6 elsif nota B then notaconvertida 55 elsif nota B then notaconvertida 5 elsif nota B then notaconvertida 45 elsif nota C then notaconvertida 4 elsif nota C then notaconvertida 35 elsif nota C then notaconvertida 3 elsif nota D then notaconvertida 25 elsif nota D then notaconvertida 2 elsif nota D then notaconvertida 15 else notaconvertida 1 return notaconvertida end Slide 541 1 CREATE FUNCTION verificaslot RETURNS trigger AS BEGIN IF EXISTSSELECT FROM oferta as O1 WHERE O1idoferta NEWidoferta AND EXISTSSELECT FROM ministra as M oferta as O2 WHERE Midprofessor NEWidprofessor AND Msemestre NEWsemestre AND Didoferta O2idoferta AND O1idslottempo O2idslottempo THEN RAISE EXCEPTION Professor já está lotado neste slot RETURN NULL END IF RETURN NEW END CREATE TRIGGER ofertahorarioigual INSTEAD OF INSERT ON ministra FOR EACH ROW EXECUTE PROCEDURE verificaslot 2 a CREATE OR REPLACE FUNCTION atualizartotcred RETURNS TRIGGER AS BEGIN UPDATE estudante SET totcred SELECT SUMcreditos FROM disciplina WHERE disciplinaiddisciplina IN SELECT iddisciplina FROM faz WHERE fazidestudante NEWidestudante AND faznota F WHERE id NEWidestudante RETURN NEW END LANGUAGE plpgsql CREATE TRIGGER creditosganhos AFTER UPDATE OF nota ON faz FOR EACH STATEMENT EXECUTE FUNCTION atualizartotcred b CREATE OR REPLACE FUNCTION inserttotcred RETURNS TRIGGER AS BEGIN IF NEWnota F THEN UPDATE estudante SET totcred totcred creditos FROM disciplina WHERE idestudante NEWidestudante AND iddisciplina NEWiddisciplina END IF RETURN NEW END LANGUAGE plpgsql CREATE TRIGGER atualizacreditos AFTER INSERT ON faz FOR EACH STATEMENT EXECUTE FUNCTION inserttotcred c A disciplina não será alterada após a sua inserção 3 CREATE OR REPLACE FUNCTION atualizaslottempo RETURNS TRIGGER AS BEGIN IF EXISTSSELECT FROM slottempo WHERE NEWidslottempo idslottempo THEN RETURN NEW END IF RETURN NULL END LANGUAGE plpgsql CREATE TRIGGER atualizaslot BEFORE UPDATE OF idslottempo ON oferta FOR EACH STATEMENT EXECUTE FUNCTION atualizaslottempo Slide 553 1 with recursive recamizadeidusuario as select idusuario2 from Amizade WHERE idusuario1 idusuario union select idusuario from Amizade recamizade where recamizadeidusuario Amizadeidusuario1 select from recamizade 2 with recursive recprereqiddisciplina idprereq profundidade as select iddisciplina idprereq produndidade from prereq union select recprereqiddisciplina prereqidprereq from recprereq prereq where recprereqidprereq prereqiddisciplina and preredprofundidade profundidade 1 select from recprereq Slide 563 1 select idvendedor rank over order by SUMvalorvenda desc as srank from vendas group by idvendedor order by srank Slide 426 1 a SELECT Pid Pnome COUNT AS numeroofertas FROM Professor AS P LEFT JOIN Ministra AS M ON Pid Midprofessor GROUP BY Pid b SELECT Pid Pnome SELECT COUNT FROM Ministras as M WHERE Midprofessor Pid AS numeroofertas FROM Professor AS P c SELECT Oidoferta Osemestre Oano Opredio Onumerosala Pnome FROM Ministra AS M Professor AS P Ofertas AS O WHERE Midoferta Oidoferta AND Midprofessor Pid d SELECT Pnomedepart COUNT AS quantidadeprofessores FROM Professor AS P GROUP BY Pnomedepart Slide 442 1 a create view credtotal as SELECT Enome Fano SUMDcreditos as totalcred from estudante AS E faz AS F disciplina AS D WHERE Fidestudante Eid AND Fiddisciplina Ddisciplina GROUP BY Enome Fano b create view departdisciplinasano as SELECT Pnomedepart Mano COUNT as quantidadedisciplinas FROM professor AS P ministra AS M WHERE Midprofessor Pidprofessor GROUP BY Pnomedepart Mano c create view disciplinasprofessores2018 as SELECT Pnome Dnome as quantidadedisciplinas FROM professor AS P ministra AS M disciplina AS D WHERE Midprofessor Pidprofessor AND Mano 2018 AND Middisciplina Diddisciplina Slide 523 1 create procedure aumentosalario in porcent numeric30 in nomedepart varchar25 begin UPDATE professor AS P SET Psalario salario salario porcent 100 WHERE Pnomedepart nomedepart end 2 create function convertenotanota varchar2 returns numeric21 begin declare notaconvertida numeric21 if nota A then notaconvertida 85 elsif nota A then notaconvertida 8 elsif nota A then notaconvertida 75 elsif nota B then notaconvertida 7 elsif nota B then notaconvertida 65 elsif nota B then notaconvertida 6 elsif nota B then notaconvertida 55 elsif nota B then notaconvertida 5 elsif nota B then notaconvertida 45 elsif nota C then notaconvertida 4 elsif nota C then notaconvertida 35 elsif nota C then notaconvertida 3 elsif nota D then notaconvertida 25 elsif nota D then notaconvertida 2 elsif nota D then notaconvertida 15 else notaconvertida 1 return notaconvertida end Slide 541 1 CREATE FUNCTION verificaslot RETURNS trigger AS BEGIN IF EXISTSSELECT FROM oferta as O1 WHERE O1idoferta NEWidoferta AND EXISTSSELECT FROM ministra as M oferta as O2 WHERE Midprofessor NEWidprofessor AND Msemestre NEWsemestre AND Didoferta O2idoferta AND O1idslottempo O2idslottempo THEN RAISE EXCEPTION Professor já está lotado neste slot RETURN NULL END IF RETURN NEW END CREATE TRIGGER ofertahorarioigual INSTEAD OF INSERT ON ministra FOR EACH ROW EXECUTE PROCEDURE verificaslot 2 a CREATE OR REPLACE FUNCTION atualizartotcred RETURNS TRIGGER AS BEGIN UPDATE estudante SET totcred SELECT SUMcreditos FROM disciplina WHERE disciplinaiddisciplina IN SELECT iddisciplina FROM faz WHERE fazidestudante NEWidestudante AND faznota F WHERE id NEWidestudante RETURN NEW END LANGUAGE plpgsql CREATE TRIGGER creditosganhos AFTER UPDATE OF nota ON faz FOR EACH STATEMENT EXECUTE FUNCTION atualizartotcred b CREATE OR REPLACE FUNCTION inserttotcred RETURNS TRIGGER AS BEGIN IF NEWnota F THEN UPDATE estudante SET totcred totcred creditos FROM disciplina WHERE idestudante NEWidestudante AND iddisciplina NEWiddisciplina END IF RETURN NEW END LANGUAGE plpgsql CREATE TRIGGER atualizacreditos AFTER INSERT ON faz FOR EACH STATEMENT EXECUTE FUNCTION inserttotcred c A disciplina não será alterada após a sua inserção 3 CREATE OR REPLACE FUNCTION atualizaslottempo RETURNS TRIGGER AS BEGIN IF EXISTSSELECT FROM slottempo WHERE NEWidslottempo idslottempo THEN RETURN NEW END IF RETURN NULL END LANGUAGE plpgsql CREATE TRIGGER atualizaslot BEFORE UPDATE OF idslottempo ON oferta FOR EACH STATEMENT EXECUTE FUNCTION atualizaslottempo Slide 553 1 with recursive recamizadeidusuario as select idusuario2 from Amizade WHERE idusuario1 idusuario union select idusuario from Amizade recamizade where recamizadeidusuario Amizadeidusuario1 select from recamizade 2 with recursive recprereqiddisciplina idprereq profundidade as select iddisciplina idprereq produndidade from prereq union select recprereqiddisciplina prereqidprereq from recprereq prereq where recprereqidprereq prereqiddisciplina and preredprofundidade profundidade 1 select from recprereq Slide 563 1 select idvendedor rank over order by SUMvalorvenda desc as srank from vendas group by idvendedor order by srank