Lista 07 - Gabarito

(1)

1

a

_Questão.

Considere

_{os seguintes esquemas de relação.}

ALUNOS

(

_{cod_aluno, nome, curso, turma)}

HISTÓRICOS

_{(cod_aluno, cod_disc, cod_prof, semestre, turma_disciplina, nota)}

PROFESSORES

_{(cod_prof, nome, departamento, titulação)}

DISCIPLINAS

(

_{cod_disc, nome, departamento, num_créditos, curso, tipo(*))}

(*)

tipo:

_{obrigatória, opcional ou eletiva}

PRÉ-REQUISITOS

(

_{cod_disc, pré-req)}

OFERTAS

(

_{cod_oferta, cod_disc, cod_prof, turma, horário)}

MATRÍCULAS

(

_{cod_oferta, cod_aluno)}

(2)

Especifique

_{os “triggers” necessários para a implementação das seguintes}

restrições

_{de Integridade:}

a) Um

_{aluno só poderá matricular-se em uma disciplina se já tiver sido}

aprovado

_{em todos os pré- requisitos dessa disciplina.}

--Solução

₀₁

CREATE OR REPLACE FUNCTION questao1() RETURNS TRIGGER AS

$body$ BEGIN

IF (EXISTS(

SELECT *

FROM prerequisitos PR, ofertas O

WHERE O.cod_oferta=NEW.cod_oferta AND

PR.cod_disc=O.cod_disc AND

PR.prereq NOT IN (

SELECT H.cod_disc

FROM historicos H

WHERE H.cod_aluno=NEW.cod_aluno AND

H.nota>=5) ))

THEN

RAISE NOTICE 'Não é possível se matricular nessa disciplina! Existem prerequisitos que não foram

cursados.'; RETURN null; ELSE RETURN new; END IF; END; $body$ LANGUAGE plpgsql;

(3)

--Solução

₀₂

$body$ DECLARE c REFCURSOR; reg prerequisitos%ROWTYPE; numdisc INTEGER=1; new_cod_discplina INTEGER; BEGIN

SELECT cod_disc INTO new_cod_discplina FROM ofertas WHERE cod_oferta=NEW.cod_oferta;

OPEN c FOR SELECT * FROM prerequisitos WHERE cod_disc=new_cod_discplina;

FETCH c INTO reg;

LOOP

EXIT WHEN NOT FOUND;

SELECT COUNT(*) INTO numdisc

FROM historicos

WHERE cod_aluno=NEW.cod_aluno

AND nota>=5

AND cod_disc=reg.prereq;

IF numdisc=0 THEN

EXIT WHEN TRUE;

END IF;

FETCH c INTO reg;

END LOOP;

CLOSE c;

IF numdisc=0 THEN

RAISE NOTICE 'Não é possível se matricular nessa disciplina! Existem prerequisitos que

não foram cursados.';

RETURN null; ELSE RETURN new; END IF; END; $body$ LANGUAGE plpgsql;

--Trigger

CREATE TRIGGER tr_inserir_mat

BEFORE INSERT ON matriculas

FOR EACH ROW

EXECUTE PROCEDURE questao1();

--Teste

-- FALTA CURSA A DISCIPLINA 4

INSERT INTO matriculas VALUES (1,1);

-- CURSOU TODOS OS PRE-REQUISITOS

(4)

b)

Um

aluno não pode matricular-se em duas disciplinas no mesmo horário.

--Solução

₀₁

$body$ BEGIN

IF (EXISTS(

SELECT *

FROM ofertas O1

WHERE O1.cod_oferta=NEW.cod_oferta AND

O1.horario IN (

SELECT O2.horario

FROM matriculas M, ofertas O2

WHERE M.cod_oferta=O2.cod_oferta AND

M.cod_aluno=NEW.cod_aluno)

)) THEN

RAISE NOTICE 'Não é possível se matricular nessa disciplina! Existe uma disciplina

matriculada no mesmo horário.';

RETURN null; ELSE RETURN new; END IF; END; $body$ LANGUAGE plpgsql;

--Solução

₀₂

$body$ DECLARE

new_horario TEXT;

numdisc INTEGER:=0;

BEGIN

SELECT horario INTO new_horario

FROM ofertas

WHERE cod_oferta=NEW.cod_oferta;

SELECT COUNT(*) INTO numdisc

FROM ofertas O, matriculas M

WHERE O.cod_oferta=M.cod_oferta AND M.cod_aluno=NEW.cod_aluno AND O.horario=new_horario; IF numdisc=0 THEN RETURN new; ELSE

RAISE NOTICE 'Não é possível se matricular nessa disciplina! Existe uma disciplina

matriculada no mesmo horário.';

RETURN null;

END IF;

END; $body$

(5)

--Trigger

CREATE TRIGGER tr_inserir_mat2

BEFORE INSERT ON matriculas

FOR EACH ROW

EXECUTE PROCEDURE questao2();

--Teste

(6)

c)

Um

aluno da computação só poderá cursar no máximo 30 créditos de

disciplinas

_opcionais.

--Solução

₀₁

CREATE OR REPLACE FUNCTION questao3_before() RETURNS TRIGGER AS

$body$ DECLARE cred_hist INTEGER:=0; cred_mat INTEGER:=0; cred_new INTEGER:=0; cursoaluno TEXT:=''; BEGIN

SELECT curso INTO cursoaluno

FROM alunos

WHERE cod_aluno=NEW.cod_aluno;

IF cursoaluno='computacao' THEN

-- CREDITOS DA DISCIPLINA DA MATRICULA

SELECT D.num_creditos INTO cred_new

FROM ofertas O, disciplinas D

WHERE O.cod_disc=D.cod_disc AND

O.cod_oferta=NEW.cod_oferta AND

D.tipo='opcional';

-- CREDITOS DAS DISCIPLINAS APROVADAS DO ALUNO

SELECT SUM(D.num_creditos) INTO cred_hist

FROM historicos H, disciplinas D

WHERE H.cod_disc=D.cod_disc AND

H.cod_aluno=NEW.cod_aluno AND

H.nota>=5 AND D.tipo='opcional';

-- CREDITOS DAS DISCIPLINAS MATRICULADAS

SELECT SUM(D.num_creditos) INTO cred_mat

FROM ofertas O, matriculas M, disciplinas D

WHERE O.cod_oferta=M.cod_oferta AND

O.cod_disc=D.cod_disc AND

M.cod_aluno=NEW.cod_aluno AND

D.tipo='opcional';

IF cred_new IS NULL THEN cred_new:=0;END IF;

IF cred_hist IS NULL THEN cred_hist:=0;END IF;

IF cred_mat IS NULL THEN cred_mat:=0; END IF;

IF (cred_hist+cred_mat+cred_new)>30 THEN

RAISE NOTICE 'Não é possível realizar a matricula! Aluno de computação não

pode cursar mais de 30 crédito de disciplinas opcinais.';

RETURN NULL; ELSE RETURN NEW; END IF; ELSE RETURN NEW; END IF; END; $body$ LANGUAGE plpgsql;

(7)

--Solução

₀₂

CREATE OR REPLACE FUNCTION questao3_after() RETURNS TRIGGER AS

$body$ DECLARE cred_hist INTEGER:=0; cred_mat INTEGER:=0; cursoaluno TEXT:=''; BEGIN

SELECT curso INTO cursoaluno

FROM alunos

WHERE cod_aluno=NEW.cod_aluno;

IF cursoaluno='computacao' THEN

-- CREDITOS DAS DISCIPLINAS APROVADAS DO ALUNO

SELECT SUM(D.num_creditos) INTO cred_hist

FROM historicos H, disciplinas D

WHERE H.cod_disc=D.cod_disc AND

H.cod_aluno=NEW.cod_aluno AND

H.nota>=5 AND D.tipo='opcional';

-- CREDITOS DAS DISCIPLINAS MATRICULADAS

SELECT SUM(D.num_creditos) INTO cred_mat

FROM ofertas O, matriculas M, disciplinas D

WHERE O.cod_oferta=M.cod_oferta AND

O.cod_disc=D.cod_disc AND

M.cod_aluno=NEW.cod_aluno AND

D.tipo='opcional';

IF cred_hist IS NULL THEN cred_hist:=0;END IF;

IF cred_mat IS NULL THEN cred_mat:=0; END IF;

IF (cred_hist+cred_mat)>30 THEN

RAISE NOTICE 'Não é possível realizar a matricula! Aluno de computação não

pode cursar mais de 30 crédito de disciplinas opcinais.';

RETURN NULL; ELSE RETURN NEW; END IF; ELSE RETURN NEW; END IF; END; $body$ LANGUAGE plpgsql;

(8)

--Trigger

_{Solução 01}

CREATE TRIGGER tr_inserir_mat3 BEFORE INSERT ON matriculas

FOR EACH ROW EXECUTE PROCEDURE questao3_before();

--Trigger

_{Solução 02}

CREATE TRIGGER tr_inserir_mat4 AFTER INSERT ON matriculas

FOR EACH ROW EXECUTE PROCEDURE questao3_after();

--Teste

(9)

2

a

Questão. (Triggers)

Suponha

os esquemas de relação:

EMPREGADO

( nome, sal, depto, nomeGerente)

DEPARTAMENTO

(#depto, orçamento, endereço)

Script:

CREATE_{TABLE EMPREGADOS( NOME VARCHAR(50) PRIMARY KEY, SAL FLOAT, DEPTO}

INTEGER,_{GERENTE VARCHAR(50) );}

CREATE_{TABLE DEPARTAMENTO( DEPTO INTEGER PRIMARY KEY, ORCAMENTO FLOAT );}

Crie os triggers necessários para a manutenção das seguintes restrições de Integridade:

(a) “A

soma do salário dos empregados de um departamento não pode ultrapassar o

orçamento

do departamento”

--INSERT

CREATE OR REPLACE FUNCTION INSERT_SAL() RETURNS TRIGGER AS

$BODY$ BEGIN

IF(NEW.SAL + (SELECT SUM(SAL)

FROM EMPREGADOS E

WHERE E.DEPTO=NEW.DEPTO)) > (SELECT ORCAMENTO

FROM DEPARTAMENTO D WHERE D.DEPTO = NEW.DEPTO)

THEN

RAISE NOTICE 'Não é possível incluir um novo empregado, pois o orçamento é

insuficiente'; RETURN NULL; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER INSERT_SAL

BEFORE INSERT ON EMPREGADOS

FOR EACH ROW

(10)

--UPDATE

CREATE_{OR REPLACE FUNCTION UPDATE_SAL() RETURNS TRIGGER AS}

$BODY$ BEGIN

IF(NEW.SAL_{+ (SELECT SUM(SAL)}

FROM_{EMPREGADOS E}

WHERE_{E.DEPTO=NEW.DEPTO}

and_{E.NOME!=NEW.NOME)) > (SELECT ORCAMENTO FROM DEPARTAMENTO D}

WHERE_{D.DEPTO = NEW.DEPTO)}

THEN

RAISE_{NOTICE 'Não é possível aumentar o salário do empregado, pois o orçamento é}

insuficiente'; RETURN_NULL; ELSE RETURN_NEW; END_IF; END; $BODY$ LANGUAGE_plpgsql;

CREATE_{TRIGGER UPDATE_SAL}

BEFORE_{UPDATE ON EMPREGADOS}

FOR_{EACH ROW}

EXECUTE_{PROCEDURE UPDATE_SAL();}

--UPDATE ORÇAMENTO

CREATE OR REPLACE FUNCTION UPDATE_ORC() RETURNS TRIGGER AS

$BODY$ BEGIN

IF NEW.ORCAMENTO < (SELECT SUM(SAL) FROM EMPREGADOS E WHERE

E.DEPTO=NEW.DEPTO) THEN

RAISE NOTICE 'Não é possível alterar o orçamento, pois o novo orçamento não é

suficiente para pagar o salário do departamento';

RETURN NULL; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER UPDATE_ORC

BEFORE UPDATE OF ORCAMENTO ON DEPARTAMENTO

FOR EACH ROW

(11)

(b)

“Empregados de um mesmo departamento tem o mesmo gerente”.

--INSERT

EMPREGADO

CREATE_{OR REPLACE FUNCTION INSERT_EMP() RETURNS TRIGGER AS}

$BODY$ BEGIN

IF_{(EXISTS (}

SELECT_*

FROM_{EMPREGADOS E}

WHERE_{E.DEPTO = NEW.DEPTO}

AND_{E.GERENTE != NEW.GERENTE))}

THEN

RAISE_{NOTICE 'Gerente inválido';}

RETURN_NULL; ELSE RETURN_NEW; END_IF; END; $BODY$ LANGUAGE_plpgsql;

CREATE_{TRIGGER INSERT_EMP BEFORE}

INSERT_{ON EMPREGADOS}

FOR_{EACH ROW}

EXECUTE_{PROCEDURE INSERT_EMP();}

--UPDATE

GERENTE(Se o gerente de um empregado mudar então todos os

empregados do mesmo departamento terão seu gerente alterado)

CREATE OR REPLACE FUNCTION UPDATE_GER() RETURNS TRIGGER AS

$BODY$ BEGIN

UPDATE EMPREGADOS E

SET E.GERENTE = NEW.GERENTE

WHERE E.DEPTO = NEW.DEPTO;

RETURN NEW;

END; $BODY$

LANGUAGE plpgsql;

CREATE TRIGGER UPDATE_GER

AFTER UPDATE OF GERENTE ON EMPREGADOS

FOR EACH ROW

EXECUTE PROCEDURE UPDATE_GER();

(12)

Lista 07 - Gabarito

1

​

​ ​​ ​Questão​.

Considere

​ ​os​ ​seguintes​ ​esquemas​ ​de​ ​relação.

ALUNOS

​

​​

(

​cod_aluno​,​ ​nome,​ ​curso,​ ​turma)

HISTÓRICOS

​

​ ​(​cod_aluno,​​ ​​cod_disc,​​ ​​cod_prof​,​ ​​semestre​,​ ​turma_disciplina,​ ​nota)

​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​​

​

PROFESSORES

​

_Questão.

_{os seguintes esquemas de relação.}

_{cod_aluno, nome, curso, turma)}

_{(cod_aluno, cod_disc, cod_prof, semestre, turma_disciplina, nota)}

_{(cod_prof, nome, departamento, titulação)}

_{cod_disc, nome, departamento, num_créditos, curso, tipo(*))}

_{obrigatória, opcional ou eletiva}

_{cod_disc, pré-req)}

_{cod_oferta, cod_disc, cod_prof, turma, horário)}

_{cod_oferta, cod_aluno)}

_{os “triggers” necessários para a implementação das seguintes}

_{de Integridade:}

_{aluno só poderá matricular-se em uma disciplina se já tiver sido}

_{em todos os pré- requisitos dessa disciplina.}

₀₁

₀₂

aluno não pode matricular-se em duas disciplinas no mesmo horário.

₀₁