· 9 years ago · Nov 14, 2016, 08:04 AM
1SET AUTOCOMMIT ON;
2
3
4DROP DATABASE IF EXISTS `IU2016`;
5SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
6CREATE DATABASE `IU2016` DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
7USE `IU2016`;
8GRANT USAGE ON *.* TO 'admin'@'localhost';
9DROP USER 'admin'@'localhost';
10CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
11GRANT USAGE ON * . * TO 'admin'@'localhost' IDENTIFIED BY 'admin'
12WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
13GRANT ALL PRIVILEGES ON `IU2016` . * TO 'admin'@'localhost' WITH GRANT OPTION ;
14
15
16DROP TABLE Inscripcion CASCADE CONSTRAINTS;
17DROP TABLE Reserva_Masaje CASCADE CONSTRAINTS;
18DROP TABLE Calendario CASCADE CONSTRAINTS;
19DROP TABLE Servicio CASCADE CONSTRAINTS;
20DROP TABLE Cobro CASCADE CONSTRAINTS;
21DROP TABLE Espacio CASCADE CONSTRAINTS;
22DROP TABLE Reserva_Espacio CASCADE CONSTRAINTS;
23DROP TABLE Horario CASCADE CONSTRAINTS;
24DROP TABLE Caja CASCADE CONSTRAINTS;
25DROP TABLE Cliente_Externo CASCADE CONSTRAINTS;
26DROP TABLE Factura CASCADE CONSTRAINTS;
27DROP TABLE Linea_Factura CASCADE CONSTRAINTS;
28
29/*
30-----------------------------------------------------
31------------------- DUDAS -------------------
32-----------------------------------------------------
33*/
34
35/*
36EVENTO ¿Qué atributos necesitais?
37RESERVA EVENTO
38TRABAJADOR_EVENTO
39
40ASISTENCIA ¿Están bien estas dos?
41CLASE
42JORNADA
43LESION ¿Es una tabla aparte o un atributo de Alumno?
44LOG_LESIONES
45*/
46
47DROP TABLE Controlador CASCADE CONSTRAINTS;
48DROP TABLE Grupo CASCADE CONSTRAINTS;
49DROP TABLE Permisos CASCADE CONSTRAINTS;
50DROP TABLE Trabajador CASCADE CONSTRAINTS;
51DROP TABLE Telefonos_t CASCADE CONSTRAINTS;
52DROP TABLE Alumno CASCADE CONSTRAINTS;
53DROP TABLE Telefonos_a CASCADE CONSTRAINTS;
54DROP TABLE Documento CASCADE CONSTRAINTS;
55DROP TABLE Descuento CASCADE CONSTRAINTS;
56
57/*EN DUDA: */
58DROP TABLE Evento CASCADE CONSTRAINTS;
59DROP TABLE Reserva_Evento CASCADE CONSTRAINTS;
60DROP TABLE Trabajador_Evento CASCADE CONSTRAINTS;
61/*
62DROP TABLE Asistencia CASCADE CONSTRAINTS;
63DROP TABLE Clase CASCADE CONSTRAINTS;
64DROP TABLE Jornada CASCADE CONSTRAINTS;
65DROP TABLE Lesion CASCADE CONSTRAINTS;
66DROP TABLE Log_Lesion CASCADE CONSTRAINTS;
67*/
68
69
70CREATE TABLE Controlador
71(
72 Nombre_Controlador VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
73 Accion VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
74 PRIMARY KEY (Nombre_Controlador,Accion)
75)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
76
77CREATE TABLE Grupo
78(
79 Nombre_Grupo VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
80 PRIMARY KEY (Nombre_Grupo)
81)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
82
83CREATE TABLE Permisos
84(
85 Nombre_Grupo VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
86 Nombre_Controlador VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
87 Accion VARCHAR2(30) COLLATE latin1_spanish_ci NOT NULL,
88 PRIMARY KEY (Nombre_Grupo, Nombre_Controlador, Accion),
89 FOREIGN KEY (Nombre_Controlador, Accion) REFERENCES Controlador (Nombre_Controlador, Accion)
90)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
91
92CREATE TABLE Trabajador
93(
94 Dni_t VARCHAR2(9) NOT NULL,
95 Apellidos_t VARCHAR2(70) COLLATE latin1_spanish_ci NOT NULL,
96 Nombre_t VARCHAR2(50) COLLATE latin1_spanish_ci NOT NULL,
97 Password VARCHAR2(20) NOT NULL,
98 Url_foto VARCHAR2(100) NOT NULL,
99 Direccion VARCHAR2(100) COLLATE latin1_spanish_ci NOT NULL,
100 Email VARCHAR2(50) NOT NULL,
101 Fecha_nacimiento_t DATE NOT NULL,
102 Observaciones VARCHAR2(500) COLLATE latin1_spanish_ci,
103 Numero_cuenta NUMBER(20) NOT NULL,
104 Horas_extra VARCHAR2(200) COLLATE latin1_spanish_ci,
105 Nombre_Grupo VARCHAR2(30) COLLATE latin1_spanish_ci,
106 Tipo_empleado ENUM ('administrador','secretario','monitor','fisioterapeuta','cafeterÃa','limpieza', 'otros') NOT NULL,
107 Externo BOOLEAN NOT NULL DEFAULT(0)),
108 PRIMARY KEY (Dni_t),
109 FOREIGN KEY (Nombre_Grupo) REFERENCES Grupo (Nombre_Grupo)
110)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
111
112CREATE TABLE Telefonos_t
113(
114 Dni_t VARCHAR2(9) NOT NULL,
115 Telefono_t NUMBER(9) NOT NULL,
116 PRIMARY KEY (Dni_t,Telefono_t),
117 FOREIGN KEY (Dni_t) REFERENCES Trabajador (Dni_t)
118)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
119
120CREATE TABLE Alumno
121(
122 Dni_a VARCHAR2(9) NOT NULL,
123 Apellidos_a VARCHAR2(70) COLLATE latin1_spanish_ci NOT NULL,
124 Nombre_a VARCHAR2(50) COLLATE latin1_spanish_ci NOT NULL,
125 Direccion VARCHAR2(100) COLLATE latin1_spanish_ci,
126 Email VARCHAR2(50) NOT NULL,
127 Fecha_nacimiento_a DATE NOT NULL,
128 Observaciones VARCHAR2(500) COLLATE latin1_spanish_ci,
129 Profesion VARCHAR2(50) COLLATE latin1_spanish_ci,
130 PRIMARY KEY (Dni_a),
131)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
132
133CREATE TABLE Telefonos_a
134(
135 Dni_a VARCHAR2(9) NOT NULL,
136 Telefono_a NUMBER(9) NOT NULL,
137 PRIMARY KEY (Dni_a,Telefono_a),
138 FOREIGN KEY (Dni_a) REFERENCES Alumno (Dni_a)
139)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
140
141CREATE TABLE Evento
142(
143 Id_evento NUMBER(20) NOT NULL,
144 Descripcion VARCHAR2(500) COLLATE latin1_spanish_ci NOT NULL,
145 PRIMARY KEY (Id_evento)
146)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
147
148CREATE TABLE Reserva_Evento
149(
150 Id_evento NUMBER(20) NOT NULL,
151 Id_reserva NUMBER(20) NOT NULL,
152 PRIMARY KEY (Id_evento,Id_reserva),
153 FOREIGN KEY (Id_evento) REFERENCES Evento (Id_evento)
154)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
155
156CREATE TABLE Trabajador_Evento
157(
158 Dni_t VARCHAR2(9) NOT NULL,
159 Id_evento NUMBER(20) NOT NULL,
160 PRIMARY KEY (Dni_t,Id_evento),
161 FOREIGN KEY(Dni_t) REFERENCES Trabajor (Dni_t),FOREIGN KEY (Id_evento) REFERENCES Evento (Id_evento)
162)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
163
164CREATE TABLE Documento
165(
166 Dni_a VARCHAR2(9) NOT NULL,
167 Tipo_Doc VARCHAR2(20) NOT NULL,
168 Fecha_Doc DATE NOT NULL,
169 Url_Doc VARCHAR2(100) NOT NULL,
170 PRIMARY KEY (Dni_a,Tipo_Doc,Fecha_Doc),
171 FOREIGN KEY (Dni_a) REFERENCES Alumno (Dni_a)
172)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
173
174CREATE TABLE Descuento
175(
176 Id_descuento VARCHAR2(20) NOT NULL,
177 Requisitos VARCHAR2(150) COLLATE latin1_spanish_ci NOT NULL,
178 Porcentaje NUMBER(4) NOT NULL,
179 PRIMARY KEY (Id_descuento)
180)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
181/*
182CREATE TABLE Jornada
183(
184 Dni_t VARCHAR2(9) NOT NULL,
185 Hora_entrada TIME NOT NULL,
186 Hora_salida TIME NOT NULL,
187 PRIMARY KEY (Dni_t,Hora_entrada,Hora_salida),
188 FOREIGN KEY (Dni_t) REFERENCES TRABAJADOR (Dni_t)
189)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
190
191CREATE TABLE Asistencia
192(
193 Dni_a VARCHAR2(9) NOT NULL,
194 Fecha_asistencia DATE NOT NULL,
195 PRIMARY KEY (),
196 FOREIGN KEY ()
197)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
198
199CREATE TABLE Clase
200(
201
202 PRIMARY KEY (),
203 FOREIGN KEY ()
204)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
205
206CREATE TABLE Lesion
207(
208 Dni_a VARCHAR2(9) NOT NULL,
209
210 PRIMARY KEY (),
211 FOREIGN KEY (Dni_a) REFERENCES Alumno (Dni_a)
212)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
213
214CREATE TABLE Log_Lesion
215(
216
217 PRIMARY KEY (),
218 FOREIGN KEY ()
219)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
220*/
221CREATE TABLE Inscripcion
222(
223 Dni_Alumno VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY REFERENCES Alumno,
224 Id_Actividad VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY REFERENCES Actividad,
225 Id_Cobro VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY REFERENCES Cobro,
226 Fecha DATE NOT NULL
227
228)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
229
230CREATE TABLE Reserva_Masaje
231(
232 Id_Reserva_Masaje VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
233 Dni_Alumno VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Alumno,
234 Dni_Trabajador VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Trabajador,
235 Tipo_Masaje VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL,
236 Hora_Inicio VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
237 Hora_Fin VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
238 Fecha DATE NOT NULL
239
240)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
241
242CREATE TABLE Calendario
243(
244 Id_Calendario VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
245 Fecha_inicio DATE NOT NULL,
246 Fecha_Fin DATE NOT NULL,
247 Hora_Inicio VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
248 Hora_Fin VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL
249
250)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
251
252CREATE TABLE Servicio
253(
254 Id_Servicio VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
255 Id_Trabajador VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Trabajador,
256 Id_Cliente VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Cliente_Externo,
257 Nombre VARCHAR (30) COLLATE latin1_spanish_ci NOT NULL
258
259)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
260
261CREATE TABLE Cobro
262(
263 Id_Cobro VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
264 Dni_Alumno VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Alumno,
265 Importe SMALLINT COLLATE latin1_spanish_ci NOT NULL,
266 Fecha_Cobro DATE NOT NULL,
267 Fecha_Confirmacion DATE NOT NULL,
268 Tipo VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL /* Duda */
269
270)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
271
272CREATE TABLE Espacio
273(
274 Id_Espacio VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
275 Nombre VARCHAR(30) COLLATE latin1_spanish_ci NOT NULL
276
277)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
278
279CREATE TABLE Reserva_Espacio
280(
281 Id_Reserva_Espacio VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
282 Id_Espacio VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Espacio,
283 Dni_Alumno VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Alumno,
284 Id_Trabajador VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Trabajador,
285 Hora_Inicio VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
286 Hora_Fin VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
287 Fecha DATE NOT NULL,
288 Evento VARCHAR(20) COLLATE latin1_spanish_ci NOT NULL
289
290)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
291
292CREATE TABLE Horario
293(
294 Id_Horario VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
295 Id_Calendario VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL REFERENCES Calendario,
296 Hora_Inicio VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL,
297 Hora_Fin VARCHAR(5) COLLATE latin1_spanish_ci NOT NULL
298
299)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
300
301CREATE TABLE Caja
302(
303 Id_Caja VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
304 Fecha DATE NOT NULL,
305 Tipo VARCHAR(20) COLLATE latin1_spanish_ci NOT NULL /* Misma duda */
306 Importe FLOAT NOT NULL
307
308)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
309
310CREATE TABLE Cliente_Externo
311(
312 Id_Cliente VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
313 Nombre VARCHAR(20) COLLATE latin1_spanish_ci NOT NULL,
314 Dni VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL UNIQUE,
315 Tlf SMALLINT NOT NULL,
316 Email VARCHAR(20) COLLATE latin1_spanish_ci NOT NULL,
317 Direccion VARCHAR(40) COLLATE latin1_spanish_ci NOT NULL
318
319)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
320
321CREATE TABLE Linea_Factura
322(
323 Id_Factura VARCHAR(10) COLLATE latin1_spanish_ci NOT NULL PRIMARY KEY,
324 Cantidad SMALLINT NOT NULL,
325 IMPORTE FLOAT NOT NULL
326
327)ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;