Neste exercício vamos criar um banco de dados para armazenar os dados dos alunos de uma universidade.
Além de desenhar o diagrama, criar o banco de dados e seus objetos, você deverá criar os scripts de população básica.
Em seguida deverá criar as procedures que irão executar as operações de manipulação das notas e faltas.
A solução deverá possibilitar:
- Matricular o aluno automaticamente nas disciplinas de acordo com o curso escolhido e baseado nos dados cadastrados na tabela MATERIAS.
- Processar as notas dos alunos ao final do 4º Bimestre, verificando ser o aluno foi aprovado, reprovado ou ficou em exame.
- Caso o aluno fique em exame, deverá ser possível lançar a nota do exame e reprocessar o resultado desse aluno.
Abaixo os códigos que criam a estrutura básica para a resolução do exercício. Lembrando que é apenas uma sugestão de resolução e que devem ser adicionadas mais funcionalidades.
O código abaixo cria o banco de dados, tabelas, além de popular o banco de dados com os dados iniciais para o seu funcionamento.
CREATE DATABASE Universidade;
GO
USE Universidade;
GO
CREATE TABLE ALUNOS
(
MATRICULA INT NOT NULL IDENTITY
CONSTRAINT PK_ALUNO PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
);
GO
CREATE TABLE CURSOS
(
CURSO CHAR(3) NOT NULL
CONSTRAINT PK_CURSO PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
);
GO
CREATE TABLE PROFESSOR
(
PROFESSOR INT IDENTITY NOT NULL
CONSTRAINT PK_PROFESSOR PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
);
GO
CREATE TABLE MATERIAS
(
SIGLA CHAR(3) NOT NULL,
NOME VARCHAR(50) NOT NULL,
CARGAHORARIA INT NOT NULL,
CURSO CHAR(3) NOT NULL,
PROFESSOR INT
CONSTRAINT PK_MATERIA
PRIMARY KEY (
SIGLA
)
CONSTRAINT FK_CURSO
FOREIGN KEY (CURSO) REFERENCES CURSOS (CURSO),
CONSTRAINT FK_PROFESSOR
FOREIGN KEY (PROFESSOR)
REFERENCES PROFESSOR (PROFESSOR)
);
GO
INSERT ALUNOS
(
NOME
)
VALUES
('Pedro');
GO
INSERT CURSOS
(
CURSO,
NOME
)
VALUES
('ENG', 'ENGENHARIA');
GO
INSERT PROFESSOR
(
NOME
)
VALUES
('DORNEL'),
('WALTER');
GO
INSERT MATERIAS
(
SIGLA,
NOME,
CARGAHORARIA,
CURSO,
PROFESSOR
)
VALUES
('BDA', 'BANCO DE DADOS', 144, 'ENG', 1),
('PRG', 'PROGRAMAÇÃO', 144, 'ENG', 2);
GO
CREATE TABLE MATRICULA
(
MATRICULA INT,
CURSO CHAR(3),
MATERIA CHAR(3),
PROFESSOR INT,
PERLETIVO INT,
N1 FLOAT,
N2 FLOAT,
N3 FLOAT,
N4 FLOAT,
TOTALPONTOS FLOAT,
MEDIA FLOAT,
F1 INT,
F2 INT,
F3 INT,
F4 INT,
TOTALFALTAS INT,
PERCFREQ FLOAT,
RESULTADO VARCHAR(20)
CONSTRAINT PK_MATRICULA
PRIMARY KEY (
MATRICULA,
CURSO,
MATERIA,
PROFESSOR,
PERLETIVO
),
CONSTRAINT FK_ALUNOS_MATRICULA
FOREIGN KEY (MATRICULA)
REFERENCES ALUNOS (MATRICULA),
CONSTRAINT FK_CURSOS_MATRICULA
FOREIGN KEY (CURSO)
REFERENCES CURSOS (CURSO),
CONSTRAINT FK_MATERIAS FOREIGN KEY (MATERIA) REFERENCES MATERIAS (SIGLA),
CONSTRAINT FK_PROFESSOR_MATRICULA
FOREIGN KEY (PROFESSOR)
REFERENCES PROFESSOR (PROFESSOR)
);
GO
ALTER TABLE MATRICULA ADD MEDIAFINAL FLOAT;
GO
ALTER TABLE MATRICULA ADD NOTAEXAME FLOAT;
GO
O insert abaixo cadastra o aluno manualmente na tabela MATRICULA
INSERT MATRICULA (MATRICULA, CURSO, MATERIA,PROFESSOR,PERLETIVO)
VALUES (1,'ENG','BDA',1,2025)A PROCEDURE abaixo é criada para lanças as notas dos alunos. Ela lança notas, faltas e apura as médias parciais, faltas de totais de pontos dos alunos. Porém ainda falta completar com a estrutura de apuração de resultados e entrada de dados de exame.
CREATE PROCEDURE sp_CadastraNotas
(
@MATRICULA INT,
@CURSO CHAR(3),
@MATERIA CHAR(3),
@PERLETIVO CHAR(4),
@NOTA FLOAT,
@FALTA INT,
@BIMESTRE INT
)
AS
BEGIN
IF @BIMESTRE = 1
BEGIN
UPDATE MATRICULA
SET N1 = @NOTA,
F1 = @FALTA,
TOTALPONTOS = @NOTA,
TOTALFALTAS = @FALTA,
MEDIA = @NOTA
WHERE MATRICULA = @MATRICULA
AND CURSO = @CURSO
AND MATERIA = @MATERIA
AND PERLETIVO = @PERLETIVO;
END
ELSE
IF @BIMESTRE = 2
BEGIN
UPDATE MATRICULA
SET N2 = @NOTA,
F2 = @FALTA,
TOTALPONTOS = @NOTA + N1,
TOTALFALTAS = @FALTA + F1,
MEDIA = (@NOTA + N1) / 2
WHERE MATRICULA = @MATRICULA
AND CURSO = @CURSO
AND MATERIA = @MATERIA
AND PERLETIVO = @PERLETIVO;
END
ELSE
IF @BIMESTRE = 3
BEGIN
UPDATE MATRICULA
SET N3 = @NOTA,
F3 = @FALTA,
TOTALPONTOS = @NOTA + N1 + N2,
TOTALFALTAS = @FALTA + F1 + F2,
MEDIA = (@NOTA + N1 + N2) / 3
WHERE MATRICULA = @MATRICULA
AND CURSO = @CURSO
AND MATERIA = @MATERIA
AND PERLETIVO = @PERLETIVO;
END
ELSE
IF @BIMESTRE = 4
BEGIN
DECLARE @RESULTADO VARCHAR(50),
@FREQUENCIA FLOAT,
@MEDIAFINAL FLOAT,
@CARGAHORA INT
SET @CARGAHORA = (
SELECT CARGAHORARIA FROM MATERIAS
WHERE SIGLA = @MATERIA
AND CURSO = @CURSO)
UPDATE MATRICULA
SET N4 = @NOTA,
F4 = @FALTA,
TOTALPONTOS = @NOTA + N1 + N2 + N3,
TOTALFALTAS = @FALTA + F1 + F2 + F3,
MEDIA = (@NOTA + N1 + N2 + N3) / 4
WHERE MATRICULA = @MATRICULA
AND CURSO = @CURSO
AND MATERIA = @MATERIA
AND PERLETIVO = @PERLETIVO;
END
SELECT * FROM MATRICULA WHERE MATRICULA = @MATRICULA
END
Exemplo de execução da PROCEDURE para lançar as notas do aluno de matricula 1, do curso de sigla ENG, da disciplina de BDA no período letivo de 2025, com nota 7, duas faltas no primeiro bimestre.
EXEC sp_CadastraNotas @MATRICULA = 1, -- int
@CURSO = 'ENG', -- char(3)
@MATERIA = 'BDA', -- char(3)
@PERLETIVO = '2025', -- char(4)
@NOTA = 7.0, -- float
@FALTA = 2,
@BIMESTRE = 1 -- int
O fragmento abaixo é uma sugestão de Procedure que poderia ser usada para matricular o aluno, efetuando seu cadastro de ALUNO e também cadastrando as disciplinas que ele irá cursas na tabela MATRICULA com base nos dados da tabela DISCIPLINA.
CREATE PROCEDURE procMATRICULAALUNO
(
@NOME VARCHAR(50),
@CURSO CHAR(3)
)
AS
BEGIN
--Parte do código a completar
--Sugestão: Use INSERT com SELECT ou então TRIGGERS.
END
GO
--Exemplo de execução, Pedro deverá cursar as disciplinas correspondentes ao curso de sigla ENG.
EXEC procMATRICULAALUNO 'PEDRO','ENG'