Bases de Dados 2007/2008 Exame

Bases de Dados 2007/2008
Exame
11 de Janeiro de 2007
Instituto Superior Técnico
Departamento de Engenharia Informática
Regras
• O exame tem a duração de 2h30m.
• A folha de capa deve ser identificada com o nome e número do
aluno.
• Todas as restantes folhas devem ser identificadas com o número
do aluno.
• O exame deve ser resolvido nas folhas do enunciado.
• O enunciado já inclui espaço para rascunho, pelo que não são
permitidas folhas de rascunho.
• Os alunos devem ter em cima da mesa apenas o material para
escrita e a sua identificação.
• O exame é sem consulta.
Boa sorte!
Número do aluno:
Nome do aluno:
Bases de Dados 2007/2008
Aluno:
1
Considere as seguintes relações, usadas na base de dados de gestão de um condomı́nio
fechado nos arredores de Lisboa:
morador(id, nome, idade, sexo, salario)
apartamento(bloco, andar, numero, tipo, renda)
habita(id, bloco, andar, numero)
id : F K(morador), bloco, andar, numero : F K(apartamento)
usou(id, servico, data)
id : F K(morador)
Escreva, em álgebra relacional, as seguintes interrogações:
(a) Quais os nomes dos moradores do bloco ’B’ que usaram o serviço de sauna no dia
1/1/2008?
(b) Quantos apartamentos não habitados tem o bloco ’C’ ?
(c) Quais os apartamentos cuja soma dos salários dos seus moradores é menor do que a
sua renda?
(0,7+0,8+1,0 = 2,5 valores)
Folha 2 de 14
Bases de Dados 2007/2008
Aluno:
2
Um restaurante italiano vende pizzas de vários tamanhos e com vários ingredientes. O
cliente pode encomendar pizzas disponı́veis no menu (p.ex. Tropical, Havaiana, etc.) ou
pode também encomendar pizzas com ingredientes à escolha. Neste caso deve indicar, para
além do tamanho da pizza, a lista de ingredientes que pretende.
A figura seguinte mostra parte das tabelas relevantes da base de dados do restaurante.
pizza
composicao
ingrediente
(a) Escreva uma consulta em SQL para determinar quais as pizzas do menu que têm
maior número de ingredientes.
(b) Existe alguma pizza média no menu que custe tanto ou mais do que uma pizza
familiar de outro tipo? Escreva uma consulta em SQL que mostre o nome dessa
pizza, se existir.
(c) Um cliente pretende encomendar uma pizza pequena com os seguintes ingredientes:
Ananás, Atum e Cogumelos. Existe alguma pizza de menu que contenha estes ingredientes? Escreva uma consulta em SQL para responder a esta pergunta.
(0,7+0,8+1,0 = 2,5 valores)
Folha 3 de 14
Bases de Dados 2007/2008
Aluno:
3
Considere novamente a base de dados da pergunta anterior. Escreva uma função que
devolve a diferença de preço entre encomendar uma pizza de menu e encomendar uma
pizza à escolha, com o mesmo tamanho e os mesmos ingredientes. A função recebe como
parâmetros o nome e o tamanho da pizza.
(2 valores)
Folha 4 de 14
Bases de Dados 2007/2008
Aluno:
4
Considere o seguinte universo de discurso:
“Os empregados da empresa ABC trabalham em diversos projectos. Cada
empregado usa um conjunto de ferramentas especı́ficas para realizar um dado
projecto. É necessário contabilizar o tempo que os empregados dedicam aos
projectos assim como o respectivo tempo de utilização de cada ferramenta em
cada projecto”.
(a) Apresente um diagrama Entidade-Associação para este universo de discurso sem fazer uso
dos conceitos de agregação, entidade fraca e participação total.
(b) Fará sentido aplicar algum desses conceitos a este cenário? Justifique e, em caso
afirmativo, apresente um novo diagrama E-A.
(c) Converta o modelo da alı́nea (b) para um modelo relacional e especifique uma restrição de integridade não capturada.
(1,0+0,7+0,8 = 2 valores)
Folha 5 de 14
Bases de Dados 2007/2008
Aluno:
5
Considere a relação
R(A, B, C, D, E)
em que se verifica o seguinte conjunto de dependências funcionais:
A → BC
CD → E
B→D
E→A
(a) Indique as chaves candidatas da relação R.
(b) A relação encontra-se na 3FN? Justifique.
(c) Indique uma decomposição para FNBC que seja lossless-join. Justifique.
(d) Indique se a decomposição apresentada na alı́nea anterior preserva todas as restrições
de integridade. Justifique.
(0,5+0,7+0,8+0,5 = 2,5 valores)
Folha 6 de 14
Bases de Dados 2007/2008
Aluno:
6
(a) Explique o que faz o seguinte comando SQL. A ordem de especificação das colunas
tem algum impacto na estrutura de indexação e/ou na pesquisa? Justifique.
CREATE INDEX idx ON MyTable(x, y, z)
(b) Justifique quais dos seguintes três comandos SQL poderão fazer uso do ı́ndice idx,
definido acima.
SELECT x, y, z
FROM MyTable
WHERE
x=10 AND y<15 AND
z>=20
(1)
SELECT x, y, z
FROM MyTable
WHERE
x=10 AND y<15
SELECT x, y, z
FROM MyTable
WHERE y<15
(2)
(3)
(c) Um ı́ndice secundário pode ser esparso? Justifique a sua resposta.
(0,5+0,5+0,5 = 1,5 valores)
Folha 7 de 14
Bases de Dados 2007/2008
Aluno:
7
Considere uma árvore B+ vazia, com n = 3. Desenhe a árvore passo a passo, mostrando
apenas as alterações, ao inserir os seguintes valores: 5, 8, 1, 7, 3, 12, 9, 6.
(2 valores)
Folha 8 de 14
Bases de Dados 2007/2008
Aluno:
8
Considere a seguinte relação:
R(a, b, c, d)
Assuma que:
• Todos os atributos são inteiros;
• O atributo d e a chave primária encontram-se indexados, com ı́ndices do tipo árvore B+;
• A relação tem cerca de 100 registos.
Escreva em álgebra relacional uma expressão que obrigue o SGBD a usar:
(a) Exclusivamente um algoritmo de selecção usando ı́ndice primário (e mais nenhum
outro algoritmo de selecção)
(b) Exclusivamente um algoritmo selecção por pesquisa sequencial (e mais nenhum outro
algoritmo de selecção), com o atributo a fazendo parte dos critérios de pesquisa.
(c) Selecção com dois critérios que permita localizar directamente o registo (sem pesquisa
sequencial).
(0,5+0,5+0,5 = 1,5 valores)
Folha 9 de 14
Bases de Dados 2007/2008
Aluno:
9
Considere as seguintes expressões:
πloan number,customer name
¡
σcustomer city=′ Lisboa′ ∧amount>1000 (borrower ⊲⊳ loan ⊲⊳ branch ⊲⊳ customer)
σcustomer city=′ Lisboa′ ∧amount>1000
¢
(1)
¢
(2)
¡
πloan number,customer name (borrower ⊲⊳ loan ⊲⊳ branch ⊲⊳ customer)
referente ao seguinte modelo relacional:
branch(branch name, branch city, assets),
customer(customer name, customer street, customer city),
loan(loan number, branch name, amount),
account(account number, branch name, balance),
borrower(customer name, loan number),
depositor(customer name, account number, access date),
com
com
com
com
com
com
100
100
300
500
300
500
registos
000 registos
000 registos
000 registos
000 registos
000 registos
(a) As expressões (1) e (2) são equivalentes? Em caso afirmativo, justifique fazendo uso
das regras de equivalência. Em caso negativo, justifique com um exemplo ou descreva
a razão.
(b) Indique a expressão equivalente à expressão (1) que seja o mais eficiente possı́vel
(considerando as dimensões das relações). Justifique.
(c) Elabore o plano de execução (em “árvore”) para a expressão (1) e para a expressão
proposta na alı́nea anterior.
(0,5+1,0+0,5 = 2 valores)
Folha 10 de 14
Bases de Dados 2007/2008
Aluno:
10
Considere a seguinte figura, onde se mostram 4 grafos (G1 a G4) do número de locks que
as transacções T1 a T4 têm ao longo do tempo.
Indique quais os grafos que garantem escalonamentos recuperáveis, caso haja uma falha
do sistema. Explique porquê.
(1,5 valores)
Folha 11 de 14
Bases de Dados 2007/2008
Aluno:
Página para Rascunho
Folha 12 de 14
Bases de Dados 2007/2008
Aluno:
Página para Rascunho
Folha 13 de 14
Bases de Dados 2007/2008
Aluno:
Página para Rascunho
Folha 14 de 14