populate.sql entr-3

CREATE DATABASE IF NOT EXISTS Entregable3;
USE Entregable3;


-- Grados

CREATE OR REPLACE TABLE degrees (
degreeId        INT                     NOT NULL AUTO_INCREMENT,
`name`          VARCHAR (60)        NOT NULL UNIQUE,
years           INT DEFAULT (4) NOT NULL,
PRIMARY KEY (degreeId),
CONSTRAINT invalidDegreeYear CHECK (years BETWEEN 3 AND 5)
);


--DEPARTAMENTOS


CREATE OR REPLACE TABLE Departments (
departmentId        INT                 NOT NULL AUTO_INCREMENT,
`name`              VARCHAR(100)    NOT NULL,
PRIMARY KEY (departmentId),
UNIQUE (`name`)
);


--ESPACIOS

CREATE OR REPLACE TABLE  Spaces (
spaceId     INT                 NOT NULL AUTO_INCREMENT,
`name`      VARCHAR (100)   NOT NULL UNIQUE,
`floor`     INT                 NOT NULL,
capacity        INT                 NOT NULL,
PRIMARY KEY(spaceId),
CONSTRAINT invalidcapacity CHECK (capacity>0)
);

#DESPACHOS

CREATE OR REPLACE TABLE Offices(
officeId        INT                 NOT NULL AUTO_INCREMENT,
`shared`        BOOLEAN             NOT NULL ,
`freespace` BOOLEAN             NOT NULL,
spaceId     INT,
PRIMARY KEY (officeId),
FOREIGN KEY (spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE
);

--AULAS

CREATE OR REPLACE TABLE classroom (
classroomId         INT                 NOT NULL AUTO_INCREMENT,
loudspeakers        BOOLEAN         NOT NULL,
projector           BOOLEAN         NOT NULL,
spaceId             INT,
PRIMARY KEY(classroomId),
FOREIGN KEY(spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE
);



--PROFESORES


CREATE OR REPLACE TABLE Teachers (
teacherId       INT                 NOT NULL AUTO_INCREMENT,
dni             CHAR(9)             NOT NULL UNIQUE ,
firstName       VARCHAR(100)    NOT NULL,
surname         VARCHAR(100)    NOT NULL,
birthDate       DATE                NOT NULL,
email               VARCHAR(258)    NOT NULL UNIQUE ,
category            ENUM('Profesor', 'Titular de Universidad', 'Profesor Contratado Doctor', 'Profesor Ayudante Doctor'),
officeId            INT,
departmentId    INT,
PRIMARY KEY(teacherId),
FOREIGN KEY (officeId) REFERENCES offices(officeId) ON DELETE SET NULL,
FOREIGN KEY (departmentId) REFERENCES offices(officeId) ON DELETE SET NULL
);

--Tabla Grupos-Profesores

CREATE OR REPLACE TABLE TeachersGroups(
teachergroupId      INT NOT NULL AUTO_INCREMENT,
teachingLoad        INT NOT NULL,
teacherId           INT NOT NULL,
groupId             INT NOT NULL,
PRIMARY KEY         (teacherGroupId),
FOREIGN KEY (teacherId) REFERENCES teachers (teacherId),
FOREIGN KEY (groupId) REFERENCES Groups (groupId)
);
--Alumnos


CREATE OR REPLACE TABLE Students(
studentId       INT             NOT NULL AUTO_INCREMENT,
accesMethod     ENUM            ('Selectividad', 'Ciclo', 'Mayor', 'Titulado Extranjero'),
dni             CHAR(9)      NOT NULL UNIQUE,
firstName       VARCHAR(100) NOT NULL,
surname         VARCHAR(100) NOT NULL,
birthDate       DATE             NOT NULL,
email               VARCHAR(250) NOT NULL UNIQUE ,
PRIMARY KEY(studentId)
);

--ASIGNATURAS



CREATE OR REPLACE TABLE Subjects (
subjectId       INT                 NOT NULL AUTO_INCREMENT,
`name`          VARCHAR(100)    NOT NULL UNIQUE,
acronym             VARCHAR(8)      NOT NULL UNIQUE,
credits             INT             NOT NULL,
`year`          INT                 NOT NULL,
`type`          ENUM                ('Formacion Basica','Optativa','Obligatoria'),
degreeId        INT                 NOT NULL,
PRIMARY KEY     (subjectId),
UNIQUE  (degreeId,`name`),
UNIQUE  (degreeId,`acronym`),
FOREIGN KEY (degreeId)  REFERENCES Degrees (degreeId) ON DELETE CASCADE ,
CONSTRAINT negativeSubjectCredits CHECK (credits > 0),
CONSTRAINT invalidSubjectCourse CHECK (year >= 1 AND year <= 5)
);



--GRUPOS

CREATE OR REPLACE TABLE Groups(
groupId         INT             NOT NULL AUTO_INCREMENT,
`name`          VARCHAR(30) NOT NULL,
activity        ENUM            ('Teoria','Laboratorio'),
`year`          INT             NOT NULL,
subjectId       INT             NOT NULL,
PRIMARY KEY (groupId),
FOREIGN KEY (subjectId) REFERENCES Subjects (subjectId),
UNIQUE (`name`, `year`, subjectId),
CONSTRAINT negativeGroupYear CHECK (year > 0)
);

--Grupos de Estudiantes


CREATE OR REPLACE TABLE GroupsStudents(
groupStudentId      INT         NOT NULL AUTO_INCREMENT,
groupId                     INT         NOT NULL,
studentId               INT         NOT NULL,
PRIMARY KEY (groupStudentId),
FOREIGN KEY (groupId) REFERENCES Groups  (groupId),
FOREIGN KEY (studentId) REFERENCES Students (studentId),
UNIQUE (groupId, studentId)
);


-- CALIFICACIONES
CREATE OR REPLACE TABLE Qualification(
qualificationId         INT                 NOT NULL AUTO_INCREMENT,
`value`                     DECIMAL(4,2)    NOT NULL,
gradeCall               INT                 NOT NULL,
honours                     BOOLEAN             NOT NULL,
studentId               INT                 NOT NULL,
groupId                     INT                 NOT NULL,
PRIMARY KEY (qualificationId),
FOREIGN KEY (studentId) REFERENCES Students (studentId),
FOREIGN KEY (groupId) REFERENCES Groups (groupId),
CONSTRAINT invalidGradeValue CHECK (value >= 0 AND value <= 10),
CONSTRAINT invalidGradeCall CHECK (gradeCall >= 1 AND gradeCall <= 3),
CONSTRAINT duplicatedCallGrade UNIQUE (gradeCall, studentId, groupId)
);


-- TUTORIAS

CREATE  OR REPLACE TABLE Tutorials (
tutorialId      INT                 NOT NULL AUTO_INCREMENT,
`day`               ENUM ('Lunes', 'Martes', 'Miercoles', 'Jueves', 'Viernes'),
start_hour      TIME                    NOT NULL,
end_hour            TIME                    NOT NULL,
teacherId       INT,
PRIMARY KEY (tutorialId),
FOREIGN KEY(teacherId) REFERENCES Teachers  (teacherId) ON DELETE SET NULL
);



--CITAS DE TUTORIAS
CREATE OR REPLACE TABLE Appointment (
appointmentId       INT                 NOT NULL AUTO_INCREMENT,
dateAppointment DATE                NOT NULL,
hourAppointment     TIME                NOT NULL,
tutorialId          INT                 NOT NULL,
studentId           INT                 NOT NULL,
PRIMARY KEY(appointmentId),
FOREIGN KEY(tutorialId) REFERENCES Tutorials     (tutorialId) ON DELETE CASCADE ,
FOREIGN KEY(studentId) REFERENCES students (studentId) ON DELETE CASCADE,
UNIQUE (dateAppointment, hourAppointment)
);