· 4 years ago · Jan 26, 2021, 07:46 PM
1-- Creado por Daniel Ayala, David Ruiz, Agustin Borrego, e Inma Hernandez
2-- Script de creacion de base de datos para examen de laboratorio de IISSI1
3-- En este scripts se encuentran procedimientos para la creacion de tablas
4-- (incluido el uso del procedimiento), la inserción de datos (incluido el
5-- uso del procedimiento), y algunos procedimientos adicionales que pueden
6-- usarse como referencia a
7
8-- Definicion del procedimiento de creacion de tablas
9DELIMITER //
10CREATE OR REPLACE PROCEDURE
11 createTables()
12BEGIN
13 SET FOREIGN_KEY_CHECKS=0;
14 DROP TABLE IF EXISTS Degrees;
15 DROP TABLE IF EXISTS Subjects;
16 DROP TABLE IF EXISTS Groups;
17 DROP TABLE IF EXISTS InternalStudents;
18 DROP TABLE IF EXISTS Students;
19 DROP TABLE IF EXISTS GroupsStudents;
20 DROP TABLE IF EXISTS Grades;
21 DROP TABLE IF EXISTS Offices;
22 DROP TABLE IF EXISTS Classrooms;
23 DROP TABLE IF EXISTS Departments;
24 DROP TABLE IF EXISTS TutoringHours;
25 DROP TABLE IF EXISTS TeachingLoads;
26 DROP TABLE IF EXISTS Appointments;
27 DROP TABLE IF EXISTS Professors;
28 SET FOREIGN_KEY_CHECKS=1;
29
30 CREATE TABLE Offices (
31 officeId INT NOT NULL AUTO_INCREMENT,
32 name VARCHAR(60) NOT NULL,
33 floor INT NOT NULL,
34 capacity INT NOT NULL,
35 PRIMARY KEY (officeId),
36 CONSTRAINT invalidFloor CHECK (floor >= 0),
37 CONSTRAINT invalidCapacity CHECK (capacity > 0),
38 UNIQUE (name)
39 );
40
41 CREATE TABLE Classrooms (
42 classroomId INT NOT NULL AUTO_INCREMENT,
43 name VARCHAR(60) NOT NULL UNIQUE,
44 floor INT NOT NULL,
45 capacity INT NOT NULL,
46 hasProjector BOOLEAN NOT NULL,
47 hasLoudSpeakers BOOLEAN NOT NULL,
48 PRIMARY KEY (classroomId),
49 CONSTRAINT invalidFloor CHECK (floor >= 0),
50 CONSTRAINT invalidCapacity CHECK (capacity > 0)
51 );
52
53 CREATE TABLE Departments (
54 departmentId INT NOT NULL AUTO_INCREMENT,
55 name VARCHAR(60) NOT NULL UNIQUE,
56 PRIMARY KEY (departmentId)
57 );
58
59 CREATE TABLE Degrees(
60 degreeId INT NOT NULL AUTO_INCREMENT,
61 name VARCHAR(60) NOT NULL UNIQUE,
62 years INT DEFAULT 4 NOT NULL,
63 PRIMARY KEY (degreeId),
64 CONSTRAINT invalidDegreeYear CHECK (years >=3 AND years <=5)
65 );
66
67 CREATE TABLE Subjects(
68 subjectId INT NOT NULL AUTO_INCREMENT,
69 name VARCHAR(100) NOT NULL UNIQUE,
70 acronym VARCHAR(8) NOT NULL UNIQUE,
71 credits INT NOT NULL,
72 course INT NOT NULL,
73 type VARCHAR(20) NOT NULL,
74 degreeId INT NOT NULL,
75 departmentId INT NOT NULL,
76 PRIMARY KEY (subjectId),
77 FOREIGN KEY (degreeId) REFERENCES Degrees (degreeId),
78 FOREIGN KEY (departmentId) REFERENCES Departments (departmentId),
79 CONSTRAINT negativeSubjectCredits CHECK (credits > 0),
80 CONSTRAINT invalidSubjectCourse CHECK (course > 0 AND course < 6),
81 CONSTRAINT invalidSubjectType CHECK (type IN ('Formacion Basica',
82 'Optativa',
83 'Obligatoria'))
84 );
85
86 CREATE TABLE Groups(
87 groupId INT NOT NULL AUTO_INCREMENT,
88 name VARCHAR(30) NOT NULL,
89 activity VARCHAR(20) NOT NULL,
90 year INT NOT NULL,
91 subjectId INT NOT NULL,
92 classroomId INT NOT NULL,
93 PRIMARY KEY (groupId),
94 FOREIGN KEY (subjectId) REFERENCES Subjects (subjectId),
95 FOREIGN KEY (classroomId) REFERENCES Classrooms (classroomId),
96 UNIQUE (name, year, subjectId),
97 CONSTRAINT negativeGroupYear CHECK (year > 0),
98 CONSTRAINT invalidGroupActivity CHECK (activity IN ('Teoria',
99 'Laboratorio'))
100 );
101
102 CREATE TABLE Students(
103 studentId INT NOT NULL AUTO_INCREMENT,
104 accessMethod VARCHAR(30) NOT NULL,
105 dni CHAR(9) NOT NULL UNIQUE,
106 firstName VARCHAR(100) NOT NULL,
107 surname VARCHAR(100) NOT NULL,
108 birthDate DATE NOT NULL,
109 email VARCHAR(250) NOT NULL UNIQUE,
110 PRIMARY KEY (studentId),
111 CONSTRAINT invalidStudentAccessMethod CHECK (accessMethod IN ('Selectividad',
112 'Ciclo',
113 'Mayor',
114 'Titulado Extranjero'))
115 );
116
117 CREATE TABLE InternalStudents (
118 internalStudentId INT NOT NULL AUTO_INCREMENT,
119 departmentId INT NOT NULL,
120 studentId INT NOT NULL,
121 academicYear INT NOT NULL,
122 duration INT,
123 PRIMARY KEY (internalStudentId),
124 FOREIGN KEY (studentId) REFERENCES students(studentId),
125 FOREIGN KEY (departmentId) REFERENCES departments(departmentId),
126 UNIQUE (studentId, academicYear),
127 CONSTRAINT invalidDuration CHECK (duration >= 3 && duration <= 9)
128 );
129
130 CREATE TABLE GroupsStudents(
131 groupStudentId INT NOT NULL AUTO_INCREMENT,
132 groupId INT NOT NULL,
133 studentId INT NOT NULL,
134 PRIMARY KEY (groupStudentId),
135 FOREIGN KEY (groupId) REFERENCES Groups (groupId) ON DELETE CASCADE,
136 FOREIGN KEY (studentId) REFERENCES Students (studentId),
137 UNIQUE (groupId, studentId)
138 );
139
140 CREATE TABLE Grades(
141 gradeId INT NOT NULL AUTO_INCREMENT,
142 value DECIMAL(4,2) NOT NULL,
143 gradeCall INT NOT NULL,
144 withHonours BOOLEAN NOT NULL,
145 studentId INT NOT NULL,
146 groupId INT NOT NULL,
147 PRIMARY KEY (gradeId),
148 FOREIGN KEY (studentId) REFERENCES Students (studentId),
149 FOREIGN KEY (groupId) REFERENCES Groups (groupId) ON DELETE CASCADE,
150 CONSTRAINT invalidGradeValue CHECK (value >= 0 AND value <= 10),
151 CONSTRAINT invalidGradeCall CHECK (gradeCall >= 1 AND gradeCall <= 3),
152 CONSTRAINT duplicatedCallGrade UNIQUE (gradeCall, studentId, groupId)
153 );
154
155 CREATE TABLE Professors (
156 professorId INT NOT NULL AUTO_INCREMENT,
157 officeId INT NOT NULL,
158 departmentId INT NOT NULL,
159 category VARCHAR(5) NOT NULL,
160 dni CHAR(9) NOT NULL UNIQUE,
161 firstName VARCHAR(100) NOT NULL,
162 surname VARCHAR(100) NOT NULL,
163 birthDate DATE NOT NULL,
164 email VARCHAR(250) NOT NULL UNIQUE,
165 PRIMARY KEY (professorId),
166 FOREIGN KEY (officeId) REFERENCES Offices (officeId),
167 FOREIGN KEY (departmentId) REFERENCES Departments (departmentId),
168 CONSTRAINT invalidCategory CHECK (category IN ('CU',
169 'TU',
170 'PCD',
171 'PAD'))
172 );
173
174 CREATE TABLE TutoringHours(
175 tutoringHoursId INT NOT NULL AUTO_INCREMENT,
176 professorId INT NOT NULL,
177 dayOfWeek INT NOT NULL,
178 startHour TIME,
179 endHour TIME,
180 PRIMARY KEY (tutoringHoursId),
181 FOREIGN KEY (professorId) REFERENCES Professors (professorId),
182 CONSTRAINT invalidDayOfWeek CHECK (dayOfWeek >= 0 AND dayOfWeek <= 6)
183 );
184
185 CREATE TABLE Appointments(
186 appointmentId INT NOT NULL AUTO_INCREMENT,
187 tutoringHoursId INT NOT NULL,
188 studentId INT NOT NULL,
189 hour TIME NOT NULL,
190 date DATE NOT NULL,
191 PRIMARY KEY (appointmentId),
192 FOREIGN KEY (tutoringHoursId) REFERENCES TutoringHours (tutoringHoursId),
193 FOREIGN KEY (studentId) REFERENCES Students (studentId)
194 );
195
196 CREATE TABLE TeachingLoads(
197 teachingLoadId INT NOT NULL AUTO_INCREMENT,
198 professorId INT NOT NULL,
199 groupId INT NOT NULL,
200 credits INT NOT NULL,
201 PRIMARY KEY (teachingLoadId),
202 FOREIGN KEY (professorId) REFERENCES Professors (professorId),
203 FOREIGN KEY (groupId) REFERENCES Groups (groupId),
204 CONSTRAINT invalidCredits CHECK (credits > 0)
205 );
206END //
207DELIMITER ;
208
209-- Llamada al procedimiento de creacion de tablas
210CALL createTables();
211
212-- Definicion del procedimiento de insercion de datos
213DELIMITER //
214CREATE OR REPLACE PROCEDURE
215 populate()
216BEGIN
217 SET FOREIGN_KEY_CHECKS=0;
218 DELETE FROM Degrees;
219 DELETE FROM Subjects;
220 DELETE FROM Groups;
221 DELETE FROM Students;
222 DELETE FROM GroupsStudents;
223 DELETE FROM Grades;
224 DELETE FROM Offices;
225 DELETE FROM Classrooms;
226 DELETE FROM Departments;
227 DELETE FROM TutoringHours;
228 DELETE FROM TeachingLoads;
229 DELETE FROM Appointments;
230 DELETE FROM Professors;
231 SET FOREIGN_KEY_CHECKS=1;
232 ALTER TABLE Degrees AUTO_INCREMENT=1;
233 ALTER TABLE Subjects AUTO_INCREMENT=1;
234 ALTER TABLE Groups AUTO_INCREMENT=1;
235 ALTER TABLE Students AUTO_INCREMENT=1;
236 ALTER TABLE GroupsStudents AUTO_INCREMENT=1;
237 ALTER TABLE Grades AUTO_INCREMENT=1;
238 ALTER TABLE Offices AUTO_INCREMENT=1;
239 ALTER TABLE Classrooms AUTO_INCREMENT=1;
240 ALTER TABLE Departments AUTO_INCREMENT=1;
241 ALTER TABLE TutoringHours AUTO_INCREMENT=1;
242 ALTER TABLE TeachingLoads AUTO_INCREMENT=1;
243 ALTER TABLE Appointments AUTO_INCREMENT=1;
244 ALTER TABLE Professors AUTO_INCREMENT=1;
245
246 INSERT INTO Offices (name, floor, capacity) VALUES
247 ('F1.85', 1, 5),
248 ('F0.45', 0, 3);
249
250 INSERT INTO Classrooms (name, floor, capacity, hasProjector, hasLoudSpeakers) VALUES
251 ('F1.31', 1, 30, TRUE, FALSE),
252 ('F1.33', 1, 35, TRUE, FALSE),
253 ('A0.31', 1, 80, TRUE, TRUE);
254
255 INSERT INTO Departments (name) VALUES
256 ('Lenguajes y Sistemas Informáticos'),
257 ('Matemáticas');
258
259 INSERT INTO Degrees (name, years) VALUES
260 ('Ingeniería del Software', 4),
261 ('Ingeniería de Computadores', 4),
262 ('Tecnologías Informáticas', 4);
263
264 INSERT INTO Subjects (name, acronym, credits, course, type, degreeId, departmentId) VALUES
265 ('Diseño y Pruebas', 'DP', 12, 3, 'Obligatoria', 1, 1),
266 ('Acceso Inteligente a la Informacion', 'AII', 6, 4, 'Optativa', 1, 1),
267 ('Optimizacion de Sistemas', 'OS', 6, 4, 'Optativa', 1, 1),
268 ('Ingeniería de Requisitos', 'IR', 6, 2, 'Obligatoria', 1, 1),
269 ('Análisis y Diseño de Datos y Algoritmos', 'ADDA', 12, 2, 'Obligatoria', 1, 1),
270 -- 5/6
271 ('Introducción a la Matematica Discreta', 'IMD', 6, 1, 'Formacion Basica', 2, 2),
272 ('Redes de Computadores', 'RC', 6, 2, 'Obligatoria', 2, 1),
273 ('Teoría de Grafos', 'TG', 6, 3, 'Obligatoria', 2, 2),
274 ('Aplicaciones de Soft Computing', 'ASC', 6, 4, 'Optativa', 2, 1),
275 -- 9/10
276 ('Fundamentos de Programación', 'FP', 12, 1, 'Formacion Basica', 3, 1),
277 ('Lógica Informatica', 'LI', 6, 2, 'Optativa', 3, 2),
278 ('Gestión y Estrategia Empresarial', 'GEE', 12, 3, 'Optativa', 3, 1),
279 ('Trabajo de Fin de Grado', 'TFG', 12, 4, 'Obligatoria', 3, 1);
280
281 INSERT INTO Groups (name, activity, year, subjectId, classroomId) VALUES
282 ('T1', 'Teoria', 2018, 1, 1),
283 ('T2', 'Teoria', 2018, 1, 2),
284 ('L1', 'Laboratorio', 2018, 1, 3),
285 ('L2', 'Laboratorio', 2018, 1, 1),
286 ('L3', 'Laboratorio', 2018, 1, 2),
287 ('T1', 'Teoria', 2019, 1, 3),
288 ('T2', 'Laboratorio', 2019, 1, 1),
289 ('L1', 'Laboratorio', 2019, 1, 2),
290 ('L2', 'Laboratorio', 2019, 1, 3),
291 -- 9/10
292 ('Teor1', 'Teoria', 2018, 2, 1),
293 ('Teor2', 'Teoria', 2018, 2, 2),
294 ('Lab1', 'Laboratorio', 2018, 2, 3),
295 ('Lab2', 'Laboratorio', 2018, 2, 1),
296 ('Teor1', 'Teoria', 2019, 2, 2),
297 ('Lab1', 'Laboratorio', 2019, 2, 3),
298 ('Lab2', 'Laboratorio', 2019, 2, 1),
299 -- 16/17
300 ('T1', 'Teoria', 2019, 10, 2),
301 ('T2', 'Teoria', 2019, 10, 3),
302 ('T3', 'Teoria', 2019, 10, 1),
303 ('L1', 'Laboratorio', 2019, 10, 2),
304 ('L2', 'Laboratorio', 2019, 10, 3),
305 ('L3', 'Laboratorio', 2019, 10, 1),
306 ('L4', 'Laboratorio', 2019, 10, 2),
307 -- 23/24
308 ('Clase', 'Teoria', 2019, 12, 3),
309 ('T1', 'Teoria', 2019, 6, 1);
310
311 INSERT INTO Students (accessMethod, dni, firstname, surname, birthdate, email) VALUES
312 ('Selectividad', '12345678A', 'Daniel', 'Pérez', '1991-01-01', 'daniel@alum.us.es'),
313 ('Selectividad', '22345678A', 'Rafael', 'Ramírez', '1992-01-01', 'rafael@alum.us.es'),
314 ('Selectividad', '32345678A', 'Gabriel', 'Hernández', '1993-01-01', 'gabriel@alum.us.es'),
315 ('Selectividad', '42345678A', 'Manuel', 'Fernández', '1994-01-01', 'manuel@alum.us.es'),
316 ('Selectividad', '52345678A', 'Joel', 'Gómez', '1995-01-01', 'joel@alum.us.es'),
317 ('Selectividad', '62345678A', 'Abel', 'López', '1996-01-01', 'abel@alum.us.es'),
318 ('Selectividad', '72345678A', 'Azael', 'González', '1997-01-01', 'azael@alum.us.es'),
319 ('Selectividad', '8345678A', 'Uriel', 'Martínez', '1998-01-01', 'uriel@alum.us.es'),
320 ('Selectividad', '92345678A', 'Gael', 'Sánchez', '1999-01-01', 'gael@alum.us.es'),
321 ('Titulado Extranjero', '12345678B', 'Noel', 'Álvarez', '1991-02-02', 'noel@alum.us.es'),
322 ('Titulado Extranjero', '22345678B', 'Ismael', 'Antúnez', '1992-02-02', 'ismael@alum.us.es'),
323 ('Titulado Extranjero', '32345678B', 'Nathanael', 'Antolinez', '1993-02-02', 'nathanael@alum.us.es'),
324 ('Titulado Extranjero', '42345678B', 'Ezequiel', 'Aznárez', '1994-02-02', 'ezequiel@alum.us.es'),
325 ('Titulado Extranjero', '52345678B', 'Ángel', 'Chávez', '1995-02-02', 'angel@alum.us.es'),
326 ('Titulado Extranjero', '62345678B', 'Matusael', 'Gutiérrez', '1996-02-02', 'matusael@alum.us.es'),
327 ('Titulado Extranjero', '72345678B', 'Samael', 'Gálvez', '1997-02-02', 'samael@alum.us.es'),
328 ('Titulado Extranjero', '82345678B', 'Baraquiel', 'Ibáñez', '1998-02-02', 'baraquiel@alum.us.es'),
329 ('Titulado Extranjero', '92345678B', 'Otoniel', 'Idiáquez', '1999-02-02', 'otoniel@alum.us.es'),
330 ('Titulado Extranjero', '12345678C', 'Niriel', 'Benítez', '1991-03-03', 'niriel@alum.us.es'),
331 ('Titulado Extranjero', '22345678C', 'Múriel', 'Bermúdez', '1992-03-03', 'muriel@alum.us.es'),
332 ('Titulado Extranjero', '32345678C', 'John', 'AII', '2000-01-01', 'john@alum.us.es');
333
334 INSERT INTO GroupsStudents (groupId, studentId) VALUES
335 (1, 1),
336 (3, 1),
337 (7, 1),
338 (8, 1),
339 (10, 1),
340 (12, 1),
341 -- 6/7
342 (2, 2),
343 (3, 2),
344 (10, 2),
345 (12, 2),
346 -- 10/11
347 (18, 21),
348 (21, 21),
349 -- 12/13
350 (1, 9);
351
352 INSERT INTO Grades (value, gradeCall, withHonours, studentId, groupId) VALUES
353 (4.50, 1, 0, 1, 1),
354 (3.25, 2, 0, 1, 1),
355 (9.95, 1, 0, 1, 7),
356 (7.5, 1, 0, 1, 10),
357 -- 4/5
358 (2.50, 1, 0, 2, 2),
359 (5.00, 2, 0, 2, 2),
360 (10.00, 1, 1, 2, 10),
361 -- 7/8
362 (0.00, 1, 0, 21, 18),
363 (1.25, 2, 0, 21, 18),
364 (0.5, 3, 0, 21, 18);
365
366 INSERT INTO Professors (officeId, departmentId, category, dni, firstname, surname, birthdate, email) VALUES
367 (1, 1, 'PAD', '42345678C', 'Fernando', 'Ramírez', '1960-05-02', 'fernando@us.es'),
368 (1, 1, 'TU', '52345678C', 'David', 'Zuir', '1902-01-01', 'dzuir@us.es'),
369 (1, 1, 'TU', '62345678C', 'Antonio', 'Zuir', '1902-01-01', 'azuir@us.es'),
370 (1, 2, 'CU', '72345678C', 'Rafael', 'Gómez', '1959-12-12', 'rdgomez@us.es'),
371 (2, 1, 'TU', '82345678C', 'Inma', 'Hernández', '1234-5-6', 'inmahrdz@us.es');
372
373 INSERT INTO TutoringHours (professorId, dayOfWeek, startHour, endHour) VALUES
374 (1, 0, '12:00:00', '14:00:00'),
375 (1, 1, '18:00:00', '19:00:00'),
376 (1, 1, '11:30:00', '12:30:00'),
377 (2, 2, '10:00:00', '20:00:00');
378
379 INSERT INTO Appointments (tutoringHoursId, studentId, hour, date) VALUES
380 (1, 1, '13:00:00', '2019-11-18'),
381 (2, 2, '18:20:00', '2019-11-19'),
382 (4, 1, '15:00:00', '2019-11-20');
383
384 INSERT INTO TeachingLoads (professorId, groupId, credits) VALUES
385 (1, 1, 6),
386 (2, 1, 12),
387 (1, 2, 6),
388 (1, 3, 12);
389END //
390DELIMITER ;
391
392-- Llamada al proceso de insercion de datos
393CALL populate();
394
395-- Procedimientos varios
396
397-- RF-001
398DELIMITER //
399CREATE OR REPLACE PROCEDURE
400 createGrade(groupId INT, studentId INT, gradeCall INT, withHonours BOOLEAN, value DECIMAL(4,2))
401BEGIN
402 INSERT INTO Grades (groupId, studentId, gradeCall, withHonours, value) VALUES (groupId, studentId, gradeCall, withHonours, value);
403END //
404DELIMITER ;
405
406-- RF-006
407DELIMITER //
408CREATE OR REPLACE PROCEDURE
409 procedureDeleteGrades(studentDni CHAR(9))
410BEGIN
411 DECLARE id INT;
412 SET id = (SELECT studentId FROM Students WHERE dni=studentDni);
413 DELETE FROM Grades WHERE studentId=id;
414END //
415DELIMITER ;
416
417-- Triggers
418
419-- RN-006
420DELIMITER //
421CREATE OR REPLACE TRIGGER triggerWithHonours
422 BEFORE INSERT ON Grades
423 FOR EACH ROW
424 BEGIN
425 IF (new.withHonours = 1 AND new.value < 9.0) THEN
426 SIGNAL SQLSTATE '45000' SET message_text =
427 'Para obtener matrícula hay que sacar al menos un 9';
428 END IF;
429 END//
430DELIMITER ;
431
432-- ExamenSimulacro
433
434DELIMITER //
435CREATE OR REPLACE PROCEDURE
436 pInsertInterns()
437BEGIN
438 INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES (1, 1, 2019, 3);
439 INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES (1, 1, 2020, 6);
440 INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES (1, 2, 2019, NULL);
441END //
442DELIMITER ;
443
444
445-- Disparador 1
446DELIMITER //
447CREATE OR REPLACE TRIGGER tCorrectDuration
448 BEFORE INSERT ON InternalStudents
449 FOR EACH ROW
450 BEGIN
451 IF (new.duration >9) THEN
452 SET new.duration = 8;
453 END IF;
454 END//
455DELIMITER ;
456
457-- Procedure 2
458
459DELIMITER //
460CREATE OR REPLACE PROCEDURE
461 pUpdateInterns(s INT,d INT)
462BEGIN
463 UPDATE InternalStudents SET studentId = d WHERE studentId = d;
464
465END //
466DELIMITER ;
467
468-- Procedure 3
469DELIMITER //
470CREATE OR REPLACE PROCEDURE
471 pDeleteInterns(s INT)
472BEGIN
473 DELETE FROM InternalStudents WHERE studentId=s;
474END //
475DELIMITER ;
476
477-- LLamadas
478CALL pInsertInterns();
479CALL pUpdateInterns(1, 13);
480CALL pDeleteInterns(2);
481
482-- Consultas
483
484-- SELECT firstName, NAME, credits FROM professors NATURAL JOIN teachingloads NATURAL JOIN groups;
485
486-- SELECT AVG(VALUE) FROM grades WHERE groupId = 2;
487
488-- SELECT studentId, MAX(VALUE) FROM grades GROUP BY studentId ORDER BY value DESC;
489
490-- SELECT firstName, surname, COUNT(professorId) numberOfGroups FROM professors NATURAL JOIN teachingloads GROUP BY professorId ORDER BY numberOfGroups DESC;