FrPATrVR

1CREATE DATABASE IF NOT EXISTS Entregable3;
2USE Entregable3;
3
4
5-- Grados 
6
7CREATE OR REPLACE TABLE degrees (
8degreeId 		INT 					NOT NULL AUTO_INCREMENT,
9`name` 			VARCHAR (60) 		NOT NULL UNIQUE,
10years 			INT DEFAULT (4)	NOT NULL,
11PRIMARY KEY (degreeId),
12CONSTRAINT invalidDegreeYear CHECK (years BETWEEN 3 AND 5)
13);
14
15
16--DEPARTAMENTOS
17
18
19CREATE OR REPLACE TABLE Departments (
20departmentId		INT 				NOT NULL AUTO_INCREMENT,
21`name`				VARCHAR(100)	NOT NULL,
22PRIMARY KEY (departmentId),
23UNIQUE (`name`)
24);
25
26
27--ESPACIOS 
28
29CREATE OR REPLACE TABLE  Spaces (
30spaceId		INT 				NOT NULL AUTO_INCREMENT,
31`name`		VARCHAR (100) 	NOT NULL UNIQUE,
32`floor`		INT 				NOT NULL,
33capacity		INT 				NOT NULL,
34PRIMARY KEY(spaceId),
35CONSTRAINT invalidcapacity CHECK (capacity>0)
36);
37
38#DESPACHOS
39
40CREATE OR REPLACE TABLE Offices(
41officeId		INT 				NOT NULL AUTO_INCREMENT,
42`shared`		BOOLEAN 			NOT NULL ,
43`freespace` BOOLEAN 			NOT NULL,
44spaceId		INT,
45PRIMARY KEY (officeId),
46FOREIGN KEY (spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE
47); 
48
49--AULAS
50
51CREATE OR REPLACE TABLE classroom (
52classroomId			INT 				NOT NULL AUTO_INCREMENT,
53loudspeakers		BOOLEAN			NOT NULL,
54projector			BOOLEAN			NOT NULL,
55spaceId				INT,
56PRIMARY KEY(classroomId),
57FOREIGN KEY(spaceId) REFERENCES spaces (spaceId) ON DELETE CASCADE ON UPDATE CASCADE 
58);
59
60
61
62--PROFESORES 
63
64
65CREATE OR REPLACE TABLE Teachers (
66teacherId		INT 				NOT NULL AUTO_INCREMENT,
67dni				CHAR(9) 			NOT NULL UNIQUE ,
68firstName		VARCHAR(100) 	NOT NULL,
69surname			VARCHAR(100) 	NOT NULL,
70birthDate		DATE 				NOT NULL,
71email				VARCHAR(258) 	NOT NULL UNIQUE ,
72category			ENUM('Profesor', 'Titular de Universidad', 'Profesor Contratado Doctor', 'Profesor Ayudante Doctor'),
73officeId			INT,
74departmentId	INT,
75PRIMARY KEY(teacherId),
76FOREIGN KEY (officeId) REFERENCES offices(officeId) ON DELETE SET NULL,
77FOREIGN KEY (departmentId) REFERENCES offices(officeId) ON DELETE SET NULL
78);
79
80--Tabla Grupos-Profesores
81
82CREATE OR REPLACE TABLE TeachersGroups(
83teachergroupId		INT NOT NULL AUTO_INCREMENT,
84teachingLoad 		INT NOT NULL,
85teacherId			INT NOT NULL,
86groupId				INT NOT NULL,
87PRIMARY KEY			(teacherGroupId),
88FOREIGN KEY (teacherId) REFERENCES teachers (teacherId),
89FOREIGN KEY (groupId) REFERENCES Groups (groupId)
90);
91--Alumnos 
92
93
94CREATE OR REPLACE TABLE Students(
95studentId		INT 			NOT NULL AUTO_INCREMENT,
96accesMethod		ENUM			('Selectividad', 'Ciclo', 'Mayor', 'Titulado Extranjero'),
97dni				CHAR(9)		 NOT NULL UNIQUE,
98firstName		VARCHAR(100) NOT NULL,
99surname			VARCHAR(100) NOT NULL,
100birthDate		DATE			 NOT NULL,
101email				VARCHAR(250) NOT NULL UNIQUE ,
102PRIMARY KEY(studentId)
103);
104
105--ASIGNATURAS 
106
107
108
109CREATE OR REPLACE TABLE Subjects (
110subjectId 		INT 				NOT NULL AUTO_INCREMENT,
111`name`			VARCHAR(100) 	NOT NULL UNIQUE,
112acronym 			VARCHAR(8) 		NOT NULL UNIQUE,
113credits 			INT				NOT NULL,
114`year` 			INT 				NOT NULL,
115`type` 			ENUM				('Formacion Basica','Optativa','Obligatoria'),
116degreeId 		INT 				NOT NULL,
117PRIMARY KEY 	(subjectId),
118UNIQUE 	(degreeId,`name`),
119UNIQUE 	(degreeId,`acronym`),
120FOREIGN KEY (degreeId) 	REFERENCES Degrees (degreeId) ON DELETE CASCADE ,
121CONSTRAINT negativeSubjectCredits CHECK (credits > 0),
122CONSTRAINT invalidSubjectCourse CHECK (year >= 1 AND year <= 5)
123);
124
125
126 
127--GRUPOS 
128
129CREATE OR REPLACE TABLE Groups(
130groupId		 	INT 			NOT NULL AUTO_INCREMENT,
131`name` 			VARCHAR(30) NOT NULL,
132activity 		ENUM			('Teoria','Laboratorio'),
133`year` 			INT 			NOT NULL,
134subjectId 		INT 			NOT NULL,
135PRIMARY KEY (groupId),
136FOREIGN KEY (subjectId) REFERENCES Subjects (subjectId),
137UNIQUE (`name`, `year`, subjectId),
138CONSTRAINT negativeGroupYear CHECK (year > 0)
139);
140
141--Grupos de Estudiantes
142
143
144CREATE OR REPLACE TABLE GroupsStudents(
145groupStudentId 		INT 		NOT NULL AUTO_INCREMENT,
146groupId 					INT 		NOT NULL,
147studentId 				INT 		NOT NULL,
148PRIMARY KEY (groupStudentId),
149FOREIGN KEY (groupId) REFERENCES Groups  (groupId),
150FOREIGN KEY (studentId) REFERENCES Students (studentId),
151UNIQUE (groupId, studentId)
152);
153
154
155-- CALIFICACIONES 
156CREATE OR REPLACE TABLE Qualification(
157qualificationId 		INT 				NOT NULL AUTO_INCREMENT,
158`value` 					DECIMAL(4,2) 	NOT NULL,
159gradeCall 				INT 				NOT NULL,
160honours 					BOOLEAN 			NOT NULL,
161studentId 				INT 				NOT NULL,
162groupId 					INT 				NOT NULL,
163PRIMARY KEY (qualificationId),
164FOREIGN KEY (studentId) REFERENCES Students (studentId),
165FOREIGN KEY (groupId) REFERENCES Groups (groupId),
166CONSTRAINT invalidGradeValue CHECK (value >= 0 AND value <= 10),
167CONSTRAINT invalidGradeCall CHECK (gradeCall >= 1 AND gradeCall <= 3),
168CONSTRAINT duplicatedCallGrade UNIQUE (gradeCall, studentId, groupId)
169);
170
171
172-- TUTORIAS 
173
174CREATE  OR REPLACE TABLE Tutorials (
175tutorialId		INT					NOT NULL AUTO_INCREMENT,
176`day`				ENUM ('Lunes', 'Martes', 'Miercoles', 'Jueves', 'Viernes'),
177start_hour		TIME					NOT NULL,
178end_hour			TIME					NOT NULL,
179teacherId		INT,
180PRIMARY KEY (tutorialId),
181FOREIGN KEY(teacherId) REFERENCES Teachers  (teacherId) ON DELETE SET NULL
182);
183
184
185
186--CITAS DE TUTORIAS 
187CREATE OR REPLACE TABLE Appointment (
188appointmentId		INT 				NOT NULL AUTO_INCREMENT,
189dateAppointment	DATE 				NOT NULL,
190hourAppointment 	TIME				NOT NULL,
191tutorialId			INT 				NOT NULL,
192studentId			INT 				NOT NULL,
193PRIMARY KEY(appointmentId),
194FOREIGN KEY(tutorialId) REFERENCES Tutorials	 (tutorialId) ON DELETE CASCADE ,
195FOREIGN KEY(studentId) REFERENCES students (studentId) ON DELETE CASCADE,
196UNIQUE (dateAppointment, hourAppointment)
197);