Aula 4 — Banco de Dados III

Módulo 2 · Ciclo Comum · IN02 · Aula 4 de 11

Banco de Dados III

JOINs e Consultas Avançadas — cruzando dados entre tabelas relacionadas

🔗 INNER JOIN

⬅️ LEFT JOIN

🔢 GROUP BY

📊 HAVING

🔍 Subconsultas

🏗️ Relações N:N

⏱️ Daily — 15 Minutos

O que você fez? O que vai fazer? Algum impedimento?

15:00

✅ O que fiz 🎯 O que vou fazer 🚧 Impedimentos 📦 Progresso do projeto

📋 Agenda da Aula 4

Estrutura e objetivos da aula de hoje

🕐 Bloco 1 — JOINs (25 min)

INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN com exemplos práticos.

🕑 Bloco 2 — Agregações e Agrupamentos (25 min)

GROUP BY, HAVING, funções de agregação COUNT, SUM, AVG, MIN, MAX.

🕒 Bloco 3 — Subconsultas e Window Functions (20 min)

Subconsultas correlacionadas e funções de janela ROW_NUMBER, RANK.

🎯 Objetivo da Aula

Ao final, o aluno deve combinar tabelas com JOINs e produzir relatórios com agrupamentos.

1ª Forma Normal — Atomicidade

Cada célula contém um único valor indivisível · sem listas, sem grupos repetidos

📐 Definição formal

Uma relação está em 1FN quando todos os atributos são atômicos: cada interseção de linha e coluna armazena exatamente um valor do domínio, sem coleções, listas ou estruturas aninhadas.

❌ Viola 1FN

id	nome	telefones
1	Ana	9999-1, 9999-2
2	Bruno	8888-3

Coluna telefones guarda múltiplos valores → inviabiliza filtros, joins e índices.

✅ Em 1FN

usuario_id	telefone
1	9999-1
1	9999-2
2	8888-3

Cada telefone vira uma linha própria → consultas previsíveis e indexáveis.

2ª Forma Normal — Dependência Total da Chave

Estar em 1FN + cada atributo não-chave depende da chave PRIMÁRIA INTEIRA (não de parte dela)

📐 Definição formal

Uma relação está em 2FN se está em 1FN e nenhum atributo não-chave depende parcialmente da chave primária. A 2FN só faz diferença quando há chave composta — com chave simples, 1FN ⇒ 2FN automaticamente.

❌ Viola 2FN

pedido_itens(pedido_id, produto_id, nome_produto, preco_unit, quantidade)

nome_produto e preco_unit dependem só de produto_id — parte da chave
Mudar o nome de um produto exige atualizar várias linhas
Risco constante de inconsistência

✅ Em 2FN

produtos(id, nome, preco_unit)

pedido_itens(pedido_id, produto_id, quantidade)

Atributos do produto vivem em produtos
pedido_itens guarda só o que depende da chave inteira
Reduz redundância e elimina anomalias de atualização

3ª Forma Normal — Sem Dependência Transitiva

Estar em 2FN + nenhum atributo não-chave depende de outro atributo não-chave

📐 Definição formal

Uma relação está em 3FN se está em 2FN e nenhum atributo não-chave depende transitivamente da chave (cadeia chave → A → B, onde A e B são não-chave). Cada atributo deve descrever diretamente a chave primária.

❌ Viola 3FN

pedidos(id, cliente_id, cep, cidade, estado)

cidade e estado dependem de cep, não da chave id
Cadeia transitiva: id → cep → cidade
Atualizar a cidade de um CEP exige varrer todos os pedidos

✅ Em 3FN

enderecos(cep, cidade, estado)

pedidos(id, cliente_id, cep)

Localização fica centralizada em enderecos
JOIN traz cidade/estado quando necessário
Elimina anomalias de atualização e inserção

Por que JOINs?

O problema de dados separados em tabelas

tabela: usuarios

id | nome | email

----+-------------+------------------

1 | Ana Lima | ana@inteli.edu

2 | Bruno Costa | bruno@inteli.edu

3 | Carla Dias | carla@inteli.edu

tabela: pedidos

id | usuario_id | total | status

----+-----------+---------+----------

10 | 1 | 249.90 | aprovado

11 | 1 | 89.00 | aprovado

12 | 2 | 510.50 | pendente

⬇️ Como cruzar esses dados? ⬇️

🔗 A solução: JOIN

Sistemas reais têm dezenas de tabelas relacionadas. O JOIN permite combinar linhas de duas ou mais tabelas com base em uma coluna em comum — normalmente uma chave estrangeira (usuario_id) ligada à chave primária (id).

Tipos de JOIN — Diagrama Visual

Cada tipo define quais linhas aparecem no resultado

INNER JOIN

Interseção

Somente registros com correspondência em AMBAS as tabelas. Linhas sem par são descartadas.

FROM a INNER JOIN b ON a.id = b.a_id

LEFT JOIN

Todos da esquerda

TODOS da esquerda + correspondências da direita. NULL se não houver correspondência.

FROM a LEFT JOIN b ON a.id = b.a_id

RIGHT JOIN

Todos da direita

TODOS da direita + correspondências da esquerda. NULL se não houver correspondência.

FROM a RIGHT JOIN b ON a.id = b.a_id

FULL OUTER

União total

TODOS de ambas as tabelas. NULL onde não há correspondência em nenhum dos lados.

FROM a FULL OUTER JOIN b ON a.id = b.a_id

💡 Na prática

O INNER JOIN e o LEFT JOIN são os mais usados no dia a dia. O RIGHT JOIN é equivalente ao LEFT JOIN com as tabelas invertidas. O FULL OUTER JOIN é raro mas útil para auditorias de dados.

INNER JOIN na Prática

Buscando pedidos com os dados completos do usuário

inner_join.sql

📊 Resultado (3 linhas)

result set

nome	pedido_id	total	status
Ana Lima	10	249.90	aprovado
Ana Lima	11	89.00	aprovado
Bruno Costa	12	510.50	aprovado

LEFT JOIN — Incluindo Nulos

Todos os usuários, mesmo sem pedidos

left_join.sql

⚠️ NULL no resultado

LEFT JOIN retorna NULL para colunas da tabela direita quando não há correspondência. Use COALESCE(coluna, 0) para substituir NULL por zero.

💡 Quando usar LEFT JOIN?

Relatório de todos os clientes (com ou sem pedidos)
Verificar registros órfãos (sem par)
Dados opcionais — endereços, fotos de perfil

GROUP BY e Funções de Agregação

Agrupando linhas e calculando estatísticas

group_by.sql

-- Resumo de pedidos por usuário

SELECT u.nome,

COUNT(p.id) AS qtd,

SUM(p.total) AS total,

AVG(p.total) AS media,

MAX(p.total) AS maior,

MIN(p.total) AS menor

FROM usuarios u

JOIN pedidos p ON p.usuario_id = u.id

GROUP BY u.id, u.nome;

COUNT

Contar linhas

COUNT(p.id) → 5

SUM

Somar valores

SUM(p.total) → 1349.40

AVG

Média aritmética

AVG(p.total) → 269.88

MAX

Maior valor

MAX(p.total) → 510.50

MIN

Menor valor

MIN(p.total) → 49.90

HAVING — Filtrando Grupos

WHERE filtra linhas · HAVING filtra grupos (após GROUP BY)

having.sql

⚡ WHERE vs HAVING

WHERE — filtra linhas individuais antes do agrupamento
HAVING — filtra grupos após o GROUP BY
Você pode usar ambos na mesma query!

📌 Exemplo mental

"Mostre clientes que compraram mais de 5 vezes e gastaram mais de R$ 1.000" — isso é um HAVING, não um WHERE, pois os valores são calculados por grupo.

Relações N:N — Tabela de Junção

Um pedido pode ter vários produtos; um produto pode estar em vários pedidos

📦 pedidos

🔑 id (PK)

usuario_id (FK)

status

criado_em

↔️

🔗 pedido_itens

🔑 pedido_id (FK)

🔑 produto_id (FK)

quantidade

preco_unitario

↔️

🛒 produtos

🔑 id (PK)

nome

preco

estoque

nn_join.sql — 3 tabelas

Subconsultas (Subqueries)

Queries dentro de queries para filtros e transformações complexas

subqueries.sql

EXPLAIN — Entendendo Performance

O PostgreSQL revela como vai executar sua query antes de rodar

explain_analyze.sql

Seq Scan

Leitura sequencial — lento

Percorre todas as linhas da tabela. Aceitável para tabelas pequenas, péssimo para tabelas grandes.

Index Scan

Leitura por índice — rápido

Usa o índice B-tree para encontrar diretamente as linhas. Ideal para WHERE id = ?.

Bitmap Scan

Scan combinado — eficiente

Combina vários índices. Bom para queries com múltiplos filtros ou ranges (BETWEEN).

Pausa estratégica — vamos respirar e pivotar 🎯

Encerramos o bloco de manipulação de dados · entramos no de arquitetura de back-end

✅ O que dominamos

Modelo relacional: tabelas, FKs, normalização
JOINs: INNER, LEFT, RIGHT, FULL OUTER
Análise: GROUP BY, HAVING, subqueries
Performance: EXPLAIN, índices, planos de execução

Sabemos guardar e consultar dados.

🏗️ Para onde vamos

Modelagem estática: diagrama de classes (UML)
Modelagem dinâmica: como entidades se comunicam
Arquitetura: Models, Controllers, Services
Próxima aula: tudo virando código Node.js

Vamos estruturar o sistema que usa esses dados.

A pergunta-chave agora: como desenhar as entidades de software que vão acessar essas tabelas? Como elas se comunicam, herdam comportamento e compõem outras? É aí que a UML entra como ponte entre o ER de dados e o código do back-end.

Modelagem Estática — Diagrama de Classes (UML)

Da tabela ao objeto · A estrutura do domínio em um instante

Cliente

− id: int

− nome: string

− email: string

− dataCadastro: Date

+ cadastrar(): void

+ listarPedidos(): Pedido[]

+ desativar(): void

+public

−private

#protected

~package

Três compartimentos: nome (topo), atributos (estado, no meio) e operações (comportamento, embaixo). A visibilidade controla quem enxerga cada elemento.

🔗 Tipos de relacionamento

Associação	A — B	Relação genérica entre duas classes
Agregação	A ◇— B	Todo–parte fraco (parte sobrevive)
Composição	A ◆— B	Todo–parte forte (parte morre com o todo)
Generalização	A ◁— B	Herança: B é especialização de A
Realização	A ◁┄ B	B implementa a interface A
Dependência	A ┄→ B	A usa B de forma transiente

Multiplicidade nas pontas: 1, 0..1, *, 1..* — equivale à cardinalidade do ER e ao "1:1, 1:N, N:M" do banco. Cada associação UML será um JOIN no SQL.

Do Modelo de Domínio ao Back-End I

Classes que se comunicam · superclasses, herança e a ponte para Node.js

🔗 Domínio do e-commerce — classes em cadeia

🧬 Superclasse, herança e interface

🚀 Aula 5 — Back-End I

Cada classe concreta vira um Model em Node.js
Cada operação (+ listarPedidos()) vira um método que executa um JOIN
Cada associação orienta uma chamada entre Models
Cada composição orienta ON DELETE CASCADE e estratégia transacional
Cada herança vira decisão de mapeamento ORM (tabela única, por subclasse, por hierarquia)

Estudo de Caso: Checkout — Sequência + SQL Final

Modelagem dinâmica e implementação física da mesma transação

④ Diagrama de Sequência — POST /pedidos

⑤ SQL Final — DDL + DML transacional

checkout.sql

Já temos os dados (estática) e o fluxo (dinâmica): conceitual = o que existe · lógico = como cada coisa é · sequência = quando e em que ordem · SQL = exatamente como. Falta o diagrama de classes — como tudo isso vira código orientado a objetos.

RM-ODP — As 5 Visões do Sistema

ISO/IEC 10746 · Como diferentes stakeholders enxergam o sistema

🏢 Enterprise
Propósito, regras de negócio

→

📋 Information
Dados, entidades, relações

→

⚙️ Computational
Componentes, interfaces

→

🔧 Engineering
Distribuição, canais, nós

→

💻 Technology
Plataformas, padrões

Esta aula: Visão Information — modelamos como dados de múltiplas tabelas se relacionam e como consultá-los com eficiência via JOINs.

Esta Aula: Information

RM-ODP · RF, RNF e Artefato desta aula

📌 Requisito Funcional

Consultar dados compostos necessários às funcionalidades do sistema — JOINs entre tabelas relacionadas.

📦 Artefato

🔗 Consultas com JOINs do projeto
📚 Catálogo de consultas reutilizáveis
📊 EXPLAIN ANALYZE de queries críticas

⚖️ RNF — 8 Eixos ISO/IEC 25010

DES Desempenho	✅ Índices + EXPLAIN
CONF Confiabilidade	✅ FK + integridade
USAB Usabilidade	— N/A
SUP Suportabilidade	→ Consultas documentadas
SEG Segurança	→ Acesso controlado ao DB

Estudo de Caso: Checkout — ER Conceitual

Lente 1 de 3 · quais entidades existem e como se relacionam

Cenário: cliente confirma pedido com N produtos · sistema valida estoque · cria pedido · registra itens · reduz estoque — tudo em uma única transação ACID.

As cardinalidades já contam tudo: 1:N entre cliente e pedido, 1:N com composição entre pedido e itens (não vivem sozinhos), N:1 entre itens e produto.

Estudo de Caso: Checkout — ER Lógico

Lente 2 de 3 · atributos, tipos e chaves (PK / FK / UK)

Decisões-chave: email UK impede contas duplicadas · preco_un em itens_pedido congela o valor da venda (RN-02) · cada FK vira um JOIN no SQL final.

Estudo de Caso: Checkout — Diagrama de Classes

Lente final · do banco para o código: atributos + comportamento + multiplicidades UML

O que muda do ER: classes ganham operações (+confirmar(), +reduzirEstoque()) e a relação Pedido ◆-- ItemPedido é composição — o item morre com o pedido (= ON DELETE CASCADE). É a ponte para os Models do Back-End.

Checklist da Aula

O que você aprendeu hoje — e o que vem a seguir

✅

INNER, LEFT, RIGHT JOIN

Cruzar dados entre tabelas relacionadas

✅

GROUP BY e funções de agregação

COUNT, SUM, AVG, MAX, MIN

✅

HAVING para filtrar grupos

Filtragem após agrupamento

✅

Relações N:N com tabela de junção

pedido_itens ligando pedidos e produtos

✅

Subconsultas

WHERE IN, FROM subquery

✅

Performance básica com EXPLAIN

Seq Scan, Index Scan, Bitmap Scan

➡️

Próxima aula: Back-End com Node.js

Aula 5 — Models, Controllers e Express.js

🔗

Dados conectados!

Agora você sabe cruzar tabelas, agregar dados e entender a performance das suas queries. Na próxima aula, o banco ganha vida com Node.js + Express.

✅ INNER JOIN

✅ LEFT JOIN

✅ GROUP BY

✅ HAVING

✅ Relações N:N

✅ Subconsultas

✅ EXPLAIN

Módulo 2 · Ciclo Comum · Aula 4 de 11