Até aqui, banco de dados apareceu como ferramenta: modelar tabelas, escrever consultas, usar o ORM. Esta disciplina abre a caixa. O objeto de estudo passa a ser o próprio sistema gerenciador: como ele guarda os dados em disco, como garante que uma transferência bancária não perca dinheiro no meio, e por que a mesma consulta às vezes leva 3 milissegundos e às vezes 3 segundos.

É uma disciplina prática. A maior parte dos encontros tem metade ou mais do tempo em laboratório, e a exposição serve para dar sentido ao que será feito no mesmo dia.

Avaliação

Na próxima aula cada um vai montar a própria máquina virtual Linux com PostgreSQL instalado, configurar o acesso externo e conectar de fora. Hoje não se instala nada: o pgAdmin já está nas máquinas do laboratório.

Todo o SQL desta página é executado no pgAdmin 4, já instalado nas máquinas do laboratório. Esta página é o material de consulta: o comando fica aqui, com botão de copiar, e você executa lá.

Os quatro primeiros passos

Se você faltou, chegou atrasado ou quer refazer em casa

O script abaixo cria as tabelas e os dados usados das seções 7 em diante, de uma vez só. Ele também está disponível como arquivo: baixar banco-aula01.sql. No pgAdmin, abra com Ctrl+O e execute com F5.

DROP TABLE IF EXISTS livro;
DROP TABLE IF EXISTS autor;

CREATE TABLE autor (
  id         serial PRIMARY KEY,
  nome       text NOT NULL,
  pais       varchar(50),
  nascimento date
);

CREATE TABLE livro (
  id         serial PRIMARY KEY,
  titulo     text NOT NULL,
  isbn       varchar(20) UNIQUE,
  ano        integer CHECK (ano > 1400),
  disponivel boolean DEFAULT true,
  autor_id   integer NOT NULL REFERENCES autor(id)
);

INSERT INTO autor (nome, pais, nascimento) VALUES
  ('Machado de Assis',       'Brasil',   '1839-06-21'),
  ('Clarice Lispector',      'Brasil',   '1920-12-10'),
  ('Graciliano Ramos',       'Brasil',   '1892-10-27'),
  ('Jorge Amado',            'Brasil',   '1912-08-10'),
  ('Gabriel García Márquez', 'Colômbia', '1927-03-06'),
  ('Autor Sem Livro',        'Brasil',   NULL);

INSERT INTO livro (titulo, isbn, ano, autor_id) VALUES
  ('Dom Casmurro',        '978-85-01', 1899, 1),
  ('Memórias Póstumas',   '978-85-02', 1881, 1),
  ('Quincas Borba',       NULL,        1891, 1),
  ('A Hora da Estrela',   '978-85-03', 1977, 2),
  ('Vidas Secas',         '978-85-04', 1938, 3),
  ('Capitães da Areia',   '978-85-05', 1937, 4),
  ('Cem Anos de Solidão', '978-85-06', 1967, 5);

Uma tabela é um conjunto de linhas que compartilham as mesmas colunas. Os nomes formais aparecem na bibliografia e vale reconhecê-los quando surgirem.

Todos os valores de uma coluna são do mesmo tipo. Essa garantia é o que separa um banco de uma planilha, e é o que permite comparar, ordenar e indexar sem precisar verificar valor por valor.

As duas chaves

O comando que cria um banco é direto, e é o único da aula que você não vai executar aqui: o executor já coloca você dentro de um banco pronto.

CREATE DATABASE biblioteca;

A tabela é onde as decisões começam. Copie o bloco abaixo para o pgAdmin, execute, e depois leia cada linha com calma: cada pedaço dela é uma escolha de projeto.

DROP TABLE IF EXISTS livro;
DROP TABLE IF EXISTS autor;

CREATE TABLE autor (
  id         serial       PRIMARY KEY,
  nome       text         NOT NULL,
  pais       varchar(50),
  nascimento date
);

SELECT * FROM autor;

O tipo não é detalhe de sintaxe. Ele define o que a coluna aceita, quanto ocupa e quais operações fazem sentido sobre ela.

A linha destacada merece demonstração. Rode isto no pgAdmin e olhe o resultado da soma:

SELECT (0.1::float8 + 0.2::float8)::text         AS soma_float,
       (0.1::numeric + 0.2::numeric)::text       AS soma_numeric,
       (0.1::float8 + 0.2::float8) = 0.3::float8 AS float_bate;

O float devolve 0.30000000000000004, e a comparação com 0.3 dá falso. Num cálculo isolado essa diferença é invisível. Veja o que ela faz somada, em mil lançamentos de um centavo:

SELECT sum(0.01::float8)::text  AS total_float,
       sum(0.01::numeric)::text AS total_numeric
FROM generate_series(1, 1000);

Agora uma tabela com vínculo e com regras. Cada restrição declarada aqui é um tipo de dado errado que deixa de existir na base.

CREATE TABLE livro (
  id         serial       PRIMARY KEY,
  titulo     text         NOT NULL,
  isbn       varchar(20)  UNIQUE,
  ano        integer      CHECK (ano > 1400),
  disponivel boolean      DEFAULT true,
  autor_id   integer      NOT NULL REFERENCES autor(id)
);

INSERT INTO autor (nome, pais) VALUES ('Machado de Assis', 'Brasil');

SELECT id, nome FROM autor;

Três tentativas que precisam falhar

No pgAdmin, selecione uma linha por vez e aperte F5, mantendo as outras duas comentadas. O erro é o resultado esperado: é o banco fazendo o trabalho dele.

-- 1) não existe autor com id 999
INSERT INTO livro (titulo, ano, autor_id) VALUES ('Livro sem autor', 2020, 999);

-- 2) 1200 não passa no CHECK
-- INSERT INTO livro (titulo, ano, autor_id) VALUES ('Livro antigo', 1200, 1);

-- 3) falta o título, que é NOT NULL
-- INSERT INTO livro (ano, autor_id) VALUES (2020, 1);

Uma linha por vez, ou várias de uma vez. Repare que ninguém informa o id: o serial cuida disso.

INSERT INTO autor (nome, pais, nascimento) VALUES
  ('Clarice Lispector',      'Brasil',   '1920-12-10'),
  ('Graciliano Ramos',       'Brasil',   '1892-10-27'),
  ('Jorge Amado',            'Brasil',   '1912-08-10'),
  ('Gabriel García Márquez', 'Colômbia', '1927-03-06');

INSERT INTO livro (titulo, isbn, ano, autor_id) VALUES
  ('Dom Casmurro',        '978-85-01', 1899, 1),
  ('A Hora da Estrela',   '978-85-03', 1977, 2),
  ('Vidas Secas',         '978-85-04', 1938, 3),
  ('Capitães da Areia',   '978-85-05', 1937, 4),
  ('Cem Anos de Solidão', '978-85-06', 1967, 5);

SELECT id, nome, pais FROM autor ORDER BY id;

O SELECT é o comando que você mais vai escrever na vida profissional, e o único que ganha uma unidade inteira desta disciplina.

SELECT titulo, ano FROM livro WHERE ano > 1950;

-- experimente trocar por uma destas:
-- SELECT titulo FROM livro WHERE ano BETWEEN 1900 AND 1950;
-- SELECT titulo FROM livro WHERE titulo LIKE '%Solidão%';
-- SELECT titulo FROM livro WHERE autor_id IN (1, 2);
-- SELECT titulo FROM livro WHERE isbn IS NULL;

SELECT count(*) AS com_igual  FROM livro WHERE isbn = NULL;
SELECT count(*) AS com_is_null FROM livro WHERE isbn IS NULL;

Ordenar e limitar fecham o repertório básico. Sem ORDER BY não existe garantia nenhuma de ordem, mesmo que o resultado pareça consistente nos seus testes.

SELECT titulo, ano FROM livro ORDER BY ano DESC LIMIT 3;

O autor_id do livro é um número, e ninguém quer ler número em relatório. O JOIN troca o número pelo dado.

SELECT l.titulo, a.nome AS autor, l.ano
FROM livro l
JOIN autor a ON a.id = l.autor_id
ORDER BY a.nome;

A palavra INNER é opcional: JOIN sozinho já significa isso. Os apelidos de tabela, l e a, são como se escreve no dia a dia.

A diferença que decide o relatório

O cenário tem um autor cadastrado sem nenhum livro. Rode o bloco abaixo, depois troque LEFT JOIN por JOIN e execute de novo. Conte as linhas nas duas versões.

SELECT a.nome, l.titulo
FROM autor a
LEFT JOIN livro l ON l.autor_id = a.id
ORDER BY a.nome;

SELECT count(*)        AS total_livros,
       min(ano)        AS mais_antigo,
       max(ano)        AS mais_recente,
       round(avg(ano)) AS ano_medio
FROM livro;

O GROUP BY faz a mesma conta, mas por grupo. Note o LEFT JOIN: sem ele, o autor sem livro sumiria da lista em vez de aparecer com zero.

SELECT a.nome, count(l.id) AS quantos_livros
FROM autor a
LEFT JOIN livro l ON l.autor_id = a.id
GROUP BY a.nome
ORDER BY quantos_livros DESC;

SELECT a.nome, count(l.id) AS quantos
FROM autor a
JOIN livro l ON l.autor_id = a.id
GROUP BY a.nome
HAVING count(l.id) > 1;

Duas contagens que parecem iguais e não são. A diferença entre elas é exatamente a quantidade de nulos da coluna:

SELECT count(*)    AS todas_as_linhas,
       count(isbn) AS com_isbn
FROM livro;

UPDATE livro SET disponivel = false WHERE id = 1;

SELECT id, titulo, disponivel FROM livro WHERE id = 1;

UPDATE livro SET disponivel = false;   -- muda TODAS as linhas
DELETE FROM livro;                     -- apaga TODAS as linhas

Mudanças de estrutura usam ALTER TABLE. E apagar tabela tem ordem: primeiro quem aponta, depois quem é apontado. É a inversa da ordem de criação.

ALTER TABLE livro ADD COLUMN paginas integer;
ALTER TABLE livro DROP COLUMN paginas;

-- esta linha falha: livro depende de autor
DROP TABLE autor;

-- a ordem correta seria:
-- DROP TABLE livro;
-- DROP TABLE autor;

Os nomes valem porque a disciplina se organiza em torno deles. Hoje você usou as três primeiras; as outras duas são assunto do semestre.

Uma empresa de manutenção de equipamentos controla os chamados dos clientes numa planilha. Três coisas já deram errado: chamado atribuído a técnico que não trabalha mais lá, cliente cadastrado duas vezes com grafias diferentes, e um relatório de produtividade que ninguém consegue fechar porque os valores estão escritos em texto livre.

Modele isso como banco de dados.

Parte 1 · Modelagem

O conjunto precisa ter, no mínimo, um NOT NULL, um UNIQUE, um CHECK e um DEFAULT, além das chaves.

Parte 2 · Dados

Quatro clientes em pelo menos duas cidades, três técnicos sendo um inativo e um sem nenhum chamado, e oito chamados distribuídos de forma desigual, com pelo menos um sem valor preenchido. A distribuição desigual não é capricho: sem ela as consultas da parte 3 devolvem tudo igual e não provam nada.

Parte 3 · Consultas

Parte 4 · Três perguntas

-- Parte 1: crie as três tabelas aqui, na ordem certa.
-- Lembre: chamado referencia as outras duas, então vem por último.

CREATE TABLE cliente (
  id   serial PRIMARY KEY
  -- continue
);