SQL Database Schema & Query Solutions

Forum Database Schema: Creation Scripts

Database Creation

CREATE DATABASE ForumDeDiscussoes;

USE ForumDeDiscussoes;

Usuario Table

CREATE TABLE Usuario (
  idUsuario INT AUTO_INCREMENT,
  nome VARCHAR(70),
  email VARCHAR(45),
  PRIMARY KEY (idUsuario)
);

Forum Table

CREATE TABLE Forum (
  idForum INT AUTO_INCREMENT,
  nome VARCHAR(45),
  PRIMARY KEY (idForum)
);

Topico Table

CREATE TABLE Topico (
  idTopico INT AUTO_INCREMENT,
  nome VARCHAR(45),
  idForum INT,
  PRIMARY KEY (idTopico),
  CONSTRAINT fkForum FOREIGN KEY (idForum) REFERENCES Forum (idForum)
);

Comentario Table

CREATE TABLE Comentario (
  idComentario INT AUTO_INCREMENT,
  Descricao VARCHAR(255),
  idTopico INT,
  PRIMARY KEY (idComentario),
  CONSTRAINT fkTopico FOREIGN KEY (idTopico) REFERENCES Topico (idTopico)
);

AvaliacaoTopico Table (Junction)

CREATE TABLE AvaliacaoTopico (
  idAvaliacaoTopico INT AUTO_INCREMENT,
  idUsuario INT,
  idTopico INT,
  PRIMARY KEY (idAvaliacaoTopico),
  CONSTRAINT fkUsuario FOREIGN KEY (idUsuario) REFERENCES Usuario (idUsuario),
  CONSTRAINT fkTopicoAvaliacao FOREIGN KEY (idTopico) REFERENCES Topico (idTopico)
);

TabAvaliacao Table (Evaluation Types)

CREATE TABLE TabAvaliacao (
  idTabAvaliacao INT AUTO_INCREMENT,
  descricao VARCHAR(60),
  PRIMARY KEY (idTabAvaliacao)
);

Exercise 2: SQL Query Answers

1. Query A

   SELECT *
   FROM ALUNO
   WHERE DATA_NASC >= '1984-07-16';

2. Query B

   SELECT C.ANO, C.SEMESTRE, C.NOME_DISCIPLINA
   FROM CURSA AS C
   INNER JOIN DISCIPLINA AS D ON C.CODIGO = D.CODIGO
   WHERE C.PROFESSOR = 'LUIS DIAS';

3. Query C

   Note: Assuming 'C.NOME_ALUNO >= D.CORTE_A' was a typo and meant 'C.NOTA >= D.CORTE_A'.

   SELECT D.NOME_DISCIPLINA
   FROM DISCIPLINA AS D
   INNER JOIN CURSA AS C ON D.CODIGO = C.CODIGO
   INNER JOIN ALUNO AS A ON C.RA = A.RA
   WHERE A.NOME_ALUNO = 'ALICE SOUSA SILVA'
     AND C.NOTA >= D.CORTE_A;

4. Query D

   SELECT A.RA
   FROM ALUNO AS A
   INNER JOIN CURSA AS C ON A.RA = C.RA
   INNER JOIN DISCIPLINA AS D ON C.CODIGO = D.CODIGO
   WHERE D.NOME_DISCIPLINA = 'BIOLOGIA COMPUTACIONAL'
     AND NOTA >= CORTE_B
     AND NOTA < CORTE_A;

5. Query E

   SELECT A.NOME_ALUNO -- Assuming the goal is the student's name, not the discipline name
   FROM DISCIPLINA AS D 
   INNER JOIN CURSA AS C ON D.CODIGO = C.CODIGO
   INNER JOIN ALUNO AS A ON C.RA = A.RA
   GROUP BY C.RA, A.NOME_ALUNO -- Include non-aggregated columns in GROUP BY
   HAVING COUNT(C.RA) = 10;

6. Query F

   SELECT D.NOME_DISCIPLINA
   FROM DISCIPLINA AS D
   LEFT OUTER JOIN CURSA AS C ON D.CODIGO = C.CODIGO;

7. Query G

   SELECT A.NOME_ALUNO
   FROM ALUNO AS A
   INNER JOIN CURSA AS C ON A.RA = C.RA
   GROUP BY A.RA, A.NOME_ALUNO -- Include non-aggregated columns in GROUP BY
   HAVING COUNT(C.RA) > 1;

Exercise 3: SQL Answers

1. Sample Data Insertion (Conceptual)

   The following represents data instances, not direct SQL insert statements:

   Estudante (10, "[email protected]", 1, 50);
FonePessoa (1, "019", "3761", "1370");

2. Table Creation Scripts

   Pessoa Table

   CREATE TABLE Pessoa (
     idPessoa INT NOT NULL AUTO_INCREMENT,
     Nome VARCHAR(40),
     Endereco VARCHAR(40),
     PRIMARY KEY (idPessoa)
   );

   FonePessoa Table

   CREATE TABLE FonePessoa (
     DDD VARCHAR(3),
     Prefixo CHAR(4),
     Nro CHAR(4),
     Pessoa_idPessoa INT,
     PRIMARY KEY (Pessoa_idPessoa, DDD, Prefixo, Nro), -- Composite key might be better
     CONSTRAINT fk_FonePessoa FOREIGN KEY (Pessoa_idPessoa) REFERENCES Pessoa (idPessoa)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION
   );

   Republica Table

   CREATE TABLE Republica (
     IdRep INT NOT NULL AUTO_INCREMENT,
     Nome VARCHAR(30),
     Endereco VARCHAR(40),
     PRIMARY KEY (IdRep)
   );

   Estudante Table

   CREATE TABLE Estudante (
     RA INT,
     Email VARCHAR(30),
     Pessoa_idPessoa INT,
     Republica_idRep INT, -- Added missing column for the foreign key
     PRIMARY KEY (RA),
     CONSTRAINT fkPessoa FOREIGN KEY (Pessoa_idPessoa) REFERENCES Pessoa (idPessoa)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
     CONSTRAINT fkRepublica FOREIGN KEY (Republica_idRep) REFERENCES Republica (IdRep)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION
   );

Exercise 4: SQL Query Answer

SELECT DISTINCT E.NomeEmp, E.cidade
FROM Empregados AS E
WHERE E.sexo = 'F';