DekkerBass

entr3.sql

Dec 26th, 2020 (edited)
842
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- --------------------------------------------------------
  2. -- Host:                         127.0.0.1
  3. -- Versión del servidor:         10.5.4-MariaDB - mariadb.org binary distribution
  4. -- SO del servidor:              Win64
  5. -- HeidiSQL Versión:             11.0.0.5919
  6. -- --------------------------------------------------------
  7.  
  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET NAMES utf8 */;
  10. /*!50503 SET NAMES utf8mb4 */;
  11. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  12. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  13.  
  14.  
  15. -- Volcando estructura de base de datos para entregable3
  16. CREATE DATABASE IF NOT EXISTS `entregable3` /*!40100 DEFAULT CHARACTER SET latin1 */;
  17. USE `entregable3`;
  18.  
  19. -- Volcando estructura para tabla entregable3.appointment
  20. CREATE TABLE IF NOT EXISTS `appointment` (
  21.   `appointmentId` int(11) NOT NULL AUTO_INCREMENT,
  22.   `dateAppointment` date NOT NULL,
  23.   `hourAppointment` time NOT NULL,
  24.   `tutorialId` int(11) NOT NULL,
  25.   `studentId` int(11) NOT NULL,
  26.   PRIMARY KEY (`appointmentId`),
  27.   UNIQUE KEY `dateAppointment` (`dateAppointment`,`hourAppointment`),
  28.   KEY `tutorialId` (`tutorialId`),
  29.   KEY `studentId` (`studentId`),
  30.   CONSTRAINT `appointment_ibfk_1` FOREIGN KEY (`tutorialId`) REFERENCES `tutorials` (`tutorialId`) ON DELETE CASCADE,
  31.   CONSTRAINT `appointment_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`) ON DELETE CASCADE
  32. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  33.  
  34. -- Volcando datos para la tabla entregable3.appointment: ~0 rows (aproximadamente)
  35. /*!40000 ALTER TABLE `appointment` DISABLE KEYS */;
  36. /*!40000 ALTER TABLE `appointment` ENABLE KEYS */;
  37.  
  38. -- Volcando estructura para tabla entregable3.classroom
  39. CREATE TABLE IF NOT EXISTS `classroom` (
  40.   `classroomId` int(11) NOT NULL AUTO_INCREMENT,
  41.   `loudspeakers` tinyint(1) NOT NULL,
  42.   `projector` tinyint(1) NOT NULL,
  43.   `spaceId` int(11) DEFAULT NULL,
  44.   PRIMARY KEY (`classroomId`),
  45.   KEY `spaceId` (`spaceId`),
  46.   CONSTRAINT `classroom_ibfk_1` FOREIGN KEY (`spaceId`) REFERENCES `spaces` (`spaceId`) ON DELETE CASCADE ON UPDATE CASCADE
  47. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  48.  
  49. -- Volcando datos para la tabla entregable3.classroom: ~0 rows (aproximadamente)
  50. /*!40000 ALTER TABLE `classroom` DISABLE KEYS */;
  51. /*!40000 ALTER TABLE `classroom` ENABLE KEYS */;
  52.  
  53. -- Volcando estructura para tabla entregable3.degrees
  54. CREATE TABLE IF NOT EXISTS `degrees` (
  55.   `degreeId` int(11) NOT NULL AUTO_INCREMENT,
  56.   `name` varchar(60) NOT NULL,
  57.   `years` int(11) NOT NULL DEFAULT 4,
  58.   PRIMARY KEY (`degreeId`),
  59.   UNIQUE KEY `name` (`name`),
  60.   CONSTRAINT `invalidDegreeYear` CHECK (`years` between 3 and 5)
  61. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  62.  
  63. -- Volcando datos para la tabla entregable3.degrees: ~0 rows (aproximadamente)
  64. /*!40000 ALTER TABLE `degrees` DISABLE KEYS */;
  65. REPLACE INTO `degrees` (`degreeId`, `name`, `years`) VALUES
  66.     (1, 'Grado en Ingeniería Informática - Tecnologías Informáticas', 4),
  67.     (2, 'Grado en Ingeniería Informática - Ingeniería del Software', 4),
  68.     (3, 'Grado en Ingeniería Informática - Ingeniería de Computadores', 4);
  69. /*!40000 ALTER TABLE `degrees` ENABLE KEYS */;
  70.  
  71. -- Volcando estructura para tabla entregable3.departments
  72. CREATE TABLE IF NOT EXISTS `departments` (
  73.   `departmentId` int(11) NOT NULL AUTO_INCREMENT,
  74.   `name` varchar(100) NOT NULL,
  75.   PRIMARY KEY (`departmentId`),
  76.   UNIQUE KEY `name` (`name`)
  77. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
  78.  
  79. -- Volcando datos para la tabla entregable3.departments: ~0 rows (aproximadamente)
  80. /*!40000 ALTER TABLE `departments` DISABLE KEYS */;
  81. REPLACE INTO `departments` (`departmentId`, `name`) VALUES
  82.     (3, '   Ciencias de la Comput. e Int. Artificial'),
  83.     (4, 'Física Aplicada I'),
  84.     (1, 'Lenguajes y Sistemas Informáticos'),
  85.     (2, 'Tecnología Electrónica');
  86. /*!40000 ALTER TABLE `departments` ENABLE KEYS */;
  87.  
  88. -- Volcando estructura para tabla entregable3.groups
  89. CREATE TABLE IF NOT EXISTS `groups` (
  90.   `groupId` int(11) NOT NULL AUTO_INCREMENT,
  91.   `name` varchar(30) NOT NULL,
  92.   `activity` enum('Teoria','Laboratorio') DEFAULT NULL,
  93.   `year` int(11) NOT NULL,
  94.   `subjectId` int(11) NOT NULL,
  95.   PRIMARY KEY (`groupId`),
  96.   UNIQUE KEY `name` (`name`,`year`,`subjectId`),
  97.   KEY `subjectId` (`subjectId`),
  98.   CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`subjectId`) REFERENCES `subjects` (`subjectId`),
  99.   CONSTRAINT `negativeGroupYear` CHECK (`year` > 0)
  100. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  101.  
  102. -- Volcando datos para la tabla entregable3.groups: ~0 rows (aproximadamente)
  103. /*!40000 ALTER TABLE `groups` DISABLE KEYS */;
  104. /*!40000 ALTER TABLE `groups` ENABLE KEYS */;
  105.  
  106. -- Volcando estructura para tabla entregable3.groupsstudents
  107. CREATE TABLE IF NOT EXISTS `groupsstudents` (
  108.   `groupStudentId` int(11) NOT NULL AUTO_INCREMENT,
  109.   `groupId` int(11) NOT NULL,
  110.   `studentId` int(11) NOT NULL,
  111.   PRIMARY KEY (`groupStudentId`),
  112.   UNIQUE KEY `groupId` (`groupId`,`studentId`),
  113.   KEY `studentId` (`studentId`),
  114.   CONSTRAINT `groupsstudents_ibfk_1` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`),
  115.   CONSTRAINT `groupsstudents_ibfk_2` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`)
  116. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  117.  
  118. -- Volcando datos para la tabla entregable3.groupsstudents: ~0 rows (aproximadamente)
  119. /*!40000 ALTER TABLE `groupsstudents` DISABLE KEYS */;
  120. /*!40000 ALTER TABLE `groupsstudents` ENABLE KEYS */;
  121.  
  122. -- Volcando estructura para tabla entregable3.offices
  123. CREATE TABLE IF NOT EXISTS `offices` (
  124.   `officeId` int(11) NOT NULL AUTO_INCREMENT,
  125.   `shared` tinyint(1) NOT NULL,
  126.   `freespace` tinyint(1) NOT NULL,
  127.   `spaceId` int(11) DEFAULT NULL,
  128.   PRIMARY KEY (`officeId`),
  129.   KEY `spaceId` (`spaceId`),
  130.   CONSTRAINT `offices_ibfk_1` FOREIGN KEY (`spaceId`) REFERENCES `spaces` (`spaceId`) ON DELETE CASCADE ON UPDATE CASCADE
  131. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  132.  
  133. -- Volcando datos para la tabla entregable3.offices: ~0 rows (aproximadamente)
  134. /*!40000 ALTER TABLE `offices` DISABLE KEYS */;
  135. /*!40000 ALTER TABLE `offices` ENABLE KEYS */;
  136.  
  137. -- Volcando estructura para tabla entregable3.qualification
  138. CREATE TABLE IF NOT EXISTS `qualification` (
  139.   `qualificationId` int(11) NOT NULL AUTO_INCREMENT,
  140.   `value` decimal(4,2) NOT NULL,
  141.   `gradeCall` int(11) NOT NULL,
  142.   `honours` tinyint(1) NOT NULL,
  143.   `studentId` int(11) NOT NULL,
  144.   `groupId` int(11) NOT NULL,
  145.   PRIMARY KEY (`qualificationId`),
  146.   UNIQUE KEY `duplicatedCallGrade` (`gradeCall`,`studentId`,`groupId`),
  147.   KEY `studentId` (`studentId`),
  148.   KEY `groupId` (`groupId`),
  149.   CONSTRAINT `qualification_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `students` (`studentId`),
  150.   CONSTRAINT `qualification_ibfk_2` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`),
  151.   CONSTRAINT `invalidGradeValue` CHECK (`value` >= 0 and `value` <= 10),
  152.   CONSTRAINT `invalidGradeCall` CHECK (`gradeCall` >= 1 and `gradeCall` <= 3)
  153. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  154.  
  155. -- Volcando datos para la tabla entregable3.qualification: ~0 rows (aproximadamente)
  156. /*!40000 ALTER TABLE `qualification` DISABLE KEYS */;
  157. /*!40000 ALTER TABLE `qualification` ENABLE KEYS */;
  158.  
  159. -- Volcando estructura para tabla entregable3.spaces
  160. CREATE TABLE IF NOT EXISTS `spaces` (
  161.   `spaceId` int(11) NOT NULL AUTO_INCREMENT,
  162.   `name` varchar(100) NOT NULL,
  163.   `floor` int(11) NOT NULL,
  164.   `capacity` int(11) NOT NULL,
  165.   PRIMARY KEY (`spaceId`),
  166.   UNIQUE KEY `name` (`name`),
  167.   CONSTRAINT `invalidcapacity` CHECK (`capacity` > 0)
  168. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  169.  
  170. -- Volcando datos para la tabla entregable3.spaces: ~0 rows (aproximadamente)
  171. /*!40000 ALTER TABLE `spaces` DISABLE KEYS */;
  172. /*!40000 ALTER TABLE `spaces` ENABLE KEYS */;
  173.  
  174. -- Volcando estructura para tabla entregable3.students
  175. CREATE TABLE IF NOT EXISTS `students` (
  176.   `studentId` int(11) NOT NULL AUTO_INCREMENT,
  177.   `accesMethod` enum('Selectividad','Ciclo','Mayor','Titulado Extranjero') DEFAULT NULL,
  178.   `dni` char(9) NOT NULL,
  179.   `firstName` varchar(100) NOT NULL,
  180.   `surname` varchar(100) NOT NULL,
  181.   `birthDate` date NOT NULL,
  182.   `email` varchar(250) NOT NULL,
  183.   PRIMARY KEY (`studentId`),
  184.   UNIQUE KEY `dni` (`dni`),
  185.   UNIQUE KEY `email` (`email`)
  186. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  187.  
  188. -- Volcando datos para la tabla entregable3.students: ~0 rows (aproximadamente)
  189. /*!40000 ALTER TABLE `students` DISABLE KEYS */;
  190. /*!40000 ALTER TABLE `students` ENABLE KEYS */;
  191.  
  192. -- Volcando estructura para tabla entregable3.subjects
  193. CREATE TABLE IF NOT EXISTS `subjects` (
  194.   `subjectId` int(11) NOT NULL AUTO_INCREMENT,
  195.   `name` varchar(100) NOT NULL,
  196.   `acronym` varchar(8) NOT NULL,
  197.   `credits` int(11) NOT NULL,
  198.   `year` int(11) NOT NULL,
  199.   `type` enum('Formacion Basica','Optativa','Obligatoria') DEFAULT NULL,
  200.   `degreeId` int(11) NOT NULL,
  201.   PRIMARY KEY (`subjectId`),
  202.   UNIQUE KEY `name` (`name`),
  203.   UNIQUE KEY `acronym` (`acronym`),
  204.   UNIQUE KEY `degreeId` (`degreeId`,`name`),
  205.   UNIQUE KEY `degreeId_2` (`degreeId`,`acronym`),
  206.   CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`degreeId`) REFERENCES `degrees` (`degreeId`) ON DELETE CASCADE,
  207.   CONSTRAINT `negativeSubjectCredits` CHECK (`credits` > 0),
  208.   CONSTRAINT `invalidSubjectCourse` CHECK (`year` >= 1 and `year` <= 5)
  209. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  210.  
  211. -- Volcando datos para la tabla entregable3.subjects: ~0 rows (aproximadamente)
  212. /*!40000 ALTER TABLE `subjects` DISABLE KEYS */;
  213. /*!40000 ALTER TABLE `subjects` ENABLE KEYS */;
  214.  
  215. -- Volcando estructura para tabla entregable3.teachers
  216. CREATE TABLE IF NOT EXISTS `teachers` (
  217.   `teacherId` int(11) NOT NULL AUTO_INCREMENT,
  218.   `dni` char(9) NOT NULL,
  219.   `firstName` varchar(100) NOT NULL,
  220.   `surname` varchar(100) NOT NULL,
  221.   `birthDate` date NOT NULL,
  222.   `email` varchar(258) NOT NULL,
  223.   `category` enum('Profesor','Titular de Universidad','Profesor Contratado Doctor','Profesor Ayudante Doctor') DEFAULT NULL,
  224.   `officeId` int(11) DEFAULT NULL,
  225.   `departmentId` int(11) DEFAULT NULL,
  226.   PRIMARY KEY (`teacherId`),
  227.   UNIQUE KEY `dni` (`dni`),
  228.   UNIQUE KEY `email` (`email`),
  229.   KEY `officeId` (`officeId`),
  230.   KEY `departmentId` (`departmentId`),
  231.   CONSTRAINT `teachers_ibfk_1` FOREIGN KEY (`officeId`) REFERENCES `offices` (`officeId`) ON DELETE SET NULL,
  232.   CONSTRAINT `teachers_ibfk_2` FOREIGN KEY (`departmentId`) REFERENCES `offices` (`officeId`) ON DELETE SET NULL
  233. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  234.  
  235. -- Volcando datos para la tabla entregable3.teachers: ~0 rows (aproximadamente)
  236. /*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
  237. /*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
  238.  
  239. -- Volcando estructura para tabla entregable3.teachersgroups
  240. CREATE TABLE IF NOT EXISTS `teachersgroups` (
  241.   `teachergroupId` int(11) NOT NULL AUTO_INCREMENT,
  242.   `teachingLoad` int(11) NOT NULL,
  243.   `teacherId` int(11) NOT NULL,
  244.   `groupId` int(11) NOT NULL,
  245.   PRIMARY KEY (`teachergroupId`),
  246.   KEY `teacherId` (`teacherId`),
  247.   KEY `groupId` (`groupId`),
  248.   CONSTRAINT `teachersgroups_ibfk_1` FOREIGN KEY (`teacherId`) REFERENCES `teachers` (`teacherId`),
  249.   CONSTRAINT `teachersgroups_ibfk_2` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`)
  250. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  251.  
  252. -- Volcando datos para la tabla entregable3.teachersgroups: ~0 rows (aproximadamente)
  253. /*!40000 ALTER TABLE `teachersgroups` DISABLE KEYS */;
  254. /*!40000 ALTER TABLE `teachersgroups` ENABLE KEYS */;
  255.  
  256. -- Volcando estructura para tabla entregable3.tutorials
  257. CREATE TABLE IF NOT EXISTS `tutorials` (
  258.   `tutorialId` int(11) NOT NULL AUTO_INCREMENT,
  259.   `day` enum('Lunes','Martes','Miercoles','Jueves','Viernes') DEFAULT NULL,
  260.   `start_hour` time NOT NULL,
  261.   `end_hour` time NOT NULL,
  262.   `teacherId` int(11) DEFAULT NULL,
  263.   PRIMARY KEY (`tutorialId`),
  264.   KEY `teacherId` (`teacherId`),
  265.   CONSTRAINT `tutorials_ibfk_1` FOREIGN KEY (`teacherId`) REFERENCES `teachers` (`teacherId`) ON DELETE SET NULL
  266. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  267.  
  268. -- Volcando datos para la tabla entregable3.tutorials: ~0 rows (aproximadamente)
  269. /*!40000 ALTER TABLE `tutorials` DISABLE KEYS */;
  270. /*!40000 ALTER TABLE `tutorials` ENABLE KEYS */;
  271.  
  272. /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
  273. /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
  274. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
RAW Paste Data