PostgreSQL: Views, Sub-Consultas e Funções

Chaves estrangeiras e Tipos - Introdução

Olá, pessoal. Boas-vindas à Alura. Meu nome é Vinicius Dias e, embora vocês não estejam me vendo, vou guiar vocês nessa continuação do treinamento de PostgreSQL.

Nesse treinamento revisaremos conceitos bem importantes, como chaves primárias, chaves estrangeiras e o conceito de unique, e como ele pode ser usado por chaves estrangeiras. Veremos um pouco das diferenças de sintaxe, principalmente nas chaves estrangeiras, e um exemplo prático de chave primária composta.

A partir desses estudos, evoluiremos até aprendermos uma sintaxe nova para inserir valores, preenchendo várias linhas com uma única instrução, descobrindo como inserir valores sem informar o nome dos campos até chegar ao ponto em que criaremos relatórios.

Esses relatórios serão feitos passo a passo para você acompanhar o processo de geração de relatórios reais. Para criação desses relatórios, obviamente utilizaremos bastante conteúdo já aprendido. No avanço desses relatórios, começaremos a entender melhor sobre novos operadores, como o IN . Também aprenderemos como utilizar consultas dentro de outras consultas e, a partir disso, nomearemos as consultas.

Além disso, veremos bastante sobre funções do Postgres, para trabalhar com strings, datas, números, conversão de títulos e outras. Então, se durante o treinamento ficar dúvidas, não hesite em abrir uma dúvida no fórum. Eu tento responder pessoalmente sempre que possível, mas quando eu não consigo, a nossa comunidade de alunos, moderadores e instrutores é muito solícita e, com certeza, alguém vai conseguir te ajudar.

Boas-vindas novamente. Espero que você tire bastante proveito desse treinamento e vamos para o próximo vídeo para finalmente começarmos a trabalhar nesse banco de dados.

Chaves estrangeiras e Tipos - Introdução ao banco

Faremos uma revisão bem rápida e resumida do que vimos no último curso. Começamos entendendo o que é um banco de dados, um local onde armazenamos informações bem definidas. Portanto, banco de dados relacional, como o Postgre, com o qual estamos trabalhando, funciona um pouco parecido com uma planilha.

Em uma planilha temos a coluna, onde definimos qual será a informação inserida, e cada linha representa a informação, como podemos ver em um documento de planilha.

Temos as colunas "primeiro_nome", "ultimo_nome" e "data_nascimento". Cada uma das linhas dessa planilha representam uma informação, ou seja, um registro. Basicamente é assim que funciona o banco de dados relacional, mas além de termos linhas e colunas, temos diferentes tabelas, como se fossem planilhas diferentes no mesmo arquivo. Por exemplo, se clicarmos na aba "Página 2" do arquivo de planilha, teremos outro arquivo, como no exemplo a seguir, onde há o nome dos cursos cadastrados.

Dessa forma, pensando de modo bem rudimentar, é organizado um banco de dados.

Voltando ao pgAdmin, notamos que no servidor gerenciado pelo Postgre temos um banco de dados chamado "alura". Nesse banco, que funciona como o arquivo da planilha, temos uma aba com a tabela "aluno" com o campo "id", que é nossa chave primária.

Revisando o conceito de chave primária, sabemos que ela é um identificador único. É através da chave primária que o banco consegue identificar que o aluno é aquele em específico, e não qualquer outro. Através da chave primária, identificamos um registro na nossa tabela.

Na tabela "aluno" também temos o campo "primeiro_nome" do tipo VARCHAR, um dado do tipo texto, que não pode ser nulo, definido pelo comando NOT NULL. Temos as mesmas especificações para o campo "ultimo_nome", e temos o "data_nascimento", sendo obviamente do tipo DATE, que também não pode ser nula.

Temos agora outra tabela, a "curso" que é equivalente ou a outra aba do mesmo arquivo de planilha que vimos. Nela temos um identificador para o curso, definido no campo "id", e o nome do curso, definido no campo "nome", sendo que para esse campo também foi codado NOT NULL .

É importante informar que sempre que determinarmos um campo como chave primária, ele já possui a informação NOT NULL , ou seja, não nulo. Então uma chave primária é única, não pode ser nula e funciona como o identificador daquela tabela.

Poderíamos codar no lugar do id SERIAL PRIMARY KEY , sendo que SERIAL é um comando para os números irem se incrementando, o campo cpf CHAR(11) PRIMARY KEY , que também seria uma chave primária. Nesse caso, ao invés de deixar o banco de dados cuidar e gerar o identificador, teríamos que informar para ele, mas continuaria funcionando perfeitamente.

Definidas essas duas tabelas, criamos uma tabela de relacionamentos. Ainda conversaremos mais sobre relacionamento, mas, basicamente, informamos nessa tabela que um aluno e um curso estão se relacionando. Podemos ter vários alunos no mesmo curso e um aluno matriculado em vários cursos.

Além disso, adicionamos a informação de uma chave primária composta, ou seja, o identificador único de cada registro dessa tabela é o conjunto dessas duas colunas. Dessa forma não podemos matricular o mesmo aluno no mesmo curso mais de uma vez, ou seja, só podemos ter uma relação daquele aluno com determinado curso, não podendo repetir essa informação. Assim utilizamos as duas colunas como identificador único, isto é, como chave primária.

Como são conceitos tratados no treinamento anterior, a explicação foi bem rápida, mas caso tenha alguma dúvida, pode abrir uma questão no fórum que conversaremos para resolver esse problema.

Agora que realizamos essa revisão, vamos incrementar o banco de dados.

Chaves estrangeiras e Tipos - Chaves estrangeiras

Agora faremos uma revisão sobre chaves estrangeiras, aprendendo coisas novas e recapitulando o que já vimos. Começaremos observando a tabela "aluno-curso". Essa tabela, como descobrimos no treinamento anterior, salva a relação entre alunos e cursos. Portanto, nela definimos quais alunos estão matriculados em cada curso.

No treinamento anterior vimos uma forma de criar uma chave estrangeira através do FOREIGN KEY (nome_do_campo) REFERENCES nome_da_tabela(nome_do_campo_na_tabela) , ou seja, criando uma chave estrangeira no campo da nossa tabela que referencia um campo de outra tabela. Também realizamos esse mesmo processo com outra sintaxe, codando diretamente na linha de definição do campo, como observamos em aluno_id INTEGER NOT NULL REFERENCES aluno(id) .

Basicamente a chave estrangeira do código anterior cria uma restrição, informando que sempre que um valor for inserido em "aluno_curso", o dado em aluno_id precisa ser um número inteiro não- nulo e precisa referenciar o que já existe na tabela "aluno" no campo "id". Dessa forma, quando inserimos um aluno_id , ele precisa existir no campo "id" da tabela "aluno".

Dessa forma garantimos a consistência do banco de dados, ou seja, que sempre será inserido na tabela "aluno_curso" um aluno que já exista. O mesmo acontece com curso_id. Essa é a definição de chave estrangeira: uma restrição para realizarmos relacionamentos.

Vamos ampliar nosso banco de dados. A partir de agora nossos cursos precisam ter categorias, e a primeira solução que me vem à mente é colocarmos na tabela "curso" o campo categoria VARCHAR(255) NOT NULL , que não pode ser nulo porque todo curso precisa estar em uma categoria. Todavia, imaginemos que o curso de HTML está na categoria "Front-End", o curso de Postgre faz parte da categoria "Banco de Dados" e o curso de PHP faz parte da categoria "Programação".

Podemos ter vários cursos na mesma categoria e, se isso acontecer, repetiremos o VARCHAR , ou seja, o texto, em vários registros, resultando na ocupação de mais espaço do que o necessário. Ocuparemos o espaço de várias strings em linhas diferentes, mesmo que seja a mesma string, porque informaremos várias vezes o mesmo dado. Contudo, o principal problema será as possíveis diferenças na digitação, como "programaçao", "programacão", "Front-End", "front end".

Esse problema pode gerar inconsistência no banco de dados. Para evitá-lo, uma solução interessante é termos uma tabela de categorias. Então vamos criá-la e salvaremos todas as informações que uma categoria pode ter.

CREATE TABLE categoria (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255) NOT NULL
);

Lembrando que não é obrigatório que o banco de dados tenha uma chave primária, ou seja, uma forma única de identificá-lo, mas considere como obrigatório. Na nossa tabela "categoria", a chave primária é o "id", que será formado um número serial. Nossa tabela também tem o campo de nome e, como toda categoria precisa de um nome, ele recebeu a propriedade NOT NULL .

A princípio essas são as únicas informações que precisamos, então podemos selecionar essa query e executar com "F5", recebendo a mensagem de sucesso. Precisamos agora relacionar a tabela "categoria" com os cursos. Para isso, ao invés de colocarmos o nome das categorias no código da tabela "curso", colocaremos apenas o "id".

Assim ocuparemos menos espaço, já que, ao invés de uma string, armazenaremos um número inteiro. Não haverá o risco de errar, porque usaremos uma chave estrangeira, que nos impedirá de inserir uma categoria que não exista previamente.

Então definiremos o campo "categoria_id" na tabela "curso".

CREATE TABLE curso(
    id SERIAL PRIMARY KEY, 
    nome VARCHAR(255) NOT NULL, 
    categoria_id INTEGER NOT NULL REFERENCES categoria(id)
);

Para realizarmos essa atualização, precisamos executar DROP TABLE curso; , porque não podemos criar outra tabela "curso". Futuramente estudaremos como alterar uma tabela já existente, mas agora iremos apagá-la. Contudo, ao tentarmos excluir essa tabela, recebemos uma mensagem de erro, informando que existem outras tabelas dependendo dela, no caso, "aluno_curso".

Então precisamos executar DROP TABLE aluno_curso, curso; , apagando "aluno_curso" e, em seguida, "curso". Após apagarmos a tabela, podemos recriá-la com o código acima, inserindo o campo categoria_id . Agora temos uma nova chave estrangeira.

Vou deixar de desafio para vocês o preenchimento desse banco de dados, da forma como preferirem, com alunos, categorias, cursos e matricular os alunos nos cursos. Inclusive podemos mover o trecho da tabela “categoria” para antes de CREATE TABLE curso, uma vez que antes de termos um curso, precisamos de uma categoria para ele.,

Na próxima aula, que será mais teórica, conversaremos sobre tipos de relacionamentos.

Sobre o curso PostgreSQL: Views, Sub-Consultas e Funções

O curso PostgreSQL: Views, Sub-Consultas e Funções possui 76 minutos de vídeos, em um total de 43 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Chaves estrangeiras e Tipos
• Praticando comandos
• Executando Sub-consultas
• Usando funções
• Criando views