· 4 years ago · Dec 27, 2020, 04:58 PM
1DROP TABLE if EXISTS appointments;
2DROP TABLE if EXISTS tutorings;
3DROP TABLE if EXISTS impart;
4DROP TABLE if EXISTS grades;
5DROP TABLE if EXISTS teachersgroups;
6DROP TABLE if EXISTS studentsgroups;
7DROP TABLE if EXISTS groups;
8DROP TABLE if EXISTS subjects;
9DROP TABLE if EXISTS teachers;
10DROP TABLE if EXISTS students;
11DROP TABLE if EXISTS offices;
12DROP TABLE if EXISTS classrooms;
13DROP TABLE if EXISTS departments;
14DROP TABLE if EXISTS `degrees`;
15DROP TABLE if EXISTS people;
16
17
18
19
20
21
22
23/* PERSONAS */
24
25CREATE TABLE if NOT EXISTS people(
26 dni CHAR(9) NOT NULL,
27 firstname VARCHAR(50) NOT NULL,
28 surname VARCHAR(50) NOT NULL,
29 birthdate DATE NOT NULL,
30 email VARCHAR(150) NOT NULL UNIQUE,
31
32 PRIMARY KEY (dni)
33 );
34
35/* GRADOS */
36
37CREATE TABLE if NOT EXISTS `degrees` (
38 degreeID INT NOT NULL AUTO_INCREMENT,
39 `name` VARCHAR(80) NOT NULL UNIQUE,
40 years INT NOT NULL,
41
42 PRIMARY KEY (degreeID)
43 );
44
45/* DEPARTAMENTOS */
46
47CREATE TABLE if NOT EXISTS departments(
48 departmentID INT NOT NULL AUTO_INCREMENT,
49 `name` VARCHAR(100) NOT NULL UNIQUE,
50
51 PRIMARY KEY (departmentID)
52 );
53
54
55/* AULAS */
56
57CREATE TABLE if NOT EXISTS classrooms (
58 classroomID INT NOT NULL AUTO_INCREMENT,
59 `name` VARCHAR(100) NOT NULL UNIQUE,
60 `floor` INT NOT NULL,
61 capacity INT NOT NULL,
62 hasLoudspeaker BOOLEAN NOT NULL,
63 hasProjector BOOLEAN NOT NULL,
64 activity VARCHAR (50) NOT NULL,
65
66 PRIMARY KEY (classroomID),
67 CONSTRAINT invalidClassroomActivity CHECK (activity IN ('Teoria',
68 'Laboratorio'))
69 );
70
71
72/* DESPACHOS */
73
74CREATE TABLE if NOT EXISTS offices (
75 officeID INT NOT NULL AUTO_INCREMENT,
76 `name` VARCHAR(100) NOT NULL UNIQUE,
77 `floor` INT NOT NULL,
78 capacity INT NOT NULL,
79
80 PRIMARY KEY (officeID)
81 );
82
83/* ALUMNOS */
84
85CREATE TABLE if NOT EXISTS students (
86 studentID INT NOT NULL AUTO_INCREMENT,
87 dni CHAR(9) NOT NULL UNIQUE,
88 accessMethod VARCHAR(30) NOT NULL,
89
90 PRIMARY KEY (studentID),
91 FOREIGN KEY (dni) REFERENCES people (dni) ON DELETE CASCADE,
92 CONSTRAINT invalidStudentAccessMethod CHECK (accessMethod IN ('Selectividad',
93 'Ciclo',
94 'Mayor',
95 'Titulado Extrangero'))
96 );
97
98/* PORFESORES */
99
100CREATE TABLE if NOT EXISTS teachers (
101 teacherID INT NOT NULL AUTO_INCREMENT,
102 category VARCHAR(40) NOT NULL,
103 departmentID INT NOT NULL,
104 officeID INT NOT NULL,
105 dni CHAR(9) NOT NULL UNIQUE,
106
107 PRIMARY KEY (teacherID),
108 FOREIGN KEY (departmentID) REFERENCES departments (departmentID),
109 FOREIGN KEY (officeID) REFERENCES offices (officeID),
110 FOREIGN KEY (dni) REFERENCES people (dni) ON DELETE CASCADE,
111 CONSTRAINT invalidTeacherCategory CHECK (category IN ('CU',
112 'TU',
113 'PCD',
114 'PAD'))
115 );
116
117/* ASIGNATURAS */
118
119CREATE TABLE if NOT EXISTS subjects (
120 subjectID INT NOT NULL AUTO_INCREMENT,
121 `name` VARCHAR(100) NOT NULL UNIQUE,
122 acronym VARCHAR(8) NOT NULL UNIQUE,
123 credit INT NOT NULL,
124 course INT NOT NULL,
125 `type` VARCHAR(20) NOT NULL,
126 departmentID INT NOT NULL,
127 degreeID INT NOT NULL,
128
129 PRIMARY KEY (subjectID),
130 FOREIGN KEY (departmentID) REFERENCES departments (departmentID) ON DELETE CASCADE,
131 FOREIGN KEY (degreeID) REFERENCES `degrees` (degreeID),
132 CONSTRAINT invalidSubjectType CHECK (`type` IN ('Formacion Basica',
133 'Optativa',
134 'Obligatoria'))
135 );
136
137/* GRUPOS */
138
139CREATE TABLE if NOT EXISTS groups (
140 groupID INT NOT NULL AUTO_INCREMENT,
141 `name` VARCHAR(30) NOT NULL UNIQUE,
142 activity VARCHAR (50) NOT NULL,
143 academicYear INT NOT NULL,
144 subjectID INT NOT NULL,
145 classroomID INT NOT NULL,
146
147 PRIMARY KEY (groupID),
148 FOREIGN KEY (subjectID) REFERENCES subjects (subjectID),
149 FOREIGN KEY (classroomID) REFERENCES classrooms (classroomID),
150 CONSTRAINT invalidGroupActivity CHECK (activity IN ('Teoria',
151 'Laboratorio'))
152 );
153
154/* ALUMNOSGRUPOS */
155
156CREATE TABLE if NOT EXISTS studentsgroups (
157 studentgroupID INT NOT NULL AUTO_INCREMENT,
158 studentID INT NOT NULL,
159 groupID INT NOT NULL,
160
161 PRIMARY KEY (studentgroupID),
162 UNIQUE (studentID, groupID),
163 FOREIGN KEY (studentID) REFERENCES students (studentID) ON DELETE CASCADE,
164 FOREIGN KEY (groupID) REFERENCES groups (groupID) ON DELETE CASCADE
165 );
166
167/* PROFESORESGRUPOS */
168
169CREATE TABLE if NOT EXISTS teachersgroups (
170 teachergroupID INT NOT NULL AUTO_INCREMENT,
171 teacherID INT NOT NULL,
172 groupID INT NOT NULL,
173
174 PRIMARY KEY (teachergroupID),
175 UNIQUE (teacherID, groupID),
176 FOREIGN KEY (teacherID) REFERENCES teachers (teacherID) ON DELETE CASCADE,
177 FOREIGN KEY (groupID) REFERENCES groups (groupID) ON DELETE CASCADE
178 );
179
180
181/* NOTAS */
182
183CREATE TABLE if NOT EXISTS grades(
184 gradeId INT NOT NULL AUTO_INCREMENT,
185 `value` DECIMAL(4,2) NOT NULL,
186 gradeCall VARCHAR(15) NOT NULL,
187 withHonours BOOLEAN NOT NULL,
188 studentId INT NOT NULL,
189 groupId INT NOT NULL,
190
191 PRIMARY KEY (gradeId),
192 UNIQUE (studentId, groupID),
193 FOREIGN KEY (studentId) REFERENCES students (studentId),
194 FOREIGN KEY (groupId) REFERENCES groups (groupId),
195 CONSTRAINT invalidGradeValue CHECK (value >= 0 AND value <= 10),
196 CONSTRAINT invalidGradesGradeCall CHECK (gradeCall IN ('Primera',
197 'Segunda',
198 'Tercera',
199 'Extraordinaria'))
200 );
201
202/* IMPARTE */
203
204CREATE TABLE if NOT EXISTS impart (
205 impartID INT NOT NULL AUTO_INCREMENT,
206 credit INT NOT NULL,
207 teachergroupID INT NOT NULL UNIQUE,
208
209 PRIMARY KEY (impartID),
210 FOREIGN KEY (teachergroupID) REFERENCES teachersgroups (teachergroupID) ON DELETE CASCADE
211 );
212
213/* TUTORIAS */
214
215CREATE TABLE if NOT EXISTS tutorings (
216 tutoringID INT NOT NULL AUTO_INCREMENT,
217 dayWeek VARCHAR(10) NOT NULL,
218 startHour TIME NOT NULL,
219 endHour TIME NOT NULL,
220 teacherID INT NOT NULL,
221
222 PRIMARY KEY (tutoringID),
223 FOREIGN KEY (teacherID) REFERENCES teachers (teacherID) ON DELETE CASCADE
224 );
225
226/* CITAS */
227
228CREATE TABLE if NOT EXISTS appointments (
229 appointmentID INT NOT NULL AUTO_INCREMENT,
230 `date` DATE NOT NULL,
231 `hour` TIME NOT NULL,
232 tutoringID INT NOT NULL,
233 studentId INT NOT NULL,
234
235 PRIMARY KEY (appointmentID),
236 FOREIGN KEY (studentId) REFERENCES students (studentId) ON DELETE CASCADE,
237 FOREIGN KEY (tutoringID) REFERENCES tutorings (tutoringID) ON DELETE CASCADE
238 );