{jogador, data}. Justificação: Assume-se que um jogador não pode ser transferido mais do que uma vez na mesma data.
Também aceito como certo {jogador, data, clube_origem, clube_destino}, se tiverem justificado que jogador e data não serve (parece que o jogador Paulo Madeira chegou a ser trasnferido 2 vezes no mesmo dia!).
Aquelas que saltam mais à vista são as seguintes:
- Restrições NOT NULL para uma série de atributos (nome do país, nome do jogador, etc).
- Nas transferências, o clube de origem tem de ser diferente do clube
de destino. Teria de se fazer um check na tabela Transferencias.
CHECK (clube_origem <> clube_destino ) - Restrição sobre datas de transferência. Por exemplo, não faz sentido
que um jogador tenha sido transferido antes de ter nascido.
CREATE ASSERTION data_transf CHECK ( NOT EXISTS ( SELECT * FROM Jogadores, Transferencias WHERE id = jogador AND data < nascimento ) );
Uma maneira possível de resolver o problema é (1) encontrar os jogadores brasileiros, (2) encontrar os jogadores que jogam em Portugal, e (3) fazer a intersecção de (1) com (2).
Em álgebra relacional:
Em SQL:
(
SELECT Jogadores.nome
FROM Jogadores, Paises
WHERE nacionalidade = sigla
AND Paises.nome = 'Brasil'
)
INTERSECT
(
SELECT Jogadores.nome
FROM Jogadores, Clubes, Paises
WHERE Jogadores.clube = Clubes.nome
AND Clubes.pais = Paises.sigla
AND Paises.nome = 'Portugal'
);
Também podiam fazer com subqueries. Assim:
(
SELECT nome
FROM Jogadores
WHERE nacionalidade = (SELECT sigla FROM Paises WHERE nome='Brasil')
)
INTERSECT
(
SELECT nome
FROM Jogadores
WHERE clube IN ( SELECT Clubes.nome
FROM Clubes, Paises
WHERE Clubes.pais = Paises.sigla
AND Paises.nome = 'Portugal'
)
);
NOTA: A resposta a esta pergunta (quer em álgebra relacional quer em SQL), fica bem mais simples se se assumir 'BR' e 'PT' como sendo as siglas de Brasil e Portugal, respectivamente. Desse modo, não é necessário fazer o join com Paises. Aceito ambas as respostas como certas. Já agora, aqui vai a versão simplificada.
Em álgebra relacional:
Em SQL:
(
SELECT nome
FROM Jogadores
WHERE nacionalidade = 'BR'
)
INTERSECT
(
SELECT Jogadores.nome
FROM Jogadores, Clubes
WHERE Jogadores.clube = Clubes.nome
AND Clubes.pais = 'PT'
);
a) Numero de jogadores estrangeiros por clube
SELECT J.clube AS "clube", COUNT(*) AS "numero de estrangeiros"
FROM Jogadores AS J, Clubes AS C
WHERE J.clube = C.nome AND J.nacionalidade <> C.pais
GROUP BY J.clube;
(NOTA: Nesta pergunta, muita gente assumiu que os jogadores
estrangeiros são aqueles que não são portugueses. Não aceito isso
como razoável visto que o exercício aparece no contexto de uma
base de dados para jogadores e clubes de todo o mundo. Por exemplo,
o Luís Figo é um jogador Português, mas no Real Madrid conta como
sendo estrangeiro.)
b) Quais as equipas em que o Luis Figo já jogou?
Dá jeito criar a seguinte view:
CREATE VIEW TransferenciasV AS
SELECT J.nome, clube_origem, clube_destino, data, valor
FROM Jogadores AS J, Transferencias AS T
WHERE J.id = T.jogador;
Depois é facil. Basta fazer a união do clube actual com
todos os clubes de (e para onde) o Luis Figo foi transferido.
(
SELECT clube_origem
FROM TransferenciasV
WHERE nome='Luis Figo'
)
UNION
(
SELECT clube_destino
FROM TransferenciasV
WHERE nome='Luis Figo'
)
UNION
(
SELECT clube
FROM Jogadores
WHERE nome='Luis Figo'
);
c) Quais os jogadores que foram transferidos mas que mais tarde
regressaram ao clube.
Esta pergunta é semelhante à questão que vimos nas aulas:
"Quais os actores que têm a mesma morada?"
A informação crucial está na tabela Transferencias, e tem de
se fazer um self-join da tabela, ou então usar uma "correlated
subquery".
Vou resolver de três maneiras distintas:
1) só com joins
SELECT J.nome
FROM Jogadores AS J, Transferencias AS T1, Transferencias AS T2
WHERE J.id = T1.jogador
AND T1.jogador = T2.jogador
AND T1.clube_origem = T2.clube_destino
AND T2.data > T1.data;
2) uma subquery e um self-join (muito parecido com 1)
SELECT nome
FROM Jogadores
WHERE id IN (
SELECT T1.jogador
FROM Transferencias AS T1, Transferencias AS T2
WHERE T1.jogador = T2.jogador
AND T1.clube_origem = T2.clube_destino
AND T2.data > T1.data
);
3) com uma sub-query e uma correlated sub-query
SELECT nome
FROM Jogadores
WHERE id IN (
SELECT jogador
FROM Transferencias AS T1
WHERE EXISTS (
SELECT clube_destino
FROM Transferencias AS T2
WHERE jogador = T1.jogador
AND clube_destino = T1.clube_origem
AND data > T1.data
)
);
A melhor maneira de atacar esta pergunta é responder às alineas por ordem inversa.
3) Sim. BC é chave candidata porque B e C determinam A (que é
o único atributo restante da relação R).
2) Sim, R está em BCNF porque BC (lado esquerdo da única DF)
é uma superchave de R (se é chave candidata, é forçosamente
uma superchave).
1) Sim, R está na 3ª FN porque BCNF => 3FN.