📚 Pré-requisitos Teóricos: este projeto aplica conceitos ensinados em Módulo 08: Bancos de Dados SQL e NoSQL. Recomendado revisar antes de começar.

🗄️ Modelagem Relacional 3FN & Automação Transacional em PostgreSQL

v1.0 — Normalização, Triggers, Constraints e Integridade Transacional

Trilha de Engenharia de Dados & Bancos de Dados — Projeto 1 de 4

• ➡️ v1 (este): Modelagem Relacional 3FN · Constraints · Triggers PL/pgSQL · PostgreSQL 16
• v2: Modelagem NoSQL · Aggregation Pipelines · MongoDB 7.0
• v3: Estratégias de Cache · Cache-Aside · Invalidação · Redis 7.2
• v4: DBA Enterprise · EXPLAIN ANALYZE · Particionamento · PgBouncer · RLS

🎓 Nível Profissional Simulado: Desenvolvedor Júnior / Engenheiro de Dados Pleno. Na vida real, 80% dos bugs em produção ocorrem por inconsistência no banco (valores negativos, CPFs duplicados, estoque divergente). Um engenheiro sênior projeta o banco para ser auto-consistente, garantindo que nenhuma aplicação externa consiga corromper o estado dos dados.

🎯 Objetivo

Construir do zero o esquema relacional de um Marketplace Corporativo aplicando rigorosamente a Terceira Forma Normal (3FN), chaves estrangeiras com restrições atômicas, colunas geradas e uma trigger procedural em PL/pgSQL para atualização automática de estoque e recálculo do valor total dos pedidos em eventos de inserção, atualização e exclusão de itens.

🏗️ Diagrama de Entidade-Relacionamento (ERD)

erDiagram
    CATEGORIAS ||--o{ PRODUTOS : "categoriza (1:N)"
    CLIENTES ||--o{ PEDIDOS : "realiza (1:N)"
    PEDIDOS ||--|{ ITENS_PEDIDO : "contém (1:N)"
    PRODUTOS ||--o{ ITENS_PEDIDO : "composto_por (1:N)"

    CLIENTES {
        int id PK
        string nome
        string cpf UK "Formato 000.000.000-00"
        string email UK
        string telefone
        timestamp data_cadastro
    }

    CATEGORIAS {
        int id PK
        string nome UK
        string slug UK
        boolean ativa
    }

    PRODUTOS {
        int id PK
        int categoria_id FK
        string nome
        string sku UK
        decimal preco "CHECK > 0"
        int estoque_atual "CHECK >= 0"
        int estoque_minimo
        boolean ativo
        timestamp criado_em
    }

    PEDIDOS {
        int id PK
        int cliente_id FK
        timestamp data_pedido
        string status "CHECK PENDENTE|PAGO|ENVIADO|CANCELADO"
        decimal valor_total "Atualizado via Trigger"
    }

    ITENS_PEDIDO {
        int pedido_id PK,FK
        int produto_id PK,FK
        int quantidade "CHECK > 0"
        decimal preco_unitario
        decimal subtotal "GENERATED ALWAYS AS"
    }

🧑‍💼 Fase 1 — Levantamento de Requisitos

O Briefing do Cliente (Diretoria de Operações)

“Estamos com sérios problemas no nosso e-commerce atual. O vendedor consegue cadastrar produto com preço zero, o estoque frequentemente fica negativo quando entram dois pedidos juntos, e o valor total do pedido às vezes não bate com a soma dos itens porque um desenvolvedor júnior esqueceu de calcular o subtotal no backend. Precisamos de um banco de dados profissional onde essas regras sejam garantidas pelo próprio SGBD!”

Requisitos Funcionais (RF) e Não-Funcionais (RNF)

📋 Fase 2 — Backlog & User Stories

🌿 Fase 3 — Engenharia em Equipe (Git Flow & Migrations)

# Branch da funcionalidade
git checkout -b feature/US03-triggers-estoque

# Subir o ambiente PostgreSQL isolado
docker-compose up -d

# Validar se o schema foi carregado com sucesso
docker exec -it db_sql_postgres psql -U admin -d marketplace_db -c "\dt"

🛠️ Fase 4 — Implementação Passo a Passo do Schema SQL

1. Criação das Tabelas Normalizadas (sql/01_schema.sql)

CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    categoria_id INT NOT NULL,
    nome VARCHAR(150) NOT NULL,
    sku VARCHAR(30) NOT NULL UNIQUE,
    preco NUMERIC(10, 2) NOT NULL,
    estoque_atual INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_produto_categoria FOREIGN KEY (categoria_id) 
        REFERENCES categorias(id) ON DELETE RESTRICT,
    CONSTRAINT chk_preco_positivo CHECK (preco > 0),
    CONSTRAINT chk_estoque_nao_negativo CHECK (estoque_atual >= 0)
);

2. Trigger de Automação Transacional em PL/pgSQL

CREATE OR REPLACE FUNCTION fn_atualizar_estoque_e_total()
RETURNS TRIGGER AS $$
DECLARE
    v_pedido_id INT;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        v_pedido_id := OLD.pedido_id;
        UPDATE produtos SET estoque_atual = estoque_atual + OLD.quantidade WHERE id = OLD.produto_id;
    ELSIF (TG_OP = 'UPDATE') THEN
        v_pedido_id := NEW.pedido_id;
        UPDATE produtos SET estoque_atual = estoque_atual + OLD.quantidade - NEW.quantidade WHERE id = NEW.produto_id;
    ELSIF (TG_OP = 'INSERT') THEN
        v_pedido_id := NEW.pedido_id;
        UPDATE produtos SET estoque_atual = estoque_atual - NEW.quantidade WHERE id = NEW.produto_id;
    END IF;

    UPDATE pedidos
    SET valor_total = (SELECT COALESCE(SUM(subtotal), 0) FROM itens_pedido WHERE pedido_id = v_pedido_id)
    WHERE id = v_pedido_id;

    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_atualizar_item_pedido
AFTER INSERT OR UPDATE OR DELETE ON itens_pedido
FOR EACH ROW
EXECUTE FUNCTION fn_atualizar_estoque_e_total();

🧭 Decisões Técnicas (ADRs)

• ADR 001: Adoção do PostgreSQL 16 com normalização 3FN e Triggers atômicas para blindar o banco contra inconsistências de software.

🚀 Como Executar no Laboratório

1. Abra o terminal na pasta deste projeto

No seu editor/IDE, abra a pasta deste projeto (File > Open Folder) ou navegue via terminal:

cd db_sql_01_modelagem_relacional

2. Execute a aplicação ou testes

docker-compose up -d
# Executar a suíte de testes automatizados:
python -m unittest tests/test_modelagem_relacional.py

[!TIP] Dica para execução a partir da raiz do repositório: Se você abriu o repositório completo no VS Code, basta navegar até a pasta antes de executar: cd proj_aplicacoes_full_stack/projetos/db_sql_01_modelagem_relacional

🧪 Testes de Validação & Asserções

-- 1. Teste de Violação de Preço Negativo (Deve FALHAR com Check Constraint)
INSERT INTO produtos (categoria_id, nome, sku, preco) VALUES (1, 'Teste Invalido', 'SKU-001', -10.00);

-- 2. Teste da Trigger: Inserir Pedido e Item
INSERT INTO pedidos (cliente_id) VALUES (1);
INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario) 
VALUES (1, 1, 2, 1450.00);

-- 3. Validar se o total do pedido foi para R$ 2900.00 e o estoque baixou 2 unidades
SELECT valor_total FROM pedidos WHERE id = 1;
SELECT estoque_atual FROM produtos WHERE id = 1;

✅ Checkpoint Final

1. Todas as tabelas estão em conformidade com 3FN (sem dependências parciais ou transitivas).
2. As constraints CHECK e UNIQUE impedem corrupção de dados na raiz.
3. A Trigger recalcula o total e sincroniza o estoque em INSERT, UPDATE e DELETE de forma 100% ACID.
4. Suíte de testes automatizados com 100% de aprovação.
5. Docker Compose pronto para subida em qualquer ambiente.