Projetar uma Base de Dados Veterinaria

Modela, normaliza e consulta uma base de dados com ER, SQL e algebra relacional.

Introduction

30 Second Summary

Quando levas o teu animal ao veterinario, ha informacao em todo o lado. Nomes de animais, contactos de proprietarios, historico de consultas, prescricoes de medicamentos. Sem uma estrutura adequada, todos esses dados tornam-se caos.

Neste projeto, vais desenhar uma base de dados completa para uma clinica veterinaria chamada "PetCare", percorrendo toda a metodologia de design desde o Modelo Entidade-Relacionamento ate SQL e algebra relacional.

What You'll Build

Um dossier completo de estudo que cobre um cenario realista de uma clinica veterinaria e exercita os 4 tipos de questoes do teu exame de Bases de Dados.

By the end of this project, you'll have:

  • Um diagrama ER completo com entidades, atributos, relacoes, cardinalidades e restricoes de participacao.
  • Um exercicio de normalizacao resolvido passo a passo (UNF a 1FN a 2FN a 3FN) e o mapeamento para o modelo relacional.
  • Um conjunto de queries SQL resolvidas (DDL e DML) com as respetivas traducoes para algebra relacional.
  • Secret Mission: um banco de perguntas teoricas de desenvolvimento com respostas modelo no formato tipico de exame.

Preciso de pre-requisitos?

Este projeto assume que acompanhaste as Partes 1-4 do livro "Database Systems: A Practical Approach to Design, Implementation, and Management" (6th Edition) de Connolly e Begg. Nao precisas de software. O exame e teorico, por isso vais trabalhar com papel e caneta (ou um editor de texto simples).

Identificar Entidades, Atributos e Relacoes

O primeiro passo no design de qualquer base de dados e analisar os requisitos do sistema. A partir de uma descricao em linguagem natural, precisas de identificar as "coisas" sobre as quais o sistema guarda informacao.

Neste passo, vais ler o cenario completo da clinica PetCare e extrair as entidades, os seus atributos e as relacoes entre elas. Este e o processo de modelacao conceptual descrito nos Capitulos 12 e 16 do livro.

In this step, get ready to:
  • Ler e analisar o cenario da clinica PetCare.
  • Identificar todas as entidades com os seus atributos e chaves primarias.
  • Definir as relacoes entre entidades com cardinalidades e restricoes de participacao.
Ler e analisar o cenario

O Modelo Entidade-Relacionamento parte sempre de um cenario descrito em linguagem natural. A tua tarefa e ler o texto com atencao e sublinhar os substantivos (potenciais entidades) e os verbos (potenciais relacoes).

Cenario: Clinica Veterinaria PetCare

A clinica veterinaria PetCare precisa de um sistema para gerir as suas operacoes. A clinica tem varios veterinarios, cada um com um numero de ordem profissional, nome, especialidade e telefone.

Os proprietarios trazem os seus animais a clinica. De cada proprietario regista-se o nome, telefone, email e morada. Cada proprietario pode ter varios animais, mas cada animal pertence a um unico proprietario. De cada animal regista-se o nome, especie, raca, data de nascimento e peso.

Quando um animal e levado a clinica, e agendada uma consulta com um veterinario. Cada consulta tem uma data, hora, motivo e, apos realizacao, um diagnostico. Durante uma consulta podem ser realizados varios tratamentos. Cada tratamento tem uma descricao e um custo.

Um tratamento pode envolver a prescricao de um ou mais medicamentos, e o mesmo medicamento pode ser prescrito em diferentes tratamentos. Para cada prescricao regista-se a dosagem e a duracao do tratamento.

  • Le o cenario acima com atencao.
  • Sublinha (ou anota) todos os substantivos que representam "coisas" sobre as quais o sistema guarda dados.
  • Para cada substantivo identificado, anota os dados que o cenario menciona (estes serao os atributos).
  • Escolhe um identificador unico para cada entidade. Este sera o atributo-chave (chave primaria).

Como distinguir entidades de atributos

Uma entidade e algo que tem existencia propria e sobre o qual guardamos multiplas informacoes. Um atributo e uma propriedade singular de uma entidade.

Se precisas de guardar varias informacoes sobre algo (nome, telefone, morada), provavelmente e uma entidade. Se e apenas um valor unico associado a outra coisa, e um atributo.

Tenta identificar todas as entidades e atributos antes de verificar a resposta.

✔️ I'll try it first

Otimo. Anota as tuas entidades e atributos no papel ou editor de texto. Quando terminares, abre o segundo separador para comparar com a resposta modelo.

ⓧ Let me check my work

Aqui esta a lista completa de entidades com os seus atributos. O atributo sublinhado (marcado com PK) e a chave primaria.

Proprietario (idProprietario PK, nome, telefone, email, morada)

Animal (idAnimal PK, nome, especie, raca, dataNascimento, peso)

Veterinario (idVeterinario PK, nome, especialidade, telefone, nrOrdem)

Consulta (idConsulta PK, data, hora, motivo, diagnostico)

Tratamento (idTratamento PK, descricao, custo)

Medicamento (idMedicamento PK, nome, tipo, fabricante)

Prescricao (dosagem, duracao) - entidade associativa entre Tratamento e Medicamento

Porque e que Prescricao e uma entidade associativa?

O cenario diz que "um tratamento pode envolver varios medicamentos" e "o mesmo medicamento pode ser prescrito em diferentes tratamentos". Isto e uma relacao M:N.

Como a prescricao tem atributos proprios (dosagem e duracao), torna-se uma entidade associativa. A sua chave primaria sera composta pelas chaves de Tratamento e Medicamento.

Identificar relacoes e cardinalidades

Agora que tens as entidades identificadas, o proximo passo e definir como elas se ligam entre si. Para cada relacao, precisas de determinar tres coisas: o nome da relacao, a cardinalidade (1:1, 1:N ou M:N) e as restricoes de participacao (total ou parcial).

O que sao restricoes de participacao?

Participacao total significa que toda instancia da entidade deve participar na relacao. Participacao parcial significa que nem todas as instancias precisam de participar.

Exemplo: o cenario diz que "cada animal pertence a um unico proprietario". Isto significa que Animal tem participacao total na relacao POSSUI. Mas um proprietario pode existir sem ter animais registados (participacao parcial).

  • Para cada par de entidades que se relacionam, anota o nome da relacao (um verbo que descreve a ligacao).
  • Determina se a relacao e 1:1, 1:N ou M:N, lendo com atencao palavras como "varios", "um unico", "pode ter".
  • Para cada lado da relacao, decide se a participacao e total (obrigatoria) ou parcial (opcional).

Tenta completar todas as relacoes antes de verificar a resposta.

✔️ I'll try it first

Anota as 5 relacoes no teu papel ou editor. Inclui o nome, a cardinalidade e a participacao de cada lado. Depois verifica no segundo separador.

ⓧ Let me check my work

Aqui estao as 5 relacoes com cardinalidades e restricoes de participacao.

1. Proprietario POSSUI Animal
   Cardinalidade: 1:N
   Um proprietario pode ter varios animais, cada animal pertence a exatamente um proprietario.
   Participacao: total do lado de Animal, parcial do lado de Proprietario.

2. Animal TEM Consulta
   Cardinalidade: 1:N
   Um animal pode ter varias consultas, cada consulta refere-se a um unico animal.
   Participacao: parcial do lado de Animal, total do lado de Consulta.

3. Veterinario REALIZA Consulta
   Cardinalidade: 1:N
   Um veterinario pode realizar varias consultas, cada consulta e realizada por um unico veterinario.
   Participacao: parcial do lado de Veterinario, total do lado de Consulta.

4. Consulta INCLUI Tratamento
   Cardinalidade: 1:N
   Uma consulta pode incluir varios tratamentos, cada tratamento pertence a uma unica consulta.
   Participacao: parcial do lado de Consulta, total do lado de Tratamento.

5. Tratamento PRESCREVE Medicamento
   Cardinalidade: M:N
   Um tratamento pode prescrever varios medicamentos, um medicamento pode ser prescrito em varios tratamentos.
   Resolvida pela entidade associativa Prescricao.

Como determinar participacao total vs parcial?

Pergunta-te: "Pode existir uma instancia desta entidade sem participar nesta relacao?"

Consulta tem participacao total no lado de Animal porque nao faz sentido ter uma consulta sem animal. Mas Animal tem participacao parcial porque um animal pode estar registado sem nunca ter ido a uma consulta.

Validar a tua analise

Antes de avancar para o diagrama ER, confirma que nao falta nada na tua analise. Esta checklist e o mesmo processo de validacao que deves aplicar em contexto de exame.

  • Verifica que cada entidade tem pelo menos um atributo-chave (PK) identificado.
  • Confirma que nenhuma relacao ficou sem cardinalidade definida.
  • Verifica que todas as relacoes M:N foram resolvidas com uma entidade associativa.
  • Confirma que cada restricao de participacao tem justificacao no texto do cenario.

Dica para o exame

Num exame, justifica sempre as tuas escolhas de cardinalidade citando frases do enunciado. "Cada animal pertence a um unico proprietario" justifica diretamente o 1 do lado Proprietario na relacao POSSUI.

Tens agora todas as pecas identificadas: 7 entidades, os seus atributos, e 5 relacoes com cardinalidades claras. No proximo passo, vais transformar tudo isto num diagrama ER visual seguindo a notacao do livro.

Desenhar o Diagrama ER e Mapear para o Modelo Relacional

No passo anterior, identificaste todas as entidades, atributos e relacoes do cenario PetCare.

Agora vais transformar essa analise num diagrama Entidade-Relacionamento visual, seguindo a notacao do livro de Connolly e Begg. Depois, vais mapear esse diagrama para o modelo relacional (tabelas com chaves primarias e estrangeiras).

In this step, get ready to:
  • Desenhar o diagrama ER completo com a notacao do livro.
  • Verificar o diagrama com uma checklist de qualidade.
  • Mapear o modelo ER para o esquema relacional aplicando regras de conversao.
Desenhar o diagrama ER completo

O diagrama ER e a representacao grafica do modelo conceptual da base de dados. Cada elemento tem um simbolo especifico na notacao do livro.

Que notacao usar?

O livro de Connolly e Begg usa a seguinte notacao grafica para diagramas ER:

  • Retangulos para entidades.
  • Ovais para atributos (oval sublinhada para chave primaria).
  • Losangos para relacoes entre entidades.
  • Linhas com 1 ou * para indicar cardinalidade.
  • Linha dupla para participacao total. Linha simples para participacao parcial.
  • Comeca por desenhar as 6 entidades como retangulos, espalhadas pela folha com espaco entre elas: Proprietario, Animal, Veterinario, Consulta, Tratamento e Medicamento.
  • Adiciona os atributos de cada entidade como ovais ligadas ao retangulo correspondente.
  • Sublinha a oval de cada atributo-chave (por exemplo, idProprietario, idAnimal, etc.).
  • Desenha um losango para cada relacao entre entidades: POSSUI, TEM, REALIZA, INCLUI e PRESCREVE.
  • Liga cada losango as entidades participantes com linhas.
  • Escreve a cardinalidade junto a cada entidade: 1 no lado "um" e * no lado "muitos".
  • Usa linha dupla para participacao total e linha simples para participacao parcial (consulta as tuas notas do passo anterior).
  • Para a relacao M:N entre Tratamento e Medicamento, desenha Prescricao como entidade associativa (retangulo dentro de losango) com os seus atributos proprios: dosagem e duracao.

Organiza o espaco no papel

Coloca Proprietario e Animal no topo, Consulta e Veterinario ao centro, e Tratamento, Prescricao e Medicamento na parte inferior. Esta disposicao segue o fluxo logico do cenario e evita cruzamento de linhas.

Verificar o diagrama com uma checklist

Antes de avancar, verifica que o teu diagrama esta completo e correto. Erros no diagrama ER propagam-se para o modelo relacional.

  • Percorre esta checklist e marca cada ponto que o teu diagrama satisfaz:
  • Cada entidade tem pelo menos um atributo-chave (sublinhado).
  • A relacao M:N (Tratamento-Medicamento) foi resolvida com a entidade associativa Prescricao.
  • As cardinalidades estao marcadas em ambos os lados de cada relacao.
  • As restricoes de participacao (total/parcial) estao representadas com linha dupla ou simples.
  • Nao existem atributos multivalor por resolver.

Falha algum ponto da checklist?

Se te esqueceste de sublinhar as chaves primarias, volta a cada entidade e verifica que o atributo identificador esta marcado.

Se as participacoes estao todas iguais, relembra: participacao total (linha dupla) significa que toda a instancia desse lado participa obrigatoriamente na relacao. Por exemplo, cada Animal pertence obrigatoriamente a um Proprietario (total do lado Animal).

Help me understand participation constraints in ER diagrams

Mapear o diagrama ER para o esquema relacional

O mapeamento converte o modelo conceptual (diagrama ER) para o modelo logico (tabelas relacionais). Este passo segue regras mecanicas que o livro define no Capitulo 17.

Quais sao as regras de mapeamento?

  • Cada entidade torna-se uma tabela (relacao) com os mesmos atributos.
  • Relacoes 1:N: a chave primaria do lado "1" e adicionada como chave estrangeira no lado "N".
  • Relacoes M:N: cria-se uma nova tabela com as chaves primarias de ambas as entidades como chave primaria composta.
  • Aplica as regras ao teu diagrama, escrevendo o esquema relacional com a seguinte notacao: NomeTabela(atributo1 PK, atributo2, ..., atributoFK FK->TabelaReferenciada).
  • Comeca pelas entidades independentes (sem chaves estrangeiras): Proprietario, Veterinario e Medicamento.
  • Depois mapeia as entidades dependentes, adicionando a chave estrangeira: Animal recebe idProprietario como FK.
  • Consulta recebe duas chaves estrangeiras: idAnimal e idVeterinario.
  • Tratamento recebe idConsulta como FK.
  • Para Prescricao (relacao M:N), cria uma tabela com chave primaria composta: (idTratamento, idMedicamento) mais os atributos proprios dosagem e duracao.

O resultado final do teu mapeamento deve ser:

Proprietario(idProprietario PK, nome, telefone, email, morada)

Animal(idAnimal PK, nome, especie, raca, dataNascimento, peso, idProprietario FK->Proprietario)

Veterinario(idVeterinario PK, nome, especialidade, telefone, nrOrdem)

Consulta(idConsulta PK, data, hora, motivo, diagnostico, idAnimal FK->Animal, idVeterinario FK->Veterinario)

Tratamento(idTratamento PK, descricao, custo, idConsulta FK->Consulta)

Medicamento(idMedicamento PK, nome, tipo, fabricante)

Prescricao(idTratamento FK->Tratamento, idMedicamento FK->Medicamento, dosagem, duracao)
-- Chave primaria composta: (idTratamento, idMedicamento)

Porque e que as FKs ficam no lado N?

Numa relacao 1:N, cada instancia do lado "N" esta associada a exatamente uma instancia do lado "1". Guardar a FK no lado "N" evita valores NULL e garante que cada registo aponta para o seu "pai" unico.

Por exemplo, cada Animal tem exatamente um Proprietario, por isso idProprietario fica na tabela Animal.

✔️ Awesome, I've got everything!

Otimo. Confirma que o teu esquema tem exatamente 7 tabelas e que a tabela Prescricao tem chave primaria composta.

ⓧ I'd like to double check the full schema

Compara o teu esquema com a versao completa abaixo:

Proprietario(idProprietario PK, nome, telefone, email, morada)

Animal(idAnimal PK, nome, especie, raca, dataNascimento, peso, idProprietario FK->Proprietario)

Veterinario(idVeterinario PK, nome, especialidade, telefone, nrOrdem)

Consulta(idConsulta PK, data, hora, motivo, diagnostico, idAnimal FK->Animal, idVeterinario FK->Veterinario)

Tratamento(idTratamento PK, descricao, custo, idConsulta FK->Consulta)

Medicamento(idMedicamento PK, nome, tipo, fabricante)

Prescricao(idTratamento FK->Tratamento, idMedicamento FK->Medicamento, dosagem, duracao)
-- Chave primaria composta: (idTratamento, idMedicamento)

Porque e que isto importa no exame?

No exame, e comum pedirem o mapeamento ER para relacional como pergunta independente. Demonstrar que sabes onde colocar as chaves estrangeiras e como resolver relacoes M:N e o que distingue uma resposta completa.

Tens agora o modelo conceptual (diagrama ER) e o modelo logico (esquema relacional) completos. No proximo passo, vais testar a qualidade deste design atraves de um exercicio de normalizacao.

Normalizar de UNF ate 3FN

No passo anterior, mapeaste o diagrama ER para um esquema relacional usando regras de conversao top-down.

Agora vais percorrer o caminho inverso: partir de uma relacao nao normalizada (com dados reais da clinica) e aplica a normalizacao passo a passo ate chegares a 3FN. No final, vais confirmar que ambos os caminhos (top-down via ER e bottom-up via normalizacao) convergem para o mesmo design.

In this step, get ready to:
  • Identificar grupos repetitivos numa relacao UNF e converter para 1FN.
  • Remover dependencias parciais para atingir 2FN.
  • Remover dependencias transitivas para atingir 3FN e validar contra o esquema ER.
Identificar a relacao UNF e converter para 1FN

Imagina que a clinica PetCare guardava toda a informacao de consultas numa unica folha de calculo enorme. Cada linha tenta conter tudo sobre uma consulta, incluindo listas de tratamentos e medicamentos dentro de celulas.

Este e o ponto de partida: uma relacao nao normalizada (UNF) com grupos repetitivos. Escreve a seguinte relacao nas tuas notas:

ConsultaCompleta(
  idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca,
  nomeProprietario, telProprietario, emailProprietario,
  nomeVeterinario, especialidade, nrOrdem,
  {idTratamento, descricaoTratamento, custoTratamento,
    {idMedicamento, nomeMedicamento, dosagem, duracao}
  }
)

O que significam as chavetas {}?

As chavetas indicam grupos repetitivos. Uma consulta pode ter varios tratamentos, e cada tratamento pode prescrever varios medicamentos.

Na notacao do livro de Connolly e Begg, estes grupos violam a 1FN porque cada celula deveria conter apenas um valor atomico.

  • Identifica os dois niveis de grupos repetitivos na relacao.
  • Elimina cada grupo repetitivo criando uma nova relacao com a chave primaria da relacao original mais os atributos do grupo.
  • Escreve o resultado nas tuas notas antes de verificares a solucao abaixo.

Tenta resolver por ti antes de verificares a resposta.

✔️ I'll try it first

Otimo. Lembra-te: para cada grupo repetitivo (cada par de {}), cria uma nova relacao. A chave da nova relacao inclui a chave primaria da relacao original mais a chave do grupo.

ⓧ Let me check my work

Aqui esta o resultado em 1FN. Compara com o que escreveste:

Consulta1FN(
  idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca,
  nomeProprietario, telProprietario, emailProprietario,
  nomeVeterinario, especialidade, nrOrdem
)

Tratamento1FN(
  idConsulta, idTratamento,
  descricaoTratamento, custoTratamento
)

Prescricao1FN(
  idConsulta, idTratamento, idMedicamento,
  nomeMedicamento, dosagem, duracao
)

Como chegamos a este resultado?

  • O primeiro grupo repetitivo {idTratamento, descricaoTratamento, custoTratamento, ...} foi extraido para Tratamento1FN. A sua chave e {idConsulta, idTratamento}.
  • O grupo repetitivo aninhado {idMedicamento, nomeMedicamento, dosagem, duracao} foi extraido para Prescricao1FN. A sua chave e {idConsulta, idTratamento, idMedicamento}.
  • Consulta1FN fica com todos os atributos atomicos e chave simples {idConsulta}.

Nao tens a certeza se os teus grupos estao corretos?

  • Verifica se cada celula da tua relacao contem apenas um valor. Se uma celula pode ter "varios tratamentos", ainda tens um grupo repetitivo.
  • Confirma que as chaves compostas das novas relacoes incluem sempre a PK da relacao original (idConsulta) mais o identificador do grupo.

Help me understand how to identify repeating groups in UNF

Remover dependencias parciais para atingir 2FN

Uma relacao esta em 2FN quando esta em 1FN e nenhum atributo nao-chave depende apenas de parte da chave composta. As relacoes com chave simples (como Consulta1FN) ja estao automaticamente em 2FN.

  • Analisa Tratamento1FN (chave: {idConsulta, idTratamento}). Identifica que atributos dependem apenas de idTratamento.
  • Analisa Prescricao1FN (chave: {idConsulta, idTratamento, idMedicamento}). Identifica dependencias parciais.
  • Para cada dependencia funcional parcial, cria uma nova relacao com o determinante como chave.

Resolve o exercicio antes de verificares a resposta modelo.

✔️ I'll try it first

Boa. Pergunta-te para cada atributo nao-chave: depende de TODA a chave composta ou apenas de uma parte? Se depende so de parte, tens uma dependencia parcial que precisa de ser resolvida.

ⓧ Let me check my work

Aqui esta a analise completa e o resultado em 2FN:

-- Analise de Tratamento1FN (Chave: {idConsulta, idTratamento})
-- descricaoTratamento depende apenas de idTratamento (parcial)
-- custoTratamento depende apenas de idTratamento (parcial)

-- Analise de Prescricao1FN (Chave: {idConsulta, idTratamento, idMedicamento})
-- nomeMedicamento depende apenas de idMedicamento (parcial)
-- dosagem e duracao dependem de {idTratamento, idMedicamento} (parcial)

-- Resultado 2FN:

Consulta2FN(
  idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca,
  nomeProprietario, telProprietario, emailProprietario,
  nomeVeterinario, especialidade, nrOrdem
)

ConsultaTratamento(idConsulta, idTratamento)

Tratamento2FN(idTratamento, descricaoTratamento, custoTratamento)

Prescricao2FN(idTratamento, idMedicamento, dosagem, duracao)

Medicamento2FN(idMedicamento, nomeMedicamento)

Porque aparece ConsultaTratamento?

Quando removes todos os atributos nao-chave de Tratamento1FN (porque todos tinham dependencia parcial), sobra apenas a chave composta {idConsulta, idTratamento}. Esta relacao mantem o registo de qual tratamento pertence a qual consulta.

Porque e que Consulta2FN nao muda?

Consulta1FN tem chave simples (idConsulta). Uma relacao com chave simples nunca pode ter dependencias parciais, porque nao ha "parte" da chave de onde depender. Passa automaticamente para 2FN sem alteracoes.

Remover dependencias transitivas para atingir 3FN

Uma relacao esta em 3FN quando esta em 2FN e nenhum atributo nao-chave depende transitivamente de outro atributo nao-chave. Por outras palavras, todos os atributos dependem diretamente da chave primaria e de nada mais.

  • Foca-te em Consulta2FN (a unica relacao grande restante). Pergunta: existem grupos de atributos que descrevem uma entidade propria?
  • Identifica que nomeAnimal, especie, raca descrevem um Animal (dependem de um idAnimal implicito, nao de idConsulta).
  • Aplica o mesmo raciocinio aos atributos do Proprietario e do Veterinario.
  • Cria relacoes separadas para cada grupo transitivo e substitui os atributos por chaves estrangeiras em Consulta.

Resolve o exercicio e depois compara com a solucao.

✔️ I'll try it first

Para cada grupo de atributos que descreve uma entidade distinta, cria uma nova relacao com um identificador como chave. Substitui esses atributos na relacao original pela nova chave estrangeira.

ⓧ Let me check my work

Aqui esta o resultado completo em 3FN:

-- Dependencias transitivas em Consulta2FN:
-- nomeAnimal, especie, raca -> dependem de idAnimal (transitiva)
-- nomeProprietario, telProprietario, emailProprietario -> dependem de idProprietario (transitiva)
-- nomeVeterinario, especialidade, nrOrdem -> dependem de idVeterinario (transitiva)

-- Resultado 3FN:

Consulta3FN(idConsulta, dataConsulta, hora, motivo, diagnostico, idAnimal, idVeterinario)

Animal3FN(idAnimal, nomeAnimal, especie, raca, idProprietario)

Proprietario3FN(idProprietario, nomeProprietario, telProprietario, emailProprietario)

Veterinario3FN(idVeterinario, nomeVeterinario, especialidade, nrOrdem)

ConsultaTratamento(idConsulta, idTratamento)

Tratamento2FN(idTratamento, descricaoTratamento, custoTratamento)

Prescricao2FN(idTratamento, idMedicamento, dosagem, duracao)

Medicamento2FN(idMedicamento, nomeMedicamento)

Porque adicionamos idAnimal e idProprietario?

Na relacao UNF original, estes identificadores estavam implicitos. Os dados do animal, proprietario e veterinario estavam embebidos diretamente na consulta. Ao normalizar, tornamos essas dependencias explicitas com chaves estrangeiras.

O resultado bate certo com o esquema ER?

Compara as relacoes em 3FN com o esquema relacional que obtiveste no passo anterior (mapeamento do diagrama ER). A estrutura e a mesma. Isto confirma o principio fundamental: o design top-down (ER) e o bottom-up (normalizacao) convergem para o mesmo resultado quando aplicados corretamente.

Se encontrares diferencas, verifica as tuas dependencias funcionais. A convergencia funciona como validacao cruzada dos dois metodos.

Dica para o exame

Num exercicio de normalizacao, escreve sempre a dependencia funcional que justifica cada decomposicao. Os professores valorizam ver "descricaoTratamento depende de idTratamento (parcial)" antes de mostrares a nova relacao. Mostra o raciocinio, nao apenas o resultado.

Verifica as tuas notas completas com o resumo abaixo.

✔️ Awesome, I've got everything!

Otimo. Confirma que tens as 3 etapas (UNF->1FN, 1FN->2FN, 2FN->3FN) escritas com as dependencias funcionais identificadas em cada passagem.

ⓧ I'd like to double check the full code

Aqui esta o exercicio completo de normalizacao, desde UNF ate 3FN:

=== UNF ===
ConsultaCompleta(idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca, nomeProprietario, telProprietario,
  emailProprietario, nomeVeterinario, especialidade, nrOrdem,
  {idTratamento, descricaoTratamento, custoTratamento,
    {idMedicamento, nomeMedicamento, dosagem, duracao}})

=== 1FN (eliminar grupos repetitivos) ===
Consulta1FN(idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca, nomeProprietario, telProprietario,
  emailProprietario, nomeVeterinario, especialidade, nrOrdem)
Tratamento1FN(idConsulta, idTratamento, descricaoTratamento, custoTratamento)
Prescricao1FN(idConsulta, idTratamento, idMedicamento, nomeMedicamento, dosagem, duracao)

=== 2FN (remover dependencias parciais) ===
Consulta2FN(idConsulta, dataConsulta, hora, motivo, diagnostico,
  nomeAnimal, especie, raca, nomeProprietario, telProprietario,
  emailProprietario, nomeVeterinario, especialidade, nrOrdem)
ConsultaTratamento(idConsulta, idTratamento)
Tratamento2FN(idTratamento, descricaoTratamento, custoTratamento)
Prescricao2FN(idTratamento, idMedicamento, dosagem, duracao)
Medicamento2FN(idMedicamento, nomeMedicamento)

=== 3FN (remover dependencias transitivas) ===
Consulta3FN(idConsulta, dataConsulta, hora, motivo, diagnostico, idAnimal, idVeterinario)
Animal3FN(idAnimal, nomeAnimal, especie, raca, idProprietario)
Proprietario3FN(idProprietario, nomeProprietario, telProprietario, emailProprietario)
Veterinario3FN(idVeterinario, nomeVeterinario, especialidade, nrOrdem)
ConsultaTratamento(idConsulta, idTratamento)
Tratamento2FN(idTratamento, descricaoTratamento, custoTratamento)
Prescricao2FN(idTratamento, idMedicamento, dosagem, duracao)
Medicamento2FN(idMedicamento, nomeMedicamento)

=== Validacao: resultado 3FN corresponde ao esquema relacional do diagrama ER ===

Tens agora um exercicio completo de normalizacao resolvido passo a passo. No proximo passo, vais usar o esquema final para escrever queries SQL (DDL e DML) sobre a base de dados da PetCare.

Escrever Queries SQL (DDL e DML)

Tens o esquema relacional completo da clinica PetCare, validado tanto pelo caminho top-down (diagrama ER) como pelo caminho bottom-up (normalizacao). Agora e hora de traduzir esse esquema para SQL, a linguagem que os SGBDs usam para criar e manipular bases de dados.

Neste passo vais praticar os dois lados do SQL: DDL (Data Definition Language) para definir a estrutura das tabelas, e DML (Data Manipulation Language) para consultar e modificar dados. Estes sao dois dos tipos de questoes mais frequentes no exame.

In this step, get ready to:
  • Escrever comandos CREATE TABLE com PRIMARY KEY, FOREIGN KEY, NOT NULL e CHECK.
  • Escrever queries SELECT com JOIN, GROUP BY, HAVING e subqueries.
  • Praticar comandos INSERT, UPDATE e DELETE.
Escrever os CREATE TABLE para as tabelas independentes

O DDL (Data Definition Language) e a parte do SQL que define a estrutura da base de dados. No exame, e comum pedirem para escrever os CREATE TABLE a partir do esquema relacional, incluindo todas as restricoes de integridade.

A ordem em que crias as tabelas importa. Tabelas que sao referenciadas por chaves estrangeiras precisam de existir primeiro. Comeca pelas 3 tabelas independentes (sem FKs).

  • No teu ficheiro ddl_petcare.sql, escreve os CREATE TABLE para Proprietario, Veterinario e Medicamento:
-- Criacao da tabela Proprietario
CREATE TABLE Proprietario (
    idProprietario  INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    telefone        VARCHAR(15),
    email           VARCHAR(100),
    morada          VARCHAR(200)
);

-- Criacao da tabela Veterinario
CREATE TABLE Veterinario (
    idVeterinario   INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    especialidade   VARCHAR(50),
    telefone        VARCHAR(15),
    nrOrdem         VARCHAR(20) NOT NULL UNIQUE
);

-- Criacao da tabela Medicamento
CREATE TABLE Medicamento (
    idMedicamento   INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    tipo            VARCHAR(50),
    fabricante      VARCHAR(100)
);

Que restricoes estao aqui?

  • PRIMARY KEY define o identificador unico de cada tuplo.
  • NOT NULL impede que campos obrigatorios fiquem vazios (ex: o nome de um veterinario).
  • UNIQUE em nrOrdem garante que dois veterinarios nao podem ter o mesmo numero de ordem profissional.
  • Campos como telefone ou email podem ser NULL porque nem sempre estao disponiveis.
  • Verifica que cada tabela tem exatamente um PRIMARY KEY e que os tipos de dados fazem sentido para os valores que vao guardar.

Erros comuns no exame com DDL basico

  • Esquecer NOT NULL em campos obrigatorios. No cenario, qualquer campo que identifica a entidade (nome) deve ser NOT NULL.
  • Usar INT para telefone. Telefones com zeros a esquerda perdem-se com INT. Usa VARCHAR.
  • Confundir UNIQUE com PRIMARY KEY. Um campo UNIQUE pode ter multiplos na tabela, mas PRIMARY KEY so ha um.

Porque e que nrOrdem e UNIQUE mas nao e PRIMARY KEY?

Escrever os CREATE TABLE com chaves estrangeiras

As tabelas dependentes referenciam outras tabelas atraves de FOREIGN KEY. Aqui e onde defines as acoes referenciais (ON DELETE, ON UPDATE) que controlam o que acontece quando um registo referenciado e eliminado ou atualizado.

  • Continua em ddl_petcare.sql e escreve os CREATE TABLE para Animal, Consulta, Tratamento e Prescricao:
-- Criacao da tabela Animal
CREATE TABLE Animal (
    idAnimal        INT         PRIMARY KEY,
    nome            VARCHAR(50) NOT NULL,
    especie         VARCHAR(30) NOT NULL,
    raca            VARCHAR(50),
    dataNascimento  DATE,
    peso            DECIMAL(5,2),
    idProprietario  INT         NOT NULL,
    CONSTRAINT fk_animal_prop FOREIGN KEY (idProprietario)
        REFERENCES Proprietario(idProprietario)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Criacao da tabela Consulta
CREATE TABLE Consulta (
    idConsulta      INT         PRIMARY KEY,
    data            DATE        NOT NULL,
    hora            TIME        NOT NULL,
    motivo          VARCHAR(200),
    diagnostico     VARCHAR(500),
    idAnimal        INT         NOT NULL,
    idVeterinario   INT         NOT NULL,
    CONSTRAINT fk_consulta_animal FOREIGN KEY (idAnimal)
        REFERENCES Animal(idAnimal)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_consulta_vet FOREIGN KEY (idVeterinario)
        REFERENCES Veterinario(idVeterinario)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Criacao da tabela Tratamento
CREATE TABLE Tratamento (
    idTratamento    INT         PRIMARY KEY,
    descricao       VARCHAR(200) NOT NULL,
    custo           DECIMAL(8,2) NOT NULL CHECK (custo >= 0),
    idConsulta      INT         NOT NULL,
    CONSTRAINT fk_tratamento_consulta FOREIGN KEY (idConsulta)
        REFERENCES Consulta(idConsulta)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Criacao da tabela Prescricao (entidade associativa M:N)
CREATE TABLE Prescricao (
    idTratamento    INT,
    idMedicamento   INT,
    dosagem         VARCHAR(50) NOT NULL,
    duracao         VARCHAR(50) NOT NULL,
    PRIMARY KEY (idTratamento, idMedicamento),
    CONSTRAINT fk_presc_trat FOREIGN KEY (idTratamento)
        REFERENCES Tratamento(idTratamento)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_presc_med FOREIGN KEY (idMedicamento)
        REFERENCES Medicamento(idMedicamento)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Que padroes de FK estao aqui?

  • ON DELETE CASCADE na tabela Animal significa que se um proprietario for eliminado, os seus animais tambem sao eliminados automaticamente.
  • ON DELETE RESTRICT na tabela Consulta (referencia ao Veterinario) impede eliminar um veterinario que tenha consultas associadas. Faz sentido porque queremos manter o historico.
  • CHECK (custo >= 0) no Tratamento garante que o custo nunca e negativo.
  • PRIMARY KEY (idTratamento, idMedicamento) na Prescricao e uma chave primaria composta. E assim que se implementa a entidade associativa de uma relacao M:N.

Porque CASCADE nuns sitios e RESTRICT noutros?

A decisao depende do significado do negocio. Se eliminar o proprietario faz sentido eliminar os seus animais do sistema (CASCADE), entao aplica-se CASCADE. Mas eliminar um veterinario nao deve apagar o historico de consultas que ele realizou (RESTRICT).

No exame, justifica sempre a tua escolha de acao referencial com base no cenario.

  • Verifica que a ordem dos CREATE TABLE respeita as dependencias: Proprietario antes de Animal, Animal e Veterinario antes de Consulta, Consulta antes de Tratamento, Tratamento e Medicamento antes de Prescricao.

Erros comuns com FOREIGN KEY no exame

  • Esquecer NOT NULL na FK quando a participacao e total. Se cada consulta DEVE ter um veterinario, idVeterinario precisa de NOT NULL.
  • Nao dar nome as constraints (CONSTRAINT fk_nome). Sem nome, torna-se dificil identificar erros.
  • Confundir a direcao da FK. A chave estrangeira fica sempre no lado N da relacao 1:N.

Ajuda-me a perceber quando usar CASCADE vs RESTRICT vs SET NULL.

✔️ Awesome, I've got everything!

Otimo. Confirma que tens as 7 tabelas escritas com todas as restricoes antes de avancar para as queries DML.

ⓧ I'd like to double check the full code

-- Criacao da tabela Proprietario
CREATE TABLE Proprietario (
    idProprietario  INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    telefone        VARCHAR(15),
    email           VARCHAR(100),
    morada          VARCHAR(200)
);

-- Criacao da tabela Animal
CREATE TABLE Animal (
    idAnimal        INT         PRIMARY KEY,
    nome            VARCHAR(50) NOT NULL,
    especie         VARCHAR(30) NOT NULL,
    raca            VARCHAR(50),
    dataNascimento  DATE,
    peso            DECIMAL(5,2),
    idProprietario  INT         NOT NULL,
    CONSTRAINT fk_animal_prop FOREIGN KEY (idProprietario)
        REFERENCES Proprietario(idProprietario)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Criacao da tabela Veterinario
CREATE TABLE Veterinario (
    idVeterinario   INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    especialidade   VARCHAR(50),
    telefone        VARCHAR(15),
    nrOrdem         VARCHAR(20) NOT NULL UNIQUE
);

-- Criacao da tabela Consulta
CREATE TABLE Consulta (
    idConsulta      INT         PRIMARY KEY,
    data            DATE        NOT NULL,
    hora            TIME        NOT NULL,
    motivo          VARCHAR(200),
    diagnostico     VARCHAR(500),
    idAnimal        INT         NOT NULL,
    idVeterinario   INT         NOT NULL,
    CONSTRAINT fk_consulta_animal FOREIGN KEY (idAnimal)
        REFERENCES Animal(idAnimal)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_consulta_vet FOREIGN KEY (idVeterinario)
        REFERENCES Veterinario(idVeterinario)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- Criacao da tabela Tratamento
CREATE TABLE Tratamento (
    idTratamento    INT         PRIMARY KEY,
    descricao       VARCHAR(200) NOT NULL,
    custo           DECIMAL(8,2) NOT NULL CHECK (custo >= 0),
    idConsulta      INT         NOT NULL,
    CONSTRAINT fk_tratamento_consulta FOREIGN KEY (idConsulta)
        REFERENCES Consulta(idConsulta)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Criacao da tabela Medicamento
CREATE TABLE Medicamento (
    idMedicamento   INT         PRIMARY KEY,
    nome            VARCHAR(100) NOT NULL,
    tipo            VARCHAR(50),
    fabricante      VARCHAR(100)
);

-- Criacao da tabela Prescricao (entidade associativa M:N)
CREATE TABLE Prescricao (
    idTratamento    INT,
    idMedicamento   INT,
    dosagem         VARCHAR(50) NOT NULL,
    duracao         VARCHAR(50) NOT NULL,
    PRIMARY KEY (idTratamento, idMedicamento),
    CONSTRAINT fk_presc_trat FOREIGN KEY (idTratamento)
        REFERENCES Tratamento(idTratamento)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_presc_med FOREIGN KEY (idMedicamento)
        REFERENCES Medicamento(idMedicamento)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
Escrever queries SELECT (Q1 a Q5)

O DML (Data Manipulation Language) e usado para consultar e modificar dados. No exame, as queries SELECT sao quase sempre pedidas com complexidade crescente: primeiro um SELECT simples, depois JOINs, depois agregacoes e subqueries.

  • Cria o ficheiro dml_queries.sql e escreve as queries Q1 e Q2 (SELECT simples e JOIN):
-- Q1: Listar nome e especie dos animais da raca 'Labrador'
SELECT nome, especie
FROM Animal
WHERE raca = 'Labrador';

-- Q2: Nome do animal, data da consulta e nome do veterinario para consultas de 2024
SELECT A.nome AS nomeAnimal, C.data, V.nome AS nomeVeterinario
FROM Consulta C
    INNER JOIN Animal A ON C.idAnimal = A.idAnimal
    INNER JOIN Veterinario V ON C.idVeterinario = V.idVeterinario
WHERE C.data >= '2024-01-01' AND C.data <= '2024-12-31';

O que esta a acontecer nestas queries?

  • Q1 usa apenas SELECT + WHERE sobre uma tabela. E o padrao mais simples.
  • Q2 precisa de dados de 3 tabelas (Animal, Consulta, Veterinario), por isso usa INNER JOIN duas vezes. Os aliases (A, C, V) tornam o codigo mais legivel.
  • A condicao de juncao (ON) usa sempre a FK que liga as tabelas. Consulta liga a Animal via idAnimal e a Veterinario via idVeterinario.
  • Agora escreve as queries Q3, Q4 e Q5 (GROUP BY, subquery e multi-join com agregacao):
-- Q3: Veterinarios com mais de 5 consultas
SELECT V.nome, COUNT(C.idConsulta) AS totalConsultas
FROM Veterinario V
    INNER JOIN Consulta C ON V.idVeterinario = C.idVeterinario
GROUP BY V.idVeterinario, V.nome
HAVING COUNT(C.idConsulta) > 5;

-- Q4: Proprietarios que nunca trouxeram animais a consulta
SELECT P.nome
FROM Proprietario P
WHERE P.idProprietario NOT IN (
    SELECT A.idProprietario
    FROM Animal A
        INNER JOIN Consulta C ON A.idAnimal = C.idAnimal
);

-- Q5: Nome do animal, nome do proprietario e custo total dos tratamentos
SELECT A.nome AS nomeAnimal, P.nome AS nomeProprietario,
       COALESCE(SUM(T.custo), 0) AS custoTotal
FROM Animal A
    INNER JOIN Proprietario P ON A.idProprietario = P.idProprietario
    LEFT JOIN Consulta C ON A.idAnimal = C.idAnimal
    LEFT JOIN Tratamento T ON C.idConsulta = T.idConsulta
GROUP BY A.idAnimal, A.nome, P.nome;

Padroes avancados nestas queries

  • Q3: HAVING filtra DEPOIS do GROUP BY (ao contrario de WHERE que filtra ANTES). Usa-se HAVING quando a condicao envolve funcoes de agregacao como COUNT.
  • Q4: NOT IN com subquery encontra proprietarios cujo ID nao aparece no resultado da subquery. A subquery lista todos os proprietarios que TEM consultas.
  • Q5: LEFT JOIN garante que animais sem consultas ou sem tratamentos aparecem no resultado (com custo 0). COALESCE substitui NULL por 0 quando nao existem tratamentos.
  • Verifica que cada query projeta apenas os atributos pedidos no enunciado e que as condicoes de juncao correspondem as FKs do esquema relacional.

Erros frequentes em queries SELECT no exame

  • Usar WHERE em vez de HAVING para filtrar agregacoes. WHERE filtra linhas individuais ANTES do agrupamento. HAVING filtra grupos DEPOIS.
  • Esquecer de incluir no GROUP BY todos os atributos nao agregados do SELECT. Na Q3, tanto V.idVeterinario como V.nome precisam de estar no GROUP BY.
  • Usar INNER JOIN quando o enunciado pede "todos os animais" (incluindo os sem consultas). Se o resultado deve incluir entidades sem correspondencia na outra tabela, usa LEFT JOIN.

Ajuda-me a decidir entre INNER JOIN e LEFT JOIN.

Escrever comandos INSERT, UPDATE e DELETE

Alem de consultar dados, o exame pede frequentemente para escrever comandos que modificam a base de dados. Estes sao mais simples que as queries SELECT mas exigem atencao a integridade referencial.

  • Continua em dml_queries.sql e escreve os comandos INSERT, UPDATE e DELETE:
-- INSERT: Novo proprietario e animal
INSERT INTO Proprietario (idProprietario, nome, telefone, email, morada)
VALUES (1, 'Maria Silva', '912345678', 'maria@email.com', 'Rua das Flores 10, Lisboa');

INSERT INTO Animal (idAnimal, nome, especie, raca, dataNascimento, peso, idProprietario)
VALUES (1, 'Rex', 'Cao', 'Pastor Alemao', '2020-03-15', 32.5, 1);

-- UPDATE: Atualizar telefone de um proprietario
UPDATE Proprietario
SET telefone = '919876543'
WHERE idProprietario = 1;

-- DELETE: Eliminar consultas anteriores a 2020
DELETE FROM Consulta
WHERE data < '2020-01-01';

O que observar nestes comandos?

  • O INSERT do Animal referencia idProprietario = 1. Este proprietario TEM de existir primeiro (inserido na linha anterior), senao a FK falha.
  • O UPDATE usa sempre WHERE para limitar as linhas afetadas. Sem WHERE, TODOS os proprietarios ficariam com o mesmo telefone.
  • O DELETE com CASCADE no esquema significa que eliminar consultas anteriores a 2020 tambem elimina automaticamente os tratamentos e prescricoes associados.
  • Verifica que a ordem dos INSERTs respeita as FKs (proprietario antes de animal) e que UPDATE e DELETE tem sempre clausula WHERE.

Erros comuns com INSERT/UPDATE/DELETE

  • Inserir um Animal com um idProprietario que ainda nao existe. Respeita a ordem das dependencias.
  • Esquecer a clausula WHERE no UPDATE ou DELETE. Sem WHERE, o comando afeta TODAS as linhas da tabela.
  • Nao pensar no efeito em cascata. Um DELETE na tabela Consulta pode desencadear deletes em Tratamento e Prescricao se as FKs tiverem ON DELETE CASCADE.

Que efeito tem o meu DELETE nas tabelas relacionadas?

✔️ Awesome, I've got everything!

Perfeito. Confirma que tens as 5 queries SELECT mais os comandos INSERT, UPDATE e DELETE escritos antes de avancar.

ⓧ I'd like to double check the full code

-- Q1: Listar nome e especie dos animais da raca 'Labrador'
SELECT nome, especie
FROM Animal
WHERE raca = 'Labrador';

-- Q2: Nome do animal, data da consulta e nome do veterinario para consultas de 2024
SELECT A.nome AS nomeAnimal, C.data, V.nome AS nomeVeterinario
FROM Consulta C
    INNER JOIN Animal A ON C.idAnimal = A.idAnimal
    INNER JOIN Veterinario V ON C.idVeterinario = V.idVeterinario
WHERE C.data >= '2024-01-01' AND C.data <= '2024-12-31';

-- Q3: Veterinarios com mais de 5 consultas
SELECT V.nome, COUNT(C.idConsulta) AS totalConsultas
FROM Veterinario V
    INNER JOIN Consulta C ON V.idVeterinario = C.idVeterinario
GROUP BY V.idVeterinario, V.nome
HAVING COUNT(C.idConsulta) > 5;

-- Q4: Proprietarios que nunca trouxeram animais a consulta
SELECT P.nome
FROM Proprietario P
WHERE P.idProprietario NOT IN (
    SELECT A.idProprietario
    FROM Animal A
        INNER JOIN Consulta C ON A.idAnimal = C.idAnimal
);

-- Q5: Nome do animal, nome do proprietario e custo total dos tratamentos
SELECT A.nome AS nomeAnimal, P.nome AS nomeProprietario,
       COALESCE(SUM(T.custo), 0) AS custoTotal
FROM Animal A
    INNER JOIN Proprietario P ON A.idProprietario = P.idProprietario
    LEFT JOIN Consulta C ON A.idAnimal = C.idAnimal
    LEFT JOIN Tratamento T ON C.idConsulta = T.idConsulta
GROUP BY A.idAnimal, A.nome, P.nome;

-- INSERT: Novo proprietario e animal
INSERT INTO Proprietario (idProprietario, nome, telefone, email, morada)
VALUES (1, 'Maria Silva', '912345678', 'maria@email.com', 'Rua das Flores 10, Lisboa');

INSERT INTO Animal (idAnimal, nome, especie, raca, dataNascimento, peso, idProprietario)
VALUES (1, 'Rex', 'Cao', 'Pastor Alemao', '2020-03-15', 32.5, 1);

-- UPDATE: Atualizar telefone de um proprietario
UPDATE Proprietario
SET telefone = '919876543'
WHERE idProprietario = 1;

-- DELETE: Eliminar consultas anteriores a 2020
DELETE FROM Consulta
WHERE data < '2020-01-01';

Tens agora o esquema DDL completo e um conjunto de queries DML que cobre os padroes mais pedidos no exame. No proximo passo, vais traduzir estas queries para algebra relacional.

Traduzir Queries para Algebra Relacional

Tens agora um conjunto completo de queries SQL que cobrem selecao, juncao, agregacao e subqueries. O proximo desafio e traduzir essas mesmas queries para a linguagem formal da algebra relacional.

A algebra relacional usa operadores como selecao (sigma), projecao (pi) e juncao (bowtie) para descrever consultas de forma matematica. No exame, e comum pedirem a traducao de SQL para esta notacao, ou vice-versa.

In this step, get ready to:
  • Traduzir as queries Q1 a Q3 para algebra relacional usando selecao, projecao e juncao.
  • Resolver exercicios com operacoes de conjunto (intersecao e diferenca).
  • Validar todas as expressoes verificando atributos de juncao, projecoes e condicoes.
Traduzir Q1 a Q3 para operacoes fundamentais

Cada query SQL pode ser decomposta numa sequencia de operacoes da algebra relacional. Os tres operadores base que vais usar aqui sao sigma (selecao de linhas), pi (projecao de colunas) e o bowtie (juncao de tabelas).

  • Pega na tua Q1 em SQL (listar nome e especie dos animais da raca 'Labrador').
  • Identifica a operacao de filtragem (WHERE) e a operacao de selecao de colunas (SELECT).
  • Escreve a expressao de algebra relacional correspondente:
Q1: pi(nome, especie)(sigma(raca = 'Labrador')(Animal))

Como ler esta expressao?

Le de dentro para fora. Primeiro, sigma seleciona as linhas da tabela Animal onde raca = 'Labrador'. Depois, pi projeta apenas as colunas nome e especie do resultado.

O mapeamento e direto: WHERE em SQL corresponde a sigma, e a lista de colunas no SELECT corresponde a pi.

  • Agora traduz a Q2 (nome do animal, data da consulta e nome do veterinario para consultas de 2024). Esta query envolve juncoes entre tres tabelas.
  • Escreve a expressao usando o operador de juncao (bowtie) com os atributos de ligacao entre parenteses:
Q2: pi(Animal.nome, Consulta.data, Veterinario.nome)(
    sigma(data >= '2024-01-01' AND data <= '2024-12-31')(
        Animal ⋈(idAnimal) Consulta ⋈(idVeterinario) Veterinario
    )
)

O que e o simbolo bowtie?

O simbolo ⋈ representa a juncao natural (natural join). Quando escrevemos ⋈(idAnimal), indicamos que a juncao e feita pelo atributo idAnimal que existe em ambas as tabelas. Cada INNER JOIN em SQL traduz-se num operador ⋈ com o atributo de ligacao.

  • Para a Q3 (veterinarios com mais de 5 consultas), precisas de uma operacao de agrupamento. Escreve a expressao usando a notacao gamma:
Q3: gamma(nomeVeterinario; COUNT(idConsulta) AS totalConsultas)(
    sigma(totalConsultas > 5)(
        Veterinario ⋈(idVeterinario) Consulta
    )
)

A notacao gamma e padrao?

A operacao de agrupamento (gamma) nao faz parte da algebra relacional classica. E uma extensao comum usada em manuais modernos. No livro de Connolly e Begg, a notacao F (funcao de agregacao) e usada para o mesmo efeito.

No teu exame, verifica qual a notacao que o docente utiliza. A logica e a mesma: agrupar por um atributo e aplicar uma funcao de agregacao ao resultado.

  • Revisa as tres expressoes e confirma que cada operador esta aplicado na ordem correta (de dentro para fora).

Dica para o exame

Comeca sempre pela juncao (se houver varias tabelas), depois aplica a selecao (filtrar linhas), e por fim a projecao (escolher colunas). Esta ordem evita erros de atributos inexistentes.

Resolver exercicios com operacoes de conjunto

Alem das operacoes fundamentais, a algebra relacional inclui operacoes de conjunto: uniao, intersecao e diferenca. Estas operacoes requerem que ambas as relacoes sejam union-compatible (mesmos atributos, na mesma ordem).

  • Resolve a Q6: listar veterinarios que realizaram consultas E tambem prescreveram o medicamento 'Amoxicilina'. Esta query usa a operacao de intersecao.
  • Constroi as duas relacoes separadamente e depois aplica o operador de intersecao:
-- Veterinarios que realizaram consultas
R1 = pi(idVeterinario, nome)(Veterinario ⋈(idVeterinario) Consulta)

-- Veterinarios que prescreveram Amoxicilina
R2 = pi(idVeterinario, nome)(
    Veterinario ⋈(idVeterinario) Consulta ⋈(idConsulta) Tratamento
    ⋈(idTratamento) Prescricao ⋈(idMedicamento)
    sigma(nome = 'Amoxicilina')(Medicamento)
)

-- Q6: Intersecao
Q6 = R1 ∩ R2

Porque e que as relacoes tem de ser union-compatible?

Para aplicar intersecao (ou uniao, ou diferenca), ambas as relacoes precisam de ter exatamente os mesmos atributos. E por isso que R1 e R2 projetam ambas para (idVeterinario, nome). Se os atributos nao coincidissem, a operacao seria invalida.

  • Resolve a Q7: listar animais que tiveram consultas mas nunca receberam tratamento. Esta query usa a operacao de diferenca.
  • Constroi o conjunto de animais com consultas, o conjunto de animais com tratamentos, e subtrai:
-- Animais que tiveram consultas
R3 = pi(idAnimal, nome)(Animal ⋈(idAnimal) Consulta)

-- Animais que receberam tratamento
R4 = pi(idAnimal, nome)(
    Animal ⋈(idAnimal) Consulta ⋈(idConsulta) Tratamento
)

-- Q7: Diferenca
Q7 = R3 - R4

Diferenca vs NOT IN em SQL

A operacao de diferenca (R3 - R4) e o equivalente formal do NOT IN ou NOT EXISTS em SQL. Retorna todos os tuplos que estao em R3 mas nao estao em R4.

Validacao cruzada das expressoes

Antes de dares o passo como concluido, verifica cada expressao de algebra relacional contra uma checklist de erros comuns. Este passo de validacao e o que separa uma resposta correta de uma resposta quase correta no exame.

  • Para cada expressao, verifica que as operacoes de juncao usam os atributos corretos (chaves estrangeiras do teu esquema relacional).
  • Confirma que a projecao final (pi) inclui apenas os atributos pedidos no enunciado.
  • Verifica que a selecao (sigma) aplica exatamente as condicoes do WHERE em SQL.
  • Para Q6 e Q7, confirma que ambas as relacoes sao union-compatible (mesmos atributos projetados em ambos os lados da intersecao ou diferenca).

Checklist rapida de validacao

  • Juncoes: Animal ⋈(idAnimal) Consulta usa idAnimal porque e a FK na tabela Consulta que referencia Animal.
  • Projecoes: Q2 pede Animal.nome, Consulta.data e Veterinario.nome. Se projetares mais ou menos colunas, esta errado.
  • Selecoes: Em Q2, a condicao e data >= '2024-01-01' AND data <= '2024-12-31'. Nao confundir com BETWEEN (que inclui ambos os extremos).
  • Union-compatible: Em Q6, R1 e R2 projetam ambas para (idVeterinario, nome). Se uma projetasse para (nome) e outra para (idVeterinario, nome), a intersecao seria invalida.
  • Corrige qualquer expressao que nao passe na checklist.
  • Compara as tuas expressoes finais com o SQL original para garantir equivalencia semantica.

Erros comuns na traducao?

  • Aplicar pi antes de sigma numa expressao com juncao faz com que os atributos do filtro ja nao existam. Aplica sempre a juncao primeiro, depois sigma, depois pi.
  • Usar o nome do atributo errado na juncao (por exemplo, escrever ⋈(idConsulta) entre Animal e Consulta quando o atributo correto e idAnimal).
  • Esquecer de projetar ambas as relacoes para os mesmos atributos antes de aplicar intersecao ou diferenca.

Ajuda-me a encontrar o erro na minha expressao de algebra relacional.

✔️ Awesome, I've got everything!

Otimo. Confirma que guardaste todas as expressoes nas tuas notas de algebra relacional.

ⓧ I'd like to double check the full code

Aqui esta o conjunto completo de expressoes de algebra relacional que deves ter nas tuas notas:

-- OPERACOES FUNDAMENTAIS --

Q1: pi(nome, especie)(sigma(raca = 'Labrador')(Animal))

Q2: pi(Animal.nome, Consulta.data, Veterinario.nome)(
    sigma(data >= '2024-01-01' AND data <= '2024-12-31')(
        Animal ⋈(idAnimal) Consulta ⋈(idVeterinario) Veterinario
    )
)

Q3: gamma(nomeVeterinario; COUNT(idConsulta) AS totalConsultas)(
    sigma(totalConsultas > 5)(
        Veterinario ⋈(idVeterinario) Consulta
    )
)
Nota: gamma/F e uma extensao da algebra relacional classica.

-- OPERACOES DE CONJUNTO --

-- Q6: Intersecao - veterinarios com consultas E que prescreveram Amoxicilina
R1 = pi(idVeterinario, nome)(Veterinario ⋈(idVeterinario) Consulta)
R2 = pi(idVeterinario, nome)(
    Veterinario ⋈(idVeterinario) Consulta ⋈(idConsulta) Tratamento
    ⋈(idTratamento) Prescricao ⋈(idMedicamento)
    sigma(nome = 'Amoxicilina')(Medicamento)
)
Q6 = R1 ∩ R2

-- Q7: Diferenca - animais com consultas mas sem tratamento
R3 = pi(idAnimal, nome)(Animal ⋈(idAnimal) Consulta)
R4 = pi(idAnimal, nome)(
    Animal ⋈(idAnimal) Consulta ⋈(idConsulta) Tratamento
)
Q7 = R3 - R4

-- VALIDACAO CRUZADA --
-- Juncoes usam atributos FK corretos: idAnimal, idVeterinario, idConsulta, idTratamento, idMedicamento
-- Projecoes incluem apenas atributos pedidos
-- Selecoes correspondem ao WHERE do SQL
-- R1/R2 e R3/R4 sao union-compatible (mesmos atributos projetados)

Secret mission

Banco de Perguntas Teoricas de Exame

O exame nao testa apenas SQL e diagramas. Tambem inclui perguntas de desenvolvimento onde tens de explicar conceitos com definicoes formais, exemplos e justificacoes. Nesta missao, vais construir um banco de respostas modelo que cobre os 6 temas teoricos mais frequentes.

Clean Up Your Resources

Clean Up Your Resources

Este projeto corre inteiramente com papel e caneta (ou um editor de texto simples), por isso nao existem custos continuos.

Decide se queres manter os teus materiais de estudo para revisao, organiza-los para mais tarde, ou elimina-los.

Recursos que utilizaste:

  • Ficheiro ddl_petcare.sql (comandos CREATE TABLE).
  • Ficheiro dml_queries.sql (queries SQL).
  • Ficheiro perguntas_teoricas.md (respostas modelo a perguntas de desenvolvimento).
  • Notas de diagrama ER, esquema relacional, normalizacao e algebra relacional (papel ou ficheiro de texto).

Keep everything running

Nao precisas de fazer nada. Escolhe esta opcao se estas a rever ativamente para o exame e vais voltar a consultar estes materiais.

  • Mantem todos os ficheiros e notas organizados numa pasta dedicada ao estudo de Bases de Dados.
  • Considera rever os exercicios periodicamente ate ao dia do exame.

Pause - I'll come back to this later

Organiza os teus materiais para os encontrares facilmente quando voltares a estudar.

  • Cria uma pasta chamada petcare-exame-bd e move todos os ficheiros para la.
  • Se fizeste notas em papel, tira uma fotografia ou digitaliza-as e guarda na mesma pasta.
  • Nao ha processos a parar nem recursos a suspender.

Delete - I don't want to use this again

Remove todos os ficheiros do projeto se ja nao precisas deles.

  • Elimina os ficheiros ddl_petcare.sql, dml_queries.sql e perguntas_teoricas.md.
  • Se guardaste notas num editor de texto, elimina esses ficheiros tambem.
  • Se fizeste notas em papel e ja nao precisas delas, podes recicla-las.

Nice Work!

Nice Work!

Parabens! Acabaste de percorrer a metodologia completa de design de bases de dados, desde a analise de requisitos ate a algebra relacional, usando o cenario da clinica veterinaria PetCare.

Neste projeto, aprendeste a:

  • Desenhar um diagrama ER completo com entidades, atributos, cardinalidades e restricoes de participacao, e mapea-lo para o modelo relacional.
  • Normalizar uma relacao de UNF ate 3FN identificando dependencias funcionais parciais e transitivas, validando que o resultado converge com o design top-down.
  • Escrever queries SQL (DDL e DML com JOINs, GROUP BY e subqueries) e traduzi-las para algebra relacional usando selecao, projecao, juncao e operacoes de conjunto.
  • Secret Mission: Respondeste a perguntas teoricas de desenvolvimento no formato de exame, demonstrando sintese entre conceitos como independencia de dados, arquitetura ANSI-SPARC, formas normais e anomalias.

Ready to quiz yourself?