· 4 years ago · Dec 26, 2020, 02:30 PM
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
16CREATE DATABASE IF NOT EXISTS `entregable3` /*!40100 DEFAULT CHARACTER SET latin1 */;
17USE `entregable3`;
18
19-- Volcando estructura para tabla entregable3.appointment
20CREATE 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
39CREATE 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
54CREATE 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 */;
65REPLACE 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
72CREATE 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 */;
81REPLACE 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
89CREATE 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
107CREATE 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
123CREATE 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
138CREATE 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
160CREATE 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
175CREATE 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
193CREATE 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
216CREATE 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
240CREATE 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
257CREATE 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 */;