Modelagem de Banco de Dados 25.2

Objetivo do Projeto

O presente projeto tem como objetivo modelar e implementar um banco de dados relacional para um sistema de e-commerce simplificado. O sistema contempla funcionalidades básicas como cadastro de clientes e produtos, gerenciamento de estoque, criação e fechamento de pedidos, processamento de pagamentos, geração de remessas e auditoria de pedidos.

Clique aqui para testar o SQL Online.

1.1 Requisitos Funcionais

Código	Nome	Descrição
RF-01	Cadastro de cliente	Permitir criar/editar/consultar clientes com nome, e-mail único, CPF/CNPJ opcional, telefone e dados de cadastro.
RF-02	Gerenciar endereços	Associar múltiplos endereços a um cliente (endereço para cobrança e para entrega), com CEP, rua, número, cidade e estado.
RF-03	Catálogo de produtos	Exibir produtos com código, nome, descrição curta, preço unitário, unidade de medida e indicador de ativo/inativo.
RF-04	Consulta de estoque	Mostrar disponibilidade atual por produto (quantidade em estoque) e indicar “esgotado” quando quantidade ≤ 0.
RF-05	Carrinho	Criar um pedido em estado carrinho onde o cliente adiciona itens (produto, quantidade), com cálculo parcial de subtotal.
RF-06	Checkout	Processar o fechamento do pedido: selecionar endereço de entrega, método de frete, forma de pagamento, cálculo de impostos/frete e gerar pedido em estado fechado.
RF-07	Pagamento e faturamento	Registrar tentativas de pagamento, marcar pagamento como aprovado ou rejeitado; ao pagamento aprovado, gerar fatura/registro fiscal e marcar pedido como faturado.
RF-08	Expedição	Criar remessa/roteiro de envio com itens do pedido faturado, atribuir transportadora/serviço de frete e registrar código de rastreamento; pedido passa para estado enviado quando remessa for despachada.
RF-09	Status do pedido	Registrar historicamente cada mudança de status do pedido (quem, quando, motivo) na entidade AuditoriaPedido.

1.2 Requisitos Não Funcionais

Código	Nome	Descrição
RNF-01	Segurança básica	Sistema exige autenticação para ações de cliente e controle de privilégios administrativos.
RNF-02	Performance básica	Listagem de produtos e consulta de estoque devem responder em ≤ 500 ms para cargas normais.
RNF-03	Disponibilidade e tolerância	Banco e API devem suportar operação contínua no horário comercial, com backups regulares.
RNF-04	Backups e recuperação	Backups automáticos diários com retenção mínima configurável e restauração documentada.
RNF-05	Consistência de estoque	Atualização de estoque deve ser transacional e operações críticas usar locks/transactions apropriados.

1.3 Regras de Negócio

Código	Nome	Descrição
RN-01	Unicidade do e-mail do cliente	O e-mail do cliente é único no sistema.
RN-02	Estados válidos de pedido	Fluxo: CARRINHO → FECHADO → PAGO/FATURADO → REMESSA_GERADA → ENVIADO → ENTREGUE/CANCELADO.
RN-03	Política de estoque	Não pode faturar sem estoque reservado suficiente; cancelamento libera reserva.
RN-04	Pagamento	Pedido só fica confirmado quando pagamento = aprovado.
RN-05	Remessa	Só é criada após faturamento.
RN-06	Auditoria obrigatória	Qualquer mudança de status deve gerar registro em auditoria_pedido com usuário responsável.

2. Modelagem Entidade-Relacionamento no BRModelo

3. Transformação do Modelo E-R em Modelo Relacional

CLIENTE

PK: id_cliente

FK: —

Atributos: nome, email, senha_hash, telefone, cpf, data_cadastro

Relacionamentos: 1:N ENDERECO, 1:N PEDIDO

Regras: email deve ser único; cpf deve ser único.

ENDERECO

PK: id_endereco

FK: id_cliente → CLIENTE(id_cliente)

Atributos: logradouro, numero, complemento, bairro, cidade, uf, cep, tipo (entrega | cobranca), principal (boolean)

Relacionamentos: N:1 CLIENTE, 1:N PEDIDO

Regras: um cliente pode ter vários endereços; cada pedido possui um endereço de entrega e um de cobrança.

PRODUTO

PK: id_produto

FK: —

Atributos: sku, nome, descricao, preco_unitario, peso, categoria, ativo

Relacionamentos: 1:1 ESTOQUE, N:N PEDIDO

Regras: sku é único; ativo indica se o produto está disponível para venda.

ESTOQUE

PK: id_estoque

FK: id_produto → PRODUTO(id_produto)

Atributos: quantidade_atual, quantidade_reservada, data_ultima_atualizacao

Relacionamentos: 1:1 PRODUTO

Regras: cada produto possui apenas um registro de estoque.

PEDIDO

PK: id_pedido

FK: id_cliente → CLIENTE(id_cliente); id_endereco_entrega → ENDERECO(id_endereco); id_endereco_cobranca → ENDERECO(id_endereco)

Atributos: data_criacao, valor_subtotal, valor_frete, valor_total, status (carrinho, confirmado, faturado, em_transporte, entregue, cancelado)

Relacionamentos: N:1 CLIENTE, 1:N ITEM_PEDIDO, 1:1 PAGAMENTO, 1:1 REMESSA, 1:N AUDITORIA_PEDIDO

Regras: cada pedido pertence a um cliente; deve ter pelo menos um item; pagamento e remessa são opcionais até suas respectivas fases.

ITEM_PEDIDO

PK: (id_pedido, id_produto)

FK: id_pedido → PEDIDO(id_pedido); id_produto → PRODUTO(id_produto)

Atributos: quantidade, preco_unitario, desconto_item

Relacionamentos: N:1 PEDIDO, N:1 PRODUTO

Regras: um mesmo produto não pode aparecer duas vezes no mesmo pedido; quantidade deve ser maior que zero.

PAGAMENTO

PK: id_pagamento

FK: id_pedido → PEDIDO(id_pedido)

Atributos: metodo (cartao, pix, boleto), valor_pago, data_pagamento, status (pendente, aprovado, negado, estornado), cod_transacao

Relacionamentos: 1:1 PEDIDO

Regras: um pedido só pode ser faturado após o pagamento ser aprovado.

REMESSA

PK: id_remessa

FK: id_pedido → PEDIDO(id_pedido)

Atributos: transportadora, codigo_rastreio, data_envio, data_entrega_prevista, status (preparando, postado, em_rota, entregue, falha_entrega)

Relacionamentos: 1:1 PEDIDO

Regras: cada pedido gera uma única remessa após ser faturado; codigo_rastreio deve ser único.

AUDITORIA_PEDIDO

PK: id_auditoria

FK: id_pedido → PEDIDO(id_pedido)

Atributos: campo_alterado, valor_anterior, valor_novo, usuario_responsavel, data_alteracao

Relacionamentos: N:1 PEDIDO

Regras: todo evento relevante de alteração em pedido, pagamento ou remessa deve ser registrado.

4.1 Criação do esquema DDL


-- 1. CLIENTE

CREATE TABLE cliente (
    id_cliente           INT GENERATED BY DEFAULT AS IDENTITY,
    nome                 VARCHAR(100) NOT NULL,
    email                VARCHAR(100) NOT NULL,
    senha_hash           VARCHAR(200) NOT NULL,
    telefone             VARCHAR(20),
    cpf                  VARCHAR(14) UNIQUE,
    data_cadastro        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_cliente PRIMARY KEY (id_cliente),
    CONSTRAINT uq_cliente_email UNIQUE (email)
);

-- 2. ENDERECO

CREATE TABLE endereco (
    id_endereco          INT GENERATED BY DEFAULT AS IDENTITY,
    id_cliente           INT NOT NULL,
    logradouro           VARCHAR(150) NOT NULL,
    numero               VARCHAR(10) NOT NULL,
    complemento          VARCHAR(50),
    bairro               VARCHAR(80),
    cidade               VARCHAR(80),
    uf                   CHAR(2) NOT NULL,
    cep                  CHAR(9) NOT NULL,
    tipo                 VARCHAR(20) CHECK (tipo IN ('entrega','cobranca')),
    principal            BOOLEAN DEFAULT FALSE,
    CONSTRAINT pk_endereco PRIMARY KEY (id_endereco),
    CONSTRAINT fk_endereco_cliente FOREIGN KEY (id_cliente)
        REFERENCES cliente (id_cliente)
        ON DELETE CASCADE
);

-- 3. PRODUTO

CREATE TABLE produto (
    id_produto           INT GENERATED BY DEFAULT AS IDENTITY,
    sku                  VARCHAR(30) NOT NULL,
    nome                 VARCHAR(120) NOT NULL,
    descricao            TEXT,
    preco_unitario       NUMERIC(10,2) NOT NULL CHECK (preco_unitario >= 0),
    peso                 NUMERIC(8,3),
    categoria            VARCHAR(60),
    ativo                BOOLEAN DEFAULT TRUE,
    CONSTRAINT pk_produto PRIMARY KEY (id_produto),
    CONSTRAINT uq_produto_sku UNIQUE (sku)
);

-- 4. ESTOQUE

CREATE TABLE estoque (
    id_estoque           INT GENERATED BY DEFAULT AS IDENTITY,
    id_produto           INT NOT NULL UNIQUE,
    quantidade_atual     INT NOT NULL DEFAULT 0 CHECK (quantidade_atual >= 0),
    quantidade_reservada INT NOT NULL DEFAULT 0 CHECK (quantidade_reservada >= 0),
    data_ultima_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_estoque PRIMARY KEY (id_estoque),
    CONSTRAINT fk_estoque_produto FOREIGN KEY (id_produto)
        REFERENCES produto (id_produto)
        ON DELETE CASCADE
);

-- 5. PEDIDO

CREATE TABLE pedido (
    id_pedido            INT GENERATED BY DEFAULT AS IDENTITY,
    id_cliente           INT NOT NULL,
    id_endereco_entrega  INT,
    id_endereco_cobranca INT,
    data_criacao         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valor_subtotal       NUMERIC(10,2) DEFAULT 0,
    valor_frete          NUMERIC(10,2) DEFAULT 0,
    valor_total          NUMERIC(10,2) DEFAULT 0,
    status               VARCHAR(20) DEFAULT 'carrinho'
        CHECK (status IN ('carrinho','confirmado','faturado','em_transporte','entregue','cancelado')),
    CONSTRAINT pk_pedido PRIMARY KEY (id_pedido),
    CONSTRAINT fk_pedido_cliente FOREIGN KEY (id_cliente)
        REFERENCES cliente (id_cliente),
    CONSTRAINT fk_pedido_endereco_entrega FOREIGN KEY (id_endereco_entrega)
        REFERENCES endereco (id_endereco),
    CONSTRAINT fk_pedido_endereco_cobranca FOREIGN KEY (id_endereco_cobranca)
        REFERENCES endereco (id_endereco)
);

-- 6. ITEM_PEDIDO

CREATE TABLE item_pedido (
    id_pedido            INT NOT NULL,
    id_produto           INT NOT NULL,
    quantidade           INT NOT NULL CHECK (quantidade > 0),
    preco_unitario       NUMERIC(10,2) NOT NULL CHECK (preco_unitario >= 0),
    desconto_item        NUMERIC(10,2) DEFAULT 0 CHECK (desconto_item >= 0),
    CONSTRAINT pk_item_pedido PRIMARY KEY (id_pedido, id_produto),
    CONSTRAINT fk_item_pedido_pedido FOREIGN KEY (id_pedido)
        REFERENCES pedido (id_pedido)
        ON DELETE CASCADE,
    CONSTRAINT fk_item_pedido_produto FOREIGN KEY (id_produto)
        REFERENCES produto (id_produto)
);

-- 7. PAGAMENTO

CREATE TABLE pagamento (
    id_pagamento         INT GENERATED BY DEFAULT AS IDENTITY,
    id_pedido            INT NOT NULL UNIQUE,
    metodo               VARCHAR(20) NOT NULL CHECK (metodo IN ('cartao','pix','boleto')),
    valor_pago           NUMERIC(10,2) NOT NULL CHECK (valor_pago >= 0),
    data_pagamento       TIMESTAMP,
    status               VARCHAR(20) DEFAULT 'pendente'
        CHECK (status IN ('pendente','aprovado','negado','estornado')),
    cod_transacao        VARCHAR(50),
    CONSTRAINT pk_pagamento PRIMARY KEY (id_pagamento),
    CONSTRAINT fk_pagamento_pedido FOREIGN KEY (id_pedido)
        REFERENCES pedido (id_pedido)
        ON DELETE CASCADE
);

-- 8. REMESSA

CREATE TABLE remessa (
    id_remessa           INT GENERATED BY DEFAULT AS IDENTITY,
    id_pedido            INT NOT NULL UNIQUE,
    transportadora       VARCHAR(80) NOT NULL,
    codigo_rastreio      VARCHAR(50) UNIQUE,
    data_envio           TIMESTAMP,
    data_entrega_prevista DATE,
    status               VARCHAR(20) DEFAULT 'preparando'
        CHECK (status IN ('preparando','postado','em_rota','entregue','falha_entrega')),
    CONSTRAINT pk_remessa PRIMARY KEY (id_remessa),
    CONSTRAINT fk_remessa_pedido FOREIGN KEY (id_pedido)
        REFERENCES pedido (id_pedido)
        ON DELETE CASCADE
);

-- 9. AUDITORIA_PEDIDO

CREATE TABLE auditoria_pedido (
    id_auditoria         INT GENERATED BY DEFAULT AS IDENTITY,
    id_pedido            INT NOT NULL,
    campo_alterado       VARCHAR(100),
    valor_anterior       VARCHAR(200),
    valor_novo           VARCHAR(200),
    usuario_responsavel  VARCHAR(100),
    data_alteracao       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_auditoria PRIMARY KEY (id_auditoria),
    CONSTRAINT fk_auditoria_pedido FOREIGN KEY (id_pedido)
        REFERENCES pedido (id_pedido)
        ON DELETE CASCADE
);

4.2 Criação dos índices essenciais


-- Índices essenciais

CREATE INDEX idx_produto_nome ON produto (nome);
CREATE INDEX idx_pedido_status ON pedido (status);
CREATE INDEX idx_pagamento_status ON pagamento (status);
CREATE INDEX idx_remessa_status ON remessa (status);
CREATE INDEX idx_cliente_email ON cliente (email);

5. Inserir os dados nas tabelas (DML)


-- Adiciona 3 clientes 

INSERT INTO cliente (nome, email, senha_hash, telefone, cpf)
VALUES

('Thiago', 'thiago.bezerra@email.com', 'hash123', '11999990001', '123.456.789-00'),
('Pedro', 'pedro.lima@email.com', 'hash456', '21988880002', '987.654.321-00'),
('Lucas', 'lucas.tertoliano@email.com', 'hash789', '31977770003', '555.666.777-00');

-- Adiciona um endereço de entrega e um de cobrança para cada cliente

INSERT INTO endereco (id_cliente, logradouro, numero, bairro, cidade, uf, cep, tipo, principal)
VALUES

(1, 'Rua das Flores', '100', 'Centro', 'São Paulo', 'SP', '01000-000', 'entrega', TRUE),
(1, 'Rua das Flores', '100', 'Centro', 'São Paulo', 'SP', '01000-000', 'cobranca', TRUE),
(2, 'Av. Brasil', '500', 'Copacabana', 'Rio de Janeiro', 'RJ', '22000-000', 'entrega', TRUE),
(2, 'Av. Brasil', '500', 'Copacabana', 'Rio de Janeiro', 'RJ', '22000-000', 'cobranca', TRUE),
(3, 'Rua Minas Gerais', '250', 'Savassi', 'Belo Horizonte', 'MG', '30100-000', 'entrega', TRUE),
(3, 'Rua Minas Gerais', '250', 'Savassi', 'Belo Horizonte', 'MG', '30100-000', 'cobranca', TRUE);

-- Adiciona 4 produtos

INSERT INTO produto (sku, nome, descricao, preco_unitario, peso, categoria)
VALUES

('P001', 'Camiseta Básica', 'Camiseta de algodão unissex', 59.90, 0.3, 'Vestuário'),
('P002', 'Tênis Running', 'Tênis leve para corrida', 249.90, 1.2, 'Calçados'),
('P003', 'Boné Casual', 'Boné ajustável com logo bordado', 79.90, 0.2, 'Acessórios'),
('P004', 'Mochila Esportiva', 'Mochila 25L resistente à água', 199.90, 0.9, 'Acessórios');

-- Adiciona estoque aos 4 produtos

INSERT INTO estoque (id_produto, quantidade_atual, quantidade_reservada)
VALUES

(1, 10, 0),
(2, 15, 0),
(3, 20, 0),
(4, 25, 0);

-- Cria 3 pedidos

INSERT INTO pedido (id_cliente, id_endereco_entrega, id_endereco_cobranca, valor_subtotal, valor_frete, valor_total, status)
VALUES

(1, 1, 2, 309.80, 25.00, 334.80, 'faturado'),
(2, 3, 4, 249.90, 20.00, 269.90, 'confirmado'),
(3, 5, 6, 59.90, 15.00, 74.90, 'em_transporte');

-- Preenche 4 pedidos com itens da Loja

INSERT INTO item_pedido (id_pedido, id_produto, quantidade, preco_unitario, desconto_item)
VALUES

(1, 1, 2, 59.90, 0),
(1, 3, 1, 79.90, 0),
(2, 2, 1, 249.90, 0),
(3, 1, 1, 59.90, 0);

-- Simula 3 pagamentos

INSERT INTO pagamento (id_pedido, metodo, valor_pago, data_pagamento, status, cod_transacao)
VALUES

(1, 'cartao', 334.80, '2025-10-01 14:00:00', 'aprovado', 'TX001'),
(2, 'pix', 269.90, '2025-10-02 09:30:00', 'pendente', 'TX002'),
(3, 'boleto', 74.90, '2025-10-03 11:45:00', 'aprovado', 'TX003');

-- Simula 2 entregas que tiveram o pagamento aprovado

INSERT INTO remessa (id_pedido, transportadora, codigo_rastreio, data_envio, data_entrega_prevista, status)
VALUES

(1, 'Correios', 'BR123456SP', '2025-10-02 08:00:00', '2025-10-05', 'em_rota'),
(3, 'Transportadora X', 'TR654321MG', '2025-10-03 10:00:00', '2025-10-07', 'em_rota');

-- Cria auditoria de 4 pedidos

INSERT INTO auditoria_pedido (id_pedido, campo_alterado, valor_anterior, valor_novo, usuario_responsavel)
VALUES

(1, 'status', 'confirmado', 'faturado', 'admin'),
(1, 'status', 'faturado', 'em_transporte', 'sistema'),
(2, 'status', 'carrinho', 'confirmado', 'cliente'),
(3, 'status', 'faturado', 'em_transporte', 'sistema');

6.1 Criação de uma VIEW para simplificar consultas complexas e ocultar dados sensíveis


CREATE VIEW vw_pedidos_resumidos AS
SELECT
    p.id_pedido,
    c.nome AS cliente,
    c.email,
    p.data_criacao,
    p.status AS status_pedido,
    p.valor_total,
    pg.metodo AS metodo_pagamento,
    pg.status AS status_pagamento,
    r.transportadora,
    r.codigo_rastreio,
    r.status AS status_remessa
FROM pedido p
JOIN cliente c ON p.id_cliente = c.id_cliente
LEFT JOIN pagamento pg ON p.id_pedido = pg.id_pedido
LEFT JOIN remessa r ON p.id_pedido = r.id_pedido;

6.2 Três exemplos de SELECT para testar a VIEW


-- Listar pedidos com situação geral consolidada
SELECT 
    id_pedido,
    cliente,
    status_pedido,
    metodo_pagamento,
    status_pagamento,
    transportadora,
    status_remessa,
    valor_total
FROM vw_pedidos_resumidos
ORDER BY id_pedido;


-- Listar pedidos em transporte
SELECT * FROM vw_pedidos_resumidos
WHERE status_pedido = 'em_transporte';


-- Listar pedidos com pagamento pendente
SELECT * FROM vw_pedidos_resumidos
WHERE status_pagamento = 'pendente';