· 7 years ago · Nov 26, 2018, 12:22 AM
1drop database if exists AirFly;
2create database if not exists AirFly;
3use AirFly;
4
5
6create table if not exists asociacion
7(
8 nombre varchar(50),
9
10 primary key(nombre)
11);
12
13create table if not exists aerolinea
14(
15 cod_aerolinea int auto_increment,
16 nombre varchar(50),
17
18 asociacion_nombre varchar(50),
19
20 primary key(cod_aerolinea),
21 foreign key(asociacion_nombre) references asociacion(nombre)
22);
23
24create table if not exists cliente
25(
26 rut int auto_increment,
27 nombre varchar(50),
28 apellido varchar(50),
29 antecedentes varchar(100),
30
31 aerolinea_cod int not null,
32
33 primary key(rut),
34 foreign key(aerolinea_cod) references aerolinea(cod_aerolinea)
35);
36
37create table if not exists registro
38(
39 num_vuelo int,
40 puntos_millas int,
41
42 cliente_rut int not null,
43 aerolinea_cod int not null,
44
45 foreign key(cliente_rut) references cliente(rut),
46 foreign key(aerolinea_cod) references aerolinea(cod_aerolinea)
47);
48
49create table if not exists avion
50(
51 cod_avion int auto_increment,
52 capacidad int not null,
53
54 aerolinea_cod int not null,
55
56 primary key(cod_avion),
57 foreign key(aerolinea_cod) references aerolinea(cod_aerolinea)
58);
59
60create table if not exists pais
61(
62 nombre varchar(50),
63
64 primary key(nombre)
65);
66
67create table if not exists ciudad
68(
69 nombre varchar(50),
70
71 pais_nombre varchar(50),
72
73 primary key(nombre),
74 foreign key(pais_nombre) references pais(nombre)
75);
76
77create table if not exists pasaje
78(
79 num_reserva int auto_increment,
80 precio int not null,
81 num_asiento int not null,
82 fecha varchar(8),
83 online_presencial bool,
84 ejecutivo_turista bool,
85
86 puerta_embarque int not null,
87
88 cliente_rut int default null,
89 pais varchar(50),
90
91 primary key(num_reserva),
92 foreign key(cliente_rut) references cliente(rut),
93 foreign key(pais) references pais(nombre)
94);
95
96create table if not exists vuelo
97(
98 cod_vuelo int auto_increment,
99 precio int not null,
100 fecha varchar (8),
101 hora_salida varchar(5),
102 hora_llegada varchar(5),
103
104 pasaje_numr int not null,
105 avion_cod int not null,
106 pais varchar(50),
107
108 primary key(cod_vuelo),
109 foreign key(pasaje_numr) references pasaje(num_reserva),
110 foreign key(avion_cod) references avion(cod_avion),
111 foreign key(pais) references pais(nombre)
112);
113
114create table if not exists aeropuerto
115(
116 nombre varchar(50),
117
118 vuelo_cod int not null,
119
120 primary key(nombre),
121 foreign key(vuelo_cod) references vuelo(cod_vuelo)
122);
123
124create table if not exists equipaje
125(
126 kilogramos int,
127 valor_extra int,
128
129 pasaje_numr int not null,
130
131 foreign key(pasaje_numr) references pasaje(num_reserva)
132);
133
134/*
135insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(0, 180000, 231, 'csacsa', true, false);
136insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(2, 143000, 432, 'dkjw', false, true);
137insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(4, 200100, 543, 'gfd', false, true);
138insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(6, 45678, 543, 'gfds', true, true);
139insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(3, 123453, 678, 'fghj', true, false);
140insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(7, 199999, 567, 'ertyu', false, false);
141insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(8, 300000, 567, 'tyuk', true , true);
142insert into pasaje(num_reserva, precio, num_asiento, fecha, online_presencial, ejecutivo_turista) values(9, 567788, 678, 'ghj0', false, true);
143*/
144
145#----------------------------------------------------------------------------------------------------------------------------------------------------
146#CONSULTAS-------------------------------------------------------------------------------------------------------------------------------------------
147#Consulta 1: Fecha y hora en que el avión de código X, despega desde un aeropuerto P-----------------------------------------------------------------
148select avion_selec.cod_avion as Codigo_Avion, vuelo.fecha as Fecha, vuelo.hora_salida as Hora_Salida, aeropuerto.nombre as Aeropuerto from (
149 select * from avion where cod_avion = 1234 #X
150) as avion_selec
151 inner join vuelo
152 on avion_selec.cod_avion = vuelo.avion_cod
153 inner join aeropuerto
154 on vuelo.cod_vuelo = aeropuerto.vuelo_cod
155where aeropuerto.nombre = 'EjemploAeropuerto'; #P
156
157#Consulta 2: Precio de los pasajes entre $100.000 y $200.000 de los vuelos de la primera semana del año 2018 pertenecientes a una aerolÃnea K--------
158select precios_selec.precio as Precio, aerolinea.nombre as Aerolinea from (
159 select precio, num_reserva from pasaje where precio < 200000
160) as precios_selec
161 inner join vuelo
162 on precios_selec.num_reserva = vuelo.pasaje_numr
163 inner join avion
164 on vuelo.avion_cod = avion.cod_avion
165 inner join aerolinea
166 on avion.aerolinea_cod = aerolinea.cod_aerolinea
167where precios_selec.precio > 100000 and fecha between(01-01-2018 and 07-01-2018) and aerolinea.nombre ='Ejemplo'; #K
168
169#Consulta 3: Antecedentes de los pasajeros que viajaron al paÃs B en una fecha DD-MM-AA--------------------------------------------------------------
170select fecha_selec.fecha as Fecha_Viaje, cliente.nombre as CLiente, cliente.antecedentes as Antecedentes from (
171 select fecha, pasaje_numr from vuelo where fecha = 'EjemploFecha' #DD-MM-AA
172) as fecha_selec
173 inner join pasaje
174 on fecha_selec.pasaje_numr = pasaje.num_reserva
175 inner join cliente
176 on pasaje.cliente_rut = cliente.rut
177where pasaje.pais ='EjemploPais';
178#----------------------------------------------------------------------------------------------------------------------------------------------------
179#----------------------------------------------------------------------------------------------------------------------------------------------------
180
181#----------------------------------------------------------------------------------------------------------------------------------------------------
182#PROCEDIMIENTOS--------------------------------------------------------------------------------------------------------------------------------------
183#Procedimiento 1: Insertar pasaje--------------------------------------------------------------------------------------------------------------------
184delimiter %%
185
186create procedure insertar_pasaje (in reserva int, in precio int , in asiento int, in fecha varchar(8), in o_p bool, in e_t bool, in rut int, in pais varchar(50))
187begin
188 declare var_reserva int;
189 declare var_precio int;
190 declare var_asiento int;
191 declare var_fecha varchar(8);
192 declare var_o_p bool;
193 declare var_e_t bool;
194 declare var_rut int;
195 declare var_pais varchar(50);
196
197 select num_reserva into var_reserva from pasaje where num_reserva = reserva;
198 select precio into var_precio from pasaje where precio = precio;
199 select num_asiento into var_asiento from pasaje where num_asiento = asiento;
200 select fecha into var_fecha from pasaje where fecha = fecha;
201 select online_presencial into var_o_p from pasaje where online_presencial = o_p;
202 select ejecutivo_turista into var_e_t from pasaje where ejecutivo_turista = e_t;
203 select cliente_rut into var_rut from pasaje where cliente_rut = rut;
204 select pais into var_pais from pasaje where pais = pais;
205
206 if var1 is null then
207 insert into pasaje(num_reserva, precio, num_asiento, online_presencial, ejecutivo_turista, cliente_rut, pais)
208 values(reserva, precio, asiento, o_p, e_t, rut, pais);
209 end if;
210
211end%%
212
213delimiter ;
214#procedimiento 2: Modificar pasaje-------------------------------------------------------------------------------------------------------------------
215delimiter //
216create procedure modificar_pasaje (in parametro2 int, in nueva_fecha varchar(8))
217begin
218 declare var_numr int;
219
220 select num_reserva into var_numr from pasaje where num_reserva = parametro2;
221
222 if var_numr is not null then
223 update pasaje set asiento = nueva_fecha where num_reserva = parametro2;
224 end if;
225
226end//
227
228delimiter ;
229#Procedimiento 3: Eliminar pasaje (por medio del numero de reserva)-----------------------------------------------------------------------------------
230delimiter $$
231
232create procedure eliminar_pasaje (in parametro3 int)
233begin
234
235 if exists (select num_reserva from pasaje where num_reserva = parametro3) then
236 delete from pasaje where num_reserva = parametro3;
237 end if;
238
239end$$
240
241delimiter ;
242
243
244-- Vistas de usuario (Fase 1)
245
246############ vista 1: datos de usuario #########
247
248drop view vista_usuario;
249
250CREATE VIEW vista_usuario AS (
251 select rut, nombre, apellido, antecedentes from cliente
252 order by nombre asc
253);
254############ vista 2: datos de vuelo #########
255
256
257CREATE VIEW vista_vuelo AS (
258 select cod_vuelo, fecha, hora_salida, hora_llegada from vuelo
259 order by cod_vuelo asc
260);
261
262############ vista 3: datos del pasaje #########
263
264CREATE VIEW vista_pasaje AS (
265 select num_reserva, ejecutivo_turista, num_asiento, puerta_embarque, fecha from pasaje
266 order by num_reserva asc
267);
268
269
270
271CREATE TRIGGER asiento_mensaje
272on avion
273for insert
274as
275print 'su asiento ha sido reservado'
276go