Modelar bases de dados relacionais (UC00602)

Há um pacote premium desta unidade: um PowerPoint editável de 100 ou mais diapositivos, um manual em PDF e dois testes com correção. Esta sebenta continua gratuita.

Introdução

Todo o software que guarda informação (uma loja online, a gestão de uma clínica, a faturação de uma oficina) assenta numa base de dados. Se o modelo dessa base de dados estiver bem pensado, o código por cima fica simples, as consultas são rápidas e os dados são fiáveis. Se estiver mal pensado, aparecem registos duplicados, valores contraditórios, relatórios que não batem certo e correções que nunca acabam. Por isso se diz que o modelo de dados é a fundação do sistema: um erro aqui propaga-se a tudo o que é construído por cima.

Esta sebenta ensina a modelar bases de dados relacionais, e segue as três realizações do referencial da UC. Primeiro aprendemos a definir a estrutura de uma base de dados relacional: o que são dados e modelos de dados, o que faz um Sistema de Gestão de Bases de Dados (SGBD), como é a sua arquitetura, e como se organiza o modelo relacional em tabelas, registos, campos e chaves, com as respetivas regras de integridade. Depois aprendemos a representar modelos relacionais: o modelo de entidades e relações, os tipos de atributos, as notações (Chen e pé-de-galinha), a passagem do modelo conceptual ao modelo físico, e os diagramas que representam as fronteiras, o comportamento e a implementação do sistema. Por fim aprendemos a normalizar dados não normalizados, passo a passo, da forma não normalizada até à terceira forma normal e à forma normal de Boyce-Codd, sempre com atenção à integridade da informação, ao Regulamento Geral de Proteção de Dados e às normas aplicáveis.

Como estudar com esta sebenta. Cada capítulo tem explicação, tabelas de consulta e, sempre que a matéria o pede, um exemplo resolvido passo a passo. Tenta resolver o exemplo no papel antes de ler a resolução. Quando houver SQL, escreve-o num SGBD verdadeiro (o SQL desta sebenta segue o dialeto do MySQL 8; o PostgreSQL e o SQLite também servem, com as adaptações do Apêndice) e corre-o: um CREATE TABLE que dá erro ensina mais do que dez que correm bem. No fim há erros comuns, glossário, síntese e exercícios resolvidos para rever; as fichas e o projeto da UC completam a prática.

1. Dados, informação e bases de dados

1.1 Dado, informação e conhecimento

Um dado é um facto isolado, sem contexto: 42, Lisboa, 2026-05-19. Sozinho não diz nada. A informação nasce quando os dados ganham contexto e significado: «o aluno número 42 matriculou-se na escola de Lisboa a 19 de maio de 2026» já responde a perguntas. O conhecimento é o que se conclui da informação: «este ano matricularam-se mais alunos em Lisboa do que no Porto». Uma base de dados guarda dados de forma organizada para que deles se possa extrair informação e, com ela, tomar decisões.

1.2 O que é uma base de dados

Uma base de dados é um conjunto organizado de dados relacionados entre si, guardados de forma persistente (sobrevivem ao desligar do computador), partilhada (vários utilizadores e programas usam os mesmos dados) e estruturada (cada dado tem um sítio e um significado definidos). Uma lista de contactos num bloco de notas não é uma base de dados no sentido da UC: falta-lhe estrutura, controlo de acessos, garantias de consistência e uma linguagem para a interrogar.

1.3 Modelo de dados e níveis de modelação

Um modelo de dados é um conjunto de conceitos que serve para descrever a estrutura de uma base de dados (que dados existem e como se organizam), as restrições que esses dados têm de cumprir e as operações que se podem fazer sobre eles. Modelar é decidir tudo isto antes de escrever a primeira linha de SQL. O trabalho faz-se em três níveis, cada um mais próximo da máquina.

Pensa num edifício: o nível conceptual é o programa do cliente («quero três quartos e uma cozinha aberta»), o lógico é a planta, e o físico é o projeto de execução, com materiais e medidas.

1.4 Tipos de modelos lógicos

Ao longo da história foram usados vários modelos lógicos. O hierárquico organiza os dados em árvore (cada registo tem um só «pai»); o modelo em rede permite vários «pais» mas exige ponteiros explícitos; o relacional, proposto por Edgar F. Codd em 1970, organiza tudo em tabelas ligadas por valores; o orientado a objetos guarda objetos de uma linguagem de programação; e os modelos NoSQL (chave-valor, documentos, famílias de colunas e grafos) surgiram para casos de enorme volume ou de estrutura muito variável. O modelo relacional continua a ser o mais usado em sistemas de gestão (faturação, stocks, recursos humanos, escolas) porque é simples, assenta em fundamentos matemáticos sólidos e tem uma linguagem normalizada, o SQL (norma ISO/IEC 9075).

1.5 Ficheiros e bases de dados

Antes dos SGBD, cada aplicação guardava os seus dados em ficheiros próprios. Um ficheiro de texto, um CSV ou uma folha de cálculo são os exemplos que ainda hoje encontramos. Os ficheiros podem ter várias organizações: sequencial (os registos leem-se pela ordem em que foram escritos), indexada (um índice indica onde está cada registo, como o índice remissivo de um livro) e direta ou por dispersão (uma função calcula a posição do registo a partir da chave). A pesquisa por chave num ficheiro sequencial obriga a percorrer tudo; num indexado ou direto é quase imediata.

1.6 Ciclo de vida de uma base de dados

Uma base de dados cria-se por fases: levantamento de requisitos (falar com quem vai usar o sistema e perceber que informação existe e que regras a governam), modelação conceptual, modelação lógica, modelação física e implementação, carregamento e teste, e manutenção. Quanto mais cedo se apanha um erro, mais barato é corrigi-lo: mudar uma linha num diagrama custa minutos; mudar uma tabela com dois milhões de registos e dez programas a depender dela custa semanas.

Exemplo resolvido · Da folha de cálculo à base de dados

Situação. Uma cooperativa agrícola guarda a lista de associados numa folha de cálculo e as entregas de fruta noutra. Em ambas aparece o nome do associado, escrito por pessoas diferentes.

Passo 1 · Identificar os problemas. O mesmo associado aparece escrito de duas maneiras («Rui Santos» e «R. Santos») e com dois telefones. Não se sabe se são duas pessoas ou uma, nem qual o telefone certo. É redundância que gerou inconsistência.

Passo 2 · Perceber a causa. O nome e o telefone do associado estão repetidos em cada entrega, quando pertencem ao associado, não à entrega.

Passo 3 · Propor a solução. Duas tabelas: Associado (um registo por associado, com identificador próprio) e Entrega (um registo por entrega, que aponta para o associado pelo identificador). Corrigir o telefone passa a fazer-se num único sítio.

Passo 4 · Verificar. Perguntar «quantos kg entregou o associado 1 este ano?» passa a ter uma resposta única, porque deixa de haver dúvida sobre quem é o associado 1.

2. Sistemas de Gestão de Bases de Dados (SGBD)

2.1 O que é um SGBD

Um Sistema de Gestão de Bases de Dados (SGBD, em inglês DBMS) é o programa que cria, mantém e dá acesso a uma ou mais bases de dados. Fica entre os dados guardados no disco e quem os usa (pessoas e aplicações). Não se deve confundir base de dados (os dados) com SGBD (o programa que os gere) nem com sistema de base de dados (o conjunto de dados, SGBD e aplicações). Dizer «instalei uma base de dados» quando se instalou o MySQL é um abuso comum, mas convém saber a diferença.

2.2 Funções de um SGBD

2.3 Transações e propriedades ACID

Uma transação é um conjunto de operações que tem de ser tratado como um todo: ou se faz tudo, ou não se faz nada. Transferir 100 euros da conta A para a conta B são duas operações (debitar A, creditar B); se o sistema falhar entre as duas, o dinheiro desaparece. Os SGBD relacionais garantem as propriedades ACID.

START TRANSACTION;
UPDATE Conta SET saldo = saldo - 100 WHERE id = 1;
UPDATE Conta SET saldo = saldo + 100 WHERE id = 2;
COMMIT;   -- confirma; ROLLBACK desfaria as duas alterações

2.4 Quem trabalha com um SGBD

O administrador da base de dados (DBA) instala, configura, protege e faz cópias de segurança. O analista ou modelador desenha o modelo de dados. O programador escreve as aplicações e as consultas. O utilizador final usa a aplicação sem saber que o SGBD existe. Numa equipa pequena, a mesma pessoa pode fazer tudo isto, e é por isso que um técnico de desenvolvimento de software tem de dominar o modelo.

2.5 As linguagens do SGBD

O SQL divide-se em sublinguagens conforme o que se pretende fazer.

Em muitos manuais o SELECT aparece dentro da DML; o importante é perceber as cinco famílias.

2.6 SGBD relacionais mais usados

MySQL e o seu derivado MariaDB (muito usados na web), PostgreSQL (completo e rigoroso), SQLite (um ficheiro só, embutido em aplicações e telemóveis), Microsoft SQL Server e Oracle Database (muito comuns em grandes organizações). Todos falam SQL, mas cada um tem as suas diferenças de sintaxe e de tipos de dados; nesta sebenta indica-se sempre que a sintaxe é de um sistema em particular.

2.7 Vantagens e desvantagens

As vantagens são a menor redundância, a consistência, a partilha segura, a independência dos programas em relação ao formato dos dados, a recuperação após falhas e uma linguagem comum. As desvantagens são o custo (licenças, hardware, pessoal), a complexidade de instalação e administração e o facto de um SGBD ser um ponto único de falha, que obriga a cópias de segurança e, em sistemas críticos, a replicação.

3. Arquitetura de um sistema de gestão de bases de dados

3.1 A arquitetura de três níveis

A arquitetura de referência, conhecida como ANSI/SPARC, separa a base de dados em três níveis, e cada nível esconde os detalhes do seguinte.

Um exemplo concreto: a secretaria vê uma vista com os dados de contacto dos clientes, a contabilidade vê outra com os valores faturados, mas ambas assentam nas mesmas tabelas. As vistas criam-se com CREATE VIEW.

3.2 Independência dos dados

A grande vantagem dos três níveis é a independência. A independência lógica permite mudar o nível conceptual (acrescentar uma coluna ou uma tabela) sem reescrever as aplicações que não a usam. A independência física permite mudar o nível interno (criar um índice, mudar de disco, reorganizar ficheiros) sem alterar o modelo lógico nem as consultas. Num sistema de ficheiros soltos nada disto é possível, porque o programa conhece o formato do ficheiro.

3.3 Componentes internos de um SGBD

Por baixo da linguagem SQL trabalham vários módulos. O processador de consultas analisa o texto SQL e verifica se é válido. O otimizador escolhe, de entre as várias formas de obter o resultado, a que gasta menos recursos (por exemplo, usar um índice em vez de ler a tabela toda). O executor corre o plano escolhido. O gestor de armazenamento lê e escreve páginas no disco e mantém uma memória intermédia (buffer) com as mais usadas. O gestor de transações garante o isolamento e a atomicidade com ajuda de um registo de transações (log). O catálogo guarda os metadados.

3.4 Arquiteturas de implantação

Num sistema cliente-servidor, o SGBD corre num servidor e as aplicações ligam-se pela rede. Em aplicações web usa-se normalmente uma arquitetura em camadas: o navegador (apresentação) fala com o servidor aplicacional (lógica de negócio) que fala com o SGBD (dados). Nunca se deve deixar o navegador ligar-se diretamente à base de dados. Há ainda bases de dados embutidas, como o SQLite, que vivem dentro da própria aplicação sem processo servidor.

Exemplo resolvido · O percurso de uma consulta

Situação. Uma aplicação envia SELECT nome FROM Cliente WHERE id = 7;. Descreve o que acontece.

Passo 1. O processador de consultas verifica a sintaxe e consulta o catálogo: a tabela Cliente existe? Tem a coluna nome? O utilizador tem permissão de leitura?

Passo 2. O otimizador vê que existe um índice sobre a chave primária id e decide usá-lo, em vez de ler a tabela inteira.

Passo 3. O executor pede ao gestor de armazenamento a página onde está o registo 7. Se a página já estiver na memória intermédia, não se vai ao disco.

Passo 4. O resultado (uma só linha, uma só coluna) volta à aplicação. Como é uma leitura, não há nada a registar no log.

Conclusão. O programador escreveu o que queria, não como obtê-lo. Essa separação é a independência dos dados em ação.

4. O modelo relacional: estrutura de dados

4.1 A ideia de Codd

Em 1970, Edgar F. Codd propôs organizar os dados em relações, conceito retirado da matemática, e consultá-los por operações sobre essas relações, sem ponteiros nem caminhos físicos. Na prática, uma relação é o que no dia a dia se chama tabela. O modelo tem três componentes: uma estrutura (as relações), regras de integridade (o que é válido) e operadores (como se consulta e altera).

4.2 Vocabulário: formal, de base de dados e do quotidiano

Atenção: a palavra cardinalidade tem dois sentidos. Aqui é o número de linhas de uma tabela; mais à frente (capítulo 7) é o número de ocorrências que se podem associar numa relação entre entidades (1:1, 1:N, N:M).

4.3 Propriedades de uma relação

Para ser uma relação válida, uma tabela cumpre cinco condições: (1) cada coluna tem um nome único e guarda valores de um só domínio; (2) cada célula contém um único valor atómico, indivisível para efeitos do modelo; (3) não há linhas duplicadas; (4) a ordem das linhas é irrelevante; (5) a ordem das colunas é irrelevante. É por isto que SELECT sem ORDER BY não garante nenhuma ordem de resultados.

4.4 Domínio e tipo de dados

O domínio de um atributo é o conjunto de valores que ele pode tomar. O domínio de nota pode ser «número decimal entre 0 e 20»; o de estado, «um de pendente, paga, enviada, entregue, cancelada». Os SGBD aproximam o domínio com o tipo de dados (capítulo 10) e com restrições CHECK.

4.5 O valor NULL

NULL significa «sem valor»: desconhecido, não aplicável ou ainda não preenchido. Não é zero nem texto vazio. Uma comparação com NULL nunca é verdadeira nem falsa, é desconhecida: WHERE telefone = NULL não devolve nada, mesmo com linhas sem telefone. Para testar usa-se IS NULL e IS NOT NULL. O NULL é útil para dados opcionais, mas deve ser evitado onde o dado é obrigatório (NOT NULL).

4.6 Esquema e instância

O esquema de uma relação é a sua estrutura (nome, colunas, domínios), e muda raramente. A instância é o conteúdo num dado momento, que muda a cada INSERT e UPDATE. Escreve-se o esquema em forma textual abreviada, com a chave primária a negrito:

Cliente(id, nome, email, telefone)

4.7 Operações básicas sobre relações

Codd definiu a álgebra relacional, base teórica do SQL. Três operações chegam para começar.

Exemplo resolvido · Ler uma tabela

Considera Produto(id, nome, preco, stock) com a seguinte instância:

Grau. São 4 colunas, logo o grau é 4. Cardinalidade da relação. São 3 linhas. Domínios plausíveis. id inteiro positivo, nome texto, preco decimal com 2 casas e stock inteiro não negativo. Valor ausente. O stock da mochila está vazio, ou seja, NULL: não se sabe se são zero unidades ou se ninguém contou. A pergunta «que produtos não têm stock?» não se responde com WHERE stock = 0 (falha a mochila) nem com WHERE stock = NULL (não devolve nada), mas com WHERE stock = 0 OR stock IS NULL, depois de se decidir o que NULL significa. Seleção e projeção. «Nomes dos produtos com preço inferior a 5» é SELECT nome FROM Produto WHERE preco < 5;, isto é, uma seleção (preco < 5) seguida de uma projeção (nome), com resultado Caneta azul e Caderno A5.

5. Tabelas, registos, campos e chaves

5.1 Tabelas, registos e campos

Numa base de dados relacional, a tabela agrupa os dados de uma mesma coisa (clientes, produtos, encomendas). Cada registo (linha) é uma ocorrência concreta, e cada campo (coluna) é uma propriedade comum a todos os registos. Uma regra de ouro de modelação: uma tabela fala de uma só coisa. Se numa tabela de encomendas aparece a morada do cliente e o nome do produto, a tabela está a falar de três coisas e vai dar problemas (capítulos 12 a 14).

5.2 Para que servem as chaves

Como não há linhas duplicadas e a ordem não conta, é preciso uma forma de identificar cada registo e de ligar tabelas entre si. Essa é a função das chaves.

5.3 Tipos de chaves

Uma tabela tem uma e uma só chave primária, mas pode ter várias candidatas e várias estrangeiras. A chave estrangeira pode repetir-se (muitas matrículas do mesmo aluno), e pode ser NULL se a ligação for opcional.

5.4 Chave natural ou chave artificial

Uma chave natural vem dos dados do mundo real (ISBN de um livro, matrícula de um carro, NIF). Uma chave artificial (surrogate key) é inventada pelo sistema, normalmente um inteiro sequencial. A artificial é estável, curta e fácil de ligar entre tabelas; a natural tem significado mas pode mudar ou ter exceções (um NIF pode ser corrigido, o nome de uma pessoa pode repetir-se, um e-mail muda quando a pessoa muda de fornecedor). A prática corrente é usar uma chave artificial como primária e declarar as naturais como UNIQUE quando existem.

5.5 Como escolher uma chave primária

A boa chave primária é única, nunca nula, estável (não muda ao longo da vida do registo), curta e sem informação sensível. Por esta ordem de ideias, o nome de uma pessoa e o e-mail são péssimas chaves primárias; o número de cartão de cidadão ou o NIF não devem ser usados como chave, para não se espalharem como chave estrangeira por todas as tabelas, o que complica o cumprimento do RGPD (capítulo 15).

Exemplo resolvido · Encontrar as chaves candidatas

Situação. Viatura(matricula, chassis, marca, modelo, cor). A matrícula identifica a viatura, o número de chassis também, e nenhuma outra coluna é única.

Passo 1. Procurar colunas que, sozinhas, não se repetem: matricula e chassis.

Passo 2. Verificar minimalidade: ambas têm uma só coluna, logo não se pode retirar nada. São as duas chaves candidatas.

Passo 3. Escolher a primária. A matrícula pode mudar em certas situações e o chassis é longo; opta-se por acrescentar id artificial como PK e declarar matricula e chassis como UNIQUE.

Passo 4. Confirmar que {marca, modelo} não serve: muitas viaturas partilham marca e modelo, logo não identifica.

CREATE TABLE Viatura (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  matricula VARCHAR(10) NOT NULL UNIQUE,
  chassis   VARCHAR(17) NOT NULL UNIQUE,
  marca     VARCHAR(40) NOT NULL,
  modelo    VARCHAR(40) NOT NULL,
  cor       VARCHAR(20)
);

6. Regras de integridade e gestão de dados

6.1 O que é a integridade

Integridade é a garantia de que os dados guardados são corretos e coerentes com as regras do negócio. O modelo relacional define duas regras gerais, que qualquer SGBD relacional aplica, e permite regras específicas.

6.2 Os quatro tipos de integridade

6.3 Restrições em SQL

Nota prática: o MySQL só aplica as restrições CHECK a partir da versão 8.0.16; em versões anteriores aceitava a sintaxe mas ignorava-a. O SQLite tem as chaves estrangeiras desligadas por omissão e é preciso ativá-las com PRAGMA foreign_keys = ON;. No MySQL, a forma abreviada cliente_id INT REFERENCES Cliente(id), escrita junto da coluna, é lida mas ignorada: não cria a chave estrangeira e não dá erro. Por isso a chave estrangeira declara-se sempre com FOREIGN KEY separada.

6.4 Ações referenciais

A integridade referencial levanta uma pergunta: o que acontece a um registo «filho» quando o «pai» é apagado ou alterado? A resposta declara-se nas cláusulas ON DELETE e ON UPDATE.

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

A manipulação de dados resume-se a inserir, consultar, atualizar e apagar. Cada uma tem de respeitar as restriçõ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;

Um UPDATE ou DELETE sem WHERE afeta a tabela inteira; é um dos erros mais caros de toda a informática. Antes de apagar, corre-se o SELECT com a mesma condição para ver o que será afetado.

Exemplo resolvido · Prever o que o SGBD faz

Esquema. Departamento(id, nome) e Funcionario(id, nome, depto_id) com FOREIGN KEY (depto_id) REFERENCES Departamento(id) ON DELETE SET NULL. Instância: departamento 1 (Contabilidade) com os funcionários Ana (id 1, depto 1) e Rui (id 2, depto 1); departamento 2 (Armazém) com Teresa (id 3, depto 2).

Operação A. INSERT INTO Funcionario VALUES (4, 'Jorge', 9);. O departamento 9 não existe. Resultado: o SGBD recusa por violação da integridade referencial.

Operação B. DELETE FROM Departamento WHERE id = 1;. Por causa do SET NULL, o departamento é apagado e Ana e Rui ficam com depto_id = NULL. Se a regra fosse RESTRICT, a operação seria recusada; se fosse CASCADE, Ana e Rui seriam apagados, o que seria uma má escolha de modelação: apagar pessoas porque um departamento fechou.

Operação C. INSERT INTO Funcionario VALUES (1, 'Pedro', 2);. O id 1 já existe. Resultado: recusado, integridade de entidade.

Conclusão. A ação referencial é uma decisão de negócio e deve ser escolhida a pensar no que acontece aos dados históricos.

7. Modelo de entidades e relações

7.1 O que é o modelo entidade-relação

O modelo entidade-relação (ER, proposto por Peter Chen em 1976) é um modelo conceptual: descreve o negócio em termos de coisas que interessam (entidades), das suas propriedades (atributos) e das associações entre elas (relações), sem pensar em tabelas, tipos ou SGBD. É a linguagem comum entre quem sabe do negócio e quem sabe de informática.

7.2 Entidades

Uma entidade é uma classe de coisas, pessoas, lugares ou acontecimentos sobre os quais queremos guardar informação, e que se distinguem entre si. Uma ocorrência (ou instância) é um caso concreto. A entidade Cliente tem como ocorrências a Marta Lopes e o Rui Santos. Nomeia-se a entidade com um substantivo no singular.

Uma entidade forte existe por si e tem chave própria (Cliente). Uma entidade fraca só se identifica com o auxílio de outra: o Quarto de um hotel só se identifica pelo número do quarto dentro de um hotel; a sua chave inclui a chave do dono.

7.3 Atributos e os seus tipos

Um atributo é uma propriedade de uma entidade. Há várias classificações, que se cruzam.

7.4 Relações

Uma relação (ou associação) liga duas ou mais entidades e nomeia-se com um verbo: o Cliente faz Encomendas, o Aluno inscreve-se em Cursos. O grau da relação é o número de entidades envolvidas: binária (2, a mais comum), unária ou recursiva (a entidade liga-se a si própria: um Funcionário chefia Funcionários) e ternária (3: um Fornecedor fornece um Produto a um Projeto).

7.5 Cardinalidade e participação

A cardinalidade diz quantas ocorrências de uma entidade se podem associar a uma ocorrência da outra. A participação diz se a ligação é obrigatória (total) ou facultativa (parcial). Ler sempre nos dois sentidos.

Na notação (mínimo, máximo), «(0,N)» quer dizer «zero ou mais» e «(1,1)» «exatamente um». Por exemplo, um Cliente pode não ter encomendas, logo é (0,N) do lado do Cliente; mas toda a Encomenda tem de pertencer a um cliente, logo (1,1) do lado da Encomenda.

7.6 Atributos de relação

Quando a propriedade só faz sentido quando se juntam as duas entidades, é um atributo da relação, não de nenhuma das entidades. A nota não é do Aluno nem da Disciplina; é da inscrição do aluno nessa disciplina. A quantidade não é do Produto nem da Encomenda; é do item da encomenda.

7.7 Entidade associativa

Uma relação N:M com atributos próprios, ou que por sua vez se relaciona com outras entidades, é promovida a entidade associativa: a relação Aluno inscreve-se em Curso, com data e nota, transforma-se na entidade Inscricao, ligada por duas relações 1:N.

Exemplo resolvido · Do texto ao modelo conceptual

Requisito. «Uma clínica veterinária regista os donos (nome, telefone) e os seus animais (nome, espécie, data de nascimento). Cada animal tem um só dono, mas um dono pode ter vários animais. Os animais têm consultas (data, motivo, veterinário que atendeu, valor). Cada veterinário (nome, número de cédula) atende muitas consultas.»

Passo 1 · Sublinhar os substantivos. Dono, animal, consulta, veterinário. Candidatos a entidade.

Passo 2 · Atributos de cada um. Dono: nome, telefone. Animal: nome, espécie, data de nascimento. Consulta: data, motivo, valor. Veterinário: nome, cédula. A idade do animal é derivada (não se guarda).

Passo 3 · Relações e cardinalidades.

Passo 4 · Verificar. Há N:M escondida? Um animal pode ter vários donos (por exemplo, um casal)? O enunciado diz que não; se dissesse sim, ter-se-ia de acrescentar a associativa Propriedade. Verificar o requisito evita este tipo de erro.

8. Notações e diagramas ER

8.1 Notação de Chen

É a notação original e a mais didática. Retângulo para entidade, losango para relação, elipse para atributo; o atributo-chave tem o nome sublinhado; a entidade fraca tem retângulo duplo; o atributo multivalor, elipse dupla; o derivado, elipse tracejada. A cardinalidade escreve-se (1, N, M) junto às linhas que ligam a entidade ao losango. É verbosa: um modelo com 20 entidades fica ilegível, e por isso se usa sobretudo para ensinar e para esboços.

 (id)      (nome)                     (data)
   \        /                           |
   [ CLIENTE ] ---1---< faz >---N--- [ ENCOMENDA ]

8.2 Notação de pé-de-galinha

O pé-de-galinha (crow's foot, ou Information Engineering) é a mais usada na indústria. Cada entidade é um retângulo com a lista das suas colunas (com PK e FK assinaladas); as relações são linhas, e os símbolos nas pontas dizem a cardinalidade e a participação. O «pé» (três riscos) indica o lado «muitos».

Cliente ||--o{ Encomenda : faz
Encomenda ||--|{ EncomendaItem : contem
Produto ||--o{ EncomendaItem : aparece_em

Lê-se: um cliente faz zero ou mais encomendas; uma encomenda contém um ou mais itens; um produto aparece em zero ou mais itens. A EncomendaItem é a associativa que resolve o N:M entre Encomenda e Produto.

8.3 Diagramas UML de classes

A UML (Unified Modeling Language) é uma linguagem gráfica geral. O seu diagrama de classes usa-se também para modelar dados: cada classe é um retângulo com atributos e as associações têm multiplicidades como 1, 0..* e 1..*. É comum em equipas que já usam UML para o resto do software.

8.4 Comparação das notações

8.5 Ferramentas

Table Cliente {
  id int [pk, increment]
  nome varchar
  email varchar [unique]
}

Table Encomenda {
  id int [pk, increment]
  data datetime
  cliente_id int [ref: > Cliente.id]
}

Em dbdiagram.io, [ref: > Cliente.id] cria uma chave estrangeira de Encomenda.cliente_id para Cliente.id, numa relação muitos-para-um.

Exemplo resolvido · Traduzir uma notação para a outra

Situação. Em Chen, desenhou-se FORMADOR (id, nome) ligado por um losango «coordena» a CURSO (código, ano), com 1 do lado do formador e N do lado do curso, e o curso tem participação total.

Passo 1. Ler: um formador coordena vários cursos; cada curso é coordenado por um só formador, e todo o curso tem de ter coordenador.

Passo 2. Em pé-de-galinha, do lado do formador, a ponta é «exatamente um» (||); do lado do curso, «zero ou mais» (o{), porque um formador pode ainda não coordenar nenhum curso.

Passo 3. Escrever em texto: Formador ||--o{ Curso : coordena.

Passo 4. Tirar a consequência para o SQL: como todo o curso tem coordenador, Curso.formador_id fica NOT NULL.

9. Do modelo ER ao modelo relacional

9.1 Regras de conversão

O modelo conceptual converte-se em modelo lógico (tabelas) com regras mecânicas. O SQL que se segue, tal como o dos capítulos seguintes, está escrito no dialeto do MySQL 8 (AUTO_INCREMENT, ENUM, DATETIME); o Apêndice lista as diferenças para o PostgreSQL.

9.2 Casos especiais

Relação recursiva. Um funcionário tem um chefe, que é também funcionário: Funcionario(id, nome, chefe_id) com chefe_id a referenciar Funcionario(id), aceitando NULL para o administrador, que não tem chefe.

Relação ternária. Um fornecedor fornece um produto a um projeto: Fornecimento(fornecedor_id, produto_id, projeto_id, quantidade) com chave composta pelas três.

Generalização e especialização. Quando Cliente e Funcionario partilham atributos de Pessoa, há três soluções: uma tabela só para tudo com uma coluna tipo (simples, com muitos NULL), uma tabela para a entidade geral mais uma tabela por especialização (a mais limpa), ou uma tabela por entidade concreta (repete colunas). A escolha depende do que as consultas pedem.

Exemplo resolvido · Converter o modelo da clínica veterinária

Parte-se do exemplo do capítulo 7: Dono, Animal, Consulta e Veterinário, com três relações 1:N.

Passo 1 · Entidades em tabelas. Quatro tabelas, cada uma com id artificial.

Passo 2 · Atributos. idade é derivada: não vira coluna. A espécie é texto simples.

Passo 3 · Relações 1:N em chaves estrangeiras. Animal.dono_id, Consulta.animal_id, Consulta.veterinario_id, todas NOT NULL (participação total do lado N).

Passo 4 · Escrever o SQL.

CREATE TABLE Dono (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  nome     VARCHAR(120) NOT NULL,
  telefone VARCHAR(20)
);

CREATE TABLE Veterinario (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  nome    VARCHAR(120) NOT NULL,
  cedula  VARCHAR(20)  NOT NULL UNIQUE
);

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
);

CREATE TABLE Consulta (
  id             INT PRIMARY KEY AUTO_INCREMENT,
  data_hora      DATETIME NOT NULL,
  motivo         VARCHAR(200) NOT NULL,
  valor          DECIMAL(8,2) NOT NULL CHECK (valor >= 0),
  animal_id      INT NOT NULL,
  veterinario_id INT NOT NULL,
  FOREIGN KEY (animal_id)      REFERENCES Animal(id)      ON DELETE RESTRICT,
  FOREIGN KEY (veterinario_id) REFERENCES Veterinario(id) ON DELETE RESTRICT
);

Passo 5 · Verificar a ordem. Dono e Veterinario criam-se antes de Animal e Consulta, porque uma FK só pode apontar para uma tabela que já existe. Passo 6 · Idade calculada. SELECT nome, TIMESTAMPDIFF(YEAR, data_nascimento, CURDATE()) AS idade FROM Animal; (função do MySQL).

10. Modelos físicos de dados

10.1 O que é o modelo físico

O modelo físico descreve como o modelo lógico fica guardado num SGBD concreto: tipos de dados exatos, índices, motor de armazenamento, partições, espaço em disco. É escrito em DDL. Mudar o modelo físico (por exemplo, criar um índice) não deve mudar o significado dos dados.

10.2 Tipos de dados

Escolhe-se o menor tipo que serve e o tipo certo para a operação: datas guardam-se como DATE, não como texto, para se poder ordenar e calcular com elas.

10.3 Índices

Um índice é uma estrutura auxiliar (normalmente uma árvore B) que acelera as pesquisas por uma coluna, como o índice remissivo de um livro. O SGBD cria-os sozinho para as chaves primárias e para UNIQUE. Criam-se à mão para colunas muito usadas em WHERE e em JOIN. O preço é espaço em disco e escritas mais lentas, porque cada INSERT tem de atualizar os índices.

CREATE INDEX idx_consulta_data_hora ON Consulta (data_hora);

A coluna data_hora não é chave estrangeira, por isso o índice é útil. No MySQL com InnoDB, uma coluna com FOREIGN KEY recebe um índice automaticamente; no PostgreSQL não, e convém criá-lo à mão.

10.4 Motores de armazenamento e outras decisões físicas

O MySQL tem vários motores; o InnoDB, o predefinido, suporta transações e chaves estrangeiras. O conjunto de carateres deve ser utf8mb4 para suportar acentos e emojis. Em tabelas enormes pode-se particionar (dividir fisicamente por data ou intervalo). As cópias de segurança e a replicação também são decisões físicas.

10.5 Ler a documentação do SGBD

Os manuais, guiões e tutoriais técnicos dizem o que um SGBD aceita de facto, e muitas dúvidas de modelação (esta restrição é aplicada?) resolvem-se lendo-os. A página do CREATE TABLE do manual de referência é a que mais interessa. A sintaxe vem num bloco com convenções que convém conhecer: o que está entre parênteses retos [ ] é opcional; a barra | separa alternativas; as chavetas { } agrupam alternativas das quais se escolhe uma; as reticências ... indicam que o elemento se pode repetir; as palavras em minúsculas (como tbl_name) são espaços a preencher, muitas vezes definidos num bloco próprio mais abaixo. As convenções variam entre fabricantes, e a introdução do manual diz quais usa.

Três hábitos ajudam: ler a versão do manual que corresponde à do SGBD instalado, porque o comportamento muda (o CHECK só é aplicado no MySQL a partir da 8.0.16); procurar as notas junto de cada cláusula, onde estão as exceções; e confirmar no próprio SGBD com um teste pequeno. Um tutorial ensina um percurso; o manual de referência responde a uma pergunta concreta.

Exemplo resolvido · Descobrir se o MySQL aceita SET DEFAULT

Situação. Quer-se que, ao apagar uma categoria, os produtos passem para a categoria «Outros». O MySQL permite ON DELETE SET DEFAULT?

Passo 1. Abrir a página do CREATE TABLE da versão em uso, na parte das chaves estrangeiras. Passo 2. Ler a sintaxe: reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT. As barras indicam alternativas, e SET DEFAULT está lá.

Passo 3. Ler a nota junto das opções: o analisador reconhece SET DEFAULT, mas o InnoDB rejeita as definições de tabela que o usem.

Passo 4. Concluir: constar da sintaxe não garante que funcione. No MySQL, a passagem para «Outros» faz-se na aplicação, antes de apagar a categoria, ou escolhe-se SET NULL.

Exemplo resolvido · Escolher tipos para uma tabela de pagamentos

Colunas pedidas: valor pago, data do pagamento, referência multibanco de 9 dígitos, método (numerário, cartão, transferência), pago ou não.

O erro mais frequente aqui é guardar a referência como número e perder os zeros à esquerda.

11. Fronteiras, comportamento e implementação do sistema

Uma base de dados faz parte de um sistema maior. Modelar só tabelas deixa de fora três perguntas: onde acaba o sistema, como se comporta e como fica instalado. A UML responde com diagramas diferentes.

11.1 Representação das fronteiras do sistema

As fronteiras separam o que está dentro do sistema do que lhe é exterior (pessoas e outros sistemas). Representam-se com um diagrama de contexto (o sistema no centro, rodeado dos atores e dos sistemas externos, com setas a mostrar o fluxo de informação) e com o diagrama de casos de uso (cada elipse é uma funcionalidade, os bonecos são os atores, e uma caixa delimita o sistema). Numa loja online, os atores são o Cliente, o Gestor de stock e o Serviço de pagamentos; este último está fora da fronteira, e por isso a loja guarda apenas a referência do pagamento, não o processa.

11.2 Representação do comportamento do sistema

O comportamento mostra o que acontece ao longo do tempo. Três diagramas bastam.

O diagrama de estados liga-se diretamente à base de dados: os estados da encomenda são os valores da coluna estado (um ENUM ou um CHECK), e as transições proibidas (de «entregue» para «pendente») são regras a garantir na aplicação ou com gatilhos.

11.3 Representação da implementação do sistema

A implementação mostra os blocos de software e onde correm. O diagrama de componentes mostra os módulos (interface web, serviço de encomendas, base de dados) e as dependências. O diagrama de implantação (deployment) mostra os nós físicos ou virtuais (servidor web, servidor de base de dados, posto do cliente) e o que corre em cada um. Para a base de dados, o essencial é o script de criação (DDL), a versão do SGBD, a configuração e a estratégia de cópias de segurança.

Exemplo resolvido · Do estado à restrição

Situação. O diagrama de estados de uma encomenda tem: pendente para paga; paga para enviada; enviada para entregue; e pendente ou paga para cancelada.

Passo 1. Os estados são os valores permitidos: estado ENUM('pendente','paga','enviada','entregue','cancelada') NOT NULL DEFAULT 'pendente'.

Passo 2. O DEFAULT garante que toda a encomenda nasce em pendente, como o diagrama manda.

Passo 3. As transições não cabem no ENUM. Registam-se na aplicação, que só permite avançar para o estado seguinte válido, ou com um gatilho que recusa saltos proibidos.

Passo 4. Para auditar, acrescenta-se uma tabela EncomendaEstado(encomenda_id, estado, momento) que guarda o histórico de transições.

12. Normalização: dependências funcionais, anomalias e forma não normalizada

12.1 O que é normalizar

Normalizar é decompor tabelas mal estruturadas em tabelas mais pequenas e bem estruturadas, seguindo regras chamadas formas normais, para eliminar a redundância e as anomalias que dela resultam. Codd descreveu as primeiras formas normais na década de 1970. A normalização faz-se sobre um modelo lógico, a partir da forma não normalizada (UNF), e avança por etapas: 1FN, 2FN, 3FN e, se necessário, FNBC. Cada etapa só se aplica se a anterior estiver cumprida.

12.2 Anomalias

Numa tabela com redundância, os mesmos factos repetem-se e surgem três problemas.

12.3 Dependências funcionais

Uma dependência funcional X → Y (lê-se «X determina Y») significa que, em qualquer linha da tabela, o mesmo valor de X corresponde sempre ao mesmo valor de Y. O nº de cliente determina o nome do cliente: num_cliente → nome. As dependências vêm das regras do negócio, não dos dados que por acaso estão na tabela: uma instância pode desmentir uma dependência, mas nunca a prova. Vocabulário útil:

• Atributo primo é o que faz parte de alguma chave candidata; não primo é o que não faz parte de nenhuma.
• Dependência parcial: um atributo não primo depende de apenas uma parte de uma chave composta.
• Dependência transitiva: A → B e B → C (com B não chave), logo A → C por intermédio de B.
• Dependência trivial: Y está contido em X ({aluno, curso} → aluno), e não conta.

12.4 Forma não normalizada e notações

A forma não normalizada (UNF) é aquela em que há grupos repetitivos: um conjunto de atributos que se repete várias vezes na mesma linha. Representa-se de três formas equivalentes.

Notação de parênteses. O grupo repetitivo vai entre parênteses, com a chave (a negrito) à cabeça.

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

Notação tabular. Uma tabela em que cada célula do grupo repetitivo contém vários valores, ou em que as primeiras colunas se repetem.

Notação de esquema relacional. Já com tabelas separadas, chave primária a negrito e chave estrangeira assinalada com (FK):

EncomendaItem(num_enc (FK),cod_prod (FK), quantidade)

Diagrama de dependências. Desenham-se as setas entre atributos: num_enc → data, num_cliente; num_cliente → nome_cliente, cod_postal; cod_postal → localidade; cod_prod → designacao, preco; {num_enc, cod_prod} → quantidade.

12.5 Método geral

1. Escrever a tabela de partida (UNF) e identificar a chave.
2. Listar as dependências funcionais pelas regras do negócio.
3. 1FN: eliminar grupos repetitivos e valores não atómicos.
4. 2FN: retirar dependências parciais.
5. 3FN: retirar dependências transitivas.
6. FNBC, se ainda houver determinantes que não são chave.
7. Verificar: nenhuma informação se perdeu (a junção reconstitui o original) e cada facto está num só sítio.

13. 1FN, 2FN e 3FN passo a passo

13.1 Primeira forma normal (1FN)

Uma tabela está em 1FN se todos os atributos têm valores atómicos (um só valor por célula), não há grupos repetitivos e existe uma chave primária que identifica cada linha. Para corrigir, ou se passa o grupo repetitivo para linhas próprias (a chave passa a ser composta) ou se cria uma tabela nova.

13.2 Segunda forma normal (2FN)

Uma tabela está em 2FN se está em 1FN e todo o atributo não primo depende da chave inteira, não apenas de parte dela. A 2FN só pode ser violada quando a chave é composta; se todas as chaves candidatas têm uma só coluna, uma tabela em 1FN já está em 2FN.

13.3 Terceira forma normal (3FN)

Uma tabela está em 3FN se está em 2FN e nenhum atributo não primo depende de outro atributo não primo, isto é, não há dependências transitivas. Dito de outra maneira, e é a formulação mais precisa: para cada dependência X → A não trivial, ou X é uma superchave, ou A é um atributo primo. Frase para decorar: «cada atributo depende da chave, de toda a chave e de nada além da chave».

Exemplo resolvido · Normalizar uma encomenda, da UNF à 3FN

Dados de partida (códigos postais ilustrativos):

Dependências (regras do negócio): num_enc → data, num_cliente; num_cliente → nome_cliente, cod_postal; cod_postal → localidade; cod_prod → designacao, preco; {num_enc, cod_prod} → quantidade.

Passo 1 · UNF. Encomenda(num_enc, data, num_cliente, nome_cliente, cod_postal, localidade, (cod_prod, designacao, preco, quantidade)). Há um grupo repetitivo.

Passo 2 · 1FN. Cada produto passa a ter a sua linha e a chave passa a ser {num_enc, cod_prod}.

Já não há grupos repetitivos, mas repete-se muita coisa: a Marta aparece quatro vezes, a Caneta duas.

Passo 3 · 2FN. Procuram-se dependências parciais. data, num_cliente, nome_cliente, cod_postal e localidade dependem só de num_enc (parte da chave). designacao e preco dependem só de cod_prod (outra parte). Só quantidade depende da chave inteira. Decompõe-se em três tabelas:

• Encomenda(num_enc, data, num_cliente, nome_cliente, cod_postal, localidade)
• Produto(cod_prod, designacao, preco)
• EncomendaItem(num_enc (FK),cod_prod (FK), quantidade)

Passo 4 · 3FN. Em Encomenda há dependências transitivas: num_enc → num_cliente → nome_cliente, cod_postal e cod_postal → localidade. Decompõe-se:

• Cliente(num_cliente, nome_cliente, cod_postal (FK))
• CodigoPostal(cod_postal, localidade)
• Encomenda(num_enc, data, num_cliente (FK))

Resultado final em 3FN: Cliente, CodigoPostal, Encomenda, Produto e EncomendaItem. A Marta aparece uma vez, a localidade «Porto» está escrita uma vez por código postal e o preço da caneta tem um só sítio.

Passo 5 · Verificação. A junção EncomendaItem ⨝ Encomenda ⨝ Cliente ⨝ CodigoPostal ⨝ Produto reconstitui as cinco linhas da 1FN, sem acrescentar nem perder nenhuma. Uma observação de modelação: na prática, guarda-se também o preco_unit na linha, porque é o preço no momento da venda, um facto diferente do preço atual do produto; não é uma violação da 3FN, porque é um dado novo.

CREATE TABLE CodigoPostal (cod_postal CHAR(8) PRIMARY KEY, localidade VARCHAR(60) NOT NULL);
CREATE TABLE Cliente (
  num_cliente  INT PRIMARY KEY,
  nome_cliente VARCHAR(120) NOT NULL,
  cod_postal   CHAR(8),
  FOREIGN KEY (cod_postal) REFERENCES CodigoPostal(cod_postal)
);
CREATE TABLE Encomenda (
  num_enc     INT PRIMARY KEY,
  data        DATE NOT NULL,
  num_cliente INT NOT NULL,
  FOREIGN KEY (num_cliente) REFERENCES Cliente(num_cliente)
);
CREATE TABLE Produto (cod_prod CHAR(4) PRIMARY KEY, designacao VARCHAR(80) NOT NULL, preco DECIMAL(8,2) NOT NULL);
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)
);

Exemplo resolvido · Atributos multivalor e 1FN

Situação. Formando(id, nome, telefones, competencias), com uma linha 1, Marta, «912000111, 933000222», «SQL, HTML, Git».

Passo 1. telefones e competencias têm vários valores por célula: viola a 1FN.

Passo 2. Os dois são independentes: um formando tem vários telefones e várias competências, sem relação entre uns e outros. Se se pusessem os dois na mesma tabela auxiliar, haveria um produto cartesiano (2 telefones × 3 competências = 6 linhas, com informação artificial). Por isso, duas tabelas.

Passo 3. Telefone(formando_id, numero) com chave composta, e Competencia(formando_id, competencia). O Formando fica com (id, nome).

Passo 4. Verificação: SELECT numero FROM Telefone WHERE formando_id = 1; devolve os dois números sem se partir texto, e o telefone pode ter um índice ou uma restrição UNIQUE.

Exemplo resolvido · Dependência parcial com chave composta

Situação. Estagio(formando_id, empresa_id, nome_empresa, horas), chave {formando_id, empresa_id}. Regra: cada empresa tem um só nome.

Passo 1. empresa_id → nome_empresa: o atributo não primo nome_empresa depende só de parte da chave. Viola a 2FN.

Passo 2. Decompor: Empresa(empresa_id, nome_empresa) e Estagio(formando_id,empresa_id (FK), horas).

Passo 3. Efeito: renomear a empresa passa a ser um UPDATE de uma linha, em vez de um por cada formando que lá estagiou.

14. Forma normal de Boyce-Codd, formas avançadas e desnormalização

14.1 Forma normal de Boyce-Codd (FNBC / BCNF)

Uma tabela está em FNBC se, para toda a dependência funcional não trivial X → Y, X é uma superchave. É uma 3FN mais exigente: a 3FN deixa passar dependências cujo lado direito é um atributo primo; a FNBC não deixa. Quase todas as tabelas em 3FN estão também em FNBC; as que não estão têm, em geral, várias chaves candidatas compostas que se sobrepõem.

Exemplo resolvido · Em 3FN mas não em FNBC

Regras. Numa escola de línguas, cada professor ensina uma só língua. Um formando tem, em cada língua, um só professor. Aula(formando, lingua, professor).

Dependências: {formando, lingua} → professor e professor → lingua.

Passo 1 · Chaves candidatas. {formando, lingua} e {formando, professor}. Todos os três atributos são primos.

Passo 2 · 3FN? Sim: a dependência professor → lingua tem o lado direito primo (lingua faz parte de uma chave).

Passo 3 · FNBC? Não: professor determina lingua mas não é superchave. Consequência prática: se o professor Nuno mudar de língua, é preciso atualizar todas as suas linhas; e não se consegue registar um professor sem formandos.

Passo 4 · Decompor. Professor(professor, lingua) e Atribuicao(formando,professor (FK)). Ambas em FNBC.

Passo 5 · Atenção ao custo. A dependência {formando, lingua} → professor já não se verifica numa só tabela: nada impede, agora, que um formando tenha dois professores da mesma língua. A FNBC pode não preservar todas as dependências; para esse caso é preciso uma regra extra (um gatilho ou uma verificação na aplicação). É por isso que, em muitos projetos, se fica pela 3FN quando a FNBC obriga a perder uma regra.

14.2 Quarta e quinta formas normais

A 4FN trata as dependências multivalor: quando dois factos independentes, ambos com vários valores, estão na mesma tabela, como Pessoa(nome, lingua, desporto) onde as línguas e os desportos de uma pessoa nada têm a ver um com o outro. A tabela obriga a escrever todas as combinações; resolve-se com PessoaLingua e PessoaDesporto. A 5FN trata dependências de junção, raras na prática. Para este nível de formação, basta saber que existem e reconhecer o problema do produto cartesiano.

14.3 Desnormalização

Desnormalizar é, deliberadamente, voltar a juntar ou duplicar dados para ganhar velocidade de leitura. Justifica-se em sistemas de leitura intensiva (relatórios, painéis de indicadores, armazéns de dados), em valores calculados guardados para não recalcular (o total de uma encomenda), e em registos históricos que não devem mudar, como o nome e a morada do cliente numa fatura já emitida.

Regra: normalizar primeiro; desnormalizar com medição e motivo claro, e documentar onde há duplicação e como se mantém coerente.

Exemplo resolvido · Decidir se se desnormaliza

Situação. O relatório mensal de vendas demora 40 segundos porque junta cinco tabelas. O gestor pede para guardar o nome do cliente dentro da tabela de encomendas.

Passo 1 · Medir. Confirma-se com EXPLAIN onde está o tempo. Se o problema for a falta de um índice numa coluna muito filtrada (por exemplo Encomenda.data), criar o índice resolve sem desnormalizar.

Passo 2 · Alternativas. Criar uma vista, uma tabela de resumo preenchida de noite, ou um índice.

Passo 3 · Se mesmo assim se desnormalizar, escolhe-se a coluna, escreve-se um gatilho ou uma rotina que a mantém atualizada, e regista-se a decisão. A tabela original em 3FN mantém-se como fonte de verdade.

15. Integridade da informação, RGPD e normas aplicáveis

15.1 Integridade da informação

A integridade da informação consegue-se por camadas: o modelo (normalização, uma tabela para cada coisa, cada facto num só sítio), as restrições do SGBD (chaves, NOT NULL, CHECK, FOREIGN KEY), as transações, as permissões e as cópias de segurança. Regra prática: tudo o que o SGBD puder garantir, deve garantir ele, porque a aplicação pode ter erros ou ser contornada, e outras aplicações podem escrever na mesma base.

15.2 O RGPD e a modelação

O Regulamento Geral sobre a Proteção de Dados (Regulamento (UE) 2016/679, aplicável desde 25 de maio de 2018, em Portugal executado pela Lei n.º 58/2019 na redação em vigor, com a Comissão Nacional de Proteção de Dados como autoridade de controlo) aplica-se sempre que a base de dados guarda dados pessoais: qualquer informação relativa a uma pessoa singular identificada ou identificável (nome, NIF, e-mail, morada, fotografia, endereço IP). Há categorias especiais (saúde, origem étnica, opiniões políticas, dados biométricos) com proteção reforçada. Para quem modela, os princípios do artigo 5.º do regulamento traduzem-se em decisões muito concretas.

A estes princípios soma-se a obrigação de proteção de dados desde a conceção e por defeito (artigo 25.º): pensar a privacidade logo no desenho, com o mínimo de colunas visíveis por defeito e vistas que escondem os dados sensíveis.

Os direitos dos titulares (acesso, retificação, apagamento, limitação, portabilidade e oposição) também exigem modelo: é preciso saber encontrar todos os dados de uma pessoa (a normalização ajuda, porque cada facto está num só sítio) e conseguir apagá-los ou anonimizá-los. Aqui a escolha da ação referencial é decisiva: com ON DELETE RESTRICT, o registo de um cliente com faturas não se apaga; a solução é anonimizar os dados pessoais (substituir nome e contactos por valores neutros) mantendo a fatura, quando a lei obriga a conservá-la.

15.3 Outras normas e regulamentos

Aplicam-se também as normas da área: o SQL padronizado pela ISO/IEC 9075; a ISO 8601 para datas (AAAA-MM-DD); as convenções de nomes internas da equipa (singular ou plural, maiúsculas ou minúsculas, sem acentos nos identificadores), aplicadas de forma consistente em todo o modelo; e a documentação do modelo (dicionário de dados, diagramas e scripts versionados). Respeitar as regras definidas pela organização faz parte do desempenho esperado do técnico.

Exemplo resolvido · Rever um modelo à luz do RGPD

Situação. Uma tabela Inscrito(id, nome, nif, email, telefone, morada, data_nascimento, religiao, observacoes) para um clube de leitura.

Passo 1 · Necessidade. Para enviar convites bastam nome e e-mail. NIF, morada e data de nascimento não são necessários para esse fim.

Passo 2 · Categoria especial. religiao é uma categoria especial; sem fundamento claro, não deve existir.

Passo 3 · Campos livres. observacoes atrai dados sensíveis sem controlo; ou se retira, ou se define o que lá pode estar.

Passo 4 · Conservação. Acrescentar inscrito_em (data) e consentimento_em (data do consentimento), para que se possa apagar os inscritos inativos.

Passo 5 · Resultado. Inscrito(id, nome, email, inscrito_em, consentimento_em).

16. Caso prático integrado · loja online

16.1 Requisitos

Pretende-se modelar uma loja online com clientes (nome, e-mail, telefone, morada), produtos (descrição, categoria, preço, stock) e encomendas que incluem vários produtos em quantidades variáveis.

16.2 Modelo conceptual

Entidades: Cliente, Categoria, Produto, Encomenda, EncomendaItem. Relações: Categoria 1:N Produto; Cliente 1:N Encomenda; Encomenda 1:N EncomendaItem; Produto 1:N EncomendaItem. O N:M entre Encomenda e Produto resolve-se pela associativa EncomendaItem, que guarda a quantidade e o preço unitário.

Categoria ||--o{ Produto : classifica
Cliente   ||--o{ Encomenda : faz
Encomenda ||--|{ EncomendaItem : contem
Produto   ||--o{ EncomendaItem : aparece_em

16.3 Modelo físico

CREATE TABLE Cliente (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  nome     VARCHAR(150) NOT NULL,
  email    VARCHAR(150) UNIQUE NOT NULL,
  telefone VARCHAR(20),
  morada   TEXT
);

CREATE TABLE Categoria (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(80) UNIQUE NOT NULL
);

CREATE TABLE Produto (
  id           INT PRIMARY KEY AUTO_INCREMENT,
  nome         VARCHAR(150) NOT NULL,
  descricao    TEXT,
  preco        DECIMAL(10,2) NOT NULL CHECK (preco >= 0),
  stock        INT NOT NULL DEFAULT 0,
  categoria_id INT,
  FOREIGN KEY (categoria_id) REFERENCES Categoria(id)
);

CREATE TABLE Encomenda (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  data       DATETIME DEFAULT CURRENT_TIMESTAMP,
  cliente_id INT NOT NULL,
  estado     ENUM('pendente','paga','enviada','entregue','cancelada') DEFAULT 'pendente',
  FOREIGN KEY (cliente_id) REFERENCES Cliente(id)
);

CREATE TABLE EncomendaItem (
  encomenda_id INT,
  produto_id   INT,
  quantidade   INT NOT NULL CHECK (quantidade > 0),
  preco_unit   DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (encomenda_id, produto_id),
  FOREIGN KEY (encomenda_id) REFERENCES Encomenda(id) ON DELETE CASCADE,
  FOREIGN KEY (produto_id)   REFERENCES Produto(id)   ON DELETE RESTRICT
);

16.4 Decisões de modelação

• preco_unit na linha regista o preço no momento da compra: o preço do produto pode mudar e as encomendas antigas não podem mudar com ele.
• O ENUM do estado restringe os valores permitidos; se a lista crescer muito, passa-se a uma tabela de estados.
• CASCADE nos itens (os itens só existem com a encomenda) e RESTRICT no produto (não se apaga um produto que consta de encomendas históricas).
• A chave composta (encomenda_id, produto_id) impede o mesmo produto duas vezes na mesma encomenda; se o negócio o permitir, usa-se um id próprio.
• O modelo está em 3FN: cada atributo depende da chave da sua tabela, e a morada, num texto único, é uma simplificação que um projeto a sério decomporia.

16.5 Consultas de validação

-- Total de cada encomenda
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;

-- Produtos com stock baixo, por categoria
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;

Como se lê a primeira consulta. JOIN EncomendaItem i ON i.encomenda_id = e.id junta cada encomenda aos seus itens, emparelhando a chave estrangeira com a chave primária que ela referencia (e e i são apelidos das tabelas). GROUP BY e.id forma um grupo por encomenda e SUM(...) soma os itens de cada grupo. Na segunda, WHERE filtra as linhas e ORDER BY ordena o resultado, que sem ele não tem ordem garantida (capítulo 4). Um LEFT JOIN mantém os produtos sem categoria, que um JOIN simples deixaria de fora.

Se a pergunta do negócio se responde juntando tabelas pelas chaves estrangeiras, as ligações do modelo estão bem desenhadas.

Apêndice · Cheatsheet de chaves

-- PK simples
id INT PRIMARY KEY AUTO_INCREMENT
-- PK composta
PRIMARY KEY (col1, col2)
-- FK separada, com ações (em MySQL é a única forma que cria a chave)
FOREIGN KEY (cliente_id) REFERENCES Cliente(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Em MySQL, a forma abreviada junto da coluna (cliente_id INT REFERENCES Cliente(id)) é ignorada e não cria a chave estrangeira; em PostgreSQL e SQLite funciona, mas a forma separada serve em todos.

Diferenças para o PostgreSQL: a coluna sequencial escreve-se id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY em vez de AUTO_INCREMENT; DATETIME passa a TIMESTAMP; o ENUM exige primeiro um tipo próprio (CREATE TYPE estado_encomenda AS ENUM (...)) ou, em alternativa, um CHECK.

Erros comuns

• Confundir base de dados com SGBD: a base é o conjunto de dados, o SGBD é o programa que a gere.
• Guardar vários valores numa célula (telefones separados por vírgulas), o que viola a 1FN.
• Resolver um N:M com colunas repetidas (disciplina1, disciplina2) em vez de uma tabela de associação.
• Pôr a chave estrangeira do lado errado numa relação 1:N: a FK fica sempre no lado «muitos».
• Usar o nome, o e-mail ou o NIF como chave primária: mudam, repetem-se ou são dados pessoais.
• Usar FLOAT ou DOUBLE para dinheiro; usa-se DECIMAL.
• Guardar a referência multibanco ou o código postal como número e perder os zeros à esquerda.
• Esquecer o NOT NULL em colunas obrigatórias, ou testar NULL com = NULL em vez de IS NULL.
• Criar tabelas sem chaves estrangeiras, deixando a integridade ao cuidado da aplicação.
• Escolher CASCADE por hábito: apagar um cliente e perder todo o histórico de faturas.
• Normalizar a olho, sem listar as dependências funcionais antes de decompor.
• Deduzir dependências a partir de uma instância: os dados de exemplo podem desmentir uma dependência, nunca a provam.
• Decompor sem verificar se a junção reconstitui o original (perda de informação).
• Guardar atributos derivados (idade, total) que ficam desatualizados.
• Desnormalizar sem medir nem documentar.
• Correr UPDATE ou DELETE sem WHERE.
• Guardar palavras-passe em texto simples ou recolher dados pessoais que não são necessários.
• Confundir cardinalidade de relação (número de linhas) com cardinalidade de associação (1:1, 1:N, N:M).

Glossário

• Anomalia: problema causado pela redundância (de inserção, de atualização ou de eliminação).
• ACID: atomicidade, consistência, isolamento e durabilidade, propriedades das transações.
• Atributo: propriedade de uma entidade; numa tabela, uma coluna.
• Atributo derivado: atributo que se calcula a partir de outros e não se guarda.
• Atributo multivalor: atributo com vários valores por ocorrência; origina uma tabela própria.
• Base de dados: conjunto organizado e persistente de dados relacionados, partilhado por vários utilizadores.
• Cardinalidade: número de ocorrências que se podem associar numa relação (1:1, 1:N, N:M); numa tabela, número de linhas.
• Chave candidata: conjunto mínimo de colunas que identifica cada linha.
• Chave estrangeira (FK): coluna cujos valores existem na chave primária de outra tabela.
• Chave primária (PK): chave candidata escolhida para identificar os registos; única e nunca nula.
• Dependência funcional: relação X → Y em que cada valor de X determina um só valor de Y.
• Dependência transitiva: A → B e B → C com B não chave.
• DDL, DML, DQL, DCL, TCL: famílias de comandos SQL para definir, manipular, consultar, controlar acessos e transações.
• Domínio: conjunto de valores válidos de um atributo.
• Entidade: classe de coisas sobre as quais se guarda informação.
• Entidade associativa: relação N:M promovida a entidade, por ter atributos próprios.
• Entidade fraca: entidade que só se identifica com a chave de outra.
• ER: modelo entidade-relação, proposto por Chen em 1976.
• FNBC (BCNF): forma normal em que todo o determinante é uma superchave.
• Forma normal (1FN, 2FN, 3FN): nível de qualidade da estrutura de uma tabela quanto à redundância.
• Índice: estrutura auxiliar que acelera pesquisas.
• Integridade referencial: garantia de que toda a FK aponta para um registo existente.
• NULL: ausência de valor; não é zero nem texto vazio.
• Pé-de-galinha: notação de diagramas ER em que os símbolos das pontas indicam a cardinalidade.
• Relação (modelo relacional): tabela; conjunto de tuplos com os mesmos atributos.
• RGPD: Regulamento Geral sobre a Proteção de Dados.
• SGBD: Sistema de Gestão de Bases de Dados.
• SQL: linguagem normalizada para definir e manipular bases de dados relacionais.
• Transação: conjunto de operações tratado como um todo.
• Tuplo: linha de uma relação.

Síntese

Os dados guardam-se em bases de dados geridas por um SGBD, cuja arquitetura de três níveis (externo, conceptual e interno) garante a independência dos dados. O modelo relacional organiza tudo em tabelas de registos e campos, ligadas por chaves (primária, candidata, alternativa e estrangeira), e protegidas pelas regras de integridade de entidade, referencial, de domínio e definida pelo utilizador, com ações CASCADE, SET NULL e RESTRICT. Modela-se do conceptual (entidades, atributos simples, compostos, multivalor e derivados, relações 1:1, 1:N e N:M, em notação de Chen ou pé-de-galinha) ao lógico (conversão em tabelas) e ao físico (tipos, índices e DDL), e completam-se com diagramas de fronteiras, comportamento e implementação. A normalização parte da forma não normalizada, separa grupos repetitivos (1FN), dependências parciais (2FN) e transitivas (3FN) e, quando necessário, aplica a FNBC, sempre verificando que a junção reconstitui o original; desnormaliza-se só com motivo medido. Tudo respeita a integridade da informação, o RGPD (licitude, finalidade, minimização, exatidão, conservação, segurança e responsabilidade) e as normas e regras da organização.

Recursos

• dbdiagram.io: diagramas online gratuitos.
• Use The Index, Luke (use-the-index-luke.com): explica índices.
• SQLBolt: tutoriais interativos de SQL.
• Livro: Database Design for Mere Mortals, de Michael J. Hernandez.

Exercícios resolvidos

Exercício resolvido 1 · Cardinalidades e chaves

Enunciado. Numa associação desportiva, cada atleta (nome, data de nascimento) pertence a uma só equipa; uma equipa tem vários atletas; cada treinador orienta várias equipas e cada equipa pode ter mais do que um treinador. Define as relações e escreve o esquema relacional.

Resolução. Atleta e Equipa: 1:N, logo Atleta.equipa_id (FK, NOT NULL). Treinador e Equipa: N:M, logo tabela Orientacao(treinador_id, equipa_id) com PK composta. Esquema: Equipa(id, nome); Atleta(id, nome, data_nascimento, equipa_id (FK)); Treinador(id, nome); Orientacao(treinador_id (FK),equipa_id (FK)).

Exercício resolvido 2 · Normalização

Enunciado. Reserva(num_reserva, data, id_hospede, nome_hospede, num_quarto, tipo_quarto, preco_noite, noites) com chave num_reserva, e as regras id_hospede → nome_hospede, num_quarto → tipo_quarto, tipo_quarto → preco_noite. Leva a 3FN.

Resolução. A chave é simples, logo está em 2FN (não há dependências parciais). Em 3FN não está: num_reserva → id_hospede → nome_hospede e num_reserva → num_quarto → tipo_quarto → preco_noite são transitivas. Decompõe-se em Hospede(id_hospede, nome_hospede), TipoQuarto(tipo_quarto, preco_noite), Quarto(num_quarto, tipo_quarto (FK)) e Reserva(num_reserva, data, id_hospede (FK), num_quarto (FK), noites). Verificação: a junção das quatro devolve a tabela original.

Exercício resolvido 3 · Integridade referencial

Enunciado. Com Armazem(id, nome) e Palete(id, armazem_id, descricao) onde armazem_id referencia Armazem(id) com ON DELETE RESTRICT, o que acontece ao correr DELETE FROM Armazem WHERE id = 5; se o armazém tiver 3 paletes?

Resolução. O SGBD recusa a operação com um erro de chave estrangeira, porque existem filhos. Para apagar o armazém, mudam-se primeiro as 3 paletes para outro armazém (ou apagam-se), ou altera-se a regra para SET NULL ou CASCADE, se o negócio o aceitar. Apagar paletes com o armazém seria raramente aceitável: as paletes têm mercadoria.