Aula 02 - Regras normalização de tabelas

Unidade 2 — Modelagem de Dados Relacional ◀ ▶ 1 / 0 ⛶ Tela cheia U2 ◆ Banco de Dados · Unidade 2 Modelagem de Dados Relacional Entidades, atributos, chaves, relacionamentos, cardinalidade, MER, normalização e conversão para SQL. 7 tópicos ~3–4 horas Iniciante–Intermediário Pré-req: Unidade 1 01 01 · Entidades e Atributos O que é uma Entidade? 📦 Definição Qualquer coisa do mundo real sobre a qual precisamos guardar informações — pessoa, objeto, evento ou conceito. 🎓 Aluno Precisamos registrar dados sobre cada aluno 📚 Livro Armazenamos título, autor, ISBN, preço... 🛒 Pedido Cada compra gera um registro no banco Aluno id_alunoPK nome email data_nascimento matricula 02 01 · Entidades e Atributos Tipos de Atributos Simples (Atômico) Valor único, indivisível. Não pode ser decomposto em partes menores. cpf · preço · matrícula Composto Pode ser dividido em partes menores com significado próprio. endereco → rua + cidade + CEP Multivalorado Pode ter vários valores para a mesma entidade. telefone → fixo, celular, work Derivado Calculado a partir de outro. Não precisa ser armazenado. idade → calculado de data_nasc Chave (Key) Identifica unicamente cada ocorrência. Nunca se repete. id · cpf · email_único Opcional (NULL) Pode não ter valor. O banco aceita ausência de dado. telefone · apelido · foto 03 02 · Chaves Chave Primária — Primary Key 🔑 Primary Key (PK) Identifica unicamente cada linha de uma tabela. Não se repete. Não pode ser NULL. Toda tabela deve ter uma. ✅Única por linha — jamais repete ✅Nunca pode ser NULL ✅Imutável — não muda com o tempo ✅Pode ser simples ou composta aluno 🔑 id (PK) nome email 1 Ana Lima ana@mail.com 2 Carlos Melo carlos@mail.com 3 Maria Silva maria@mail.com pk.sqlSQL📋 CREATE TABLE aluno ( id INT AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(100) NOT NULL, email VARCHAR(120) UNIQUE ); 04 02 · Chaves Chave Estrangeira — Foreign Key 🔗 Foreign Key (FK) Coluna que armazena o valor da PK de outra tabela. Cria o vínculo entre tabelas e garante integridade referencial. curso idPK nome FK → aluno idPK nome id_cursoFK curso id (PK) nome 1 Informática 2 Engenharia aluno id nome id_curso (FK) 1 Ana 1 2 Carlos 2 3 Maria 1 fk.sqlSQL📋 CREATE TABLE curso ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(80) ); CREATE TABLE aluno ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100), id_curso INT, -- FK FOREIGN KEY (id_curso) REFERENCES curso(id) ); ⚠️ Integridade Referencial O banco impede inserir um aluno com id_curso = 99 se esse curso não existir. Sem registros órfãos. 05 04 · Cardinalidade Cardinalidade — Tipos 1 : 1 Um para Um Cada registro de A se associa a no máximo um de B, e vice-versa. Pessoa ←→ Passaporte Funcionário ←→ Crachá FK com UNIQUE 1 : N Um para Muitos Um de A se associa a vários de B. Cada B pertence a apenas um A. Departamento → Funcionários Curso → Alunos FK no lado N N : N Muitos para Muitos Vários de A se associam a vários de B. Aluno ↔ Disciplina Pedido ↔ Produto Tabela intermediária 06 04 · Cardinalidade Implementando o N:N em SQL 🗂️ Tabela Intermediária O N:N gera uma nova tabela com as PKs das duas entidades como FKs. A PK desta tabela é composta. aluno idPK nome N matricula 🔗 id_alunoPK+FK id_discPK+FK nota data N disciplina idPK nome nn.sqlSQL📋 CREATE TABLE aluno ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100) ); CREATE TABLE disciplina ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100) ); -- Tabela intermediária do N:N CREATE TABLE matricula ( id_aluno INT, id_disc INT, nota DECIMAL(4,2), data DATE, PRIMARY KEY (id_aluno, id_disc), FOREIGN KEY (id_aluno) REFERENCES aluno(id), FOREIGN KEY (id_disc) REFERENCES disciplina(id) ); 07 05 · MER Modelo ER — Símbolos e Níveis Notação ENTIDADE Retângulo Representa uma entidade (tabela) atributo Elipse Representa um atributo relação Losango Representa um relacionamento id Elipse sublinhada Atributo-chave (PK) 3 Níveis de Modelagem 1 Conceitual — MER Entidades, atributos e relacionamentos. Independente de tecnologia. Linguagem do cliente. 2 Lógico — Modelo Relacional Tabelas, colunas, PKs e FKs. Ainda independente do banco de dados. 3 Físico — DDL SQL Código SQL real executado no MySQL, PostgreSQL, SQL Server etc. 08 05 · MER MER — Sistema de Biblioteca Leitor id_leitor nome email realiza 1 N Empréstimo id_emprest. data_empr. data_retorno refere-se N 1 Livro id_livro titulo Entidade Atributo Relacionamento Atributo-chave (PK) 09 06 · Normalização O problema: tabela desnormalizada pedido_tudo — ⚠ Tabela problemática id cliente_nome cliente_email produto qtd 1 Ana Silva ana@mail.com Notebook 1 2 Ana Silva ana@mail.com Mouse 2 3 João Lima joao@mail.com Mesa 1 ⚠️ Anomalias Atualização: Ana muda o e-mail → precisa editar 2 linhas. Inserção: Não posso cadastrar cliente sem pedido. Exclusão: Deletar pedido de João apaga os dados do João. 1FN Atômico — sem listas 2FN Sem dep. parcial da PK 3FN Sem dep. transitiva 10 06 · Normalização 1FN — Primeira Forma Normal 📏 Regra da 1FN Cada célula deve conter um único valor atômico. Sem listas, sem grupos repetitivos, sem "telefone1, telefone2, telefone3". ✗ Viola a 1FN -- Múltiplos valores em uma célula id | cliente | produtos ----+---------+--------------------- 1 | Ana | Notebook, Mouse, Teclado 2 | João | Caderno, Caneta -- Impossível filtrar por produto ✓ Conforme a 1FN -- Um valor por célula id | cliente | produto ----+---------+----------- 1 | Ana | Notebook 1 | Ana | Mouse 1 | Ana | Teclado 2 | João | Caderno 2 | João | Caneta 11 06 · Normalização 2FN — Segunda Forma Normal 🔍 Regra da 2FN 1FN + todos os atributos não-chave dependem da PK inteira — não de parte dela. Aplica-se quando há PK composta. ✗ Viola a 2FN -- PK: id_pedido + id_prod -- "nome_prod" depende SÓ de id_prod id_pedido | id_prod | qtd | nome_prod ----------+---------+-----+----------- 1 | 10 | 2 | Notebook 2 | 10 | 1 | Notebook ← repetido! ✓ Conforme a 2FN -- Separar em duas tabelas -- item_pedido id_pedido | id_prod | qtd 1 | 10 | 2 2 | 10 | 1 -- produto id | nome_prod 10 | Notebook 12 06 · Normalização 3FN — Terceira Forma Normal 🔗 Regra da 3FN 2FN + nenhum atributo não-chave depende de outro não-chave. Eliminar dependências transitivas: A → B → C, onde B não é a PK. ✗ Viola a 3FN -- id → id_dept → nome_dept -- "nome_dept" depende de id_dept, -- que não é a PK! id | nome | id_dept | nome_dept ---+--------+---------+---------- 1 | Ana | 10 | TI 2 | Carlos | 10 | TI ← repetido! 3 | Maria | 20 | RH ✓ Conforme a 3FN -- Extrair departamento -- funcionario id | nome | id_dept (FK) 1 | Ana | 10 2 | Carlos | 10 3 | Maria | 20 -- departamento id | nome 10 | TI 20 | RH 13 07 · Conversão Convertendo MER em Tabelas SQL 1 Entidade → Tabela Atributo-chave vira PK. Demais atributos viram colunas. 2 Rel. 1:N → FK no lado N PK do lado "1" vira FK na tabela do lado "N". 3 Rel. N:N → Tabela intermediária Nova tabela com duas FKs e PK composta. Atributos do relacionamento também ficam aqui. 4 Rel. 1:1 → FK com UNIQUE FK na tabela mais fraca, com UNIQUE para garantir cardinalidade 1:1. 5 Atrib. multivalorado → Nova tabela Tabela separada com FK para a entidade de origem. escola.sqlSQL📋 -- Turma (1) → Aluno (N) -- Aluno (N) ↔ Disciplina (N) CREATE TABLE turma ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(80) ); CREATE TABLE aluno ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100), id_turma INT, FOREIGN KEY (id_turma) REFERENCES turma(id) ); CREATE TABLE disciplina ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100) ); CREATE TABLE matricula ( id_aluno INT, id_disc INT, nota DECIMAL(4,2), PRIMARY KEY (id_aluno, id_disc), FOREIGN KEY(id_aluno) REFERENCES aluno(id), FOREIGN KEY(id_disc) REFERENCES disciplina(id) ); 14 08 · Exercícios Exercícios Práticos 🎯 Instrução Resolva cada exercício antes de abrir a solução. Clique para expandir e ver a resposta. 01 Qual a cardinalidade? "Um médico atende vários pacientes. Um paciente pode ser atendido por vários médicos." FÁCIL ▼ 💡 Dos dois lados há "vários" → cardinalidade N:N → precisa de tabela intermediária. Cardinalidade: N:N — tabela intermediária consulta. Médico↔ consulta↔ Paciente Solução📋 CREATE TABLE consulta ( id_medico INT, id_paciente INT, data_hora DATETIME, PRIMARY KEY(id_medico,id_paciente,data_hora), FOREIGN KEY(id_medico) REFERENCES medico(id), FOREIGN KEY(id_paciente) REFERENCES paciente(id) ); 💡 Ver dica 02 Qual FN está sendo violada? "id_pedido + id_prod formam a PK, mas nome_produto depende só de id_prod." FÁCIL ▼ 💡 Dependência de PARTE da PK composta → violação da 2FN. Violação da 2FN. "nome_produto" depende apenas de id_prod, não da PK composta completa. Solução: mover para uma tabela produto separada. 💡 Ver dica ★ Desafio: Modele em SQL — Hospital com Médicos, Pacientes, Internações e Quartos. DESAFIO ▼ 💡 Internação é a entidade central que conecta Médico (1:N), Paciente (1:N) e Quarto (1:N). Não há N:N aqui. hospital.sql📋 CREATE TABLE medico ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100), crm VARCHAR(20) UNIQUE ); CREATE TABLE paciente ( id INT PRIMARY KEY AUTO_INCREMENT, nome VARCHAR(100), cpf VARCHAR(14) UNIQUE ); CREATE TABLE quarto ( id INT PRIMARY KEY AUTO_INCREMENT, numero VARCHAR(10) UNIQUE, tipo ENUM('simples','uti') ); CREATE TABLE internacao ( id INT PRIMARY KEY AUTO_INCREMENT, id_medico INT NOT NULL, id_paciente INT NOT NULL, id_quarto INT NOT NULL, data_entrada DATETIME, diagnostico TEXT, FOREIGN KEY (id_medico) REFERENCES medico(id), FOREIGN KEY (id_paciente) REFERENCES paciente(id), FOREIGN KEY (id_quarto) REFERENCES quarto(id) ); 💡 Ver dica 15 FIM · Resumo Resumo da Unidade 2 01 Entidade = objeto do mundo real. Atributo = característica (simples, composto, multivalorado, derivado, chave). 02 PK identifica cada linha unicamente. FK cria vínculo entre tabelas e garante integridade referencial. 03 Relacionamentos descritos por verbos. 1:1 FK+UNIQUE · 1:N FK no lado N · N:N tabela intermediária. 04 MER = modelo conceitual. Retângulo = entidade · Elipse = atributo · Losango = relacionamento. 05 3 níveis: Conceitual (MER) → Lógico (Relacional) → Físico (SQL). 06 1FN atômico · 2FN sem dep. parcial da PK · 3FN sem dep. transitiva. 07 Conversão MER→SQL: entidade = tabela · 1:N = FK · N:N = tabela intermediária · multivalorado = nova tabela. Próxima aula Unidade 3 — SQL: SELECT avançado e JOINs