Enunciado - Técnico Lisboa

Número do Aluno:
Nome do Aluno:
Instituto Superior Técnico
Departamento de Engenharia Informática
BASES DE DADOS 2005/06
LEIC e LERCI Tagus Park
3 de Fevereiro 2006
Duração: 2H30
Identifique esta prova preenchendo o seu nome e número nesta página, e o número em
todas as restantes
Resolva a prova nas folhas do enunciado. Pode usar o verso das páginas para responder às
questões.
A prova é sem consulta. Quaisquer tentativas de cópia serão penalizadas com a anulação
da prova.
Página 1 de 11
1º Exame de Base de Dados Tagus 2005/06s
Página 2 de 11 - ALUNO NÚMERO: ..........
P1 – Modelo ER (2,5 v)
a) Desenhe um diagrama ER correspondente ao seguinte esquema relacional:
filme(titulo, ano, direccao)
direccao: FK(realizador)
actor(nome, idade, salario)
realizador(nome, idade)
produtor(nome, ncontrib)
orcamento(id, valor)
actua(nome, titulo, ano)
nome: FK(actor)
titulo, ano: FK(filme)
produz(titulo, ano, nome, id)
titulo, ano: FK(filme)
nome: FK(produtor)
id: FK(orcamento)
b) Volte a desenhar o diagrama da alínea a), acrescentando a seguinte informação:
Um filme pode ser constituído por várias cenas. Cada cena tem um número e data em que
foi filmada. Embora um filme possa não ter cenas, não faz sentido existirem cenas sem
filme.
NOTA: Desenhe apenas a parte relevante do diagrama.
1º Exame de Base de Dados Tagus 2005/06s
Página 3 de 11 - ALUNO NÚMERO: ..........
P2 – Modelos ER e Relacional (2,5 v)
Considere o seguinte diagrama ER:
Assuma que, na base de dados, existem tantas instâncias de insecto como de mamífero.
Construa um modelo relacional correspondente, o mais optimizado possível, sabendo que:
a) A maioria das consultas feitas na base de dados são para saber o tipo de alimento e o
peso de um mamífero, dado o seu id. Muito poucas consultas são feitas sobre insectos.
Justifique as suas decisões.
b) A maioria das consultas são, dado o id de um animal, se este for insecto, saber o
número de patas, se este for mamífero, saber o tipo de alimento. Justifique as suas
decisões.
c) Enumere, se existirem, as restrições de integridade necessárias para os modelos
construídos nas alíneas anteriores.
1º Exame de Base de Dados Tagus 2005/06s
Página 4 de 11 - ALUNO NÚMERO: ..........
P3 – Formas Normais (2v)
a) Dada a relação R(A,B,C,D,E), indique quais as chaves candidatas, considerando as
seguintes dependências funcionais:
i) A->B, BC->E, D->C
ii) D->E, E->D
b) Dada a relação R(A,B,C,D,E), indique em que forma normal esta se encontra, sabendo
que:
i) ABC é a única chave candidata e D->E
ii) ABC e CD são as únicas chaves candidatas e A->D
Justifique detalhadamente a sua resposta.
1º Exame de Base de Dados Tagus 2005/06s
Página 5 de 11 - ALUNO NÚMERO: ..........
P4 – Álgebra Relacional (3v)
Considere o seguinte esquema relacional em que as chaves primárias estão sublinhadas e
as chaves estrangeiras estão em itálico:
Tratador (bi, nome, idade, salário)
Cao (cid, nome, raça)
DarBanho (bi, cid)
O que significa a seguinte expressão de álgebra relacional:
a) DarBanho ÷ Πcid (σraça = “Boxer” (Cao))
Escreva as seguintes interrogações em Álgebra Relacional:
b) Modifique a base de dados, de forma a que todos os tratadores que dão banho a pelo
menos 5 cães tenham 20% de aumento. Os restantes tratadores devem ter 5% de
aumento.
c) Qual o bi e nome dos tratadores que têm menos 30 anos e dão banho a pelo menos um
cão de raça “Bulldog”.
1º Exame de Base de Dados Tagus 2005/06s
Página 6 de 11 - ALUNO NÚMERO: ..........
P5 – Linguagem SQL (3,5v)
Considere o seguinte esquema relacional em que as chaves primárias estão sublinhadas e
as chaves estrangeiras estão em itálico:
Campeonato (cid, época, descrição)
Equipa (eid, nome)
Jogo (cid, eid1, eid2, golosEquipa1, golosEquipa2, data)
a) Diga, detalhadamente, o que significa a chave primária da relação Jogo. Justifique
na sua resposta se é possível ter mais do que um jogo entre as mesmas equipas, no
mesmo campeonato.
Escreva as seguintes interrogações em SQL. (Pode usar o verso da página)
b) Qual o nome de todas as equipas que defrontaram a equipa de nome “Dream team”
no(s) campeonato(s) da época “2004/2005”.
c) Liste o nome das equipas que tiveram mais vitórias no(s) campeonato(s) da época
“2004/2005” (nota: considera-se uma vitória num jogo quando a equipa marca mais
golos do que a equipa adversária)
d) Liste o nome das equipas que só têm vitórias no(s) campeonato(s) da época
“2004/2005”
1º Exame de Base de Dados Tagus 2005/06s
Página 7 de 11 - ALUNO NÚMERO: ..........
P6 – Triggers e PL/SQL (1v)
Considere os seguintes triggers implementados no âmbito do projecto da cadeira:
CREATE OR REPLACE TRIGGER TR4
AFTER INSERT OR UPDATE OF CODROTINA ON ROTINA
FOR EACH ROW
DECLARE
CTRL NUMBER;
BEGIN
SELECT COUNT(*) INTO CTRL
FROM CONSTITUIDA
WHERE CODROTINA = :NEW.CODROTINA;
IF CTRL = 0
THEN RAISE_APPLICATION_ERROR (-20002, 'A
exercício');
END IF;
END;
/
rotina
tem
pelo
menos
CREATE OR REPLACE TRIGGER TR4a
AFTER DELETE OR UPDATE ON CONSTITUIDA
DECLARE
CTRL NUMBER;
BEGIN
SELECT COUNT(*) INTO CTRL
FROM ROTINA R
WHERE R.CODROTINA NOT IN (SELECT C.CODROTINA FROM CONSTITUIDA C);
IF CTRL > 0
THEN RAISE_APPLICATION_ERROR (-20003, 'A rotina tem pelo menos
exercício');
END IF;
END;
um
um
e as seguintes relações envolvidas nos triggers:
Rotina(codRotina, idCategoriaRotina)
idCategoriaRotina: FK(CategoriaRotina)
not null(idCategoriaRotina)
Exercicio (codExercicio, descricao)
not null(descricao)
Constituida (codRotina, codExercicio)
codRotina: FK(Rotina)
codExercicio: FK(Exercicio)
Explique porque são necessários os dois triggers. O que poderia acontecer se só um deles
fosse implementado? (Pode usar o verso da página para responder sucinta e
objectivamente à pergunta)
1º Exame de Base de Dados Tagus 2005/06s
Página 8 de 11 - ALUNO NÚMERO: ..........
P7 – Transacções, Controlo de concorrência e Gestão de recuperação (1 v)
Assinale com V as afirmações verdadeiras e F as afirmações falsas:
O protocolo strict two-phase locking evita cascading rollbacks
O processo de recuperação de uma base de dados em modo de modificação “deferred”,
usa operações de undo e redo
Usando o nível de isolamento read commited, os locks exclusivos só são libertados
quando a transacção termina
A propriedade de consistência de uma transacção garante que as alterações feitas por
uma transacção terminada com sucesso, se mantêm com uma falha no sistema
1º Exame de Base de Dados Tagus 2005/06s
Página 9 de 11 - ALUNO NÚMERO: ..........
P8 – Controlo de concorrência (1,5 v)
Considere o seguinte esquema de base de dados relacional:
Aluno(anum, anome, major, idade)
Disciplina(dnome, pnum)
Inscricao(anum, dnome)
Professor(pnum, pnome, deptid)
Em que as chaves primárias estão sublinhadas e as estrangeiras em itálico
Para cada uma das transacções que se segue, diga qual o nível de isolamento SQL que
deve usar e explique porquê.
a) Inscrever um aluno identificado pelo seu anum na disciplina denominada “Bases de
Dados”.
b) Actualizar a inscrição de um aluno identificado pelo seu anum, mudando a disciplina
em que se encontra inscrito.
c) Atribuir um novo professor identificado pelo seu pnum à disciplina com o menor
número de alunos inscritos.
1º Exame de Base de Dados Tagus 2005/06s
Página 10 de 11 - ALUNO NÚMERO: ..........
P9 – XML (2v)
Considere o seguinte documento XML:
<bank-2>
<account account_number=“A-401” owners=“C100 C102”>
<branch_name> Downtown </branch_name>
<balance> 500 </balance>
</account>
<customer customer_id=“C100” accounts=“A-401”>
<customer_name>Joe </customer_name>
<customer_street> Monroe </customer_street>
<customer_city> Madison</customer_city>
</customer>
<customer customer_id=“C102” accounts=“A-401 A-402”>
<customer_name> Mary </customer_name>
<customer_street> Erin </customer_street>
<customer_city> Newark </customer_city>
</customer>
</bank-2>
a) Diga qual a diferença entre as duas expressões XPath:
/bank-2/account[balance > 400]
/bank-2/account[balance > 400]/@account_number
b) Escreva a expressão XPath que retorna os elementos de conta que contêm um subelemento “balance”.
1º Exame de Base de Dados Tagus 2005/06s
Página 11 de 11 - ALUNO NÚMERO: ..........
P10 – Índices e Tuning (1v)
Indique se as seguintes afirmações são verdadeiras ou falsas:
a) Num índice denso, existe uma entrada no índice por cada valor da chave de procura
b) Num índice esparso, existe uma entrada no índice por cada valor da chave de
procura.
c) Numa estratégia de avaliação “index-only plan”, o índice deve ser clustered (ou
primário)
d) Um índice hash-based clustered é útil numa interrogação cuja claúsula where
envolve uma desigualdade sobre um atributo que contém valores duplicados.