Restri

1
Restrições de Integridade

• Integridade de dados
• garantia de dados sempre corretos com relação ao
  domínio da aplicação
• garantia de dados consistentes (não errados)
• Violações de integridade
• falhas em transações
• interferência entre transações
• acessos não autorizados
• valores incorretos na atualização de dados
• exige controle de integridade semântica

2
Integridade Semântica

• Controle de valores válidos para os dados
• estados dos dados condizentes com os requisitos
  da aplicação
• transições de estados corretas
• O SGBD deve controlar esta integridade
• subsistema de integridade semântica

3
Subsistema de Integridade Semântica

• Funções básicas
• especificação de regras de integridade (DDL)
• monitoração das atualizações de dados
• tratamento de violações de integridade
  ocasionadas por estas operações
• Definição de uma Regra de Integridade (RI)
• para quais dados deve-se verificar a regra
• quando a regra deve ser verificada
• que ação deve ser tomada
• Garantia de independência de gerenciamento de
  integridade de dados para as aplicações

4
Classificação de RIs

• Quanto ao seu alcance
• volume de dados investigado pela RI
• Quanto ao momento de verificação
• imediato ou postergado
• RIs de transição de estado
• verificação somente quando o dado muda
• Ris associadas a eventos
• verificação independente de operações de
  atualização

5
Classificação de RIs

• Quanto ao seu alcance
• 1.1) Atinge um atributo de uma tabela
• Exemplos dados de empregados
• a) salário gt 0
• b) 16 lt idade lt 90
• c) depto Vendas,Pessoal, Finanças
• 1.2) Atinge mais de um atributo de uma tabela
• Exemplos a) tempoServiço lt idade
• b) códigoEmpregado lt gt
  códigoGerente

6
Classificação de RIs

• Quanto ao seu alcance
• 1.3) Atinge mais de uma tupla da mesma tabela
• Exemplos a) RI Entidade
• b) Salário do gerente gt Salário
  dos seus subordinados
• 1.4) Atinge tuplas de tabelas diferentes
• Exemplos a) RI Referencial
• b) Orçamento Departamento gt
  Soma
• salários pagos aos seus
  empregados

7
Classificação de RIs

• Quanto ao momento de verificação
• 2.1) Imediato
• verificação feita no momento da ocorrência de uma
  operação de atualização
• 2.2) Postergado
• verificação feita após o término da transação
• executa rollback em caso de violação
• Exemplos
• a) alteração de código dos departamentos
• d0,d1,d2,...,dn
  d1,d2,d3,...,dn1
• b) alteração do salário de vários
  empregados e
• verificação do orçamento do departamento

8
Classificação de RIs

• RIs de transição de estado
• analisa os valores novo e antigo do dado
• Exemplos a) reajustes de salário não podem
  diminuir o
• valor do salário do
  empregado
• b) solteiro casado
  desquitado
• 
  divorciado
• 
  viúvo

9
Classificação de RIs

• RIs associadas a eventos
• Exemplo tabela Empréstimos
• Empréstimos(código, nomeDevedor, valorTotal,
  valorParcela, DiaVencimento, NroParcelasAPagar)
• Evento pagamento automático
• SE DataAtual.Dia Empréstimos.DiaVencimento
• ENTÃO INÍCIO
• Empréstimos.ValorTotal ? Empréstimos.ValorTotal
  Empréstimos.ValorParcela
• Empréstimos.NroParcelasAPagar
  ?
• 
  Empréstimos.NroParcelasAPagar 1
• FIM

10
RIs em SQL

• RIs associadas à criação de tabelas
• cláusula not null
• cláusula unique
• cláusula check
• restrições de integridade de entidade e
  referencial
• Assertivas (Assertions) predicados que devem ser
  sempre verdadeiros (ativação automática)
• Gatilhos (Triggers) disparo de ações vinculadas
  à execução de uma operação específica (ativação
  automática)
• Procedimentos (Stored Procedures) podem usadas
  para verificação de integridade (ativação pela
  aplicação)

11
RIs na Definição de Tabelas

• create table Empregados (
• codEmp integer,
• nome varchar(40)not null,
• RG numeric(10) not null unique,
• idade integer check (idade between 16 and 90),
• estadoCivil char(10) check (estado_civil in
  solteiro, casado, viuvo, desquitado,
  divorciado),
• salario numeric(8,2) check (salário gt 0),
• tempoServiço integer,
• codGer integer,
• codDepto integer,
• ...

12
RIs na Definição de Tabelas

• ...
• constraint EmpPk primary key (codEmp),
• constraint EmpCodg foreign key (codGer)
• references Empregados on update cascade
  
• on delete set null,
• constraint EmpDept foreign key (codDepto)
• references Departamentos on update cascade
• on delete no action,
• constraint IdadeTS check(tempoServico lt idade),
• constraint EmpGer check(codEmp lt gt codGer),
• constraint SalarioGerente
• check(salario lt (select salario
  
  from Empregados e
  
  where e.codEmp codGer))
• )

13
RIs na Definição de Tabelas

• create table Departamentos (
• codDepto integer,
• nome varchar(20) check (nome in Vendas,
  Pessoal, Finanças, Administrativo),
• andar integer check (andar between 1 and 10),
• orçamento numeric(20,2),
• constraint DeptPk primary key (codDepto),
• constraint ControleOrç
• check( orçamento gt
  
• (select sum(salário)
  
• from Empregados e
  
• where e.codDepto codDepto))
• )

14
Assertiva - Assertion

• Declaração de predicado que deve ser sempre
  verdadeiro
• não está vinculado a uma tabela específica
• geralmente é uma RI que envolve várias tabelas
• independe da operação de atualização
• Exemplo
• create assertion ControleDespesas
• check ( (select sum(ValorTotal)
• from Empréstimos)
• (select sum(Orçamento)
• from Departamentos)
• lt 50000000.00) )
• esta assertiva é verificada mesmo se alguma das
  tabelas estiver vazia

15
Gatilho - Trigger

• Execução automática de modificações no BD para
  garantia de integridade
• Princípio de funcionamento
• evento-condição-ação
• Especificação de um trigger
• evento que o dispara comando atualização
• condição (opcional) predicado
• ação(ões) a realizar comandos SQL ou execução de
  procedimentos (stored procedures)

16
Exemplos de Gatilho

• a) create trigger EmpréstimoLiquidado
• after update on Empréstimos
• (delete from Empréstimos
• where valorTotal 0)
• b) create trigger SalárioInjusto
• after insert, update on Empregados
• if exists (select
• from Empregados
• where estadoCivil casado and
  salário lt 250.00)
• begin
• print salário injusto para o estado civil!
• exec CorrigeSalário
• end
• c) create trigger AumentoSalário
• before update on Empregados
• referencing NEW salário as SalárioNovo when
• salário gt SalárioNovo
• (Rollback Transaction)

17
Transação

• Princípio do tudo ou nada
• Útil para a garantia de algumas RIs
• Exemplo
• todo departamento deve ter pelo menos um
  empregado lotado nele
• begin transaction
• insert into Departamentos
• values(1,Vendas, ...)
• insert into Empregados
• values(1, João, ..., 1)
• commit transaction

codDepto
18
Procedimento Stored Procedure

• Conjunto de comandos SQL mantido no BD
• Pode ser utilizado para controle de RI
• execução a cargo da aplicação ou usuário
• Exemplo
• débitos diários de parcelas de empréstimos
• create procedure PagaEmpréstimo as
• update Empréstimos
• set ValorTotal ValorTotal ValorParcela,
• NroParcelas NroParcelas 1
• where DiaVencimento day(getdate())
• Chamada de um storedprocedure
• exec PagaEmpréstimo

19
Suporte a RIs em SQL

• RIs quanto ao alcance de dados
• cláusula check asserts e triggers
• RIs quanto ao momento da verificação
• imediato (default)
• postergado (SQL padrão)
• set integrity for Departamentos off
• ...
• set integrity for Departamentos immediate
  checked
• RIs de transição de estado
• triggers (com referência a valores novos/antigos)
• RIs baseadas em eventos
• stored procedures