Exercícios de Modelo Relacional

Número do Aluno:
Nome do Aluno:
Instituto Superior Técnico
Departamento de Engenharia Informática
BASES DE DADOS 2004/05
LEIC e LERCI Tagus Park
14 de Janeiro 2005
1º Exame
2º Teste
- o exame é composto por 14 perguntas
- inclui as perguntas P1 a P14
- duração: 2h:30m
- o 2º teste é composto por 8 perguntas
- inclui as perguntas P7 a P14
- duração: 1h:15m
- cada pergunta vale o dobro do que
está indicado
Indique se está a prestar provas para o:
1º Exame

ou
2º Teste

Identifique esta prova preenchendo o seu nome e número nesta página, e o número em
todas as restantes
A prova é sem consulta.
Página 1 de 16
1º Exame e 2º Teste de Base de Dados
Página 2 de 16 - ALUNO NÚMERO: ..........
P1 – Modelo Relacional (2v)
Responda a cada alínea em baixo com sim/não ou um número.
Suponha que tem duas relações R(A,B) e S(B,C). Os três atributos (A, B, C) são inteiros.
Assuma que a relação R contém os seguintes tuplos: (1,2), (2,3) e (3,4) e que a relação S
contém os tuplos: (2,2), (2,3) e (5,1).
a) A é uma chave de R? Resposta (sim/não):
b) b) (B, C) é uma superchave de S? Resposta (sim/não):
c) Por quantos tuplos é constituído o resultado do produto cartesiano entre R e S?
Resposta (um número):
d) Por quantos tuplos é constituído o resultado da junção natural entre R e S?
Resposta (um número):
1º Exame e 2º Teste de Base de Dados
Página 3 de 16 - ALUNO NÚMERO: ..........
P2 – Modelo ER (2v)
CuidadosMédicos.com necessita de guardar numa base de dados a seguinte informação:




Os doentes são identificados pelo seu número de segurança social, e pretende-se
também guardar os seus nomes e idades.
Os médicos são identificados pelo seu número de segurança social e queremo
também guardar o seu nome e especialidade.
Cada doente tem que ter um medico de família e queremos saber desde quando o
doente é assistido por esse médico.
Cada médico tem pelo menos um doente.
a) Desenhe um diagrama ER que capture esta informação.
b) Indique uma possível restrição de integridade do domínio que não conseguisse
capturar no diagrama ER.
1º Exame e 2º Teste de Base de Dados
Página 4 de 16 - ALUNO NÚMERO: ..........
P3 – Álgebra Relacional (2v)
Considere o seguinte esquema relacional:
Fornecedores(fid, fnome, morada)
Peças(pid, pnome, cor)
Catálogo(fid, pid, preço)
A relação Fornecedores contém informação sobre os fornecedores de peças. A relação
Peças contém informação sobre cada peça. A relação Catálogo lista os preços em euros de
cada peça fornecida por um fornecedor.
As chaves primárias estão sublinhadas.
Escreva as interrogações seguintes em álgebra relacional:
a) Quais os nomes dos fornecedores que fornecem todas as peças azuis
b) Quais os nomes dos fornecedores que fornecem exactamente três peças.
1º Exame e 2º Teste de Base de Dados
Página 5 de 16 - ALUNO NÚMERO: ..........
P4 – Formas Normais (1v)
Considere as relações da figura seguinte. Admita que a figura seguinte representa um
esquema relacional, no qual estão apenas representados relações (rectângulos), atributos
(dentro da parte mais clara rectângulos) e chaves estrangeiras (as setas com origem e
destino em atributos das relações). Assuma que cada vez que é criado um registo (tuplo)
na relação loan, todos os seus atributos são preenchidos: (i) loan-number é preenchido
com um número único, sendo a chave primária desta relação; (ii) date é preenchida com a
data/hora de criação do registo (iii) amount é o valor do empréstimo feito, (iv) branchname é preenchido com o nome da sucursal (branch) onde o empréstimo foi feito, sendo
uma chave estrangeira para branch:branch-name; (v) branch-city, branch-ponhe-number e
branch-manager-name são preenchidos com uma cópia do valor dos atributos
correspondentes na relação branch.
branch
loan
branch-name
branch-city
assets
branch-phone-number
branch-manager-name
date
loan-number
branch-name
amount
date
branch-phone-number
branch-namager-name
branch-city
Indique a afirmação verdadeira:
a) A relação loan não está 2 FN.
b) A relação loan está na 2 FN, mas não está na 3 FN.
c) A relação loan está na 3 FN, mas não está na BCNF.
d) A relação loan está na BCNF.
e) Nenhuma das hipóteses anteriores.
1º Exame e 2º Teste de Base de Dados
Página 6 de 16 - ALUNO NÚMERO: ..........
P5 – Linguagem SQL (2v)
Considere as seguintes tabelas com os seguintes registos.
depositor
customer
account
Escreva em SQL a seguinte interrogação: Qual o saldo médio e o número de contas de
cada sucursal, tal como está indicado na tabela seguinte:
branch-name
avg-balance
Nr accounts
Redwood
700
1
Mianus
700
1
Perryridge
400
1
Round Hill
350
1
Brighton
825
2
Downtown
500
1
1º Exame e 2º Teste de Base de Dados
Página 7 de 16 - ALUNO NÚMERO: ..........
P6 – Linguagem SQL (1v)
Considere as tabelas da pergunta anterior.
Indique a interrogação em SQL 92 cujo resultado são os pares de nomes de clientes que
moram na mesma cidade, eliminando os pares repetidos. Por exemplo, se o João e o
António moram na mesma cidade, pretende-se que a resposta inclua os pares (João,
António) e (António, João). Obviamente que não se pretende pares do tipo: (João, João) e
(António, António).
a) SELECT DISTINCT customer1.customer-name, customer2.customer-name
FROM customer AS customer1, customer AS customer2
WHERE customer1.customer-city = customer2.customer-city
b) SELECT custumer1.customer-name, customer2.customer-name
FROM customer AS customer2, customer AS customer1
WHERE customer1.customer-city = customer2.customer-city
AND customer1.customer-name <> customer2.customer-name;
c) SELECT Tx.customer-name, Ty.customer-name
FROM customer AS Tx
WHERE Tx.customer-city =
(SELECT customer-city FROM customer AS Ty
WHERE Tx.customer-city = Ty.customer-city);
d) SELECT customer-name (SELECT customer-city FROM customer AS Ty)
FROM
customer as Tx WHERE Tx.customer-city =
(SELECT customer-city FROM customer)
e) Nenhuma das hipóteses anteriores
1º Exame e 2º Teste de Base de Dados
Página 8 de 16 - ALUNO NÚMERO: ..........
P7 – Desenho de aplicações de bases de dados (2v)
a) Indique uma vantagem e uma desvantagem de usar Embedded SQL em vez de usar
uma API como ODBC/JDBC para invocar comandos SQL a partir de uma programa
em C ou Java. Justifique.
b) Considere o seguinte código Java com chamadas JDBC e suponha que a variável
stmt foi devidamente declarada e inicializada:
try {
ResultSet rset = stmt.executeUpdate( "select branch_name,
avg(balance) from account group by branch_name");
while (rset.next()) {
System.out.println(rset.getInt("branch_name")
rset.getFloat(2));
+
“
}
} catch (SQLException e) {
System.out.println("Could not execute JDBC call: " + e);
}
Indique dois erros no código e como os corrigir.
"
+
1º Exame e 2º Teste de Base de Dados
Página 9 de 16 - ALUNO NÚMERO: ..........
P8 – Transacções e Gestão de Recuperação (1v)
Considere a figura seguinte, a qual representa a execução de 5 transacções num sistema
que fez o checkpoint no momento Tc que falhou no momento Tf e que arrancou no
momento Ta. Chamemos de REDO(Ti) à operação que repõe os valores finais dos dados
alterados pela transacção Ti e UNDO(Ti) a operação que repõe os valores iniciais dos dados
alterados pela transacção Ti.
T1
Temp
o
T2
T3
T5
Tc
checkpoint
O Sistema inicia a transacção
O Sistema faz o COMMIT da
transacção
T4
Tf
Falha do Sistema
Ta
Arranque do Sistema
Indique a sequência das operações REDO() e UNDO() que o sistema deverá fazer quando
arrancou em Ta.
Nota: Relembre que durante a operação de checkpointing, todos os registos do ficheiro de
log que residam em memória principal são escritos para memória estável e que todos os
registos de dados modificados que residam em memória principal são escritos para disco.
a) REDO(T5), REDO(T3), UNDO(T5), UNDO(T4)
b) UNDO(T5), UNDO(T4), REDO(T2), REDO(T3)
c) REDO(T1), REDO(T2), REDO(T3), UNDO(T5), UNDO(T4)
d) REDO(T1), UNDO(T5), UNDO(T4), REDO(T2), REDO(T3)
e) Nenhuma das hipóteses anteriores.
1º Exame e 2º Teste de Base de Dados
Página 10 de 16 - ALUNO NÚMERO: ..........
P9 – Gestão de transacções (1v)
Relativamente à pergunta P10, assuma que a lógica de cada transacção (T1 a T5) leva a
base de dados de um estado consistente para outro estado consistente e diga qual ou
quais das propriedades ACID o sistema visa assegurar quando, após o arranque do
sistema, inicia a sequência das operações de UNDO() e REDO():
a) O isolamento entre as transacções.
b) A atomicidade e durabilidade das transacções.
c) Todas as propriedades ACID.
d) Nenhuma das propriedades ACID.
e) Nenhuma das hipóteses anteriores.
1º Exame e 2º Teste de Base de Dados
Página 11 de 16 - ALUNO NÚMERO: ..........
P10 – Gestão de transacções (1v)
Considere as transacções Tduplica, Tquadrado Tmetade, que, respectivamente, duplica, eleva ao
quadrado ou reduz para metade o valor do atributo A. Considere que o sistema
transaccional disponibiliza as funções BEGIN_TRANSACTION e COMMIT_TRANSACTION
para, respectivamente, iniciar e fazer COMMIT às transacções. Considere a sequência
temporal apresentada na figura seguinte relativa ao momento em que estas funções são
chamadas pelas transacções.
Tduplica
Tquadrado
Temp
o
Tmetade
Chamada à função BEGIN_TRANSACTION
Chamada à função COMMIT_TRANSACTION
Note-se que o círculo e o losango indicam as chamadas ás funções indicadas mas não
indicam necessariamente a conclusão das mesmas por parte do sistema transaccional.
Assuma o nível de isolamento SERIALIZABLE para as três transacções.
Assumindo que A tem um valor inicial de 10, indique que valores podem ser considerados
correctos para o valor final de A.
a) O valor de A só pode ser 200, o resultado da sequência : Tduplica; Tquadrado;
Tmetade
b) O valor de A só pode ser 100, o resultado da sequência : Tduplica; Tmetade;
Tquadrado
c) O valor de A só pode ser 100, o resultado da sequência : Tquadrado; Tmetade;
Tduplica
d) O valor de A pode ser o equivalente a qualquer uma 6 das sequências possíveis:
200 (Tduplica; Tquadrado; Tmetade)
100 (Tduplica; Tmetade; Tquadrado)
100 (Tquadrado; Tmetade ; Tduplica)
100 (Tquadrado; Tduplica;Tmetade)
100 (Tmetade; Tduplica, Tquadrado)
50 (Tmetade; Tquadrado; Tduplica)
e) Nenhuma das hipóteses anteriores.
1º Exame e 2º Teste de Base de Dados
Página 12 de 16 - ALUNO NÚMERO: ..........
P11 – Controlo de concorrência (1v)
Considere os 4 gráficos abaixo (G1 a G4), cada um correspondendo a uma política diferente
de reserva e libertação de locks. Considere ainda que todos os locks (partilhados e
exclusivos) são libertados na segunda marca temporal de todos os gráficos.
Assinale quais das seguintes afirmações são verdadeiras e quais as falsas:
a) A política expressa no gráfico G1 evita a ocorrência de deadlocks.
b) A política expressa no gráfico G2 evita a existência de rollbacks em cascata.
c) Só a política expressa no gráfico G3 conduz a schedules serializáveis.
d) A política expressa no gráfico G4 detecta a existência de deadlocks
e) A política expressa no gráfico G4 evita a existência de deadlocks
1º Exame e 2º Teste de Base de Dados
Página 13 de 16 - ALUNO NÚMERO: ..........
P12 – Controlo de concorrência (1v)
Considere o esquema relacional ilustrado na figura seguinte, na qual estão apenas
representados: relações (rectângulos), atributos (dentro da parte mais clara rectângulos) e
chaves estrangeiras (as setas com origem e destino em atributos das relações).
branch
account
depositior
customer
branch-name
branch-city
assets
account-number
branch-name
balance
customer-name
account-number
customer-name
customer-city
customer-street
Considere ainda duas transacções, T1 e T2, que executam, respectivamente, as operações
Q1 e Q2, entre outras, de forma concorrente.
Q1 – SELECT SUM(balance) FROM account WHERE branch-name = “Sucursal-1”
Q2 – INSERT INTO account(account-number, branch-name, balance)
VALUES (“NC-2345”, “Sucursal-1”, 12345)
Considere ainda que ambas as transacções estão a executar estas operações com o nível
de isolamento REPEATABLE READ. Indique as frases verdadeiras.
a) Q1 nunca lê registos inseridos por Q2.
b) Q1 só lê registos inseridos por Q2 quando T2 fizer commit.
c) Considerando um schedule S que envolve a execução de T1 e T2, um schedule série
equivalente a S é: T1;T2
d) Considerando um schedule S que envolve a execução de T1 e T2, um schedule série
equivalente a S é: T2;T1
e) Nenhuma das hipóteses anteriores
1º Exame e 2º Teste de Base de Dados
Página 14 de 16 - ALUNO NÚMERO: ..........
P13 - Bases de Dados Objecto-Relacional (1v)
Considere a relação flat-books que se encontra na 1ª forma normal:
Indique a relação resultante da aplicação da seguinte interrogação:
select title, author, Publisher(pub_name, pub_branch) as
publisher, set(keyword) as keyword-list
from flat-books
group by title, author, publisher
1º Exame e 2º Teste de Base de Dados
Página 15 de 16 - ALUNO NÚMERO: ..........
P14 – XML (2v)
Considere os seguintes dados sob o formato relacional:
Id Duração
Título
Compositor
Maestro
3
1:01
Mad Rush
J. Sibelius
4
1:47
L. Beethoven
5
1:57
Andante
Upon
Enchanted
Ground
L.
Bernstein
NULL
A. Hovhaness
NULL
Id
13
55
56
57
Pai
3
5
5
5
Cantor
A. Karis
Y. Kondonassis
F. Hendrickx
H. Coryn
a) Escreva o documento XML obras.xml obtido depois de exportar os dados relacionais
acima para a seguinte DTD:
<!ELEMENT obras (peça*)>
<!ELEMENT peça (duração, título, compositor, maestro?, cantor*>
<!ELEMENT duração (#PCDATA)>
<!ELEMENT título (#PCDATA)>
<!ELEMENT compositor (#PCDATA)>
<!ELEMENT maestro (#PCDATA)>
<!ELEMENT cantor (#PCDATA)>
<!ATTLIST peça id ID REQUIRED>
1º Exame e 2º Teste de Base de Dados
Página 16 de 16 - ALUNO NÚMERO: ..........
b) Escreva uma interrogação em XQuery que retorne todos os maetros que dirigiram
"Mad Rush" de "J. Sibelius". O resultado não deve conter duplicados.