UC00602 · Modelar bases de dados relacionais

UC00602

Modelar bases de dados relacionais

Modelo relacional, diagramas entidade-relação, chaves, integridade e normalização

Curso profissional · 25h · Desenvolvimento de Software

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Plano

  1. Dados, SGBD e arquitetura
  2. O modelo relacional, chaves e integridade
  3. Modelo entidade-relação e notações
  4. Do modelo ER às tabelas e ao modelo físico
  5. Fronteiras, comportamento e implementação do sistema
  6. Normalização: dependências e anomalias
  7. 1FN, 2FN e 3FN passo a passo
  8. FNBC, formas avançadas e desnormalização
  9. Integridade, RGPD e caso prático
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 1 · Dados, SGBD e arquitetura

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Dado, informação e base de dados

  • Dado: facto isolado, sem contexto (42, Lisboa).
  • Informação: dado com contexto e significado.
  • Base de dados: conjunto organizado de dados relacionados, persistente, partilhado e estruturado.
  • Modelo de dados: estrutura, restrições e operações permitidas.

Uma lista de contactos num bloco de notas não é uma base de dados: falta estrutura, controlo e uma linguagem de consulta.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Três níveis de modelação

Nível Pergunta Resultado
Conceptual Que informação interessa? Diagrama ER
Lógico Como se organiza em tabelas? Tabelas, PK, FK
Físico Como fica guardado? CREATE TABLE, índices

Modela-se antes de escrever SQL.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Ficheiros soltos contra SGBD

Aspeto Ficheiros SGBD
Redundância Dados repetidos Cada dado uma vez
Acesso simultâneo Estraga o ficheiro Gerido
Segurança Tudo ou nada Permissões finas
Integridade Nenhuma Restrições declaradas
Programas Conhecem o formato Pedem dados ao SGBD
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

O que faz um SGBD

  • Define dados (CREATE) e manipula-os (INSERT, UPDATE, DELETE, SELECT).
  • Garante a integridade e controla os acessos.
  • Gere a concorrência e a recuperação após falhas.
  • Guarda o catálogo (dicionário de dados).

Exemplos: MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, Oracle.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Transações e ACID

Letra Propriedade Se falhasse
A Atomicidade: tudo ou nada Débito feito, crédito por fazer
C Consistência Estado inválido
I Isolamento Duas vendas do último artigo
D Durabilidade Venda confirmada que desaparece 
START TRANSACTION;
UPDATE Conta SET saldo = saldo - 100 WHERE id = 1;
UPDATE Conta SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

As linguagens do SQL

Sigla Para quê Comandos
DDL Estrutura CREATE, ALTER, DROP
DML Alterar dados INSERT, UPDATE, DELETE
DQL Consultar SELECT
DCL Permissões GRANT, REVOKE
TCL Transações COMMIT, ROLLBACK
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Arquitetura de três níveis

Nível O que vê
Externo Vistas de cada utilizador
Conceptual Tabelas, colunas, chaves, restrições
Interno Ficheiros, páginas, índices

Independência lógica: mudar o conceptual sem reescrever aplicações.
Independência física: mudar o interno sem mexer no conceptual.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

O percurso de uma consulta

  1. O processador verifica sintaxe, tabelas e permissões.
  2. O otimizador escolhe o plano mais barato (por exemplo, usar um índice).
  3. O executor corre o plano; o gestor de armazenamento lê as páginas.
  4. O resultado volta à aplicação.
SELECT nome FROM Cliente WHERE id = 7;
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 2 · O modelo relacional, chaves e integridade

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

A estrutura relacional

Formal Base de dados
Relação Tabela
Tuplo Linha, registo
Atributo Coluna, campo
Domínio Tipo e valores válidos
Grau / cardinalidade N.º de colunas / n.º de linhas

Sem linhas duplicadas; a ordem de linhas e colunas não conta; cada célula tem um valor.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

O valor NULL

  • NULL é ausência de valor: não é zero, nem texto vazio.
  • Qualquer comparação com NULL dá desconhecido.
WHERE telefone = NULL     -- nunca devolve nada
WHERE telefone IS NULL    -- correto

Usar NOT NULL onde o dado é obrigatório.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Chaves

Chave Definição
Superchave Identifica cada linha
Candidata Superchave mínima
Primária (PK) A candidata escolhida; única e nunca nula
Alternativa Candidata não escolhida (UNIQUE)
Estrangeira (FK) Aponta para a chave de outra tabela
Composta Mais de uma coluna
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Chave natural ou artificial?

  • Natural: vem do mundo real (ISBN, matrícula, NIF). Pode mudar ou ter exceções.
  • Artificial: inventada pelo sistema (id sequencial). Estável, curta.

Prática corrente: PK artificial e as naturais como UNIQUE.

Má PK: nome, e-mail, NIF (mudam, repetem-se ou são dados pessoais).

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · chaves candidatas

Viatura(matricula, chassis, marca, modelo, cor)

  1. Colunas que sozinhas não se repetem: matricula, chassis.
  2. Ambas têm uma coluna só: são as duas candidatas.
  3. Escolhe-se id artificial como PK e UNIQUE nas duas.
  4. {marca, modelo} não serve: muitas viaturas partilham.
id INT PRIMARY KEY AUTO_INCREMENT,
matricula VARCHAR(10) NOT NULL UNIQUE,
chassis VARCHAR(17) NOT NULL UNIQUE
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Os quatro tipos de integridade

Tipo Regra Declaração
Entidade PK única e não nula PRIMARY KEY
Referencial FK existe no pai FOREIGN KEY
Domínio Valor no domínio tipo, CHECK, NOT NULL
Utilizador Regra de negócio CHECK, gatilhos

MySQL: CHECK só é aplicado desde a versão 8.0.16; a FK abreviada junto da coluna (REFERENCES inline) é ignorada, usa-se sempre FOREIGN KEY. SQLite: PRAGMA foreign_keys = ON;.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Ações referenciais

Ação Efeito sobre os filhos
CASCADE Apaga ou atualiza também
SET NULL FK passa a NULL
RESTRICT / NO ACTION Recusa se houver filhos
SET DEFAULT Valor por omissão (não suportado pelo InnoDB)

Itens da encomenda: CASCADE. Produto vendido: RESTRICT.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · prever o SGBD

Funcionario.depto_id referencia Departamento(id) com ON DELETE SET NULL.

Operação Resultado
INSERT funcionário no departamento 9 (inexistente) Recusado: integridade referencial
DELETE departamento 1 Funcionários ficam com depto_id = NULL
INSERT com id repetido Recusado: integridade de entidade
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Gestão de dados: as quatro operações

INSERT INTO Cliente (nome, email) VALUES ('Marta Lopes', 'marta@exemplo.pt');
SELECT nome, email FROM Cliente WHERE id = 1;
UPDATE Cliente SET email = 'marta.lopes@exemplo.pt' WHERE id = 1;
DELETE FROM Cliente WHERE id = 1;

Sem WHERE, o UPDATE e o DELETE afetam a tabela inteira. Corre primeiro o SELECT com a mesma condição.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 3 · Modelo entidade-relação e notações

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Entidades e atributos

  • Entidade: classe de coisas de que guardamos informação (substantivo no singular).
  • Ocorrência: caso concreto.
  • Entidade fraca: só se identifica com a chave de outra.
Atributo Exemplo Em tabelas
Composto morada Uma coluna por parte
Multivalor telefones Tabela própria
Derivado idade Não se guarda
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Relações, cardinalidade e participação

Tipo Exemplo Implementação
1:1 Pessoa e Cartão FK com UNIQUE
1:N Cliente e Encomendas FK no lado N
N:M Aluno e Curso Tabela de associação

Participação total (obrigatória, NOT NULL) ou parcial (opcional). Ler nos dois sentidos.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Atributos de relação e entidade associativa

  • A propriedade que só faz sentido com as duas entidades pertence à relação: a nota é da inscrição, a quantidade é do item da encomenda.
  • Uma N:M com atributos próprios passa a entidade associativa: Inscricao, ligada por duas relações 1:N.

Graus: binária, unária (recursiva) e ternária.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · do texto ao modelo

Clínica veterinária: donos, animais, consultas, veterinários.

  1. Sublinhar os substantivos: candidatos a entidade.
  2. Atributos de cada um; a idade do animal é derivada.
  3. Relações: Dono 1:N Animal, Animal 1:N Consulta, Veterinário 1:N Consulta.
  4. Verificar se há N:M escondida (animal com vários donos?).
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Notação de Chen

  • Retângulo: entidade. Losango: relação. Elipse: atributo.
  • Chave sublinhada; entidade fraca em retângulo duplo; multivalor em elipse dupla; derivado em elipse tracejada.
  • Cardinalidade (1, N, M) junto às linhas.

Didática, mas verbosa: com 20 entidades fica ilegível.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Notação de pé-de-galinha

Símbolo Significado Mermaid
Dois traços Exatamente um ||
Círculo e traço Zero ou um |o
Pé e traço Um ou mais }|
Pé e círculo Zero ou mais }o 
Cliente ||--o{ Encomenda : faz
Encomenda ||--|{ EncomendaItem : contem
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Ferramentas e comparação de notações

Chen Pé-de-galinha UML
Compacidade Baixa Alta Média
Uso Ensino Projetos reais Equipas OO

Ferramentas: dbdiagram.io, drawSQL, MySQL Workbench, DBeaver, diagrams.net, Mermaid.

Table Encomenda {
  id int [pk, increment]
  cliente_id int [ref: > Cliente.id]
}
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 4 · Do modelo ER às tabelas e ao modelo físico

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Regras de conversão ER para relacional

Elemento Passa a ser
Entidade Tabela com PK
Composto Uma coluna por parte
Multivalor Tabela com FK
Derivado Não se guarda
1:N FK no lado N
1:1 FK com UNIQUE
N:M Tabela de associação
Ternária Tabela com 3 FK
Recursiva FK para a própria tabela
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Casos especiais de conversão

  • Recursiva: Funcionario(id, nome, chefe_id), chefe_id aceita NULL para quem não tem chefe.
  • Ternária: Fornecimento(fornecedor_id, produto_id, projeto_id, quantidade).
  • Generalização: uma tabela com tipo, uma tabela por especialização, ou uma por entidade concreta.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · clínica em SQL

CREATE TABLE Animal (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(60) NOT NULL,
  especie VARCHAR(40) NOT NULL,
  data_nascimento DATE,
  dono_id INT NOT NULL,
  FOREIGN KEY (dono_id) REFERENCES Dono(id)
    ON DELETE RESTRICT
);

Criar Dono e Veterinario antes de Animal e Consulta. A idade calcula-se na consulta.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Modelo físico: tipos de dados

O SQL destes slides segue o dialeto do MySQL 8.

Família Tipos Cuidado
Inteiros INT, BIGINT INT ≈ 2,1 mil milhões
Exatos DECIMAL(p,s) Dinheiro
Aproximados FLOAT, DOUBLE Nunca dinheiro
Texto CHAR, VARCHAR, TEXT Tamanho
Datas DATE, DATETIME, TIMESTAMP TIMESTAMP até 2038 (MySQL)
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Índices e motores

  • Índice: estrutura auxiliar (árvore B) que acelera pesquisas por uma coluna.
  • Custo: espaço e escritas mais lentas.
  • InnoDB: motor do MySQL com transações e chaves estrangeiras.
  • Conjunto de carateres utf8mb4.
CREATE INDEX idx_consulta_data_hora ON Consulta (data_hora);

MySQL (InnoDB): a coluna com FK já recebe índice automático. PostgreSQL: não, cria-se à mão.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · tipos para pagamentos

Coluna Tipo Porquê
valor DECIMAL(10,2) Dinheiro exato
data_pagamento DATE Ordenável
referencia CHAR(9) Pode começar por zero
metodo ENUM(...) Lista curta e estável
pago BOOLEAN Duas hipóteses
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Ler a documentação do SGBD

  • Sintaxe nos manuais: [ ] opcional, | alternativa, { } escolha, ... repetição.
  • Ler a versão do manual igual à do SGBD instalado.
  • Procurar as notas junto de cada cláusula: é onde estão as exceções.
  • Confirmar com um teste pequeno no SGBD.

reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT: no MySQL, o InnoDB rejeita SET DEFAULT.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 5 · Fronteiras, comportamento e implementação

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Representação das fronteiras

  • Diagrama de contexto: o sistema ao centro, rodeado de atores e sistemas externos.
  • Diagrama de casos de uso: funcionalidades (elipses), atores e a caixa do sistema.

Na loja online: Cliente, Gestor de stock e Serviço de pagamentos (fora da fronteira).

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Representação do comportamento

Diagrama Mostra Exemplo
Atividade Fluxo de passos e decisões Da compra à confirmação
Sequência Mensagens no tempo App, SGBD, pagamentos
Estados Estados e transições Encomenda: pendente, paga, enviada, entregue
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Representação da implementação

  • Diagrama de componentes: módulos (interface, serviço, base de dados) e dependências.
  • Diagrama de implantação: nós onde corre cada parte (servidor web, servidor de BD).
  • Para a base de dados: script DDL, versão do SGBD, configuração e cópias de segurança.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · do estado à restrição

Estados: pendente, paga, enviada, entregue, cancelada.

estado ENUM('pendente','paga','enviada','entregue','cancelada')
  NOT NULL DEFAULT 'pendente'

As transições não cabem no ENUM: validam-se na aplicação ou com um gatilho; guarda-se o histórico em EncomendaEstado.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 6 · Normalização: dependências e anomalias

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Porquê normalizar

Normalizar é decompor tabelas mal estruturadas para eliminar a redundância.

Anomalia Exemplo
Inserção Não se regista um cliente sem encomenda
Atualização Mudar o telefone em 40 linhas e falhar uma
Eliminação Apagar a única encomenda apaga o cliente
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Dependências funcionais

X → Y: cada valor de X determina um só valor de Y.

  • Primo / não primo: faz ou não parte de uma chave candidata.
  • Parcial: depende de parte de uma chave composta.
  • Transitiva: A → B e B → C, com B não chave.

As dependências vêm das regras do negócio, não dos dados de exemplo.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Forma não normalizada e notações

UNF: há grupos repetitivos.

Encomenda(num_enc, data, num_cliente, nome_cliente, cod_postal, localidade, (cod_prod, designacao, preco, quantidade))

  • Parênteses: grupo repetitivo entre parênteses.
  • Tabular: células com vários valores.
  • Esquema relacional: PK a negrito, FK assinalada.
  • Diagrama de dependências: setas entre atributos.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Método geral de normalização

  1. Escrever a tabela de partida e identificar a chave.
  2. Listar as dependências funcionais.
  3. 1FN: grupos repetitivos e valores não atómicos.
  4. 2FN: dependências parciais.
  5. 3FN: dependências transitivas.
  6. FNBC, se sobrar determinante que não é chave.
  7. Verificar que a junção reconstitui o original.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 7 · 1FN, 2FN e 3FN passo a passo

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

1FN · valores atómicos

Uma célula, um valor. Sem colunas repetidas.

Errado Correto
telefones = «912000111, 933000222» Telefone(formando_id, numero)
disc1, disc2, disc3 Inscricao(aluno_id, disciplina_id)
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

2FN · a chave inteira

1FN + todo o atributo não primo depende da chave inteira.

Só pode ser violada com chave composta.

EncomendaItem(num_enc, cod_prod, designacao, quantidade): a designacao depende só de cod_prod. Mover para Produto.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

3FN · nada além da chave

2FN + sem dependências transitivas.

Frase para decorar: «cada atributo depende da chave, de toda a chave e de nada além da chave».

Definição precisa: para cada X → A não trivial, X é superchave ou A é primo.

Aluno(id, cod_postal, localidade): cod_postal → localidade. Mover para CodigoPostal.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · encomenda em UNF

num_enc cliente cod_postal localidade produtos
101 7 Marta 4000-100 Porto (P1, Caneta, 1,20, 10); (P2, Caderno, 3,50, 5)
102 9 Rui 1000-200 Lisboa (P1, Caneta, 1,20, 20)

Dependências: num_enc → data, num_cliente; num_cliente → nome, cod_postal; cod_postal → localidade; cod_prod → designacao, preco; {num_enc, cod_prod} → quantidade.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · passo 1, a 1FN

Cada produto ganha a sua linha; a chave passa a ser {num_enc, cod_prod}.

num_enc cod_prod cliente localidade designacao preco quantidade
101 P1 Marta Porto Caneta 1,20 10
101 P2 Marta Porto Caderno 3,50 5
102 P1 Rui Lisboa Caneta 1,20 20

Já não há grupos repetitivos, mas repete-se muita coisa.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · passo 2, a 2FN

Dependências parciais da chave {num_enc, cod_prod}:

  • num_enc → data, cliente, localidade
  • cod_prod → designacao, preco

Só quantidade depende da chave inteira.

  • Encomenda(num_enc, data, num_cliente, nome_cliente, cod_postal, localidade)
  • Produto(cod_prod, designacao, preco)
  • EncomendaItem(num_enc, cod_prod, quantidade)
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · passo 3, a 3FN

Em Encomenda: num_enc → num_cliente → nome_cliente, cod_postal e cod_postal → localidade.

Resultado final:

Cliente, CodigoPostal, Encomenda, Produto, EncomendaItem.

Verificação: a junção das cinco reconstitui as linhas da 1FN, sem acrescentar nem perder nenhuma.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · 3FN em SQL

CREATE TABLE EncomendaItem (
  num_enc  INT,
  cod_prod CHAR(4),
  quantidade      INT NOT NULL CHECK (quantidade > 0),
  PRIMARY KEY (num_enc, cod_prod),
  FOREIGN KEY (num_enc)  REFERENCES Encomenda(num_enc),
  FOREIGN KEY (cod_prod) REFERENCES Produto(cod_prod)
);

A chave composta impede o mesmo produto duas vezes na mesma encomenda.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo · multivalor e dependência parcial

Multivalor. Formando(id, nome, telefones, competencias): dois atributos independentes. Duas tabelas, Telefone e Competencia, para não criar produto cartesiano.

Parcial. Estagio(formando_id, empresa_id, nome_empresa, horas): empresa_id → nome_empresa. Mover o nome para Empresa.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 8 · FNBC, formas avançadas e desnormalização

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

FNBC

Em FNBC, para toda a dependência não trivial X → Y, X é superchave.

Aula(formando, lingua, professor) com professor → lingua:

  • Chaves: {formando, lingua} e {formando, professor}.
  • Em 3FN (lingua é primo), mas não em FNBC.
  • Decomposição: Professor(professor, lingua) e Atribuicao(formando, professor).
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

O custo da FNBC

Depois da decomposição, a dependência {formando, lingua} → professor deixa de ser verificada numa só tabela.

  • Nada impede um formando com dois professores da mesma língua.
  • É preciso uma regra extra (gatilho ou aplicação).
  • Por isso, muitos projetos param na 3FN quando a FNBC perde uma regra.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

4FN e 5FN

  • 4FN: dependências multivalor. Pessoa(nome, lingua, desporto) obriga a todas as combinações; separar em PessoaLingua e PessoaDesporto.
  • 5FN: dependências de junção; raras na prática.

Para este nível, importa reconhecer o problema do produto cartesiano.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Desnormalização

Normalizado Desnormalizado
Sem redundância Leituras mais rápidas
Mais junções Redundância, risco de inconsistência
Gestão (OLTP) Relatórios (OLAP)

Regra: normalizar primeiro; desnormalizar com medição e motivo, e documentar.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · desnormalizar ou não?

Relatório mensal demora 40 s com cinco junções.

  1. Medir com EXPLAIN: falta um índice em Encomenda.data?
  2. Alternativas: índice, vista, tabela de resumo noturna.
  3. Se mesmo assim se desnormalizar: gatilho ou rotina para manter coerente, e registar a decisão.
  4. A tabela em 3FN fica como fonte de verdade.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Bloco 9 · Integridade, RGPD e caso prático

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Integridade da informação em camadas

  1. Modelo: normalização, cada facto num só sítio.
  2. Restrições: chaves, NOT NULL, CHECK, FOREIGN KEY.
  3. Transações.
  4. Permissões.
  5. Cópias de segurança.

Tudo o que o SGBD puder garantir, deve garantir ele.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

RGPD e modelação

Regulamento (UE) 2016/679: aplica-se a dados pessoais.

Princípio No modelo
Licitude, lealdade, transparência Registar a base do tratamento
Finalidade Separar contacto de marketing
Minimização Só as colunas necessárias
Exatidão Um facto, um sítio
Conservação Datas para eliminar
Segurança Permissões, cifragem, hash de palavras-passe
Responsabilidade Dicionário de dados com os dados pessoais

Princípios do artigo 5.º. Soma-se a proteção desde a conceção e por defeito (artigo 25.º): vistas que escondem dados sensíveis.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Direitos dos titulares e apagamento

Acesso, retificação, apagamento, limitação, portabilidade e oposição.

  • A normalização ajuda a encontrar todos os dados de uma pessoa.
  • Com RESTRICT, o cliente com faturas não se apaga: anonimizam-se os dados pessoais e mantém-se a fatura.
  • Cuidado com campos livres (observacoes) e categorias especiais.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Exemplo resolvido · rever um modelo

Inscrito(id, nome, nif, email, telefone, morada, data_nascimento, religiao, observacoes)

  • Para convites bastam nome e e-mail.
  • religiao é categoria especial: retirar.
  • observacoes atrai dados sensíveis: retirar ou delimitar.
  • Acrescentar inscrito_em e consentimento_em.

Inscrito(id, nome, email, inscrito_em, consentimento_em)

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Caso prático · loja online

Categoria ||--o{ Produto : classifica
Cliente   ||--o{ Encomenda : faz
Encomenda ||--|{ EncomendaItem : contem
Produto   ||--o{ EncomendaItem : aparece_em
  • preco_unit na linha: o preço no momento da compra.
  • CASCADE nos itens, RESTRICT no produto.
  • Chave composta (encomenda_id, produto_id).
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Caso prático · consultas de validação

SELECT e.id, SUM(i.quantidade * i.preco_unit) AS total
FROM Encomenda e
JOIN EncomendaItem i ON i.encomenda_id = e.id
GROUP BY e.id;

SELECT c.nome AS categoria, p.nome, p.stock
FROM Produto p
LEFT JOIN Categoria c ON c.id = p.categoria_id
WHERE p.stock < 10
ORDER BY c.nome, p.nome;

JOIN ... ON emparelha FK e PK; GROUP BY forma um grupo por encomenda; WHERE filtra; ORDER BY ordena. O LEFT JOIN mantém produtos sem categoria.

Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Erros comuns a evitar

  • Vários valores numa célula (1FN).
  • N:M resolvido com colunas repetidas.
  • FK do lado errado.
  • Nome, e-mail ou NIF como chave primária.
  • FLOAT para dinheiro.
  • UPDATE ou DELETE sem WHERE.
  • Normalizar a olho, sem listar dependências.
  • CASCADE por hábito.
Aulify · Cursos Profissionais
UC00602 · Modelar bases de dados relacionais

Recapitulando

  • SGBD e arquitetura de três níveis: independência dos dados.
  • Modelo relacional: tabelas, chaves, integridade de entidade, referencial, de domínio e do utilizador.
  • Modelo ER (Chen e pé-de-galinha) e conversão em tabelas; modelo físico e DDL.
  • Normalização: 1FN, 2FN, 3FN e FNBC, sempre verificando a junção.
  • RGPD: licitude, finalidade, minimização, exatidão, conservação, segurança e responsabilidade.

Próximo: fichas e projeto, modelar uma base de dados à escolha.

Aulify · Cursos Profissionais

NOTAS DO PROFESSOR - enfatizar: a diferença entre dado e informação decide o que vale a pena guardar. - erro comum/pergunta: achar que uma folha de cálculo é sempre uma base de dados. Perguntar: o que impede dois utilizadores de a estragarem? - exemplo/analogia: o dado é um tijolo, a informação é a parede com porta e janela.

NOTAS DO PROFESSOR - enfatizar: cada nível responde a uma pergunta diferente e só o físico depende do SGBD. - erro comum/pergunta: começar logo pelo `CREATE TABLE`. Perguntar: com que cliente falaste antes? - exemplo/analogia: programa do cliente, planta e projeto de execução de um edifício.

NOTAS DO PROFESSOR - enfatizar: a dependência dos programas em relação ao formato do ficheiro é o custo escondido. - erro comum/pergunta: pensar que o SGBD só serve para dados grandes. Serve para dados que têm de estar certos. - exemplo/analogia: a cooperativa com duas folhas de cálculo onde o mesmo associado aparece escrito de duas maneiras.

NOTAS DO PROFESSOR - enfatizar: base de dados é o conjunto de dados, SGBD é o programa que a gere. - erro comum/pergunta: dizer «instalei uma base de dados» quando se instalou o MySQL. Pedir a distinção. - exemplo/analogia: o SGBD é o bibliotecário: os livros são a base de dados.

NOTAS DO PROFESSOR - enfatizar: a transferência bancária é a imagem certa de atomicidade. - erro comum/pergunta: esquecer o `COMMIT` e achar que os dados ficaram gravados. Perguntar o que faz o `ROLLBACK`. - exemplo/analogia: levantar dinheiro num multibanco: ou sai o dinheiro e o saldo desce, ou nada acontece.

NOTAS DO PROFESSOR - enfatizar: saber a que família pertence cada comando ajuda a decidir quem tem permissão para o correr. - erro comum/pergunta: misturar `DROP` (apaga a tabela) com `DELETE` (apaga linhas). - exemplo/analogia: DDL é a planta do prédio, DML é a mobília que entra e sai.

NOTAS DO PROFESSOR - enfatizar: a independência dos dados é a razão de existir da arquitetura. - erro comum/pergunta: achar que criar um índice obriga a mudar as consultas. Não obriga. - exemplo/analogia: a secretaria e a contabilidade veem vistas diferentes das mesmas tabelas.

NOTAS DO PROFESSOR - enfatizar: o programador diz o que quer, o SGBD decide como obter. - erro comum/pergunta: achar que o SELECT lê sempre a tabela inteira. Perguntar o que muda com um índice. - exemplo/analogia: pedir um livro à bibliotecária pelo número de cota, em vez de percorrer as estantes.

NOTAS DO PROFESSOR - enfatizar: uma tabela fala de uma só coisa. - erro comum/pergunta: esperar que o SELECT sem ORDER BY devolva sempre a mesma ordem. Não garante. - exemplo/analogia: uma tabela é uma lista de presenças onde cada linha é uma pessoa diferente.

NOTAS DO PROFESSOR - enfatizar: NULL pede sempre uma decisão: o que significa neste campo? - erro comum/pergunta: escrever `= NULL`. Pedir à turma que corra os dois exemplos. - exemplo/analogia: o stock de um produto que ninguém contou não é zero: é desconhecido.

NOTAS DO PROFESSOR - enfatizar: uma tabela tem uma só PK, mas pode ter várias candidatas e várias FK. - erro comum/pergunta: pensar que a FK tem de ser única. Não: muitas matrículas do mesmo aluno. - exemplo/analogia: a PK é o número de sócio de uma biblioteca; a FK é esse mesmo número escrito em cada ficha de empréstimo.

NOTAS DO PROFESSOR - enfatizar: estabilidade e brevidade são as qualidades de uma boa PK. - erro comum/pergunta: usar o e-mail como PK. Perguntar o que acontece quando a pessoa muda de e-mail. - exemplo/analogia: o número de aluno é estável; o nome do aluno pode repetir-se.

NOTAS DO PROFESSOR - enfatizar: o teste de minimalidade: retirar uma coluna ainda identifica? - erro comum/pergunta: incluir colunas a mais numa chave. Pedir à turma que mostre a minimalidade. - exemplo/analogia: é como identificar uma pessoa pelo cartão de cidadão ou pelo passaporte, ambos servem.

NOTAS DO PROFESSOR - enfatizar: tudo o que o SGBD puder garantir, deve garantir ele. - erro comum/pergunta: confiar só na aplicação. Perguntar: e se outra aplicação escrever na mesma base? - exemplo/analogia: o segurança à porta: não deixa entrar quem não está na lista.

NOTAS DO PROFESSOR - enfatizar: a ação referencial é uma decisão de negócio, não técnica. - erro comum/pergunta: escolher CASCADE por hábito e apagar o histórico de um cliente. - exemplo/analogia: fechar um departamento: os funcionários ficam sem departamento, não são despedidos.

NOTAS DO PROFESSOR - enfatizar: para cada operação, nomear a regra de integridade que decide. - erro comum/pergunta: esquecer que SET NULL exige que a coluna aceite NULL. - exemplo/analogia: comparar com a decisão: apagar pessoas porque um departamento fechou seria absurdo.

NOTAS DO PROFESSOR - enfatizar: o hábito de testar o WHERE com um SELECT antes de apagar. - erro comum/pergunta: esquecer o WHERE. Perguntar o que acontece a 2 milhões de linhas. - exemplo/analogia: antes de cortar, medir duas vezes.

NOTAS DO PROFESSOR - enfatizar: o atributo derivado calcula-se, não se guarda. - erro comum/pergunta: guardar a idade em vez da data de nascimento: fica desatualizada. - exemplo/analogia: um quarto de hotel só se identifica pelo número dentro de um hotel.

NOTAS DO PROFESSOR - enfatizar: ler a relação sempre nos dois sentidos. - erro comum/pergunta: pôr a FK do lado errado. Regra: a FK fica no lado «muitos». - exemplo/analogia: um cliente pode não ter encomendas, mas toda a encomenda tem cliente.

NOTAS DO PROFESSOR - enfatizar: onde vive a nota? Nem no aluno nem na disciplina. - erro comum/pergunta: pôr a nota na tabela do aluno. Pedir um exemplo com dois alunos e duas disciplinas. - exemplo/analogia: a inscrição é o bilhete: liga um aluno a uma disciplina e tem a sua própria data.

NOTAS DO PROFESSOR - enfatizar: verificar o enunciado antes de assumir cardinalidades. - erro comum/pergunta: deixar passar um N:M disfarçado de 1:N. - exemplo/analogia: é como ler um contrato: cada «um ou mais» muda o desenho.

NOTAS DO PROFESSOR - enfatizar: Chen serve para ensinar e esboçar; a indústria usa pé-de-galinha. - erro comum/pergunta: confundir losango (relação) com decisão de um fluxograma. - exemplo/analogia: um mapa de metro desenhado com todos os pormenores: bonito, mas pouco prático.

NOTAS DO PROFESSOR - enfatizar: o pé aponta sempre para o lado «muitos». - erro comum/pergunta: ler só um sentido. Pedir a leitura do diagrama nos dois sentidos. - exemplo/analogia: o pé de galinha é a tomada com muitas fichas.

NOTAS DO PROFESSOR - enfatizar: a ferramenta é secundária; o raciocínio é o mesmo. - erro comum/pergunta: gastar a aula a escolher ferramenta. Qualquer uma serve para começar. - exemplo/analogia: o diagrama é para a equipa, como a planta é para o pedreiro.

NOTAS DO PROFESSOR - enfatizar: decorar a tabela: cada elemento tem a sua tradução mecânica. - erro comum/pergunta: esquecer a tabela de associação numa N:M. - exemplo/analogia: tradução simultânea: do desenho para o SQL.

NOTAS DO PROFESSOR - enfatizar: a escolha da generalização depende das consultas. - erro comum/pergunta: esquecer que o topo da hierarquia não tem chefe. - exemplo/analogia: o organigrama da empresa, de baixo para cima.

NOTAS DO PROFESSOR - enfatizar: a ordem de criação segue as dependências das chaves estrangeiras. - erro comum/pergunta: criar a tabela filha primeiro e receber erro de FK. - exemplo/analogia: não se constrói o telhado antes das paredes.

NOTAS DO PROFESSOR - enfatizar: o menor tipo que serve e o tipo certo para a operação. - erro comum/pergunta: guardar datas como texto e perder a capacidade de ordenar e calcular. - exemplo/analogia: 0,1 + 0,2 num FLOAT não dá exatamente 0,3.

NOTAS DO PROFESSOR - enfatizar: índice acelera leituras e atrasa escritas: tudo tem custo. - erro comum/pergunta: indexar todas as colunas. Perguntar o que acontece nos INSERT. - exemplo/analogia: o índice remissivo de um livro.

NOTAS DO PROFESSOR - enfatizar: a referência é texto porque não se faz aritmética com ela. - erro comum/pergunta: guardar referências e códigos postais como número e perder os zeros. - exemplo/analogia: o número de telefone não se soma nem se multiplica.

NOTAS DO PROFESSOR - enfatizar: constar da sintaxe não garante que funcione no SGBD em uso; a nota do manual decide. - erro comum/pergunta: copiar sintaxe de um tutorial sem ver a versão. Perguntar: que versão do MySQL tem o vosso computador? - exemplo/analogia: o manual é o livro de instruções do aparelho; o tutorial é a demonstração na loja.

NOTAS DO PROFESSOR - enfatizar: o que está fora da fronteira não se modela por dentro. - erro comum/pergunta: modelar o sistema de pagamentos dentro da loja. Guarda-se só a referência. - exemplo/analogia: o muro do quintal: o que está dentro é nosso, o resto são vizinhos.

NOTAS DO PROFESSOR - enfatizar: o diagrama de estados dá os valores da coluna `estado`. - erro comum/pergunta: confundir diagrama de atividade com de sequência. - exemplo/analogia: o percurso de uma encomenda é como o de uma carta: cada estado é uma paragem.

NOTAS DO PROFESSOR - enfatizar: o script DDL é o documento de implementação da base de dados. - erro comum/pergunta: deixar o navegador falar diretamente com a base de dados. - exemplo/analogia: a planta de um armazém diz onde cada coisa está e como se chega lá.

NOTAS DO PROFESSOR - enfatizar: os valores vêm do diagrama de estados; as transições precisam de lógica. - erro comum/pergunta: achar que o ENUM impede saltar de «entregue» para «pendente». - exemplo/analogia: a máquina de estados é o regulamento do jogo.

NOTAS DO PROFESSOR - enfatizar: as três anomalias são o argumento de venda da normalização. - erro comum/pergunta: achar que normalizar é só «partir tabelas». Pedir sempre a anomalia que se evita. - exemplo/analogia: escrever o mesmo endereço em 40 cartas: um erro, quarenta correções.

NOTAS DO PROFESSOR - enfatizar: uma instância pode desmentir uma dependência, nunca a prova. - erro comum/pergunta: deduzir dependências olhando só para três linhas de dados. - exemplo/analogia: o número de cliente determina o nome; o nome não determina o número.

NOTAS DO PROFESSOR - enfatizar: ler os parênteses como «isto repete-se». - erro comum/pergunta: misturar a notação de parênteses com a de esquema relacional. - exemplo/analogia: uma fatura em papel: cabeçalho uma vez, linhas várias.

NOTAS DO PROFESSOR - enfatizar: o passo 7 evita perder informação. - erro comum/pergunta: decompor sem listar dependências primeiro. - exemplo/analogia: uma receita: não se salta nenhum passo.

NOTAS DO PROFESSOR - enfatizar: se cabe «e» ou «,» numa célula, provavelmente viola a 1FN. - erro comum/pergunta: manter colunas numeradas (`disc1`, `disc2`) por serem «poucas». - exemplo/analogia: uma gaveta com uma só meia em cada compartimento.

NOTAS DO PROFESSOR - enfatizar: com chave simples, a 1FN já implica a 2FN. - erro comum/pergunta: procurar violações da 2FN numa tabela de chave simples. - exemplo/analogia: num grupo com dois chefes, cada tarefa tem de depender dos dois.

NOTAS DO PROFESSOR - enfatizar: a frase da chave, da chave inteira e de nada além da chave. - erro comum/pergunta: confundir transitiva com parcial: transitiva passa por outro atributo não chave. - exemplo/analogia: um aluno não determina a cidade; o código postal sim.

NOTAS DO PROFESSOR - enfatizar: as dependências escrevem-se antes de decompor. - erro comum/pergunta: esquecer a dependência `cod_postal → localidade`. Pedir à turma que a encontre. - exemplo/analogia: códigos postais ilustrativos; o que conta é a regra.

NOTAS DO PROFESSOR - enfatizar: a 1FN cria redundância visível; a 2FN e 3FN corrigem-na. - erro comum/pergunta: parar na 1FN por a tabela «já estar certa». - exemplo/analogia: a Marta e a Caneta aparecem repetidas: é o sintoma.

NOTAS DO PROFESSOR - enfatizar: separar o que depende de cada parte da chave. - erro comum/pergunta: deixar `quantidade` fora de `EncomendaItem`. - exemplo/analogia: cada informação vai para a tabela da coisa a que pertence.

NOTAS DO PROFESSOR - enfatizar: cinco tabelas, cada facto num só sítio. - erro comum/pergunta: achar que são «demasiadas tabelas». Perguntar: onde se corrige o nome de um cliente? - exemplo/analogia: o preço unitário na linha é outro facto: o preço no momento da venda.

NOTAS DO PROFESSOR - enfatizar: o modelo normalizado traduz-se diretamente em chaves. - erro comum/pergunta: esquecer a chave composta e permitir linhas duplicadas. - exemplo/analogia: o SQL é o resumo do desenho.

NOTAS DO PROFESSOR - enfatizar: atributos multivalor independentes vão para tabelas diferentes. - erro comum/pergunta: pôr telefones e competências na mesma tabela auxiliar. - exemplo/analogia: 2 telefones por 3 competências dariam 6 linhas de informação inventada.

NOTAS DO PROFESSOR - enfatizar: FNBC é uma 3FN mais exigente. - erro comum/pergunta: achar que 3FN e FNBC são sempre iguais. - exemplo/analogia: cada professor ensina uma só língua: a língua é decidida pelo professor.

NOTAS DO PROFESSOR - enfatizar: a FNBC pode não preservar todas as dependências. - erro comum/pergunta: decompor sempre até à FNBC sem ver o que se perde. - exemplo/analogia: arrumar tudo por tema e depois já não conseguir saber quem atende quem.

NOTAS DO PROFESSOR - enfatizar: reconhecer o problema; a teoria fina fica para depois. - erro comum/pergunta: gastar a aula na 5FN. Basta saber que existe. - exemplo/analogia: línguas e desportos de uma pessoa não têm relação entre si.

NOTAS DO PROFESSOR - enfatizar: medir antes de desnormalizar. - erro comum/pergunta: desnormalizar «por precaução». Perguntar: medido onde? - exemplo/analogia: a fatura emitida guarda o nome do cliente como estava naquele dia.

NOTAS DO PROFESSOR - enfatizar: o índice resolve muitos casos sem desnormalizar. - erro comum/pergunta: saltar a medição e ir direto à duplicação de dados. - exemplo/analogia: antes de mudar a casa, ver se não é só a lâmpada.

NOTAS DO PROFESSOR - enfatizar: várias camadas, porque nenhuma é infalível sozinha. - erro comum/pergunta: deixar toda a validação na interface. - exemplo/analogia: cinto, airbag e travões: cada um protege de uma coisa.

NOTAS DO PROFESSOR - enfatizar: o RGPD traduz-se em decisões concretas de colunas e chaves. - erro comum/pergunta: guardar dados «para o caso de ser preciso». - exemplo/analogia: a mala de viagem: só levamos o que vamos usar.

NOTAS DO PROFESSOR - enfatizar: apagar nem sempre é possível: anonimizar é a alternativa. - erro comum/pergunta: usar CASCADE para satisfazer um pedido de apagamento e destruir as faturas. - exemplo/analogia: riscar o nome de uma fatura, mas manter o valor e a data.

NOTAS DO PROFESSOR - enfatizar: cada coluna tem de ter um motivo para existir. - erro comum/pergunta: manter o NIF só porque «pode vir a ser preciso». - exemplo/analogia: menos colunas, menos risco.

NOTAS DO PROFESSOR - enfatizar: o N:M entre encomenda e produto resolve-se com a associativa. - erro comum/pergunta: buscar o preço ao produto e alterar encomendas antigas. - exemplo/analogia: o talão da compra não muda quando o preço sobe.

NOTAS DO PROFESSOR - enfatizar: validar o modelo com consultas reais de negócio. - erro comum/pergunta: usar JOIN simples e perder os produtos sem categoria. - exemplo/analogia: o teste do modelo é conseguir responder às perguntas do dono da loja.

NOTAS DO PROFESSOR - enfatizar: este é o resumo das armadilhas que mais custam. - erro comum/pergunta: cada erro desta lista aparece no projeto. Pedir à turma que cace dois nos próprios trabalhos. - exemplo/analogia: lista de verificação antes de entregar.