Base de Dados, 2003/2004
Resolução do teste de 10/Dez/2003

Texto
O texto tem alguns erros de ortografia e diversas frases mal construídas e/ou que poderiam ser escritas de forma mais sucinta e objectiva.

Erros ortográficos:
- nessessário --> necessário
- conciste --> consiste
- diagram --> diagrama
Frases mal construídas:
- O tema da minha base de dados é fazer uma base de dados ...
- Então é assim: ...
- Pois nesse caso ...
- Os trabalhos são feitos em grupo mas também podem ser feitos individualmente.
- Para cada aluno, deve ser guardado o nome do aluno, ...
Como exemplo, o primeiro parágrafo seria mais claro e objectivo se fosse escrito do seguinte modo:
- O tema do meu trabalho é fazer uma base de dados para ajudar os professores da disciplina a gerirem os trabalhos dos alunos efectuados ao longo dos anos. Os trabalhos são feitos em grupo com um mínimo de 1 e um máximo de 3 alunos. Para cada aluno, deve ser guardado o seu número, nome e número do bilhete de identidade.
DEA, conversão para modelo relacional, e normalização

a) DEA

Erros muito graves feitos pelo Tó Tabelas:
1. Para o conjunto entidade 'Alunos', apenas 'nº' ou 'bi' deveria estar sublinhado. Ao sublinhar ambos estamos a dizer que {nº,bi} é uma chave composta, o que é incorrecto.
2. Eliminar o atributo 'nº de alunos' em 'Grupos'. O atributo não faz sentido porque está implícito na associação 'pertence' (um grupo tem vários alunos).
3. Os valores percentuais dos trabalhos ('perct1', 'perct2', 'perct3', 'perct4'), não deveriam pertencer ao conjunto entidade 'Trabalhos' visto que esses valores serão replicados para todos os trabalhos de um determinado ano, o que dá origem a redundância.
Erros menos graves feitos pelo Tó Tabelas:
1. Conjunto entidade 'Temas' não é necessário. Em geral, quando um conjunto entidade só tem um atributo é sinal de que esse conjunto entidade é desnecessário, podendo ser representado por um simples atributo.
2. Deveria mencionar-se explicitamente a restrição de que um grupo só pode ter entre 1 e 3 alunos inclusive.
O diagrama corrigido ficaria assim,

Existem outras soluções que também consideraria correctas.

b) Conversão para o modelo relacional

Tendo em atenção o modelo EA feito pelo Tó Tabelas, a conversão para o modelo relacional apenas tem como incorrecto a especificação da chave primária nas associações muitos-um. Para essas associações a chave primária é constituída pelos atributos chave do lado de "muitos". Isto é, respeitando a notação do Tó Tabelas, 'TEMA' e 'COD_TRABALHO' devem estar em minúsculas.
```
  Escolhe( NUM_GRUPO, ANO_GRUPO, tema )

  Realiza( NUM_GRUPO, ANO_GRUPO, cod_trabalho )
```
Embora não esteja incorrecto, poderíamos ainda eliminar as associações muitos-um. Ficaríamos com apenas 4 relações:
```
  Alunos( NUMERO, BI, nome )

  Grupos( NUMERO, ANO, num_alunos, tema, cod_trabalho )

  Trabalhos( CODIGO, ano, nota1, nota2, nota3, nota4,
             perct1, perct2, perct3, perct4 )

  Pertence( NUM_ALUNO, BI_ALUNO, NUM_GRUPO, ANO_GRUPO )
```
c) Normalização

Existe redundância visto que as percentagens aparecem replicadas para um determinado ano. Formalmente, existe uma DF que viola a condição de BCNF.
```
  ano --> perct1, perct2, perct3, perct4
```
O atributo 'ano' não é superchave da relação 'Trabalhos'. Logo, 'Trabalhos não está em BCNF.

SQL

a) Esquema da base de dados

Tendo em atenção o modelo relacional feito pelo Tó Tabelas, a definição do esquema da base de dados em SQL tem os seguintes erros:

Chave primária mal especificada nas tabelas 'Alunos' e 'Grupos'.
Tabelas 'Pertence', 'Escolhe' e 'Realiza' não têm chave primária especificada.
Chaves estrangeiras mal especificadas.

A definição correcta (tendo em atenção o modelo relacional incorrecto do Tó Tabelas) seria a seguinte:

  CREATE TABLE Alunos (
     numero     INTEGER,
     bi         INTEGER,
     nome       VARCHAR(30),
     PRIMARY KEY( numero, bi )
  );

  CREATE TABLE Grupos (
     numero     INTEGER,
     ano        INTEGER,
     num_alunos INTEGER,
     PRIMARY KEY( numero, ano )
  );

  CREATE TABLE Temas (
     tema       VARCHAR(50) PRIMARY KEY
  );

  CREATE TABLE Trabalhos( 
     codigo     INTEGER PRIMARY KEY,
     ano        INTEGER, 
     nota1      INTEGER,
     nota2      INTEGER,
     nota3      INTEGER,
     nota4      INTEGER,
     perct1     INTEGER,
     perct2     INTEGER,
     perct3     INTEGER,
     perct4     INTEGER
  );

  CREATE TABLE Pertence( 
     num_aluno  INTEGER,
     bi_aluno   INTEGER,
     num_grupo  INTEGER,
     ano_grupo  INTEGER,
     PRIMARY KEY (num_aluno,bi_aluno,num_grupo,ano_grupo),
     FOREIGN KEY (num_aluno,bi_aluno) REFERENCES Alunos(numero,bi),
     FOREIGN KEY (num_grupo,ano_grupo) REFERENCES Grupos(numero,ano)
  );

  CREATE TABLE Escolhe( 
     num_grupo  INTEGER,
     ano_grupo  INTEGER,
     tema       VARCHAR(50) REFERENCES Temas( tema ),
     PRIMARY KEY (num_grupo,ano_grupo,tema),
     FOREIGN KEY (num_grupo,ano_grupo) REFERENCES Grupos(numero,ano)
  );

  CREATE TABLE Realiza( 
     num_grupo  INTEGER,
     ano_grupo  INTEGER,
     cod_trabalho INTEGER REFERENCES Trabalhos( codigo ),
     PRIMARY KEY (num_grupo,ano_grupo,cod_trabalho),
     FOREIGN KEY (num_grupo,ano_grupo) REFERENCES Grupos(numero,ano)
  );

b) Queries em SQL

Ou colocam LIKE '%DVD%', ou = 'DVD'. Solução:

  SELECT count(*)
  FROM Escolhe
  WHERE tema LIKE '%DVD%';   // ou ... WHERE tema = 'DVD'

'=' deveria ser 'IN'. count(*) não faz sentido num WHERE. Solução:

  SELECT nome
  FROM Alunos
  WHERE (numero,bi) IN (
     SELECT num_aluno, bi_aluno
     FROM Pertence
     GROUP BY num_aluno, bi_aluno
     HAVING count(*) > 1
  );

semelhante à alínea anterior. Solução:

  SELECT nome
  FROM Alunos
  WHERE (numero,bi) IN (
     SELECT num_aluno, bi_aluno
     FROM Pertence
     GROUP BY num_aluno, bi_aluno
     HAVING count(*) >= ALL (
            SELECT count(*)
            FROM Pertence
            GROUP BY num_aluno, bi_aluno
         )
  );

Alunos.grupo=Grupos.numero não faz qualquer sentido. A condição do join das tabelas não está especificada. A solução correcta é:

  SELECT nome, nota1, nota2, nota3, nota4
  FROM Alunos AS A, Pertence AS P, 
       Realiza AS R, Trabalhos AS T
  WHERE A.numero = P.num_aluno
    AND A.bi = P.bi_aluno
    AND P.num_grupo = R.num_grupo
    AND P.ano_grupo = R.ano_grupo
    AND R.cod_trabalho = T.codigo
    AND P.ano_grupo = 2003;

A condição de join não está completa. Solução:

  SELECT A.nome
  FROM Alunos AS A, Pertence AS P, 
       Realiza AS R, Trabalhos AS T
  WHERE A.numero = P.num_aluno 
    AND A.bi = P.bi_aluno
    AND P.num_grupo = R.num_grupo
    AND P.ano_grupo = R.ano_grupo
    AND R.cod_trabalho = T.codigo
    AND T.nota1 > 17 AND T.nota2 > 17 
    AND T.nota3 > 17 AND T.nota4 > 17

Considero a solução anterior correcta, mas para sermos mais precisos deveríamos considerar que pode haver alunos repetentes que tenham tido mais de 17 em todos os trabalhos de 2003, mas não em 2002. Para sermos 100% correctos, esses alunos não tiveram mais do que 17 em todos os trabalhos que realizaram. A solução correcta tendo em conta este detalhe é:

  (
    SELECT A.nome
    FROM Alunos AS A, Pertence AS P, 
         Realiza AS R, Trabalhos AS T
    WHERE A.numero = P.num_aluno 
      AND A.bi = P.bi_aluno
      AND P.num_grupo = R.num_grupo
      AND P.ano_grupo = R.ano_grupo
      AND R.cod_trabalho = T.codigo
      AND T.nota1 > 17 AND T.nota2 > 17 
      AND T.nota3 > 17 AND T.nota4 > 17
  )
  EXCEPT
  (
    SELECT A.nome
    FROM Alunos AS A, Pertence AS P, 
         Realiza AS R, Trabalhos AS T
    WHERE A.numero = P.num_aluno 
      AND A.bi = P.bi_aluno
      AND P.num_grupo = R.num_grupo
      AND P.ano_grupo = R.ano_grupo
      AND R.cod_trabalho = T.codigo
      AND (T.nota1 <= 17 OR T.nota2 <= 17 OR 
          T.nota3 <= 17 OR T.nota4 <= 17 )
  );

Considerações finais

Esta correcção tem em conta os erros feitos pelo Tó Tabelas. Assim, a correcção do modelo relacional é feita respeitando o modelo EA feito incorrectamente por ele. O mesmo se passa relativamente à definição do esquema da BD e às queries em SQL. Isto é, as queries são corrigidas respeitando o esquema de BD do Tó Tabelas.

Estas queries ficariam bem mais simples se fossem aplicadas a um modelo relacional resultante de um diagrama EA feito correctamente. Deixo isso como exercício.

Base de Dados, 2003/2004 Resolução do teste de 10/Dez/2003

Considerações finais

Base de Dados, 2003/2004
Resolução do teste de 10/Dez/2003