Bases de Dados

Bases de Dados
Revisão da matéria
Programa
ƒ Modelação
• modelo Entidade – Associação
• modelo e álgebra relacionais
ƒ Implementação
• SQL básico e avançado
• desenvolvimento de aplicações
• normalização
ƒ Optimização
• ficheiros e índices
• optimização de consultas
• transacções, concorrência, recuperação
IST ▪ DEI ▪ Bases de Dados
2
1
Diagrama E-A
IST ▪ DEI ▪ Bases de Dados
3
Sumário da notação
IST ▪ DEI ▪ Bases de Dados
4
2
Exemplo do banco
branch
account
depositor
loan
borrower
customer
IST ▪ DEI ▪ Bases de Dados
5
Chaves
ƒ Como distinguir tuplos?
• não pode haver 2 tuplos exactamente iguais
• super-chave
▫ o conjunto de atributos que identificam univocamente
• chave candidata
▫ conjunto mínimo de atributos
• chave primária
▫ a chave candidata escolhida
▫ normalmente prefere-se um atributo separado por si só
◦ atenção aos atributos (supostamente) únicos
IST ▪ DEI ▪ Bases de Dados
6
3
Chaves estrangeiras
ƒ Chave estrangeira
• referência entre relações
IST ▪ DEI ▪ Bases de Dados
7
Restrições de integridade
ƒ As restrições de integridade garantem a consistência
da BD
• exemplos
▫ RI 1: o saldo de uma conta não pode ser null
▫ RI 2: nenhuma conta pode ter o mesmo número de outra
▫ RI 3: todos os números de conta em depositor tem que
existir em account
▫ RI 4: o salário de um funcionário tem que ser pelo menos
5€/hora
▫ …
IST ▪ DEI ▪ Bases de Dados
8
4
Resumo
ƒ Operações fundamentais da álgebra relacional
• σp ((E1) em qque p é um ppredicado sobre os atributos de E1
• ∏s(E1) em que S é uma lista de atributos de E1
• E1 U E2
• E1 – E2
• E1 x E2
• ρ x (E1) em que x é o novo nome para E1
IST ▪ DEI ▪ Bases de Dados
9
Exemplo de renomeação
ƒ Maior saldo em conta
Πbalance (account) –
Πaccount.balance (
σaccount.balance < d.balance (
account x ρd (account) ) )
IST ▪ DEI ▪ Bases de Dados
10
5
Outro exemplo de renomeação
ƒ Encontrar o nome de todos os clientes que vivem na
mesma rua e cidade que o cliente chamado Smith
Πcustomer.customer_name (
σcustomer.customer_street = smith_addr.street Λ
customer.customer_city = smith_addr.city (
customer x ρsmith_addr(street,city) (
Πcustomer_street, customer_city (
σcustomer_name = “Smith” (customer) ) ) ) )
IST ▪ DEI ▪ Bases de Dados
11
Resumo
ƒ Operações adicionais de álgebra relacional
• E1 ∩ E2
• E1 ‫ ڇ‬E2
• E1 ‫ڇ‬θ E2
• E1 ÷ E2
IST ▪ DEI ▪ Bases de Dados
12
6
Agrupamento e agregação
branch_nameGsum(salary)
(works)
IST ▪ DEI ▪ Bases de Dados
13
Atribuição (←)
ƒ Operador que permite guardar resultados em variáveis
temporárias ou tabelas existentes
• exemplo:
temp1 ← ∏R-S (r)
temp2 ← ∏R-S ( (temp1 x s) – ∏R-S,S (r) )
result ← temp1 – temp2
IST ▪ DEI ▪ Bases de Dados
14
7
Estrutura básica de perguntas em SQL
ƒ Pergunta típica em SQL
select
l t A1, A2, ..., An
from r1, r2, ..., rm
where P
Π A1 , A2 , An (σ
( P (r
( 1 x r2 x ... x rm) )
IST ▪ DEI ▪ Bases de Dados
15
Operações em conjuntos – exemplos
ƒ Nomes dos clientes com conta ou empréstimo
(select customer_name from depositor)
union
(select customer_name from borrower)
ƒ Nomes dos clientes com conta e empréstimo
(select customer_name from depositor)
intersect
(select customer_name from borrower)
IST ▪ DEI ▪ Bases de Dados
16
8
Ordenação de tuplos – order by
ƒ A cláusula order by permite ordenar tuplos
• exemplo: listar por ordem alfabética os nomes dos
clientes com empréstimo na agência de Perryridge
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number
and branch_name = ‘Perryridge’
y g
order by customer_name
IST ▪ DEI ▪ Bases de Dados
17
Funções de agregação
ƒ Operam no conjunto de valores de uma coluna
• avg, min, max, sum, count
ƒ Exemplo: saldo médio das contas da agência de
Perryridge
select avg(balance)
from account
where branch_name = ‘Perryridge’
IST ▪ DEI ▪ Bases de Dados
18
9
Funções de agregação – group by (outro exemplo)
ƒ Número de titulares em cada agência
select branch_name, count(distinct customer_name)
from depositor, account
where depositor.account_number =
account.account_number
group by branch_name
branch name
IST ▪ DEI ▪ Bases de Dados
19
Funções de agregação – having
ƒ saldo médio das contas de cada agência, mas só
agências com saldo médio superior a 1200
select branch_name, avg(balance)
from account
group by branch_name
having avg (balance) > 1200
• predicados de having são aplicados depois do agrupamento
• predicados de where são aplicados antes do agrupamento
IST ▪ DEI ▪ Bases de Dados
20
10
Exemplo 6
ƒ Nomes dos clientes com contas na mesma agência
onde o cliente Hayes tem conta
select distinct d.customer_name
from depositor as d, account as a
where d.account_number = a.account_number
and a.branch_name in
(select a2.branch_name
f
from
d
depositor
it as d2,
d2 accountt as a2
2
where d2.account_number = a2.account_number
and d2.customer_name = 'Hayes');
21
Exemplo 11
ƒ Nome da agência com o maior saldo médio
select branch_name
from account
group by branch_name
having avg(balance) >= all
(select avg(balance)
from account
group by branch_name);
22
11
Exemplo 14
ƒ Quem são os clientes com conta em todas as
agências da cidade de Brooklyn
select distinct customer_name
from depositor as d
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn')
except
( l t branch_name
(select
b
h
from depositor as d2, account as a2
where d2.account_number = a2.account_number
and d2.customer_name = d.customer_name) );
23
Integridade referencial
ƒ
Outro exemplo:
create table depositor
(customer_name
char(20),
account_number
char(10),
primary key (customer_name, account_number),
foreign key (account_number) references account,
foreign key (customer_name) references customer)
customer
depositor
IST ▪ DEI ▪ Bases de Dados
account
24
12
Funções – exemplo (Postgres)
create function account_count (c_name varchar(255))
returns integer
as
$$
declare a_count integer;
begin
select count(*) into a_count
from depositor
where customer_name = c_name;
return a_count;
a count;
end
$$
language plpgsql;
"A CREATE FUNCTION command is defined in SQL:1999 and later. The PostgreSQL version is
similar but not fully compatible. The attributes are not portable, neither are the different available
25
IST ▪ DEI ▪ Bases de Dados
languages." (Postgres manual)
Procedimentos (Postgres)
create function account_count_proc (in c_name varchar(255),
out a_count integer)
as
$$
begin
select count(*) into a_count
from depositor
where customer_name = c_name;
end
$$
language plpgsql;
select * from account_count_proc ('Smith');
IST ▪ DEI ▪ Bases de Dados
26
13
Exemplo (Postgres)
ƒ Criação da função que implementa o trigger
create or replace function overdraft_trigger_proc() returns trigger
as $$
begin
if new.balance < 0 then
insert into loan values
(new.account_number, new.branch_name, (-1)*new.balance);
insert into borrower
(select customer_name, account_number from depositor
where depositor.account_number = new.account_number);
update account set balance = 0
where account.account_number = new.account_number;
end
d if;
if
return new;
end
$$ language plpgsql;
27
Papéis (roles) – exemplo
create role caixa
create role gestor
grant select on account to caixa
grant update(balance) on account to caixa
grant caixa to gestor
grant all privileges on account to gestor
grant caixa to alice, jorge
grant gestor to susana
IST ▪ DEI ▪ Bases de Dados
28
14
PHP e Postgres
1. Abrir a ligação e escolher a BD
$user = “istxxxxxx";
$host = “db.ist.utl.pt";
$port = 5432;
$password = “xxxxxxxx“;
$dbname = $user;
/* username sigma */
/* password psql_reset */
/* porquê? */
$connection = pg_connect("host=$host port=$port
user=$user password=$password dbname=$dbname")
or die(pg_last_error());
IST ▪ DEI ▪ Bases de Dados
29
PHP e Postgres
2. Fazer uma consulta
$sql = "select * from customer";
$result = pg_query($sql) or die(pg_last_error());
IST ▪ DEI ▪ Bases de Dados
30
15
PHP e Postgres
4. Iterar pelos resultados
echo("<table>");
while($row_array = pg_fetch_assoc($result))
{
echo("<tr>");
echo("<td>{$row_array['customer_name']}</td>");
echo("<td>{$row_array['customer_street']}</td>");
echo("<td>{$row_array['customer_city']}</td>");
echo("</tr>");
}
echo("</table>");
IST ▪ DEI ▪ Bases de Dados
31
1FN
ƒ Uma tabela está na 1FN se e só se:
• todas as colunas contém valores atómicos
• i.e., cada coluna pode ter apenas um valor para
cada linha
IST ▪ DEI ▪ Bases de Dados
32
16
2FN
ƒ Uma tabela está na 2FN se e só se:
• estiver na 1FN
• cada atributo não-chave depende de todos os
atributos da chave primária
• (i.e., não há dependências parciais da chave)
33
IST ▪ DEI ▪ Bases de Dados
Axiomas de Armstrong
ƒ Transitividade
• se α → β e β → γ, então α → γ
ƒ Reflexividade
• se β ⊆ α, então α → β
ƒ Aumento
• se α → β, então γα → γβ
IST ▪ DEI ▪ Bases de Dados
(p.ex. AB → A ou A → A)
(p.ex. AB → AC)
34
17
Chaves
ƒ Seja R o esquema de uma relação
• exemplo: R = (A,B,C,D,E)
ƒ Seja K um subconjunto de R
• exemplo: K = AB
ƒ K é uma super-chave em R se K → R
• exemplo: AB → ABCDE
IST ▪ DEI ▪ Bases de Dados
35
3FN
ƒ Uma tabela está na 3FN se e só se:
• para cada dependência α → β
▫ (α e β são sub-conjuntos dos atributos da tabela)
• ou α é super-chave
• ou β está contido numa chave candidata
IST ▪ DEI ▪ Bases de Dados
36
18
FNBC
ƒ Uma tabela está na FNBC se e só se:
• para cada dependência α → β
▫ (α e β são sub-conjuntos dos atributos da tabela)
• α é super-chave.
37
IST ▪ DEI ▪ Bases de Dados
Decomposição
ƒ Se uma dependência α → β infringe uma FN então
decompõe-se a relação
customer_name branch_name branch_city
• (α U β )
Smith
Downtown
Brooklyn
• (R – (β – α))
Johnson
• no exemplo
▫ α = branch_name
▫ β = branch_city
Perryridge
Washington
Hayes
Perryridge
Washington
Jones
Downtown
Brooklyn
• e a relação decompõe
decompõe-se
se em
▫ (α U β ) = (branch_name, branch_city)
▫ (R – (β – α)) = (customer_name, branch_name)
IST ▪ DEI ▪ Bases de Dados
38
19
Decomposição sem perdas
ƒ Uma decomposição de R em R1 e R2 não tem perdas
(lossless) se
r = ∏R1 (r ) ∏R2 (r )
ƒ Uma decomposição de R em R1 e R2 não tem perdas
se uma destas dependências se verificar
• R1 ∩ R2 → R1
• R1 ∩ R2 → R2
39
IST ▪ DEI ▪ Bases de Dados
Exercício
a)
5
1
9
1
5
1
1
3
6
9
8
8
5
5
9
5
3
5
9
9
3
1
1
6
9
1
9
3
5
6
9
5
8
9
8
9
4
1
3
4
5
4
IST ▪ DEI ▪ Bases de Dados
9
5
6
8
9
40
20
Hashing dinâmico – inserção
IST ▪ DEI ▪ Bases de Dados
41
Chaves de procura compostas
ƒ Chaves de procura compostas
• chaves de procura com mais de um atributo
• p.ex. (branch_name, balance)
ƒ Ordem lexicográfica
• (a1, a2) < (b1, b2) se
▫ (a1 < b1) ou
▫ (a1= b1) e (a2 < b2)
IST ▪ DEI ▪ Bases de Dados
42
21
Ordenação externa – exemplo
ƒ
ƒ
1 registo ocupa 1 bloco
M=3
Passo 1:
ƒ ler 3 blocos
ƒ colocar em Ri
ƒ dá N=4 ficheiros
Passo 2
ƒ N>M, logo juntar M-1
ficheiros de cada vez
ƒ dá N/(M-1) = 2 ficheiros
ƒ agora já temos N<M
ƒ fazer merge final
IST ▪ DEI ▪ Bases de Dados
43
Algoritmos para junções
ƒ Há vários algoritmos para junções
• nested-loop join
• indexed nested-loop join
• merge-join
• hash-join
IST ▪ DEI ▪ Bases de Dados
44
22
Merge-join
ƒ
Para calcular a junção natural (r ‫ ڇ‬s)
1. ordenar relações r e s pelo atributo da junção
2. fundir as duas relações numa só (merge)
▫
semelhante ao passo 2 da ordenação
IST ▪ DEI ▪ Bases de Dados
45
Hash-join
ƒ Para calcular a junção natural (r ‫ ڇ‬s)
• subdividir os registos de r e s em n partições
• função de hash diz qual é a partição para um dado
registo
▫ a função calcula a partição com base no atributo da junção
• aplicar build-and-probe
a cada par de partições
IST ▪ DEI ▪ Bases de Dados
46
23
Materialização e pipelining no Postgres
select customer_name
from depositor natural join account
group by customer_name
having sum(balance) >=
all (select sum(balance)
from depositor natural join account
group by customer_name);
47
IST ▪ DEI ▪ Bases de Dados
Propriedades ACID
ƒ Atomicidade
• se a transação falhar entre os
passos 4 – 6,
6 os passos 1 – 3
ficam sem efeito
ƒ Consistência
• a soma A+B tem que ser igual
antes e depois
1
2
3
4
5
6
Ti : read(A)
A := A – 50
write(A)
read(B)
B := B + 50
write(B)
ƒ Isolamento
• nenhuma outra operação deve ler os valores de A e B entre os
passos 3 e 6
ƒ Durabilidade
• se a transacção termina com sucesso, as alterações são
definitivas
IST ▪ DEI ▪ Bases de Dados
48
24
Serialização por conflitos
ƒ Existe conflito se as instruções de T1 e T2 tiverem de
ser feitas numa certa ordem
• se não houver conflito, podem ser trocadas
IST ▪ DEI ▪ Bases de Dados
49
Teste de serialização – exemplo 4
ƒ O escalonamento 4 não é serializável
4
IST ▪ DEI ▪ Bases de Dados
50
25
Escalonamentos recuperáveis
ƒ Considerar apenas escalonamentos recuperáveis
• estes escalonamentos devem obedecer à seguinte
condição:
Para qualquer par de transacções Ti e Tk
se Ti executa write(Q) antes de Tk executar read(Q)
então Ti faz commit antes de Tk fazer commit
commit
commit
IST ▪ DEI ▪ Bases de Dados
51
Rollback encadeado
ƒ Considerar apenas escalonamentos sem hipótese de
rollback encadeado
• estes escalonamentos devem obedecer à seguinte
condição:
Para qualquer par de transacções Ti e Tk
se Ti executa write(Q) antes de Tk executar read(Q)
então Ti faz commit antes de Tk fazer read
commit
IST ▪ DEI ▪ Bases de Dados
52
26
Protocolo de 2 fases – exemplo
ƒ Pode haver rollbacks encadeados
IST ▪ DEI ▪ Bases de Dados
53
Protocolo em árvore – exemplo
IST ▪ DEI ▪ Bases de Dados
54
27
Regras do protocolo com timestamps
ƒ Quando Ti pretende fazer read(Q)
• se W-timestamp(Q) > TS(Ti) então rollback Ti
▫ está a tentar ler valor que já não está presente no objecto
• se W-timestamp(Q) ≤ TS(Ti) executar read(Q)
▫ e se R-timestamp(Q) ≤ TS(Ti)
◦ fazer R-timestamp(Q) := TS(Ti)
IST ▪ DEI ▪ Bases de Dados
55
Regras do protocolo com timestamps
ƒ Quando Ti pretende fazer write(Q)
• se R-timestamp(Q) > TS(Ti) então rollback Ti
▫ a operação de escrita já vem tarde demais
• se W-timestamp(Q) > TS(Ti) então rollback Ti
▫ tentativa de escrita de um valor obsoleto
• senão, executar write(Q)
▫ e fazer W-timestamp(Q) := TS(Ti)
IST ▪ DEI ▪ Bases de Dados
56
28
Protocolo com timestamps – exemplo
IST ▪ DEI ▪ Bases de Dados
57
Exemplo
<T0 start >
<T0 , A, 0, 10 >
<T0 commit >
<T1 start >
<T1 , B, 0, 10 >
<T2 start >
<T2 , C, 0, 10 >
<T2 , C, 10, 20 >
< checkpoint T1 ,T2 >
<T3 start >
<T3 , A, 10, 20 >
<T4 start >
<T3 , D, 0, 10 >
<T4 , E, 0, 10 >
<T3 commit >
--- crash!! --58
29
Exemplo – construção das listas
<T0 start >
<T0 , A, 0, 10 >
<T0 commit >
<T1 start >
<T1 , B, 0, 10 >
<T2 start >
<T2 , C, 0, 10 >
<T2 , C, 10, 20 >
< checkpoint T1 ,T2 >
<T3 start >
<T3 , A, 10, 20 >
<T4 start >
<T3 , D, 0, 10 >
<T4 , E, 0, 10 >
<T3 commit >
undo-list = { T4 , T1 , T2 }
redo-list = { T3 }
IST ▪ DEI ▪ Bases de Dados
59
Exemplo – fase 1
<T0 start >
<T0 , A, 0, 10 >
<T0 commit >
<T1 start >
<T1 , B, 0, 10 >
<T2 start >
<T2 , C, 0, 10 >
<T2 , C, 10, 20 >
< checkpoint T1 ,T2 >
<T3 start >
<T3 , A, 10, 20 >
<T4 start >
<T3 , D, 0, 10 >
<T4 , E, 0, 10 >
<T3 commit >
undo-list = { T4 , T1 , T2 }
repõe valor 0 em B
repõe valor 0 em C
repõe valor 10 em C
repõe valor 0 em E
IST ▪ DEI ▪ Bases de Dados
60
30
Exemplo – fases 2 e 3
<T0 start >
<T0 , A, 0, 10 >
<T0 commit >
<T1 start >
<T1 , B, 0, 10 >
<T2 start >
<T2 , C, 0, 10 >
<T2 , C, 10, 20 >
< checkpoint T1 ,T2 >
<T3 start >
<T3 , A, 10, 20 >
<T4 start >
<T3 , D, 0, 10 >
<T4 , E, 0, 10 >
<T3 commit >
redo-list = { T3 }
repete escrita de valor 20 em A
repete escrita de valor 10 em D
IST ▪ DEI ▪ Bases de Dados
61
Exemplo de transformações múltiplas
IST ▪ DEI ▪ Bases de Dados
62
31
Regras mais usadas
1. Decompor selecções conjuntivas em sequências de selecções
(regra 1)
2. Mover selecções mais para baixo na árvore para serem
executadas mais cedo (regras 2, 7, 11)
3. Executar primeiro as selecções e junções que produzirem
menores relações (regra 6)
4. Substituir produtos cartesianos seguidos de selecção por
operações de junção (regra 4)
5. Decompor e empurrar projecções para baixo na árvore, criando
novas projecções se necessário (regras 3
3, 8
8, 12)
6. Nas sub-árvores em que seja possível, executar as operações via
pipelining
IST ▪ DEI ▪ Bases de Dados
63
32