Bases de Dados 2008/2009

Bases de Dados 2008/2009

Exame de 30 de Janeiro de 2009 Duração: 2h30

1. Considere o seguinte cenário relativo a um stand de compra e venda de carros usados, em que o negócio é comprar carros, executar reparações, e vendê-los por um preço acrescido:

Os carros são identificados pela matrícula e têm uma marca, modelo e ano. O stand tem dois tipos de empregados: mecânicos e vendedores. Ambos são identificados pelo seu número de empregado e deve guardar-se o guardar-seu nome. Os mecânicos executam guardar-serviços de reparação, que são numerados guardar-sequencialmente para cada carro. Estes serviços têm uma descrição e um custo, que é composto pelo custo do material e o custo da mão-de-obra. Os vendedores compram carros a proprietários por um preço-base e vendem-nos a compradores por um valor final que é calculado somando o preço-base, o preço total dos serviços executados, e uma comissão. Para todas as compras e vendas é necessário guardar a data e o valor. Para as vendas é necessário registar a comissão. É também necessário guardar o nome morada e telefone dos proprietários e dos compradores. O proprietário a quem o stand compra um carro pode ser comprador de outro carro, e vice-versa.

(b) Dê exemplo de duas restrições de integridade que não possam ser representadas neste modelo E-A. Resposta:

Exemplo 1: Para qualquer veículo, o valor/data de venda tem de ser superior ao valor/data de compra. Exemplo 2: O custo total das reparações realizadas sobre um veículo não pode ultrapassar um limite. 2. Considere o seguinte modelo relacional, que representa parte de uma base de dados de uma universidade:

aluno(numa, nome, morada)

disciplina(numd, designacao)

aula(numd, data, professor) numd: FK(disciplina)

assistiu(numa,numd,data) numa: FK(aluno) numd,data: FK(aula)

Escreva uma expressão em álgebra relacional para responder às seguintes questões:

(a) Qual o nome dos alunos que assistiram a pelo menos uma aula da disciplina de “Português”, onde também esteve presente o aluno “Tadeu”?

Resposta:

t1 ←

πnumd, data

[

σnome=”Tadeu”

assistiu

⋈

aluno

⋈

disciplina)]

resultado ←

πnome (

assistiu

⋈

aluno

⋈

ou em alternativa:

(b) Qual o nome dos alunos que estiveram presentes na(s) aula(s) de “Português” em que menos alunos estiveram presentes?

Resposta:

t1 ← numd, data

G

[

σdesignacao=”Português” (

assistiu

⋈

disciplina) ]

t2 ←

π numd, data

ρ

ρ

resultado ←

πnome

(

aluno

⋈

assistiu

⋈

ou em alternativa:

A 

G



assistiu

⋈



disciplina



B G



A

C  



A×B

resultado  



C

⋈ assistiu⋈ aluno

3. Considerando que o modelo anterior se encontra implementado numa base de dados relacional, apresente uma consulta SQL para responder a cada uma das seguintes questões:

(a) Qual a disciplina com o maior número médio de presenças nas aulas em 2008? Resposta:

(b) Qual o professor que já deu aulas a todos os alunos? Resposta:

ou em alternativa:

4.

(a) Partindo de uma árvore vazia, desenhe o resultado dos vários passos de inserção à medida que insere os elementos 6, 8, 3, 4, 1, 2, 7 por esta ordem.

select numd

from (select numd, data, count(numa) as c

from assistiu

where extract(year from data) = 2008

group by numd, data) as sub1

group by numd

having avg(c) >= all (select avg(c)

from (select numd, data, count(numa) as c

from assistiu

where extract(year from data) = 2008

group by numd, data) as sub2

group by numd);

select professor

from aula as A, assistiu as B

where A.numd = B.numd and A.data = B.data

group by professor

having count(distinct B.numa) in (select count(numa) from aluno);

select a.professor

from aula as a

where not exists (

select b.numa

from aluno as b

where b.numa not in (

select d.numa

from aula as c, assistiu as d

where c.numd = d.numd and c.data=d.data

and c.professor = a.professor));

Resposta:

(b) Explique em que situação uma árvore B+ reduz o número de níveis (altura da árvore). Dê um exemplo. Resposta:

O número de níveis da árvore pode reduzir-se quando a remoção de um elemento resulta num conjunto de alterações que se propagam de forma ascendente até à raiz. Por exemplo, na árvore seguinte a remoção do elemento 4 resulta no desaparecimento de uma folha. Dado que passam a existir apenas 3 folhas, é suficiente ter apenas 1 nó no nível imediatamente acima, o que implica reduzir a altura da árvore de 3 para 2.

5. Considere o seguinte conjunto de tabelas, implementado numa base de dados SQL:

produto(id, nome, preço)

loja(codigo, cidade)

vende(codigo, id) codigo: FK(loja) id: FK(produto)

(a) Assuma que não existem quaisquer índices criados. Diga, justificando, sobre quais atributos criaria índices para tornar eficiente uma consulta que devolva os produtos vendidos em “Lisboa” por mais de 1000 euros. Indique quais os índices primários e quais os secundários.

Resposta:

Com base na informação fornecida, os índices a criar seriam:

● índices primários sobre produto.id, loja.codigo e (vende.codigo, vende.id) ● índices secundários sobre loja.cidade e produto.preço

Os índices primários permitiriam optimizar a junção das 3 tabelas. Os índices secundários permitiram encontrar com eficiência todos os produtos com preço > 1000 e todas as lojas na cidade de Lisboa, utilizando os ponteiros dos índices para determinar quais os registos a incluir na junção. Assume-se que o sistema executaria primeiro as selecções de preço e cidade e só depois as junções.

(b) Assuma agora que já existem índices primários sobre todas as chaves primárias. Considere uma consulta que retorna todos os produtos que custam 1500 euros. Seria vantajoso criar um índice sobre o atributo preço? Porquê?

Resposta:

Na generalidade dos casos, não. Se o índice for secundário, apenas permitirá recuperar eficientemente os ponteiros para os registos pretendidos. Posteriormente, recuperar toda a informação nos registos obrigaria a fazer diversos acessos aleatórios, o que poderá ser menos eficiente do que fazer uma pesquisa sequencial. Por essa mesma razão, o índice seria útil apenas se existissem muito poucos registos com preço igual a 1500. (c) Um índice secundário pode ser esparso? Justifique a sua resposta, ilustrando com um exemplo.

Resposta:

Um índice secundário não pode ser esparso. Suponha uma tabela com os atributos “nome” e “idade”, ordenada por nome e com um índice secundário sobre idade. Suponha que a tabela contém registos com idade igual a 50, 25 e 30, e que o índice contem ponteiros apenas para os registos com idade igual a 50 e 25 (sendo, portanto, esparso). Tal índice seria inútil para procurar um registo em que idade seja igual a 30: uma vez que a tabela não está ordenada por idade, seria sempre necessário percorrer todos os registos.

6.

select nome, preço

from produto, loja, vende

where produto.id=vende.id and loja.codigo=vende.codigo and preço>1000

(a) Desenhe o plano de execução mais eficiente possível para esta consulta, usando a notação das aulas teóricas. Justifique a posição de cada operação de projecção e de selecção nesse plano de execução. Resposta:

Neste plano, as operações de projecção e selecção são feitas o mais cedo possível, para reduzir o tamanho das tabelas ao mínimo necessário para fazer as junções.

(b) Suponha que as tabelas produto, loja e vende têm 1000, 500 e 3 tuplos, respectivamente. Porque ordem deveriam ser efectuados os joins? Justifique.

produto

π

σ

loja

π

vende

π

⋈

⋈

Π

Resposta:

Primeiro vende com loja, depois com produto.

Para ser o mais eficiente possível, a junção deve ser feita em primeiro lugar com as menores tabelas. Uma vez que o resultado da junção nunca é maior do que o tamanho da menor tabela envolvida, isto garante que as junções seguintes serão feitas também com as tabelas o mais reduzidas possível.

7. Considere o seguinte escalonamento de transacções:

T1 read(A) read(B) read(A)

T2 read(B) write(B)

T3 write(A) read(B)

T4 read(B)

T5 write(A)

(a) Desenhe o grafo de conflitos para este escalonamento. O escalonamento é serializável? Justifique. Resposta:

O escalonamento não é serializável (por conflitos) porque existem ciclos no grafo.

(b) Apresente a ordem pela qual as transacções devem fazer commit de forma a que não ocorram rollbacks encadeados (cascading rollbacks). Justifique.

Resposta:

Para que não existam cascading rollbacks, uma transacção Ta só pode ler itens escritos por outra transacção Tb depois de Tb ter feito commit. No escalonamento apresentado esta situação ocorre em 2 casos: T3 e T1 com o objecto A; T2 e T3 com o objecto B. Logo, a ordem dos commits no escalonamento dado deveria ser: T2, T3, T1, e a ordem de T4 e T5 é indiferente.

(c) Apresente as transacções, inserindo locks exclusivos e partilhados nas posições adequadas, de modo a que estas obedeçam ao protocolo em duas fases (two-phase locking).

Resposta:

T1: S-lock(A); read(A); S-lock(B); read(B); unlock(B); read(A); unlock(A) T2: X-lock(B); read(B); write(B); unlock(B)

T3: X-lock(A); write(A); S-lock(B); read(B); unlock(A); unlock(B) T4: S-lock(B); read(B); unlock(B)

T5: X-lock(A); write(A); unlock(A)

T1

T3

T2

T4

T5

A

A

B

A

B

A

B

(d) Num sistema que use a estratégia de escrita imediata, é possível uma transacção fazer commit antes dos seus dados serem escritos em disco? Justifique.

Resposta:

Sim. Durante o commit, os dados são escritos num buffer em memória, que apenas é copiado para disco ocasionalmente. Portanto, o commit não garante a escrita dos dados em disco. Só há garantia se tiver havido um checkpoint.

8. Considere a seguinte relação, com o conjunto de dependências dado:

R(A,B,C,D,E) A → BC E → DA

Assuma que a relação está na primeira forma normal. (a) Indique, justificando, todas as chaves candidatas. Resposta:

A -> BC

A -> ABC (porque A -> A)

A, por si só, não determina mais atributos E -> DA

E -> EDA (porque E -> E) E -> ABCDE (porque A -> ABC) logo, E é chave candidata.

Nenhum outro subconjunto de atributos, que não inclua E, determina os restantes. Logo, a única chave candidata é E.

(b) Diga qual é a forma normal mais elevada a que a relação obedece. Justifique. Resposta:

Assumimos que está na 1a forma normal.

Como todos os atributos não chave (A, B,C e D) dependem completamente da chave candidata, a relação está na 2a forma normal.

Como existem dependências entre atributos não-chave (e.g., A -> BC), a relação não está na 3a forma normal. Portanto, a relação está apenas na 2a forma normal.

(c) Apresente uma decomposição da relação para a Forma Normal de Boyce-Codd. Resposta:

R1(A,B,C) R2(A,D,E)

(d)

Resposta:

Uma decomposição com perdas seria: R1(A,B,C)

R2(C,D,E)

Considere, por exemplo que a relação R é a seguinte: R A, B, C, D, E

a1, b3, c5, d8, e9 a2, b5, c6, d2, e1 a2, b3, c5, d3, e2 Neste caso, R1 e R2 seriam: R1 A, B, C a1, b3, c5 a2, b5, c6 a2, b3, c5 R2 C, D, E c5, d8, e9 c6, d2, e1 c5, d3, e2 E a junção de R1 e R2 seria: R1 ⋈ R2 A, B, C, D, E a1, b3, c5, d8, e9 a1, b3, c5, d3, e2 a2, b5, c6, d2, e1 a2, b3, c5, d8, e9 a2, b3, c5, d3, e2