VccudUxX

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
159-- drop procedure EJ2_1;
160Delimiter $$
161create procedure EJ2_1 (in O int, in D int, out Linea varchar(30))
162begin
163select linea_vuelo into Linea
164from Vuelo as V join programavuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
165where valor_CC = (select min(V2.valor_CC) from Vuelo as V2 join programavuelo
166 as PV2 on (V2.nro_vuelo = PV2.nro_vuelo) where id_aeropuerto_salida = 1
167 and id_aeropuerto_llegada = 3);
168end $$
169Delimiter ;
170
171-- call EJ2_1(1,3,@hhh);
172
173-- select @hhh;
174
175-- drop procedure EJ2_2;
176Delimiter $$
177create procedure EJ2_2 (in aeropuerto_salida int, in aeropuerto_llegada int, in tvuelo time, in valor int)
178begin
179select P.id_programa_vuelo 
180from ProgramaVuelo as P
181where P.id_aeropuerto_salida = aeropuerto_salida 
182and P.id_aeropuerto_llegada = aeropuerto_llegada
183and P.tiempo_vuelo < tvuelo 
184and P.nro_vuelo in (select PA.nro_vueloFK from pasaje as PA where PA.monto <= valor);
185end $$
186Delimiter ;
187
188-- CALL EJ2_2(001,003, 4, 3000);
189
190-- drop procedure EJ2_3;
191
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
202-- CALL EJ2_3(001,003);
203Delimiter $$
204create procedure EJ2_4 ()
205begin
206select avg(sueldo_bruto), linea
207 from Personal as P
208 group by P.linea  having avg(sueldo_bruto)>=all 
209 (
210 select avg(sueldo_bruto) 
211 from Personal as P2
212 group by P2.linea
213 );
214
215end $$
216Delimiter;
217
218call EJ2_4();
219
220
221--               TRIGGERS
222
223Delimiter $$
224create trigger EJ3_1 After Insert on Pasaje for each row
225begin
226if(not exists (select * from Pasaje as P where P.id_pasaje = new.id_pasaje and P.estado like 'activo')) then
227	delete from Pasaje where Pasaje.id_pasaje = new.id_pasaje;
228end if;
229end $$
230Delimiter ;
231
232Delimiter $$
233create trigger EJ3_2 After Update on Pasaje for each row
234begin
235if((select count(P.clase) from Pasaje as P group by (P.clase) having P.clase = new.clase and P.nro_vueloFK = new.nro_vueloFK) > (
236
237	case new.clase
238    
239		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)
240     
241		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)
242     
243		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)
244
245	end
246    
247)) then delete from Pasaje where new.id_pasaje = id_pasaje;
248
249end if;
250
251end $$
252
253Delimiter ;
254
255--               VISTAS
256
257create or replace view EJ4_1 as 
258select avg(V.valor_CB)
259from Vuelo as V join ProgramaVuelo as PV on (V.nro_vuelo = PV.nro_vuelo)
260where PV.id_programa_vuelo not in (
261select E.id_programa_vueloFK
262from Escalas as E
263);
264
265create or replace view EJ4_2 as 
266select M.capacidad_CA, M.capacidad_CB, M.capacidad_CC
267from 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)
268where PV.id_aeropuerto_salida = 006 and PV.id_aeropuerto_llegada = 007;
269
270--               USUARIOS
271
272create user 'user1'
273identified by 'Usuario 1';
274
275grant select, insert on sistemavuelos.pasaje to user1;
276grant select, insert on sistemavuelos.vuelo to user1;
277
278
279create user 'user2'
280identified by 'Usuario 2';
281
282grant insert, update on sistemavuelos.EJ4_1 to user2;
283grant insert, update on sistemavuelos.EJ4_2 to user2;
284
285create user 'user3'
286identified by 'Usuario 3';
287
288grant create on sistemavuelos.* to user3;
289
290create user 'admin'
291identified by 'Administrador';
292
293grant all on *.* to admin;
294
295
296create user 'user4'
297identified by 'Usuario 4';
298
299grant update (peso_max, distancia_min_aterrizaje) on sistemavuelos.modelo to user4;