Semana 9 - Relacionamentos, Chaves Primárias e Estrangeiras

🗄️ Estrutura do Banco de Dados para Prática

Para acompanhar todos os exemplos desta aula, execute o script abaixo no seu banco de dados:

Script Completo de Criação

Execute este script no seu SGBD para criar a estrutura completa com relacionamentos:

-- ============================================
-- SCRIPT COMPLETO PARA AULA DE RELACIONAMENTOS
-- ============================================

-- 1. Criação da tabela CATEGORIAS (Tabela Pai)
CREATE TABLE categorias (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(50) NOT NULL UNIQUE,
    descricao TEXT,
    ativo BOOLEAN DEFAULT TRUE
);

-- 2. Criação da tabela FORNECEDORES (Tabela Pai)
CREATE TABLE fornecedores (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cnpj VARCHAR(18) UNIQUE,
    telefone VARCHAR(20),
    email VARCHAR(100),
    endereco TEXT,
    ativo BOOLEAN DEFAULT TRUE
);

-- 3. Criação da tabela PRODUTOS (Tabela Filha)
CREATE TABLE produtos (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10,2) NOT NULL,
    estoque INTEGER NOT NULL DEFAULT 0,
    categoria_id INTEGER NOT NULL,
    fornecedor_id INTEGER NOT NULL,
    data_cadastro DATE NOT NULL,
    ativo BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id),
    FOREIGN KEY (fornecedor_id) REFERENCES fornecedores(id)
);

-- 4. Criação da tabela CLIENTES (Tabela Pai)
CREATE TABLE clientes (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cpf VARCHAR(14) UNIQUE,
    telefone VARCHAR(20),
    email VARCHAR(100),
    endereco TEXT,
    data_cadastro DATE NOT NULL,
    ativo BOOLEAN DEFAULT TRUE
);

-- 5. Criação da tabela PEDIDOS (Tabela Filha)
CREATE TABLE pedidos (
    id INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL,
    data_pedido DATE NOT NULL,
    valor_total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pendente',
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

-- 6. Criação da tabela ITENS_PEDIDO (Tabela de Relacionamento M:N)
CREATE TABLE itens_pedido (
    id INTEGER PRIMARY KEY,
    pedido_id INTEGER NOT NULL,
    produto_id INTEGER NOT NULL,
    quantidade INTEGER NOT NULL,
    preco_unitario DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
    FOREIGN KEY (produto_id) REFERENCES produtos(id)
);

-- 7. Inserção de dados de exemplo
INSERT INTO categorias (nome, descricao) VALUES
('Informática', 'Produtos de informática e tecnologia'),
('Móveis', 'Móveis para escritório e casa'),
('Eletrônicos', 'Aparelhos eletrônicos diversos'),
('Livros', 'Livros técnicos e literários');

INSERT INTO fornecedores (nome, cnpj, telefone, email, endereco) VALUES
('TechCorp Ltda', '12.345.678/0001-90', '(11) 99999-1111', 'contato@techcorp.com', 'Rua A, 123'),
('Móveis & Cia', '98.765.432/0001-10', '(11) 99999-2222', 'vendas@moveis.com', 'Rua B, 456'),
('EletroMax', '11.222.333/0001-44', '(11) 99999-3333', 'info@eletromax.com', 'Rua C, 789'),
('Editora ABC', '55.666.777/0001-88', '(11) 99999-4444', 'editora@abc.com', 'Rua D, 321');

INSERT INTO produtos (nome, preco, estoque, categoria_id, fornecedor_id, data_cadastro) VALUES
('Notebook Dell', 2500.00, 15, 1, 1, '2023-01-10'),
('Mouse Logitech', 45.00, 50, 1, 1, '2023-01-15'),
('Cadeira Gamer', 350.00, 40, 2, 2, '2023-01-20'),
('Smartphone Samsung', 1200.00, 25, 3, 3, '2023-02-01'),
('Livro SQL Avançado', 89.90, 100, 4, 4, '2023-02-05'),
('Teclado Mecânico', 120.00, 30, 1, 1, '2023-02-10'),
('Mesa Escritório', 200.00, 15, 2, 2, '2023-02-15'),
('Tablet iPad', 2000.00, 10, 3, 3, '2023-02-20');

INSERT INTO clientes (nome, cpf, telefone, email, endereco, data_cadastro) VALUES
('João Silva', '123.456.789-00', '(11) 99999-5555', 'joao@email.com', 'Rua E, 111', '2023-01-05'),
('Maria Santos', '987.654.321-00', '(11) 99999-6666', 'maria@email.com', 'Rua F, 222', '2023-01-10'),
('Pedro Costa', '456.789.123-00', '(11) 99999-7777', 'pedro@email.com', 'Rua G, 333', '2023-01-15'),
('Ana Oliveira', '789.123.456-00', '(11) 99999-8888', 'ana@email.com', 'Rua H, 444', '2023-01-20');

INSERT INTO pedidos (cliente_id, data_pedido, valor_total, status) VALUES
(1, '2023-03-01', 2545.00, 'Entregue'),
(2, '2023-03-02', 350.00, 'Entregue'),
(3, '2023-03-03', 1320.00, 'Pendente'),
(4, '2023-03-04', 89.90, 'Entregue'),
(1, '2023-03-05', 200.00, 'Cancelado');

INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario, subtotal) VALUES
(1, 1, 1, 2500.00, 2500.00),  -- Notebook Dell
(1, 2, 1, 45.00, 45.00),      -- Mouse Logitech
(2, 3, 1, 350.00, 350.00),    -- Cadeira Gamer
(3, 4, 1, 1200.00, 1200.00),  -- Smartphone Samsung
(3, 6, 1, 120.00, 120.00),    -- Teclado Mecânico
(4, 5, 1, 89.90, 89.90),      -- Livro SQL Avançado
(5, 7, 1, 200.00, 200.00);    -- Mesa Escritório

🔑 1. Chaves Primárias (Primary Keys)

O que é uma Chave Primária?

Uma chave primária é um campo (ou conjunto de campos) que identifica unicamente cada registro em uma tabela. Não pode ser nula e deve ser única.

📝 Características das Chaves Primárias:

Única: Não pode haver dois registros com a mesma chave primária
Não nula: Sempre deve ter um valor
Imutável: Não deve ser alterada após criação
Mínima: Deve usar o menor número de campos possível

🎯 Exemplos de Chaves Primárias:

Exemplo 1: Chave Primária Simples

-- Tabela com chave primária simples
CREATE TABLE clientes (
    id INTEGER PRIMARY KEY,  -- Chave primária
    nome VARCHAR(100) NOT NULL,
    cpf VARCHAR(14) UNIQUE
);

-- Inserir dados
INSERT INTO clientes (id, nome, cpf) VALUES
(1, 'João Silva', '123.456.789-00'),
(2, 'Maria Santos', '987.654.321-00');

Exemplo 2: Chave Primária Composta

-- Tabela com chave primária composta
CREATE TABLE itens_pedido (
    pedido_id INTEGER,
    produto_id INTEGER,
    quantidade INTEGER,
    PRIMARY KEY (pedido_id, produto_id)
);

🔍 Consultas com Chaves Primárias:

-- Buscar cliente por ID (chave primária)
SELECT * FROM clientes WHERE id = 1;

-- Buscar produto por ID
SELECT * FROM produtos WHERE id = 3;

-- Contar registros por chave primária
SELECT COUNT(*) FROM clientes;

🔗 2. Chaves Estrangeiras (Foreign Keys)

O que é uma Chave Estrangeira?

Uma chave estrangeira é um campo que referencia a chave primária de outra tabela. Ela estabelece o relacionamento entre as tabelas.

📝 Características das Chaves Estrangeiras:

Referencia: Aponta para a chave primária de outra tabela
Integridade: Garante que o valor existe na tabela referenciada
Pode ser nula: Dependendo do relacionamento
Restrições: Impede exclusão de registros referenciados

🎯 Exemplos de Chaves Estrangeiras:

Exemplo 1: Relacionamento 1:N

-- Tabela PRODUTOS com chave estrangeira
CREATE TABLE produtos (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    categoria_id INTEGER,  -- Chave estrangeira
    FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id)
);

-- Consultar produtos com categoria
SELECT p.nome, c.nome as categoria
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id;

Exemplo 2: Relacionamento M:N

-- Tabela de relacionamento M:N
CREATE TABLE itens_pedido (
    pedido_id INTEGER,
    produto_id INTEGER,
    quantidade INTEGER,
    FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
    FOREIGN KEY (produto_id) REFERENCES produtos(id)
);

-- Consultar pedidos com produtos
SELECT p.id, pr.nome, ip.quantidade
FROM pedidos p
JOIN itens_pedido ip ON p.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id;

🔗 3. Tipos de Relacionamentos

🔗 Relacionamento 1:1

Um para Um: Cada registro de uma tabela se relaciona com apenas um registro de outra tabela.

Exemplo:
Pessoa ←→ CPF
Cliente ←→ Endereço

🔗 Relacionamento 1:N

Um para Muitos: Um registro de uma tabela se relaciona com vários registros de outra tabela.

Exemplo:
Categoria ←→ Produtos
Cliente ←→ Pedidos

🔗 Relacionamento M:N

Muitos para Muitos: Vários registros de uma tabela se relacionam com vários registros de outra tabela.

Exemplo:
Pedidos ←→ Produtos
Alunos ←→ Disciplinas

🎯 Exemplos Práticos de Consultas:

Relacionamento 1:N - Categoria → Produtos

-- Listar produtos por categoria
SELECT c.nome as categoria, p.nome as produto, p.preco
FROM categorias c
JOIN produtos p ON c.id = p.categoria_id
ORDER BY c.nome, p.nome;

-- Contar produtos por categoria
SELECT c.nome, COUNT(p.id) as total_produtos
FROM categorias c
LEFT JOIN produtos p ON c.id = p.categoria_id
GROUP BY c.id, c.nome;

Relacionamento M:N - Pedidos ↔ Produtos

-- Listar pedidos com produtos
SELECT 
    cl.nome as cliente,
    p.data_pedido,
    pr.nome as produto,
    ip.quantidade,
    ip.subtotal
FROM clientes cl
JOIN pedidos p ON cl.id = p.cliente_id
JOIN itens_pedido ip ON p.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
ORDER BY p.data_pedido;

🛡️ 4. Integridade Referencial

O que é Integridade Referencial?

É a garantia de que os relacionamentos entre tabelas são consistentes. Impede a criação de registros órfãos e mantém a consistência dos dados.

📝 Regras de Integridade Referencial:

✅ Regras de Inserção:

Não pode inserir registro com chave estrangeira inexistente
Deve existir o registro referenciado na tabela pai
Valores de chave estrangeira devem ser válidos

❌ Regras de Exclusão:

Não pode excluir registro referenciado por outros
Deve excluir primeiro os registros filhos
Ou usar CASCADE para exclusão em cascata

🎯 Exemplos de Violações:

❌ Tentativa de Inserção Inválida:

-- ERRO: categoria_id não existe
INSERT INTO produtos (nome, preco, categoria_id) 
VALUES ('Produto Teste', 100.00, 999);

-- ERRO: fornecedor_id não existe
INSERT INTO produtos (nome, preco, fornecedor_id) 
VALUES ('Produto Teste', 100.00, 999);

❌ Tentativa de Exclusão Inválida:

-- ERRO: categoria tem produtos
DELETE FROM categorias WHERE id = 1;

-- ERRO: cliente tem pedidos
DELETE FROM clientes WHERE id = 1;

✅ Soluções para Violações:

Exclusão em Cascata:

-- Criar tabela com CASCADE
CREATE TABLE produtos (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100),
    categoria_id INTEGER,
    FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id) 
        ON DELETE CASCADE
);

Exclusão Segura:

-- 1. Verificar se há registros filhos
SELECT COUNT(*) FROM produtos WHERE categoria_id = 1;

-- 2. Se não houver, excluir categoria
DELETE FROM categorias WHERE id = 1;

🔍 5. Consultas com Relacionamentos

📝 Tipos de JOIN:

INNER JOIN (Junção Interna)

Retorna apenas registros que têm correspondência em ambas as tabelas.

-- Produtos com suas categorias
SELECT p.nome, c.nome as categoria
FROM produtos p
INNER JOIN categorias c ON p.categoria_id = c.id;

LEFT JOIN (Junção à Esquerda)

Retorna todos os registros da tabela à esquerda, mesmo sem correspondência.

-- Todas as categorias, mesmo sem produtos
SELECT c.nome, COUNT(p.id) as total_produtos
FROM categorias c
LEFT JOIN produtos p ON c.id = p.categoria_id
GROUP BY c.id, c.nome;

RIGHT JOIN (Junção à Direita)

Retorna todos os registros da tabela à direita, mesmo sem correspondência.

-- Todos os produtos, mesmo sem categoria
SELECT p.nome, c.nome as categoria
FROM produtos p
RIGHT JOIN categorias c ON p.categoria_id = c.id;

FULL OUTER JOIN

Retorna todos os registros de ambas as tabelas.

-- Todos os registros de ambas as tabelas
SELECT p.nome, c.nome as categoria
FROM produtos p
FULL OUTER JOIN categorias c ON p.categoria_id = c.id;

🎯 Consultas Complexas:

Relatório de Vendas por Cliente

-- Vendas detalhadas por cliente
SELECT 
    cl.nome as cliente,
    p.data_pedido,
    p.valor_total,
    COUNT(ip.id) as itens,
    GROUP_CONCAT(pr.nome) as produtos
FROM clientes cl
JOIN pedidos p ON cl.id = p.cliente_id
JOIN itens_pedido ip ON p.id = ip.pedido_id
JOIN produtos pr ON ip.produto_id = pr.id
GROUP BY cl.id, p.id
ORDER BY p.data_pedido DESC;

Análise de Produtos por Categoria

-- Estatísticas por categoria
SELECT 
    c.nome as categoria,
    COUNT(p.id) as total_produtos,
    AVG(p.preco) as preco_medio,
    SUM(p.estoque) as estoque_total
FROM categorias c
LEFT JOIN produtos p ON c.id = p.categoria_id
GROUP BY c.id, c.nome
ORDER BY total_produtos DESC;

💪 6. Exercícios Práticos

🎯 Exercício 1: Consultas Básicas

Liste todos os produtos com suas categorias
Mostre todos os clientes que fizeram pedidos
Conte quantos produtos cada categoria possui
Calcule o valor total de vendas por cliente

🎯 Exercício 2: Consultas Avançadas

Encontre produtos que nunca foram vendidos
Liste clientes que não fizeram pedidos
Mostre o produto mais vendido
Calcule a média de itens por pedido

Dicas para os Exercícios

Use LEFT JOIN para encontrar registros sem correspondência
Use GROUP BY para agrupar resultados
Use HAVING para filtrar grupos
Use subconsultas para consultas complexas

📋 7. Resumo e Próximos Passos

✅ O que você aprendeu:

Chaves Primárias: Identificam registros unicamente
Chaves Estrangeiras: Estabelecem relacionamentos
Tipos de Relacionamentos: 1:1, 1:N, M:N
Integridade Referencial: Mantém consistência
JOINs: Consultas entre tabelas relacionadas

🎯 Próximos passos:

Pratique consultas com múltiplas tabelas
Estude índices para performance
Aprenda sobre normalização
Explore ferramentas de modelagem
Prepare-se para o Projeto Final!

🎉 Parabéns! Você agora domina os relacionamentos em bancos de dados. Esses conceitos são fundamentais para criar sistemas robustos e eficientes!

🧠 Quiz Progressivo

Teste seus conhecimentos sobre relacionamentos e chaves com nosso quiz interativo!

Iniciar Quiz