SQL Básico: bancos de dados para iniciantes

1. Introdução: A maioria dos desenvolvedores vê um banco de dados como no caminho, uma necessidade infeliz. O que eles não percebem é que o design de banco pode fazer ou quebrar seu produto ou recurso. Ei, eu sou [inaudível]. Sou cientista de dados em uma pequena startup e estudante de doutorado em Ciência da Computação na UC Berkeley. No campus, ensinei mais de 5.000 alunos, e fora do campus, recebi mais de 50 avaliações cinco estrelas para ensinar as pessoas a codificar o seu Airbnb. No final desta aula, você saberá como usar o banco de dados do InDesign para sua próxima grande ideia, seja um aplicativo tão simples quanto um item de tarefas ou tão complexo quanto o Airbnb. Nesta classe, mostrarei a maneira certa de organizar seus dados, os pilares de design e uso do banco de dados, desde escrever consultas básicas até projetar um banco de dados inteiro. Para desenvolvedores, você terá a prática de design de banco de dados. Para pessoas não técnicas, você entenderá como comunicar requisitos aos desenvolvedores. Esta classe é projetada para pessoas com pouca ou nenhuma experiência. Se você não sabe o que é SQL, se você nunca usou SQL, ou se você só buscou dados, então esta classe é para você. Tudo o que você precisa é de um computador, Internet, e cerca de uma hora de tempo. Esta é uma aula prática, então você estará codificando a cada segundo do caminho. Você trabalhará em três estudos de caso: um aplicativo meteorológico, um aplicativo de tarefas e, finalmente, uma versão mínima do Airbnb. Em cada fase você irá projetar, criar, consultar e, finalmente, otimizar um banco de dados. Vou mostrar-lhe exemplos de design ruim e dar-lhe dicas para um bom design. Estou animado para fornecer essas ferramentas de design de banco de dados e vou explicar por que essas ferramentas podem fazer ou quebrar seu produto no próximo vídeo. 2. Projeto: criando um banco de dados: Seu objetivo nesta classe é projetar e usar um banco de dados. Um bom design de banco de dados é essencial para o seu produto, e aqui está o porquê. Em qualquer aplicativo, o banco de dados fornece dados para a camada lógica. É aqui que os dados são processados, classificados, reanalisados. Essa camada lógica então fornece informações para sua interface de usuário. Como resultado, o banco de dados afeta, realmente, todas as partes de seu aplicativo. más escolhas de design irão atrasar os desenvolvedores, danificar a experiência do usuário com tempos de carregamento ruins e incentivar escolhas de design ruins em todas as partes da base de código. Esta é uma aula prática, então você estará codificando cada passo do caminho. Você trabalhará em três estudos de caso, um aplicativo meteorológico, um aplicativo de afazeres e, finalmente, uma versão mínima do Airbnb. Seu projeto de classe é projetar, criar, consultar e, finalmente, otimizar o banco de dados para um estudo de caso de sua escolha. Você precisará acessar glitch.com e dbdiagram.io. Você não precisa se inscrever para usar também. No entanto, sugiro que se inscreva de qualquer maneira para que você possa salvar seu progresso. Agora, vamos começar. 3. Bancos de dados: o básico: Esta lição servirá como um primer para diferentes termos e conceitos de banco de dados. Você provavelmente está se perguntando, o que é uma consulta? O que é um banco de dados? Um banco de dados é uma coleção organizada de dados, uma consulta é como acessamos e gerenciamos essa coleta organizada de dados. Finalmente, SQL ou linguagem de consulta estruturada é uma linguagem que usamos para expressar essas consultas. Como resultado, muitas vezes você vai me ouvir dizer consulta SQL como essas consultas são como nós interagimos com o banco de dados. Uma nota rápida sobre como os dados são organizados. Pense em uma planilha do Excel, todos os dados são organizados em tabelas, cada tabela contém um tipo de objeto como usuário, cada usuário tem um número de atributos diferentes, como ID e nome, como você pode ver, cada atributo é uma coluna. Cada coluna também tem um determinado tipo, então ID é um inteiro, nome é texto. Podemos preencher esta tabela com novos usuários, como um, John Doe e dois Jane Doe, como você pode ver, cada linha é um novo usuário. É isso para terminologia. Agora, vamos falar sobre algumas dicas diferentes. Aqui estão três dicas que eu tenho para você em particular: Dica número 1, para ganhar o lado da cautela, sempre copiar o código exato que eu tenho. Dica número 2, pausar o vídeo quando necessário. Vou explicar cada linha de código que eu escrever mas se você precisar de tempo para digitar e experimentar código você mesmo, não hesite em pausar. Dica número 3, você aprende melhor fazendo, eu sugiro configurar-se para o sucesso colocando suas janelas Skillshare e Glitch lado a lado, como mostrado aqui. Isso é tudo para dicas gerais. Na próxima lição, criaremos seu primeiro banco de dados. 4. SQL “Hello World”: Vamos criar um banco de dados e escrever nossas primeiras consultas SQL. Nesta lição, você aprenderá como configurar um banco de dados e, em seguida, criar, ler, atualizar e excluir dados. Vamos usar um banco de dados chamado SQLite. Por que eu uso o ar para codificar não é muito importante. Tudo o que você precisa saber é que SQLite nunca deve ser usado na produção. No entanto, todos os bancos de dados que você usa no trabalho, Postgres , MongoDB, MySQL suportarão os mesmos comandos que usaremos nesta classe hoje. Bem-vindos à Lição 4. Vamos falar sobre o que exatamente vamos construir. Aqui está um diagrama. O que você está vendo é um diagrama que lista o nome de uma tabela de usuários. À esquerda estão os nomes das colunas. Cada um deles é uma propriedade de um usuário, o ID do usuário, o nome do usuário e algumas informações sobre quando as informações do usuário foram criadas ou atualizadas. À direita, você tem os tipos de coluna, um int para inteiro. Por enquanto, você pode tratar varchar como texto, e um carimbo de data/hora como uma data e hora. ID aqui na primeira linha em negrito, é o que é conhecido como a chave primária. A chave primária identifica exclusivamente cada linha de nossos dados e é um must-have para todas as tabelas. Essas duas linhas; criadas em e atualizadas em são frequentemente incluídas e recomendadas. Agora, vamos navegar para glitch.com. Uma vez feito isso, você verá uma tela como esta. Vá em frente e clique em “Novo projeto” no canto superior direito e selecione hello-página web. Em seguida, você deve ver uma página da Web como a seguinte. Vá em frente e no canto inferior esquerdo, clique em “Ferramentas” e “Terminal”. Você verá uma página carregada como esta. Vá em frente e, em seguida, clique no terminal de página inteira. É aqui que vamos codificar. Vamos em frente e começar iniciando um prompt. Neste terminal, vá em frente e digite sqlite3.data/lesson4.db. Este primeiro pedaço de texto é o comando, sqlite3. O primeiro argumento, ou o segundo pedaço de texto, é o nome do arquivo que estamos passando. Neste caso, vamos estar armazenando nosso banco de dados em um arquivo chamado.data/lesson4.db. Vá em frente e aperte “Enter”. Agora você será recebido com o prompt SQLite. Este prompt nos permitirá codificar em SQL. Vamos em frente e criar nossa primeira tabela. Neste prompt, vá em frente e digite criar usuários de tabela. Este usuário terá uma coluna ID que tem uma chave primária. Este usuário terá um nome que é necessário. Aqui, não nulo significa que o nome não pode ser deixado vazio. Finalmente, teremos uma coluna para criado em. Este é o momento em que o usuário foi adicionado ao banco de dados. Vá em frente e adicione um parêntese de fechamento e, em seguida, um ponto e vírgula. Digite em.tables para listar as tabelas neste banco de dados. Aqui podemos ver que agora há uma tabela chamada usuários, confirmando que criamos com sucesso nossa tabela de usuários. Vá em frente e digite usuários.schema para descrever a tabela de usuários. Como você pode ver, esta descrição representa fielmente o que digitamos anteriormente. Agora, vamos em frente e inserir nossos dados. Vá em frente e digite, insira em, passe o nome da tabela, que é usuários. Em seguida, passe na coluna que queremos preencher, que é o nome. Finalmente, digite os valores com os quais queremos preenchê-lo. Neste caso, John. Agora vá em frente e repita a mesma coisa, mas para um usuário chamado Jane. Agora podemos mostrar os dados que inserimos em nossa tabela. Vá em frente e digite selecione todos os usuários. Este asterisco aqui significa selecionar todas as colunas. Isso nos permitirá exibir todas as colunas no resultado. Então os usuários é o nome da tabela que queremos selecionar nossos dados. Vá em frente e pressione “Enter”, e você verá aqui que temos tanto o ID, o nome e, em seguida, um campo criado em que não preenchemos. Agora, vamos em frente e falar sobre um tipo diferente de instrução seletiva. Digite select all de usuários em que o nome é Jane. Esta é agora uma consulta de seleção filtrada. A instrução where nos permite especificar condições que queremos ser verdadeiras para os usuários que selecionamos. Agora, vamos atualizar as informações da Jane. Digite atualização, o nome do conjunto é igual a Jane Doe. Estamos dando um sobrenome para Jane, onde nome é igual a Jane atualmente. Vá em frente e aperte “Enter”. Mais uma vez, selecione todos os usuários para confirmar que nossas informações de usuários foram atualizadas, e agora podemos ver que Jane é agora Jane Doe. Finalmente, vá em frente e exclua o usuário John. Excluir de usuários em que o nome é igual a John. Para confirmar que a exclusão foi bem-sucedida, vá em frente e selecione todos os usuários mais uma vez. Agora podemos ver que o usuário John se foi. Finalmente, vá em frente e exclua a tabela. Eliminar usuários da tabela. Podemos confirmar que essa tabela foi descartada digitando em.tables para listar todas as tabelas. Como podemos ver, não há mais tabelas restantes, e isso conclui a parte de codificação desta lição. Vamos em frente e pular agora para a revisão. O que aprendemos nesta lição? O primeiro takeaway, é que todas as tabelas incluem uma chave primária, e que muitas vezes incluem duas colunas adicionais chamadas criado em e atualizado em. A próxima conclusão é que existem várias consultas SQL comuns que exploramos; selecione, insira, atualize e exclua. Também exploramos uma maneira de filtrar consultas selecionadas usando a cláusula where. Vamos rever a lista de conceitos que aprendemos esta lição. Você aprendeu muito nestes últimos minutos. Nós abordamos a cláusula where, as tabelas de chave primária, colunas , consultas e muitos outros termos que não listamos aqui. É isso. Isso conclui a Lição 4. 5. Caso Estudo 1: aplicativo de clima: Nesta lição, analisaremos nosso primeiro estudo de caso, um aplicativo meteorológico. Você tocará e usará alguns conceitos e termos diferentes. Em vez de defini-los antecipadamente, estaremos definindo-os à medida que os usamos. Não se preocupe, o próximo slide deve parecer completamente estranho para você, isto é apenas para dizer, nós estaremos aprendendo muito nesta lição. Vamos começar. Em cada um de nossos estudos de caso, seguiremos cinco etapas: requisitos, design, otimização, diagramação e, finalmente, código. Vamos em frente e começar com o primeiro passo aqui, requisitos. Quais são os requisitos para este aplicativo meteorológico? Este aplicativo meteorológico vai ter várias entidades, essas entidades incluem o usuário e um fuso horário. Como essas entidades estão relacionadas? Isso é parte dos requisitos para relacionamentos. Cada fuso horário potencialmente tem muitos usuários e cada usuário tem apenas um fuso horário. Nós nos referimos a isso como um relacionamento um-para-muitos; um usuário para muitos fusos horários, discutiremos isso mais em apenas um segundo. Vamos em frente e passar para o passo 2, design. Aqui você pode ver que você tem ambas as entidades, o usuário e o fuso horário, e você também pode ver que há muitos usuários para cada fuso horário, então é por isso que chamamos isso de muitos-para-um ou um-para-muitos, dependendo da sua perspectiva . Vamos falar sobre um mau exemplo de implementação desse requisito. Aqui temos uma tabela para usuários, como antes. Agora, vamos adicionar outra tabela para fusos horários. Agora vamos inserir uma referência ao fuso horário. Aqui está o id de fuso horário, que faz referência ao fuso horário de cada usuário. Agora, aqui está porque é uma má ideia, porque podemos fazer melhor. Vamos passar para o passo 3, otimização. Antes de fazermos isso, aqui está um princípio que eu gostaria de apresentar. Em bancos de dados, seu objetivo é usar menos tabelas. Dica número 1 é quando você deve substituir um um-para-muitos por um tipo de dados diferente. Neste exemplo em particular, vamos sugerir o uso de uma enumeração, uma enumeração que você pode pensar apenas como uma lista de opções possíveis. Queremos substituir uma tabela um-para-muitos por uma lista de opções possíveis quando A, você tem um número limitado de opções, e B, cada opção tem um identificador exclusivo. Neste caso específico, notaremos que o fuso horário satisfaz ambas as condições. Número 1, o fuso horário apenas tem um nome e esse nome identifica exclusivamente um fuso horário. Em segundo lugar, descobriremos que há um número limitado de fusos horários, então, em vez disso, substituiremos essa tabela por uma enumeração ou uma lista de opções. Agora, o usuário tem um fuso horário de atributo, onde o fuso horário é restrito para estar em PDT, EDT, assim por diante e assim por diante. Isto é o que é conhecido como um gráfico de relações de entidade, ou para resumir, um ERC. Estaremos projetando um desses diagramas em um segundo. Na próxima etapa, passo 4, vamos diagrama. Vá em frente e navegue até dbdiagram.io em seu navegador. Depois de acessar a página da web, vá em frente e clique em “Ir para App” no canto superior direito. A primeira coisa que vou fazer é ir em frente e criar um enumeração, então aqui vamos digitar o fuso horário enum com colchetes, em seguida, ir em frente e digitar alguns fusos horários diferentes de sua escolha, neste caso, vamos usar PDT, EDT e CDT. Agora, temos uma lista de diferentes opções para fusos horários, vamos em frente e digite nossa tabela. Aqui vamos digitar usuários de tabela, assim como antes com chaves. Assim como antes, nossa tabela de usuários terá um ID do tipo inteiro que tem uma chave primária. Vá em frente e adicione outra coluna para nome, isto é, do tipo texto e, finalmente, adicione fuso horário com o tipo de fuso horário. Estes são os diferentes valores que o nosso usuário tem. Agora, vamos em frente e adicionar mais duas colunas que são necessárias, created_at timestamp e updated_at também com um timestamp. Eu vou ir em frente e diminuir um pouco aqui, e aqui você pode ver nossa mesa completa fora para a direita. Isto completa o nosso diagrama, vamos em frente e voltar para os nossos slides. Agora, vamos em frente e código. Para codificar, vá em frente e navegue de volta para glitch.com. Em glitch.com, vá em frente e acesse seu projeto existente clicando em “Editar projeto”. Se você não completou a última lição, vá em frente e clique em “Novo projeto” e “HELLO-página da Web” e isso abrirá um novo projeto. Uma vez que você estiver nessa página, vá em frente e clique em “Ferramentas”, “Terminal” e, em seguida, “Terminal de página inteira”. Isso carregará uma página como esta. Vamos começar iniciando o prompt SQLite, assim como da última vez. Vá em frente e digite sqlite3.data/lesson5.db. Novamente, este primeiro pedaço de texto, sqlite3, é o comando, o segundo pedaço de texto, .data/lesson5.db, é o arquivo em que vamos armazenar nossos dados. Vá e aperte “Enter”. Agora, crie sua tabela de usuários, CREATE TABLE usuários, esta tabela começará com um id, que é do tipo INTEGER e é uma chave primária. Em seguida, vá em frente e adicione outra coluna como antes, que tem um nome, e assim como antes, exigem que ela não esteja vazia. Adicionar um fuso horário, e um pequeno tem você, é que SQLite não tem realmente tipos de enumeração, então em vez disso, vamos apenas usar texto, finalmente, adicionar as duas últimas colunas, e created_at. Vá em frente e adicione um parêntese de fechamento e um ponto-e-vírgula. Isso agora cria nossa nova tabela de usuários. Vá em frente e insira alguns valores nesta tabela de usuários. Novamente, especifique as colunas às quais você deseja adicionar valores, nome e fuso horário e, finalmente, especifique os valores que deseja inserir. Aqui, vamos inserir algumas diferentes dessas linhas. Vamos ter um usuário chamado John, um usuário chamado Jane, e finalmente um usuário chamado Jenny. Agora, vamos em frente e selecionar a partir desta tabela, vamos escrever SELECT all FROM usuários, isso nos dá todos os três usuários que inserimos com id 1, chamado John, id 2, nomeado Jenny, e id 3, chamado Jane. Finalmente, vamos examinar algumas consultas diferentes que gostaríamos de executar. Primeiro, gostaríamos de selecionar todos os usuários para um determinado fuso horário. Então, aqui vou escrever SELECT all FROM usuários onde o fuso horário é igual a EDT, e como veremos, veremos ambos os usuários que estão nesse fuso horário. Agora, vamos em frente e contar quantos usuários estão nesse fuso horário, este é um novo tipo de função que ainda não vimos chamado de agregador. Vá em frente e escrevê-lo em usuários SELECT COUNT FROM onde fuso horário é igual a EDT, e isso nos dá dois, assim como vimos anteriormente. Mais uma vez, abordamos cinco etapas diferentes: requisitos, design, otimização, diagramação e, finalmente, código. Nós abordamos uma série de tópicos diferentes nos últimos minutos: O primeiro é um ERC ou o diagrama, um relacionamento um-para-muitos, um agregador ou a função de contagem que usamos, um enum, e finalmente, o cinco- processo de etapa em si. Parabéns, esse é o seu primeiro projeto de banco de dados para um aplicativo do mundo real. Se você tem ideias para um aplicativo climático mais legal e mais chique, sinta-se à vontade para modificar e enfeitar o ERC que criamos nesta lição e enviá-lo para a aba Projetos no Skillshare. Na próxima lição, vamos projetar um banco de dados para um aplicativo um pouco mais complicado, um aplicativo Todo. 6. Estudo de caso 2: aplicativo de lista de afazeres: Nesta lição, analisaremos um segundo estudo de caso, um aplicativo ToDO para os requisitos da primeira etapa. Para este aplicativo ToDO, precisaremos de duas entidades diferentes. O primeiro é um usuário, e o segundo é uma tarefa. As relações entre essas entidades, cada usuário tem muitas tarefas. Cada tarefa também tem apenas um usuário. Parece que temos outra relação de um-para-muitos. Vamos falar sobre o design. Aqui temos uma tarefa e um usuário. Temos muitas tarefas para cada usuário. Vemos que este é um muitos-para-um ou um um-para-muitos. Aqui está um mau exemplo. Podemos começar tentando o que falamos da última vez, que é usar um campo como enum-like. Agora não podemos usar um enumeração exatamente porque, , há muitos e-mails de usuários diferentes. Não há uma lista limitada que conhecemos de antemão. Segundo, digamos que queremos adicionar um nome de usuário, então teríamos que adicionar esse nome e endereço de e-mail para cada tarefa que este usuário tem. Em vez disso, aqui está um bom exemplo. Agora vamos dividir uma tabela diferente para os usuários. Isso é chamado de normalização. Aqui temos uma tabela de usuário à esquerda com o nome e e-mail. Agora só temos que armazenar informações do usuário uma vez. No entanto, agora o link entre o usuário e suas tarefas está quebrado. Aqui precisamos adicionar algo chamado chave estrangeira. No lado direito, você tem o ID de usuário, que faz referência ao usuário para a tarefa. Em seguida, vamos em frente e otimizar. Dica número 2 para otimização é usar índices para consultas mais rápidas. Índices únicos são um dos mais simples de adicionar. Nesse caso, é o endereço de email do usuário. Esperamos que cada usuário tenha um endereço de e-mail diferente e exclusivo. Vamos em frente e passar para o diagrama antes de navegar para dbdiagram.io nesta página da Web. Se você ainda tiver o diagrama original aberto, passe o mouse sobre este menu suspenso e clique em “Novo diagrama”. Vamos começar criando nossas duas tabelas. Vá em frente e digite usuários de tabela. Esta tabela terá um ID do tipo inteiro que é uma chave primária. Ele também terá um nome com texto de texto, em seguida, um endereço de e-mail também do tipo texto que tem uma restrição de exclusividade. Finalmente, vamos adicionar mais duas colunas. Em seguida, vamos adicionar uma enumeração para o status da tarefa. Aqui teremos enumeração de status, e haverá dois status possíveis. Agora, vamos em frente e adicionar outra tabela. Aqui teremos uma tabela chamada tarefas. Dê a ele um id com tipo inteiro de chave primária e, em seguida, a chave estrangeira que falamos anteriormente. Vá em frente e digite user_id do tipo integer, e vamos digitar em ref dois-pontos maiores que users.id. Aqui users.id refere-se à tabela do usuário, id da coluna. Vamos agora adicionar um status e, em seguida, vamos adicionar uma descrição do texto do tipo. Finalmente, criado em e atualizado em, e aqui estão as duas tabelas que acabamos de criar. Isso conclui nossa diagramação. Vamos em frente e passar para o código. Passo 5, comece navegando até glitch.com. Quando você estiver lá, você deve ver uma página como esta, como antes. Você pode editar o projeto existente ou você pode digitar um novo projeto hello-página web. Uma vez que você fizer isso, você será recebido com uma página como esta. Vamos escrever um script que irá criar o banco de dados e adicionar alguns valores diferentes para nós no banco de dados. Comece pressionando Novo arquivo no canto superior esquerdo, depois digite lesson6.sql, solte usuários da tabela, se ele existir. Agora, crie uma tabela para nossos usuários e adicione o id, o nome e o e-mail, e vamos adicionar a restrição adicional de que este e-mail é exclusivo. Vamos em frente e agora adicione as duas colunas com as quais estamos familiarizados. Vamos em frente e criar as tarefas das outras pessoas. Digite o id. Nós vamos adicionar em user_id, status TEXT, uma descrição também do tipo TEXT. Finalmente, as duas colunas que sempre adicionamos. Agora vamos adicionar o que é chamado de restrição de chave estrangeira. A restrição de chave estrangeira simplesmente garante que sempre que uma tarefa tiver um id de usuário, esse id de usuário se refere a um usuário real na tabela de usuários. Adicione esta chave estrangeira, que se refere a chamá-los user_id, que é suposto fazer referência aos usuários da tabela e id da coluna. Vá em frente e adicione um ponto-e-vírgula para completar sua instrução. Vamos começar inserindo alguns usuários, INSERT INTO usuários, e vamos adicionar duas colunas, nome e e-mail, com valores diferentes, John, adicionar um ponto-e-vírgula, e agora vamos em frente e copiar e colar essa linha e substituir esses valores por Jane. Agora, vamos adicionar algumas tarefas para o John. INSERT INTO tarefas as colunas que vamos preencher, nosso user_id, status e descrição. Agora vamos adicionar alguns valores diferentes. O primeiro usuário inserido aqui, John, terá user_id 1. O status dessa tarefa deve ser TODO, e a descrição será Swim. Agora vamos copiar e colar e vamos adicionar outra tarefa para John. Agora, vamos em frente e adicionar mais algumas tarefas desta vez para Jane. É isso. Glitch salva automaticamente esse arquivo para você. Vamos em frente e agora navegue até o terminal. Vá para Ferramentas, Terminal e Terminal de Página Completa. Você verá uma página como esta. Vá em frente e digite sqllite3.data/lesson6.db, vá e pressione Enter, e agora você está no prompt SQL. Vamos em frente e executar o arquivo que acabamos de criar, .read lesson6.sql. Vamos primeiro selecionar todas as tarefas. SELECT asterisco FROM tarefas. Nós também vamos selecionar todos os usuários. Isto parece bom. Temos ambos os nossos usuários e as duas tarefas atribuídas a cada um. Vamos em frente e agora, em vez de selecionar tudo, queremos selecionar colunas específicas. Aqui nós estamos indo apenas para selecionar o nome e colunas de e-mail de usuários. Agora só recebemos o nome e o e-mail de cada usuário. Agora vamos explorar uma nova consulta chamada JOIN. Vamos selecionar o nome do usuário e a descrição da tarefa. Vamos SELECT FROM usuários, vamos JOIN com a tabela de tarefas, e vamos juntá-los quando o users_id é igual às tarefas user_id. vamos JOIN com a tabela de tarefas, e vamos juntá-los quando o users_id é igual às tarefas user_id. John e Jane, e as duas tarefas atribuídas a cada um. Agora, vamos rever esta lição. Visitamos vários tópicos diferentes, incluindo desnormalização, junção, chaves estrangeiras e normalização. Também cobrimos índices e adicionamos um índice exclusivo à coluna de e-mail. Isso conclui esta lição. Concluímos agora um estudo de caso para o aplicativo ToDO. Na próxima lição, você iniciará um estudo de caso para ABMB. 7. Estudo de caso 3: AirBnb (design): Nesta lição, vamos começar nosso terceiro estudo de caso, Airbnb. Vamos cobrir novamente as cinco etapas diferentes, requisitos, design, otimização, e não vamos realmente chegar a diagramação ou código. Vamos começar, requisitos. Existem vários requisitos diferentes. Em particular, as entidades com as quais nos preocupamos são o usuário, a casa e várias relações entre essas entidades. Os usuários visitarão muitas casas. Casas terão muitos visitantes e um requisito extra que nunca vimos antes. Os usuários também serão capazes de possuir casas. Em outras palavras, temos que os relacionamentos são muitos-para-muitos. Há muitos usuários em cada casa, e há muitas casas para cada usuário. Os usuários também podem possuir casas, o que significa que existem diferentes tipos de relacionamentos. Você pode ser um proprietário ou você pode ser um visitante. Agora vamos falar sobre design. Aqui estão as duas entidades; usuário e home. Há muitos usuários para cada casa, e há muitas casas para cada usuário. Esta é a nossa relação muitos-para-muitos. No entanto, como representamos proprietários versus visitantes? Aqui está um mau exemplo. Este é o nosso terceiro e último mau exemplo. Talvez você já saiba a solução, caso em que, ótimo. Se não, não se preocupe. Este é um problema difícil. Então, qual é o mau exemplo? Aqui, temos os donos em vermelho, e os visitantes em preto. Temos que criar uma conta para possuir, e outra conta para visitar. No entanto, Airbnb consegue evitar isso. Você pode criar uma conta para gerenciar propriedades e visitar propriedades ao mesmo tempo. Como é isso? Agora, vamos otimizar. O que vamos agora fazer em vez disso, é associar proprietário ou visitante com a relação entre o usuário, ea casa, em vez de associá-lo com o próprio usuário. Aqui, veremos que as linhas vermelhas denotam relações de propriedade. As linhas pretas representam visitas. Aqui, o primeiro usuário no canto superior esquerdo possui duas casas, e eles visitam a terceira. Isso nos leva à nossa dica número 3, considere adicionar informações às tabelas de relacionamento. Às vezes, as informações não pertencem a nenhuma das entidades. Nesse caso, a relação de proprietário ou visitante não pertence ao usuário ou à casa. Aqui está o diagrama. Do lado direito temos os usuários, do lado esquerdo, temos as casas, e no meio, temos as relações entre usuários e casas. Nós também temos referências tanto para a casa, o usuário da tabela no meio. Esta tabela no meio, as casas dos usuários, é o que nos permite representar uma relação muitos-para-muitos. Você também observará, que esta tabela no meio tem uma coluna de função. Essa coluna de função é o que distingue os proprietários dos visitantes. Agora, vamos em frente, e rever o que conversamos nesta lição. Você cobriu uma série de passos diferentes. Cobrimos requisitos, design e otimização. Na próxima lição, abordaremos diagramas que concluem esta lição. Airbnbs, primeiros três passos do estudo de caso. 8. Estudo de caso 3: AirBnb (diagrama): Nesta lição, vamos agora discutir a etapa de estudo de caso Airbnb número 4; diagramação. Aqui, já terminamos os três primeiros passos: requisitos, design e otimização. Aqui vamos cobrir diagramação, e você pode estar se perguntando por que metade do código está realmente destacado. Bem, vamos fazer um pouco de código nesta lição e depois terminar na próxima. Vamos começar com diagramação. Assim como antes, navegue até dbdiagram.io. Uma vez que você estiver em dbdiagram.io, se você ainda não tiver, você pode selecionar o menu suspenso e clicar em “Novo diagrama”. Você será recebido com um diagrama vazio como este. Vamos em frente e criar as três tabelas diferentes que precisaremos. Vamos adicionar a tabela de usuários. Assim como antes, teremos o id do tipo inteiro que é uma chave primária. Assim como antes, também teremos o campo de nome do texto do tipo, o campo de e-mail do texto do tipo e os dois campos adicionais created_at timestamp e updated_at timestamp. Agora vamos criar uma enumeração para os diferentes tipos de regras que um usuário poderia ter para uma casa. Então aqui vamos ter uma função de enumeração, e a função pode ser um proprietário ou um visitante. Em seguida, vamos criar uma mesa para nossas casas. Vamos em frente e criar mesa, casas. O id vai estar em inteiro, que é uma chave primária. Nós vamos ter um endereço para esta casa, que é do tipo texto, um preço por noite, que é um inteiro também, e finalmente, as duas colunas necessárias created_at e updated_at. Para a nossa terceira e última tabela, agora precisamos representar a relação muitos-para-muitos entre usuários e residências. Aqui vamos ter uma tabela de usuários, casas, um id com um inteiro e uma chave primária. Vamos então ter referências a ambas as casas e aos usuários. Nós vamos ter um home id como do tipo inteiro e esta é uma referência de chave estrangeira, assim como falamos antes, e nós vamos ter uma referência muito semelhante à tabela de usuário. Em seguida, adicione a função para esse relacionamento. Aqui teremos o papel de proprietário ou o papel de visitante. Então vamos adicionar o início, ou o início da casa ou o início da visita. Finalmente, o fim. Em seguida, adicione as duas colunas necessárias que sempre adicionamos, e updated_at. Isso conclui nosso diagrama. Eu vou ir em frente e diminuir o zoom como antes para que você possa ver o diagrama inteiro. Vá em frente e clique em “Auto-organizar” e lá vamos nós. Temos nossos usuários, nossas casas e nossa tabela de relacionamentos. Agora, vamos em frente e passar para o próximo passo. Código. 9. Estudo de caso 3: AirBnb (banco de dados de código): Aqui, vamos realmente codificar apenas o início, vamos criar o banco de dados. Vá em frente e navegue até glitch.com, assim como antes, você pode editar um projeto existente ou clicar em “Novo projeto” e “HELLO-página”, você verá uma página que se parece com esta, vá em frente e clique em “Novo arquivo” e lesson8.sql. Neste arquivo, vamos em frente e criar o banco de dados que estamos falando. Vá em frente e, como antes, solte as tabelas neste script se elas já existirem. Aqui, vamos para usuários DROP TABLE, vamos para casas DROP TABLE e, finalmente, vamos para DROP TABLE users_homes. Observe esta convenção de nomenclatura, a tabela de relacionamentos entre usuários e casas deve ser apenas a concatenação desses dois nomes. Agora, vamos em frente e criar a tabela de usuários. Assim como antes, vamos ter o id, uma chave primária, vamos ter o nome, que vai ser um TEXTO que não está vazio, vamos ter o e-mail que é TEXTO que não está vazio e é único, e então vamos ter os campos updated_at e created_at. No entanto, desta vez nosso campo created_at vai se auto-preencher, vá em frente e adicione um valor padrão, isto é, o CURRENT_TIMESTAMP. Pressione “Enter”, ponto-e-vírgula, e esta sintaxe padrão irá realmente preencher este campo created_at para nós sempre que inserirmos uma linha, veremos isso em ação na próxima lição. Vamos em frente e agora criar uma mesa de casas. Vamos CREATE TABLE homes, assim como antes, esta tabela vai ter um ID de INTEGER, PRIMARY KEY, ele vai ter um endereço que é do tipo TEXT que não está vazio, um price_per_night do tipo inteiro que também não é vazio e, finalmente, as duas colunas updated_at e created_at, assim como antes, vá em frente e adicione um valor padrão para created_at de CURRENT_TIMESTAMP. Finalmente, vamos em frente e criar nossa terceira tabela. Aqui vamos CREATE TABLE users_homes. Assim como qualquer outra tabela, esta tabela vai ter o seu tipo INTEGER para PRIMARY KEY, ele também vai ter referências tanto para a casa e para o usuário. Agora, vá em frente e adicione a função para este relacionamento, adicione o início e o fim, em seguida, nossas duas colunas favoritas updated_at e created_at, assim como antes adicionar um padrão. Em seguida, vamos em frente e inserir alguns dados nessas tabelas. Vamos, em particular, inserir alguns usuários com um nome e um endereço de e-mail, então vou copiar e colar os usuários restantes da lição anterior. Agora, vamos em frente e inserir algumas casas. Vamos inserir um endereço e um price_per_night, aqui teremos um endereço e um price_per_night. Eu vou clicar na seta no canto superior esquerdo, que irá minimizar esta barra de navegação para que você possa ver mais do meu código de uma só vez, eu também vou copiar e colar esta linha aqui para que possamos modificar os endereços e os preços muito mais rápido. Finalmente, vamos adicionar alguma propriedade de casa. Aqui, vamos INSERT users_homes, vamos adicionar o home_id, o user_id, a função, o início e o fim; vamos adicionar alguns valores. Aqui vamos adicionar na primeira casa, o primeiro usuário com relacionamento, PROPRIETÁRIO, e vamos adicionar em um horário de início arbitrário para o início da propriedade da casa, aqui você pode usar qualquer formato que você gostaria para o tempo, vamos usar um formato que é aproximadamente semelhante a algo chamado formato ISO, no entanto, novamente, realmente não importa o formato que você usa. Certifique-se de adicionar um ponto-e-vírgula no final da linha, e vá em frente e repita a mesma coisa, mas desta vez para Jane. Então Jane é user_id 2 e ela também não pode possuir a primeira propriedade, então nós vamos ter sua própria propriedade diferente. Agora, vamos copiar e colar isso mais uma vez. Nesta lição, estamos assumindo que cada casa tem apenas um proprietário, no entanto, você poderia, em teoria, com o banco de dados que você criou, representar vários proprietários por casa. Agora, vamos em frente e mudar esta casa para a terceira casa, ainda usando o usuário Jane, que tem user_id 2. Agora, finalmente, a última coisa que vamos adicionar a este script é um número de visitas diferentes. Vamos em frente e copiar e colar a mesma linha, mas agora vamos mudar o relacionamento de PROPRIETÁRIO para VISITANTE, também vamos alterar o ID da casa. Então, agora, John é dono da primeira propriedade, então vamos pedir ao John para visitar a segunda propriedade. Vá em frente e mude as datas para algo razoável, aqui eu vou começar em 5 de outubro e terminar em 7 de outubro. Vamos agora continuar e duplicar isso algumas vezes. Agora, teremos, novamente, John visitando, desta vez ele visitará a propriedade 3, e começará a visita 7 de outubro e visitará até 9 de outubro. Finalmente, vamos em frente e adicionar nosso terceiro e último visitante. Aqui vamos ter o nosso novo visitante, Bob, visitar a terceira propriedade antes do John. Tudo bem. Isso conclui nosso código. Na próxima lição, vamos realmente executar esse código, e então vamos fazer um pouco mais para consultar esses dados. Isso conclui nossa lição aqui. Este foi o estudo de caso do Airbnb passo quatro de diagramação e um pouco de código. Na próxima lição, terminaremos de consultar os dados. 10. Estudo de caso 3: AirBnb (consultas do código): Bem-vindos à nona e última lição do estudo de caso da Airbnb. Neste estudo de caso, vamos terminar o código que começamos da última vez, em particular, vamos consultar o banco de dados e os dados que construímos e configuramos. Assim como antes, estamos seguindo o processo de cinco etapas que descrevemos. Em particular, nós cobrimos requisitos, design, otimização, diagrama e, finalmente, nesta etapa, vamos cobrir código. Vá em frente e acesse glitch.com. Uma vez nesta página, você poderá encontrar seu projeto existente e clicar em “Editar projeto”. Observe que, ao contrário de antes, você não pode iniciar um novo projeto, porque precisaremos usar o código que escrevemos da última vez. Quando você estiver em glitch.com, você verá uma página como esta, vá em frente e pressione “Ferramentas” e “Terminal”. Isso vai levar-te a uma conta como esta. Vá em frente e agora inicie o prompt sqlite3 para um novo banco de dados. Aqui vamos ter.data/lesson9.db. Agora, vamos em frente e executar o script que escrevemos na última lição.read lesson8.sql. Nenhuma notícia é uma boa notícia como antes. Aqui podemos ver que não há saída no entanto, isso significa que nosso script foi executado com sucesso. Vamos em frente e agora construir algumas consultas diferentes que podemos nos preocupar. Em particular, falaremos através de diferentes páginas do site da Airbnb que são comumente usadas. Vamos em frente e agora construir consultas para a página de pesquisa. Digamos que queremos listar todas as casas sob US $45 por noite, vá em frente e digite select all from homes onde o preço por noite é inferior a 45. Aqui temos apenas uma propriedade que satisfaça esses critérios. Digamos que também queremos paginar os resultados, em outras palavras, mostrar um número limitado de resultados por página. Aqui vamos selecionar tudo de casas e vamos limitar o número de resultados a dois, e vamos começar a partir do segundo resultado porque esta é talvez a segunda página de resultados. Aqui podemos ver uma das propriedades porque as outras duas propriedades já foram listadas. Finalmente, vamos tentar classificar por preço. Vá em frente e digite, selecione todas as casas e peça por preço por noite. Como você pode ver, as casas estão agora listadas em ordem crescente de preço. Vamos agora explorar a página do host. Queremos fazer perguntas sobre o anfitrião particular, neste caso, vamos perguntar quantas propriedades Jane tem? Vamos combinar os tópicos que aprendemos com lições anteriores. Vamos primeiro selecionar, a fim de contar, vamos usar a contagem de agregadores, vamos selecionar a partir de casas de usuários, a tabela de relacionamentos. Vamos nos juntar na tabela de usuários porque precisamos filtrar para os usuários com o nome Jane. Como antes, precisamos especificar como os usuários e as tabelas de casas de usuários estão vinculados. Aqui temos o ID de usuários é igual aos lares de usuários, ID de usuário. Finalmente, queremos apenas selecionar usuários com o nome Jane e talvez mais importante, queremos apenas selecionar relacionamentos de proprietário do tipo. Aqui podemos ver que Jane tem duas casas, como esperávamos. Agora, vamos em frente e descobrir quantos visitantes uma das propriedades tinha. Aqui vamos mais uma vez, selecione a contagem, vamos selecionar a partir da tabela de usuários e casas. Vamos nos juntar a casas. Mais uma vez, podemos especificar como essas duas tabelas estão relacionadas e vamos filtrar apenas para a casa que nos importamos. Neste caso, nos preocupamos com a 345 Main Street, e só nos importamos com os visitantes. Aqui temos o papel é igual ao visitante. Como você espera, há dois visitantes. Vamos agora explorar uma página diferente do Airbnb. Digamos que queremos explorar a página inicial dos visitantes. Neste caso, queremos listar todas as viagens para um único usuário. Então vamos escrever select e vamos selecionar o endereço que eles visitaram, as datas de início e fim. Nós vamos selecionar de casas e vamos juntar-nos nos relacionamentos de casas de usuários ou para especificar como essas tabelas estão relacionadas por seus IDs e também precisamos participar em usuários. Ao contrário de antes, precisamos ter duas instruções de junção nesta consulta. Este é o ID do usuário. Finalmente, precisamos filtrar por todos os usuários com o nome John, e estamos interessados apenas em visitas em vez de proprietários. É isso, agora podemos ver os endereços que John visitou e suas viagens. Isso conclui o código. Vamos ir em frente e navegar de volta para nossos slides. Aqui vamos rever diferentes conceitos que discutimos. Discutimos ordem, grupo por, limite, deslocamento e junções complicadas. Usamos tudo isso para construir consultas e nosso estudo de caso do Airbnb para cada página diferente no site da Airbnb. Isso conclui a Lição 9. Agora, Airbnb em si é muito mais sofisticado do que o design de banco de dados que construímos. Se você tem idéias para estender este design de banco de dados, vá em frente e faça um brainstorm. Adicione ao seu [inaudível], expanda-o e compartilhe nas guias do projeto. Parabéns por terminar este terceiro e último estudo de caso. Assista ao próximo vídeo para obter um resumo do que você aprendeu e das próximas etapas. 11. Próximos passos: Você já construiu não um, mas três bancos de dados. Você viu três exemplos do que não fazer, recebeu três dicas de design de banco de dados e cobriu um grande número de conceitos de design de banco de dados diferentes. Lembre-se, o design do seu banco de dados é fundamental para a sanidade de sua base de código. Faça isso direito, e o resto do desenvolvimento será muito mais fácil. Agora, se você ainda não fez isso, escolha seu aplicativo favorito. Pode ser uma ideia existente, uma ideia revolucionária que só você conhece, ou uma nova funcionalidade. Elabore um gráfico de relacionamentos de entidade e mostre-nos o que você tem carregando-o na guia projetos e recursos. É isso, ainda há muito a aprender. Se você gostaria de levar seu conhecimento de design de banco de dados para o próximo nível, aqui está uma lista de tópicos para começar; outros tipos de banco de dados, como se comunicar entre a camada lógica e o banco de dados e, finalmente, outros conceitos de banco de dados. Certifique-se de também olhar para outras 101 aulas no meu perfil Skillshare, incluindo aquelas em visão computacional e outras em ciência de dados. Parabéns mais uma vez por chegar ao final do curso e até a próxima vez.