Comandos SQL para gerenciar notas de alunos em uma universidade

Prévia do material em texto

TRABALHO ACADÊMICO AV1
DISCIPLINA: DESENVOLVIMENTO DE APLICAÇÕES COM BANCO DE DADOS 2020/2
ATIVIDADE
Minimundo: Em uma universidade, as avaliações de uma disciplina são formadas por três notas: AV1, AV2 e AV3. A nota AV1 é composta por um trabalho acadêmico, que vale 7,0 pontos, e uma APS que vale 3,0 pontos. A AV2 é composta por um trabalho acadêmico, que vale 8,0 pontos, e uma APS que vale 2,0 pontos. A AV3 é composta apenas por um trabalho acadêmico que vale 10,0 pontos. O aluno é considerado aprovado se a média de duas avaliações quaisquer é maior ou igual a 7,0. Cada aluno pertencerá a um curso de graduação que poderá fazer de 1 (uma) a 10 (dez) disciplinas, sendo que cada disciplina será regida pelas regras das avaliações acima descritas.
De acordo com o minimundo descrito elabore os comandos SQL:
 
a) (2,0 pontos) Criação da base de dados;
b) (1,0 pontos) Criação do usuário DBA que poderá criar e manipular o esquema da base de dados;
c) (1,0 pontos) Criação do usuário DEV que poderá apenas manipular os dados; e
d) (3,0 pontos) Comandos SQL para constituir o CRUD de cada entidade presente no minimundo.
 
 
 RESPOSTAS
a)
#cria a base de dados
CREATE DATABASE faculdadex;
USE faculdadex;
 
#cria a tabela aluno
CREATE TABLE Aluno(
id INT AUTO_INCREMENT NOT NULL,
nome VARCHAR(30) NOT NULL,
curso VARCHAR(20) NOT NULL,
PRIMARY KEY(id));
 
#cria a tabela disciplina
CREATE TABLE Disciplina(
id INT AUTO_INCREMENT NOT NULL,
nome VARCHAR(30) NOT NULL,
carga INT NOT NULL,
professor VARCHAR(20) NOT NULL,
PRIMARY KEY(id));
 
#cria a tabela de disciplinas por aluno
CREATE TABLE aluno_disciplina(
aluno INT,
disciplina INT,
Av1_trab FLOAT,
Av1_aps FLOAT,
Av2_trab FLOAT,
Av2_aps FLOAT,
Av3_trab FLOAT,
Situação VARCHAR(30),
CONSTRAINT FK_idAluno FOREIGN KEY (aluno) REFERENCES Aluno (id),
CONSTRAINT FK_idDisciplina FOREIGN KEY (disciplina) REFERENCES Disciplina (id)
);
 
 
b)
#cria o usuário DBA
CREATE USER 'DBA'@'localhost' IDENTIFIED BY 'senha';
GRANT ALL PRIVILEGES ON faculdadex.* TO 'DBA'@'localhost';
 
c)
#cria o usuário DEV
CREATE USER 'DEV'@'localhost' IDENTIFIED BY 'senha';
GRANT SELECT,UPDATE,INSERT,DELETE ON faculdadex.* TO 'DEV'@'localhost';
 
d)
#COMANDOS CRUD
 
Create (Criação)
#inserção de alunos
INSERT INTO aluno (nome, curso) Values ('Douglas', 'ADS');
INSERT INTO aluno (nome, curso) Values ('Thiago', 'ADS');
 
#inserção de disciplinas
INSERT INTO Disciplina (nome, carga) Values ('Algoritmo', '60', 'Anderson');
INSERT INTO Disciplina (nome, carga) Values ('ADS', '60', 'Anderson');
 
#inserção de notas
INSERT INTO aluno_disciplina (aluno, disciplina) Values ('1', '2');
INSERT INTO aluno_disciplina (aluno, disciplina) Values ('2', '2');
 
 
Read (Consulta)
 
#a consulta retorna todos os alunos e as notas
select a.nome as Aluno, d.nome as Disciplina, ad.Av1trab as 'AV1 Trabalho', ad.Av1aps as 'AV1 APS', ad.Av2trab as 'AV2 Trabalho', ad.Av2aps as 'AV2 APS', ad.Av3trab as 'AV3 Trabalho'
from aluno a
INNER JOIN aluno_disciplina ad
ON a.id = ad.aluno
INNER JOIN disciplina d
ON d.id = ad.disciplina
 
 
Update (Atualização)
 
#atualização de notas
UPDATE aluno_disciplina SET Av1trab = 7 WHERE aluno = 1;
UPDATE aluno_disciplina SET Av1aps = 3 WHERE aluno = 1;
 
#atualização de situação
UPDATE aluno_disciplina SET situacao = 'APROVADO' WHERE aluno = 1;
 
 
Delete (Destruição)
 
#removendo um aluno da tabela de disciplinas
DELETE FROM aluno_disciplina WHERE aluno = 1;
Sensitivity: Internal