Triggers Restrições de Integridade

(1)

Restrições de

Integridade

(2)

Roteiro

• Manutenção de RI através das transações

• Declaração e Manutenção de RI em SQL-92

• Restrições Declarativas em SQL-92

• Manutenção de Rest. Referencial em SQL-92

• Definindo Restrições Gerais com Assertivas

• Banco de Dados Ativos

(3)

Motivação

• _{Um Banco de Dados armazena informações}

sobre uma parte do mundo real: o

mini-mundo.

• _{Deve haver regras que determinam quando}

uma dada informação pode ser armazenada,

de forma a manter o banco consistente

• _{Para isso, o SGBD deve:}

– _{garantir que as informações armazenadas sejam}

sempre exatas e consistentes

– oferecer mecanismos para garantir a

(4)

Restrições de Integridade

• _{São as regras que governam o mini-mundo}

• _{Especificam os estados do BD que são}

considerados consistentes.

– _{O Estado de um Banco de Dados é dito}

(5)

Restrições de Integridade

• _{São regras de consistência de dados,}

garantidas pelo próprio SGBD.

• _{Precisam ser testadas quando um registro}

é incluído, alterado ou excluído do BD

• _Garantem

_{que mudanças feitas no banco}

de dados por usuários autorizados não

(6)

(7)

Manutenção de RIs

• _{Qualquer operação de atualização do BD}

deve preservar as restrições de

integridade.

mapeia um estado consistente em um outro estado consistente

• _{Quando uma atualização viola uma}

restrição pode-se:

– _{Proibir a atualização (“Rolling Back”)} – _{Disparar ações corretivas}

Estad o Inicial

Estad o Final

(8)

Enfoques para manutenção de

RIs

• _{Através dos programas de aplicação que}

utilizam o BD (manutenção por

transações)

• _{Através do SGBD}

(database enforced constraints)

– _{Enfoque Declarativo}

(9)

Manutenção por transações

• _{As restrições são checadas pelas}

transações realizadas pelas aplicações que

acessam o BD.

• _{Parte do código da transação deve checar}

explicitamente se alguma RI será violada.

• _{Em caso positivo, a transação deve ser}

abortada ou tratada.

SGBD passivo

BD1 _BD2

(10)

Manutenção por transações

• _{Vantagens :}

– _{Bem geral. Pode ser utilizado em qualquer BD} – _{Permite checagem de forma eficiente.}

• _{Desvantagens :}

– _{Não é flexível; é completamente aberto.}

– _{Responsabilidade extra para o programador.} – _{Mudanças nas restrições requerem}

recodificação das transações.

– _{Falta de segurança.}

(11)

Manutenção através do SGBD

• _{Enfoque Declarativo}

– _{RI é implementada como parte do comando de}

definição de uma tabela

• _{Enfoque Procedimental:}

(12)

Restrições Implícitas X Explícitas

• Restrições Implícitas (Declarativas) –

– São especificadas usando a LDD do modelo de dados.

• Restrições Explicitas (Regras de Negocio)

– _{Não podem ser capturadas nos esquemas do}

banco de dados, nem usando LDD.

– _Exemplos:

• _{Um empregado do departamento “Financeiro” não}

pode ter a ocupação “Recepcionista”.

• _{Um empregado não pode ter um salário maior que}

(13)

Restrições Declarativas em

SQL-92

• _{DE DOMÍNIO}

• _{NOT NULL}

• _DEFAULT

• _CHECK

• _{CHAVE PRIMÁRIA}

• _UNIQUE

(14)

Restrições de domínio

• _{Refere-se ao domínio de um atributo}

– _{Especificam que o valor de cada atributo A de}

uma relação deve ser um valor atômico do domínio dom(A)

• _{Restrições de domínio são as mais}

elementares

(15)

Restrições de domínio

• _{Similar aos tipos de variáveis em linguagens de}

programação

• _{Vários atributos podem ter o mesmo domínio} • _{Avaliar quando cada tipo deve ser utilizado} • _Exemplo:

– _{o atributo idade é numérico, deve ser de domínio}

inteiro, e não do tipo character, como é o caso do atributo nome

Create table aluno

(matricula integer not null,

nome varchar(30) not null,

(16)

Restrições de domínio

• O padrão SQL-92 suporta um conjunto restrito de tipos de domínio:

– Cadeia com comprimento de caracteres fixo, com comprimento especificado pelo usuário

– Número de casas decimais

– Inteiro (conjunto finito de números inteiros)

– Data, etc

Create table aluno

(matricula integer not null,

nome varchar(30) not null,

dataNascimento date,

curso varchar(20))

Matricul

a Nome dataNascimento Curso

123 Ana Maia 20/05/1990 Matemática 234 Rafael Gomes 01/02/1992 Física

456 Carla Sousa 23/09/1990 Computação

(17)

(18)

Restrição de valor vazio

• _{O valor especial null é utilizado para}

representar valores não conhecidos ou

não aplicáveis a uma determinada tupla

– _{Restrição de valor vazio determina quando}

esse valor pode ou não ser atribuído

• Ex:

– O aluno 123 não tem nome, a tupla se refere a um aluno anônimo, o que não faz sentido no BD.

– _{Restringir o domínio do atributo nome para}

not nullMatricula₁₂₃ Nome Telefone_{(85) 32234545 Matemática}Curso

234 Rafael Gomes (85) 34820908 Física

(19)

Restrição de valor vazio

• _{Um valor de atributo pode assumir o valor}

vazio

– _Colunas_{obrigatórias}

• _{Colunas nas quais}_{não são admitidos valores vazios}

– _{Colunas opcionais}

• _{Colunas nas quais podem aparecer valores vazios}

• _{Abodagem relacional}

– _{todas colunas que compõem a}_{chave primária} devem ser obrigatórias

– _{demais chaves podem conter colunas}

(20)

Restrição de valor vazio

• Exemplo em SQL-92: – _{Create table aluno}

telefone varchar(20) curso varchar(20))

• _{Inserções que violam a restrição NOT NULL}

– Insert into aluno values(234, ´´,´32820009´,’física’)

– _{Insert into aluno values(null,’Rafael Gomes’,48-33542519}

´,’matematica’)

• _{Inserções válidas}

(21)

(22)

Restrições de chave

• Por definição todas as tuplas são distintas

• Um conjunto de atributos SK de um esquema de relação R tal que:

– para duas tuplas distintas quaisquer t1 e t2 de r(R) t1[SK] ≠ t2[SK] é uma super-chave de R

– _{Super-chave default: todos os atributos}

• Uma chave de R é uma super-chave com a

propriedade adicional de que nenhum de seus

subconjuntos também seja uma super-chave de R

(23)

Restrições de Chave

• _{Um esquema de relação pode ter mais de}

uma chave , denominadas chaves

candidatas

• _{Dentre as chaves candidatas de um}

esquema de relação, uma delas é indicada

como

chave primária

e as demais

(24)

Restrição de Chave Primária

• _{Cada relação deve possuir uma chave}

primária

– _{Restrição para que cada tupla de uma relação}

seja identificada por um valor único

• _{Pode ser simples ou composta}

Chave simples Chave composta

Create table aluno

endereco varchar(35),

PRIMARY KEY (matricula) )

Create table professor

(cpf integer not null,

regProfessor integer not null,

endereço varchar(35),

(25)

Restrições Chave Alternativa

• _Restrições_Unique_{garantem que os dados}

contidos em uma coluna ou um grupo de colunas é único em relação a todas as linhas da tabela

– _{Sintaxe: quando escrita como uma restrição de}

coluna

CREATE TABLE curso (codigoCurso integer UNIQUE, nome varchar(30));

– _{Sintaxe: quando escrita como uma restrição de}

tabela

(26)

Restrição Referencial

• _{Seja FK um conjunto de atributos de um}

esquema de relação R

1

definido sobre o

mesmo domínio dos atributos da chave

primária PK de outro esquema R

2

.

• Então, para qualquer tupla t

1

de R

1

:

– t₁[FK] = t₂[PK], onde t₂ é uma tupla de R₂

(27)

Restrição Referencial

• _{A restrição de integridade referencial pode}

ser expressa pela notação R

1

[FK] R

2

[PK],

– onde PK é a chave primária de R₂ e FK é a chave estrangeira de R1

• _Exemplos:

– _{Aluno[curso] Curso[codCurso]} – _{Professor[cpf] Ensina[prof]}

(28)

Exemplo

Matricul

a Nome Curso

123 Ana Maia 100 234 Rafael Gomes 200 456 Carla Sousa 300 678 Solon Pinheiro 800

codCurso Nome

100 Matematica 200 Física

300 Computação

Viola a restrição referencial, pois curso 800 não existe

CREATE TABLE curso

(codCurso integer NOT NULL, nome varchar(40) NOT NULL,

PRIMARY KEY (codCurso))

CREATE TABLE aluno

(matricula integer NOT NULL, nome varchar(30) NOT NULL, curso integer,

PRIMARY KEY (matricula),

FOREIGN KEY (curso) REFERENCES curso (codCurso))

Curso

(29)

Manutenção de Restrições de

Integridade

• _{O que fazer quando uma atualização viola}

uma RI?

– Proibir a atualização (“Rolling Back”) – Disparar ações corretivas.

• _{No caso das restrições declarativas, com}

exceção da

Restrição Referencial

, se um

(30)

Manutenção de Restrições de

Integridade

• As operações de atualização devem preservar as Restrições de Integridade.

nome #CI #depto dnome #depto josé 623.090 D1 pessoal D1

a) insere <Pedro, 623.090 , D1> b) insere <Maria, 678.095 , D2> c) insere <Marta, NULL , D1>

d) Remover a tupla de depto t.q. #depto=‘D1’

e) Modifique o #depto da tupla de Empregado com #CI=623.090 para #D2

(31)

Manutenção da Restrição

Referencial

• _{Que operações de atualização podem violar essa RR?}

– _{Aluno[curso] Curso[codCurso]}

• _{O que fazer quando uma atualização viola essa RR?}

– _{As ações corretivas possíveis dependem do tipo da}

operação de atualização.

– _{A ação corretiva correta é depende da semântica do}

relacionamento (é uma decisão de projeto). Matric

ula Nome Curso

codCur

so Nome

Curso

(32)

Ações Corretivas para manutenção

de RR

• _{NO ACTION}

_(default):

– _{não permite a exclusão/alteração enquanto}

houver dependência;

• Ex: só permite excluir o curso quando nenhum aluno referenciar este curso

• _{SET DEFAULT}

_{: se houver um valor}

default para a coluna da chave

estrangeira, ela recebe este valor

(33)

Ações Corretivas para manutenção

de R

1

[FK] R

2

[PK]

• Na remoção em R

₂

CASCADE

ON DELETE SET DEFAULT SET NULL

CONSTRAINT FK_curso_aluno FOREIGN KEY (curso)

(34)

Ações Corretivas para manutenção

de Aluno[curso] Curso[codCurso]

Matricul

123 Ana Maia 100 234 Rafael Gomes 200 456 Carla Sousa 300

(35)

Ações Corretivas para manutenção

de Aluno[curso] Curso[codCurso]

Matricul

123 Ana Maia _NULL 234 Rafael Gomes 200 456 Carla Sousa 300

(36)

Ações Corretivas para manutenção

de R

1

[FK] R

2

[PK]

• Na modificação em R

₂

CASCADE

ON UPDATE SET DEFAULT SET NULL

CONSTRAINT FK_curso_aluno FOREIGN KEY (curso)

(37)

Ações Corretivas para manutenção

de Aluno[curso] Curso[codCurso]

Matricul

123 Ana Maia ₄₀₄ 234 Rafael Gomes 200 456 Carla Sousa 300

(38)

Ações Corretivas para manutenção

de Aluno[curso] Curso[codCurso]

Matricul

123 Ana Maia _NULL 234 Rafael Gomes 200 456 Carla Sousa 300

(39)

(40)

Exemplo de Código Gerado pelo

Workbench

-- --- Table `mydb`.`CURSO`

--

---CREATE TABLE IF NOT EXISTS `mydb`.`CURSO` ( `codCurso` INT NOT NULL ,

`nome` VARCHAR(45) NULL , PRIMARY KEY (`codCurso`) ) ENGINE = InnoDB;

-- --- Table `mydb`.`ALUNO`

--

---CREATE TABLE IF NOT EXISTS `mydb`.`ALUNO` ( `matricula` INT NOT NULL ,

`nome` VARCHAR(45) NULL , `curso` INT NULL ,

PRIMARY KEY (`matricula`) , INDEX `fk1` (`curso` ASC) , CONSTRAINT `fk1`

FOREIGN KEY (`curso` )

REFERENCES `mydb`.`CURSO` (`codCurso` ) ON DELETE RESTRICT

ON UPDATE RESTRICT)

ENGINE = InnoDB;

Rejeitar alteração

(41)

Exemplo de Código Gerado pelo

Workbench

--

PRIMARY KEY (`matricula`) , INDEX `fk1` (`curso` ASC) ,

CONSTRAINT `fk1`

REFERENCES `mydb`.`CURSO` (`codCurso` ) ON DELETE CASCADE

ON UPDATE CASCADE) ENGINE = InnoDB;

Propagar alteração

(42)

Exemplo de Código Gerado pelo

Workbench

--

PRIMARY KEY (`matricula`) , INDEX `fk1` (`curso` ASC) ,

CONSTRAINT `fk1`

REFERENCES `mydb`.`CURSO` (`codCurso` ) ON DELETE SET NULL

ON UPDATE SET NULL) ENGINE = InnoDB;

(43)

Exemplo de Código Gerado pelo

Workbench

--

PRIMARY KEY (`matricula`) , INDEX `fk1` (`curso` ASC) , CONSTRAINT `fk1`

REFERENCES `mydb`.`CURSO` (`codCurso` ) ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

(44)

(45)

Integridade Semântica

• _{Regras de negócio da aplicação}

• _{Controle de valores válidos para os dados}

– _{estados dos dados condizentes com os}

requisitos da aplicação

– _{transições de estados corretas}

• _{Este tipo de restrição é definida}

explicitamente

– _{O usuário deve incluir dentro de cada}

transação de alteração o código

(46)

Restrições de Integridade Explícitas

• _{Uma única restrição de integridade pode ficar}

espalhada dentro de diversas transações. Exemplo:

– _{Integridade referencial entre empregado e}

departamento:

• _{Ao incluir um empregado, verificar se o departamento existe.} • _{Ao excluir um departamento, verificar se o mesmo está vazio.} • _{Ao alterar o departamento de um empregado, garantir que o}

mesmo existe.

• _{Conseqüência:}

– _{Quando restrições de integridade mudam, todos}

(47)

Restrições de Integridade Explicitas

• _Tipos

– _{Cláusula CHECK}

(48)

(49)

Cláusula

_CHECK

• _{Especificação declarativa de uma}

expressão booleana que deve ser

verdadeira para todas linhas de uma

tabela (valores de uma coluna)

(50)

Exemplo

• _{Ex.: O que a restrição a seguir representa?}

• _Resposta:

– _{somente o Bar do João pode vender cervejas}

por mais de R$2,00 CREATE TABLE Vendas ( bar CHAR(20);

cerveja CHAR(20); preço REAL;

(51)

(52)

Assertivas

• _{Especificação declarativa de uma}

expressão booleana que deve ser

verdadeira para toda a base de dados

(pode envolver várias tabelas)

• _{Implementação em SGBD é restrita (problema de}

(53)

Exemplo

• _Exemplo:

– _{“ O Salario do empregado não pode ser maior}

do que o do seu gerente.”

Create Assertion Restrição_de _salario Check ( not exists

(Select *

From EMP E, EMP M, DEPTO D

Where E.Salario > M.Salario AND

(54)

(55)

Banco de Dados Ativos

• _{Utilizam Regras E-C-A ( Evento, Condição e Ação} ).

As Regras são da Forma :

– On<Evento>

– If <condição>

– Then <ação>

• _Eventos_{-É uma expressão que especifica operação de}

atualização que causa o disparo de uma regra .

• _Condições_{-É uma consulta que contém um predicado}

que deve ser testado para executar a ação corretiva.

• _Ações_{-É uma seqüência de operações que deve ser}

(56)

Banco de Dados Ativos

• _{A maioria dos SGBDs de ponta do mercado}

implementa regras E-C-A através dos

TRIGGERS.

• _{Os Triggers podem ser usados para}

manutenção de RI complexas.

• _{SQL-2 não previu sua pradonização}

CREATE TRIGGER < nome-trigger > ON <nome-tabela>

(57)

Triggers

• _{Procedimento executado automaticamente}

na atualização de uma tabela

• _{Pode ser usado para:}

– _{Atualizações em cascata}

– _{Verificação de restrições de integridade que}

(58)

Opções de Trigger

• _{Eventos possíveis incluem}

– _{INSERT ON table} – _{DELETE ON table}

– _{UPDATE [OF column] ON table} – _{INSTEAD OF}

• _{Trigger pode ser ativado por}

– _{FOR EACH ROW}

• _{Ação é executada para cada linha modificada}

– _{FOR EACH STATEMENT}

• _{Ação é executada uma vez somente para todas linhas}

modificadas pela operação

• _{Ação de modificação pode ser executada}

(59)

Variáveis usadas em Trigger

• OLD

– _{A linha modificada antes da ocorrência do evento.}

• _NEW

– _{A linha modificada após da ocorrência do evento.}

• OLD_TABLE

– _{Uma tabela virtual que contém todas linhas}

modificadas no estado de antes da ocorrência do evento

• NEW_TABLE

– _{Uma tabela virtual que contém todas linhas}

(60)

Trigger – detalhes de

implementação

• _{Chamada recursiva}

– _{Ação de um trigger pode disparar outro} – _{Pode entrar em um laço infinito}

• _{Interação com o teste de RI pelo SGBD}

– _{Quando testar se um trigger viola uma}

restrição:

• _{Após a execução de um BEFORE trigger.}

– O trigger pode consertar a violação

• _{Antes de um AFTER trigger}

(61)

Trigger: exemplo

• _{Como os Triggers diferem dos CHECKS e}

Assertions??

CREATE TRIGGER Verifica_notas ON Notas FOR INSERT

AS

IF Nota < 5.0

(62)

Nome Curso Nota

Ana Maia 200 7.0

Ana Maia 200 4.0

Notas

INSERTED

Ana Maia 200 4.0

CREATE TRIGGER Verifica_notas ON Notas FOR INSERT

AS

IF Nota < 5.0

(63)

Manutenção de RI com Triggers

• _{Passo 1:}

– _{Determina as operações de atualização que}

podem violar a restrição (operações relevantes)

• _{Passo 2:}

– _{Determina a ação corretiva para cada}

operação relevante .

• _{Passo 3:}

– _{Cria um trigger (regra E_C_A) para cada uma}

(64)

Manutenção de Restrição de

Integridade com TRIGGERS

• _{Suponha os esquemas de relação:}

– EMPRÉSTIMO(ISBN, #copia, #título,

data-dev)

– CÓPIA(ISBN, #copia, #título, emprestado?)

• _{Suponha a RI:}

– O valor do atributo emprestado? é “sim” se o livro está emprestado, caso contrario é “não”.

• _{Que operações podem violar a restrição?}

– Inserção em EMPRÉSTIMO

– Remoção em EMPRÉSTIMO

(65)

Trigger para Inserção em EMPRESTIMO

ISBN #copia #titulo #membr

o Data_dev

1 2 1001 1001 12/02/20

10

4 2 1004 1002 12/02/20

10 EMPRESTIMO

INSERTED

4 1 1004 1001 12/02/20

10

CREATE TRIGGER Inserção-em-empréstimo ON Empréstimo

FOR INSERT AS

UPDATE copia

SET emprestado?= ´sim´ FROM copia, inserted

WHERE copia.isbn= inserted.isbn

(66)

Trigger para Remoção em EMPRESTIMO

ISBN #copia #titulo #membr

o Data_dev

1 2 1001 1001 12/02/20

10

4 2 1004 1002 12/02/20

10 EMPRESTIMO

DELETED

4 1 1004 1001 12/02/20

10

CREATE TRIGGER Remoção-em-empréstimo ON Empréstimo

FOR INSERT AS

UPDATE copia

SET emprestado?= ´não´ FROM copia, deleted

WHERE copia.isbn= deleted.isbn

(67)

(68)

Stored Procedures

• _{São comandos SQL precompilados e}

armazenados no Servidor.

• _{Podem acessar e modificar os dados.}

• _{São chamados pela aplicação}

(69)

Uso de Stored Procedures na

Manutenção de RI

• _{Procedimento Armazenado é uma coleção de comandos}

compilados que é armazena no banco de dados.

• _{O projetista de aplicação pode usar procedimentos}

armazenados para fazer a manutenção de restrições de integridade.

– _Exemplo:_{EMP(enum, depto, salario) e}

DEPTO(dnum, gerente).

“o salário de um empregado deve ser menor do que o salário do seu gerente”

Procedimentos armazenados:

PA1: Inserção de um novo empregado

PA2: Modificar o salário de um empregado

PA3: Modificar o gerente de um departamento

(70)

Stored Procedures

• _Vantagens:

– _{Mecanismo de segurança (evita manipulação}

inadequada dos dados)

– _{Aumenta performance e consistência para}

tarefas repetitivas

– _{Aceitam parâmetros}

– _{Mudanças das regras de negócio acarretam}