Base de dados "Formação"

-- Active: 1719327896324@@185.12.116.122@3306@cetmultimedia24_dabd

-- ------------------------------------------------------------------------------------

-- -------------------- Base de dados -------------------------------------------------

-- ------------------------------------------------------------------------------------

-- DROP DATABASE IF EXISTS bd_formacao;

-- CREATE DATABASE bd_formacao;

-- ------------------------------------------------------------------------------------

-- -------------------- CURSOS  -------------------------------------------------------

-- ------------------------------------------------------------------------------------

SHOW PROCEDURE STATUS WHERE Db = (select database());

SHOW TABLES;

DELIMITER //

DROP TABLE IF EXISTS tb_curso;

CREATE TABLE tb_curso(

    idCurso INT AUTO_INCREMENT,

    designacaoCurso VARCHAR(50) NOT NULL,

    PRIMARY KEY(idCurso),

    UNIQUE KEY (designacaoCurso)

);

DESCRIBE tb_curso;

DROP PROCEDURE IF EXISTS p_registar_curso; 

CREATE PROCEDURE p_registar_curso(IN a VARCHAR(50))

BEGIN

    DECLARE cursoExistente INT;

    DECLARE resultado TEXT;

    -- Verificar se o curso já existe

   SET cursoExistente = (SELECT COUNT(*) FROM tb_curso WHERE designacaoCurso = a);

    -- Se o curso não existir, insira-o

    IF cursoExistente = 0 THEN

        INSERT INTO tb_curso (designacaoCurso)

        VALUES (a);

        SET resultado = "Adicionou um novo curso.";

    ELSE

        -- Se o curso já existir, lançar um erro

        SET resultado = "Curso já constava da base de dados.";

    END IF;

    SELECT resultado AS resultado;

END //

-- ------------------------------------------------------------------------------------

-- -------------------- regimes -------------------------------------------------------

-- ------------------------------------------------------------------------------------

DROP TABLE IF EXISTS tb_regime;

CREATE TABLE tb_regime(

    idRegime INT AUTO_INCREMENT,

    designacaoRegime VARCHAR(50) NOT NULL,

    PRIMARY KEY(idRegime),

    UNIQUE KEY (designacaoRegime)

);

DESCRIBE tb_regime;

-- Criação da tabela de associação regimes

DROP PROCEDURE IF EXISTS p_registar_regime; 

CREATE PROCEDURE p_registar_regime(IN a VARCHAR(50))

BEGIN

    DECLARE regimeExistente INT;

    DECLARE resultado TEXT;

    -- Verificar se o regime já existe

    SELECT COUNT(*) INTO regimeExistente

    FROM tb_regime

    WHERE designacaoRegime = a;

    -- Se o regime não existir, insira-o

    IF regimeExistente = 0 THEN

        INSERT INTO tb_regime (designacaoRegime)

        VALUES (a);

         SET resultado = "Adicionou um novo regime.";

    ELSE

        -- Se o regime já existir, lançar um erro

         SET resultado = "Regime já constava da base de dados.";

    END IF;

    SELECT resultado AS resultado;

END //

-- ------------------------------------------------------------------------------------

-- -------------------- CURSOS REGIMES ------------------------------------------------

-- ------------------------------------------------------------------------------------

DROP TABLE IF EXISTS tb_curso_regime;

CREATE TABLE tb_curso_regime(

    idCursoRegime INT AUTO_INCREMENT,

    idCurso INT NOT NULL,

    idRegime INT NOT NULL,

    PRIMARY KEY(idCursoRegime),

    FOREIGN KEY (idCurso) REFERENCES tb_curso (idCurso),

    FOREIGN KEY (idRegime) REFERENCES tb_regime (idRegime),

    UNIQUE KEY indexCR (idCurso, idRegime) 

);

DESCRIBE tb_curso_regime;

DROP PROCEDURE IF EXISTS p_associar_curso_regime;

CREATE PROCEDURE p_associar_curso_regime(IN a VARCHAR(50), b VARCHAR(50))

BEGIN

    DECLARE totalCurso INT;

    DECLARE totalRegime INT;

    DECLARE totalCursoRegime INT;

    DECLARE resultado TEXT;

    SET totalCurso = (SELECT COUNT(*) FROM tb_curso WHERE idCurso = a );

    SET totalRegime = (SELECT COUNT(*) FROM tb_regime WHERE idRegime = b );

    SET totalCursoRegime = (SELECT COUNT(*) FROM tb_curso_regime WHERE idCurso = a AND idRegime = b );

    -- Verificar se a associação já existe

    IF (totalCurso = 1 AND totalRegime = 1 AND totalCursoRegime = 0) THEN

       INSERT INTO tb_curso_regime (idCurso, idRegime) VALUES (a , b);

       SET resultado = "Adicionou uma nova associação na base de dados.";

    ELSE

        -- Se a associação já existir, lançar um erro

        SET resultado = "A associação já constava da base de dados.";

    END IF;

    SELECT resultado AS resultado;

END //

-- ------------------------------------------------------------------------------------

-- -------------------- TURMAS --------------------------------------------------------

-- ------------------------------------------------------------------------------------

DROP TABLE IF EXISTS tb_turma;

CREATE TABLE tb_turma(

    idTurma INT AUTO_INCREMENT,

    turmaDesignacao VARCHAR(50) NOT NULL,

    idCursoRegime INT NOT NULL,

    PRIMARY KEY(idTurma),

    FOREIGN KEY(idCursoRegime) REFERENCES tb_curso_regime (idCursoRegime),

    UNIQUE KEY (turmaDesignacao)

);

DESCRIBE tb_turma;

DROP PROCEDURE IF EXISTS p_registar_turma;

CREATE PROCEDURE p_registar_turma(IN a TEXT, b INT)

BEGIN-- tratar a chave unica

DECLARE totalTurma INT;

DECLARE totalCursoRegime INT;

DECLARE resultado TEXT;

SET totalCursoRegime = (SELECT COUNT(idCursoRegime) FROM tb_curso_regime WHERE idCursoRegime = b);

SET totalTurma = (SELECT COUNT(idTurma) FROM tb_turma WHERE turmaDesignacao = a);

    IF (totalCursoRegime = 1 and totalTurma=0) THEN

        INSERT INTO tb_turma VALUES (NULL, a, b);

        SET resultado = "Turma criada com sucesso";

    ELSE

        IF (totalTurma=1) THEN

        SET resultado = "Turma já existente na base de dados";

        END IF;

        IF (totalCursoRegime=0) THEN

        SET resultado = "Não existe curso regime associados";

        END IF;

    END IF;

SELECT resultado AS resultado;

END //

-- ------------------------------------------------------------------------------------

-- -------------------- FORMANDOS -----------------------------------------------------

-- ------------------------------------------------------------------------------------

DROP TABLE IF EXISTS tb_formando;

CREATE TABLE tb_formando(

    idFormando INT AUTO_INCREMENT,

    nome VARCHAR(50) NOT NULL,

    apelido VARCHAR(50) NOT NULL,

    dataNascimento DATE,

    cc VARCHAR(50),

    PRIMARY KEY(idFormando),

    UNIQUE KEY (cc)

);

DESCRIBE tb_formando;

DROP PROCEDURE IF EXISTS p_registar_formando; 

CREATE PROCEDURE p_registar_formando(IN a VARCHAR(50), b VARCHAR(50), c DATE, d VARCHAR(50))

BEGIN

    DECLARE totalFormandos INT;

    DECLARE resultado TEXT;

    -- Verificar se o regime já existe

    SET totalFormandos  = (SELECT COUNT(*)  FROM tb_formando  WHERE cc = d);

    IF totalFormandos = 0 THEN

        INSERT INTO tb_formando (nome, apelido, dataNascimento, cc) VALUES (a,b,c,d);

        SET resultado = "Adicionou um novo formando.";

    ELSE

        -- Se o regime já existir, lançar um erro

         SET resultado = "formando já constava da base de dados.";

    END IF;

    SELECT resultado AS resultado;

END //

DROP TABLE IF EXISTS tb_formando_turma;

CREATE TABLE tb_formando_turma(

    idFormandoTurma INT AUTO_INCREMENT,

    idFormando INT NOT NULL,

    idTurma INT NOT NULL,

    dataMatricula DATE,

    PRIMARY KEY(idFormandoTurma),

    FOREIGN KEY (idFormando) REFERENCES tb_formando (idFormando),

    FOREIGN KEY (idTurma) REFERENCES tb_turma (idTurma),

    UNIQUE KEY indexFT (idFormando, idTurma)

);

DESCRIBE tb_formando_turma;

DROP PROCEDURE IF EXISTS p_associar_formando_turma;

CREATE PROCEDURE p_associar_formando_turma(IN a INT, b INT, c DATE)

BEGIN

    DECLARE totalFormando INT;

    DECLARE totalTurma INT;

    DECLARE totalFormandoTurma INT;

    DECLARE resultado TEXT;    

    SET totalFormando = (SELECT COUNT(idFormando) FROM tb_formando WHERE idFormando = a);

    SET totalTurma = (SELECT COUNT(idTurma) FROM tb_turma WHERE idTurma = b);

    SET totalFormandoTurma = (SELECT COUNT(*) FROM tb_formando_turma WHERE idFormando = a AND idTurma = b);

    -- Verificar se a associação já existe

    IF (totalFormando=1 AND totalTurma=1 AND totalFormandoTurma=0) THEN

        INSERT INTO tb_formando_turma (idFormando, idTurma, dataMatricula)

        VALUES (a, b, c);

        SET resultado = "Adicionou uma nova associação na base de dados.";

    ELSE

        -- Se a associação já existir, lançar um erro

        SET resultado = "A associação já constava da base de dados.";

    END IF;

    SELECT resultado AS resultado;

END //

DELIMITER ;

CALL p_registar_curso('CET de Multimédia');

CALL p_registar_curso('CET Redes');

CALL p_registar_curso('CEF de Multimédia');

SELECT * from tb_curso;

CALL p_registar_regime('Laboral');

CALL p_registar_regime('Pós Laboral');

SELECT * FROM tb_regime;

CALL p_associar_curso_regime(1,1);

CALL p_associar_curso_regime(1,2);

CALL p_associar_curso_regime(2,1);

SELECT * FROM tb_curso_regime;

CALL p_registar_formando("Ana","Lopes","2000-03-23","CC2423");

SELECT * FROM tb_formando;

CALL p_registar_turma("CET MU 24", 3);

SELECT * FROM tb_turma;

SELECT * FROM tb_curso_regime;

SELECT * FROM tb_turma;

SELECT * FROM tb_formando;

CALL p_associar_formando_turma(1,1,CURRENT_TIMESTAMP());

SELECT * FROM tb_formando_turma;

DROP VIEW IF EXISTS v1_curso_regime;

CREATE VIEW v1_curso_regime AS

SELECT * FROM tb_curso JOIN tb_curso_regime USING(idCurso) JOIN tb_regime USING(idRegime);

DROP VIEW IF EXISTS v1_formando_turma;

CREATE VIEW v1_formando_turma AS

SELECT * FROM tb_formando JOIN tb_formando_turma USING(idFormando) JOIN tb_turma USING(idTurma);

DROP VIEW IF EXISTS v2_global_1;

CREATE VIEW v2_global AS

SELECT * FROM v1_curso_regime JOIN v1_formando_turma USING(idCursoRegime);

SELECT * FROM v2_global;