entr3.sql

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Versión del servidor:         10.5.4-MariaDB - mariadb.org binary distribution
-- SO del servidor:              Win64
-- HeidiSQL Versión:             11.0.0.5919
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Volcando estructura de base de datos para entregable3
CREATE DATABASE IF NOT EXISTS `entregable3` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `entregable3`;

-- Volcando estructura para tabla entregable3.appointment
CREATE TABLE IF NOT EXISTS `appointment` (
  `appointmentId` int(11) NOT NULL AUTO_INCREMENT,
  `dateAppointment` date NOT NULL,
  `hourAppointment` time NOT NULL,
  `tutorialId` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  PRIMARY KEY (`appointmentId`),
  UNIQUE KEY `dateAppointment` (`dateAppointment`,`hourAppointment`),
  KEY `tutorialId` (`tutorialId`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `appointment_ibfk_1` FOREIGN KEY (`tutorialId`) REFERENCES `tutorials` (`tutorialId`) ON DELETE CASCADE,
  CONSTRAINT `appointment_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.appointment: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `appointment` DISABLE KEYS */;
/*!40000 ALTER TABLE `appointment` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.classroom
CREATE TABLE IF NOT EXISTS `classroom` (
  `classroomId` int(11) NOT NULL AUTO_INCREMENT,
  `loudspeakers` tinyint(1) NOT NULL,
  `projector` tinyint(1) NOT NULL,
  `spaceId` int(11) DEFAULT NULL,
  PRIMARY KEY (`classroomId`),
  KEY `spaceId` (`spaceId`),
  CONSTRAINT `classroom_ibfk_1` FOREIGN KEY (`spaceId`) REFERENCES `spaces` (`spaceId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.classroom: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `classroom` DISABLE KEYS */;
/*!40000 ALTER TABLE `classroom` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.degrees
CREATE TABLE IF NOT EXISTS `degrees` (
  `degreeId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `years` int(11) NOT NULL DEFAULT 4,
  PRIMARY KEY (`degreeId`),
  UNIQUE KEY `name` (`name`),
  CONSTRAINT `invalidDegreeYear` CHECK (`years` between 3 and 5)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.degrees: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `degrees` DISABLE KEYS */;
REPLACE INTO `degrees` (`degreeId`, `name`, `years`) VALUES
    (1, 'Grado en Ingeniería Informática - Tecnologías Informáticas', 4),
    (2, 'Grado en Ingeniería Informática - Ingeniería del Software', 4),
    (3, 'Grado en Ingeniería Informática - Ingeniería de Computadores', 4);
/*!40000 ALTER TABLE `degrees` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.departments
CREATE TABLE IF NOT EXISTS `departments` (
  `departmentId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`departmentId`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.departments: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
REPLACE INTO `departments` (`departmentId`, `name`) VALUES
    (3, '   Ciencias de la Comput. e Int. Artificial'),
    (4, 'Física Aplicada I'),
    (1, 'Lenguajes y Sistemas Informáticos'),
    (2, 'Tecnología Electrónica');
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.groups
CREATE TABLE IF NOT EXISTS `groups` (
  `groupId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `activity` enum('Teoria','Laboratorio') DEFAULT NULL,
  `year` int(11) NOT NULL,
  `subjectId` int(11) NOT NULL,
  PRIMARY KEY (`groupId`),
  UNIQUE KEY `name` (`name`,`year`,`subjectId`),
  KEY `subjectId` (`subjectId`),
  CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`subjectId`) REFERENCES `subjects` (`subjectId`),
  CONSTRAINT `negativeGroupYear` CHECK (`year` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.groups: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `groups` DISABLE KEYS */;
/*!40000 ALTER TABLE `groups` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.groupsstudents
CREATE TABLE IF NOT EXISTS `groupsstudents` (
  `groupStudentId` int(11) NOT NULL AUTO_INCREMENT,
  `groupId` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  PRIMARY KEY (`groupStudentId`),
  UNIQUE KEY `groupId` (`groupId`,`studentId`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `groupsstudents_ibfk_1` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`),
  CONSTRAINT `groupsstudents_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.groupsstudents: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `groupsstudents` DISABLE KEYS */;
/*!40000 ALTER TABLE `groupsstudents` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.offices
CREATE TABLE IF NOT EXISTS `offices` (
  `officeId` int(11) NOT NULL AUTO_INCREMENT,
  `shared` tinyint(1) NOT NULL,
  `freespace` tinyint(1) NOT NULL,
  `spaceId` int(11) DEFAULT NULL,
  PRIMARY KEY (`officeId`),
  KEY `spaceId` (`spaceId`),
  CONSTRAINT `offices_ibfk_1` FOREIGN KEY (`spaceId`) REFERENCES `spaces` (`spaceId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.offices: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `offices` DISABLE KEYS */;
/*!40000 ALTER TABLE `offices` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.qualification
CREATE TABLE IF NOT EXISTS `qualification` (
  `qualificationId` int(11) NOT NULL AUTO_INCREMENT,
  `value` decimal(4,2) NOT NULL,
  `gradeCall` int(11) NOT NULL,
  `honours` tinyint(1) NOT NULL,
  `studentId` int(11) NOT NULL,
  `groupId` int(11) NOT NULL,
  PRIMARY KEY (`qualificationId`),
  UNIQUE KEY `duplicatedCallGrade` (`gradeCall`,`studentId`,`groupId`),
  KEY `studentId` (`studentId`),
  KEY `groupId` (`groupId`),
  CONSTRAINT `qualification_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`),
  CONSTRAINT `qualification_ibfk_2` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`),
  CONSTRAINT `invalidGradeValue` CHECK (`value` >= 0 and `value` <= 10),
  CONSTRAINT `invalidGradeCall` CHECK (`gradeCall` >= 1 and `gradeCall` <= 3)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.qualification: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `qualification` DISABLE KEYS */;
/*!40000 ALTER TABLE `qualification` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.spaces
CREATE TABLE IF NOT EXISTS `spaces` (
  `spaceId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `floor` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  PRIMARY KEY (`spaceId`),
  UNIQUE KEY `name` (`name`),
  CONSTRAINT `invalidcapacity` CHECK (`capacity` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.spaces: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `spaces` DISABLE KEYS */;
/*!40000 ALTER TABLE `spaces` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.students
CREATE TABLE IF NOT EXISTS `students` (
  `studentId` int(11) NOT NULL AUTO_INCREMENT,
  `accesMethod` enum('Selectividad','Ciclo','Mayor','Titulado Extranjero') DEFAULT NULL,
  `dni` char(9) NOT NULL,
  `firstName` varchar(100) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `birthDate` date NOT NULL,
  `email` varchar(250) NOT NULL,
  PRIMARY KEY (`studentId`),
  UNIQUE KEY `dni` (`dni`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.students: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
/*!40000 ALTER TABLE `students` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.subjects
CREATE TABLE IF NOT EXISTS `subjects` (
  `subjectId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `acronym` varchar(8) NOT NULL,
  `credits` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `type` enum('Formacion Basica','Optativa','Obligatoria') DEFAULT NULL,
  `degreeId` int(11) NOT NULL,
  PRIMARY KEY (`subjectId`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `acronym` (`acronym`),
  UNIQUE KEY `degreeId` (`degreeId`,`name`),
  UNIQUE KEY `degreeId_2` (`degreeId`,`acronym`),
  CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`degreeId`) REFERENCES `degrees` (`degreeId`) ON DELETE CASCADE,
  CONSTRAINT `negativeSubjectCredits` CHECK (`credits` > 0),
  CONSTRAINT `invalidSubjectCourse` CHECK (`year` >= 1 and `year` <= 5)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.subjects: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `subjects` DISABLE KEYS */;
/*!40000 ALTER TABLE `subjects` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.teachers
CREATE TABLE IF NOT EXISTS `teachers` (
  `teacherId` int(11) NOT NULL AUTO_INCREMENT,
  `dni` char(9) NOT NULL,
  `firstName` varchar(100) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `birthDate` date NOT NULL,
  `email` varchar(258) NOT NULL,
  `category` enum('Profesor','Titular de Universidad','Profesor Contratado Doctor','Profesor Ayudante Doctor') DEFAULT NULL,
  `officeId` int(11) DEFAULT NULL,
  `departmentId` int(11) DEFAULT NULL,
  PRIMARY KEY (`teacherId`),
  UNIQUE KEY `dni` (`dni`),
  UNIQUE KEY `email` (`email`),
  KEY `officeId` (`officeId`),
  KEY `departmentId` (`departmentId`),
  CONSTRAINT `teachers_ibfk_1` FOREIGN KEY (`officeId`) REFERENCES `offices` (`officeId`) ON DELETE SET NULL,
  CONSTRAINT `teachers_ibfk_2` FOREIGN KEY (`departmentId`) REFERENCES `offices` (`officeId`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.teachers: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.teachersgroups
CREATE TABLE IF NOT EXISTS `teachersgroups` (
  `teachergroupId` int(11) NOT NULL AUTO_INCREMENT,
  `teachingLoad` int(11) NOT NULL,
  `teacherId` int(11) NOT NULL,
  `groupId` int(11) NOT NULL,
  PRIMARY KEY (`teachergroupId`),
  KEY `teacherId` (`teacherId`),
  KEY `groupId` (`groupId`),
  CONSTRAINT `teachersgroups_ibfk_1` FOREIGN KEY (`teacherId`) REFERENCES `teachers` (`teacherId`),
  CONSTRAINT `teachersgroups_ibfk_2` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.teachersgroups: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `teachersgroups` DISABLE KEYS */;
/*!40000 ALTER TABLE `teachersgroups` ENABLE KEYS */;

-- Volcando estructura para tabla entregable3.tutorials
CREATE TABLE IF NOT EXISTS `tutorials` (
  `tutorialId` int(11) NOT NULL AUTO_INCREMENT,
  `day` enum('Lunes','Martes','Miercoles','Jueves','Viernes') DEFAULT NULL,
  `start_hour` time NOT NULL,
  `end_hour` time NOT NULL,
  `teacherId` int(11) DEFAULT NULL,
  PRIMARY KEY (`tutorialId`),
  KEY `teacherId` (`teacherId`),
  CONSTRAINT `tutorials_ibfk_1` FOREIGN KEY (`teacherId`) REFERENCES `teachers` (`teacherId`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Volcando datos para la tabla entregable3.tutorials: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `tutorials` DISABLE KEYS */;
/*!40000 ALTER TABLE `tutorials` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;