create table AEROPORTO 
(Città varchar(20) primary key, 
Nazione varchar(20) not null, 
NumPiste integer);

create table VOLO (IdVolo char(6), 
GiornoSett enum('lunedì', 'martedì', 'mercoledì', 'giovedì', 'venerdì', 'sabato', 'domenica'),
CittàPart varchar(20) not null references AEROPORTO(Città), 
OraPart time not null, 
CittàArr varchar(20) not null references AEROPORTO(Città), 
OraArr time not null,  
TipoAereo char(20),
primary key(IdVolo, GiornoSett));

create table AEREO (
TipoAereo char(20) primary key, 
QtaMerci float, 
NumPasseggeri smallint not null);

insert into AEROPORTO values ('Milano', 'Italia', 50), 
('Roma', 'Italia', 30),
('Firenze', 'Italia', 50),
('Pisa', 'Italia', 50),
('Pescara', 'Italia', 25),
('Ancona', 'Italia', 35),
('Torino', 'Italia', 35),
('Bologna', 'Italia', 45),
('Napoli', 'Italia', 45),
('Parigi', 'Francia', 50), 
('Bordeaux', 'Francia', 15),('Nizza', 'Francia', 20),('Tolosa', 'Francia', null),
('Strasburgo', 'Francia', 10),('Figari', 'Francia', null),('Montpellier', 'Francia', 25),
('Londra', 'Inghilterra', 50), 
('Manchester', 'Inghilterra', 15),('Bath', 'Inghilterra', 20),('Glasgow', 'Inghilterra', 30),
('Bristol', 'Inghilterra', 10),('Birmingham', 'Inghilterra', 10),('Edimburgo', 'Inghilterra', 25),
('Madrid', 'Spagna', 50), 
('Barcellona', 'Spagna', 15),('Palma di Maiorca', 'Spagna', 20),('Malaga', 'Spagna', 30),
('Alicante', 'Spagna', 10),('Tenerife', 'Spagna', 10),('Ibiza', 'Spagna', 25),('Valencia', 'Spagna', null),
('Braga', 'Portogallo', 50), ('Lisbona', 'Portogallo', null), ('Lagos', 'Portogallo', 50), ('Coimbra', 'Portogallo', 30);

insert into VOLO values ('AZ274', 'domenica', 'Roma', '12:05:00', 'Londra', '14:30:00', 'Boeing 747-8'), 
('AZ274', 'giovedì', 'Roma', '12:05:00', 'Londra', '14:30:00', 'Boeing 747-8'), 
('AZ274', 'sabato', 'Roma', '12:05:00', 'Londra', '14:30:00', 'Embraer 190'), 
('AZ100', 'martedì', 'Parigi', '12:05:00', 'Milano', '14:30:00', 'Airbus A340-600'), 
('AZ100', 'giovedì', 'Parigi', '12:05:00', 'Milano', '14:30:00', 'Embraer 190'), 
 ('AZ274', 'lunedì', 'Alicante', '13:15:00', 'Parigi', '15:00:00', 'Boeing 747-8'),
('AZ274', 'venerdì', 'Parigi', '13:15:00', 'Alicante', '15:00:00', 'Boeing 777-200ER'),
('AZ115', 'giovedì', 'Torino', '12:05:00', 'Milano', '14:30:00', 'Airbus A340-600'),
('AZ115', 'martedì', 'Torino', '12:05:00', 'Milano', '14:30:00', 'Boeing 747-8'), 
('AZ115', 'sabato', 'Torino', '09:05:00', 'Parigi', '12:30:00', 'Boeing 777-200ER'), 
('AZ115', 'domenica', 'Torino', '18:05:00', 'Londra', '20:30:00', 'Airbus A340-500'), 
('AZ275', 'venerdì', 'Parigi', '13:15:00', 'Bologna', '15:00:00', 'Boeing 747-8'),
('AZ116', 'giovedì', 'Torino', '12:05:00', 'Bologna', '14:30:00', 'Airbus A340-600'),
('AZ116', 'martedì', 'Torino', '12:05:00', 'Bologna', '14:30:00', 'Boeing 747-8'), 
('AZ116', 'sabato', 'Torino', '09:05:00', 'Bologna', '12:30:00', 'Boeing 747-400'), 
('AZ116', 'domenica', 'Torino', '18:05:00', 'Bologna', '20:30:00', 'Airbus A340-500'), 
('AZ254', 'giovedì', 'Napoli', '12:05:00', 'Londra', '14:30:00', 'Boeing 777-200'), 
('AZ254', 'sabato', 'Napoli', '12:05:00', 'Londra', '14:30:00', 'Boeing 747-8'), 
('AZ130', 'martedì', 'Napoli', '12:05:00', 'Milano', '14:30:00', 'Embraer 190'), 
('AZ130', 'giovedì', 'Napoli', '12:05:00', 'Milano', '14:30:00', 'Airbus A340-600'), 
('AZ234', 'giovedì', 'Napoli', '13:15:00', 'Parigi', '15:00:00', 'Boeing 777-200'),
('AZ234', 'venerdì', 'Napoli', '13:15:00', 'Alicante', '15:00:00', 'Boeing 747-8'),
('AZ135', 'giovedì', 'Napoli', '12:05:00', 'Madrid', '14:30:00', 'Airbus A340-600'),
('AZ224', 'domenica', 'Pescara', '13:15:00', 'Roma', '15:00:00', 'Boeing 747-8'),
('AZ224', 'martedì', 'Pescara', '13:15:00', 'Milano', '15:00:00', 'Boeing 747-8'),
('AZ224', 'venerdì', 'Pisa', '13:15:00', 'Roma', '15:00:00', 'Boeing 747-8'),
('AZ224', 'lunedì', 'Pisa', '14:15:00', 'Milano', '15:30:00', 'Boeing 777-200'),
('AV115', 'giovedì', 'Parigi', '12:05:00', 'Milano', '14:30:00', 'Airbus A340-600'),
('AV115', 'martedì', 'Parigi', '12:05:00', 'Milano', '14:30:00', 'Boeing 747-8'), 
('AV115', 'sabato', 'Bordeaux', '09:05:00', 'Torino', '12:30:00', 'Boeing 747-400'), 
('AR115', 'domenica', 'Bordeaux', '18:05:00', 'Milano', '20:30:00', 'Airbus A340-500'), 
('AR275', 'venerdì', 'Parigi', '13:15:00', 'Bologna', '15:00:00', 'Boeing 747-8'),
('AW116', 'giovedì', 'Bordeaux', '12:05:00', 'Bologna', '14:30:00', 'Airbus A340-600'),
('AW116', 'martedì', 'Bordeaux', '12:05:00', 'Bologna', '14:30:00', 'Boeing 747-8'), 
('AW116', 'sabato', 'Strasburgo', '09:05:00', 'Bologna', '12:30:00', 'Boeing 747-400'), 
('AW116', 'domenica', 'Strasburgo', '18:05:00', 'Bologna', '20:30:00', 'Airbus A340-500');




insert into AEREO values ('Boeing 747-8', 80.00, 700), ('Airbus A340-600', 40.00, 420), 
('Boeing 747-400', 25.00, 624), ('Boeing 777-200', 0.00, 440), ('Embraer 190', 20.00, 100),
('Boeing 777-200ER', 0.00 ,320), ('Curtiss JN-4H', 100.00, 0);