database_schema.sql

-- ========================================
-- Sistema de Gerenciamento Bancário - Database Schema
-- Aplicação das 3 Formas Normais (1NF, 2NF, 3NF)
-- ========================================

-- Criar banco de dados
CREATE DATABASE IF NOT EXISTS sistema_bancario;
USE sistema_bancario;

-- ========================================
-- 1. Tabela de Cidades
-- ========================================
CREATE TABLE city (
    id_city INT PRIMARY KEY AUTO_INCREMENT,
    name_city VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_city_name (name_city)
);

-- ========================================
-- 2. Tabela de Clientes
-- ========================================
CREATE TABLE client (
    id_client INT PRIMARY KEY AUTO_INCREMENT,
    name_client VARCHAR(255) NOT NULL,
    CPF INT NOT NULL UNIQUE,
    id_city_client INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_client_city 
        FOREIGN KEY (id_city_client) 
        REFERENCES city(id_city)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
        
    INDEX idx_client_cpf (CPF),
    INDEX idx_client_city (id_city_client)
);

-- ========================================
-- 3. Tabela de Telefones
-- ========================================
CREATE TABLE phone (
    id_phone VARCHAR(45) PRIMARY KEY,
    num_phone INT NOT NULL,
    client_id_client INT NOT NULL,
    client_id_client1 INT,
    phone_type ENUM('RESIDENCIAL', 'COMERCIAL', 'CELULAR') DEFAULT 'CELULAR',
    
    CONSTRAINT fk_phone_client 
        FOREIGN KEY (client_id_client) 
        REFERENCES client(id_client)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        
    CONSTRAINT fk_phone_client1 
        FOREIGN KEY (client_id_client1) 
        REFERENCES client(id_client)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        
    INDEX idx_phone_client (client_id_client),
    INDEX idx_phone_number (num_phone)
);

-- ========================================
-- 4. Tabela de Bancos
-- ========================================
CREATE TABLE bank (
    id_bank INT PRIMARY KEY AUTO_INCREMENT,
    bank_name VARCHAR(100) NOT NULL,
    bank_code CHAR(3),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_bank_name (bank_name),
    UNIQUE KEY uk_bank_code (bank_code)
);

-- ========================================
-- 5. Tabela de Agências Bancárias
-- ========================================
CREATE TABLE bank_agency (
    id_bank_agency INT PRIMARY KEY AUTO_INCREMENT,
    name_agency VARCHAR(100) NOT NULL,
    agency_code VARCHAR(10),
    id_bank INT NOT NULL,
    address VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_agency_bank 
        FOREIGN KEY (id_bank) 
        REFERENCES bank(id_bank)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        
    UNIQUE KEY uk_agency_code_bank (agency_code, id_bank),
    INDEX idx_agency_bank (id_bank)
);

-- ========================================
-- 6. Tabela de Contas Bancárias
-- ========================================
CREATE TABLE account_bank (
    account_id_bank_agency INT PRIMARY KEY AUTO_INCREMENT,
    id_account INT NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0.00,
    account_type ENUM('CORRENTE', 'POUPANCA', 'SALARIO') DEFAULT 'CORRENTE',
    client_id_client INT NOT NULL,
    bank_agency_id_bank_agency INT NOT NULL,
    client_id_client1 INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_account_client 
        FOREIGN KEY (client_id_client) 
        REFERENCES client(id_client)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
        
    CONSTRAINT fk_account_agency 
        FOREIGN KEY (bank_agency_id_bank_agency) 
        REFERENCES bank_agency(id_bank_agency)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
        
    CONSTRAINT fk_account_client1 
        FOREIGN KEY (client_id_client1) 
        REFERENCES client(id_client)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
        
    CONSTRAINT chk_balance CHECK (balance >= 0),
    
    UNIQUE KEY uk_account_agency (id_account, bank_agency_id_bank_agency),
    INDEX idx_account_client (client_id_client),
    INDEX idx_account_agency (bank_agency_id_bank_agency)
);

-- ========================================
-- VIEWS ÚTEIS
-- ========================================

-- View: Relatório Completo de Clientes
CREATE VIEW vw_relatorio_clientes AS
SELECT 
    c.id_client AS codigo_cliente,
    c.name_client AS nome_cliente,
    c.CPF AS cpf,
    ct.name_city AS cidade,
    GROUP_CONCAT(DISTINCT p.num_phone ORDER BY p.num_phone SEPARATOR ', ') AS telefones,
    b.bank_name AS banco,
    ba.name_agency AS agencia,
    ab.id_account AS numero_conta,
    ab.account_type AS tipo_conta,
    ab.balance AS saldo
FROM client c
LEFT JOIN city ct ON c.id_city_client = ct.id_city
LEFT JOIN phone p ON c.id_client = p.client_id_client
LEFT JOIN account_bank ab ON c.id_client = ab.client_id_client
LEFT JOIN bank_agency ba ON ab.bank_agency_id_bank_agency = ba.id_bank_agency
LEFT JOIN bank b ON ba.id_bank = b.id_bank
GROUP BY c.id_client, ab.account_id_bank_agency
ORDER BY c.name_client;

-- View: Resumo por Banco
CREATE VIEW vw_resumo_banco AS
SELECT 
    b.bank_name AS banco,
    COUNT(DISTINCT ba.id_bank_agency) AS total_agencias,
    COUNT(DISTINCT ab.account_id_bank_agency) AS total_contas,
    COUNT(DISTINCT c.id_client) AS total_clientes,
    SUM(ab.balance) AS saldo_total
FROM bank b
LEFT JOIN bank_agency ba ON b.id_bank = ba.id_bank
LEFT JOIN account_bank ab ON ba.id_bank_agency = ab.bank_agency_id_bank_agency
LEFT JOIN client c ON ab.client_id_client = c.id_client
GROUP BY b.id_bank, b.bank_name
ORDER BY saldo_total DESC;

-- ========================================
-- COMENTÁRIOS SOBRE RELACIONAMENTOS
-- ========================================

/*
RELACIONAMENTOS IDENTIFICADORES (Linhas Sólidas no Workbench):
- bank -> bank_agency: Agência depende do banco para existir
- bank_agency -> account_bank: Conta depende da agência para existir

RELACIONAMENTOS NÃO-IDENTIFICADORES (Linhas Tracejadas no Workbench):  
- city -> client: Cliente referencia cidade mas tem identidade própria
- client -> phone: Telefone referencia cliente mas tem identidade própria
- client -> account_bank: Conta referencia cliente mas tem identidade própria

APLICAÇÃO DAS FORMAS NORMAIS:
1NF: Eliminação de campos multivalorados (telefones em tabela separada)
2NF: Eliminação de dependências parciais (separação de entidades)
3NF: Eliminação de dependências transitivas (cidades separadas de clientes)
*/