🔑 CAPÍTULO 07: CHAVES, RELACIONAMENTOS E CARDINALIDADE

🎯 Objetivos de Aprendizagem

Ao final deste capítulo (estimativa: 2 horas de estudo autoguiado), você será capaz de:

• 🔹 Dominar os conceitos de Chave Candidata, Chave Primária (PK), Chave Estrangeira (FK), Chave Composta e Chave Substituta (Surrogate Key).
• 🔹 Compreender o princípio da Integridade Referencial e os comportamentos de ação em cascata (ON DELETE CASCADE, ON DELETE RESTRICT, ON DELETE SET NULL).
• 🔹 Modelar integridade referencial rigorosa com SQLAlchemy 2.0 utilizando ForeignKey() e relationship().
• 🔹 Prevenir exclusões acidentais de registros pai com dependentes no banco de dados.

A grande força de um banco de dados Relacional está no seu próprio nome: Relacionamentos. Neste capítulo, vamos entender como tabelas isoladas se conectam para formar um ecossistema inteligente, capaz de responder perguntas de negócios complexas. 🛡️🧩

🏢 O Cenário Prático (Seu Desafio)

Na TecProExpress, o RH solicitou um sistema para controlar quais motoristas estão utilizando quais caminhões. Além disso, eles precisam de um sistema de entregas, onde um cliente pode ter vários pacotes. Os estagiários de TI desenharam tabelas, mas não conseguiram "conectá-las".

"Seu desafio é ser o Engenheiro de Integração: usar Chaves Estrangeiras para garantir que nenhum pacote seja registrado sem um cliente responsável, dominando as cardinalidades 1:N e N:M."

🧠 Fundamentos: O Fio Condutor (FK)

A Chave Estrangeira (FK) é o mecanismo de vínculo. Ela é literalmente o valor da Chave Primária (PK) de uma tabela, copiado para dentro de outra tabela.

📊 Diagrama de Cardinalidade (Crow's Foot)

Existem 3 tipos básicos de relacionamentos, representados pela notação "Pé de Galinha":

erDiagram
    CLIENTE ||--o{ PACOTE : "possui (1:N)"
    MOTORISTA |o--o| CAMINHAO : "dirige (1:1)"
    ENTREGADOR }|--|{ ROTA : "atua (N:M)"

🔍 Detalhamento Visual:

• ||: Indica que a participação é obrigatória (ex: Mínimo 1).
• o{: Indica "Zero ou Muitos". Um cliente pode acabar de se cadastrar e ainda não ter pacotes (zero), ou pode ter dezenas (muitos).

📐 O Guia de Ouro da Cardinalidade

Onde eu coloco a Chave Estrangeira? Esta é a pergunta que mais derruba candidatos em entrevistas.

📖 Exemplo Guiado: O Relacionamento 1:N (Pai e Filho)

A TecProExpress quer vincular um cliente aos seus pacotes. Veja como aplicar isso no SQL garantindo a ordem correta (DDL -> DML).

🛠️ Código do Exemplo

-- PASSO 1: DDL (Criar a tabela PAI primeiro)
CREATE TABLE cliente_exp (
    id INT PRIMARY KEY,
    nome VARCHAR(100)
);

-- PASSO 2: DDL (Criar a tabela FILHO com a Chave Estrangeira)
CREATE TABLE pacote (
    codigo INT PRIMARY KEY,
    descricao VARCHAR(100),
    id_cliente INT, -- A coluna que receberá o link
    CONSTRAINT fk_cliente_pacote FOREIGN KEY (id_cliente) REFERENCES cliente_exp(id)
);

-- PASSO 3: DML (Inserir Pai, depois Filho)
INSERT INTO cliente_exp VALUES (10, 'Maria Silva');
INSERT INTO pacote VALUES (5001, 'Notebook', 10);

🔍 Detalhamento do Código:

• A Regra da Ordem: Você não pode nascer sem ter pais. No SGBD, você não pode inserir um pacote apontando para o cliente 10 se o cliente 10 ainda não foi cadastrado (INSERT).
• FOREIGN KEY: Avisa o SGBD para vigiar esta coluna. Se alguém tentar deletar a Maria Silva, o banco bloqueará, avisando que existem pacotes vinculados a ela.

🛠️ Prática Obrigatória: Resolvendo o Caos do N:M

Cenário: O sistema de Entregadores e Rotas da TecProExpress. Um Entregador pode atuar em Várias Rotas. Uma Rota pode ser feita por Vários Entregadores.

1. Crie as tabelas independentes entregador e rota.
2. Crie a tabela associativa escala_trabalho contendo a chave estrangeira de ambos.
3. Insira dados provando a vinculação.

🚀 Script de Seed (Gabarito de Associação)

-- DDL PAI 1
CREATE TABLE entregador ( id INT PRIMARY KEY, nome VARCHAR(50) );
-- DDL PAI 2
CREATE TABLE rota ( id INT PRIMARY KEY, regiao VARCHAR(50) );

-- DDL FILHO ASSOCIATIVO (A ponte entre os dois)
CREATE TABLE escala_trabalho (
    id_entregador INT,
    id_rota INT,
    PRIMARY KEY (id_entregador, id_rota), -- Chave Composta
    FOREIGN KEY (id_entregador) REFERENCES entregador(id),
    FOREIGN KEY (id_rota) REFERENCES rota(id)
);

-- DML (Carregando a base)
INSERT INTO entregador VALUES (1, 'Carlos'), (2, 'Ana');
INSERT INTO rota VALUES (100, 'Centro'), (200, 'Litoral');

-- DML (O Relacionamento N:M na prática)
INSERT INTO escala_trabalho VALUES (1, 100); -- Carlos atende o Centro
INSERT INTO escala_trabalho VALUES (1, 200); -- Carlos atende o Litoral
INSERT INTO escala_trabalho VALUES (2, 100); -- Ana também atende o Centro

💻 Ponte Prática: Do SQL Manual ao SQLAlchemy 2.0 ORM

Como o SQLAlchemy 2.0 implementa Chaves Estrangeiras (FK) e Relacionamentos 1:N e N:M de forma fluida no código?

🔴 1. A Abordagem Manual (IDs Órfãos e Falta de Navegabilidade)

No SQL manual puro, para listar os pacotes de um cliente, você precisa escrever queries JOIN manuais. Se alguém excluir o cliente sem verificar os pacotes, registros órfãos poluem o banco:

# ❌ ABORDAGEM COM SQL MANUAL: Gestão manual de FK e perigo de órfãos
import sqlite3

conn = sqlite3.connect("entregas_legadas.db")
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;") # No SQLite é preciso ligar explicitamente!
# Risco de inserir pacote com cliente inexistente se a FK não for configurada:
cursor.execute("INSERT INTO pacote VALUES (999, 'Celular', 99999);") # ⚠️ ID de cliente inexistente!

🟢 2. A Abordagem com SQLAlchemy 2.0 (Relacionamentos Bidirecionais relationship())

Com o SQLAlchemy 2.0, definimos ForeignKey("tabela.id") e a propriedade relationship(). O Python gerencia o grafo de objetos em memória e emite as chaves estrangeiras automaticamente:

# ✅ ABORDAGEM MODERNA COM SQLALCHEMY 2.0: Relacionamento 1:N Pai-Filho
from typing import Any
from sqlalchemy import create_engine, String, Integer, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

class ClienteExpModel(Base):
    """Lado 1 do relacionamento (Pai)."""
    __tablename__ = "clientes_exp"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(100), nullable=False)

    # Relacionamento 1:N (Um cliente possui muitos pacotes)
    pacotes: Mapped[list["PacoteModel"]] = relationship(
        "PacoteModel", back_populates="cliente", cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"Cliente(id={self.id}, nome='{self.nome}')"

class PacoteModel(Base):
    """Lado N do relacionamento (Filho)."""
    __tablename__ = "pacotes_exp"

    codigo: Mapped[int] = mapped_column(Integer, primary_key=True)
    descricao: Mapped[str] = mapped_column(String(100), nullable=False)
    id_cliente: Mapped[int] = mapped_column(ForeignKey("clientes_exp.id"), nullable=False)

    # Vínculo inverso com o Pai
    cliente: Mapped["ClienteExpModel"] = relationship("ClienteExpModel", back_populates="pacotes")

    def __repr__(self) -> str:
        return f"Pacote(codigo={self.codigo}, desc='{self.descricao}', cliente_id={self.id_cliente})"

🛠️ Mini-Projeto 07 (BD): Gestor de Entregas e Rastreamento 1:N

Objetivo: Construir e popular um modelo relacional 1:N, navegando pelas entidades conectadas a partir do objeto Python.

📋 Pré-requisitos e Instalação

No terminal do seu ambiente virtual (PowerShell ou Bash), instale a biblioteca necessária:

pip install sqlalchemy

💻 Código Completo e Autocontido (miniprojeto_07_relacionamentos.py)

Crie o arquivo miniprojeto_07_relacionamentos.py e insira o código abaixo integralmente:

"""
Mini-Projeto 07: Gestor de Entregas e Rastreamento 1:N
Curso: GTI - Banco de Dados Relacionais e Engenharia de Software
Stack: Python 3.11+ | SQLAlchemy 2.0 | SQLite
"""
import os
from sqlalchemy import create_engine, String, Integer, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

# 1. Definição Declarativa do Schema Relacional 1:N
class Base(DeclarativeBase):
    pass

class ClienteExpModel(Base):
    """Lado 1 do relacionamento (Pai)."""
    __tablename__ = "clientes_exp"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(100), nullable=False)

    # Relacionamento 1:N (Um cliente possui muitos pacotes)
    pacotes: Mapped[list["PacoteModel"]] = relationship(
        "PacoteModel", back_populates="cliente", cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"Cliente(id={self.id}, nome='{self.nome}')"

class PacoteModel(Base):
    """Lado N do relacionamento (Filho)."""
    __tablename__ = "pacotes_exp"

    codigo: Mapped[int] = mapped_column(Integer, primary_key=True)
    descricao: Mapped[str] = mapped_column(String(100), nullable=False)
    id_cliente: Mapped[int] = mapped_column(ForeignKey("clientes_exp.id"), nullable=False)

    # Vínculo inverso com o Pai
    cliente: Mapped["ClienteExpModel"] = relationship("ClienteExpModel", back_populates="pacotes")

    def __repr__(self) -> str:
        return f"Pacote(codigo={self.codigo}, desc='{self.descricao}', cliente_id={self.id_cliente})"

# 2. Ponto de Entrada Executável
if __name__ == "__main__":
    DB_FILE = "tecpro_entregas.db"

    # Reset preventivo para garantir idempotência em testes repetidos
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    engine = create_engine(f"sqlite:///{DB_FILE}", echo=False)
    Base.metadata.create_all(bind=engine)

    print("🔗 GESTOR DE RELACIONAMENTOS 1:N (TECPROEXPRESS)")
    print("=" * 65)

    # 1. Inserindo o Pai e seus Filhos diretamente pela coleção Python
    with Session(engine) as session:
        cliente_ana = ClienteExpModel(id=101, nome="Ana Cristina Santos")

        # Associando objetos filhos diretamente na lista:
        p1 = PacoteModel(codigo=5001, descricao="Monitor Gamer 27'", id_cliente=101)
        p2 = PacoteModel(codigo=5002, descricao="Teclado Mecânico RGB", id_cliente=101)
        p3 = PacoteModel(codigo=5003, descricao="Mouse Ergonômico", id_cliente=101)
        cliente_ana.pacotes.extend([p1, p2, p3])

        session.merge(cliente_ana)
        session.commit()
        print(f"✅ Cliente {cliente_ana.nome} e {len(cliente_ana.pacotes)} pacotes persistidos!")

    # 2. Consultando o Cliente e navegando nos seus pacotes automaticamente
    print("\n🔍 Consultando entregas vinculadas por integridade referencial:")
    with Session(engine) as session:
        cliente = session.get(ClienteExpModel, 101)
        if cliente:
            print(f"👤 Destinatário: {cliente.nome}")
            print("📦 Pacotes em rota de entrega:")
            for pacote in cliente.pacotes:
                print(f"   • Código #{pacote.codigo}: {pacote.descricao}")
    print("=" * 65)

🚀 Como Executar

Execute o script no terminal:

python miniprojeto_07_relacionamentos.py

🖥️ Saída Esperada no Console

🔗 GESTOR DE RELACIONAMENTOS 1:N (TECPROEXPRESS)
=================================================================
✅ Cliente Ana Cristina Santos e 3 pacotes persistidos!

🔍 Consultando entregas vinculadas por integridade referencial:
👤 Destinatário: Ana Cristina Santos
📦 Pacotes em rota de entrega:
   • Código #5001: Monitor Gamer 27'
   • Código #5002: Teclado Mecânico RGB
   • Código #5003: Mouse Ergonômico
=================================================================

💡 Checkpoint de Lógica