➗ CAPÍTULO 09: MAPEAMENTO MER ➔ RELACIONAL E ÁLGEBRA

🎯 Objetivos de Aprendizagem

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

• 🔹 Aplicar as 7 regras formais de transformação do Modelo Conceitual (MER) para o Modelo Lógico Relacional.
• 🔹 Mapear relacionamentos N:N gerando automaticamente a Tabela Associativa intermediária com chave composta.
• 🔹 Dominar os operadores da Álgebra Relacional: Projeção ($\pi$), Seleção ($\sigma$), Junção ($\bowtie$), Produto Cartesiano ($\times$) e União ($\cup$).
• 🔹 Escrever consultas complexas conectando álgebra formal à sintaxe SQL e SQLAlchemy 2.0.

Chegamos à fase final do design de banco de dados. Você já tem um Diagrama Entidade-Relacionamento (DER) validado com o cliente. Agora, você precisa "traduzir" esse desenho para a estrutura física (Tabelas, Chaves) que o SGBD entende. Além disso, vamos espiar o motor matemático que faz o SQL ser tão rápido: a Álgebra Relacional. 🛡️🧩

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

Os diretores da TecProExpress aprovaram no quadro branco o diagrama de como a empresa funciona: "O Cliente faz um Pedido, que é entregue por um Motorista usando um Veículo".

"Seu desafio é pegar esse desenho (MER) e transformá-lo em tabelas reais usando regras estritas de mapeamento para que a equipe de desenvolvimento possa finalmente conectar a aplicação ao SGBD."

🧠 Fundamentos: O Roteiro de Mapeamento (MER -> Lógico)

A tradução de um diagrama para tabelas segue um roteiro matemático. Você não pode adivinhar o resultado; deve aplicar a regra:

📖 Exemplo Guiado: Mapeamento da Rota de Entregas (DDL -> DML)

A TecProExpress tem Entregadores e Telefones. Como o entregador pode ter vários telefones (Multivalorado), não podemos colocar 3 colunas de telefone na mesma tabela. A regra diz: "Crie uma nova tabela".

🛠️ Código do Exemplo

-- PASSO 1: DDL (A Tabela Principal - Entidade)
CREATE TABLE entregador_tecpro (
    id INT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
);

-- PASSO 2: DDL (A Nova Tabela gerada pelo Atributo Multivalorado)
CREATE TABLE telefone_entregador (
    id_entregador INT,
    numero VARCHAR(15),
    PRIMARY KEY (id_entregador, numero), -- Chave Composta!
    FOREIGN KEY (id_entregador) REFERENCES entregador_tecpro(id) ON DELETE CASCADE
);

-- PASSO 3: DML (Carga dos Dados)
INSERT INTO entregador_tecpro VALUES (1, 'Marcos Silva');
INSERT INTO telefone_entregador VALUES (1, '11-9999-8888');
INSERT INTO telefone_entregador VALUES (1, '11-7777-6666');

🔍 Detalhamento do Código:

• A tabela telefone_entregador só tem sentido de existir por causa do entregador. Por isso ela tem ON DELETE CASCADE.
• A junção do ID com o Número forma a Chave Primária, garantindo que não cadastraremos o mesmo número duas vezes para a mesma pessoa.

🧮 Álgebra Relacional (O Motor Oculto)

Você nunca escreverá "Álgebra Relacional" no terminal do seu trabalho. No entanto, o motor do MySQL/PostgreSQL lê o seu SQL, o converte em álgebra relacional nos bastidores, e otimiza a matemática para responder rápido.

🧮 Mapa Visual dos Operadores da Álgebra Relacional

flowchart TD
    subgraph Unarios["Operadores Unários (1 Relação)"]
        SIGMA["σ Seleção (Filtro Horizontal)<br>SQL: WHERE preco > 100"]
        PI["π Projeção (Filtro Vertical)<br>SQL: SELECT nome, email"]
    end

    subgraph Binarios["Operadores Binarios (2 Relações)"]
        JOIN["⋈ Junção / Theta Join<br>SQL: INNER JOIN ... ON"]
        UNION["∪ União (R1 ∪ R2)<br>SQL: UNION"]
        DIFF["- Diferença (R1 - R2)<br>SQL: EXCEPT / NOT IN"]
        INTER["∩ Interseção (R1 ∩ R2)<br>SQL: INTERSECT"]
        CART["× Produto Cartesiano<br>SQL: CROSS JOIN"]
    end

📊 Fluxo da Junção (⋈)

flowchart LR
    T1["Tabela: CLIENTE<br/>ID=10, Nome=Maria"] --> J{"⋈<br/>Onde as chaves batem"}
    T2["Tabela: PACOTE<br/>ID_Cliente=10, Item=Laptop"] --> J
    J --> RESULT["Resultado Unificado:<br/>Maria comprou um Laptop"]

🛠️ Prática Obrigatória: O Encontro do SQL com a Álgebra

Cenário: O diretor pediu a lista de pacotes e seus donos na TecProExpress.

1. Crie a query SQL que representa a Junção (⋈) entre a tabela Cliente e a tabela Pacote.

🚀 Script de Seed (Gabarito da Junção)

-- DDL de Setup Rápido (Se você não os tiver da unidade passada)
-- CREATE TABLE cliente (id INT PRIMARY KEY, nome VARCHAR(50));
-- CREATE TABLE pacote (id INT PRIMARY KEY, fk_cliente INT);

-- DML (A Consulta SQL que representa a Álgebra: Cliente ⋈ Pacote)
SELECT cliente.nome, pacote.id 
FROM cliente 
JOIN pacote ON cliente.id = pacote.fk_cliente;

🔍 Detalhamento da Consulta:

• O comando JOIN ... ON é a tradução exata do símbolo ⋈ (Junção Natural). Ele varre as duas tabelas e só retorna os dados que estão perfeitamente linkados.

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

Como os operadores matemáticos da Álgebra Relacional ($\sigma, \pi, \bowtie, \cup, \cap$) se traduzem na API moderna do SQLAlchemy 2.0?

🔴 1. A Abordagem Manual (Álgebra Feita com Loops Python Ineficientes)

No modelo procedural sem motor relacional, fazer uma junção ou interseção exige percorrer listas aninhadas em complexidade $O(N \times M)$:

# ❌ ABORDAGEM PROCEDURAL: Loops aninhados para simular a Junção (⋈)
clientes = [{"id": 1, "nome": "Maria"}, {"id": 2, "nome": "Carlos"}]
pacotes = [{"cod": 501, "cliente_id": 1, "item": "Notebook"}]

# Junção manual ineficiente:
resultado_join = []
for c in clientes:
    for p in pacotes:
        if c["id"] == p["cliente_id"]:
            resultado_join.append({"nome": c["nome"], "item": p["item"]})

🟢 2. A Abordagem com SQLAlchemy 2.0 (Expressões Algébricas Declarativas)

Com o SQLAlchemy 2.0, usamos os métodos que espelham exatamente os operadores algébricos:

• $\sigma$ (Seleção / Filtro Horizontal): .where(...)
• $\pi$ (Projeção / Filtro Vertical): select(Modelo.campo1, Modelo.campo2)
• $\bowtie$ (Junção Relacional): select(...).join(...)
• $\cup$ (União de Relações): union(stmt1, stmt2)

# ✅ ABORDAGEM MODERNA: Mapeamento Direto dos Operadores da Álgebra
from sqlalchemy import create_engine, String, Integer, ForeignKey, select, union
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class EntregadorModel(Base):
    __tablename__ = "entregadores_algebristas"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(50))
    cidade: Mapped[str] = mapped_column(String(50))

class RotaModel(Base):
    __tablename__ = "rotas_algebristas"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    cidade_origem: Mapped[str] = mapped_column(String(50))

🛠️ Mini-Projeto 09 (BD): Motor de Consultas e Álgebra Relacional

Objetivo: Executar Projeção ($\pi$), Seleção ($\sigma$), Junção ($\bowtie$) e União ($\cup$) via SQLAlchemy 2.0.

📋 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_09_algebra.py)

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

"""
Mini-Projeto 09: Motor de Consultas e Álgebra Relacional
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, select, String, Integer, union
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

# 1. Definição Declarativa dos Schemas
class Base(DeclarativeBase):
    pass

class EntregadorModel(Base):
    __tablename__ = "entregadores_algebra"

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

class RotaModel(Base):
    __tablename__ = "rotas_algebristas"

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

# 2. Ponto de Entrada Executável
if __name__ == "__main__":
    DB_FILE = "tecpro_algebra.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)

    # Povoando as tabelas para testes algébricos
    with Session(engine) as session:
        e1 = EntregadorModel(id=1, nome="Marcos Silva", cidade="São Paulo")
        e2 = EntregadorModel(id=2, nome="Beatriz Souza", cidade="Campinas")
        e3 = EntregadorModel(id=3, nome="Carlos Eduardo", cidade="Santos")
        session.merge(e1)
        session.merge(e2)
        session.merge(e3)

        r1 = RotaModel(id=101, cidade_origem="São Paulo")
        r2 = RotaModel(id=102, cidade_origem="Curitiba")
        session.merge(r1)
        session.merge(r2)
        session.commit()

    print("🧮 SIMULADOR DA ÁLGEBRA RELACIONAL (SQLALCHEMY 2.0):")
    print("=" * 65)

    with Session(engine) as session:
        # Operação 1: Projeção (π) e Seleção (σ) -> π_nome (σ_cidade='São Paulo' (Entregadores))
        stmt_proj_sel = select(EntregadorModel.nome).where(EntregadorModel.cidade == "São Paulo")
        nomes_sp = session.scalars(stmt_proj_sel).all()
        print(f"1. π e σ (Entregadores de SP): {nomes_sp}")

        # Operação 2: União (∪) de Cidades de Entregadores e Rotas
        stmt_cidades_ent = select(EntregadorModel.cidade)
        stmt_cidades_rot = select(RotaModel.cidade_origem)
        stmt_uniao = union(stmt_cidades_ent, stmt_cidades_rot)

        cidades_totais = session.scalars(stmt_uniao).all()
        print(f"2. União ∪ (Todas as Cidades no Ecossistema): {cidades_totais}")

    print("=" * 65)

🚀 Como Executar

Execute o script no terminal:

python miniprojeto_09_algebra.py

🖥️ Saída Esperada no Console

🧮 SIMULADOR DA ÁLGEBRA RELACIONAL (SQLALCHEMY 2.0):
=================================================================
1. π e σ (Entregadores de SP): ['Marcos Silva']
2. União ∪ (Todas as Cidades no Ecossistema): ['Campinas', 'Curitiba', 'Santos', 'São Paulo']
=================================================================

💡 Checkpoint de Lógica