Ao iniciar este projeto, você concorda com as diretrizes do Código de Ética e Conduta e do Manual da Pessoa Estudante da Trybe.
Você já usa o GitHub diariamente para desenvolver os exercícios, certo? Agora, para desenvolver os projetos, você deverá seguir as instruções a seguir. Fique atento a cada passo e, se tiver qualquer dúvida, nos envie por Slack! #vqv 🚀
Aqui você vai encontrar os detalhes de como estruturar o desenvolvimento do seu projeto a partir desse repositório, utilizando uma branch específica e um Pull Request para colocar seus códigos.
- Boas vindas ao repositório do projeto One For All!
- SUMÁRIO
- Habilidades
- Entregáveis
- Como desenvolver
- Requisitos do projeto
- Avisos Finais
Neste projeto, verificamos se voce é capaz de:
-
Modelar um banco de dados
-
Identificar entidades, atributos e relacionamentos
-
Aplicar a 1ª, 2ª e 3ª Forma Normal
-
Criar tabelas
Para entregar o seu projeto você deverá criar um Pull Request neste repositório.
Lembre-se que você pode consultar nosso conteúdo sobre Git & GitHub e nosso Blog - Git & GitHub sempre que precisar!
Hoje, no projeto One For All, você usará uma tabela para revisar e consolidar todos os principais conceitos vistos até o momento.
Você receberá uma tabela não normalizada, a qual deverá ser normalizada e populada, para que você possa executar queries sobre ela com o intuito de encontrar as informações solicitadas.
Normalize uma tabela na terceira forma normal e acesse as informações desta tabela através de queries
utilizando SQL
.
- Serão
2
dias de projeto. - Data de entrega para avaliação final do projeto:
19/04/2022 14:00
.
- Clone o repositório
git clone [email protected]:tryber/sd-017-mysql-one-for-all.git
.- Entre na pasta do repositório que você acabou de clonar:
cd sd-017-mysql-one-for-all
- Instale as dependências
npm install
- Crie uma branch a partir da branch
master
- Verifique que você está na branch
master
- Exemplo:
git branch
- Exemplo:
- Se não estiver, mude para a branch
master
- Exemplo:
git checkout master
- Exemplo:
- Agora, crie uma branch onde você vai guardar os
commits
do seu projeto- Você deve criar uma branch no seguinte formato:
nome-de-usuario-nome-do-projeto
- Exemplo:
git checkout -b seunome-mysql-one-for-all
- Você deve criar uma branch no seguinte formato:
- Para cada exercício você deve criar um novo arquivo sql seguindo a seguinte estrutura:
- desafio1.sql, desafio2.sql, desafio3.sql, desafio4.sql, ..., desafioN.sql
- Adicione as mudanças ao stage do Git e faça um
commit
- Verifique que as mudanças ainda não estão no stage
- Exemplo:
git status
(deve aparecer o arquivo que você alterou como desafio1.sql)
- Exemplo:
- Adicione o novo arquivo ao stage do Git
- Exemplo:
git add .
(adicionando solução para desafio 1)git status
(deve aparecer listado o arquivo desafio1.sql em verde)
- Exemplo:
- Faça o
commit
inicial- Exemplo:
git commit -m 'iniciando o projeto MySQL One For All'
(fazendo o primeiro commit)git status
(deve aparecer uma mensagem tipo nothing to commit )
- Exemplo:
-
Faça
commits
das alterações que você fizer no código regularmente -
Lembre-se de sempre após um (ou alguns)
commits
atualizar o repositório remoto -
Os comandos que você utilizará com mais frequência são:
git status
(para verificar o que está em vermelho - fora do stage - e o que está em verde - no stage)git add
(para adicionar arquivos ao stage do Git)git commit
(para criar um commit com os arquivos que estão no stage do Git)git push -u origin nome-da-branch
(para enviar o commit para o repositório remoto na primeira vez que fizer opush
de uma nova branch)git push
(para enviar o commit para o repositório remoto após o passo anterior)
Para cada desafio, crie um arquivo SQL e inclua sua solução nele.
-
Leia a pergunta e crie um arquivo na pasta raiz do projeto chamado
desafioN.sql
, em que N é o número do desafio (exemplo: desafio3.sql). Para o primeiro desafio, é preciso criar também o arquivo de configuraçõesdesafio1.json
também na pasta raiz do projeto. -
O arquivo deve conter apenas o código SQL do desafio resolvido. Não se esqueça de incluir o ponto e vírgula (";") no final de suas queries e também de colocar o nome do banco_de_dados.tabela por completo, como no exemplo a seguir:
SELECT * FROM SpotifyClone.artista;
desafionN.sql
,
USE SpotifyClone;
-
Atente-se a detalhes de digitação em seu código. Qualquer diferença em nomes, apelidos, ou CAIXA ALTA e caixa baixa podem invalidar suas respostas.
-
Faça isso até finalizar todos os desafios e depois siga as instruções de como entregar o projeto em Instruções para entregar seu projeto
-
Para entregar o seu projeto você deverá criar um Pull Request neste repositório. Este Pull Request deverá conter tanto o arquivo de configurações
desafio1.json
do primeiro desafio quanto os arquivosdesafio1.sql
,desafio2.sql
e assim por diante até odesafio11.sql
, que conterão seu códigoSQL
de cada desafio, respectivamente.
Qualquer dúvida, procure a monitoria. Lembre-se que você pode consultar nosso conteúdo sobre Git & GitHub sempre que precisar!
Você receberá uma tabela não normalizada semelhante ao que poderia ser uma versão simplificada do banco de dados do Spotify. O trabalho consistirá de duas partes:
-
Normalizar essa tabela, criar o schema no seu banco de dados local e populá-lo;
-
Realizar os desafios no seu banco normalizado e populado.
Aviso: Todos os dados da tabela abaixo são fictícios e foram gerados aleatoriamente através de ferramentas, com o intuito de deixar o projeto mais real para vocês 😉. Quaisquer similaridades com informações reais são meras coincidências.
Observação: O banco de dados real do Spotify é muito mais completo e complexo que o mostrado abaixo. No entanto, use APENAS as informações passadas e solicitadas aqui. Não crie dados que não foram solicitados.
Abaixo você pode visualizar e baixar uma planilha com as tabelas que deverão ser normalizadas:
Antes de tudo, você deverá modelar e normalizar essas tabelas para a 3° Forma Normal. Monte quantas planilhas e tabelas forem necessárias para criar essa versão normalizada, desde que você siga as regras de negócio. Não é necessário criar o código SQL neste momento.
Embora não seja necessário, é recomendado que você crie suas planilhas na sequência 1ª -> 2ª -> 3ª Forma Normal.
Caso haja dúvidas, consulte o material já passado até hoje para te auxiliar.
Seu banco de dados deve seguir as regras de negócio e ser capaz de recuperar:
-
Informações sobre quais planos estão disponíveis e seus detalhes;
- Cada pessoa usuária pode possuir apenas um plano.
-
Informações sobre todas as pessoas artistas;
- Uma pessoa artista pode ter vários álbuns;
- Uma pessoa artista pode ser seguida por várias pessoas usuárias.
-
Informações sobre todos os álbuns de cada artista;
- Para fins deste projeto, considere que cada álbum possui apenas uma pessoa artista como principal;
- Cada álbum possui várias canções.
-
Informações sobre todas as canções de cada álbum;
- Para fins deste projeto, considere que cada canção está contida em apenas um álbum.
-
Informações sobre todas as pessoas usuárias, seus planos, seu histórico de reprodução e pessoas artistas seguidas.
- Uma pessoa usuária pode possuir apenas um plano;
- Cada música do histórico de reprodução pode aparecer uma única vez por pessoa (para simplificar, considere que o objetivo do histórico é saber quais canções já foram reproduzidas e não quantas vezes foram reproduzidas);
- Uma pessoa usuária pode seguir várias pessoas artistas, mas cada pessoa artista pode ser seguida apenas uma vez por pessoa usuária.
Algumas coisas devem seguir um padrão pré-estabelecido para que os testes de correção funcionem adequadamente:
- Ao modelar o banco de dados no desafio 1:
- Quando for preciso que uma
coluna_a
referencie uma colunacoluna_b
em outra tabela, é necessário que nacoluna_a
haja restrição de foreign key e quecoluna_b
seja uma primary key;
-
Todos os desafios que pedirem para criar uma query devem possuir no seu arquivo
.sql
correspondente somente a query solicitada. -
Para executar localmente os testes, é preciso escrever o seguinte no seu terminal:
MYSQL_USER=<SEU_NOME_DE_PESSOA_USUARIA> MYSQL_PASSWORD=<SUA SENHA> HOSTNAME=<NOME_DO_HOST> PORT=<PORTA> npm test
-
Ou seja, suponha que para poder acessar a base de dados feita neste projeto você tenha
root
como seu nome de pessoa usuária,password
como senha,localhost
como host e1337
como porta. Logo, você executaria:MYSQL_USER=root MYSQL_PASSWORD=password HOSTNAME=localhost PORT=1337 npm test
-
Usando o exemplo anterior de base, suponha que você não tenha setado uma senha para
root
e esteja usando a porta padrão (3306). Neste caso, você executaria:MYSQL_USER=root MYSQL_PASSWORD= HOSTNAME=localhost PORT= npm test
Dica: variáveis de ambiente definidas na mesma linha do comando valem apenas para aquele comando. Se preferir, você pode exportar as variáveis de ambiente para toda a sessão (todos os comandos até você fechar aquele terminal). Por exemplo:
export MYSQL_USER=root MYSQL_PASSWORD=password HOSTNAME=localhost PORT=1337
E depois disso você só precisa rodar npm test
quando for testar os projetos.
docker run --name meu-mysql-5_7 -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql:5.7
O que está sendo feito
> 👉 ideia geral: - Baixa e executa uma imagem _docker_ do MySQL na versão 5.7.
👉 flag --name:
- Define um nome para o nosso container: "meu-mysql-5_7".
👉 flag -e:
- Define definimos a nossa variável de ambiente "MYSQL_ROOT_PASSWORD" com o valor "1234".
👉 flag -d:
- Define que o container rode em segundo plano.
👉 flag -p:
- Mapeia uma porta local a uma porta do container.
👉 mysql:5.7:
- Define qual versão da imagem mySQL queremos, no caso, a 5.7, que é a esperada pelo avaliador.
Utilizando o comando acima, para executar os testes localmente basta digitar no terminal:
MYSQL_USER=root MYSQL_PASSWORD=1234 HOSTNAME=localhost npm test
-
Crie um banco com o nome de
SpotifyClone
. -
Providencie as queries necessárias para criar tabelas normalizadas que atendam aos requisitos descritos na seção anterior;
-
Providencie as queries necessárias para popular as tabelas de acordo com os dados listados na seção anterior;
-
Crie um arquivo de configurações
desafio1.json
, que mapeará em qual tabela e coluna se encontram as informações necessárias para a avaliação automatizada deste desafio. As configurações devem possuir o seguinte formato:
{
"coluna_usuario": "nome-da-coluna",
"tabela_que_contem_usuario": "nome-da-tabela-que-armazena-a-coluna",
"coluna_plano": "nome-da-coluna",
"tabela_que_contem_plano": "nome-da-tabela-que-armazena-a-coluna",
"coluna_historico_de_reproducoes": "nome-da-coluna",
"tabela_que_contem_historico_de_reproducoes": "nome-da-tabela-que-armazena-a-coluna",
"coluna_seguindo_artistas": "nome-da-coluna",
"tabela_que_contem_seguindo_artistas": "nome-da-tabela-que-armazena-a-coluna",
"coluna_artista": "nome-da-coluna",
"tabela_que_contem_artista": "nome-da-tabela-que-armazena-a-coluna",
"coluna_album": "nome-da-coluna",
"tabela_que_contem_album": "nome-da-tabela-que-armazena-a-coluna",
"coluna_cancoes": "nome-da-coluna",
"tabela_que_contem_cancoes": "nome-da-tabela-que-armazena-a-coluna"
}
Essa configuração deve ser feita baseada no seu banco de dados após a normalização. Ou seja, se você criou uma tabela chamada users
que possui a coluna name
, você substituiria "coluna_usuario"
e "tabela_que_contem_usuario"
da seguinte forma:
{
"coluna_usuario": "name",
"tabela_que_contem_usuario": "users",
...
}
- Salve as queries criadas no arquivo
desafio1.sql
. Seu código deverá ser similar ao seguinte:
DROP DATABASE IF EXISTS SpotifyClone;
CREATE DATABASE SpotifyClone;
CREATE TABLE SpotifyClone.tabela1(
coluna1 tipo restricoes,
coluna2 tipo restricoes,
colunaN tipo restricoes,
) engine = InnoDB;
CREATE TABLE SpotifyClone.tabela2(
coluna1 tipo restricoes,
coluna2 tipo restricoes,
colunaN tipo restricoes,
) engine = InnoDB;
INSERT INTO SpotifyClone.tabela1 (coluna1, coluna2)
VALUES
('exemplo de dados 1', 'exemplo de dados A'),
('exemplo de dados 2', 'exemplo de dados B'),
('exemplo de dados 3', 'exemplo de dados C');
INSERT INTO SpotifyClone.tabela2 (coluna1, coluna2)
VALUES
('exemplo de dados 1', 'exemplo de dados X'),
('exemplo de dados 2', 'exemplo de dados Y');
O que será verificado:
-
Será validado se os planos estão na tabela normalizada.
-
Será validado se o histórico de reprodução está na tabela normalizada.
-
Será validado se as informações sobre pessoas seguindo artistas estão na tabela normalizada.
-
Será validado se os álbuns estão na tabela normalizada.
-
Será validado se os canções estão na tabela normalizada.
-
Será validado se as informações sobre pessoas usuárias estão na tabela normalizada.
-
Será validado se as informações sobre pessoas artistas estão na tabela normalizada.
SpotifyClone
será dropada. Logo, se atente a salvar seu progresso nos arquivos de desafio!
Crie uma QUERY
que exiba três colunas:
-
A primeira coluna deve exibir a quantidade total de canções. Dê a essa coluna o alias "cancoes".
-
A segunda coluna deve exibir a quantidade total de artistas e deverá ter o alias "artistas".
-
A terceira coluna deve exibir a quantidade de álbuns e deverá ter o alias "albuns".
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se existe uma
QUERY
que exibe os dados corretos nas colunascancoes
,artistas
ealbuns
.
Crie uma QUERY
que deverá ter apenas três colunas:
-
A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.
-
A segunda coluna deve possuir o alias "qtde_musicas_ouvidas" e exibir a quantidade de músicas ouvida pela pessoa com base no seu histórico de reprodução.
-
A terceira coluna deve possuir o alias "total_minutos" e exibir a soma dos minutos ouvidos pela pessoa usuária com base no seu histórico de reprodução.
Os resultados devem estar agrupados pelo nome da pessoa usuária e ordenados em ordem alfabética.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasusuario
,qtde_musicas_ouvidas
etotal_minutos
. -
Será validado se as colunas estão ordenadas de forma correta.
Crie uma QUERY
que deve mostrar as pessoas usuárias que estavam ativas no ano de 2021 se baseando na data mais recente no histórico de reprodução.
-
A primeira coluna deve possuir o alias "usuario" e exibir o nome da pessoa usuária.
-
A segunda coluna deve ter o alias "condicao_usuario" e exibir se a pessoa usuária está ativa ou inativa.
O resultado deve estar ordenado em ordem alfabética.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasusuario
econdicao_usuario
. -
Será validado se as colunas estão ordenadas de forma correta.
Estamos fazendo um estudo das músicas mais tocadas e precisamos saber quais são as duas músicas mais tocadas no momento. Crie uma QUERY
que possua duas colunas:
-
A primeira coluna deve possuir o alias "cancao" e exibir o nome da canção.
-
A segunda coluna deve possuir o alias "reproducoes" e exibir a quantidade de pessoas que já escutaram a canção em questão.
Seu resultado deve estar ordenado em ordem decrescente, baseando-se no número de reproduções. Em caso de empate, ordene os resultados pelo nome da canção em ordem alfabética. Queremos apenas o top 2 de músicas mais tocadas.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunascancao
ereproducoes
. -
Será validado se as colunas estão ordenadas de forma correta.
Tendo como base o valor dos planos e o plano que cada pessoa usuária cadastrada possui no banco, queremos algumas informações sobre o faturamento da empresa. Crie uma QUERY
que deve exibir quatro dados:
-
A primeira coluna deve ter o alias "faturamento_minimo" e exibir o menor valor de plano existente para uma pessoa usuária.
-
A segunda coluna deve ter o alias "faturamento_maximo" e exibir o maior valor de plano existente para uma pessoa usuária.
-
A terceira coluna deve ter o alias "faturamento_medio" e exibir o valor médio dos planos possuídos por pessoas usuárias até o momento.
-
Por fim, a quarta coluna deve ter o alias "faturamento_total" e exibir o valor total obtido com os planos possuídos por pessuas usuárias.
Para cada um desses dados, por se tratarem de valores monetários, deve-se arredondar o faturamento usando apenas duas casas decimais.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasfaturamento_minimo
,faturamento_maximo
,faturamento_medio
efaturamento_total
.
Mostre uma relação de todos os álbuns produzidos por cada pessoa artista, com a quantidade de seguidores que ela possui, de acordo com os detalhes a seguir. Para tal, crie uma QUERY
com as seguintes colunas:
-
A primeira coluna deve exibir o nome da pessoa artista, com o alias "artista".
-
A segunda coluna deve exibir o nome do álbum, com o alias "album".
-
A terceira coluna deve exibir a quantidade de pessoas seguidoras que aquela pessoa artista possui e deve possuir o alias "seguidores".
Seus resultados devem estar ordenados de forma decrescente, baseando-se no número de pessoas seguidoras. Em caso de empate no número de pessoas, ordene os resultados pelo nome da pessoa artista em ordem alfabética e caso há artistas com o mesmo nome, ordene os resultados pelo nome do álbum alfabeticamente.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasartista
,album
eseguidores
. -
Será validado se as colunas estão ordenadas de forma correta.
Mostre uma relação dos álbuns produzidos por um artista específico, neste caso "Walter Phoenix"
.
Para isto crie uma QUERY
que o retorno deve exibir as seguintes colunas:
-
O nome da pessoa artista, com o alias "artista".
-
O nome do álbum, com o alias "album".
Os resultados devem ser ordenados pelo nome do álbum em ordem alfabética.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se a
QUERY
retorna a colunaartista
contendo o nome da pessoa artista e a colunaalbum
contendo o nome do album. -
Será validado se as colunas estão ordenadas de forma correta.
Crie uma QUERY
que exibe a quantidade de músicas que estão presentes atualmente no histórico de reprodução de uma pessoa usuária específica. Para este caso queremos saber quantas músicas estão no histórico do usuário "Bill"
e a consulta deve retornar a seguinte coluna:
- O valor da quantidade, com o alias "quantidade_musicas_no_historico".
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
- Será validado se a
QUERY
, retorna a quantidade correta de canções presentes no histórico de reprodução da pessoa especificada.
Crie uma QUERY
que exiba o nome e a quantidade de vezes que cada canção foi tocada por pessoas usuárias do plano gratuito ou pessoal, de acordo com os detalhes a seguir:
-
A primeira coluna deve exibir o nome da canção, com o alias "nome";
-
A segunda coluna deve exibir a quantidade de pessoas que já escutaram aquela canção, com o alias "reproducoes";
-
Seus resultados devem estar agrupados pelo nome da canção e ordenados em ordem alfabética.
Sua QUERY
deve retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasnome
ereproducoes
. -
Será validado se as colunas estão ordenadas de forma correta.
Crie uma QUERY
que altere o nome de algumas músicas e as ordene em ordem alfabética com as colunas abaixo se baseando nos seguintes critérios:
-
O nome da música em seu estado normal com o alias nome_musica
-
O nome da música atualizado com o alias novo_nome
Critérios
-
Trocar a palavra "Streets" no final do nome de uma música por "Code Review"
-
Trocar a palavra "Her Own" no final do nome de uma música por "Trybe"
-
Trocar a palavra "Inner Fire" no final do nome de uma música por "Project"
-
Trocar a palavra "Silly" no final do nome de uma música por "Nice"
-
Trocar a palavra "Circus" no final do nome de uma música por "Pull Request"
Sua QUERY
retornar a seguinte informação:
O que será verificado:
-
Será validado se existe uma
QUERY
que exibe os dados corretos nas colunasnome_musica
enovo_nome
. -
Será validado se as colunas estão ordenadas de forma correta.
Para "entregar" seu projeto, siga os passos a seguir:
- Vá até a página DO SEU Pull Request, adicione a label de "code-review" e marque seus colegas
- No menu à direita, clique no link "Labels" e escolha a label code-review
- No menu à direita, clique no link "Assignees" e escolha o seu usuário
- No menu à direita, clique no link "Reviewers" e digite
students
, selecione o timetryber/students-sd-00
Se ainda houver alguma dúvida sobre como entregar seu projeto, aqui tem um video explicativo.
À medida que você e as outras pessoas que estudam na Trybe forem entregando os projetos, vocês receberão um alerta via Slack para também fazer a revisão dos Pull Requests dos seus colegas. Fiquem atentos às mensagens do "Pull Reminders" no Slack!
Use o material que você já viu sobre Code Review para te ajudar a revisar os projetos que chegaram para você.
Ao finalizar e submeter o projeto, não se esqueça de avaliar sua experiência preenchendo o formulário. Leva menos de 3 minutos!
Link: FORMULÁRIO DE AVALIAÇÃO DE PROJETO
O avaliador automático não necessariamente avalia seu projeto na ordem em que os requisitos aparecem no readme. Isso acontece para deixar o processo de avaliação mais rápido. Então, nãose assuste se isso acontecer, ok?