Normal

Bases de Dados 1, Época Normal 2003/04
DI- FCT/UNL
2004.01.16
16 de Janeiro de 2004
Bases de Dados 1
Exame de época normal, 2003/04
Duração: 3 horas (com consulta)
Grupo 1
Nota prévia: O enunciado deste grupo é intencionalmente vago. Ao responder deverá, sempre que
ache necessário, dizer o que assumiu e que não estava detalhado no enunciado.
O Euro’2004 aproxima-se a passos largos. Para se preparar para o evento, um amigo seu
decidiu pedir-lhe que implementasse uma pequena base de dados para armazenar informação sobre o
evento. Essa base de dados deve permitir armazenar informação referente a:
• Todas as equipas participantes incluindo, de cada uma delas, o nome do país, do treinador
e informação do plantel com, pelo menos, os nomes dos jogadores e suas idades;
• Constituição dos grupos da primeira fase do campeonato;
• Todos os jogos da primeira fase incluindo, para cada um, informação das (duas) equipas
que o disputarão, data em que terá lugar, nome do estádio onde se realizará, localidade
onde se situa e lotação do estádio. A base de dados deve já estar preparada para se virem
depois a introduzir os resultados dos vários jogos da primeira fase.
1 a)
Esboce um diagrama de entidades e relações para a base de dados.
1 b)
Com base nesse diagrama, apresente em SQL os comandos de criação das tabelas que
constituem a referida base de dados. Os comandos SQL deverão ter em conta todo o tipo de
restrições de integridade relevantes, devendo garantir automaticamente, por exemplo, a
integridade de referência.
1 c)
Como deverá saber, cada grupo da primeira fase é constituído exactamente por 4 equipas,
sendo que cada uma das equipas só pertence a um grupo. É avisado deixar que a base de
dados deixe que um grupo possa ter menos que 4 equipas (senão, antes mesmo de introduzir
dados a base de dados já estaria inconsistente). Mas não convém nada que a base de dados
permita que um grupo tenha mais que 4 equipas.
A sua base de dados já garante que um grupo não possa ter mais do que 4 equipas? Se sim,
explique como o garante. Se não, explique sucintamente o que teria que fazer para que
passasse a garantir?
1 d)
Como também deverá saber, na primeira fase do campeonato só pode haver jogos entre
equipas dum mesmo grupo.
A sua base de dados já garante que um jogo não pode envolver equipas de grupos
diferentes? Se sim, explique como o garante. Se não, explique sucintamente o que teria que
fazer para que passasse a garantir?
1 e)
Para transferir informação relativa aos plantéis e treinadores das várias equipas dava jeito
usar documentos XML. Proponha um DTD para ficheiros XML contendo informação sobre
várias equipas, respectivos treinadores e jogadores, com informação dos nomes e idades
destes últimos.
1 f)
Escreva uma expressão XPath que identifique o nome dos jogadores com menos de 20 anos
contidos num ficheiro XML que esteja de acordo com a DTD que propôs.
Página 1 de 2
DI-FCT/UNL
Bases de Dados 1, Época Normal 2003/04
2004.01.16
Grupo 2
Com o tempo foram sendo acrescentadas funcionalidades à base de dados e algumas delas
exigiram que se tivesse que guardar, para cada jogador que participasse em cada jogo, o minuto em
que esse jogador entrou (que seria 0 no caso do jogador entrar no início do jogo) e o minuto em que
saiu.
Para guardar essa informação foi definido uma relação com o seguinte esquema:
R = {Jogo,Jogador,Equipa,Treinador,Entrada,Saida}
2 a)
Sobre este esquema há uma série de condições que importa impôr:
“Cada equipa tem no máximo um treinador. Um jogador só pertence a uma equipa. Um
jogador que tenha sido substituído não pode voltar a entrar no mesmo jogo (ou seja, em
cada jogo um jogador no máximo só pode entrar uma vez, e no máximo só pode sair uma
vez)”.
Apresente um conjunto de dependências funcionais que imponha estas restrições.
2 b)
Decomponha o esquema R, sem perdas, por forma a obter um conjunto de esquemas que,
face às dependências funcionais da alínea anterior, esteja na forma normal de Boyce-Codd.
Grupo 3
Considere o seguinte esquema duma base de dados relacional para armazenar informação
sobre jogos dum campeonato de futebol (onde os atributos chave se encontram sublinhados):
jogos({NumJogo,Data,Equipa1,Equipa2})
golos({NumJogo,CodJog,Minuto,Auto})
jogadores({CodJog,Nome,NomeEq})
jogaram({CodJog,NumJogo,Desde,Até})
Assim, cada jogo tem um número único, uma data e os nomes das duas equipas que se
defrontaram; cada jogador têm um código único, um nome e a indicação da equipa em que joga; cada
golo é marcado num jogo, por um jogador num dado minuto do jogo. Há ainda a indicação de se tratar
ou não dum auto-golo, armazenada no atributo Booleano Auto. A relação jogaram armazena a
informação dos jogadores que jogaram em que jogos, desde que minuto e até que minuto.
Apresente o código SQL correspondente a cada uma das expressões das alíneas de a) a d) (pode,
sempre que o entender, criar “views” com perguntas intermédias; pode também usar abreviaturas desde
que as explicite):
3 a)
3 b)
3 c)
3 d)
Quais os jogadores que alinharam de início (i.e. desde o minuto 0) no jogo Portugal-Grécia?
Quantos minutos jogou cada um dos jogadores de Portugal ao longo de todo o campeonato?
Quantos golos foram marcados por cada uma das equipas?
Qual o resultado do Portugal-Grécia?
3 e)
Apresente uma expressão em álgebra relacional que corresponda à pergunta: “Quais os
jogadores de Portugal que participaram em todos os jogos (de Portugal, claro!)?”
3 f)
Apresente uma expressão em Datalog que corresponda à pergunta: “Quais os nomes dos
jogadores que não marcaram nenhum golo?”
Grupo 4
Com o passar do tempo constatou-se que as perguntas mais frequentes à base de dados do
grupo anterior incidiam sobre resultados dos jogos. Para tornar mais eficiente a resposta a esse tipo de
perguntas foi decidido adicionar à relação jogos os atributos (redundantes) Golos1 e Golos2, para em
cada jogo armazenar quantos golos foram marcados pela Equipa1 e pela Equipa2. Feito isto, para
garantir a consistência dos dados nesses atributos houve que fazer duas coisas:
4 a)
4 b)
Apresente o código SQL para, imediatamente após a alteração do esquema, preencher
correctamente os atributos Golos1 e Golos2 de cada um dos tuplos já existentes em jogos.
Assumindo que, após a alteração, a informação sobre os golos marcados em cada jogo
continua a ser feita apenas através da relação golos, apresente o código dos “triggers” SQL
necessários para manter sempre consistente a informação dos atributos Golos1 e Golos2.
Página 2 de 2
DI-FCT/UNL