tables_creation.sql

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ############################################################################################################################################################### --
-- ##################################################################### CRIAÇÃO DAS TABELAS ##################################################################### --
-- ############################################################################################################################################################### --
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- 


CREATE TABLE ALUNO
(
	COD_ALUNO SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(30) NOT NULL,
	CPF VARCHAR(11),
	DATA_NASCIMENTO DATE NOT NULL,
	EMAIL VARCHAR(30) NOT NULL,
	SENHA VARCHAR(30) NOT NULL,
	SALDO FLOAT DEFAULT 0
);

CREATE TABLE PROFESSOR
(
	COD_PROFESSOR SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(30) NOT NULL,
	CPF VARCHAR(11) NOT NULL,
	DATA_NASCIMENTO DATE NOT NULL,
	EMAIL VARCHAR(30) NOT NULL,
	SENHA VARCHAR(30) NOT NULL,
	SALDO FLOAT DEFAULT 0,
	DATA_ULTIMO_PAGAMENTO DATE DEFAULT NULL
);

CREATE TABLE CURSO
(
	COD_CURSO SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(60) NOT NULL,
	DESCRICAO VARCHAR(300),
	DURACAO INT DEFAULT 0,
	PRECO FLOAT,
	NUMERO_MODULOS INT DEFAULT 0,
	PUBLICADO BOOLEAN DEFAULT FALSE,
	DISPONIBILIDADE BOOLEAN DEFAULT FALSE,

	COD_PROFESSOR INT NOT NULL REFERENCES PROFESSOR(COD_PROFESSOR) ON DELETE CASCADE
);

CREATE TABLE ALUNO_CURSO
(
	COD_ALUNO_CURSO SERIAL NOT NULL PRIMARY KEY,
	DATA_COMPRA DATE,
	NOTA_AVALIACAO FLOAT,

	COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO) ON DELETE CASCADE,
	COD_CURSO INT NOT NULL REFERENCES CURSO(COD_CURSO) ON DELETE CASCADE
);

CREATE TABLE MODULO
(
	COD_MODULO SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(100),
	DESCRICAO VARCHAR(300),

	COD_CURSO INT NOT NULL REFERENCES CURSO(COD_CURSO) ON DELETE CASCADE
);

CREATE TABLE ALUNO_MODULO
(
	COD_ALUNO_MODULO SERIAL NOT NULL PRIMARY KEY,
	ACESSIVEL BOOLEAN,
	META_CONCLUIDA BOOLEAN,

	COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO) ON DELETE CASCADE,
	COD_MODULO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE
);

CREATE TABLE PRE_REQUISITO
(
	COD_PRE_REQUISITO SERIAL NOT NULL PRIMARY KEY,

	COD_MODULO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE,
	COD_MODULO_PRE_REQUISITO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE,

	UNIQUE (COD_PRE_REQUISITO, COD_MODULO) -- PAR DE VALORES ÚNICOS (1, 2), (2, 1), PORÉM (1, 2) NOVAMENTE NÃO PODE.
);

CREATE TABLE DISCIPLINA
(
	COD_DISCIPLINA SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(100),
	DESCRICAO VARCHAR(300),

	COD_MODULO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE
);

CREATE TABLE VIDEO_AULA
(
	COD_VIDEO_AULA SERIAL PRIMARY KEY,
	NOME VARCHAR(30) NOT NULL,
	DESCRICAO VARCHAR(300),
	DURACAO FLOAT,

	COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE ALUNO_VIDEO_ASSISTIDO
(
	COD_ALUNO_VIDEO_ASSISTIDO SERIAL NOT NULL PRIMARY KEY,

	COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO) ON DELETE CASCADE,
	COD_VIDEO_AULA INT NOT NULL REFERENCES VIDEO_AULA(COD_VIDEO_AULA) ON DELETE CASCADE
);

CREATE TABLE QUESTAO
(
	COD_QUESTAO SERIAL NOT NULL PRIMARY KEY,
	TEXTO VARCHAR(500),

	COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE QUESTIONARIO
(
	COD_QUESTIONARIO SERIAL NOT NULL PRIMARY KEY,
	NOME VARCHAR(30),

	COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE QUESTAO_QUESTIONARIO
(
	COD_QUESTAO_QUESTIONARIO SERIAL NOT NULL PRIMARY KEY,

	COD_QUESTAO INT NOT NULL REFERENCES QUESTAO(COD_QUESTAO) ON DELETE CASCADE,
	COD_QUESTIONARIO INT NOT NULL REFERENCES QUESTIONARIO(COD_QUESTIONARIO) ON DELETE CASCADE
);

CREATE TABLE QUESTAO_ALUNO
(
	COD_QUESTAO_ALUNO SERIAL NOT NULL PRIMARY KEY,
	RESPOSTA_ALUNO VARCHAR(500),
	RESPOSTA_CORRETA VARCHAR(13) DEFAULT 'NÃO ANALISADA',

	COD_QUESTAO INT NOT NULL REFERENCES QUESTAO(COD_QUESTAO),
	COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO)
);