--CREATE DATABASE Universidade;
--USE Universidade;

-- Tabela Professores
CREATE TABLE Professores (
    matricula INT,
    nome VARCHAR(100) NOT NULL,
    RG VARCHAR(20) NOT NULL,
    sexo CHAR(1),
    idade INT,
    titulacao VARCHAR(20),
    categoria VARCHAR(20),
    nroTurmas INT,
    CONSTRAINT pk_professores PRIMARY KEY (matricula),
    CONSTRAINT uq_professores_rg UNIQUE (RG),
    CONSTRAINT ck_professores_sexo CHECK (sexo IN ('M', 'F')),
    CONSTRAINT ck_professores_idade CHECK (idade BETWEEN 21 AND 80),
    CONSTRAINT ck_professores_titulacao CHECK (titulacao IN ('graduado', 'especialista', 'mestre', 'doutor')),
    CONSTRAINT ck_professores_categoria CHECK (categoria IN ('auxiliar', 'assistente', 'adjunto', 'titular')),
    CONSTRAINT ck_professores_nroturmas CHECK (nroTurmas >= 0)
);

-- Tabela Cursos
CREATE TABLE Cursos (
    codigo INT,
    nome VARCHAR(100) NOT NULL,
    duracao INT,
    coordenador INT,
    CONSTRAINT pk_cursos PRIMARY KEY (codigo),
    CONSTRAINT ck_cursos_duracao CHECK (duracao BETWEEN 4 AND 12),
    CONSTRAINT fk_cursos_professores FOREIGN KEY (coordenador) 
        REFERENCES Professores(matricula) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

-- Tabela Alunos
CREATE TABLE Alunos (
    matricula INT,
    nome VARCHAR(100) NOT NULL,
    RG VARCHAR(20) NOT NULL,
    sexo CHAR(1),
    idade INT,
    curso INT,
    CONSTRAINT pk_alunos PRIMARY KEY (matricula),
    CONSTRAINT uq_alunos_rg UNIQUE (RG),
    CONSTRAINT ck_alunos_sexo CHECK (sexo IN ('M', 'F')),
    CONSTRAINT ck_alunos_idade CHECK (idade BETWEEN 17 AND 90),
    CONSTRAINT fk_alunos_cursos FOREIGN KEY (curso) 
        REFERENCES Cursos(codigo)
);

-- Tabela Disciplinas
CREATE TABLE Disciplinas (
    codigo INT,
    nome VARCHAR(100) NOT NULL,
    creditos INT,
    CONSTRAINT pk_disciplinas PRIMARY KEY (codigo),
    CONSTRAINT ck_disciplinas_creditos CHECK (creditos BETWEEN 2 AND 8)
);

-- Tabela Currículos
CREATE TABLE Curriculos (
    curso INT,
    disciplina INT,
    fase INT,
    CONSTRAINT pk_curriculos PRIMARY KEY (curso, disciplina),
    CONSTRAINT fk_curriculos_cursos FOREIGN KEY (curso) 
        REFERENCES Cursos(codigo) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    CONSTRAINT fk_curriculos_disciplinas FOREIGN KEY (disciplina) 
        REFERENCES Disciplinas(codigo) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    CONSTRAINT ck_curriculos_fase CHECK (fase BETWEEN 1 AND 12)
);

-- Tabela Turmas
CREATE TABLE Turmas (
    disciplina INT,
    codigo INT,
    vagas INT,
    professor INT NOT NULL,
    CONSTRAINT pk_turmas PRIMARY KEY (disciplina, codigo),
    CONSTRAINT ck_turmas_vagas CHECK (vagas > 0),
    CONSTRAINT fk_turmas_disciplinas FOREIGN KEY (disciplina) 
        REFERENCES Disciplinas(codigo),
    CONSTRAINT fk_turmas_professores FOREIGN KEY (professor) 
        REFERENCES Professores(matricula)
);

-- Tabela Matrículas
CREATE TABLE Matriculas (
    aluno INT,
    disciplina INT,
    turma INT,
    CONSTRAINT pk_matriculas PRIMARY KEY (aluno, disciplina, turma),
    CONSTRAINT fk_matriculas_alunos FOREIGN KEY (aluno) 
        REFERENCES Alunos(matricula) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    CONSTRAINT fk_matriculas_turmas FOREIGN KEY (disciplina, turma) 
        REFERENCES Turmas(disciplina, codigo)
);