-- Base de données Vols aériens

-- Création de la base si elle n'existe pas
CREATE DATABASE  IF NOT EXISTS `bdd_vols_aeriens` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `bdd_vols_aeriens`;

-- Suppression des tables si elles existent
DROP TABLE IF EXISTS `vol`;
DROP TABLE IF EXISTS `pilote`;
DROP TABLE IF EXISTS `avion`;

-- création des tables et insertion des enregistrements
CREATE TABLE `avion` (
	`noAvion` INTEGER PRIMARY KEY NOT NULL ,
	`nomAvion` VARCHAR(50) NOT NULL,
	`capacite` INTEGER NULL)
	ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `avion`(`noAvion`, `nomAvion`, `capacite`) VALUES (1, 'AIRBUS', 120);
INSERT INTO `avion`(`noAvion`, `nomAvion`, `capacite`) VALUES (2, 'AIRBUS', 200);
INSERT INTO `avion`(`noAvion`, `nomAvion`, `capacite`) VALUES (3, 'AIRBUS', 350);
INSERT INTO `avion`(`noAvion`, `nomAvion`, `capacite`) VALUES (4, 'CARAVELLE', 250);
INSERT INTO `avion`(`noAvion`, `nomAvion`, `capacite`) VALUES (5, 'BOEING', 500);

CREATE TABLE `pilote` (
	`noPilote` INTEGER PRIMARY KEY NOT NULL ,
	`nomPilote` VARCHAR(50) NOT NULL,
	`villePilote` VARCHAR(50) NULL)
	ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (100, 'DEMOY', 'PARIS');
INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (101, 'TARDI', 'TOULOUSE');
INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (110, 'HERTZ', 'PARIS');
INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (120, 'GARDARIN', 'PARIS');
INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (140, 'ORMEAU', 'BORDEAUX');
INSERT INTO `pilote`(`noPilote`, `nomPilote`, `villePilote`) VALUES (166, 'SAVARY', 'PERPIGNAN');

CREATE TABLE `vol` (
	`noVol` INTEGER PRIMARY KEY NOT NULL ,
	`villeDepart` VARCHAR(50) NOT NULL,
	`villearrivee` VARCHAR(50) NOT NULL,
	`heureDepart` TIME NULL,
	`heureArrivee` TIME NULL,
	`noPilote` INT NULL,
	`noAvion` INT NULL )
	ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `vol` ADD CONSTRAINT `FK_vol_pilote` FOREIGN KEY (`noPilote`) REFERENCES `pilote`(`noPilote`) ;
ALTER TABLE `vol` ADD CONSTRAINT `FK_vol_avion` FOREIGN KEY (`noAvion`) REFERENCES `avion`(`noAvion`);

INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (602, 'PARIS', 'NANTES', '12:50:00', '14:05:00', 110, 2);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (603, 'PARIS', 'NANTES', '08:05:00', '10:00:00', 110, 2);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (605, 'NANTES', 'BORDEAUX', '17:30:00', '18:45:00', 110, 2);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (606, 'BORDEAUX', 'PARIS', '20:45:00', '22:00:00', 140, 2);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (608, 'PARIS', 'GENEVE', '04:30:00', '05:50:00', 140, 4);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (615, 'BORDEAUX', 'PARIS', '07:20:00', '08:50:00', 101, 3);
INSERT INTO `vol` (`noVol`, `villeDepart`, `villeArrivee`, `heureDepart`, `heureArrivee`, `noPilote`, `noAvion`) VALUES (616, 'TOULOUSE', 'PARIS', '12:50:00', '14:45:00', 120, 5);
