Bases de dados: Integridade referencial e Normalização

Carlos SantosLabMM 4 - NTC - DeCA - UAAula 09, 14-03-2013

Erros comuns na integridade dos dados

Erros tipográficos na introdução dos dados

Erros na introdução de dados num campo, cujo conjunto de valores possíveis deveria estar bem delimitado (escolher um país da lista de países)

• Na BD poder-se-ão usar tipos de dados como o SET e ENUM ou tabelas dicionário

Erros entre os dados introduzidos em diferentes campos

• Exemplo: todas as datas relacionadas com a vida de um indivíduo têm de ser posteriores à data do seu nascimento

Prevenir ou minorar os efeitos destes erros com:

• HTML/JavaScript• PHP• MySQL

Integridade referencial

A integridade referencial implica que se deva garantir que todos os valores atribuídos à FK (tabela de chegada) existam do lado da PK (tabela de partida)

No mySQL, o motor InnoDB, com o seu suporte a chaves estrangeiras, já implementa mecanismos de preservação da integridade referencial da BD

Existe no entanto um “problema”:

• Quando um registo da tabela de partida (do lado da PK) é removido o que deve acontecer ao(s) registo(s) na tabela de chegada (do lado da FK) que o referenciava(m)?

Integridade referencial

Removendo na tabela CLIENTES, o registo onde a PK toma o valor 2 o que acontecerá aos registos na tabela ENCOMENDAS onde a FK toma esse valor?

ENCOMENDASENCOMENDASidENCOMENDAS DataEncomenda DataPagamento CLIENTES_idCLIENTES

1 2008-­‐02-­‐23 2008-­‐01-­‐25 12 2008-­‐04-­‐11 2008-­‐02-­‐23 23 2008-­‐03-­‐13 2008-­‐03-­‐23 24 2008-­‐05-­‐21 2008-­‐04-­‐23 35 2008-­‐06-­‐25 2008-­‐08-­‐23 2

CLIENTESCLIENTESidCLIENTES Nome

1 João2 Maria3 Manuel

Em muitos casos não se removem registos! Os registos “removidos” são guardados num estado de inactivos/invisíveis.

Solução: Adicionar coluna extra à tabela (estado: activo/inactivo, 0/1)

Integridade referencial

No Workbench, no separador Foreign Keys da tabela de chegada (do lado da FK), devem configurar-se as Foreign Key Options

Isso permite decidir o que acontece na tabela de chegada, quando se actualiza/apaga um registo na tabela de partida (do lado da PK)

Foreign Key Options [Foreign Key Constraints]

RESTRICT

• Não é permitido atualizar/apagar um registo na tabela de partida se este tiver registos relacionados na tabela de chegada

NO ACTION

• Funcionamento idêntico ao RESTRICT

CASCADE

• Ao atualizar/apagar um registo na tabela de partida essa operação é executada também nos registos relacionados na tabela de chegada

SET NULL

• Ao actualizar/apagar um registo na tabela de partida, são colocados a NULL os valores da chave estrangeira nos registos relacionados (isto só é possível, se a definição da FK o permitir, por exemplo, se não tiver sido parametrizada com o NOT NULL)

CASCADE DELETE

Resultados potencialmente catastróficos para a base de dados!

• Imaginemos um cenário de uma BD para uma loja que só tem um vendedor e que está associado a todas as vendas efetuadas.• Se todas as relações da BD estiverem com CASCADE DELETE, o que

acontece se o vendedor for apagado?

E se for uma actualização do valor da chave primária?

Os mecanismos que permitem definir como reagir ao evento de apagar existem também para o evento de actualizar.

Normalização

Processo de organizar os campos e tabelas de uma base de dados relacional de modo a minimizar a redundância e dependência dos dados

• é essencial para que se possa tirar partido do SQL

Objetivos:

• libertar a BD de anomalias de edição• minimizar necessidades de redesenho da BD quando é necessário

estender a sua implementação• tornar o modelo de dados mais informativo para os utilizadores• evitar tendência do modelo relativamente a um determinado padrão de

queries

(http://en.wikipedia.org/wiki/Database_normalization)

Primeira regra de normalização (First normal form)

Eliminação de grupos repetitivos:

• A primeira regra de normalização envolve a eliminação de grupos repetitivos dentro de um qualquer campo.

ENCOMENDASENCOMENDASENCOMENDASENCOMENDASENCOMENDAS

NrEncom DataEncomenda DataPagamento Produto Quantidade

1 12-09-2000 12-09-2000Mesa de Sala

SofáCadeira

1212

2 30-08-2001 11-12-2001Cadeira

Aparador21

3 21-01-2000 24-01-2000 Sofá 3

Segunda regra de normalização(Second normal form)

Eliminação de dados redundantes (na tabela):

• a primeira regra de normalização tem que ser respeitada;• todos os campos que não fazem parte da chave primária são

funcionalmente dependentes da totalidade da chave primária; isto é, não podem existir campos que são dependentes apenas de uma parte da chave primária.

• Neste caso, o campo “Produto” é dependente do campo “ID_Produto” que é apenas uma parte da chave primária, logo a tabela não se encontra de acordo com a segunda regra de normalização.

ENCOM_PRODENCOM_PRODENCOM_PRODENCOM_PROD

NrEncom ID_Produto Produto Quantidade

1 1 Cadeira 12

1 2 Mesa de Sala 1

2 3 Aparador 2

Terceira regra de normalização (Third normal form)

Eliminação de campos que não são dependente das chaves:

• a segunda regra de normalização tem que ser respeitada;• todos os campos que não são chaves têm que ser independentes entre si.

Ou seja, para os campos que não são chaves, o valor de uma dado campo não pode depender do valor de outro campo.

• Neste caso, o campo “Localidade” é dependente do campo “CodPostal”, logo a tabela não se encontra de acordo com a terceira regra de normalização.

• Por vezes, em situações práticas, pode ser conveniente não aplicar totalmente esta regra de normalização....

• Campos calculados também são “proibidos” por esta regra.

VENDEDORVENDEDORVENDEDORVENDEDORVENDEDORNrVendedor Nome Apelido CodPostal Localidade

1 João Tomás 3810 Aveiro1 Maria Costa 3830 Ílhavo2 Manuel Ribeiro 1000 Lisboa

Regras de normalização

Mais regras de normalização:

• Boyce/Codd Normal Form• Quarta regra de normalização (Fourth normal form)• Quinta regra de normalização (Fifth normal form)• Domain Key/Normal Form• ...

Estas regras raramente têm que ser aplicadas porque só fazem sentido em situações muito complexas que raramente são encontradas no mundo real.

Desnormalização

Em condições específicas deve ser realizada, por exemplo, para melhorar a performance...

E a seguir?

SQL vs noSQL

Mini-teste e... PHP