Apostila - BDII

Banco de Dados II
Profa MSc. Tanisi Carvalho
Profa MSc. Simone Vicari
Notas de Aula
Banco de Dados II
Sumário
1 GERÊNCIA DE TRANSAÇÕES ................................................................................................................. 3
1.1 PROPRIEDADES DA TRANSAÇÃO ................................................................................................................ 4
1.2 ESTADOS DA TRANSAÇÃO ......................................................................................................................... 4
2 CONTROLE DE CONCORRÊNCIA ......................................................................................................... 7
2.1 PROBLEMAS ASSOCIADOS À EXECUÇÃO CONCORRENTE DE TAS................................................................ 8
2.2 MECANISMOS PARA CONTROLE DE CONCORRÊNCIA ................................................................................. 9
2.2.1 Bloqueio simples ................................................................................................................................ 9
2.2.2 Bloqueio de Duas Fases (2PL) ........................................................................................................ 10
2.3 DEADLOCK ............................................................................................................................................... 12
2.3.1 Prevenção de Deadlock ................................................................................................................... 12
2.3.2 Detecção e Recuperação de Deadlock ............................................................................................ 13
2.4 GRANULARIDADE MÚLTIPLA .................................................................................................................. 13
3 RECUPERAÇÃO APÓS FALHAS ........................................................................................................... 17
3.1 PROJETO DE UM SUBSISTEMA DE RECOVERY........................................................................................... 17
3.2 PROCEDIMENTOS PARA RECUPERAÇÃO DE FALHAS BASEADOS EM LOG ................................................ 18
3.2.1 Checkpoints ..................................................................................................................................... 20
4 SEGURANÇA EM SGBDS ........................................................................................................................ 22
4.1 AUTORIZAÇÃO DE ACESSO ...................................................................................................................... 23
4.1.1 Criando usuários ............................................................................................................................. 23
4.1.2 Concedendo/Revogando privilégios de acesso ................................................................................ 23
4.1.3 Roles ................................................................................................................................................ 24
4.1.4 Sinônimos ........................................................................................................................................ 25
5 VISÕES ........................................................................................................................................................ 26
5.1 USO DE VISÕES ........................................................................................................................................ 27
5.2 PROBLEMAS ASSOCIADOS ÀS VISÕES ....................................................................................................... 28
6 RESTRIÇÕES DE INTEGRIDADE ......................................................................................................... 29
6.1 CLASSIFICAÇÃO DAS RESTRIÇÕES DE INTEGRIDADE................................................................................ 29
6.1.1 Segundo seu alcance........................................................................................................................ 29
6.1.2 Segundo o momento do teste ........................................................................................................... 29
6.1.3 RIs que regulamentam a atualização de valores ............................................................................. 29
6.1.4 RIs que devem ser testadas na ocorrência de eventos externos ...................................................... 30
6.2 RESTRIÇÕES DE INTEGRIDADE NO MODELO RELACIONAL ....................................................................... 30
6.2.1 Restrição de domínio ....................................................................................................................... 30
6.2.2 Restrição de valor nulo (NULL) ...................................................................................................... 31
6.2.3 Restrição de chave primária............................................................................................................ 31
6.2.4 Restrição de integridade referencial ............................................................................................... 31
6.3 ESPECIFICAÇÃO DE RESTRIÇÕES DE INTEGRIDADE .................................................................................. 31
7 ANEXOS ...................................................................................................................................................... 33
7.1 ARQUITETURA BÁSICA DE UM SGBD...................................................................................................... 33
7.2 ARQUITETURA DO GERENCIADOR DE BANCO DE DADOS ......................................................................... 34
-2-
Banco de dados II
1 Gerência de Transações
Um conceito bastante importante no contexto de sistemas gerenciadores de banco de
dados (SGBDs) é o conceito da transação:
 A transação é uma unidade de trabalho do usuário (da aplicação) que é atômica do
ponto de vista da aplicação [DAT 81]
 A transação é uma unidade de execução de programa que acessa e, possivelmente,
atualiza vários itens de dados. Geralmente, é o resultado da execução de um programa
escrito em uma linguagem de manipulação de dados de alto nível ou em uma linguagem
de programação (por exemplo, SQL, COBOL, C ou Pascal) [SIL 99]
A partir dos dois conceitos acima é importante definir que:


a transação sempre leva o banco de dados de um estado inicial consistente a
um outro final, também consistente;
a transação é atômica, ou seja, a transação deve ser tratada pelo SGBD como
uma unidade única, ou todas as alterações devem estar no BD ou nada deve
acontecer.
Normalmente, as transações definidas em linguagens de manipulação apresentam
comandos para marcar o início e o fim da transação (begin transaction/end transaction).
A Figura 1.1-1 apresenta um exemplo de uma transação que retira R$ 100,00 de uma
Conta Bancária. A Figura 1.1-2 apresenta o mesmo exemplo em SQL.
Begin transaction
Read(Cta=x, saldo)
Saldo = saldo - 100 (valor informado)
Write(cta=x, saldo)
end transaction
Figura 1.1-1: Exemplo de transação
Como pode ser observado na Figura 1.1-2, não aparece o comando de início de
transação. O padrão SQL92 define que uma transação inicia a partir do primeiro
comando SQL, todos os comandos subseqüentes são considerados da mesma transação
até que seja encontrado um comando de fim de transação (COMMIT ou ROLLBACK).
UPDATE CONTA
SET saldo = saldo - 100
WHERE nro_conta = x;
COMMIT;
Figura 1.1-2: Exemplo de transação em SQL
Capítulo 1: Gerência de Transações
Banco de Dados II
1.1 Propriedades da Transação
Para garantir a integridade do banco de dados é necessário que o sistema de banco
de dados mantenha as seguintes propriedades da transação:




Atomicidade: o programa que representa a transação deve, ou executar por
completo e com sucesso sobre o banco de dados, ou deve parecer nunca ter
executado (Lei do Tudo-Ou-Nada). Essa propriedade é, normalmente, garantida
pelo SGBD, através de seu componente de recuperação após falhas;
Consistência: a transação só pode realizar operações corretas, do ponto e vista da
aplicação, sobre o banco de dados. Isto é, a transação não pode transgredir
qualquer restrição de integridade (RI) declarada ao SGBD (restrições de
integridade de chave primária e restrições de integridade de chave estrangeira.
Isolamento: em um ambiente multiusuário (multiprogramado), a execução de
uma transação não deve ser influenciada pela execução de outras. Essa
propriedade é, normalmente, garantida pelo SGBD, através do módulo de
Controle de Concorrência (scheduler);
Durabilidade: os resultados de uma transação que termina com sucesso devem
permanecer inalterados, no banco de dados, até que outra transação os altere e
também termine com sucesso. Isto é, os resultados de transações que terminam
com sucesso devem sobreviver a falhas ( de transação, de sistema ou de meio de
armazenamento. A Durabilidade também é garantida pelo componente de
recuperação após falhas.
As transações que seguem estas quatro propriedades são também conhecidas como
transações ACID.
1.2 Estados da Transação
Uma transação pode concluir sua execução com sucesso ou não. Quando uma
transação não conclui com sucesso, a propriedade da atomicidade deve ser garantida, isso
significa que todas as modificações feitas sobre o banco de dados, por essa transação,
devem ser desfeitas (rollback). Se, no entanto, a transação conclui com sucesso seus
efeitos estão materializados no banco de dados e ela é dita committed.
Podemos dizer que uma transação, ao longo da sua execução, passa por vários
estados:





ativa: é o estado inicial, uma transação fica nesse estado enquanto estiver
executando;
em efetivação: após a execução da última declaração;
em falha: após a descoberta de que a execução normal não poderá se realizar;
abort: depois que a transação foi desfeita e o banco de dados foi restabelecido ao
estado anterior ao início da execução da transação;
commit: após a conclusão com sucesso da transação.
A Figura 1.3 apresenta o diagrama de estados correspondente a uma transação.
Capítulo 1: Gerência de Transações
-4-
Banco de Dados II
Em
efetivação
Commit
Ativa
Em
Falha
Abort
Figura 1.3: Diagrama de Estados da Transação
Enquanto uma transação estiver executando ela estará no estado ativa. No momento
em que a transação concluir a execução da última declaração ela entra no estado de
efetivação. Uma transação não vai direto para o estado de commit, porque o SGBD
precisa concluir uma série de operações como, por exemplo, atualizar arquivos de log,
atualizar o banco de dados, para garantir as propriedades de atomicidade e durabilidade.
O estado de efetivação é uma preparação para o commit. Pode ocorrer que durante esse
estado ocorra alguma falha e a transação não possa ser concluída normalmente. Nessa
situação, a transação passa de um estado de efetivação para um estado em falha onde
todos os efeitos da transação deverão ser desfeitos. Uma transação passará para o estado
de commit quando todas as operações necessárias estiverem concluídas. A partir desse
momento a aplicação teria conhecimento de que a transação realmente concluiu. O
estado abort se dará quando uma vez detectada a falha o estado do banco de dados,
anterior à falha, tenha sido estabelecido.
A Figura 1.4 apresenta um esquema mostrando a relação existente entre o programa
de aplicação e a execução de uma transação pelo SGBD (Gerente de Transações).
Capítulo 1: Gerência de Transações
-5-
Banco de Dados II
Ações da Aplicação
Ações do Gerente
de Transações
Begin
Transaction
(BOT)
Inicia ações padrão
para UNDO. Garantia de
atomicidade da TA
Seqüência de operações
DML associadas a RIs
Execução das operações DML
e teste de Ris imediatas, com
mensagem de erro
End of
Transaction
(EOT)
Teste de Ris Postergadas
com mensagem de erro e
UNDO
Ações padrão para REDO.
Garantia de durabilidade
Avisa aplicação/usuário
do final da TA
Figura 1.4: Ações da Aplicação x Ações do Gerente de Transações
Uma transação começa a executar quando uma instrução Begin Transaction é
encontrada. O gerente de transações deve então executar um conjunto de ações para
garantir a atomicidade de uma transação no caso de uma falha. A aplicação continua
executando as suas operações. estas operações são processadas pelo SGBD, as restrições
de integridade associadas são testadas. A aplicação envia a declaração de fim de
Transação. A partir desse momento a transação está em estado de efetivação, o SGBD
testa as restrições de integridade postergadas, que só podem ser testadas no fim,
executada as ações de REDO para garantir a durabilidade e, por fim, envia um aviso a
aplicação de que a transação foi concluída com sucesso. Nesse ponto, a transação passa a
ser commit e seus efeitos estão materializados no banco de dados. No caso de uma falha,
o sistema precisa executar um conjunto de operações para garantir a atomicidade e a
transação é abortada.
Bibliografia utilizada no capítulo:
[BER 87] BERNSTEIN, Philip A; HADZILACOS, Vassos; GOODMAN, Nathan. Concurrency Control
and Recovery in Database Systems. Reading:Addison-Wesley, 1987.
[DAT 81] DATE, Chris. J. An Introduction to Database Systems. Reading:Addison-Wesley, 1983, p. 112123.
[IOC 95] IOCHPE, Cirano. Notas de Aula CMP97 – Sistemas de Banco de Dados. Curso de PósGraduação em Ciência da Computação, 1995.
[SIL 99] SILBERSCHATZ, Abraham.; KORTH, Henry F.; SUDARSHAN, S. Sistema de Banco de Dados.
3ª edição., São Paulo: Makron Books, 1999.
Capítulo 1: Gerência de Transações
-6-
Banco de dados II
2 Controle de Concorrência
O controle de concorrência é a atividade de coordenar a ação de processos que
operam em paralelo, acessam dados compartilhados e, portanto, interferem uns com os
outros.
Quando duas ou mais transações executam concorrentemente, suas operações podem
ser processadas, pelo sistema de computação, de forma intercalada (interleaving). A
Figura 2-1 apresenta um exemplo da execução serial das transações T1, T2 e T3 e da
execução interleaving das mesmas transações.
T1
T1
T2
T2
T3
T1
T3
T2
T1
tempo
Figura 2-1: Exemplos de execuções das transações T1, T2 e T3
A execução concorrente das transações permite um melhor aproveitamento dos
recursos do sistema. A execução de uma transação geralmente envolve operações de E/S
liberando o uso da CPU, tornando-a ociosa. Esse tempo de CPU pode ser destinado à
execução de outra transação. A idéia central é manter em paralelo o processamento da
E/S e da CPU melhorando, assim, o throughput do sistema (número de transações
executadas por unidade de tempo).
Um outro aspecto é que em um sistema podem existir transações longas e transações
curtas. Se a execução for seqüencial, então pode ocorrer da transação curta ter que
aguardar pela transação longa acarretando em um tempo de resposta maior (tempo que
uma transação leva para começar a responder à solicitação realizada). A Figura 2-1
apresenta um exemplo dessa situação, a transação T3 tem que aguardar a execução de T1
e T2; já na execução interleaving, o tempo de T3 é melhorado.
Na Figura 2-2 temos a arquitetura do SGBD do ponto de vista de gerência de
transações:


gerente de transações: executa as operações do banco de dados e das
transações, passando-as para o scheduler;
scheduler: controla a ordem da execução das operações read, write, commit e
abort de diferentes transações que são executadas, garantindo o isolamento. As
ações do scheduler podem ser: executar, rejeitar ou postergar a operação que
chega para o scheduler;
Capítulo 2: Controle de Concorrência
-7-
Banco de dados II


gerente de recuperação: responsável pela finalização das transações (garante a
atomicidade e a durabilidade);
gerente de buffers: armazena parte do banco de dados em memória durante a
execução das transações. É responsável pela tansferência dos dados entre o
disco e memória principal.
T1
T2
T3
....
Tn
Gerente de
Transações
Scheduler
Gerente de Dados
Gerente de
Recuperação
Falhas
Gerente de
Buffers
Banco de Dados
/LOG
Figura 2-2 Gerência de Transações
2.1 Problemas associados à execução concorrente de TAs
Quando se tem transações executando concorrentemente pode-se levar o banco de
dados a um estado inconsistente.
Problemas que podem existir sem um mecanismo de controle de concorrência:
Atualização perdida: ocorre quando uma atualização de uma transação é
sobreescrita pela atualização de outra transação, fazendo com que uma delas seja
perdida;
a) execução serial
T1
read(x)
x=x-10
write(x)
read(y)
Y=y+10
write(y)
commit
b) execução não serial
T1
T2
read(x)
x=x-10
read(x)
x=x+100
write(x)
Commit
write(x)
read(y)
y=y+n
write(y)
commit
T2
read(x)
x=x+100
write(x)
commit
Capítulo 2: Controle de Concorrência
-8-
Banco de dados II
TAs não recuperáveis:
Uma transação não pode se basear em dados de uma transação que ainda não foi
efetivada (princípio da recuperabilidade).
T3
read(x)
x=x-10
write(x)
T4
read(x)
x=x+100
write(x)
commit
read(y)
rollback
T4 considerou T3
concluída com
sucesso o que não
é verdadeiro!!!
2.2 Mecanismos para Controle de Concorrência
Os mecanismos de controle de concorrência têm por objetivo permitir a
concorrência garantido o princípio da serializabilidade. Serão estudados três
implementações de mecanismos de controle de concorrência:



marcas de tempo (timestamp);
versões.
bloqueios (locking);
2.2.1 Bloqueio simples
Consiste em estabelecer bloqueios sobre o dado a ser acessado pela transação, de
modo a restringir o acesso a este dado.
A unidade de bloqueio, geralmente, é a de registro. Quanto menor for a
granularidade de bloqueio, maior o grau de concorrência.
São definidos dois tipos de bloqueios:


Exclusivo (E): se uma transação obteve um bloqueio E em um dado, então a
transação pode ler e escrever neste dado.
Compartilhado (C): se uma transação obteve um bloqueio C em um dado, esta
transação e as demais podem somente ler este dado
Matriz de Compatibilidade de Bloqueios:
C
E
Capítulo 2: Controle de Concorrência
-9-
Banco de dados II
C
E

X
X
X
Quando uma transação Ti deseja acessar um dado, ela deve primeiro bloqueá-lo com
um dos dois tipos de bloqueio, dependendo da operação desejada. Se o dado já está
bloqueado com tipo incompatível, Ti precisa esperar até que todos os bloqueios
incompatíveis sejam liberados.
Tão logo o dado deixe de ser utilizado o bloqueio deve ser liberado.
T1
lock_E(x)
read(x)
x=x-1
write(x)
unlock(x)
T2
lock_E(x)
espera
...
...
...
lock_E(x)
x=x+100
write(x)
unlock(x)
Um dos problemas associados a este protocolo é o fato dos bloqueios serem
liberados muito cedo. Observe o exemplo abaixo:
T3
T4
sum=0
lock_C(x)
read(x)
unlock(x)
sum=sum+x
lock_C(y)
lock_E(x)
x=x-10
write(x)
unlock(x)
commit
read(y)
unlock(y)
sum-sum+y
O protocolo de bloqueio simples não garante o isolamento por completo. No
exemplo acima, caso T4 seja abortada T3 foi efetivada com valores de x que não
existiram no banco de dados (atomicidade para T4).
2.2.2 Bloqueio de Duas Fases (2PL)
O mecanismo de bloqueio de duas fases (Two-Phase Lock), garante a
serializabilidade tratando os bloqueios em duas fases:

fase de aquisição (growing phase): nesta fase a transação apenas adquire os
bloqueios;
Capítulo 2: Controle de Concorrência
- 10 -
Banco de dados II

fase de liberação (shrinking phase): nesta fase a transação somente libera
bloqueios. A partir do momento que o primeiro bloqueio é liberado nenhum
bloqueio pode ser adquirido pela transação.
O Strict 2PL garente execuções concorrentes serializáveis, recuperáveis, evita o
cascating abort e liberando os bloqueios apenas no commit ou rollback.
O mecanismo funciona de forma análoga ao mecanismo de bloqueios simples,
diferenciando-se apenas pela forma como são tratadas a aquisição e a liberação de
bloqueios.
T1
read(x)
x=x-10
T2
T1
Lock_E(x)
x=x-10
T2
read(x)
x=x+100
write(x)
commit
write(x)
read(y)
y=y+10
write(y)
commit
lock_E(x)
espera
...
...
...
...
...
...
...
...
...
continua ...
write(x)
lock_E(y)
read(y)
y=y+10
write(y)
commit
unlock(x,y)
A implementação do mecanismo 2PL tem algumas variações conforme mostra a
Figura 2-3. As variações caracterizam-se pela forma como são implementadas as fases de
aquisição e liberação de bloqueios.
2PL Estrito
2PL Básico
BOT
EOT
BOT
2PL Conservativo
EOT
BOT
EOT
Figura 2-3: Variações do mecanismo 2PL
Na versão 2PL Básico, os bloqueios são liberados à medida que a transação deixa de
utilizá-los. Isso pode acarretar num problema conhecido como aborto em cascata
(cascading abort). Uma vez que o bloqueio foi liberado, este dado pode ser utilizado por
qualquer outra transação, assim se a transação não concluir com sucesso, outras
transações terão se baseado em um dado intermediário levando a uma inconsistência do
banco de dados.
Capítulo 2: Controle de Concorrência
- 11 -
Banco de dados II
A implementação estrita resolve esta situação liberando os bloqueios apenas no final
e em um momento único, garantindo com isso que o problema de aborto em cascata não
ocorra. Esta implementação pode ter ainda uma otimização, permitindo que os bloqueios
compartilhados sejam liberados deixando apenas os bloqueios exclusivos. A
implementação estrita é a mais utilizada comercialmente.
Uma característica dos protocolos baseados em bloqueios são as situações de
impasse (deadlocks). Uma situação de impasse ocorre quando uma transação está a
espera de um dado que está bloqueado por uma segunda transação e esta está a espera de
um dado segurado pela primeira. As situações de deadlock são indesejáveis, pois
degradam o desempenho. Este assunto será detalhado na próxima sessão.
A implementação conservativo evita que o deadlock ocorra, pois solicita todos os
dados de antemão. Esta abordagem, no entanto, tende a diminuir o throughput do
sistema, visto que uma transação não vai utilizar todos os dados ao mesmo. Além disso, é
difícil saber quais dados serão utilizados por uma transação. Esta implementação pode
levar a outro problema conhecido como postergação definida, situação em que uma
transação fica a espera de um evento que nunca venha a ocorrer.
Uma outra característica dos protocolos de bloqueios é que alguns sistemas
permitem iniciar o bloqueio como compartilhado e depois passar este bloqueio para o
modo exclusivo (lock upgrade/downgrade). A vantagem disso é que pode-se retardar a
aquisição de bloqueios no modo exclusivo, permitindo assim um maior grau de
concorrência. Tanto para o upgrade ou downgrade a matriz de compatibilidade de
bloqueios deve ser obedecida. Isso pode acarretar em situações de deadlock.
2.3 Deadlock
Uma situação que pode ocorrer em sistemas concorrentes é conhecida por impasse
ou deadlock (abraço mortal). O deadlock está associado a utilização de recursos
compartilhados que só podem ser utilizados de forma exclusiva, no caso de banco de
dados, os dados utilizados pelas transações.
Um sistema está em deadlock sempre que uma transação Ti está esperando por um
item de dado que está bloqueado por uma transação Tj e Tj está esperando por um item
de dado que está bloqueado por Ti.
Há dois métodos para resolver um deadlock:


utilizar alguma técnica que evite a sua ocorrência;
possuir mecanismos para detecção e recuperação de deadlock.
2.3.1 Prevenção de Deadlock
Há duas abordagens para a prevenção de deadlock. Uma garante que nenhum ciclo
de espera poderá ocorrer pela ordenação de solicitações de bloqueio, ou pela aquisição de
todos os bloqueios juntos. A outra faz com que a transação seja refeita, em vez de esperar
por um bloqueio, sempre que a espera possa potencialmente decorrer em deadlock.
Capítulo 2: Controle de Concorrência
- 12 -
Banco de dados II
Pela primeira abordagem, cada transação é obrigada a bloquear todos os itens de
dados antes de sua execução. Além disso, ou todos os dados são bloqueados de uma só
vez ou nenhum será. Há duas desvantagens nesse protocolo: a dificuldade de prever,
antes da transação começar, quais itens de dados deverão ser bloqueados. Segundo, a
utilização do item de dado pode ser bastante reduzida já que os dados podem ser
bloqueados e não ser usados por um longo período de tempo.
Pela segunda, são utilizados timeouts para decidir se uma transação deve ficar em
wait ou deve ser refeita (REDO). Se o tempo de espera ultrapassar um valor x, a
transação é abortada independente de ter ocorrido o deadlock ou não.
2.3.2 Detecção e Recuperação de Deadlock
As técnicas de detecção e recuperação são utilizadas quando o subsistema de
controle de concorrência não possui nenhum mecanismo de prevenção de deadlock.
Normalmente a detecção é feita pela geração de um grafo de espera, onde a ocorrência de
ciclos indica a presença de deadlock.
Para recuperar do deadlock é necessário que uma ou mais transações sejam
selecionadas como vítimas para serem abortadas. Para a seleção das vítimas podem ser
utilizados critérios como, por exemplo, a transação mais recente, o quanto falta para uma
transação terminar, quantas atualizações a transação realizou ou até mesmo nenhum
critério (por facilidades de implementação).
Um problema é decidir quando o algoritmo de detecção deve ser acionado. Se os
intervalos forem curtos, muito overhead. Se forem intervalos longos, pode-se levar muito
tempo para verificar que um deadlock ocorreu.
Grafo de espera:




é criado um nó para cada transação do sistema
se Ti está esperando por um dado utilizado por Tj, é criada uma borda Ti  Tj
quando o dado é liberado, a borda é removida
ocorre um dealock quando o grafo contiver um ciclo
T1
T2
2.4 Granularidade Múltipla
Os exemplos de bloqueios apresentados até agora se referiam a bloquear um registro
por vez. Existe, porém, situações em que é vantajoso bloquear diversos itens de dados,
tratando-os como uma unidade de acesso do que bloquear um registro. Por exemplo:

atualização de todos os dados de um arquivo;
Capítulo 2: Controle de Concorrência
- 13 -
Banco de dados II

leitura de todos os dados do banco de dados;
Nessas situações é mais interessante bloquear todo o arquivo, pois em uma única
solicitação todo o arquivo estará bloqueado e o tempo necessário para realizar os bloqueios
(de cada registro) é evitado. Em contrapartida, se a transação necessita de apenas um
registro, bloquear todo o arquivo é desnecessário e, também, elimina a concorrência.
É importante, então, que o sistema permita definir múltiplos níveis de granularidade
de bloqueio. A granularidade de bloqueio é, então, a porção de itens de dados que pode ser
bloqueada por uma transação. As granularidades mais usuais são:
 registro (tupla);
 página;
 arquivo (tabela).
Isso pode ser implementado através de uma árvore de granularidade, onde cada
nodo representa a porção de dados (grão) que está sendo bloqueada e existe uma hierarquia
entre os nodos. Observe a Figura 2-4:
BD
A2
A1
Arq a
ra1
...
Arq b
ran
rb1
...
Arq c
rbn
rc1
...
rcn
Figura 2-4: Árvore de Granularidades
Um banco de dados (BD) pode ser dividido em áreas (A). Cada área pode ser
dividida em um ou mais arquivos e cada arquivo possui um ou mais registros.
Princípio do bloqueio de grãos:


quando uma transação bloqueia um determinado grão (ou nodo), ela bloqueia
também todos os nodos filhos deste grão no mesmo modo de bloqueio;
quando uma transação bloqueia um grão, todos os seus ancestrais devem ser
bloqueados intencionalmente no mesmo modo de bloqueio.
Modos de bloqueio:

compartilhado (C): o nodo e toda a sua subárvore estão bloqueados
explicitamente no modo compartilhado;
Capítulo 2: Controle de Concorrência
- 14 -
Banco de dados II




exclusivo (E): o nodo e toda a sua subárvore estão bloqueados explicitamente
no modo exclusivo;
compartilhado intencional (CI): um nodo com este bloqueio significa que
algum bloqueio compartilhado explícito está sendo mantido na sua subárvore;
exclusivo intencional (EI): um nodo com este bloqueio significa que algum
bloqueio exclusivo explícito está sendo mantido na sua subárvore;
compartilhado e exclusivo intencional (EI): um nodo está bloqueado
explicitamente no modo compartilhado e algum nodo na sua subárvore está
bloqueado explicitamente no modo exclusivo;
CI
EI
C
CEI
E
CI
V
V
V
V
F
EI
V
V
F
F
F
C
V
F
V
F
F
CEI
V
F
F
F
F
E
F
F
F
F
F
Regras:
1) respeitar a matriz de compatibilidade dos modos de bloqueio
2) a raiz da árvore precisa ser bloqueada primeira e pode ser bloqueada em
qualquer modo
3) um nodo n pode ser bloqueado por Ti no modo C ou CI apenas se os pais de n
estão correntemente bloqueados por Ti no modo EI ou CI
4) Um nodo n pode ser bloqueado por Ti no modo E, CEI, ou EI apenas se os pais
de n estão correntemente bloqueados por Ti no modo EI ou CEI
5) Ti pode bloquear um nodo apenas se ele não desbloqueou nenhum nodo antes
(segue a técnica de bloqueio de duas fases)
6) Uma Ti pode desbloquear um nodo apenas se nenhum dos filhos estiver
bloqueado por Ti.
O protocolo de granularidade múltipla exige que os bloqueios sejam feitos de cima
para baixo (top-down – da raiz para as folhas), enquanto a liberação deve ser de baixo para
cima (bottom-up – das folhas para a raiz).
Esse protocolo aumenta a concorrência e reduz o overhead por bloqueio. Isso é
particularmente útil em aplicações que misturam:
Capítulo 2: Controle de Concorrência
- 15 -
Banco de dados II


Transações curtas que mantêm acesso em poucos itens de dados;
Transações longas que produzem relatórios a partir d eum arquivo ou de um
conjunto de arquivos.
Os seguintes fenômenos podem ocorrer:
 leitura suja (dirty reads): ocorre quando uma transação tem acesso aos dados
modificados por uma transação que ainda não concluiu, ou seja, a transação está
acessando dados intermediários.
 Leituras não repetidas (Nonrepeatable Read): A TA faz a mesma consulta em
vários momentos e encontra valores diferentes que foram modificados ou deletados.
 Leitura fantasma (Phantom reads): A TA faz a mesma consulta com uma
determinada condição que retorna um conjunto de valores, esta mesma consulta é
executada novamente e retorna mais linhas, que foram adicionadas por TAs commited e
que satisfazem a condição.
Bibliografia utilizada no capítulo:
[BER 87] BERNSTEIN, Philip A; HADZILACOS, Vassos; GOODMAN, Nathan. Concurrency Control
and Recovery in Database Systems. Reading:Addison-Wesley, 1987.
[CON 98] CONNOLLY, Thomas; BEGG, Carolyn. Database Systems – a practical approach to design,
implementation and management. Harlow:Addison-Wesley, 1998.
[IOC 95] IOCHPE, Cirano. Notas de Aula CMP97 – Sistemas de Banco de Dados. Curso de PósGraduação em Ciência da Computação, 1995.
[SIL 99] SILBERSCHATZ, Abraham.; KORTH, Henry F.; SUDARSHAN, S. Sistema de Banco de
Dados. 3ª edição., São Paulo: Makron Books, 1999.
Capítulo 2: Controle de Concorrência
- 16 -
Banco de dados II
3 Recuperação Após Falhas
Diversas falhas podem ocorrer em um sistema de computador como, por exemplo,
queda de energia, falha na unidade de armazenamento físico, falha no programa de
aplicação. Tais falhas podem tornar os dados armazenados no banco de dados
inconsistentes, fazendo com que testes e procedimentos sejam incorporados ao SGBD
para tratamento dessas falhas. O módulo do SGBD responsável por essa tarefa é
chamado de recuperação após falhas ou recovery.
O objetivo do subsistema de recovery é levar o banco de dados a um estado
consistente após uma falha que o tenha deixado em um estado não consistente. Para que
isso ocorra, todo o sistema de recovery está baseado na redundância de informações.
Essa redundância normalmente é obtida a partir das seguintes hierarquias de
armazenamento:



Arquivos de log: correspondem a arquivos que mantém um histórico das
transações executadas. Contém as informações necessárias para reconstruir o
estado mais recente do database buffer e, normalmente, estão armazenados em
disco. Suporta falhas de sistema;
Archive log: é uma cópia de um estado consistente do banco de dados. Suporta
falhas de mídia (disco). É armazenado em discos ou fitas magnéticas.
Arquivos de backup: correspondem às cópias de segurança. Podem estar
armazenadas em disco ou fita magnética e armazenam o conteúdo de um banco
de dados consistente em um dado período do tempo.
3.1 Projeto de um Subsistema de Recovery
O projeto de um subsistema de recovery deve levar em consideração:



Meios de armazenamento de dados;
Tipos de falhas e como estas falhas afetam a integridade dos dados
armazenados;
Procedimentos para recuperação destas falhas.
Os meios de armazenamento podem ser divididos em:


Meio de armazenamento volátil: caracteriza-se pela perda dos conteúdos
armazenados em caso de falta de energia. Por exemplo, a memória principal;
Meio de armazenamento não volátil (físico): caracteriza-se por manter o
conteúdo armazenado, mesmo em caso de falta de energia. Exemplo: memória
secundária (discos e fitas magnéticas).
Com relação às falhas podemos caracterizar três tipos básicos:

Falha de transação: erro detectado pela própria transação. Exemplo: overflow,
divisão por zero, violação de proteção de memória. Qual o meio de
Capítulo 4: Segurança em SGBDs
- 17 -
Banco de dados II


armazenamento comprometido? Nenhum! Qual a ação a ser executada?
Desfazer a transação.
Falha de sistema: interrupção do sistema. Exemplo: queda de luz, falha no
sistema operacional. Meio de armazenamento comprometido: volátil. Ação:
desfazer as transações em execução no momento da falha que não foram
concluídas.
Falha no meio físico: perda total ou parcial do meio físico. Exemplo: falha no
cabeçote de gravação do disco, erro de hardware. Meio de armazenamento
comprometido: físico. Ações: acionar a última cópia de segurança e refazer
transações executadas com sucesso após a última cópia.
A partir dos tipos de falhas acima apresentados é possível identificar quatro ações a
serem implementadas pelo mecanismo de recovery:




transaction UNDO: desfaz os efeitos de uma única transação, garantindo a
atomicidade (ou a transação é concluída com sucesso e seus efeitos são
materializados no banco de dados, ou é como se a transação nunca tivesse
existido); - Falha de Transação
global UNDO: desfaz os efeitos de todas as transações atualmente sendo
executadas, garantindo a atomicidade de um conjunto de transações; - Falha de
sistema
partial REDO: refaz os efeitos de um conjunto de transações que terminaram
com sucesso (committed), os quais talvez não tenham sido salvos no BD;
garante durabilidade - Falha de sistema
global REDO: refaz os efeitos de um conjunto de transações que terminaram
com sucesso (committed), independente do meio onde estes efeitos estejam
armazenados. – Falha de disco
A implementação das operações acima depende, no entanto, da forma como alguns
aspectos do SGBD são implementados. Quatro aspectos devem ser analisados:




tipo de propagação dos dados: como os dados são transferidos do buffer de
dados para o banco de dados;
gerência dos buffers: como determinar que buffers de dados serão liberados para
outros dados;
tratamento de EOT (end-of-transaction): quando uma transação termina com
sucesso que tratamento recebem os dados que ainda estão nos buffers de dados;
checkpoints: técnica utilizada para reduzir o esforço de recovery. Alguns
sistemas permitem sua implementação.
3.2 Procedimentos para Recuperação de Falhas Baseados em
LOG
Periodicamente é realizada uma cópia do banco de dados. Cada mudança feita no
banco de dados por uma transação fica registrada no arquivo de LOG (histórico).
A estrutura do LOG é composta por três registros:
<Início Ti>
<Ti, nome arquivo, id-registro, atributo, valor antigo, valor novo>
Capítulo 4: Segurança em SGBDs
- 18 -
Banco de dados II
<Fim Ti>
No caso de uma falha, é executado o seguinte algoritmo:
1. Aloca duas listas: lista-UNDO e lista-REDO
2. Percorre o arquivo de LOG do fim para o início até chegar ao primeiro registro de
checkpoint, examinando cada registro:
2.1.
se achou <Fim Ti>, adiciona Ti na lista-REDO
2.2.
se achou <Inicio Ti> e Ti não está na lista-REDO, adiciona Ti na lista-UNDO
3. Quando se chega ao registro checkpoint, para cada transação Ti na lista de transações
desse registro:
3.1.
se Ti não estiver na lista-REDO, então adiciona Ti na lista-UNDO
4. Percorre o arquivo de LOG do fim para o início:
4.1.
realizando UNDO(Ti) para todas as transações Ti existentes na lista-UNDO
4.2.
marcando na lista-REDO as transações Ti cujos registros <Início Ti> estão
sendo encontrados nessa varredura
5. caso todas as transações existentes na lista-UNDO tenham sido desfeitas e ficou alguma
transação Ti não marcada na lista-REDO;
5.1.
continua percorrendo o arquivo de LOG para trás até que todos os registros
<Inicio Ti> das transações não marcadas na lista-REDO tenham sido encontradas
6. Percorre o arquivo de LOG para a frente, realizando REDO(Ti) para todas as transações
existentes na lista-REDO.
Esse algoritmo evita a varredura e a verificação de todas as transações existentes em todo o
arquivo de LOG. A operação de REDO deve ser idempotente, ou seja, a execução do
REDO(Ti) diversas vezes deve ser equivalente a executá-la apenas uma única vez!
Exemplo: Suponha um ambiente bancário multiusuário e um arquivo de LOG com as
seguintes informações num dado instante:
<início T1>
<T1, Conta, c1, 1000, 500>
<início T2>
<início T3>
<T2, Conta, c2, 3000, 3500>
<fim T1>
<início T4>
<T3, Conta, c3, 1500, 1200>
<T2, Conta, c6, 500, 100>
<T4, Conta, c8, 200, 0>
<fim T3>
<início T5>
<T4, Conta, c9, 500, 600>
Capítulo 4: Segurança em SGBDs
- 19 -
Banco de dados II
<Inicio T6>
<T5, Conta, c5, 100, 260>
<fim T5>
<T2, Conta, c7, 700, 850>
<T6, Conta, c8, 200, 550>
Supondo que a técnica de modificação imediata do banco de dados esteja sendo
utilizada:
a) que ações devem ser realizadas pelo subsistema de recovey caso ocorra:
1) uma falha da transação T4;
2) uma falha de sistema;
b) se houvesse um registro de checkpoint imediatamente após o registro <início T4>,
que ações deveriam ser realizadas se ocorresse uma falha de sistema?
Antes da transação ser efetivada no banco de dados, ela deve estar gravada no LOG
físico (WAL – Write Ahead Log).
Como as modificações realizadas pela transação estão armazenadas no LOG, podese retardar o momento em que as mesmas serão transferidas para o banco de dados como,
por exemplo, no momento da seleção de uma vítima. Em caso de falha de sistema, é
necessário fazer o partial redo para recuperar as transações committed no momento da
falha e o global undo para desfazer o efeito das transações que não haviam sido
concluídas.
No segundo caso, as modificações realizadas por uma transação são efetivadas no
banco de dados no momento em que a transação está sendo executada (imediato). O
procedimento WAL continua valendo, ou seja, primeiro a informação é armazenada no
arquivo de log. A implementação da política force pode gerar problemas no
processamento de transação, pois o overhead no sistema é maior.
3.2.1 Checkpoints
Checkpoints são pontos de verificação que garantem que até aquele ponto os
conteúdos dos buffers de LOG e do banco de dados foram descarregados nos respectivos
meios físicos. Os checkpoints são executados periodicamente pelo sistema de recovery e
tem por objetivo reduzir o esforço de recovery.
Os seguintes passos são executados quando da ocorrência de um checkpoint:



o buffer de LOG é descarregado para o arquivo de LOG;
o buffer de dados é descarregado para o banco de dados físico;
um registro de checkpoint é gravado no arquivo de LOG: <checkpoint <t1, t2,
..., tn>>
Capítulo 4: Segurança em SGBDs
- 20 -
Banco de dados II
Bibliografia utilizada no capítulo:
[BER 87] BERNSTEIN, Philip A; HADZILACOS, Vassos; GOODMAN, Nathan. Concurrency Control
and Recovery in Database Systems. Reading:Addison-Wesley, 1987.
[SIL 99] SILBERSCHATZ, Abraham.; KORTH, Henry F.; SUDARSHAN, S. Sistema de Banco de Dados.
3ª edição., São Paulo: Makron Books, 1999.
Capítulo 4: Segurança em SGBDs
- 21 -
Banco de dados II
4 Segurança em SGBDs
O termo segurança, em banco de dados, refere-se à proteção do banco de dados
contra acessos intencionais ou não intencionais utilizando controles baseados em
computador ou não [CON 98]
São considerados acessos intencionais aqueles realizados propositadamente, por
exemplo, um usuário do sistema cede sua senha a pessoas não autorizadas. Acessos não
intencionais são aqueles que, ao ocorrer, causam algum tipo de perda (por exemplo, perda
da consistência do banco de dados ou perda de informação), mas não foram propositados.
Exemplo, queda de energia que corrompe o banco de dados.
Segundo [CON 98], o contexto de segurança pode ser analisado segundo as
seguintes situações:
 roubo e fraude de informação;
 perda de confiabilidade;
 perda de privacidade;
 perda de integridade;
 perda de disponibilidade.
Os controles que podem ser implementados podem ser baseados em computador ou
não. Os controles não baseados em computador geralmente estão associados a políticas e
planos de segurança estabelecidos pela organização. Exemplo: política para cessão de
contas de usuários, os usuários devem trocar suas senhas mensalmente e não devem deixálas registradas em locais de fácil acesso.
Os controles baseados em computador são agrupados nas seguintes categorias:
 autorização: refere-se a concessão de um direito ou de um privilégio a um
usuário (ou a um programa) a acessar legitimamente o sistema ou um objeto do
sistema;
 visões: é um mecanismo que permite estabelecer porções de dados que podem
ser visualizados por um determinado usuário (ou programa);
 backup e recuperação de falhas: refere-se ao mecanismos necessários para
garantir a disponibilidade do sistema em caso de falhas;
 integridade: refere-se aos controles que contribuem para manter a segurança do
sistema evitando que dados inválidos sejam registrados no sistema (ver
Capítulo 2);
 criptografia: refere-se à codificação dos dados a partir de um algoritmo
especial que torna os dados impossibilitados de serem lidos sem que se tenha a
chave de criptografia. Esse tópico não faz parte do escopo da disciplina;
 auditorias: tem por objetivo verificar os acessos que são realizados sobre o
sistema e observar se os acessos realizados seguem as políticas de segurança
propostas. Esse tópico não faz parte do escopo da disciplina.
Capítulo 4: Segurança em SGBDs
- 22 -
Banco de dados II
4.1 Autorização de Acesso
Os mecanismos de autorização envolvem duas abordagens: autenticação e
autorização de acesso. A primeira tem por objetivo verificar se o usuário que tenta acessar o
sistema é quem realmente diz ser. Em SGBDs, a autenticação é realizada através de senhas
embora este recurso não seja totalmente garantido. O mecanismo de autorização permite
conceder privilégios para cada um dos objetos aos usuários. Desse modo, apenas os
usuários que têm privilégio sobre o objeto podem acessá-lo.
Em SQL existe um conjunto de comandos DDL que permite criar usuários e
conceder privilégios.
4.1.1 Criando usuários
Os usuários são criados pelo administrador do sistema (DBA) e, geralmente, é o
administrador do sistema que concede os privilégios que esse usuário terá.
Exemplo:
CREATE USER ALUNO IDENTIFIED BY ALUNOSENHA;
A execução do comando acima criará um usuário chamado ALUNO, cuja senha é
ALUNOSENHA.
4.1.2 Concedendo/Revogando privilégios de acesso
O fato de criar um usuário não significa que ele terá acesso aos objetos do sistema.
Em SQL, para que um usuário tenha acesso aos objetos do esquema ele deve receber,
explicitamente, esse privilégio. Caso ele deixe de ter acesso aos objetos do esquema, os
direitos deverão ser revogados, também, explicitamente.
Quem pode conceder/revogar privilégios? A resposta é o proprietário do esquema já
que, inicialmente, apenas ele tem conhecimento da existência do objeto.
Que privilégios podem ser concedidos/revogados? Em SQL, os privilégios que
podem ser atribuídos a objetos são:






SELECT: permite recuperar dados de uma tabela;
INSERT: permite inserir novas tuplas em uma tabela;
UPDATE: permite modificar tuplas de uma tabela;
DELETE: permite remover tuplas de uma tabela;
EXECUTE: permite executar uma stored procedure;
REFERENCES: permite referenciar colunas de uma tabela já existente (de outro esquema) em
restrições de integridade.
O comando SQL para conceder privilégios a um usuário é GRANT.
Sintaxe:
GRANT <privilégios> ON <nome tabela ou visão> TO <usuários>;
Exemplo: autorização de leitura e atualização dos atributos da tabela Funcionário
para o usuário Pedro.
GRANT SELECT, UPDATE ON FUNCIONARIO TO PEDRO;
Capítulo 4: Segurança em SGBDs
- 23 -
Banco de dados II
Para revogar privilégios:
REVOKE <privilégios> ON <nome tabela ou visão> FROM <usuários>;
Exemplo: retirar a autorização de atualização sobre a tabela Funcionário do usuário
Pedro.
REVOKE UPDATE ON FUNCIONARIO FROM PEDRO;
4.1.3 Roles
Como você pode observar, para cada objeto do esquema deve ser definido quais
usuários terão direito sobre ele e que tipo de direito terão. Alguns SGBDs oferecem alguns
recursos para facilitar a administração do sistema, as roles. Uma role (papel) consiste de
um conjunto de privilégios que são definidos sobre uma tabela e que podem ser atribuídas a
um usuário, facilitando a tarefa de concessão de privilégios.
Usuário 1
Usuário 2
Tabela A
Usuário 3
Tabela B
Figura 4-1: Usuários e privilégios sobre tabelas
A vantagem na utilização de roles é que se novas tabelas precisam ser
acrescentadas, basta atribuir isso a role criada e todos os usuários passarão a ter esse
privilégio. Além disso, se um novo usuário é acrescentado ao sistema, é necessário atribuir
a ele apenas a role apropriada.
O uso de roles é muito similar ao uso dos privilégios. Primeiro de tudo é necessário
criar a role. Exemplo, definir um papel (role) que permita a recuperação de dados e a
atualização de dados da tabela Funcionário e atribuir ao usuário Pedro:
CREATE ROLE RL_FUNCIONARIO;
Criada a role, atribui-se a ela os privilégios sobre os objetos:
GRANT SELECT, UPDATE ON FUNCIONARIO TO RL_FUNCIONARIO;
Finalmente, atribui-se a role ao usuário;
GRANT RL_FUNCIONARIO TO PEDRO;
Capítulo 4: Segurança em SGBDs
- 24 -
Banco de dados II
Usuário 1
Usuário 2
Usuário 3
Role
Tabela A
Tabela B
Figura 4-2: Usuários, privilégios sobre tabelas e roles
Também é possível atribuir uma role a outra role. Por exemplo, se agora fosse
criada uma role RL_DEPENDENTES que permite selecionar, inserir e atualizar
Dependentes de um Funcionário e que o usuário Pedro pode executar essas operações.
Pode-se fazer isso de duas formas:
GRANT RL_FUNCIONARIO, RL_DEPENDENTES TO PEDRO;
GRANT RL_DEPENDENTES TO RL_FUNCIONARIO;
4.1.4 Sinônimos
Um outro recurso oferecido pelo SGBD ORACLE é o de sinônimo. Um sinônimo
tem por objetivo oferecer transparência no acesso aos objetos. Quando um objeto é
acessado por um usuário que não é o proprietário ele deve ser referenciado pelo
[nome_do_proprietário.nome_do_objeto].
O sinônimo cria um apelido para um objeto:
CREATE PUBLIC SYNONYM FUNCIONARIO FOR RH.FUNCIONARIO;
Foi criado um sinônimo público para a tabela Funcionário, que pertence ao esquema
RH, chamado Funcionário. A partir da criação do sinônimo todos os usuários que tiverem
privilégio sobre a tabela Funcionário poderão referenciá-la como Funcionário, apenas.
Esse capítulo apresentou alguns recursos que são oferecidos pelo SGBD Oracle,
mas que podem estar presentes em outros SGBDs, também. Embora alguns desses recursos
não estejam previstos na linguagem SQL, estes ainda assim foram apresentados para que
você faça uma melhor utilização do SGBD nos exercícios práticos.
Capítulo 4: Segurança em SGBDs
- 25 -
Banco de dados II
5 Visões
As tabelas criadas em um banco de dados relacional têm existência física dentro do
sistema de computação. Algumas vezes, porém, é necessário criar tabelas que não
ocupem espaço físico, mas que possam ser utilizadas como as tabelas normais. Essas
tabelas são chamadas de visões ou tabelas virtuais, pois não existem por si, mas parecem
ao usuário como se fossem. Visões são, então, relações virtuais derivadas das relações do
banco de dados.
Uma visão é definida utilizando a instrução create view. Para definir uma visão é
necessário definir um nome e estabelecer uma consulta. Em SQL a sintaxe é:
create view <nome da visão> as <expressão da consulta>
Onde:


<expressão da consulta> é qualquer expressão de consulta legal da álgebra
relacional;
<nome da visão> é o nome da tabela virtual.
A definição de uma visão é armazenada no dicionário de dados (catálogo) do
sistema. Para o usuário, no entanto, é como se tivesse sido criada uma nova tabela no
banco de dados.
A visão criada é dita dinâmica, pois todas as modificações que forem executadas
sobre a tabela origem (da qual a visão foi derivada) se refletirão sobre a mesma.
Assim como as tabelas, as visões também podem ser removidas:
drop view <nome da visão>
A visão especificada é eliminada (isto é, sua definição é removida do dicionário de
dados) e todas as visões definidas em termos desta visão também são automaticamente
anuladas.
Exemplos:
CREATE VIEW DadosPac AS
SELECT cod_pac, nome
FROM Pacientes
DROP VIEW DadosPac
Uma vez definida a visão, o nome pode ser usado para referenciar a relação virtual
que a visão gera. Nomes de visões podem aparecer em qualquer lugar em que um nome
de relação possa aparecer.
Capítulo 6: Restrições de Integridade
- 26 -
Banco de dados II
5.1 Uso de visões
Podemos ressaltar as seguintes vantagens na utilização das visões:




pode-se “preparar o terreno” para usuários casuais que não entendem de
processamento de dados, e aos quais não se deseja entrar nos detalhes de
comandos SQL mais complexos;
pode-se simplificar consultas criando visões com as consultas mais comuns
que envolvem muitas tabelas;
permitem que um mesmo dado seja visto por diferentes usuários de diferentes
formas (ao mesmo tempo);
na segurança de dados, isto é, a limitação do acesso por parte das pessoas
indesejáveis. Para isso, pode-se criar uma visão e fornecê-la como relação
“existente” para esses usuários, porém com a ocultação de dados. Por exemplo,
pode-se criar uma visão com os dados apenas dos funcionários que ganham
menos do que 5000, ou apenas dos funcionários do departamento de
Informática (usando uma junção com Departamentos e Funcionários, por
exemplo). Essas restrições de acesso podem ser divididas em geral nas
restrições do acesso apenas a algumas colunas inteiras de uma ou mais relações
ou a apenas algumas de suas linhas, ou um misto de ambas.
Exemplos:

uso na segurança de acesso (autorização): Um funcionário do hospital não
deve ter acesso a todos os dados pessoais de um paciente, somente ao seu
código e nome:
CREATE VIEW DadosPac AS
SELECT cod_pac, nome
FROM Pacientes

para simplificar consultas: Pode ser interessante vincular os dados de um
médico aos dados de suas consultas.
CREATE VIEW MedCons AS
SELECT nome, to_char(data_hora,’dd/mm/yy hh24:mi:ss’)
FROM Medico, Consultas
WHERE medico.cod_med=consultas.cod_med
Operações realizadas sobre uma visão se refletem diretamente sobre as tabelas
físicas das quais ela deriva.
Exemplos:
a)

o funcionário do hospital deseja buscar o nome de todos os pacientes cadastrados que
começam com a letra R.
SELECT nome
FROM DadosPac
Where nome LIKE ‘R%’
buscar a data das consultas do médico Pedro
SELECT data_hora
FROM MedCons
WHERE nome = ‘Pedro’
Capítulo 6: Restrições de Integridade
- 27 -
Banco de dados II
5.2 Problemas associados às visões
Visões são mecanismos úteis para simplificar consultas dos bancos de dados, mas
modificações do banco de dados através de visões têm potencialmente conseqüências
desvantajosas.
Exemplos:
Situação 1: Inserção de tuplas na visão MedCons. Problemas:


violação da regra de integridade de entidade (não tenho o código do médico e
do paciente);
perde-se os relacionamentos entre médicos e consultas (algumas consultas não
teriam resultados satisfatórios).
Situação 2: visão que relaciona o total de consultas de um determinado paciente

não podem suportar operações de insert, update e delete
CREATE VIEW ConsPac (codp,totcons) AS
SELECT codp, count(*)
FROM consultas
GROUP BY codp
Visões são interessantes para facilitar consultas e estabelecer níveis de segurança.
Entretanto, modificações utilizando visões podem gerar problemas devido à possibilidade
de envolver várias tabelas e do fato de que uma visão pode apresentar apenas alguns
atributos da relação, impossibilitando a realização de modificações sobre a mesma.
Não é possível implementar visões atualizáveis que envolvam funções de agregação
como, por exemplo, GROUP BY, CONNECT BY, DISTINCT.
Capítulo 6: Restrições de Integridade
- 28 -
Banco de dados II
6 Restrições de Integridade
As restrições de integridade são regras que determinam as operações válidas sobre o
banco de dados, mantendo a consistência dos dados armazenados. Por operações válidas
entende-se aquelas operações que mantém a relação entre o mundo real (do usuário ou da
aplicação) e a sua representação no banco de dados. Pode-se dizer que existe um
mapeamento das regras existentes no mundo real para o banco de dados
A partir da definição, por parte do usuário, dos estados corretos e das transições de
estado corretas, o SGBD deve garantir que o banco de dados reflita somente estes estados e
que as operações sobre o banco de dados reflitam somente estas transições.
Alguns exemplos de restrições de integridade:
a) em campos do tipo numérico caracteres não são permitidos (com exceção do
ponto decimal);
b) um aluno não pode estar matriculado na mesma disciplina, mais de uma vez, no
mesmo semestre;
c) pela CLT, um funcionário não pode ter o seu salário rebaixado.
No primeiro exemplo, a restrição de integridade é definida em mais baixo nível,
sendo válida para qualquer aplicação. No segundo e terceiro exemplo, as restrições de
integridade são definidas em mais alto nível e dependem da aplicação.
6.1 Classificação das Restrições de Integridade
6.1.1 Segundo seu alcance
Refere-se ao volume de informação associada à RI
a) atinge um único atributo de uma relação;
b) atinge mais de um atributo da mesma tupla;
c) atinge mais de uma tupla do mesmo tipo (mesma relação);
d) atinge duas ou mais tuplas que podem ser de tipos diferentes (relações
diferentes).
6.1.2 Segundo o momento do teste
a) instantânea: sempre que o dado associado à RI for inserido ou atualizado, a RI
deve ser testada;
b) postergada: a RI só pode ser testada após uma série de operações sobre o banco
de dados (típico em RI que atingem mais de uma tupla).
6.1.3 RIs que regulamentam a atualização de valores
Referem-se a RIs baseadas em versões de valores de dados e RIs que levam em
conta a relação (valor_antigo, valor_novo).
Anexos
- 29 -
Banco de dados II
Exemplo 1: o salário do empregado não pode ser rebaixado e-sal(novo_valor) >=
e-sal(valor_atual)
Exemplo 2: as transições do estado civil do empregado devem respeitar o seguinte
grafo de precedências:
VIÚVO
SOLTEIRO
CASADO
SEPARADO
DIVORCIADO
Figura 6-1: Exemplo de Restrição de Integridade
6.1.4 RIs que devem ser testadas na ocorrência de eventos externos
O momento do teste da RI é determinado pela aplicação.
Exemplo: a partir de três meses de sua data de ingresso na empresa, o salário do
empregado deve refletir o salário básico estipulado para sua função (piso salarial da
categoria).
6.2 Restrições de Integridade no Modelo Relacional
No modelo relacional as restrições de integridade podem ser agrupadas da seguinte
forma:




restrição de domínio;
restrição de valor nulo (NULL);
restrição de chave primária;
restrição de integridade referencial..
6.2.1 Restrição de domínio
São a forma mais elementar de restrições de integridade. Especificam valores que
podem ser definidos para um atributo, também estão associadas aos tipos de dados.
Exemplos de domínios:





Telefones: conjunto de números válidos dentro de um determinado código de área;
Notas: valores entre 0 e 10,0;
CEP: conjunto de CEP’s do Brasil válidos;
Sexo: valores ‘M’e ‘F’;
Tipos de dados: caractere, numérico, data.
Anexos
- 30 -
Banco de dados II
6.2.2 Restrição de valor nulo (NULL)
A restrição de valor nulo proíbe a inserção do valor nulo para um atributo. É útil
para evitar que valores nulos sejam especificados para atributos onde se deseja obrigar que
o dado seja informado. Em SQL, esta restrição é implementada a partir da cláusula NOT
NULL.
6.2.3 Restrição de chave primária
A restrição de chave primária refere-se ao fato da chave ser identificador único em
uma tupla do banco de dados. A chave primária é fundamental para o modelo relacional
uma vez que os valores da chave primária são utilizados como referências às tuplas
identificadas por eles.
Quando uma relação apresenta mais de uma chave, cada uma delas é uma chave
candidata. A chave primária é aquela escolhida dentre as candidatas. Além disso, a chave
primária não pode ter valor nulo.
6.2.4 Restrição de integridade referencial
Representa referências de uma relação para outra, isto é, o relacionamento existente
entre tuplas. Com isso, assegura-se que um valor que aparece em uma relação A, para um
dado conjunto de atributos, aparece também para um certo conjunto de atributos, na outra
relação – relação B.
Se uma relação inclui uma chave estrangeira, então o valor desta chave só pode ser:


nulo ou
igual a algum valor na relação onde ela é chave primária.
6.3 Especificação de Restrições de Integridade
Em SQL, as restrições de integridade de chave primária e referencial podem ser
especificadas na própria cláusula CREATE TABLE.
CREATE TABLE Empregados
(cod_func NUMBER
 restrição de domínio
CONSTRAINT pk_cod_func PRIMARY KEY  restrição de chave primária
CONSTRAINT ck_cod_func CHECK (cod_func BETWEEN 1 AND 999),
 restrição de domínio
nome VARCHAR220) NOT NULL,  restrição de campo nulo
salario NUMBER(7,2),
idade NUMBER(2),
estado_civil NUMBER CONSTRAINT ck_estado_civil
CHECK (estado_civil IN (1, 2, 3, 4,5)),  restrição de domínio
cod_depto CONSTRAINT fk_cod_depto REFERENCES Departamento(cod_depto)
 restrição de chave estrangeira
)
Existem ainda as restrições de integridade próprias do contexto da aplicação. Essas
podem ser especificadas através de:
Anexos
- 31 -
Banco de dados II


cláusula ASSERT;
TRIGGERS.
Anexos
- 32 -
Banco de dados II
7 ANEXOS
7.1 Arquitetura Básica de um SGBD
Programadores
Usuários
DBA
Programas de
Aplicação
Consultas
Esquema do
Banco de Dados
Pré-processador
de DML
Processador
de consulta
Compilador
de DDL
Gerenciador de
Banco de Dados
Gerenciador
Dicion.Dados
Código objeto
de programas
SGBD
Gerenciador
de Arquivos
Anexos
- 33 -
Banco de dados II
7.2 Arquitetura do Gerenciador de Banco de Dados
Código objeto
de programas
Processador
de consulta
Gerenciador
Dicion.Dados
Autorização
de Acesso
Verificação
de Integridade
Gerente de Dados
Processador de
Comandos
Otimizador de
Consultas
Gerente de
Transações
Scheduler
Gerente de
Buffers
Gerente de
Recuperação
Falhas
Gerenciador
de Arquivos
Banco de Dados e
Dicionário de Dados
Anexos
- 34 -