bpxaJku5

1drop database sistemavuelos;
2
3create database SistemaVuelos;
4
5use SistemaVuelos;
6
7--          TABLAS
8
9create table Aeropuertos(
10codigo_aeropuerto int primary key,
11nombre varchar(30),
12ciudad varchar(50),
13pais varchar(15)
14);
15
16create table Modelo(
17id_modelo int primary key,
18capacidad_CA int,
19capacidad_CB int,
20capacidad_CC int,
21fabricante varchar(15),
22envergadura int,
23peso_max int,
24distancia_min_aterrizaje int
25);
26
27create table Personal(
28id_personal int primary key,
29linea varchar(15),
30nombre varchar(15),
31apellido varchar(15),
32nacim date,
33nacionalidad varchar(25),
34pasaporte int,
35sueldo_bruto int
36);
37
38create table Vuelo(
39nro_vuelo int primary key,
40linea_vuelo varchar(15),
41id_modelo int,
42id_personalFK int,
43valor_CA decimal(10,2),
44valor_CB decimal(10,2),
45valor_CC decimal(10,2),
46fecha date,
47hora time,
48foreign key (id_modelo) references Modelo(id_modelo),
49foreign key (id_personalFK) references Personal(id_personal));
50
51create table vueloPersonal(
52id_vueloPersonal int primary key,
53id_personalFK int,
54nro_vueloFK int,
55cargo varchar(20),
56foreign key (id_personalFK) references Personal (id_personal),
57foreign key (nro_vueloFK) references Vuelo (nro_vuelo)
58);
59create table ProgramaVuelo(
60id_programa_vuelo int primary key,
61nro_vuelo int,
62id_aeropuerto_salida int,
63id_aeropuerto_llegada int,
64estado varchar(15),
65tiempo_vuelo time,
66foreign key (nro_vuelo) references Vuelo(nro_vuelo)
67);
68
69create table Escalas(
70id_escala int primary key,
71id_programa_vueloFK int,
72id_aeropuertoFK int,
73duracion_mins time,
74tipo varchar(10),
75foreign key (id_programa_vueloFK) references ProgramaVuelo(id_programa_vuelo),
76foreign key (id_aeropuertoFK) references Aeropuertos(codigo_aeropuerto)
77);
78
79create table Pasaje(
80id_pasaje int primary key,
81nro_vueloFK int,
82estado varchar(10),
83clase varchar(5),
84dni int,
85monto int,
86foreign key(nro_vueloFK) references Vuelo(nro_vuelo)
87);
88
89
90--             DATOS
91
92insert into Aeropuertos values (001,'Sao Paulo','Sao Paulo','Brasil');
93insert into Aeropuertos values (002,'Ezeiza','Buenos Aires','Argentina');
94insert into Aeropuertos values (003,'Lima','Lima','Peru');
95insert into Aeropuertos values (004,'Santiago','Santiago de Chile','Chile');
96insert into Aeropuertos values (005,'Bogotá','Bogotá','Colombia');
97insert into Aeropuertos values (006,'Berlín','Berlín','Alemania');
98insert into Aeropuertos values (007,'Moscú','Moscú','Rusia');
99
100insert into Modelo values (1,10,30,60,'Boeing',40,300000,200);
101insert into Modelo values (2,30,50,120,'Airbus',30,340000,300);
102insert into Modelo values (3,50,150,300,'Boeing',60,1200000,600);
103insert into Modelo values (4,10,0,0,'Boeing',20,150000,100);
104insert into Modelo values (5,30,70,200,'Airbus',30,300000,200);
105
106insert into Personal values (1,'Latam','Nazareno','Quiroga','1999/09/04','argentino',1001001,90000);
107insert into Personal values (2,'Latam','Hola','Chau','1994/09/04','argentino',1001002,10000);
108insert into Personal values (3,'Aerolineas','Julio','Quiroga','1998/09/04','argentino',1001003,100000);
109insert into Personal values (4,'Aerolineas','Pan','Frances','1990/09/04','argentino',1001004,20000);
110insert into Personal values (5,'Tam','Piso','Plastificado','1994/09/04','argentino',1001005,30000);
111insert into Personal values (6,'Tam','Goku','China','1996/09/04','argentino',1001006,40000);
112insert into Personal values (7,'Air France','Armando','Estebanquito','1994/09/08','argentino',1001007,10000);
113insert into Personal values (8,'Air France','Esteban','Balcarcel','1999/03/08','argentino',1001008,30000);
114insert into Personal values (9,'Emirates','Locas','Benchinull','1997/07/12','argentino',1001009,120000);
115insert into Personal values (10,'Emirates','Lucas','Benchinol','1992/02/12','argentino',1001010,10000);
116
117insert into Vuelo values (000,'Latam',1,1,5000,3000,1000,'2017/04/14','07:30:00');
118insert into Vuelo values (001,'Aerolineas',2,2,3000,1000,500,'2017/04/20','22:30:00');
119insert into Vuelo values (002,'Tam',3,3,4000,2000,1000,'2017/04/22','10:00:00');
120insert into Vuelo values (003,'Air France',4,4,7000,5000,3000,'2017/05/01','03:25:00');
121insert into Vuelo values (004,'Emirates',5,5,12000,10000,8000,'2017/06/15','18:00:00');
122insert into Vuelo values (005,'Emirates',5,5,12000,10000,8000,'2017/06/15','18:00:00');
123insert into Vuelo values (006,'Etihad',5,5,12000,10000,8000,'2017/06/15','18:00:00');
124
125insert into ProgramaVuelo values (01,000,003,005,'aprobado','02:15:00');
126insert into ProgramaVuelo values (02,001,004,001,'rechazado','04:05:00');
127insert into ProgramaVuelo values (03,002,002,005,'rechazado','05:20:00');
128insert into ProgramaVuelo values (04,003,004,002,'aprobado','02:30:00');
129insert into ProgramaVuelo values (05,004,001,003,'aprobado','01:30:00');
130insert into ProgramaVuelo values (06,005,001,003,'aprobado','01:30:00');
131insert into ProgramaVuelo values (07,006,006,007,'aprobado','03:45:00');
132
133insert into Escalas values (111,01,001,'00:30:00','tecnicas');
134insert into Escalas values (112,02,003,'00:15:00','tecnicas');
135insert into Escalas values (113,03,004,'01:00:00','tecnicas');
136insert into Escalas values (114,04,005,'00:20:00','tecnicas');
137insert into Escalas values (115,05,002,'00:25:00','tecnicas');
138
139insert into Pasaje values (11,000,'activo','A',42147744,12000);
140insert into Pasaje values (12,001,'cancelado','C',44643636,3000);
141insert into Pasaje values (13,002,'activo','B',42183524,10000);
142insert into Pasaje values (14,003,'cancelado','B',43897498,5000);
143insert into Pasaje values (15,004,'activo','A',44832774,4000);
144
145insert into vuelopersonal values (1, 1, 1, "Piloto");
146insert into vuelopersonal values (2, 2, 1, "Copiloto");
147insert into vuelopersonal values (3, 4, 2, "Piloto");
148insert into vuelopersonal values (4, 5, 2, "Copiloto");
149insert into vuelopersonal values (5, 2, 3, "Piloto");
150insert into vuelopersonal values (6, 3, 3, "Copiloto");
151insert into vuelopersonal values (7, 1, 4, "Piloto");
152insert into vuelopersonal values (8, 3, 4, "Copiloto");
153insert into vuelopersonal values (9, 4, 5, "Piloto");
154insert into vuelopersonal values (10, 5, 5, "Copiloto");
155
156
157
158--               PROCEDIMIENTOS ALMACENADOS
159drop procedure EJ2_1;
160Delimiter $$
161create procedure EJ2_1 (in O varchar(30), in D varchar(30), out Linea varchar(30))
162begin
163declare aux decimal(10,2);
164select V.linea_vuelo into Linea
165from Vuelo as V join programavuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
166where V.valor_CB <= aux and PV.id_aeropuerto_salida in
167	(select A.codigo_aeropuerto from Aeropuertos as A where O = A.nombre)
168and  PV.id_aeropuerto_llegada in
169	(select A2.codigo_aeropuerto from Aeropuertos as A2 where D = A2.nombre);
170end $$
171Delimiter ;
172
173call EJ2_1(001,003,@hhh);
174
175select @hhh;
176
177Delimiter $$
178create procedure EJ2_2 (in aeropuerto_salida int, in aeropuerto_llegada int, in tvuelo time, in valor int)
179begin
180select P.id_programa_vuelo 
181from ProgramaVuelo as P
182where P.id_aeropuerto_salida = aeropuerto_salida 
183and P.id_aeropuerto_llegada = aeropuerto_llegada
184and P.tiempo_vuelo > tvuelo 
185and P.nro_vuelo in (select PA.nro_vueloFK from pasaje as PA where PA.monto >= valor);
186end $$
187Delimiter ;
188
189CALL EJ2_2(001,003, 4, 3000);
190
191drop procedure EJ2_3;
192Delimiter $$
193create procedure EJ2_3 (in aeropuerto_salida int, in aeropuerto_llegada int)
194begin
195select *
196from VueloPersonal as VP join Vuelo as V on (VP.nro_vueloFK = V.nro_vuelo) 
197join ProgramaVuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
198where id_aeropuerto_salida = aeropuerto_salida and id_aeropuerto_llegada = aeropuerto_llegada;
199end $$
200Delimiter ;
201
202CALL EJ2_3(001,003);
203
204Delimiter $$
205create procedure EJ2_4 ()
206begin
207select avg(sueldo_bruto), Linea
208 from Personal as P
209 group by P.linea  having avg(sueldo_bruto)>=all 
210 (
211 select avg(sueldo_bruto) 
212 from Personal as P2
213 group by P2.linea
214 );
215
216end $$
217Delimiter;
218
219call EJ2_4();
220
221
222--               TRIGGERS
223
224Delimiter $$
225create trigger EJ3_1 After Insert on Pasaje for each row
226begin
227if(not exists (select * from Pasaje as P where P.id_pasaje = new.id_pasaje and P.estado like 'activo')) then
228	delete from Pasaje where Pasaje.id_pasaje = new.id_pasaje;
229end if;
230end $$
231Delimiter ;
232
233Delimiter $$
234create trigger EJ3_2 After Update on Pasaje for each row
235begin
236if((select count(P.clase) from Pasaje as P group by (P.clase) having P.clase = new.clase and P.nro_vueloFK = new.nro_vueloFK) > (
237
238	case new.clase
239    
240		when 'A' then (select M.capacidad_CA from Modelo as M join Vuelo as V on (M.id_modelo = V.id_modelo) where new.nro_vueloFK = V.nro_vuelo)
241     
242		when 'B' then (select M.capacidad_CA from Modelo as M join Vuelo as V on (M.id_modelo = V.id_modelo) where new.nro_vueloFK = V.nro_vuelo)
243     
244		when 'C' then (select M.capacidad_CA from Modelo as M join Vuelo as V on (M.id_modelo = V.id_modelo) where new.nro_vueloFK = V.nro_vuelo)
245
246	end
247    
248)) then delete from Pasaje where new.id_pasaje = id_pasaje;
249
250end if;
251
252end $$
253
254Delimiter ;
255
256--               VISTAS
257
258create or replace view EJ4_1 as 
259select avg(V.valor_CB)
260from Vuelo as V join ProgramaVuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
261where PV.id_programa_vuelo not in (
262select E.id_programa_vueloFK
263from Escalas as E
264);
265
266create or replace view EJ4_2 as 
267select M.capacidad_CA, M.capacidad_CB, M.capacidad_CC
268from Modelo as M join Vuelo as V on(M.id_modelo = V.id_modelo) join ProgramaVuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
269where PV.id_aeropuerto_salida = 006 and PV.id_aeropuerto_llegada = 007;
270
271create or replace view EJ4_3 as 
272select avg(P.count(id_pasaje))
273from Pasaje as P join Vuelo as V;
274
275create or replace view EJ4_4 as 
276select id_aeropuerto_salida, id_aeropuerto_llegada)
277from ProgramaVuelo as PV inner join
278(select id_aeropuerto_salida, max(ProgamaVuelo.id_aeropuerto_llegada) as id_aeropuerto_llegada
279from ProgramaVuelo
280group by id_aeropuerto_salida as maxLlegada
281on ProgramaVuelo.id_aeropuerto_salida = maxLlegada.id_aeropuerto_salida 
282AND ProgramaVuelo.id_aeropuerto_llegada= maxLlegada.id_aeropuerto_llegada);
283
284--               USUARIOS
285
286create user 'user1'
287identified by 'Usuario 1';
288
289grant select, insert on sistemavuelos.pasaje to user1;
290grant select, insert on sistemavuelos.vuelo to user1;
291
292
293create user 'user2'
294identified by 'Usuario 2';
295
296grant insert, update on sistemavuelos.EJ4_1 to user2;
297grant insert, update on sistemavuelos.EJ4_2 to user2;
298
299create user 'user3'
300identified by 'Usuario 3';
301
302grant on procedure sistemavuelos.EJ2_1 to user3;
303grant on procedure sistemavuelos.EJ2_2 to user3;
304grant on procedure sistemavuelos.EJ2_3 to user3;
305grant on procedure sistemavuelos.EJ2_4 to user3;
306
307create user 'admin'
308identified by 'Administrador';
309
310grant all on *.* to admin;
311
312
313create user 'user5'
314identified by 'Usuario 5';
315
316grant update (sistemavuelos.modelo.peso_max, sistemavuelos.modelo.distancia_min_aterrizaje) on sistemavuelos.modelo to user5; -- poner los campos entre los ()