Trabalho 1: SQL e diagrama ER Data de entrega: 02/05/2025 as 23:59h Objetivo: O objetivo deste trabalho e' se familiarizar com a linguagem SQL e com diagramas ER e serve como nota do trabalho T1. Sera utilizado um BD sobre copas do mundo. BD worldcup Ele e' um subconjunto da base disponivel em https://www.github.com/jfjelstul/worldcup. Ele conte'm informacoes sobre 21 torneios da Copa do Mundo (1930-2018). O banco de dados inclui 13 tabelas, que envolvem: 1) componentes dos torneios: torneios (tournaments), incluindo o pais-sede, o vencedor, as datas do torneio e informacoes sobre o formato de cada torneio; as confederacoes da FIFA (confederations); times (teams); jogadores (players); tecnicos (managers), incluindo sua equipe e pais de origem; arbitros (referees), incluindo seu pais de origem e confederacao; os premios individuais concedidos aos jogadores em cada torneio (awards). 2) mapeamentos entre componentes e classificacao: equipes que se classificaram (qualified_teams), que indica o desempenho de cada equipe no torneio; um conjunto de dados sobre os times (squads), que indica o nome, a posicao e o numero da camisa de cada jogador; um conjunto de dados sobre nomeacoes de tecnicos (manager_appointments), que indica a equipe e o pais de origem de cada te'cnico; e um conjunto de dados sobre nomeacoes de arbitros (referee_appointments), que indica o pais de origem e a confederacao de cada arbitro. 3) paises sede e premios: os paises-sede (host_countries), incluindo o desempenho de cada pais-sede; e um conjunto de dados sobre todos os premios individuais de jogadores entregues em cada torneio (award_winners). Execucao do trabalho O trabalho devera ser realizado com o SGBD SQLite3 que pode ser encontrado facilmente para instalacao em qualquer sistema operacional. Verifique se o seu computador ja possui o SGBD instalado digitando sqlite3 no terminal. Deve aparecer a mensagem abaixo: $ sqlite3 SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> Para sair: sqlite> .quit Especificacao: O trabalho possui 8 questoes: 7 consultas SQL valendo 1.0 ponto cada e uma questao sobre ER valendo 3.0 pontos. Para cada questao voce devera apresentar uma consulta SQL para executar no SGBD SQLite. A entrega devera seguir o formato descrito abaixo. Entrega: Este arquivo deve ser enviado por e-mail para carmemhara@ufpr.br com o assunto "CI218-Trab1" (exatamente). IMPORTANTE: Minha caixa de email usa o assunto do email como filtro. O trabalho e' individual. Formato de entrega: O formato do arquivo deve ser GRR000000.tar.gz contendo a sua matricula. O arquivo deve conter as consultas SQL (um arquivo por consulta) e um arquivo no formato pdf com o diagrama ER (chamado de er.pdf). Segue abaixo o comando para gerar os arquivos com as consultas SQL: $ mkdir GRR000000 $ cd GRR000000 $ touch q1_test.sql \ q2_brazil_campeao.sql \ q3_paises_campeoes.sql \ q4_brazil_premiados.sql \ q5_camise10_2022.sql \ q6_cinco_finais.sql \ q7_maior_qtd_finais.sql Carregando e verificando o BD: Para fazer o download do BD worldcup para este trabalho use o comando: $ wget http://www.inf.ufpr.br/carmem/ci218/trab1/worldcup.db Apos baixar o BD, opcionalmente voce podera verificar sua integridade com o seguinte commando (os valores devem ser iguais): $ cksum tpch.db 430307900 3997696 worldcup.db O BD tem tamanho aproximado de 3MBytes e deve ser acessado no SQLite de acordo com o comando abaixo: $ sqlite3 worldcup.db Voce pode obter o esquema do BD com o commando abaixo: sqlite> .tables award_winners managers squads awards players teams confederations qualified_teams tournaments host_countries referee_appointments manager_appointments referees Voce pode obter o esquema de cada tabela com o commando .schema. Por exemplo, para obter o esquema da tabela awards, digite: sqlite> .schema awards CREATE TABLE awards( award_id TEXT NOT NULL, award_name TEXT, award_description TEXT, year_introduced INTEGER, PRIMARY KEY (award_id) ); Segue o exemplo de uma linha da tabela award (note que os atributos sao separado por "|"): A-1|Golden Ball|best player|1978 Consultas SQL: Abaixo esta a especificacao de cada consulta SQL que deve ser entregue. A primeira consulta serve apenas para verificar se a resposta da consulta corresponde ao esperado. q1_teste: Obtenha os nomes de todos os premios existentes. sqlite> select distinct(award_name) from awards; Golden Ball Silver Ball Bronze Ball Golden Boot Silver Boot Bronze Boot Golden Glove Best Young Player LEMBRETE: Voce deve colocar SOMENTE o comando SQL no arquivo "q1_test.sql", SEM o resultado da consulta. Este arquivo devera ser colocado no subdiretorio com seu GRR (por ex., GRR000000). Nao esqueca que o ";" finaliza o comando SQL. q2_brazil_campeao: Para as copas nas quais o "Brazil" foi campeao: obter o nome do campeonato e o pais onde ele foi disputado. q3_paises_campeoes: Considerando as copas masculinas e femininas, obtenha quantas vezes cada pais foi campeao do campeonato. O resultado so' precisa conter os paises que ja foram campeoes pelo menos uma vez. q4_brazil_premiados: Dos jogadores brasileiros premiados (award_winners) em alguma Copa: Obtenha o nome do campeonato, nome do premio e nome do jogador (family name). q5_camisa10_2022: Obter o nome do jogador e o pais dos jogadores que vestiram a camisa 10 na Copa de 2022. q6_cinco_finais: Para os paises que ja estiveram em pelo menos 5 finais de Copa: obter o nome do pais e a quantidade de finais em que ele jogou. q7_maior_qtd_finais: Obter o nome do pais que esteve na maior quantidade de finais de Copa. Diagrama ER: Obtenha um diagrama ER cujo mapeamento poderia gerar o esquema relacional do BD worldcup. O diagrama deve ser apresentado no formato pdf (arquivo chamado de er.pdf). Sugestao: Voce pode utilizar a ferramenta BR Modelo e verificar se o mapeamento gerado coincide com o esquema relacional do BD. https://www.brmodeloweb.com/lang/pt-br/index.html