Bases de Dados 2007/2008 Exame 11 de Janeiro de 2008 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 Número: 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 Número: 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 Número: 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 Número: 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. (0,8+0,6+0,6 = 2 valores) Folha 5 de 14 Bases de Dados 2007/2008 Número: 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 Número: 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 Número: 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 Número: 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 Número: 9 Considere as seguintes expressões: πloan number,customer name ¡ ¢ σcustomer city=0 Lisboa0 ∧amount>1000 (borrower ./ loan ./ branch ./ customer) (1) σcustomer city=0 Lisboa0 ∧amount>1000 ¡ ¢ πloan number,customer name (borrower ./ loan ./ branch ./ customer) (2) 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 Número: 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 Número: Página para Rascunho Folha 12 de 14 Bases de Dados 2007/2008 Número: Página para Rascunho Folha 13 de 14 Bases de Dados 2007/2008 Número: Página para Rascunho Folha 14 de 14