Ministério da Educação	
Nome: .................................................................................................................................................	
	
................................................................................................	Número:									

Unidade Lectiva de:	Sistemas de Bases de Dados

Elaborado em: 2002
	Código	2611	
Teste Formativo Nº	1		

INFORMAÇÕES

     1)	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.
     2)	A cotação de cada problema encontra-se imediatamente antes do seu enunciado.
     3)	O exame será feito sem CONSULTA.
     4)	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..
     5)	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.
     6)	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
     7)	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)	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(snome_realizador=Spielberg (Filmes a Realizadores))
d)	Diga o que calcula a expressão 
nome_realizador ((cod_realizador(ano(X a Filmes) a Filmes)) a Realizadores)
em que X = cod_realizador(snome_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

Elaborado em: 2002
	Código	2611	
Teste Formativo Nº	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

Elaborado em: 2002
	Código	2611	
Teste Formativo Nº	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) a codcliente(snomecliente=”Manelinho”(cliente))) a copia a filme)
IV (4v)
Considere as seguintes relações (em que a é a junção natural e ~ é o complemento activo):
A	C	
Y	X	
X	X	
       r(R)                       s(S)                                  t(T)
A	B	
X	1	
Y	2	
X	2	
B	C	
1	X	
1	Y	
3	X	

a)	Calcule r a s a t .
b)	Calcule (r a s)  PC(t)
c)	r a s a 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