ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';



 REM Creation de la base de donnees aerienne

drop table pilote CASCADE CONSTRAINTS;

create table pilote(
       pl#     	number(4)    	constraint pk_pilote 
				primary key,
       plnom    varchar2(12)	constraint 
				nl_pilote_plnom not null 
				constraint 
				uk_pilote_plnom  unique,
       dnaiss	date   		constraint 
				nl_pilote_dnaiss  
				not null,
       adr     	varchar2(20)    default 'Paris',
       tel	varchar2(12),       
       sal	number(7,2)    constraint nl_pilote_sal 
				 not null constraint 
				chk_pilote_sal  
				check (sal < 70000.0)
);

drop table avion CASCADE CONSTRAINTS ;

create table avion(
       av#     	number(4)       	constraint pk_avion  primary key,
       avtype	varchar2(10)	constraint nl_avion_avtype   not null 
		CONSTRAINT chk_avion_type 
		CHECK (avtype in 	
('A300','A310','A320','B707','B727','CONCORDE','CARAVELLE')),
       cap 	number(4)	CONSTRAINT nl_avion_cap not null,
       loc	varchar2(20)	CONSTRAINT nl_avion_loc not null,
       remarq  	long            
);

drop table vol CASCADE CONSTRAINTS ;

create table vol(
       vol#    	number(4)    	CONSTRAINT pk_vol primary key,
       pilote#	number(4)    	CONSTRAINT nl_vol_pilote# not null
		CONSTRAINT vol_fk_pilote REFERENCES PILOTE(PL#)
		ON DELETE CASCADE,
       avion#  	number(4)    	CONSTRAINT nl_vol_avion# not null,
       vd		varchar2(20),
       va		varchar2(20),       
       hd		number(4) 	CONSTRAINT nl_vol_hd not null, 
       ha		number(4) CONSTRAINT nl_vol_ha not null,  
       dat		date,
	CONSTRAINT vol_chk_ha_hd CHECK (ha>hd),
	constraint fk_vol_avion# FOREIGN KEY (avion#) 
	REFERENCES AVION(AV#)
);


REM insertion des valeurs dans les tables       

insert into  pilote values(1, 'Miranda', '16-AUG-1952','Sophia-Antipolis', '93548254', 18009.0);
insert into  pilote values(2, 'St-exupery', '16-OCT-1932', 'Lyon', '91548254', 12300.0);
insert into  pilote values(3, 'Armstrong ', '11-MAR-1930', 'Wapakoneta','96548254', 24500.0);
insert into  pilote values(4, 'Tintin', '01-AUG-1929', 'Bruxelles','93548254', 21100.0);
insert into  pilote values(5, 'Gagarine', '12-AUG-1934', 'Klouchino','93548454', 22100.0);
insert into  pilote values(6, 'Baudry', '31-AUG-1959', 'Toulouse','93548444', 21000.0);
insert into  pilote values(8, 'Bush', '28-FEB-1924', 'Milton','44556254', 22000.0);
insert into  pilote values(9, 'Ruskoi', '16-AUG-1930', 'Moscou','73548254', 22000.0);
insert into  pilote values(10, 'Math', '12-AUG-1938', 'Paris', '23548254', 15000.0);
insert into  pilote values(11, 'Yen', '19-SEP-1942', 'Munich','13548254', 29000.0);
insert into  pilote values(12, 'Icare', '17-DEC-1962', 'Ithaques','73548211', 17000.6);
insert into  pilote values(13, 'Mopolo', '04-NOV-1955', 'Nice','93958211', 17000.6);
insert into  pilote values(14, 'Chretien', '04-NOV-1945', '','73223322', 15000.6);
insert into  pilote values(15, 'Vernes', '04-NOV-1935', 'Paris', '',17000.6);
insert into  pilote values(16, 'Tournesol', '04-AUG-1929', 'Bruxelles','', 15000.6);
insert into  pilote values(17, 'Concorde', '04-AUG-1966', 'Paris', '',21000.6);
insert into  pilote values(18, 'Foudil', '04-AUG-1966', 'Paris', '',21000.6);
insert into  pilote values(19, 'Foudelle', '04-AUG-1966', 'Paris', '',21000.6);
insert into  pilote values(20, 'Zembla', '04-AUG-1966', 'Paris', '',21000.6);


REM Insertion des avions

insert into  avion values(1, 'A300', 300, 'Nice', 'En service');   
insert into  avion values(2, 'A300', 300, 'Nice', 'En service');   
insert into  avion values(3, 'A320', 320, 'Paris', 'En service');  
insert into  avion values(4, 'A300', 300, 'Paris', 'En service');  
insert into  avion values(5, 'CONCORDE', 300, 'Nice', 'En service');
insert into  avion values(6, 'B707', 400, 'Paris', 'En panne');    
insert into  avion values(7, 'CARAVELLE', 300, 'Paris', 'En service');
insert into  avion values(8, 'B727', 250, 'Toulouse', 'En service');
insert into  avion values(9, 'CONCORDE', 350, 'Toulouse', 'En service');   
insert into  avion values(10, 'A300', 400, 'Paris', 'En service');
insert into  avion values(11, 'A300', 400, 'Paris', 'En service');
insert into  avion values(12, 'A300', 400, 'Paris', 'En service');
		    

REM Insertion des vols

insert into  vol values(100, 1,1,'Nice', 'Paris', '1345', '1500','3-MAR-1989' );  
insert into  vol values(110, 3,6,'Nice', 'Toulouse', '1230', '1325','6-MAR-1989' );       
insert into  vol values(120,4,3,'Nice', 'Paris', '0745', '0900','21-JUN-1989' );    
insert into  vol values(125, 12,6,'Paris', 'Nice', '1330', '1845','10-JAN-1989' );        
insert into  vol values(130, 4,8,'Toulouse','Beauvais', '0630','0750', '27-MAR-1989' );     
insert into  vol values(111, 5,3,'Nice', 'Paris', '0800', '0920','4-DEC-89' );    
insert into  vol values(135, 8,5,'Paris', 'Toulouse', '1200','1320','22-MAR-1989' );     
insert into  vol values(140, 14,9,'Lyon', 'Nice', '0700', '0750','4-JUN-1989' );     
insert into  vol values(150, 1,1,'Paris', 'Nantes','1630', '1725','28-MAR-1989' );        
insert into  vol values(153, 2,3,'Lyon', 'Nice', '1210', '1300','6-NOV-1989' );   
insert into  vol values(156, 9,2,'Paris', 'Lyon', '0230', '0320','14-JAN-1989' );   
insert into  vol values(200, 5,3,'Nice', 'Toulouse', '2030', '2125','17-JUN-1989' );      
insert into  vol values(210, 14,7,'Nice', 'Nantes', '1430', '1525','14-OCT-1989' );        
insert into  vol values(236, 8,4,'Lyon', 'Toulouse', '2130', '2250','15-OCT-1989' );     
insert into  vol values(240, 13,10, 'Nice', 'Paris', '2300', '2355','19-NOV-1989' );       
insert into  vol values(250, 13,4,'Bordeaux', 'Paris', '2300','2355', '25-DEC-89' );   
insert into  vol values(260, 13,5,'Bordeaux', 'Paris', '2300','2355', '30-NOV-1989' );    
insert into  vol values(270, 13,9,'Paris', 'New york', '1400','2300', '3-JAN-1989' );    
insert into  vol values(280, 8,9,'Nice', 'Mulhouse', '1200','1320','21-MAR-1989' );     
insert into  vol values(290, 3,8,'Beauvais', 'Marseille', '1230','1425', '9-MAR-1989' );       
insert into  vol values(310, 19,8,'Beauvais', 'Marseille', '1230','1425', '9-MAR-1989' );       

REM * insert into  vol values(360, 4,8,'Bordeaux', 'Paris', '23:00','23:55', sysdate );       

commit;
