Ficha 02 · Normalização e restrições

O SQL desta ficha segue o dialeto do MySQL 8.

Parte I · Formas normais

Exercício 1 · Identificar violação (15 pts)

Para cada caso, indica que forma normal é violada, como corrigir e a que forma normal pertence cada tabela depois de corrigida:

a)

b)

(chave primária: aluno e modulo; cada módulo tem um só formador)

c)

(chave primária: num, o número da fatura; o NIF determina o nome e a morada do cliente)

Exercício 2 · Normalizar (20 pts)

Uma oficina regista as reparações assim, com um grupo repetitivo entre parênteses:

Reparacao(num_ordem, data, matricula, marca, modelo, nome_dono, (cod_servico, descricao, preco_hora, horas))

Regras: num_ordem identifica a reparação; matricula → marca, modelo, nome_dono; cod_servico → descricao, preco_hora; {num_ordem, cod_servico} → horas.

a) Passa à 1FN e indica a chave. (5 pts)

b) Passa à 2FN. (7 pts)

c) Passa à 3FN e escreve o esquema final. (8 pts)

Exercício 3 · Quando desnormalizar (10 pts)

a) Em que situações pode fazer sentido desnormalizar uma base de dados bem normalizada?

b) Que cuidados ter quando se desnormaliza?

Exercício 4 · FNBC (15 pts)

Numa escola profissional, cada orientador de estágio pertence a um só curso. Cada aluno tem, em cada curso, um só orientador. A tabela é Orientacao(aluno, curso, orientador), com estes dados:

a) Escreve as dependências funcionais e as chaves candidatas. (5 pts)

b) A tabela está em 3FN? E em FNBC? Mostra, com os dados, a anomalia de atualização que surge se o Dr. Silva passar para o curso de Multimédia. (5 pts)

c) Decompõe-na e indica o que se perde. (5 pts)

Parte II · Restrições

Exercício 5 · Definir restrições (12 pts)

Para a tabela Produto (id, nome, preco, stock, cat_id), escreve SQL com todas as restrições adequadas:

Exercício 6 · Ações referenciais (10 pts)

Para cada relação, indica a ação ON DELETE mais apropriada e justifica:

a) Apagar Encomenda: o que fazer com EncomendaItem?

b) Apagar Cliente: o que fazer com Encomenda?

c) Apagar Categoria: o que fazer com Produto?

d) Apagar Autor: o que fazer com Livro_Autor (tabela de associação)?

Parte III · Cenário

Exercício 7 · Sistema de gestão escolar (10 pts)

Pretende-se gerir disciplinas, alunos, professores, turmas e notas.

a) Identifica 5 ou mais entidades. (2 pts)

b) Desenha pelo menos uma relação 1:N e uma N:M com tabela de associação. (6 pts)

c) Indica restrições importantes em pelo menos 3 colunas. (2 pts)

Parte IV · Documentação

Exercício 8 · Ler o manual (8 pts)

Este excerto resume a sintaxe das chaves estrangeiras no manual do MySQL 8.0:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Junto da opção SET DEFAULT, o manual avisa que o analisador a reconhece, mas que o InnoDB rejeita as definições de tabela que a usem.

a) A cláusula ON DELETE é obrigatória? Que opções se podem escrever? (3 pts)

b) Uma chave estrangeira pode ter mais do que uma coluna? Como se vê no excerto? (2 pts)

c) Um colega quer escrever ON DELETE SET DEFAULT numa tabela InnoDB para passar os produtos à categoria «Outros». Vai funcionar? Que alternativa lhe propões? (3 pts)