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 pilote2 CASCADE CONSTRAINTS;

create table pilote2(
       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 avion2 CASCADE CONSTRAINTS ;

create table avion2(
       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 vol2 CASCADE CONSTRAINTS ;

create table vol2(
       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#)
);