X - Departamento de Sistemas de Informação

Ministério da Educação
Nome: .................................................................................................................................................
................................................................................................
Número:
Unidade Lectiva de: Sistemas de Bases de Dados
Código
Elaborado em: 2002
Teste Formativo Nº
2611
1
INFORMAÇÕES
1)
2)
3)
4)
5)
6)
7)
Este teste formativo tem um tempo de resolução de duas horas e trinta minutos.
Exclui-se deste tempo o tempo de implementação e utilização do computador.
A cotação de cada problema encontra-se imediatamente antes do seu enunciado.
O exame será feito sem CONSULTA.
Aconselha-se o aluno(a) a tentar implementar cada um dos problemas apresentados num
computador. É impossível aprender as diversas técnicas de programação apresentadas
sem utilizar um computador para exercitar os conhecimentos adquiridos. Só através da
utilização de um verdadeiro sistema de base de dados, como seja o sistema da
ORACLE, DB2 da IBM, ou SQL-Server da Microsoft, ou as alternativas grátis como o
MySql (http://mysql.org) ou Postgres (http://cygwin.org) irá ganhar a prática e
segurança necessárias para a compreensão dos diversos aspectos da utilização,
configuração e programação de aplicações utilizando sistemas de bases de dados
relacionais cobertos nesta cadeira. Em muitos exercícios, a utilização do computador
pode inclusive servir para confirmar a sua resposta..
Deve assinalar e JUSTIFICAR todas as opções tomadas.
a) No código dos seus programas SQL, qualquer aspecto que considere menos claro
deverá ser explicado, quando possível em comentário ou então em texto esplicativo.
A clareza da sua solução também será avaliada.
b) Nas deduções formais, não esqueça de indicar qual a justificação de cada
transformação efectuada. Se desejar, pode, no inicio da sua prova definir um
conjunto de abreviaturas para as várias definições e notações utilizadas.
Nas várias soluções apresentadas procurou-se favorecer a clareza das soluções sobre
qualquer outro critério de eficiência/economia.
No caso de persistirem dúvidas, de desejar fazer sugestões ou de ter encontrado algum
erro, agradece-se desde já que entre em contacto com o docente da cadeira. Consulte a
página web da cadeira no endereço: http://www.univ-ab.pt/disciplinas/dcet/sbd2611
Deve tentar responder às questões propostas antes de ver a sua resolução. Depois, e
ainda antes de ver a resolução de um dado problema, tente implementar esse problema
no
computador.
LEMBRE-SE
QUE
SÓ
PRATICANDO
E
EXPERIMENTANDO POR SI, PODE APRENDER AS DIVERSAS
TÉCNICAS APRESENTADAS NA CADEIRA.
Este teste formativo cobre a matéria referente aos módulos de estudo 1-3 e 5-6 do
caderno de apoio.
I
Considere a seguinte relação r(ABC):
A
x
y
z
z
r
B
2
1
1
1
C
a
a
a
b
a) Calcule ~r (~ é o complemento activo).
b) Para a relação r apresentada indique que dependências funcionais se verificam.
c) A relação r decompõe-se sem perdas nas relações s(AB) e t(BC)?
II
Considere a seguinte Base de Dados filmes, seus realizadores e actores:
Filmes (cod_filme, nome_filme, cod_realizador, ano)
Actores (cod_actor, nome_actor, sexo_actor)
Realizadores (cod_realizador, nome_realizador)
Participações (cod_actor, cod_filme )
cujas chaves primárias estão a sublinhado.
a) Expresse em Álgebra Relacional a pergunta: “Qual o nome dos actores que participaram no
último filme do realizador Spielberg?”
b) Expresse em Álgebra Relacional a pergunta: “Quais os co-actores (i.e. actores que
pertencem ao elenco de um mesmo filme) de sexo oposto que participaram em filmes
realizados por Spielberg?”
c) Diga o que calcula a expressão nome_actor ((Participações / X )  Actores)
em que X = cod_filme(nome_realizador=Spielberg (Filmes Realizadores))
d) Diga o que calcula a expressão
nome_realizador ((cod_realizador(ano(X Filmes)  Filmes))  Realizadores)
em que X = cod_realizador(nome_realizador=Spielberg (Realizadores))
III
Considere um esquema relacional R(ABCD) com chave primária (A,B).
a)
Indique um conjunto de dependências F sobre esquema relacional R tal que R esteja na
1NF mas não esteja na 2NF.
b)
Indique um conjunto de dependências F sobre esquema relacional R tal que R esteja na
2NF mas não esteja na 3NF.
IV
Considere o esquema relacional R(ABCDE) e conjunto de dependências funcionais
F= {C -> D, C -> A, B -> C, A-> E}.
a) Indique qual a forma normal em que se encontra a relação R.
b) O esquema acima não está na forma BCNF. Justifique.
c) Normalize o esquema R acima para a forma BCNF.
d) Indique se a decomposição obtida é com ou sem perdas, e se existe preservação das
dependências funcionais.
FIM
Ministério da Educação
Nome: .................................................................................................................................................
................................................................................................
Número:
Unidade Lectiva de: Sistemas de Bases de Dados
Código
Elaborado em: 2002
Teste Formativo Nº
2611
2
Este teste formativo cobre a matéria referente ao módulo de estudo 4 do caderno de
apoio.
Imagine que foi contratado pelos serviços académicos do Instituto Politécnico de
Engenharia Aplicada ao Cinema para implementar a base de dados dos serviços
académicos. Após a fase de análise de sistemas, foram-lhe fornecidos os seguintes
elementos para a base de dados da referida instituição.
ER ou DEA:
Dicionário de dados:
aluno(num_aluno, nome_aluno, local_aluno, data_nsc_aluno, sexo_aluno, cod_curso)
curso(cod_curso, nome_curso)
cadeira(cod_cadeira, nome_cadeira, cod_departamento)
curso_cadeira ( cod_curso, cod_cadeira, creditos)
inscricao (num_aluno, cod_curso, cod_cadeira, data_inscricao, data_avaliacao, nota);
departamento (cod_departamento, nome_departamento)
professor (cod_professor, nome_professor, cod_departamento, cod_categoria)
categoria (cod_categoria, nome_categoria, vencimento)
professor_categoria (cod_professor, cod_categoria, data)
Devido à contenção orçamental na função publica, a proposta que fez para utilização de
uma base de dados Open Source baseada numa solução MySql
(http://www.mysql.com/) foi aceite.
I (7 Valores)
Indique os comandos da sub-linguagem DDL que utilizaria para implementar a referida
Base de Dados. Insira igualmente alguns dados exemplo.
II (8 Valores)
a) Justifique a seguinte afirmação: nesta base de dados cada cadeira pode ter no
máximo um departamento.
b) Diga como poderia alterar a sua base de dados para que cada cadeira possa pertencer
a vários departamentos. Comente a sua proposta de alteração.
c) Considerando a base de dados acima apresentada traduza para português a seguinte
expressão SQL do ORACLE (note que ... = ... (+) denota um right outer join):
SELECT a.num_aluno, nome_aluno, creditos
FROM aluno a,
(SELECT num_aluno, SUM(creditos) creditos
FROM inscricao i, curso_cadeira cc
WHERE i.cod_curso = cc.cod_curso
AND i.cod_cadeira = cc.cod_cadeira
AND data_avaliacao <= ‘1-jan-1989’
GROUP BY num_aluno) c
WHERE a.num_aluno = c.num_aluno (+)
ORDER BY CREDITOS;
d) Escreva em SQL a instrução para listar o nome, a categoria e vencimento, dos
professores que tenham vencimento inferior a um dos professores do Departamento de
Matemática.
III (5 Valores)
Sabendo que se inseriram na referida base de dados com os seguintes comandos:
REM curso(cod_curso, nome_curso)
insert into curso values(1,'Engenharia Informatica de Gestao');
insert into curso values(2,'Engenharia Electronica');
insert into curso values(3,'Engenharia do Ambiente');
insert into curso values(4,'Matematica');
REM aluno(num_aluno, nome_aluno, local_aluno, data_nsc_aluno, sexo_aluno, cod_curso)
insert into aluno values(1,'Joaquim Pires','Lisboa','65.01.01','M',2);
insert into aluno values(2,'Ana Maria Fonseca','Setubal','67.03.01','F',1);
insert into aluno values(3,'Paula Antunes','Lisboa','74.07.13','F',2);
insert into aluno values(4,'Joana Ramalho','Costa da Caparica','74.09.23','F',3);
insert into aluno values(5,'Rui Manuel','Cascais','74.08.15','M',1);
insert into aluno values(6,'Joao Paulo Santos','Lisboa','81.11.16','M',1);
insert into aluno values(7,'Cristina Lopes','Lisboa','66.01.07','F',1);
insert into aluno values(8,'Miguel Pinto Leite','Cascais','74.01.07','M',3);
insert into aluno values(9,'Francisco Rosa',NULL,'78.02.16','M',4);
insert into aluno values(10,'Elsa Fialho Pinto',NULL,'79.10.29','F',1);
REM categoria(cod_categoria, nome_categoria, vencimento)
insert into categoria values(1,'Assistente',165000);
insert into categoria values(2,
'Professor adjunto Equiparado',215000);
insert into categoria values(3,'Professor Adjunto',300000);
insert into categoria values(4,'Professor Coordenador',450000);
REM professor(cod_professor, nome_professor, cod_departamento) */
insert into professor values(1,'Joana Ramalho Silva',1,1);
insert into professor values(2,'Filipe Costa Rocha',2,1);
insert into professor values(3,'Joao Mario Cunha',1,3);
insert into professor values(4,'Carla Maria Vargas',3,2);
/* departamento(cod_departamento, nome_departamento) */
insert into departamento values( 1, 'Departamento de Sistemas de Informacao');
insert into departamento values( 2, 'Departamento de Electronica');
insert into departamento values( 3, 'Departamento de Matematica');
Indique qual o resultado das seguintes querys SQL:
a) SELECT DISTINCT local_aluno, sexo_aluno FROM aluno;
b) SELECT nome_categoria Categoria, vencimento*12 “Vencimento Annual”
FROM categoria;
c) SELECT nome_aluno Nomes FROM aluno UNION SELECT nome_professor
FROM professor;
d) SELECT nome_categoria, nome_professor FROM professor p, categoria c
WHERE p.cod_categoria = c.cod_categoria ORDER BY 1;
e) SELECT nome_professor, nome_categoria, vencimento FROM professor p,
categoria c WHERE p.cod_categoria = c.cod_categoria AND vencimento < (
SELECT MAX(vencimento) FROM professor p, categoria c, departamento d
WHERE p.cod_categoria = c.cod_categoria AND p.cod_departamento =
d.cod_departamento AND nome_departamento = ‘Departamento de
Matematica’;
Ministério da Educação
Nome: .................................................................................................................................................
................................................................................................
Número:
Unidade Lectiva de: Sistemas de Bases de Dados
Código
Elaborado em: 2002
Teste Formativo Nº
2611
3
Este teste formativo cobre a matéria referente aos módulos de estudo 1-6 do caderno
de apoio.
I (1v)
No ambiente cliente/servidor que usou e instalou para o seu estudo da cadeira indique
um programa cliente que tenha usado para consultar a base de dados. Seria possível
utilizar esse programa cliente num outro computador. Justifique detalhadamente.
II (6v)
Considere a seguinte troço da base de dados do clube de vídeo HáPoucos...:
filme(codfilme, titulofilme)
copia(codcopia, codfilme)
cliente(codcliente, nomecliente)
aluguer(codcliente, codcopia, dataaluguer)
devolucao(codcliente, codcopia, datadevolucao)
a) Justifique a seguinte afirmação: “Neste clube de video cada cliente só pode
alugar uma cópia uma vez”.
b) Diga como poderia alterar a base de dados de forma a permitir que um cliente
alugue a mesma copia mais vezes. Comente a sua proposta de alteração.
c) Considerando a base de dados acima apresentada traduza para português a
seguinte expressão SQL.
SELECT COUNT(*),
FROM filme, copia, cliente, aluguer
WHERE cliente.codcliente = aluguer.codcliente
AND aluguer.codcopia = copia.codcopia
AND copia.codfilme = filme.codfilme;
d) Escreva em SQL a instrução que mostra, para cada cliente, os nomes dos filmes
por devolver.
e) Escreva em SQL a instrução correspondente à expressão:
titulofilme((codcliente,codcopia(aluguer) codcliente(nomecliente=”Manelinho”(cliente)))  copia
 filme)
IV (4v)
Considere as seguintes relações (em que  é a junção natural e ~ é o complemento activo):
r(R)
A
X
Y
s(S)
B
1
2
t(T)
A
C
Y
X
X
X
X
2
B
1
1
3
C
X
Y
X
a) Calcule r s t .
b) Calcule (r  s)  C(t)
c) r s t decompõe-se sem perdas em relações r’ e r’’ sobre esquemas R’={A,B,C} e
R’’={B,C}.?
V (4v)
Considere o esquema relacional R(ABCDEFG) e o conjunto de dependências
DF={AB -> C, B -> DE, E -> F,A->BE}
a) Diga qual a forma normal em que se encontra a relação R acima..
b) Diga se DF |= {AG -> F}
c) Indique uma cobertura reduzida para o conjunto DF.
VI (5v)
Considere a Base de Dados apresentada no exercício III.
a) Desenhe um diagrama de entidades e associações (DEA) da Base de Dados apresentada.
b) Indique as instruções SQL para criar a Base de Dados indicada.
Em face de restrições de orçamento, pretende-se limitar o número de cópias por filme a um
máximo de 5 cópias.
c) Indique como alterar o DEA da alínea a).
d) Indique o que poderia fazer para garantir, que na Base de Dados a restrição referida é
satisfeita (pode assumir que estaria a utilizar uma gestor de bases de dados como o
ORACLE ou DB/2).
FIM