Trabalho 2

Grupo: Romulo Mourão, Jhonatan Teixeira e Victor Sodré

Questão 1

Mudamos o campo SALARIO NUMBER (2) para SALARIO NUMERIC (10,2)
Alteramos o campo CPF para ser UNIQUE

CREATE TABLE funcionario (
    nome VARCHAR( 60 ) NOT NULL, 
    email VARCHAR( 60 ) NOT NULL,
    sexo VARCHAR( 10 ) NOT NULL,
    ddd INT( 2 ),
    salario NUMERIC (10,2),
    telefone VARCHAR ( 8 ),
    ativo VARCHAR(1), 
    endereco VARCHAR( 70 ) NOT NULL,
    cpf VARCHAR (11) NOT NULL, 
    cidade VARCHAR( 20 ) NOT NULL,
    estado VARCHAR( 2 ) NOT NULL,
    bairro VARCHAR( 20 ) NOT NULL,
    pais VARCHAR( 20 ) NOT NULL,
    login VARCHAR( 12 ) NOT NULL,
    senha VARCHAR( 12 ) NOT NULL,
    news VARCHAR( 8 ),
    id INT( 200 ) AUTO_INCREMENT,
    UNIQUE (id )
);

alter table funcionario add unique(cpf);

Stored Procedure DIMINUIR SALARIO

DELIMITER $$

create procedure DiminuirSalario(
	IN cpf varchar(11),
    IN porcentagem int
    )
    
BEGIN
	/* -- VARIÁVEIS --*/
	DECLARE salarioAjustado NUMERIC(10,2);
    SET @salario:=(SELECT salario FROM funcionario f WHERE f.cpf = cpf);
    SET @percentual := ((100 - porcentagem) / 100);
    SET salarioAjustado := @salario * @percentual;

	/* -- UPDATE --*/
	UPDATE funcionario f
    SET salario =  salarioAjustado
    WHERE f.cpf = cpf;
    
    /* -- RESULTADO -- */
    SELECT nome, 
        cpf, 
        concat('R$', @salario) as salario_antigo,
        concat(porcentagem, '%') as redução, 
        concat('R$', salarioAjustado) as salario_ajustado 
    FROM funcionario f 
    WHERE f.cpf = cpf;
    
END $$

call DiminuirSalario('12104956819',10);

TRIGGER FALTAS

DELIMITER $$
CREATE TRIGGER TGR_FALTAS_AI AFTER INSERT ON faltas FOR EACH ROW
BEGIN
	DECLARE faltas_sem_justificativa INT;
        BEGIN
			SELECT count(*) INTO faltas_sem_justificativa
			FROM faltas
			WHERE
			funcionario_id = NEW.funcionario_id and
			(justificativa is null OR justificativa = "");
    END;
		
    /* Se as faltas sem justificativas forem iguais a 5 chama a procedure para demitir o funcionario */
	IF faltas_sem_justificativa = 5 THEN
		CALL DemitirFuncionario(NEW.funcionario_id);  
	END IF;

END $$
DELIMITER ;

Stored Procedure usada na Trigger

DELIMITER $$
create procedure DemitirFuncionario(
	IN id INT
    )
    
BEGIN
	/* -- UPDATE --*/
    UPDATE funcionario f
    SET ativo =  "N"
    WHERE f.id = id;
    
END $$
DELIMITER ;

Promoção de Funcionario

Primeiro alteramos a tabela funcionario adicionando as seguintes colunas:

alter table funcionario
    add cargo VARCHAR(40) default "CARGO1",
    add nivel ENUM('1','2','3','4','5','6','7') default "1";
	add data_promocao DATE default '2014-01-01'

Stored Procedure PROMOCAO

DELIMITER $$

CREATE PROCEDURE SP_PROMOCAO(
	IN cpf varchar(11),
    IN nivel_promo INT
)

BEGIN
	DECLARE nivelAtual INT;
	DECLARE data_promo DATE;

	IF (nivel_promo > 0) AND (nivel_promo < 8) THEN

		SELECT nivel, data_promocao INTO nivelAtual, data_promo FROM funcionario f where f.cpf = cpf; 

		IF nivelAtual IS NULL THEN
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "Não há nenhum funcionário com este CPF.";

		ELSEIF nivelAtual = 7 THEN
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "Este funcionário já atingiu o nível máximo";
		
        ELSEIF nivelAtual <> (nivel_promo - 1) THEN
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "Não é possivel mudar para este nível.";

		ELSEIF (data_promo > curdate() - interval 3 year) THEN
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = "A última promoção foi há menos de 3 anos ";
		
		ELSE
			UPDATE funcionario f
			SET f.nivel = nivel_promo,
            f.data_promocao = curdate()
			WHERE f.cpf = cpf;
		END IF;
		
	ELSE
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "Este nível não é válido";

	END IF;

END $$

DELIMITER ;

Questão 2

Criação das tabelas

CREATE TABLE PESSOA (
 
	ID_PESSOA INT NOT NULL AUTO_INCREMENT,
	NOME VARCHAR(50),
	LOGIN VARCHAR(15),
	SENHA VARCHAR(15),
	CONSTRAINT PK_PESSOA PRIMARY KEY (ID_PESSOA)

);

CREATE TABLE RECEITA (
 
	ID_RECEITA INT NOT NULL AUTO_INCREMENT, 
	DATA_ENVIO DATE,
	TITULO VARCHAR(70),
	MODO_PREPARO TEXT,
	ID_PESSOA INT,
 
	CONSTRAINT PK_RECEITA PRIMARY KEY (ID_RECEITA),
	CONSTRAINT FK_RECEITA_PESSOA FOREIGN KEY (ID_PESSOA) 
	REFERENCES PESSOA (ID_PESSOA) ON UPDATE NO ACTION ON DELETE NO ACTION
 
);

 
CREATE TABLE INGREDIENTE (
 
	ID_INGREDIENTE INT NOT NULL AUTO_INCREMENT, 
	DESCRICAO TEXT,
	NOME VARCHAR(30),
	MEDIDA VARCHAR(40),
	 
	CONSTRAINT PK_INGREDIENTE PRIMARY KEY (ID_INGREDIENTE)
 
);
 
CREATE TABLE RECEITA_INGREDIENTE (
 
	ID_RECEITA INT NOT NULL,
	ID_INGREDIENTE INT NOT NULL,
	QUANTIDADE NUMERIC(4,1),

	CONSTRAINT PK_INGREDIENTE_RECEITA PRIMARY KEY (ID_RECEITA, ID_INGREDIENTE),

	CONSTRAINT FK_RI_INGREDIENTE FOREIGN KEY (ID_INGREDIENTE)
	REFERENCES INGREDIENTE (ID_INGREDIENTE) ON UPDATE NO ACTION ON DELETE NO ACTION,

	CONSTRAINT FK_RI_RECEITA FOREIGN KEY (ID_RECEITA)
	REFERENCES RECEITA (ID_RECEITA) ON UPDATE NO ACTION ON DELETE NO ACTION
 
);
 

CREATE TABLE VOTACAO (
 
	ID_VOTO INT NOT NULL AUTO_INCREMENT,
	NOTA NUMERIC(3,1),
	ID_PESSOA INT,
	ID_RECEITA INT,
	 
	CONSTRAINT PK_VOTO PRIMARY KEY (ID_VOTO),

	CONSTRAINT FK_VOTACAO_PESSOA FOREIGN KEY (ID_PESSOA)
	REFERENCES PESSOA (ID_PESSOA) ON UPDATE NO ACTION ON DELETE NO ACTION,
	 
	CONSTRAINT FK_VOTACAO_RECEITA FOREIGN KEY (ID_RECEITA)
	REFERENCES RECEITA (ID_RECEITA) ON UPDATE NO ACTION ON DELETE NO ACTION
 
);

2.1 Stored Procedure INSERE_RECEITA

/* Criamos uma tabela temporaria para colocar os ingredientes que serão adicionados na receita
Ex: */

CREATE TEMPORARY TABLE TmpIngrediente (nome varchar(20), quantidade numeric(4,1));
INSERT INTO TmpIngrediente (nome, quantidade) VALUES ("arroz", 2.5);
INSERT INTO TmpIngrediente (nome, quantidade) VALUES ("feijão", 3);

/* */

DELIMITER $$

create procedure insere_receita(
    IN pessoa_id int,
    IN titulo_receita varchar(70),
    IN data_postagem date,
    IN modo_preparo text
    )
    
BEGIN    
	/* Declaraçao das variáveis */
	DECLARE done int;
    DECLARE c_nome VARCHAR(30);
    DECLARE c_qtd NUMERIC(4,1);
    DECLARE ing_id int;
    DECLARE receita_id int;
    
    /* CURSOR para a tabela temporaria criada*/
	DECLARE cur_ingrediente CURSOR FOR
		SELECT nome, quantidade FROM TmpIngrediente;
	DECLARE CONTINUE handler for not found set done=1;
    
    /* Insere a Receita com os parametros passados*/
    insert into RECEITA(data_envio, titulo, modo_preparo, id_pessoa) 
    values (data_postagem, titulo_receita, modo_preparo, pessoa_id);
    
    set receita_id = last_insert_id();
    
    
    set done = 0;
	/* Inicia o cursor*/
    open cur_ingrediente;
    get_ingrediente: LOOP
		fetch cur_ingrediente into c_nome, c_qtd;
        if done = 1 then
			leave get_ingrediente;
		end if;
        /* Se o ingrediente existe na tabela INGREDIENTES 
		chama a procedure que associa o ingrediente a receita
		Caso contrário, mostra o erro dizendo que o ingrediente não está cadastrado*/
        IF EXISTS (SELECT 1 FROM INGREDIENTE
					WHERE nome like c_nome) THEN
			select id_ingrediente into ing_id from INGREDIENTE where nome like c_nome;
			call associa_ingrediente_receita(receita_id, ing_id, c_qtd);
		ELSE
			SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = "Este ingrediente não está cadastrado";
        END IF;
	END LOOP get_ingrediente;
    close cur_ingrediente;
        
    
END $$

DELIMITER ;

/* chamada da procedure*/
call insere_receita('1','Feijoada','2017-10-12','modo de preparo');

Procedure usada em INSERE_RECEITA

DELIMITER $$

create procedure associa_ingrediente_receita(
	IN receita_id int,
    IN ingrediente_id int,
    IN qtd numeric(4,1)

)

BEGIN

	INSERT INTO RECEITA_INGREDIENTE(id_receita, id_ingrediente, quantidade) 
	VALUES (receita_id,ingrediente_id, qtd);

END $$

DELIMITER ;

2.2 - Compatibilidade da Quantidade

/* Adicionamos a seguinte condição */
IF med = 'Unidade' THEN
	set c_qtd = round(c_qtd);
END IF;

Ficou desta forma:

create procedure insere_receita(
    IN pessoa_id int,
    IN titulo_receita varchar(70),
    IN data_postagem date,
    IN modo_preparo text
    )
    
BEGIN    
	/* Declaraçao das variáveis */
	DECLARE done int;
    DECLARE c_nome VARCHAR(30);
    DECLARE c_qtd NUMERIC(4,1);
    DECLARE ing_id int;
    DECLARE med varchar(40);
    DECLARE receita_id int;
    
    /* CURSOR */
	DECLARE cur_ingrediente CURSOR FOR
		SELECT nome, quantidade FROM TmpIngrediente;
	DECLARE CONTINUE handler for not found set done=1;
    
    /* Insere a Receita com os parametros passados*/
    insert into RECEITA(data_envio, titulo, modo_preparo, id_pessoa) 
    values (data_postagem, titulo_receita, modo_preparo, pessoa_id);
    
    set receita_id = last_insert_id();
    
    
    set done = 0;
    open cur_ingrediente;
    get_ingrediente: LOOP
		fetch cur_ingrediente into c_nome, c_qtd;
        if done = 1 then
			leave get_ingrediente;
		end if;
        
        IF EXISTS (SELECT 1 FROM INGREDIENTE
					WHERE nome like c_nome) THEN
			select id_ingrediente, medida into ing_id, med from INGREDIENTE where nome like c_nome;
			/* Arredonda quantidade caso a medida seja unidade */
            IF med = 'Unidade' THEN
            set c_qtd = round(c_qtd);
            END IF;
			call associa_ingrediente_receita(receita_id, ing_id, c_qtd);
		ELSE
			SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = "Este ingrediente não está cadastrado";
        END IF;
	END LOOP get_ingrediente;
    close cur_ingrediente;
        
    
END $$

2.3 - Função da maior média

/*A função retornará o ID da receita dentro dos critérios estipulados*/
  DELIMITER $$
  CREATE FUNCTION maior_media() returns int
  DETERMINISTIC
  BEGIN
  
	DECLARE resultado int;

	select v.id_receita
    into resultado
	from VOTACAO v, RECEITA r
     
    where v.id_receita IN (
		select id_receita 
        from RECEITA 
        #Seleciona as receitas com menos de um mês
        where data_envio > curdate() - interval 1 month
	)  
    and v.id_receita = r.id_receita
    group by v.id_receita 
    having count(*) >= 1000 
    #Ordenando pela media seguido da data mais recente
    order by avg(nota) desc, data_envio desc
    limit 1;

   return (resultado);
  END $$
  
  DELIMITER ;

Assim podemos chamar a função dentro de uma consulta:

select * from RECEITA WHERE id_receita = maior_media();

2.4 - Receita fracionada

Usamos uma procedure para passar o titulo da receita e o fator Dentro da procedure criamos uma tabela temporaria para adicionar os ingredientes da receita buscada Utilizamos um cursor para percorrer cada linha dessa tabela temporaria de Ingredientes e a cada iteração passamos as informações do ingrediente para uma função que criamos que verifica Se o ingrediente pode ou não ser fracionado, respeitando as regras estipuladas

Se todos os ingredientes podem ser fracionados, é retornada uma tabela com as respectivas quantidades fracionadas para cada ingrediente Caso contrário, a procedure exibe um erro.

Function que usamos dentro da procedure:

DELIMITER $$
  CREATE FUNCTION pode_fracionar(medida varchar(20), qtd numeric(4,1), fator numeric(2,1)) returns boolean
  DETERMINISTIC
  BEGIN
	
	DECLARE resto numeric(3,1);
    
    IF medida = 'Unidade' THEN 
		set resto = (qtd * fator) % 1;
		IF resto = 0 THEN
			return true;
        ELSE
			return false;
		END IF;
	ELSE
		return true;
    END IF;
    
  END $$
  
  DELIMITER ;

Procedure para fracionar a receita:

DELIMITER $$

create procedure sp_receita_fracionada(
    IN titulo_receita varchar(70),
    IN fator numeric(2,1)
    
    )
    
BEGIN    
	/* Declaraçao das variáveis */
	DECLARE done int;
    DECLARE id_receita_selecionada int;
	DECLARE c_nome varchar(40);
    DECLARE c_medida varchar(40);
    DECLARE c_titulo varchar(70);
    DECLARE c_modo_preparo text;
    DECLARE c_qtd numeric(4,1);
    DECLARE qtd_fracionada numeric(4,1);
    
	    /* CURSOR */
	DECLARE cur_ingr CURSOR FOR
		SELECT nome, quantidade, medida 
        FROM Tmp_Ing;
	DECLARE CONTINUE handler for not found set done=1;
    
    # Obter ID da receita
	SELECT id_receita, titulo, modo_preparo 
    INTO id_receita_selecionada, c_titulo, c_modo_preparo
    FROM RECEITA 
    WHERE titulo like titulo_receita limit 1;
    
	/*Se o id existir então cria uma tabela temporaria 
    com os ingredientes referentes a receita*/
    IF id_receita_selecionada IS NOT NULL THEN
		
		DROP TABLE IF EXISTS Tmp_Ing;
		CREATE TEMPORARY TABLE IF NOT EXISTS Tmp_Ing as (  
		select titulo, modo_preparo, nome, quantidade, medida 
		from RECEITA_INGREDIENTE ri, INGREDIENTE i, RECEITA r 
		WHERE ri.id_ingrediente = i.id_ingrediente and 
			ri.id_receita = r.id_receita and
			ri.id_receita = id_receita_selecionada
		);
    
		DROP TABLE IF EXISTS Tmp_receita_fracionada;
		CREATE TEMPORARY TABLE IF NOT EXISTS Tmp_receita_fracionada (
			titulo varchar(40), 
            modo_preparo text,
            ingrediente varchar(70),
            qtd_frac numeric(4,1)
        );
		set done = 0;
		open cur_ingr;
		get_ingrediente: LOOP
			fetch cur_ingr into c_nome, c_qtd, c_medida;
			if done = 1 then
				leave get_ingrediente;
			end if;
			
			IF pode_fracionar(c_medida, c_qtd, fator) THEN
				set qtd_fracionada = c_qtd * fator;
				INSERT INTO Tmp_receita_fracionada VALUES (c_titulo, c_modo_preparo, c_nome, qtd_fracionada);
			ELSE 
		
				SIGNAL SQLSTATE '45000' 
				SET MESSAGE_TEXT = "Este receita não pode ser fracionada com este fator";
				leave get_ingrediente;
			END IF;
		END LOOP get_ingrediente;
		close cur_ingr;
        select * from Tmp_receita_fracionada;
	ELSE
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = "Este receita não está cadastrada";
    END IF;
    
    
END $$

DELIMITER ;

romulomourao/trabalho2-bd-2017.1.md

Trabalho 2

Grupo: Romulo Mourão, Jhonatan Teixeira e Victor Sodré

Questão 1

Stored Procedure DIMINUIR SALARIO

TRIGGER FALTAS

Stored Procedure usada na Trigger

Promoção de Funcionario

Stored Procedure PROMOCAO

Questão 2

2.1 Stored Procedure INSERE_RECEITA

Procedure usada em INSERE_RECEITA

2.2 - Compatibilidade da Quantidade

2.3 - Função da maior média

Assim podemos chamar a função dentro de uma consulta:

2.4 - Receita fracionada

Function que usamos dentro da procedure:

Procedure para fracionar a receita: